openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dianne Richards (JIRA)" <j...@apache.org>
Subject [jira] Created: (OPENJPA-1573) Incorrect SQL for HAVING = entity variable
Date Mon, 15 Mar 2010 14:28:27 GMT
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:

<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