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

• Key: DERBY-3649
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: Kathey Marsden
• Reporter: Sebastian
• Component/s: SQL
• Affects Version/s: 10.4.1.3
• Fix Version/s: 10.4.2.0, 10.5.1.1

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

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