[ http://issues.apache.org/jira/browse/OPENJPA-48?page=all ]
George Hongell updated OPENJPA-48: ---------------------------------- Attachment: failureEntities.jar entity java files and derby ddl script to use to recreate this issue > parsing error - cast of subselect does not work > ------------------------------------------------ > > Key: OPENJPA-48 > URL: http://issues.apache.org/jira/browse/OPENJPA-48 > Project: OpenJPA > Issue Type: Bug > Components: query > Environment: Windows xp, derby and db2 > Reporter: George Hongell > Attachments: failureEntities.jar > > > 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 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira