[DERBY-3649] can't call a stored function with an aggregate argument without getting the following error: ERROR 42Y29

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.4.1.3
• Fix Version/s: 10.4.2.0, 10.5.1.1
• Component/s: SQL

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)
------------------------------------------------------

• Issue & fix info: Patch Available
• Bug behavior facts: Regression
• Assignee: Katherine Marsden
• Reporter: Sebastian
• Created: 28/Apr/08 22:26
• Updated: 21/Jan/11 17:51
• Resolved: 02/May/08 23:14

Description

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

Issue Links

• is duplicated by: DERBY-3631 UDF used with aggregate arguments results in error 30000