openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Wisneski (JIRA)" <j...@apache.org>
Subject [jira] Closed: (OPENJPA-45) pushdown sql uses outer join when it should use inner join
Date Thu, 01 Feb 2007 01:23:05 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-45?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

David Wisneski closed OPENJPA-45.
---------------------------------

    Resolution: Duplicate

duplicate of OPENJPA-49

> pushdown sql uses outer join when it should use inner join
> ----------------------------------------------------------
>
>                 Key: OPENJPA-45
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-45
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: query
>         Environment: windows xp, derby, db2
>            Reporter: George Hongell
>         Attachments: failureEntities.jar
>
>
> 13 - uses outer join not inner join
>  TEST13; select $ from EmpBean $, DeptBean _a 
> 5859  TRACE  [main] openjpa.jdbc.SQL - <t 1094861122, conn 295440796> [0 ms] executing
prepstmnt 612246654 SELECT t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel,
t0.hireDate, t0.hireTime, t0.hireTimestamp, t2.street, t2.city, t2.state, t2.zip, t0.isManager,
t0.name, t0.salary, t3.street, t3.city, t3.state, t3.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean
t1 ON t0.dept_deptno = t1.deptno LEFT OUTER JOIN AddressBean t2 ON t0.home_street = t2.street
LEFT OUTER JOIN AddressBean t3 ON t0.work_street = t3.street
> pushdown sql s/b
> select t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel, t0.hireDate,
t0.hireTime, t0.hireTimestamp, t2.street, t2.city, t2.state, t2.zip, t0.isManager, t0.name,
t0.salary, t3.street, t3.city, t3.state, t3.zip FROM EmpBean t0 JOIN DeptBean t1 ON t0.dept_deptno
= t1.deptno LEFT OUTER JOIN AddressBean t2 ON t0.home_street = t2.street LEFT OUTER JOIN AddressBean
t3 ON t0.work_street = t3.street
>   [ FAILED 13- bucket = fvtfull, query = select $ from EmpBean $, DeptBean _a  : 
>    DIFFERENCE-locations based on expected-(
> diff at line 2 position 1 EXPECTED [ ]  ACTUAL [E] 
>                                             $                                       
     
> EmpBean 
> ) 
>    EXPECTED(
>  TEST13; select $ from EmpBean $, DeptBean _a 
> EmpBean 
> ~~~~~~~ 
>   [1]   
>   [2]   
>   [3]   
>   [4]   
>   [5]   
>   [6]   
>   [7]   
>   [8]   
>   [9]   
>  TEST13; 9 tuples) ]
>    ACTUAL(
>  TEST13; select $ from EmpBean $, DeptBean _a 
> EmpBean 
> ~~~~~~~ 
>   [1]   
>   [2]   
>   [3]   
>   [4]   
>   [5]   
>   [6]   
>   [7]   
>   [8]   
>   [9]   
>  [10]   
>  TEST13; 10 tuples) ]
> 83 pushdown uses all left outer joins but last 2 joins should be inner
>  TEST83; select d.name, e.name, p.name from DeptBean d left join d.emps e join e.tasks
p
> bad trace /does not work
> 9234  TRACE  [main] openjpa.jdbc.SQL - <t 1094861122, conn 295440796> [15 ms] executing
prepstmnt 343938176 SELECT t0.name, t1.name, t3.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean
t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN TaskBean_EmpBean t2 ON t1.empid = t2.empid
LEFT OUTER JOIN TaskBean t3 ON t2.tasks_taskid = t3.taskid
> trace s/b
> 9234  TRACE  [main] openjpa.jdbc.SQL - <t 1094861122, conn 295440796> [15 ms] executing
prepstmnt 343938176 SELECT t0.name, t1.name, t3.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean
t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN TaskBean_EmpBean t2 ON t1.empid = t2.emps_empid
LEFT OUTER JOIN TaskBean t3 ON t2.tasks_taskid = t3.taskid
> pushdown sql s/b
> select t0.name, t1.name, t3.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean t1 ON t0.deptno
= t1.dept_deptno  JOIN TaskBean_EmpBean t2 ON t1.empid = t2.emps_empid  JOIN TaskBean t3 ON
t2.tasks_taskid = t3.taskidactual  TEST83; select d.name, e.name, p.name from DeptBean d left
join d.emps e join e.tasks p
>   [ FAILED 83- bucket = fvtfull, query = select d.name, e.name, p.name from DeptBean
d left join d.emps e join e.tasks p : 
>    EXPECTED(
>  TEST83; select d.name, e.name, p.name from DeptBean d left join d.emps e join e.tasks
p
>   d.name      e.name    p.name 
> ~~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~ 
>   Service     ritika     Test  
> Development    david     Code  
> Development    david    Design 
> Development    david    Design  
> Development    harry     Code  
> Development    harry     Test  
> Development   andrew     Code  
>  TEST83; 7 tuples ) 
>    ACTUAL(
>  TEST83; select d.name, e.name, p.name from DeptBean d left join d.emps e join e.tasks
p
>   d.name      e.name    p.name 
> ~~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~ 
>     CEO        ahmad     null  
>     CEO     Tom Rayburn  null  
>    Admin      george     null  
>    Admin      minmei     null  
>    Sales       null      null  
>   Service     ritika     Test  
> Development    david     Code  
> Development    david    Design 
> Development    david    Design  
> Development    harry     Code  
> Development    harry     Test  
> Development   andrew     Code  
> Development  charlene    null  
>  TEST83; 13 tuples) ]
> 85 same as 83 but last join uses the (,in relationship) syntax
>  TEST85; select d.name, e.name, p.name from DeptBean d left join d.emps e, in (e.tasks)
p 
> 9297  TRACE  [main] openjpa.jdbc.SQL - <t 1094861122, conn 295440796> [16 ms] executing
prepstmnt 2016704564 SELECT t0.name, t1.name, t3.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean
t1 ON t0.deptno = t1.dept_deptno LEFT OUTER JOIN TaskBean_EmpBean t2 ON t1.empid = t2.empid
LEFT OUTER JOIN TaskBean t3 ON t2.tasks_taskid = t3.taskid
> pushdown sql s/b
> select t0.name, t1.name, t3.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean t1 ON t0.deptno
= t1.dept_deptno LEFT OUTER JOIN TaskBean_EmpBean t2 ON t1.empid = t2.emps_empid JOIN TaskBean
t3 ON t2.tasks_taskid = t3.taskid
>   [ FAILED 85- bucket = fvtfull, query = select d.name, e.name, p.name from DeptBean
d left join d.emps e, in (e.tasks) p  : 
>    EXPECTED(
>  TEST85; select d.name, e.name, p.name from DeptBean d left join d.emps e, in (e.tasks)
p 
>   d.name      e.name    p.name 
> ~~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~ 
>   Service     ritika     Test  
> Development    david     Code  
> Development    david    Design 
> Development    david    Design  
> Development    harry     Code  
> Development    harry     Test  
> Development   andrew     Code  
>  TEST85; 7 tuples ) 
>    ACTUAL(
>  TEST85; select d.name, e.name, p.name from DeptBean d left join d.emps e, in (e.tasks)
p 
>   d.name      e.name    p.name 
> ~~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~ 
>     CEO        ahmad     null  
>     CEO     Tom Rayburn  null  
>    Admin      george     null  
>    Admin      minmei     null  
>    Sales       null      null  
>   Service     ritika     Test  
> Development    david     Code  
> Development    david    Design 
> Development    david    Design  
> Development    harry     Code  
> Development    harry     Test  
> Development   andrew     Code  
> Development  charlene    null  
>  TEST85; 13 tuples) ]

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message