[
https://issues.apache.org/jira/browse/OPENJPA-53?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
David Wisneski closed OPENJPA-53.
---------------------------------
Resolution: Fixed
not a problem anymore
> bad sql pushdown for nested subselects, missing nested subselect
> -----------------------------------------------------------------
>
> Key: OPENJPA-53
> URL: https://issues.apache.org/jira/browse/OPENJPA-53
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Environment: Windows xp, derby, db2
> Reporter: George Hongell
> Attachments: failureEntities.jar
>
>
> 536 bad sql pushdown for nested subselects
> TEST536; select e from EmpBean e where (e.salary = (select max(e.salary)
> from EmpBean e where e.salary > some (select f.salary from EmpBean f where
> f.dept.mgr.empid=e.dept.mgr.empid)) )
> Syntax error: Encountered ")" at line 1, column 504. {SELECT t0.empid,
> t0.bonus, t5.deptno, t5.budget, t5.name, t0.execLevel, t0.hireDate,
> t0.hireTime, t0.hireTimestamp, t7.street, t7.city, t7.state, t7.zip,
> t0.isManager, t0.name, t0.salary, t8.street, t8.city, t8.state, t8.zip FROM
> EmpBean t0 LEFT OUTER JOIN DeptBean t5 ON t0.dept_deptno = t5.deptno LEFT
> OUTER JOIN AddressBean t7 ON t0.home_street = t7.street LEFT OUTER JOIN
> AddressBean t8 ON t0.work_street = t8.street WHERE (t0.salary = (SELECT
> MAX(t1.salary) FROM EmpBean t1 WHERE (t1.salary > ANY(()))))} [code=30000,
> state=42X01]
> s/b
> select q1."EMPID", q1."SALARY", q1."DEPT_DEPTNO" from EMPVO q1 where (
> q1."SALARY" = ( select max( q2."SALARY") from EMPVO q2, DEPTVO q3, EMPVO q4
> where ( q2."SALARY" > ANY ( select q5."SALARY" from EMPVO q5, DEPTVO q6,
> EMPVO q7 where ( q7."EMPID" = q4."EMPID") and ( q6."DEPTNO" =
> q5."DEPT_DEPTNO") and ( q7."EMPID" = q6."MGR_EMPID") ) ) and (
> q3."DEPTNO" = q2."DEPT_DEPTNO") and ( q4."EMPID" = q3."MGR_EMPID") ) )
> [ FAILED 536- bucket = fvtfull, query = select e from EmpBean e where
> (e.salary = (select max(e.salary) from EmpBean e where e.salary > some
> (select f.salary from EmpBean f where f.dept.mgr.empid=e.dept.mgr.empid)) )
> :
> DIFFERENCE-locations based on expected-(
> diff at line 2 position 295 EXPECTED [ ] ACTUAL [e]
>
>
>
>
> e
>
>
>
>
>
>
>
> e
>
>
>
>
> )
> EXPECTED(
> TEST536; select e from EmpBean e where (e.salary = (select max(e.salary)
> from EmpBean e where e.salary > some (select f.salary from EmpBean f where
> f.dept.mgr.empid=e.dept.mgr.empid)) )
>
>
>
>
> e
>
>
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: );E (t1.salary >
> ANY((;<query_expr_body> {prepstmnt 605037584 SELECT t0.empid, t0.bonus,
> t5.deptno, t5.budget, t5.name, t0.execLevel, t0.hireDate, t0.hireTime,
> t0.hireTimestamp, t7.street, t7.city, t7.state, t7.zip, t0.isManager,
> t0.name, t0.salary, t8.street, t8.city, t8.state, t8.zip FROM EmpBean t0 LEFT
> OUTER JOIN DeptBean t5 ON t0.dept_deptno = t5.deptno LEFT OUTER JOIN
> AddressBean t7 ON t0.home_street = t7.street LEFT OUTER JOIN AddressBean t8
> ON t0.work_street = t8.street WHERE (t0.salary = (SELECT MAX(t1.salary) FROM
> EmpBean t1 WHERE (t1.salary > ANY(()))))} [code=-104, state=42601]
> TEST536; 1 tuple)
> ACTUAL(
> TEST536; select e from EmpBean e where (e.salary = (select max(e.salary)
> from EmpBean e where e.salary > some (select f.salary from EmpBean f where
> f.dept.mgr.empid=e.dept.mgr.empid)) )
>
>
>
> e
>
>
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Syntax error: Encountered ")" at line 1, column 504. {SELECT t0.empid,
> t0.bonus, t5.deptno, t5.budget, t5.name, t0.execLevel, t0.hireDate,
> t0.hireTime, t0.hireTimestamp, t7.street, t7.city, t7.state, t7.zip,
> t0.isManager, t0.name, t0.salary, t8.street, t8.city, t8.state, t8.zip FROM
> EmpBean t0 LEFT OUTER JOIN DeptBean t5 ON t0.dept_deptno = t5.deptno LEFT
> OUTER JOIN AddressBean t7 ON t0.home_street = t7.street LEFT OUTER JOIN
> AddressBean t8 ON t0.work_street = t8.street WHERE (t0.salary = (SELECT
> MAX(t1.salary) FROM EmpBean t1 WHERE (t1.salary > ANY(()))))} [code=30000,
> state=42X01]
> TEST536; 1 tuple) ]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
