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 "Chris Wilson (JIRA)" <ji...@apache.org> on 2011/01/02 18:48:46 UTC
[jira] Created: (DERBY-4955) Prepared statement fails when GROUP BY
and SELECT clause contain the same expression based on a parameter
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.
[jira] Updated: (DERBY-4955) Prepared statement fails when GROUP BY
and SELECT clause contain the same expression based on a parameter
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-4955:
----------------------------------
Issue & fix info: [Repro attached] (was: [Repro attached, Newcomer])
It seems this is not a bug, so if there are no objections, we should close it invalid. unchecking Newcomer as that is for issues appropriate for new developers and don't think this applies.
> 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.
[jira] [Commented] (DERBY-4955) Prepared statement fails when GROUP
BY and SELECT clause contain the same expression based on a parameter
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Resolved] (DERBY-4955) Prepared statement fails when GROUP
BY and SELECT clause contain the same expression based on a parameter
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor resolved DERBY-4955.
------------------------------------
Resolution: Invalid
> 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
[jira] Commented: (DERBY-4955) Prepared statement fails when GROUP
BY and SELECT clause contain the same expression based on a parameter
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4955?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12976677#action_12976677 ]
Knut Anders Hatlen commented on DERBY-4955:
-------------------------------------------
Strictly speaking, SQL doesn't allow expressions in the group by clause (at least, that's how I read section 7.9 <group by clause> in SQL:2003, part 2), so a portable version of the query would have to look something like this:
SELECT x FROM
(SELECT CASE WHEN (this.id = ?) THEN 1 ELSE 0 END
FROM request_line AS this) s(x)
GROUP BY x
Or, in this case, it could be even simpler:
SELECT DISTINCT CASE WHEN (id = ?) THEN 1 ELSE 0 END FROM request_line
I'm not familiar with Hibernate, so I don't know if it's possible to tweak the generated SQL this way, but I think the most reasonable fix for this problem would be to make it generate conforming SQL.
> 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.