db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3649) can't call a stored function with an aggregate argument without getting the following error: ERROR 42Y29
Date Mon, 28 Apr 2008 22:33:56 GMT

    [ 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.


Mime
View raw message