You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Kathey Marsden (JIRA)" <ji...@apache.org> on 2008/04/29 00:33:56 UTC
[jira] Commented: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12592915#action_12592915 ]
Kathey Marsden commented on DERBY-3649:
---------------------------------------
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.
> 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
> Affects Versions: 10.4.1.3
> Environment: ------------------ Java Information ------------------
> Java Version: 1.4.2
> Java Vendor: Free Software Foundation, Inc.
> Java home: /usr/lib/jvm/java-1.4.2-gcj-1.4.2.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(tm) Platform API Specification
> java.specification.version: 1.4
> --------- Derby Information --------
> JRE - JDBC: J2SE 1.4.2 - JDBC 3.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 : [en/US [en_US]]
> Found support for locale: [fr]
> version: 10.4.1.3 - (648739)
> ------------------------------------------------------
> Reporter: Sebastian
>
> 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.