openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Catalina Wei (JIRA)" <j...@apache.org>
Subject [jira] Resolved: (OPENJPA-1573) Incorrect SQL for HAVING = entity variable
Date Tue, 16 Mar 2010 06:32:27 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-1573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Catalina Wei resolved OPENJPA-1573.
-----------------------------------

       Resolution: Fixed
    Fix Version/s: 2.0.0

Fix checked under trunk svn r923589

> 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
>             Fix For: 2.0.0
>
>
> 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

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message