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 "Mamta A. Satoor (JIRA)" <ji...@apache.org> on 2012/09/28 18:55:07 UTC
[jira] [Commented] (DERBY-4955) Prepared statement fails when GROUP
BY and SELECT clause contain the same expression based on a parameter
[ https://issues.apache.org/jira/browse/DERBY-4955?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13465728#comment-13465728 ]
Mamta A. Satoor commented on DERBY-4955:
----------------------------------------
Kathey, I will go ahead and close this as invalid since it seems like Derby is conforming to SQL standard.
> 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.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira