OpenJPA
  1. OpenJPA
  2. OPENJPA-51

bad sql pushdown, sub select is missing from clause

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.0, 1.0.1, 1.0.2, 1.0.3, 1.0.4, 1.1.0, 1.1.1, 1.2.0
    • Fix Version/s: 1.0.3, 1.1.1, 1.2.0
    • Component/s: query
    • Labels:
      None
    • Environment:
      Windows xp, db2, derby

      Description

      451 - bad sql pushdown sub select is missing from clause
      TEST451; select e from EmpBean e where e.empid > any (select e1.empid from DeptBean d, in(d.emps) e1 where d.no = 200)
      28344 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 295440796> [0 ms] executing prepstmnt 81790176 SELECT t0.empid, t0.bonus, t2.deptno, t2.budget, t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street, t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street, t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street = t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE (t0.empid = ANY((SELECT t1.deptno FROM DeptBean t1)))
      s/b
      select t1.empid FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t1.dept_deptno = t3.deptno WHERE t1.empid > ANY((SELECT t5.empid FROM DeptBean t4 INNER JOIN EmpBean t5 ON t4.deptno = t5.dept_deptno WHERE (CAST(t4.deptno AS BIGINT) = ?)))

      {int 200}

      <0|false|0.0.0> org.apache.openjpa.persistence.PersistenceException: Syntax error: Encountered "WHERE" at line 1, column 520.

      {SELECT t2.empid, t2.bonus, t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime, t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager, t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street = t5.street WHERE (t2.empid > ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno AS BIGINT) = CAST(? AS BIGINT)))))} [code=30000, state=42X01]
      at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3713)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:94)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:80)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:56)
      at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:152)
      at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:37)
      at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
      at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512)
      at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216)
      at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254)
      at com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187)
      at com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536)
      at com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591)
      at com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:615)
      at junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116)
      at junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106)
      at junit.framework.TestResult.runProtected(Unknown Source)
      at junit.extensions.jfunc.TestletWrapper.run(TestletWrapper.java:109)
      at junit.framework.TestSuite.runTest(Unknown Source)
      at junit.framework.TestSuite.run(Unknown Source)
      at junit.extensions.jfunc.JFuncSuite.run(JFuncSuite.java:134)
      at junit.extensions.jfunc.textui.JFuncRunner.doRun(JFuncRunner.java:76)
      at junit.extensions.jfunc.textui.JFuncRunner.start(JFuncRunner.java:398)
      at junit.extensions.jfunc.textui.JFuncRunner.main(JFuncRunner.java:218)
      Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: Syntax error: Encountered "WHERE" at line 1, column 520. {SELECT t2.empid, t2.bonus, t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime, t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager, t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street = t5.street WHERE (t2.empid > ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno AS BIGINT) = CAST(? AS BIGINT)))))}

      [code=30000, state=42X01]
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:193)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$6(LoggingConnectionDecorator.java:189)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:217)
      at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
      at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:137)
      at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1305)
      at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:149)
      at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:463)
      at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:443)
      at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:322)
      at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:295)
      at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:397)
      at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208)
      at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:198)
      at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:91)
      at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:31)
      ... 26 more
      NestedThrowables:
      ERROR 42X01: Syntax error: Encountered "WHERE" at line 1, column 520.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(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)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
      at org.apache.derby.jdbc.Driver30.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:185)
      at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:278)
      at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:162)
      at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:214)
      at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
      at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:137)
      at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1305)
      at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:149)
      at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:463)
      at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:443)
      at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:322)
      at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:295)
      at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:397)
      at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208)
      at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:198)
      at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:91)
      at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:31)
      at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
      at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
      at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512)
      at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216)
      at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254)
      at com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187)
      at com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536)
      at com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591)
      at com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:615)
      at junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116)
      at junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106)actual TEST451; select e from EmpBean e where e.empid > any (select e1.empid from DeptBean d, in(d.emps) e1 where d.no = 200)
      e
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Syntax error: Encountered "WHERE" at line 1, column 520.

      {SELECT t2.empid, t2.bonus, t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime, t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager, t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street = t5.street WHERE (t2.empid > ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno AS BIGINT) = CAST(? AS BIGINT)))))} [code=30000, state=42X01]
      TEST451; 1 tuple

      expected and actual line lengths do NOT match for line 2
      [ FAILED 451- bucket = fvtfull, query = select e from EmpBean e where e.empid > any (select e1.empid from DeptBean d, in(d.emps) e1 where d.no = 200) :
      DIFFERENCE-locations based on expected-(
      diff at line 2 position 1 EXPECTED [[] ACTUAL [ ]
      [( class com.dw.test.EmpBean empid=4 name=george salary=0.0 dept=200)]
      e
      )
      EXPECTED(
      TEST451; select e from EmpBean e where e.empid > any (select e1.empid from DeptBean d, in(d.emps) e1 where d.no = 200)
      [( class com.dw.test.EmpBean empid=4 name=george salary=0.0 dept=200)]
      [( class com.dw.test.EmpBean empid=9 name=harry salary=0.0 dept=210)]
      [( class com.dw.test.EmpBean empid=10 name=Catalina Wei salary=0.0 dept=0)]
      [( class com.dw.test.EmpBean empid=5 name=ritika salary=0.0 dept=220)]
      [( class com.dw.test.EmpBean empid=6 name=ahmad salary=0.0 dept=100)]
      [( class com.dw.test.EmpBean empid=7 name=charlene salary=0.0 dept=210)]
      [( class com.dw.test.EmpBean empid=8 name=Tom Rayburn salary=0.0 dept=100)]
      TEST451; 7 tuples )
      ACTUAL(
      TEST451; select e from EmpBean e where e.empid > any (select e1.empid from DeptBean d, in(d.emps) e1 where d.no = 200)
      e
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Syntax error: Encountered "WHERE" at line 1, column 520. {SELECT t2.empid, t2.bonus, t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime, t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager, t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street = t5.street WHERE (t2.empid > ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno AS BIGINT) = CAST(? AS BIGINT)))))}

      [code=30000, state=42X01]
      TEST451; 1 tuple) ]

      1. SelectImpl.java.patch
        3 kB
        Catalina Wei
      2. SelectImpl.java.patch
        3 kB
        Catalina Wei
      3. openjpa-51.patch
        3 kB
        Catalina Wei
      4. failureEntities.jar
        7 kB
        George Hongell

        Activity

        Hide
        George Hongell added a comment -

        removed testcase references.
        run on version 443432.

        EJBQL:
        select e from EmpBean e where e.empid > any (select e1.empid from DeptBean d, in(d.emps) e1 where d.no = 200)

        OPENJPA ERROR OR SQL PUSHDOWN:
        <0|false|0.0.0> org.apache.openjpa.persistence.PersistenceException: Syntax error: Encountered "WHERE" at line 1, column 520.

        {SELECT t2.empid, t2.bonus, t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime, t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager, t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street = t5.street WHERE (t2.empid > ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno AS BIGINT) = CAST(? AS BIGINT)))))} [code=30000, state=42X01]
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3713)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:94)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:80)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:56)
        at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:152)
        at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:37)
        at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
        at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
        at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
        at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
        at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
        at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512)
        at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216)
        at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254)
        at com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187)
        at com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536)
        at com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591)
        at com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:615)
        at junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116)
        at junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106)
        at junit.framework.TestResult.runProtected(Unknown Source)
        at junit.extensions.jfunc.TestletWrapper.run(TestletWrapper.java:109)
        at junit.framework.TestSuite.runTest(Unknown Source)
        at junit.framework.TestSuite.run(Unknown Source)
        at junit.extensions.jfunc.JFuncSuite.run(JFuncSuite.java:134)
        at junit.extensions.jfunc.textui.JFuncRunner.doRun(JFuncRunner.java:76)
        at junit.extensions.jfunc.textui.JFuncRunner.start(JFuncRunner.java:398)
        at junit.extensions.jfunc.textui.JFuncRunner.main(JFuncRunner.java:218)
        Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: Syntax error: Encountered "WHERE" at line 1, column 520. {SELECT t2.empid, t2.bonus, t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime, t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager, t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street = t5.street WHERE (t2.empid > ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno AS BIGINT) = CAST(? AS BIGINT)))))}

        [code=30000, state=42X01]
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:193)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$6(LoggingConnectionDecorator.java:189)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:217)
        at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
        at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:137)
        at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1305)
        at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:149)
        at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:463)
        at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:443)
        at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:322)
        at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:295)
        at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:397)
        at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208)
        at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:198)
        at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:91)
        at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:31)
        ... 26 more
        NestedThrowables:
        ERROR 42X01: Syntax error: Encountered "WHERE" at line 1, column 520.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(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)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
        at org.apache.derby.jdbc.Driver30.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:185)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:278)
        at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:162)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:214)
        at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
        at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:137)
        at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1305)
        at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:149)
        at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:463)
        at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:443)
        at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:322)
        at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:295)
        at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:397)
        at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208)
        at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:198)
        at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:91)
        at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:31)
        at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
        at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
        at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
        at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
        at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
        at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512)
        at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216)
        at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254)
        at com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187)
        at com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536)
        at com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591)
        at com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:615)
        at junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116)
        at junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106)

        SUGGESTED SQL PUSHDOWN:
        select t0.empid, t0.bonus, t2.deptno, t2.budget, t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street, t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street, t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street = t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE t0.empid > ANY((select t5.empid FROM DeptBean t4 INNER JOIN EmpBean t5 ON t4.deptno = t5.dept_deptno WHERE (CAST(t4.deptno AS BIGINT) = ?)))

        {int 200}
        Show
        George Hongell added a comment - removed testcase references. run on version 443432. EJBQL: select e from EmpBean e where e.empid > any (select e1.empid from DeptBean d, in(d.emps) e1 where d.no = 200) OPENJPA ERROR OR SQL PUSHDOWN: <0|false|0.0.0> org.apache.openjpa.persistence.PersistenceException: Syntax error: Encountered "WHERE" at line 1, column 520. {SELECT t2.empid, t2.bonus, t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime, t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager, t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street = t5.street WHERE (t2.empid > ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno AS BIGINT) = CAST(? AS BIGINT)))))} [code=30000, state=42X01] at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3713) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:94) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:80) at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:56) at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:152) at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:37) at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712) at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512) at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216) at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254) at com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187) at com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536) at com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591) at com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:615) at junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116) at junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106) at junit.framework.TestResult.runProtected(Unknown Source) at junit.extensions.jfunc.TestletWrapper.run(TestletWrapper.java:109) at junit.framework.TestSuite.runTest(Unknown Source) at junit.framework.TestSuite.run(Unknown Source) at junit.extensions.jfunc.JFuncSuite.run(JFuncSuite.java:134) at junit.extensions.jfunc.textui.JFuncRunner.doRun(JFuncRunner.java:76) at junit.extensions.jfunc.textui.JFuncRunner.start(JFuncRunner.java:398) at junit.extensions.jfunc.textui.JFuncRunner.main(JFuncRunner.java:218) Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: Syntax error: Encountered "WHERE" at line 1, column 520. {SELECT t2.empid, t2.bonus, t3.deptno, t3.budget, t3.name, t2.execLevel, t2.hireDate, t2.hireTime, t2.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t2.isManager, t2.name, t2.salary, t5.street, t5.city, t5.state, t5.zip FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN DeptBean t3 ON t2.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t2.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t2.work_street = t5.street WHERE (t2.empid > ANY((SELECT t1.empid FROM WHERE (CAST(t0.deptno AS BIGINT) = CAST(? AS BIGINT)))))} [code=30000, state=42X01] at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:193) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$6(LoggingConnectionDecorator.java:189) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:217) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160) at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:137) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160) at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1305) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:149) at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:463) at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:443) at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:322) at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:295) at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:397) at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208) at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:198) at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:91) at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:31) ... 26 more NestedThrowables: ERROR 42X01: Syntax error: Encountered "WHERE" at line 1, column 520. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(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) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source) at org.apache.derby.jdbc.Driver30.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:185) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:278) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:162) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:214) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160) at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:137) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160) at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1305) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:149) at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:463) at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:443) at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:322) at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:295) at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:397) at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208) at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:198) at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:91) at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:31) at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712) at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512) at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216) at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254) at com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187) at com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536) at com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591) at com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:615) at junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116) at junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106) SUGGESTED SQL PUSHDOWN: select t0.empid, t0.bonus, t2.deptno, t2.budget, t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street, t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street, t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street = t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE t0.empid > ANY((select t5.empid FROM DeptBean t4 INNER JOIN EmpBean t5 ON t4.deptno = t5.dept_deptno WHERE (CAST(t4.deptno AS BIGINT) = ?))) {int 200}
        Hide
        George Hongell added a comment -

        entity java files and derby ddl script to use to recreate this issue

        Show
        George Hongell added a comment - entity java files and derby ddl script to use to recreate this issue
        Hide
        Catalina Wei added a comment -

        Abe,
        I am working on issue OPENJPA-51 for David Wisneski.
        I need some education before attempting to resolve this issue.
        How aliases in the subqueries are created and resolved ?
        Consider the following 2 queries:
        1. select o from Customer c, in(c.orders)o where o.amount > (select sum(o2.amount) from c.orders o2)
        2. select o from Order where o.amount > (select sum(o2.amount) from Customer c, in(c.orders) o2)

        Aside from the semantic difference, in the 2 queries, the syntax difference is where "Customer c " is defined.
        When Customer c is defined in the subquery, the generated SQL has syntax error, essentially the FROM clause is missing the body and t2 in the selections is undefined:
        SELECT t2.oid, t2.version, t2.amount, t2.customer_countryCode, t2.customer_id, t2.delivered
        FROM TCUSTOMER t0 INNER JOIN TORDER t1 ON t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id WHERE (t2.amount > (SELECT SUM(t1.amount) FROM ))

        Under the debugger, it shows in which SelectImpl the aliases/tables are created/defined:

        Query 1: _aliases _tables
        SelectImpl (main): TCUSTOMER=0 0=TCUSTOMER t0
        TORDER=1 1=TORDER t1
        "oders.o"
        SelectImpl(subQ): TORDER=2 2=TORDER t2
        "orders"
        TORDER=3
        "jpqlalias1:"

        Query 2 (all aliase/tables are in the main SelectImpl, null in subQ's SelectImpl)
        SelectImpl(main): TCUSTOMER=0 0=TCUSTOMER t0
        "jpqlalias1:"
        TORDER=1 1=TORDER t1
        "jpqlalias1:.orders.o2"
        TORDER=2 2=TORDER t2

        It seems to me that openjpa assumes the alias defined in subquery is always derived from (i.e. correlated to) the main select.
        Correct me if I am wrong.
        I have tried changing the method in SelectImpl.findAlias(Table....)
        to call recordTableAlias() for subselect when the alias was found in parent select by the following code fragment:
        if (!fromParent && _parent != null) {
        boolean removeFromParent = key.toString().contains(":"); //+
        alias = _parent.findAlias(table, key, removeFromParent, this); //false => removeFromParent
        if (alias != null)

        { if (removeFromParent) //+ recordTableAlias(table, key, alias); //+ return alias; }

        }

        after this change, the SQL generates OK, but I do not think it is correct, because the subselect is missing the join predicate (missing where clause) for the path c.orders.
        In debugging, I saw selectJoins for c.order s were created; one for the parent select and one for the subselect. But somehow the one created for subselect disappeared
        ( or never got set in SelectImpl's _join field for subselect), the main SelectImpl's _join field seems to have the selectJoin for c.orders
        ( but no SQL predicate was generated out of this selectJoin).
        Should the selectionJoin on the parent be moved to subselect ? How/When should this be done?
        Am I on the right track of resolving this issue?
        Your input is much appreciated.

        Catalina

        Show
        Catalina Wei added a comment - Abe, I am working on issue OPENJPA-51 for David Wisneski. I need some education before attempting to resolve this issue. How aliases in the subqueries are created and resolved ? Consider the following 2 queries: 1. select o from Customer c, in(c.orders)o where o.amount > (select sum(o2.amount) from c.orders o2) 2. select o from Order where o.amount > (select sum(o2.amount) from Customer c, in(c.orders) o2) Aside from the semantic difference, in the 2 queries, the syntax difference is where "Customer c " is defined. When Customer c is defined in the subquery, the generated SQL has syntax error, essentially the FROM clause is missing the body and t2 in the selections is undefined: SELECT t2.oid, t2.version, t2.amount, t2.customer_countryCode, t2.customer_id, t2.delivered FROM TCUSTOMER t0 INNER JOIN TORDER t1 ON t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id WHERE (t2.amount > (SELECT SUM(t1.amount) FROM )) Under the debugger, it shows in which SelectImpl the aliases/tables are created/defined: Query 1: _aliases _tables SelectImpl (main): TCUSTOMER=0 0=TCUSTOMER t0 TORDER=1 1=TORDER t1 "oders.o" SelectImpl(subQ): TORDER=2 2=TORDER t2 "orders" TORDER=3 "jpqlalias1:" Query 2 (all aliase/tables are in the main SelectImpl, null in subQ's SelectImpl) SelectImpl(main): TCUSTOMER=0 0=TCUSTOMER t0 "jpqlalias1:" TORDER=1 1=TORDER t1 "jpqlalias1:.orders.o2" TORDER=2 2=TORDER t2 It seems to me that openjpa assumes the alias defined in subquery is always derived from (i.e. correlated to) the main select. Correct me if I am wrong. I have tried changing the method in SelectImpl.findAlias(Table....) to call recordTableAlias() for subselect when the alias was found in parent select by the following code fragment: if (!fromParent && _parent != null) { boolean removeFromParent = key.toString().contains(":"); //+ alias = _parent.findAlias(table, key, removeFromParent, this); //false => removeFromParent if (alias != null) { if (removeFromParent) //+ recordTableAlias(table, key, alias); //+ return alias; } } after this change, the SQL generates OK, but I do not think it is correct, because the subselect is missing the join predicate (missing where clause) for the path c.orders. In debugging, I saw selectJoins for c.order s were created; one for the parent select and one for the subselect. But somehow the one created for subselect disappeared ( or never got set in SelectImpl's _join field for subselect), the main SelectImpl's _join field seems to have the selectJoin for c.orders ( but no SQL predicate was generated out of this selectJoin). Should the selectionJoin on the parent be moved to subselect ? How/When should this be done? Am I on the right track of resolving this issue? Your input is much appreciated. Catalina
        Hide
        Catalina Wei added a comment -

        Abe,
        Could you please verify and comment on the patch I am providing for this issue.
        Thanks.

        Show
        Catalina Wei added a comment - Abe, Could you please verify and comment on the patch I am providing for this issue. Thanks.
        Hide
        Abe White added a comment -

        I don't remember how any of that stuff works at all. However, the following line in findAlias is suspect:

        _removeAliasFromParent = key.toString().contains(":");

        findAlias is called a lot with different keys. Setting a member field that is used in other methods based
        on whether the latest key happens to match a certain criteria seems like it could lead to arbitrary
        results in different circumstances. If there is a continuous thread of logic that links the last call to
        findAlias with the other methods where _removeAliasFromParent is used, it needs to be make more
        explicit.

        Show
        Abe White added a comment - I don't remember how any of that stuff works at all. However, the following line in findAlias is suspect: _removeAliasFromParent = key.toString().contains(":"); findAlias is called a lot with different keys. Setting a member field that is used in other methods based on whether the latest key happens to match a certain criteria seems like it could lead to arbitrary results in different circumstances. If there is a continuous thread of logic that links the last call to findAlias with the other methods where _removeAliasFromParent is used, it needs to be make more explicit.
        Hide
        Catalina Wei added a comment -

        Abe,
        Based on your comment, I have revised my patch.
        Please verify if the new patch is a better solution.
        Thanks.

        Show
        Catalina Wei added a comment - Abe, Based on your comment, I have revised my patch. Please verify if the new patch is a better solution. Thanks.
        Hide
        Abe White added a comment -

        No, this is no better. And now you've changed the meaning of the code. Before, the remove-from-parent
        logic was based on the key to the last call to findAlias. Now it's based on the key to any previous call to findAlias
        (so long as 0 <= alias <= 16 ??!!). The fact that you changed the meaning of the patch seemingly without intent
        and still feel it is correct doesn't give me any confidence in it.

        p.s. The boolean array is pointless since you never flip the
        boolean values – you only ever set to true that I see, and then test if any index is true later. So you could just as
        easily get by with a single boolean where you "|=" its value each time.

        p.p.s. I notice that you also have a "boolean removed = false"
        local declared in one method and you never change its value, making the local pointless too, unless
        you meant to assign it a new value based on something somewhere.

        Show
        Abe White added a comment - No, this is no better. And now you've changed the meaning of the code. Before, the remove-from-parent logic was based on the key to the last call to findAlias. Now it's based on the key to any previous call to findAlias (so long as 0 <= alias <= 16 ??!!). The fact that you changed the meaning of the patch seemingly without intent and still feel it is correct doesn't give me any confidence in it. p.s. The boolean array is pointless since you never flip the boolean values – you only ever set to true that I see, and then test if any index is true later. So you could just as easily get by with a single boolean where you "|=" its value each time. p.p.s. I notice that you also have a "boolean removed = false" local declared in one method and you never change its value, making the local pointless too, unless you meant to assign it a new value based on something somewhere.
        Hide
        Catalina Wei added a comment -

        Abe,
        I make some mistake in my previous patch, attaching a revised version.
        your conceren about removedAliasFromParent[] flag once set, was never reset; that is purposely done.
        In case any flag (base on number of aliases created so far) is on, there is potentially duplicate joins in the parent select (those joins were initially created for aliases initially attached in parent which was removed from parent, and now attached on subselect), we need to remove the duplicate joins on the parent, since there is one also created for subselect. The duplicate joins once removed, will never be seen on the next call to removeJoinsFromParent().
        The local boolean flag is set when joins are indeed removed from parent. Otherwise the normal processing of removing duplcate joins from subselect is performed.

        I think there is a much better solution to this issue, that is to create SelectImpl when a "SELECT" is encountered, rather than delaying it later as it is currently done.
        Current code creates aliases for the subselect to come later – in the middle of alias creating for subselect, there isn't a SelectImpl created for subselect yet.. So the alias is temporarily attached under the parent SelectImpl. (This could be based on the assumption that any alias defined in subselect is correated to outer subselect).

        Show
        Catalina Wei added a comment - Abe, I make some mistake in my previous patch, attaching a revised version. your conceren about removedAliasFromParent[] flag once set, was never reset; that is purposely done. In case any flag (base on number of aliases created so far) is on, there is potentially duplicate joins in the parent select (those joins were initially created for aliases initially attached in parent which was removed from parent, and now attached on subselect), we need to remove the duplicate joins on the parent, since there is one also created for subselect. The duplicate joins once removed, will never be seen on the next call to removeJoinsFromParent(). The local boolean flag is set when joins are indeed removed from parent. Otherwise the normal processing of removing duplcate joins from subselect is performed. I think there is a much better solution to this issue, that is to create SelectImpl when a "SELECT" is encountered, rather than delaying it later as it is currently done. Current code creates aliases for the subselect to come later – in the middle of alias creating for subselect, there isn't a SelectImpl created for subselect yet.. So the alias is temporarily attached under the parent SelectImpl. (This could be based on the assumption that any alias defined in subselect is correated to outer subselect).
        Hide
        Abe White added a comment -

        OK, I see. You still shouldn't be using a fixed array. Use a BitSet.

        As to the rest... as I said before, I don't remember how this stuff works. So it could be wrong
        and I wouldn't know it without doing more code-diving than I have time for. It's our fault for
        not having enough tests checked into OpenJPA. So after changing it to use a BitSet,
        assuming it passes all your tests, I'd vote to commit it. Once that happens we'll see how it
        fairs against our more extensive Kodo test suite.

        Show
        Abe White added a comment - OK, I see. You still shouldn't be using a fixed array. Use a BitSet. As to the rest... as I said before, I don't remember how this stuff works. So it could be wrong and I wouldn't know it without doing more code-diving than I have time for. It's our fault for not having enough tests checked into OpenJPA. So after changing it to use a BitSet, assuming it passes all your tests, I'd vote to commit it. Once that happens we'll see how it fairs against our more extensive Kodo test suite.
        Hide
        David Wisneski added a comment -

        fixed in r534623

        Show
        David Wisneski added a comment - fixed in r534623
        Hide
        Abe White added a comment -

        I have to reopen this, as the fix is causing a regression in our test suite. Given an entity EntityA with a field "b" of type EntityB, the following JPQL:

        select a from EntityA a where
        exists (select b from a.b b where b.intfield = 1)
        or
        exists (select b from a.b b where b.intfield = 0)

        is getting translated to:

        SELECT t1.ID, t1.TYP, t1.A FROM ENTITYA t1 WHERE (
        EXISTS (SELECT t0.ID FROM ENTITYB t0 WHERE t0.INTFIELD = 1 AND t1.B = t0.ID))
        OR
        EXISTS (SELECT t2.ID FROM ENTITYB t2, ENTITYA t3 WHERE t2.INTFIELD = 0 AND t3.B = t2.ID))
        )

        Notice that the first correlated subselect is fine, but the second has lost its correlation – it creates a new alias for EntityA's table.

        Show
        Abe White added a comment - I have to reopen this, as the fix is causing a regression in our test suite. Given an entity EntityA with a field "b" of type EntityB, the following JPQL: select a from EntityA a where exists (select b from a.b b where b.intfield = 1) or exists (select b from a.b b where b.intfield = 0) is getting translated to: SELECT t1.ID, t1.TYP, t1.A FROM ENTITYA t1 WHERE ( EXISTS (SELECT t0.ID FROM ENTITYB t0 WHERE t0.INTFIELD = 1 AND t1.B = t0.ID)) OR EXISTS (SELECT t2.ID FROM ENTITYB t2, ENTITYA t3 WHERE t2.INTFIELD = 0 AND t3.B = t2.ID)) ) Notice that the first correlated subselect is fine, but the second has lost its correlation – it creates a new alias for EntityA's table.
        Hide
        Catalina Wei added a comment -

        Abe,
        Is it really a regression? I replaced the copy of SelectImpl with revision 52881 (the original without my fixes),
        the similar query I ran gets "missing FROM body":

        1078 demo TRACE [main] openjpa.Query - Executing query: select c from Customer c where exists(select o from c.orders o where o.oid = 1) or exists(select o from c.orders o where o.oid = 2)
        1156 demo TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1643274738> executing prepstmnt 2138865532 SELECT t0.countryCode, t0.id, t0.version, t0.city, t0.state, t0.street, t0.zip, t0.name FROM WHERE (EXISTS (SELECT t2.oid FROM TORDER t2 WHERE (CAST(t2.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t2.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id ) OR EXISTS (SELECT t3.oid FROM TORDER t3 WHERE (CAST(t3.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t3.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id )) [params=(long) 1, (long) 2]
        1172 demo TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1643274738> [16 ms] spent
        1172 demo TRACE [main] openjpa.jdbc.JDBC - <t 1094861122, conn 0> [0 ms] close
        Exception: <0.0.0 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;t0.name FROM WHERE;WHERE

        {prepstmnt 2138865532 SELECT t0.countryCode, t0.id, t0.version, t0.city, t0.state, t0.street, t0.zip, t0.name FROM WHERE (EXISTS (SELECT t2.oid FROM TORDER t2 WHERE (CAST(t2.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t2.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id ) OR EXISTS (SELECT t3.oid FROM TORDER t3 WHERE (CAST(t3.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t3.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id )) [params=(long) 1, (long) 2]} [code=-104, state=42601]SQLCA OUTPUT[Errp=SQLNP012, Errd=[-2145779603, 0, 0, 0, -705, 0]]
        <0.0.0 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;t0.name FROM WHERE;WHERE {prepstmnt 2138865532 SELECT t0.countryCode, t0.id, t0.version, t0.city, t0.state, t0.street, t0.zip, t0.name FROM WHERE (EXISTS (SELECT t2.oid FROM TORDER t2 WHERE (CAST(t2.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t2.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id ) OR EXISTS (SELECT t3.oid FROM TORDER t3 WHERE (CAST(t3.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t3.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id )) [params=(long) 1, (long) 2]}

        [code=-104, state=42601]SQLCA OUTPUT[Errp=SQLNP012, Errd=[-2145779603, 0, 0, 0, -705, 0]]

        Are you sure your query ever worked before this fix ?

        Show
        Catalina Wei added a comment - Abe, Is it really a regression? I replaced the copy of SelectImpl with revision 52881 (the original without my fixes), the similar query I ran gets "missing FROM body": 1078 demo TRACE [main] openjpa.Query - Executing query: select c from Customer c where exists(select o from c.orders o where o.oid = 1) or exists(select o from c.orders o where o.oid = 2) 1156 demo TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1643274738> executing prepstmnt 2138865532 SELECT t0.countryCode, t0.id, t0.version, t0.city, t0.state, t0.street, t0.zip, t0.name FROM WHERE (EXISTS (SELECT t2.oid FROM TORDER t2 WHERE (CAST(t2.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t2.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id ) OR EXISTS (SELECT t3.oid FROM TORDER t3 WHERE (CAST(t3.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t3.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id )) [params=(long) 1, (long) 2] 1172 demo TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1643274738> [16 ms] spent 1172 demo TRACE [main] openjpa.jdbc.JDBC - <t 1094861122, conn 0> [0 ms] close Exception: <0.0.0 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;t0.name FROM WHERE;WHERE {prepstmnt 2138865532 SELECT t0.countryCode, t0.id, t0.version, t0.city, t0.state, t0.street, t0.zip, t0.name FROM WHERE (EXISTS (SELECT t2.oid FROM TORDER t2 WHERE (CAST(t2.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t2.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id ) OR EXISTS (SELECT t3.oid FROM TORDER t3 WHERE (CAST(t3.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t3.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id )) [params=(long) 1, (long) 2]} [code=-104, state=42601] SQLCA OUTPUT[Errp=SQLNP012, Errd= [-2145779603, 0, 0, 0, -705, 0] ] <0.0.0 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;t0.name FROM WHERE;WHERE {prepstmnt 2138865532 SELECT t0.countryCode, t0.id, t0.version, t0.city, t0.state, t0.street, t0.zip, t0.name FROM WHERE (EXISTS (SELECT t2.oid FROM TORDER t2 WHERE (CAST(t2.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t2.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id ) OR EXISTS (SELECT t3.oid FROM TORDER t3 WHERE (CAST(t3.oid AS BIGINT) = CAST(? AS BIGINT) AND t1.oid = t3.oid) AND t0.countryCode = t1.customer_countryCode AND t0.id = t1.customer_id )) [params=(long) 1, (long) 2]} [code=-104, state=42601] SQLCA OUTPUT[Errp=SQLNP012, Errd= [-2145779603, 0, 0, 0, -705, 0] ] Are you sure your query ever worked before this fix ?
        Hide
        Abe White added a comment -

        Yes, the test was passing before. Have you tried against a to-one relation rather than a to-many?
        Perhaps it was never working against a to-many.

        Show
        Abe White added a comment - Yes, the test was passing before. Have you tried against a to-one relation rather than a to-many? Perhaps it was never working against a to-many.
        Hide
        Catalina Wei added a comment -

        Abe,
        You are right, the code before theispatch (SelectImpl.java revision 52881) worked for to-one relation,
        but never worked for to-many relation.

        Do you have any idea of fixing this problem?

        Show
        Catalina Wei added a comment - Abe, You are right, the code before theispatch (SelectImpl.java revision 52881) worked for to-one relation, but never worked for to-many relation. Do you have any idea of fixing this problem?
        Hide
        Marc Prud'hommeaux added a comment -

        This patch caused 17 TCK tests to start failing, so it needs to be reverted as soon as possible.

        Show
        Marc Prud'hommeaux added a comment - This patch caused 17 TCK tests to start failing, so it needs to be reverted as soon as possible.
        Hide
        Catalina Wei added a comment -

        Abe,
        Could you please verify if this patch regress your tests ?
        thanks.

        Show
        Catalina Wei added a comment - Abe, Could you please verify if this patch regress your tests ? thanks.
        Hide
        Catalina Wei added a comment -

        The attached patch passed TCK on derby.

        Show
        Catalina Wei added a comment - The attached patch passed TCK on derby.
        Hide
        Catalina Wei added a comment -

        A sub-task OPENJPA-658 is opened for outstanding issues remaining in subqueries.
        Fix committed under r676467 is for subqueries that are not correlated to its outer select,
        examples:

        1. "select o.oid from Order o where o.customer.name =" +
        " (select max(o2.customer.name) from Order o2" +
        " where o.customer.cid.id = o2.customer.cid.id)",
        2. "select o from Order o where o.customer.name =" +
        " (select max(o2.customer.name) from Order o2" +
        " where o.customer.cid.id = o2.customer.cid.id)",

        Show
        Catalina Wei added a comment - A sub-task OPENJPA-658 is opened for outstanding issues remaining in subqueries. Fix committed under r676467 is for subqueries that are not correlated to its outer select, examples: 1. "select o.oid from Order o where o.customer.name =" + " (select max(o2.customer.name) from Order o2" + " where o.customer.cid.id = o2.customer.cid.id)", 2. "select o from Order o where o.customer.name =" + " (select max(o2.customer.name) from Order o2" + " where o.customer.cid.id = o2.customer.cid.id)",
        Hide
        Catalina Wei added a comment -

        fix in 1.0.3 and 1.2.0

        Show
        Catalina Wei added a comment - fix in 1.0.3 and 1.2.0
        Hide
        Catalina Wei added a comment -

        The query string with the following subquery resulting a SQL with syntax error:
        "select o1.oid from Order o1 where o1.oid in " +
        " (select distinct o.oid from OrderItem i, Order o" +
        " where i.quantity > 10 and o.amount > 1000 and i.lid = o.oid)"

        This kind of subquery is more like a SQL style subquery, and subquery is not correlated to its outer query.

        Rather than creating yet another sub-task (OPENJPA-658, sub-task of this issue addressing some outstanding subquery problems),
        the fix for this subquery problem is checked in under this issue, - svn revision : r676787.

        Show
        Catalina Wei added a comment - The query string with the following subquery resulting a SQL with syntax error: "select o1.oid from Order o1 where o1.oid in " + " (select distinct o.oid from OrderItem i, Order o" + " where i.quantity > 10 and o.amount > 1000 and i.lid = o.oid)" This kind of subquery is more like a SQL style subquery, and subquery is not correlated to its outer query. Rather than creating yet another sub-task ( OPENJPA-658 , sub-task of this issue addressing some outstanding subquery problems), the fix for this subquery problem is checked in under this issue, - svn revision : r676787.
        Hide
        David Ezzio added a comment -

        From trunk, merged fix to 1.1.x branch at 802200

        Show
        David Ezzio added a comment - From trunk, merged fix to 1.1.x branch at 802200

          People

          • Assignee:
            Catalina Wei
            Reporter:
            George Hongell
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development