[
https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kathey Marsden updated DERBY-3649:
----------------------------------
Component/s: SQL
Interestingly the code does not correctly restrict group by expression if the
expression is not in the select list.
Even with the check in place, I can
CREATE FUNCTION R() returns double external name 'java.lang.Math.random'
language java parameter style java;
ij> select count(*) from test group by r();
1
-----------
1
Which I don't think I am supposed to be able to do.
The functional test does
select r(), count(*) from test group by r()
so gets the expected error.
> can't call a stored function with an aggregate argument without getting the
> following error: ERROR 42Y29
> --------------------------------------------------------------------------------------------------------
>
> Key: DERBY-3649
> URL: https://issues.apache.org/jira/browse/DERBY-3649
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3
> Environment: ------------------ Java Information ------------------
> Java Version: 1.6.0
> Java Vendor: Sun Microsystems Inc.
> Java home: /usr/java/jdk1.6.0/jre
> Java classpath:
> /me/apache_libs/db-derby-10.4.1.3-bin/lib/derby.jar:/me/apache_libs/db-derby-10.4.1.3-bin/lib/derbynet.jar:/me/apache_libs/db-derby-10.4.1.3-bin/lib/derbytools.jar:/me/apache_libs/db-derby-10.4.1.3-bin/lib/derbyclient.jar
> OS name: Linux
> OS architecture: i386
> OS version: 2.6.23.1
> Java user name: me
> Java user home: /me
> Java user dir: /me/apache_libs/db-derby-10.4.1.3-bin/bin
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.6
> --------- Derby Information --------
> JRE - JDBC: Java SE 6 - JDBC 4.0
> [/me/apache_libs/db-derby-10.4.1.3-bin/lib/derby.jar] 10.4.1.3 - (648739)
> [/me/apache_libs/db-derby-10.4.1.3-bin/lib/derbytools.jar] 10.4.1.3 - (648739)
> [/me/apache_libs/db-derby-10.4.1.3-bin/lib/derbynet.jar] 10.4.1.3 - (648739)
> [/me/apache_libs/db-derby-10.4.1.3-bin/lib/derbyclient.jar] 10.4.1.3 -
> (648739)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> Current Locale : [English/United States [en_US]]
> Found support for locale: [cs]
> version: 10.4.1.3 - (648739)
> Found support for locale: [de_DE]
> version: 10.4.1.3 - (648739)
> Found support for locale: [es]
> version: 10.4.1.3 - (648739)
> Found support for locale: [fr]
> version: 10.4.1.3 - (648739)
> Found support for locale: [hu]
> version: 10.4.1.3 - (648739)
> Found support for locale: [it]
> version: 10.4.1.3 - (648739)
> Found support for locale: [ja_JP]
> version: 10.4.1.3 - (648739)
> Found support for locale: [ko_KR]
> version: 10.4.1.3 - (648739)
> Found support for locale: [pl]
> version: 10.4.1.3 - (648739)
> Found support for locale: [pt_BR]
> version: 10.4.1.3 - (648739)
> Found support for locale: [ru]
> version: 10.4.1.3 - (648739)
> Found support for locale: [zh_CN]
> version: 10.4.1.3 - (648739)
> Found support for locale: [zh_TW]
> version: 10.4.1.3 - (648739)
> ------------------------------------------------------
> Reporter: Sebastian
> Assignee: Kathey Marsden
>
> hi,
> i cant execute the following statement with an aggregate (count(*)) argument:
> SELECT checkCount(count(*)) FROM SYS.SYSTABLES;
> .. without getting this error/stacktrace:
> ERROR 42Y29: The SELECT list of a non-grouped query contains at least one
> invalid expression. When the SELECT list contains at least one aggregate then
> all entries must be valid aggregate expressions.
> java.sql.SQLSyntaxErrorException: The SELECT list of a non-grouped query
> contains at least one invalid expression. When the SELECT list contains at
> least one aggregate then all entries must be valid aggregate expressions.
> at
> org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.SqlException.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.Statement.execute(Unknown Source)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.main(Unknown Source)
> at org.apache.derby.tools.ij.main(Unknown Source)
> Caused by: org.apache.derby.client.am.SqlException: The SELECT list of a
> non-grouped query contains at least one invalid expression. When the SELECT
> list contains at least one aggregate then all entries must be valid aggregate
> expressions.
> at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
> at
> org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown
> Source)
> at
> org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown
> Source)
> at
> org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
> at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
> at org.apache.derby.client.am.Statement.executeX(Unknown Source)
> ... 9 more
> here the code to create the function in derby:
> CREATE FUNCTION checkCount
> (count INTEGER)
> RETURNS INTEGER
> LANGUAGE JAVA PARAMETER STYLE JAVA
> NO SQL
> EXTERNAL NAME 'ExceptionOnZeroCount.checkCount';
> and here the code of the (quite simple) java method:
> public class ExceptionOnZeroCount {
> public static int checkCount(int count)
> throws SQLException {
> if (count == 0)
> throw new SQLException("No results found", "38777");
> }
> return count;
> }
> }
> hope this will be fixed :) Good night!
> mamurdian
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
