openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "George Hongell (JIRA)" <j...@apache.org>
Subject [jira] Updated: (OPENJPA-53) bad sql pushdown for nested subselects, missing nested subselect
Date Wed, 20 Sep 2006 01:52:23 GMT
     [ http://issues.apache.org/jira/browse/OPENJPA-53?page=all ]

George Hongell updated OPENJPA-53:
----------------------------------

    Attachment: failureEntities.jar

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

>  bad sql pushdown for nested subselects, missing nested subselect
> -----------------------------------------------------------------
>
>                 Key: OPENJPA-53
>                 URL: http://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.
-
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

       

Mime
View raw message