[
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.
