Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-1573

Incorrect SQL for HAVING = entity variable

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 2.0.0-beta2
    • 2.0.0-beta3
    • None
    • 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

            fancy Catalina Wei
            dianner Dianne E. Richards
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: