Prepared statement fails when GROUP BY and SELECT clause contain the same
expression based on a parameter
---------------------------------------------------------------------------------------------------------
Key: DERBY-4955
URL: https://issues.apache.org/jira/browse/DERBY-4955
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.7.1.1
Environment: M2_REPO/javax/activation/activation/1.1/activation-1.1.jar
M2_REPO/javax/el/el-api/2.2/el-api-2.2.jar
M2_REPO/javax/xml/bind/jaxb-api/2.1/jaxb-api-2.1.jar
M2_REPO/javax/servlet/jsp/jsp-api/2.1.2/jsp-api-2.1.2.jar
M2_REPO/javax/transaction/jta/1.1/jta-1.1.jar
M2_REPO/javax/xml/soap/saaj-api/1.3/saaj-api-1.3.jar
M2_REPO/javax/servlet/servlet-api/2.5/servlet-api-2.5.jar
M2_REPO/javax/xml/stream/stax-api/1.0-2/stax-api-1.0-2.jar
M2_REPO/javax/validation/validation-api/1.0.0.GA/validation-api-1.0.0.GA.jar
M2_REPO/antlr/antlr/2.7.6/antlr-2.7.6.jar
M2_REPO/aopalliance/aopalliance/1.0/aopalliance-1.0.jar
M2_REPO/org/apache/james/apache-mime4j/0.6/apache-mime4j-0.6.jar
M2_REPO/asm/asm/2.2.3/asm-2.2.3.jar
M2_REPO/org/aspectj/aspectjrt/1.6.2/aspectjrt-1.6.2.jar
M2_REPO/bouncycastle/bcmail-jdk14/138/bcmail-jdk14-138.jar
M2_REPO/org/bouncycastle/bcmail-jdk14/1.38/bcmail-jdk14-1.38.jar
M2_REPO/bouncycastle/bcprov-jdk14/138/bcprov-jdk14-138.jar
M2_REPO/org/bouncycastle/bcprov-jdk14/1.38/bcprov-jdk14-1.38.jar
M2_REPO/org/bouncycastle/bctsp-jdk14/1.38/bctsp-jdk14-1.38.jar
M2_REPO/org/beanshell/bsh/2.0b4/bsh-2.0b4.jar
M2_REPO/c3p0/c3p0/0.9.1/c3p0-0.9.1.jar
M2_REPO/commons-beanutils/commons-beanutils/1.8.2/commons-beanutils-1.8.2.jar
M2_REPO/commons-codec/commons-codec/1.3/commons-codec-1.3.jar
M2_REPO/commons-collections/commons-collections/3.2.1/commons-collections-3.2.1.jar
M2_REPO/commons-digester/commons-digester/1.7/commons-digester-1.7.jar
M2_REPO/commons-io/commons-io/1.4/commons-io-1.4.jar
M2_REPO/commons-lang/commons-lang/2.4/commons-lang-2.4.jar
M2_REPO/commons-logging/commons-logging/1.1.1/commons-logging-1.1.1.jar
M2_REPO/net/sourceforge/cssparser/cssparser/0.9.5/cssparser-0.9.5.jar
M2_REPO/org/apache/cxf/cxf-api/2.2.7/cxf-api-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-common-schemas/2.2.7/cxf-common-schemas-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-common-utilities/2.2.7/cxf-common-utilities-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-bindings-soap/2.2.7/cxf-rt-bindings-soap-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-bindings-xml/2.2.7/cxf-rt-bindings-xml-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-core/2.2.7/cxf-rt-core-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-databinding-aegis/2.2.7/cxf-rt-databinding-aegis-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-databinding-jaxb/2.2.7/cxf-rt-databinding-jaxb-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-frontend-jaxws/2.2.7/cxf-rt-frontend-jaxws-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-frontend-simple/2.2.7/cxf-rt-frontend-simple-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-javascript/2.2.7/cxf-rt-javascript-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-transports-http/2.2.7/cxf-rt-transports-http-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-transports-http-jetty/2.2.7/cxf-rt-transports-http-jetty-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-transports-local/2.2.7/cxf-rt-transports-local-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-rt-ws-addr/2.2.7/cxf-rt-ws-addr-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-tools-common/2.2.7/cxf-tools-common-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-tools-java2ws/2.2.7/cxf-tools-java2ws-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-tools-validator/2.2.7/cxf-tools-validator-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-tools-wsdlto-core/2.2.7/cxf-tools-wsdlto-core-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-tools-wsdlto-databinding-jaxb/2.2.7/cxf-tools-wsdlto-databinding-jaxb-2.2.7.jar
M2_REPO/org/apache/cxf/cxf-tools-wsdlto-frontend-jaxws/2.2.7/cxf-tools-wsdlto-frontend-jaxws-2.2.7.jar
M2_REPO/org/apache/derby/derby/10.7.1.1/derby-10.7.1.1.jar
M2_REPO/org/apache/derby/derbytools/10.7.1.1/derbytools-10.7.1.1.jar
M2_REPO/dom4j/dom4j/1.6.1/dom4j-1.6.1.jar
M2_REPO/org/glassfish/web/el-impl/2.2/el-impl-2.2.jar
M2_REPO/org/springframework/security/facelets-taglib/0.2_jsf-2.0_spring-2/facelets-taglib-0.2_jsf-2.0_spring-2.jar
M2_REPO/freemarker/freemarker/2.3.8/freemarker-2.3.8.jar
M2_REPO/org/apache/geronimo/specs/geronimo-annotation_1.0_spec/1.1.1/geronimo-annotation_1.0_spec-1.1.1.jar
M2_REPO/org/apache/geronimo/javamail/geronimo-javamail_1.4_mail/1.7/geronimo-javamail_1.4_mail-1.7.jar
M2_REPO/org/apache/geronimo/specs/geronimo-jaxws_2.1_spec/1.0/geronimo-jaxws_2.1_spec-1.0.jar
M2_REPO/org/apache/geronimo/specs/geronimo-ws-metadata_2.0_spec/1.1.2/geronimo-ws-metadata_2.0_spec-1.1.2.jar
M2_REPO/org/codehaus/groovy/groovy-all/1.7.5/groovy-all-1.7.5.jar
M2_REPO/org/hibernate/hibernate-annotations/3.5.6-Final/hibernate-annotations-3.5.6-Final.jar
M2_REPO/org/hibernate/hibernate-c3p0/3.5.6-Final/hibernate-c3p0-3.5.6-Final.jar
M2_REPO/org/hibernate/hibernate-commons-annotations/3.2.0.Final/hibernate-commons-annotations-3.2.0.Final.jar
M2_REPO/org/hibernate/hibernate-core/3.5.6-Final/hibernate-core-3.5.6-Final.jar
M2_REPO/org/hibernate/hibernate-entitymanager/3.5.3-Final/hibernate-entitymanager-3.5.3-Final.jar
M2_REPO/org/hibernate/hibernate-envers/3.5.3-Final/hibernate-envers-3.5.3-Final.jar
M2_REPO/org/hibernate/javax/persistence/hibernate-jpa-2.0-api/1.0.0.Final/hibernate-jpa-2.0-api-1.0.0.Final.jar
M2_REPO/org/hibernate/hibernate-tools/3.2.0.ga/hibernate-tools-3.2.0.ga.jar
M2_REPO/org/hibernate/hibernate-validator/4.1.0.Beta1/hibernate-validator-4.1.0.Beta1.jar
M2_REPO/net/sourceforge/htmlunit/htmlunit/2.8/htmlunit-2.8.jar
M2_REPO/net/sourceforge/htmlunit/htmlunit-core-js/2.8/htmlunit-core-js-2.8.jar
M2_REPO/org/apache/httpcomponents/httpclient/4.0.1/httpclient-4.0.1.jar
M2_REPO/org/apache/httpcomponents/httpcore/4.0.1/httpcore-4.0.1.jar
M2_REPO/org/apache/httpcomponents/httpmime/4.0.1/httpmime-4.0.1.jar
M2_REPO/com/lowagie/itext/2.1.7/itext-2.1.7.jar
M2_REPO/net/sf/jasperreports/jasperreports/3.7.2/jasperreports-3.7.2.jar
M2_REPO/javassist/javassist/3.8.0.GA/javassist-3.8.0.GA.jar
M2_REPO/com/sun/xml/bind/jaxb-impl/2.1.12/jaxb-impl-2.1.12.jar
M2_REPO/com/sun/xml/bind/jaxb-xjc/2.1.12/jaxb-xjc-2.1.12.jar
M2_REPO/jfree/jcommon/1.0.15/jcommon-1.0.15.jar
M2_REPO/eclipse/jdtcore/3.1.0/jdtcore-3.1.0.jar
M2_REPO/org/mortbay/jetty/jetty/6.1.21/jetty-6.1.21.jar
M2_REPO/org/eclipse/jetty/jetty-http/7.0.1.v20091125/jetty-http-7.0.1.v20091125.jar
M2_REPO/org/eclipse/jetty/jetty-io/7.0.1.v20091125/jetty-io-7.0.1.v20091125.jar
M2_REPO/org/eclipse/jetty/jetty-util/7.0.1.v20091125/jetty-util-7.0.1.v20091125.jar
M2_REPO/org/mortbay/jetty/jetty-util/6.1.21/jetty-util-6.1.21.jar
M2_REPO/jfree/jfreechart/1.0.12/jfreechart-1.0.12.jar
M2_REPO/com/sun/faces/jsf-api/2.0.2/jsf-api-2.0.2.jar
M2_REPO/com/sun/faces/jsf-impl/2.0.2/jsf-impl-2.0.2.jar
M2_REPO/jstl/jstl/1.2/jstl-1.2.jar
M2_REPO/org/hibernate/jtidy/r8-20060801/jtidy-r8-20060801.jar
M2_REPO/junit/junit/3.8.2/junit-3.8.2.jar
M2_REPO/log4j/log4j/1.2.13/log4j-1.2.13.jar
/rita/lib/migrate/migrate4j-svn-091117.jar
M2_REPO/mysql/mysql-connector-java/5.1.13/mysql-connector-java-5.1.13.jar
M2_REPO/org/apache/neethi/neethi/2.0.4/neethi-2.0.4.jar
M2_REPO/net/sourceforge/nekohtml/nekohtml/1.9.14/nekohtml-1.9.14.jar
M2_REPO/oro/oro/2.0.8/oro-2.0.8.jar
M2_REPO/org/apache/poi/poi/3.6/poi-3.6.jar
M2_REPO/com/sun/xml/messaging/saaj/saaj-impl/1.3.2/saaj-impl-1.3.2.jar
M2_REPO/org/w3c/css/sac/1.3/sac-1.3.jar
M2_REPO/xalan/serializer/2.7.1/serializer-2.7.1.jar
M2_REPO/org/slf4j/slf4j-api/1.5.8/slf4j-api-1.5.8.jar
M2_REPO/org/slf4j/slf4j-log4j12/1.5.8/slf4j-log4j12-1.5.8.jar
M2_REPO/org/springframework/spring-aop/2.0.8/spring-aop-2.0.8.jar
M2_REPO/org/springframework/spring-beans/2.0.8/spring-beans-2.0.8.jar
M2_REPO/org/springframework/spring-context/2.0.8/spring-context-2.0.8.jar
M2_REPO/org/springframework/spring-core/2.0.8/spring-core-2.0.8.jar
M2_REPO/org/springframework/security/spring-security-core/2.0.4/spring-security-core-2.0.4.jar
M2_REPO/org/springframework/spring-support/2.0.8/spring-support-2.0.8.jar
M2_REPO/org/springframework/spring-web/2.0.8/spring-web-2.0.8.jar
M2_REPO/org/apache/tomcat/tomcat-juli/7.0.5/tomcat-juli-7.0.5.jar
M2_REPO/org/apache/velocity/velocity/1.6.2/velocity-1.6.2.jar
M2_REPO/wsdl4j/wsdl4j/1.6.2/wsdl4j-1.6.2.jar
M2_REPO/org/codehaus/woodstox/wstx-asl/3.2.9/wstx-asl-3.2.9.jar
M2_REPO/xalan/xalan/2.7.1/xalan-2.7.1.jar
M2_REPO/xerces/xercesImpl/2.9.1/xercesImpl-2.9.1.jar
M2_REPO/xml-apis/xml-apis/1.3.02/xml-apis-1.3.02.jar
M2_REPO/xml-resolver/xml-resolver/1.2/xml-resolver-1.2.jar
M2_REPO/org/apache/ws/commons/schema/XmlSchema/1.4.5/XmlSchema-1.4.5.jar
/rita/lib/zxing/zxing13_core.jar
/rita/lib/zxing/zxing13_javase.jar
Reporter: Chris Wilson
This query:
conn.prepareStatement("SELECT " +
"CASE WHEN (this.id = ?) THEN 1 ELSE 0 END " +
"FROM request_line AS this " +
"GROUP BY " +
"CASE WHEN (this.id = ?) THEN 1 ELSE 0 END");
Fails with the following exception:
java.sql.SQLSyntaxErrorException: Column reference 'THIS.ID' is invalid, or is
part of an invalid expression. For a SELECT list with a GROUP BY, the columns
and expressions being selected may only contain valid grouping expressions and
valid aggregate expressions.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at
org.wfp.rita.test.derby.DerbyCrashTest.testPrepareQueryWithGroupByParameterFails(DerbyCrashTest.java:82)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at junit.framework.TestCase.runTest(TestCase.java:164)
at junit.framework.TestCase.runBare(TestCase.java:130)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:230)
at junit.framework.TestSuite.run(TestSuite.java:225)
at
org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
at
org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: java.sql.SQLException: Column reference 'THIS.ID' is invalid, or is
part of an invalid expression. For a SELECT list with a GROUP BY, the columns
and expressions being selected may only contain valid grouping expressions and
valid aggregate expressions.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
... 32 more
Caused by: ERROR 42Y36: Column reference 'THIS.ID' is invalid, or is part of an
invalid expression. For a SELECT list with a GROUP BY, the columns and
expressions being selected may only contain valid grouping expressions and
valid aggregate expressions.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.VerifyAggregateExpressionsVisitor.visit(Unknown
Source)
at org.apache.derby.impl.sql.compile.QueryTreeNode.accept(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryOperatorNode.acceptChildren(Unknown
Source)
at org.apache.derby.impl.sql.compile.QueryTreeNode.accept(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ConditionalNode.acceptChildren(Unknown Source)
at org.apache.derby.impl.sql.compile.QueryTreeNode.accept(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ResultColumn.acceptChildren(Unknown Source)
at org.apache.derby.impl.sql.compile.QueryTreeNode.accept(Unknown
Source)
at
org.apache.derby.impl.sql.compile.QueryTreeNodeVector.acceptChildren(Unknown
Source)
at org.apache.derby.impl.sql.compile.QueryTreeNode.accept(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source)
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
... 26 more
Derby doesn't know at SQL compile time whether the SELECT and the GROUP BY
expression are equal, because it depends which value would be bound to them
*after* the statement is prepared. However, when using Hibernate all constant
values in a query are bound using parameters, which hits this limitation.
Test case:
package org.wfp.rita.test.derby;
import java.io.File;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Properties;
import junit.framework.TestCase;
import org.apache.derby.jdbc.EmbeddedDriver;
public class DerbyCrashTest extends TestCase
{
boolean useTempFile = true;
Connection conn;
Statement s;
public void setUp() throws Exception
{
if (useTempFile)
{
File t =
File.createTempFile("DerbyCrashTest.testCrashOnSelectQuery",
"derby");
t.delete();
conn = new EmbeddedDriver().connect("jdbc:derby:" +
";databaseName=" + t.getCanonicalPath() + ";user=rita" +
";create=true", new Properties());
}
else
{
conn = new EmbeddedDriver().connect("jdbc:derby:" +
";databaseName=/tmp/rita-copy/rita-test.derby;user=rita",
new Properties());
}
s = conn.createStatement();
if (useTempFile)
{
s.execute("CREATE TABLE request_line (" +
"request_site_id integer NOT NULL, " +
"id integer NOT NULL, " +
"request_id integer NOT NULL)");
s.execute("CREATE TABLE request (" +
"request_site_id integer NOT NULL, " +
"id integer NOT NULL)");
s.execute("ALTER TABLE request " +
"ADD CONSTRAINT pk_request " +
"PRIMARY KEY (request_site_id, id)");
s.execute("ALTER TABLE request_line " +
"ADD CONSTRAINT pk_request_line " +
"PRIMARY KEY (id, request_site_id)");
s.execute("ALTER TABLE request_line " +
"ADD CONSTRAINT fk_tblreque_reference_tblrequ4 " +
"FOREIGN KEY (request_site_id, request_id) " +
"REFERENCES request(request_site_id, id) " +
"ON UPDATE RESTRICT ON DELETE RESTRICT");
}
}
public void tearDown() throws Exception
{
s.close();
conn.close();
}
public void testPrepareQueryWithGroupByParameterFails() throws Exception
{
conn.prepareStatement("SELECT " +
"CASE WHEN (this.id = ?) THEN 1 ELSE 0 END " +
"FROM request_line AS this " +
"GROUP BY " +
"CASE WHEN (this.id = ?) THEN 1 ELSE 0 END");
}
}
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
