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

parsing error - cast of subselect does not work

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 0.9.7
    • query
    • None
    • Windows xp, derby and db2

    Description

      163 - parsing error - cast of subselect does not work
      TEST163; select e from EmpBean e where e.salary + 100 > all (select e1.salary from EmpBean e1 left join e1.dept d where d.no = 20)
      Syntax error: Encountered "ALL" at line 1, column 484.

      {SELECT t0.empid, t0.bonus, t3.deptno, t3.budget, t3.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t0.isManager, t0.name, t0.salary, t5.street, t5.city, t5.state, t5.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t3 ON t0.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t0.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t0.work_street = t5.street WHERE ((CAST(t0.salary AS DOUBLE) + CAST(? AS DOUBLE)) > CAST(ALL((SELECT t1.salary FROM EmpBean t1, DeptBean t2 WHERE (CAST(t2.deptno AS BIGINT) = CAST(? AS BIGINT)) AND t1.dept_deptno = t2.deptno)) AS DOUBLE))} [code=30000, state=42X01]
      {double 100, int 20}

      this works
      select t0.empid, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t3 ON t0.dept_deptno = t3.deptno WHERE ((CAST(t0.salary AS DOUBLE) + CAST(? AS DOUBLE)) > ALL (SELECT t1.salary FROM EmpBean t1, DeptBean t2 WHERE (CAST(t2.deptno AS BIGINT) = CAST(? AS BIGINT)) AND t1.dept_deptno = t2.deptno)) {double 100, int 20}

      <0|false|0.0.0> org.apache.openjpa.persistence.PersistenceException: Syntax error: Encountered "ALL" at line 1, column 484. {SELECT t0.empid, t0.bonus, t3.deptno, t3.budget, t3.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t0.isManager, t0.name, t0.salary, t5.street, t5.city, t5.state, t5.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t3 ON t0.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t0.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t0.work_street = t5.street WHERE ((CAST(t0.salary AS DOUBLE) + CAST(? AS DOUBLE)) > CAST(ALL((SELECT t1.salary FROM EmpBean t1, DeptBean t2 WHERE (CAST(t2.deptno AS BIGINT) = CAST(? AS BIGINT)) AND t1.dept_deptno = t2.deptno)) AS DOUBLE))}

      [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 "ALL" at line 1, column 484.

      {SELECT t0.empid, t0.bonus, t3.deptno, t3.budget, t3.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t0.isManager, t0.name, t0.salary, t5.street, t5.city, t5.state, t5.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t3 ON t0.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t0.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t0.work_street = t5.street WHERE ((CAST(t0.salary AS DOUBLE) + CAST(? AS DOUBLE)) > CAST(ALL((SELECT t1.salary FROM EmpBean t1, DeptBean t2 WHERE (CAST(t2.deptno AS BIGINT) = CAST(? AS BIGINT)) AND t1.dept_deptno = t2.deptno)) AS DOUBLE))} [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 "ALL" at line 1, column 484.
      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)
      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)
      TEST163; select e from EmpBean e where e.salary + 100 > all (select e1.salary from EmpBean e1 left join e1.dept d where d.no = 20)
      e
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Syntax error: Encountered "ALL" at line 1, column 484. {SELECT t0.empid, t0.bonus, t3.deptno, t3.budget, t3.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t4.street, t4.city, t4.state, t4.zip, t0.isManager, t0.name, t0.salary, t5.street, t5.city, t5.state, t5.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t3 ON t0.dept_deptno = t3.deptno LEFT OUTER JOIN AddressBean t4 ON t0.home_street = t4.street LEFT OUTER JOIN AddressBean t5 ON t0.work_street = t5.street WHERE ((CAST(t0.salary AS DOUBLE) + CAST(? AS DOUBLE)) > CAST(ALL((SELECT t1.salary FROM EmpBean t1, DeptBean t2 WHERE (CAST(t2.deptno AS BIGINT) = CAST(? AS BIGINT)) AND t1.dept_deptno = t2.deptno)) AS DOUBLE))}

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

      Attachments

        1. failureEntities.jar
          7 kB
          George Hongell

        Activity

          People

            Unassigned Unassigned
            ghongell George Hongell
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: