[OPENJPA-1573] Incorrect SQL for HAVING = entity variable - ASF JIRA

XML

Word

Printable

JSON

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 2.0.0-beta2
Fix Version/s: 2.0.0-beta3
Component/s: None
Labels:
None

Description

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:

<openjpa-2.0.0-SNAPSHOT-r422266:922946M fatal general error> 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.<init>(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.<init>(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.<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.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.<init>(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

Attachments

Activity

People

Assignee:: Catalina Wei

Reporter:: Dianne E. Richards

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 15/Mar/10 14:27

Updated:: 29/Mar/10 23:17

Resolved:: 16/Mar/10 06:31