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 "Sebastian (JIRA)" <ji...@apache.org> on 2008/04/29 00:27:55 UTC
[jira] Created: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
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.
[jira] Updated: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Sebastian (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sebastian updated DERBY-3649:
-----------------------------
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)
------------------------------------------------------
was:
------------------ 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)
------------------------------------------------------
i corrected my eviroment information.
> 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.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)
> ------------------------------------------------------
> Reporter: Sebastian
> Assignee: Kathey Marsden
>
> 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.
[jira] Commented: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12592939#action_12592939 ]
Bryan Pendleton commented on DERBY-3649:
----------------------------------------
GroupByNode.bindStatement() definitely seems like a natural
and appropriate location for this sort of check.
Thanks for working on this problem, Kathey!
> 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
> Components: SQL
> Affects Versions: 10.4.1.3
> 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)
> ------------------------------------------------------
> Reporter: Sebastian
> Assignee: Kathey Marsden
> Attachments: derby-3649_try1_diff.txt
>
>
> 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.
[jira] Commented: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12592927#action_12592927 ]
Kathey Marsden commented on DERBY-3649:
---------------------------------------
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.
> 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
> Components: SQL
> Affects Versions: 10.4.1.3
> 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)
> ------------------------------------------------------
> Reporter: Sebastian
> Assignee: Kathey Marsden
>
> 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.
[jira] Updated: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3649:
----------------------------------
Attachment: derby-3649_diff.txt
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
> 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
> Components: SQL
> Affects Versions: 10.4.1.3
> 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)
> ------------------------------------------------------
> Reporter: Sebastian
> Assignee: Kathey Marsden
> Attachments: derby-3649_diff.txt, derby-3649_try1_diff.txt
>
>
> 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.
[jira] Updated: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3649:
----------------------------------
Component/s: SQL
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.
> 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
> Components: SQL
> Affects Versions: 10.4.1.3
> 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)
> ------------------------------------------------------
> Reporter: Sebastian
> Assignee: Kathey Marsden
>
> 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.
[jira] Commented: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12592923#action_12592923 ]
Bryan Pendleton commented on DERBY-3649:
----------------------------------------
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.
> 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
> Components: SQL
> Affects Versions: 10.4.1.3
> 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)
> ------------------------------------------------------
> Reporter: Sebastian
> Assignee: Kathey Marsden
>
> 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.
[jira] Assigned: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden reassigned DERBY-3649:
-------------------------------------
Assignee: Kathey Marsden
> 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
> Assignee: Kathey Marsden
>
> 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.
[jira] Updated: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3649:
----------------------------------
Attachment: derby-3649_try1_diff.txt
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.
> 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
> Components: SQL
> Affects Versions: 10.4.1.3
> 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)
> ------------------------------------------------------
> Reporter: Sebastian
> Assignee: Kathey Marsden
> Attachments: derby-3649_try1_diff.txt
>
>
> 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.
[jira] Updated: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3649:
----------------------------------
Derby Info: [Patch Available, Regression] (was: [Regression])
> 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
> Components: SQL
> Affects Versions: 10.4.1.3
> 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)
> ------------------------------------------------------
> Reporter: Sebastian
> Assignee: Kathey Marsden
> Attachments: derby-3649_diff.txt, derby-3649_try1_diff.txt
>
>
> 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.
[jira] Resolved: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3649?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden resolved DERBY-3649.
-----------------------------------
Resolution: Fixed
Fix Version/s: 10.5.0.0
10.4.1.4
10.3.2.2
Derby Info: [Patch Available, Regression] (was: [Regression, Patch Available])
> 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
> Components: SQL
> Affects Versions: 10.4.1.3
> 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)
> ------------------------------------------------------
> Reporter: Sebastian
> Assignee: Kathey Marsden
> Fix For: 10.3.2.2, 10.4.1.4, 10.5.0.0
>
> Attachments: derby-3649_diff.txt, derby-3649_try1_diff.txt
>
>
> 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.
[jira] Commented: (DERBY-3649) can't call a stored function with an
aggregate argument without getting the following error: ERROR 42Y29
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ 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.