From dev-return-15675-apmail-openjpa-dev-archive=openjpa.apache.org@openjpa.apache.org Mon Mar 15 14:29:39 2010 Return-Path: Delivered-To: apmail-openjpa-dev-archive@www.apache.org Received: (qmail 984 invoked from network); 15 Mar 2010 14:29:39 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 15 Mar 2010 14:29:39 -0000 Received: (qmail 96963 invoked by uid 500); 15 Mar 2010 14:28:52 -0000 Delivered-To: apmail-openjpa-dev-archive@openjpa.apache.org Received: (qmail 96934 invoked by uid 500); 15 Mar 2010 14:28:52 -0000 Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@openjpa.apache.org Delivered-To: mailing list dev@openjpa.apache.org Received: (qmail 96926 invoked by uid 99); 15 Mar 2010 14:28:52 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 15 Mar 2010 14:28:52 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.140] (HELO brutus.apache.org) (140.211.11.140) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 15 Mar 2010 14:28:48 +0000 Received: from brutus.apache.org (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id 47F33234C4A8 for ; Mon, 15 Mar 2010 14:28:27 +0000 (UTC) Message-ID: <181624505.266241268663307293.JavaMail.jira@brutus.apache.org> Date: Mon, 15 Mar 2010 14:28:27 +0000 (UTC) From: "Dianne Richards (JIRA)" To: dev@openjpa.apache.org Subject: [jira] Created: (OPENJPA-1573) Incorrect SQL for HAVING = entity variable MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org Incorrect SQL for HAVING = entity variable ------------------------------------------ Key: OPENJPA-1573 URL: https://issues.apache.org/jira/browse/OPENJPA-1573 Project: OpenJPA Issue Type: Bug Affects Versions: 2.0.0-beta2 Reporter: Dianne Richards Assignee: Catalina Wei I added the following query to test case TestSpec10_1_26: query = "select VALUE(e) from Department1 d, " + " in (d.empMap) e " + "group by VALUE(e) " + "having VALUE(e) = :emp1"; q = em.createQuery(query).setParameter("emp1", e1); I also tried the following, which should be equivalent: query = "select e from Department1 d, " + " in (d.empMap) e " + "group by e " + "having e = :emp1"; q = em.createQuery(query).setParameter("emp1", e1); Both failed with the following: org.apache.openjpa.persistence.PersistenceException: Column T2.EMPID is referenced in the HAVING clause but is not in the GROUP BY list. {SELECT t1.empId, t1.dept_id FROM T6D1 t0 INNER JOIN T6E1 t1 ON t0.deptId = t1.dept_id INNER JOIN T6E1 t2 ON t0.deptId = t2.dept_id GROUP BY t1.empId, t1.dept_id HAVING t2.empId = ?} [code=30000, state=42X24] at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4823) at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4783) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:137) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:118) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:70) at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:155) at org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.handleCheckedException(QueryImpl.java:2101) at org.apache.openjpa.lib.rop.EagerResultList.(EagerResultList.java:40) at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1243) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1004) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:860) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:791) at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542) at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:290) at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:304) at org.apache.openjpa.persistence.jdbc.maps.spec_10_1_26_ex0.TestSpec10_1_26.queryQualifiedId(TestSpec10_1_26.java:151) at org.apache.openjpa.persistence.jdbc.maps.spec_10_1_26_ex0.TestSpec10_1_26.testQueryQualifiedId(TestSpec10_1_26.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37) at java.lang.reflect.Method.invoke(Method.java:600) at junit.framework.TestCase.runTest(TestCase.java:154) at org.apache.openjpa.persistence.test.AbstractPersistenceTestCase.runTest(AbstractPersistenceTestCase.java:516) at junit.framework.TestCase.runBare(TestCase.java:127) at org.apache.openjpa.persistence.test.AbstractPersistenceTestCase.runBare(AbstractPersistenceTestCase.java:503) at org.apache.openjpa.persistence.test.AbstractPersistenceTestCase.runBare(AbstractPersistenceTestCase.java:479) 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:118) at org.apache.openjpa.persistence.test.AbstractPersistenceTestCase.run(AbstractPersistenceTestCase.java:179) at junit.framework.TestSuite.runTest(TestSuite.java:208) at junit.framework.TestSuite.run(TestSuite.java:203) 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: org.apache.openjpa.lib.jdbc.ReportingSQLException: Column T2.EMPID is referenced in the HAVING clause but is not in the GROUP BY list. {SELECT t1.empId, t1.dept_id FROM T6D1 t0 INNER JOIN T6E1 t1 ON t0.deptId = t1.dept_id INNER JOIN T6E1 t2 ON t0.deptId = t2.dept_id GROUP BY t1.empId, t1.dept_id HAVING t2.empId = ?} [code=30000, state=42X24] at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:257) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:237) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:70) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:289) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:186) at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:155) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:186) at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1600) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:175) at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:549) at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:529) at org.apache.openjpa.jdbc.sql.SelectImpl.prepareStatement(SelectImpl.java:450) at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:391) at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:363) at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:427) at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:230) at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:220) at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94) at org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.open(QueryImpl.java:2060) at org.apache.openjpa.lib.rop.EagerResultList.(EagerResultList.java:34) ... 31 more NestedThrowables: java.sql.SQLSyntaxErrorException: Column T2.EMPID is referenced in the HAVING clause but is not in the GROUP BY list. 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.(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.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:262) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:307) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:188) at org.apache.openjpa.lib.jdbc.JDBCEventConnectionDecorator$EventConnection.prepareStatement(JDBCEventConnectionDecorator.java:200) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:186) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:286) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:186) at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:155) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:186) at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1600) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:175) at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:549) at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:529) at org.apache.openjpa.jdbc.sql.SelectImpl.prepareStatement(SelectImpl.java:450) at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:391) at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:363) at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:427) at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:230) at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:220) at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94) at org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.open(QueryImpl.java:2060) at org.apache.openjpa.lib.rop.EagerResultList.(EagerResultList.java:34) at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1243) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1004) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:860) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:791) at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542) at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:290) at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:304) at org.apache.openjpa.persistence.jdbc.maps.spec_10_1_26_ex0.TestSpec10_1_26.queryQualifiedId(TestSpec10_1_26.java:151) at org.apache.openjpa.persistence.jdbc.maps.spec_10_1_26_ex0.TestSpec10_1_26.testQueryQualifiedId(TestSpec10_1_26.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37) at java.lang.reflect.Method.invoke(Method.java:600) at junit.framework.TestCase.runTest(TestCase.java:154) at org.apache.openjpa.persistence.test.AbstractPersistenceTestCase.runTest(AbstractPersistenceTestCase.java:516) at junit.framework.TestCase.runBare(TestCase.java:127) at org.apache.openjpa.persistence.test.AbstractPersistenceTestCase.runBare(AbstractPersistenceTestCase.java:503) at org.apache.openjpa.persistence.test.AbstractPersistenceTestCase.runBare(AbstractPersistenceTestCase.java:479) 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:118) at org.apache.openjpa.persistence.test.AbstractPersistenceTestCase.run(AbstractPersistenceTestCase.java:179) at junit.framework.TestSuite.runTest(TestSuite.java:208) at junit.framework.TestSuite.run(TestSuite.java:203) 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 T2.EMPID is referenced in the HAVING clause but is not in the GROUP BY list. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 63 more Caused by: ERROR 42X24: Column T2.EMPID is referenced in the HAVING clause but is not in the GROUP BY list. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown Source) at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown Source) at org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(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) ... 57 more -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.