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

Comments

[Kathey Marsden]

The query works fine with 10.1 and seems related to this change for DERBY-883.
http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VerifyAggregateExpressionsVisitor.java?p2=%2Fdb%2Fderby%2Fcode%2Ftrunk%2Fjava%2Fengine%2Forg%2Fapache%2Fderby%2Fimpl%2Fsql%2Fcompile%2FVerifyAggregateExpressionsVisitor.java&p1=%2Fdb%2Fderby%2Fcode%2Ftrunk%2Fjava%2Fengine%2Forg%2Fapache%2Fderby%2Fimpl%2Fsql%2Fcompile%2FVerifyAggregateExpressionsVisitor.java&r1=437070&r2=437069&view=diff&pathrev=437070

The change I think was intended to prevent group by java function, but disallows java functions with aggregate arguments.
Removing the code when node instanceof JavaToSQLValueNode, corrects the problem but also allows group by function.
Looking for a better way to check for that.

[Sebastian]

i corrected my eviroment information.

[Kathey Marsden]

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.

[Bryan Pendleton]

Yes, I believe that VerifyAggregateExpressionsVisitor visits the
select's "result column list", and for each component of that list,
verifies that it is either a valid aggregate function, or a valid grouping column.

From memory, I thought that in your example:

   select count(*) from test group by r()

the "r()" expression should have been "generated" into the
result column list (look at the places where ResultColumn.markGenerated
gets called to see how that process occurs).

Generating the group by expression into the select's RCL should have
then resulted in the Verify visitor encountering it, so I'm not sure why
your example isn't being caught, but maybe this provides some
ideas to investigate further.

[Kathey Marsden]

It seems to only add it to the generated columns if it is an instance of ColumnReference.
In GroupByList see ~line 177
if (! matchFound &&
groupingCol.getColumnExpression() instanceof ColumnReference)
{
// only add matching columns for column references not
// expressions yet. See DERBY-883 for details.


I am wondering if VerifyAggregateExpressionsVisitor is even the right place to check if the group by expression is a function.
It seems to know only about the select columns. Maybe we should be checking elsewhere entirely.

[Kathey Marsden]

Attached is an experimental patch for this issue. I have not run tests or added regression tests yet, but it passes for the repro and disallows group by function as expected I believe. I moved the check to GroupByNode.bindStatement() where I could check the node type of the group by expression. Please let me know if this approach looks ok.

GroupByNode.bindStatement() definitely seems like a natural
and appropriate location for this sort of check.

Thanks for working on this problem, Kathey!

[Kathey Marsden]

Attached is a patch for this issue. derbyall and suites.All ran fine except for some strange network server timeouts that don't seem to be related to this change as I get them without my changes as well.

I saw one file merge on svn update so I will rerun tests, but this patch is ready for review.

Moves the check for routine in group by expression from VerifyAggregateExpressionsVistor to GroupByList.bindGroupByColumns.

Kathey