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] Commented: (OPENJPA-54) bad sql pushdown, should use all syntax
Date Wed, 20 Sep 2006 01:31:22 GMT
    [ http://issues.apache.org/jira/browse/OPENJPA-54?page=comments#action_12436081 ] 
            
George Hongell commented on OPENJPA-54:
---------------------------------------

removed testcase references.
run on version 443432.

EJBQL:
select e from EmpBean e where e.empid not in (select e.no from DeptBean e) 
 
OPENJPA ERROR OR SQL PUSHDOWN:
Scalar subquery is only allowed to return a single row. {prepstmnt 241176160 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 (NOT (t0.empid = (SELECT t1.deptno FROM DeptBean t1))
AND t0.empid IS NOT NULL)} [code=30000, state=21000] 
 
SUGGESTED SQL PUSHDOWN:
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 (NOT (t0.empid = ALL (select t1.deptno FROM DeptBean
t1)) AND t0.empid IS NOT NULL)



> bad sql pushdown, should use all syntax
> ---------------------------------------
>
>                 Key: OPENJPA-54
>                 URL: http://issues.apache.org/jira/browse/OPENJPA-54
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: query
>         Environment: Windows xp, derby, db2
>            Reporter: George Hongell
>         Attachments: failureEntities.jar
>
>
> 454 bad sql pushdown, should use all syntax
>  TEST454; select e from EmpBean e where e.empid not in (select e.no from DeptBean e)

> Scalar subquery is only allowed to return a single row. {prepstmnt 241176160 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 (NOT (t0.empid = (SELECT t1.deptno FROM DeptBean t1))
AND t0.empid IS NOT NULL)} [code=30000, state=21000] 
> s/b
> select   t0.empid from EMPBean t0 where  ( t0.empid  <> ALL  ( select  t2.deptno
from DEPTbean t2) ) 
>   [ FAILED 454- bucket = fvtfull, query = select e from EmpBean e where e.empid not in
(select e.no from DeptBean e)  : 
>    DIFFERENCE-locations based on expected-(
> diff at line 2 position 2 EXPECTED [T]  ACTUAL [ ] 
>  TEST454; 0 tuples 
>                                                                                     
                                                                                         
                                                                                         
                                       e                                                 
                                                                                         
                                                                                         
                                                                            
> ) 
>    EXPECTED(
>  TEST454; select e from EmpBean e where e.empid not in (select e.no from DeptBean e)

>  TEST454; 0 tuples ) 
>    ACTUAL(
>  TEST454; select e from EmpBean e where e.empid not in (select e.no from DeptBean e)

>                                                                                     
                                                                                         
                                                                                         
                                       e                                                 
                                                                                         
                                                                                         
                                                                            
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> Scalar subquery is only allowed to return a single row. {prepstmnt 241176160 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 (NOT (t0.empid = (SELECT t1.deptno FROM DeptBean t1))
AND t0.empid IS NOT NULL)} [code=30000, state=21000] 
>  TEST454; 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