You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "George Hongell (JIRA)" <ji...@apache.org> on 2006/09/19 01:55:22 UTC
[jira] Created: (OPENJPA-45) pushdown sql uses outer join when it
should use inner join
pushdown sql uses outer join when it should use inner join
----------------------------------------------------------
Key: OPENJPA-45
URL: http://issues.apache.org/jira/browse/OPENJPA-45
Project: OpenJPA
Issue Type: Bug
Components: query
Environment: windows xp, derby, db2
Reporter: George Hongell
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.
-
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
[jira] Closed: (OPENJPA-45) pushdown sql uses outer join when it
should use inner join
Posted by "David Wisneski (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (OPENJPA-45) pushdown sql uses outer join when it
should use inner join
Posted by "George Hongell (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OPENJPA-45?page=all ]
George Hongell updated OPENJPA-45:
----------------------------------
Attachment: failureEntities.jar
entity java files and derby ddl script to use to recreate this problem
> pushdown sql uses outer join when it should use inner join
> ----------------------------------------------------------
>
> Key: OPENJPA-45
> URL: http://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.
-
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
[jira] Commented: (OPENJPA-45) pushdown sql uses outer join when it
should use inner join
Posted by "George Hongell (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OPENJPA-45?page=comments#action_12436032 ]
George Hongell commented on OPENJPA-45:
---------------------------------------
EJBQL:
select $ from EmpBean $, DeptBean _a
OPENJPA ERROR OR SQL PUSHDOWN:
returns 10 entities not 9
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
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
SUGGESTED SQL PUSHDOWN:
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
> pushdown sql uses outer join when it should use inner join
> ----------------------------------------------------------
>
> Key: OPENJPA-45
> URL: http://issues.apache.org/jira/browse/OPENJPA-45
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Environment: windows xp, derby, db2
> Reporter: George Hongell
>
> 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.
-
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