You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Catalina Wei (JIRA)" <ji...@apache.org> on 2007/04/26 09:43:15 UTC
[jira] Commented: (OPENJPA-51) bad sql pushdown, sub select is
missing from clause
[ https://issues.apache.org/jira/browse/OPENJPA-51?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12491902 ]
Catalina Wei commented on OPENJPA-51:
-------------------------------------
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
> bad sql pushdown, sub select is missing from clause
> ---------------------------------------------------
>
> Key: OPENJPA-51
> URL: https://issues.apache.org/jira/browse/OPENJPA-51
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Environment: Windows xp, db2, derby
> Reporter: George Hongell
> Assigned To: David Wisneski
> Fix For: 0.9.8
>
> Attachments: failureEntities.jar
>
>
> 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) ]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.