Details
Major Description
Each of the following queries, when run by itself in a new persistence context where EmpBean and DeptBean are not populated,
is showing null values for DeptBean and EmpBean respectively which should be impossible for an inner join.
Outer join queries have similar results.
The DataBase is populated.
Whether or not the query is run in a transaction context does not matter.
select d,e from DeptBean d join d.emps e where e.bonus<100.02 or e.name='name2'
select d,e from EmpBean e join e.dept d where e.bonus<100.02 or e.name='name6'
TEST1; select d,e from DeptBean d join d.emps e where e.bonus<100.02 or e.name='name2'
DeptBean EmpBean
~~~~~~~~ ~~~~~~~
null [1]
null [3]
null [5]
null [6]
[100] [8]
[200] [4]
[210] [2]
[210] [7]
[210] [9]
TEST1; 9 tuples
TEST1; select d,e from EmpBean e join e.dept d where e.bonus<100.02 or e.name='name6'
DeptBean EmpBean
~~~~~~~~ ~~~~~~~
[100] [8]
[100] null
[200] [3]
[200] null
[210] null
[210] null
[210] null
[210] null
[220] null
TEST1; 9 tuples
Furthermore, if either of these queries is run twice in a new persistence context,
the second query gets a Cannot load object with id "1" error.
ACTUAL(
TEST2; select d,e from EmpBean e join e.dept d where e.bonus<100.02 or e.name='name6'
d
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Cannot load object with id "1". Instance "com.ibm.ws.query.entities.objectgrid.annotated.DeptBean-1" with the same id already exists in the L1 cache. This can occur when you assign an existing id to a new instance, and before flushing attempt to load the existing instance for that id.
TEST2; 1 tuple
However, if
select d,e from DeptBean d join d.emps e where e.bonus<100.02 or e.name='name2'
is proceeded by
select d from DeptBean d
it gets the correct result.
and if select d,e from EmpBean e join e.dept d where e.bonus<100.02 or e.name='name6'
is proceeded by
select e from EmpBean e
it gets the correct result.
should be
TEST2; select d,e from EmpBean e join e.dept d where e.bonus<100.02 or e.name='name6'
DeptBean EmpBean
~~~~~~~~ ~~~~~~~
[100] [6]
[100] [8]
[200] [3]
[200] [4]
[210] [1]
[210] [2]
[210] [7]
[210] [9]
[220] [5]
TEST2; 9 tuples)
TEST2; select d,e from DeptBean d join d.emps e where e.bonus<100.02 or e.name='name2'
DeptBean EmpBean
~~~~~~~~ ~~~~~~~
[100] [6]
[100] [8]
[200] [3]
[200] [4]
[210] [1]
[210] [2]
[210] [7]
[210] [9]
[220] [5]
TEST2; 9 tuples)
the database shows
select t0.empid, t0.dept_deptno, t0.name, t0.bonus, t0.home_street, t0.work_street FROM EmpBean t0
EMPID :DEPT_DEPTNO :NAME :BONUS :HOME_STREET :WORK_STREET :
1 :210 :david :0.0 :1780 Mercury Way :555 Silicon Valley Drive :
2 :210 :andrew :0.0 :1780 Mercury Way :555 Silicon Valley Drive :
3 :200 :minmei :0.0 :1780 Mercury Way :555 Silicon Valley Drive :
4 :200 :george :0.0 :512 Venus Drive :555 Silicon Valley Drive :
5 :220 :ritika :0.0 :12440 Vulcan Avenue :555 Silicon Valley Drive :
6 :100 :ahmad :0.0 :4983 Plutonium Avenue :4983 Plutonium Avenue :
7 :210 :charlene :0.0 :182 Martian Street :555 Silicon Valley Drive :
8 :100 :Tom Rayburn :0.0 :6200 Vegas Drive :555 Silicon Valley Drive :
9 :210 :harry :0.0 :150 North First Apt E1 :8900 Jupiter Park :
10 : SQL_NULL :Catalina Wei :0.0 : SQL_NULL :555 Silicon Valley Drive :
select * from deptbean
DEPTNO :BUDGET :NAME :MGR_EMPID :REPORTSTO_DEPTNO :
210 :2.1 :Development :3 :200 :
220 :2.1 :Service :4 :200 :
300 :2.1 :Sales :6 :100 :
200 :2.1 :Admin :8 :100 :
100 :2.1 :CEO :10 :100 :
simple ejb queries are always correct
TEST1; select e from EmpBean e
EmpBean
~~~~~~~
[1]
[2]
[3]
[4]
[5]
[6]
[7]
[8]
[9]
[10]
TEST1; 10 tuples)
TEST1; select d from DeptBean d
DeptBean
~~~~~~~~
[100]
[200]
[210]
[220]
[300]
TEST1; 5 tuples)
the trace statements
for select d,e from DeptBean d join d.emps e where e.bonus<100.02 or e.name='name2'
359 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 615523504 SELECT t0.deptno, t1.empid, t1.bonus, t1.dept_deptno, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.home_street, t1.isManager, t1.name, t1.salary, t1.work_street FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.deptno = t1.dept_deptno WHERE ((CAST(t1.bonus AS DOUBLE) < ? OR t1.name = ?)) [params=(BigDecimal) 100.02, (String) name2]
406 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 380245674 SELECT 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 WHERE t0.empid = ? [params=(int) 100]
469 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1021328608 SELECT t0.budget, t0.name FROM DeptBean t0 WHERE t0.deptno = ? [params=(int) 100]
484 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1420580012 SELECT t0.city, t0.state, t0.zip FROM AddressBean t0 WHERE t0.street = ? [params=(String) 4983 Plutonium Avenue]
562 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 799027104 SELECT t1.empid, t1.bonus, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.isManager, t1.name, t1.salary FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.mgr_empid = t1.empid WHERE t0.deptno = ? [params=(int) 100]
609 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 931018622 SELECT t1.deptno, t1.budget, t1.name FROM DeptBean t0 INNER JOIN DeptBean t1 ON t0.reportsTo_deptno = t1.deptno WHERE t0.deptno = ? [params=(int) 100]
625 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1703830926 SELECT t0.city, t0.state, t0.zip FROM AddressBean t0 WHERE t0.street = ? [params=(String) 6200 Vegas Drive]
625 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 494935424 SELECT t0.city, t0.state, t0.zip FROM AddressBean t0 WHERE t0.street = ? [params=(String) 555 Silicon Valley Drive]
625 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1753245824 SELECT 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 WHERE t0.empid = ? [params=(int) 200]
640 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1718773362 SELECT t0.budget, t0.name FROM DeptBean t0 WHERE t0.deptno = ? [params=(int) 200]
656 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 252841746 SELECT t0.city, t0.state, t0.zip FROM AddressBean t0 WHERE t0.street = ? [params=(String) 1780 Mercury Way]
656 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1000749990 SELECT t1.empid, t1.bonus, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.isManager, t1.name, t1.salary FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.mgr_empid = t1.empid WHERE t0.deptno = ? [params=(int) 200]
656 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 2040035736 SELECT t1.deptno, t1.budget, t1.name FROM DeptBean t0 INNER JOIN DeptBean t1 ON t0.reportsTo_deptno = t1.deptno WHERE t0.deptno = ? [params=(int) 200]
672 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 441981528 SELECT t0.city, t0.state, t0.zip FROM AddressBean t0 WHERE t0.street = ? [params=(String) 512 Venus Drive]
672 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1135756210 SELECT 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 WHERE t0.empid = ? [params=(int) 210]
687 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1956672672 SELECT t0.budget, t0.name FROM DeptBean t0 WHERE t0.deptno = ? [params=(int) 210]
687 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 497163682 SELECT t1.empid, t1.bonus, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.isManager, t1.name, t1.salary FROM DeptBean t0 INNER JOIN EmpBean t1 ON t0.mgr_empid = t1.empid WHERE t0.deptno = ? [params=(int) 210]
734 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1160398122 SELECT t1.deptno, t1.budget, t1.name FROM DeptBean t0 INNER JOIN DeptBean t1 ON t0.reportsTo_deptno = t1.deptno WHERE t0.deptno = ? [params=(int) 210]
734 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1948939306 SELECT t0.city, t0.state, t0.zip FROM AddressBean t0 WHERE t0.street = ? [params=(String) 182 Martian Street]
750 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1117667998 SELECT t0.city, t0.state, t0.zip FROM AddressBean t0 WHERE t0.street = ? [params=(String) 150 North First Apt E1]
750 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1403016096 SELECT t0.city, t0.state, t0.zip FROM AddressBean t0 WHERE t0.street = ? [params=(String) 8900 Jupiter Park]
765 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 1997174538 SELECT 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 WHERE t0.empid = ? [params=(int) 220]
765 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 658908998 SELECT t0.budget, t0.name FROM DeptBean t0 WHERE t0.deptno = ? [params=(int) 220]
765 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1279413314> [0 ms] executing prepstmnt 947140724 SELECT t0.city, t0.state, t0.zip FROM AddressBean t0 WHERE t0.street = ? [params=(String) 12440 Vulcan Avenue]
the trace statements
for select d,e from EmpBean e join e.dept e where e.bonus<100.02 or e.name='name2'
312 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 784346816 SELECT t1.deptno, t1.budget, t1.mgr_empid, t1.name, t1.reportsTo_deptno, t0.empid FROM EmpBean t0 INNER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE ((CAST(t0.bonus AS DOUBLE) < ? OR t0.name = ?)) [params=(BigDecimal) 100.02, (String) name6]
343 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 1902276962 SELECT t0.bonus, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t0.isManager, t0.name, t0.salary FROM EmpBean t0 WHERE t0.empid = ? [params=(int) 3]
359 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 692463942 SELECT t0.budget, t0.name FROM DeptBean t0 WHERE t0.deptno = ? [params=(int) 200]
375 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 288493874 SELECT t0.budget, t1.empid, t1.bonus, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.isManager, t1.name, t1.salary, t0.name, t2.deptno, t2.budget, t2.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN DeptBean t2 ON t0.reportsTo_deptno = t2.deptno WHERE t0.deptno = ? [params=(int) 1]
375 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 1962964224 SELECT t0.budget, t1.empid, t1.bonus, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.isManager, t1.name, t1.salary, t0.name, t2.deptno, t2.budget, t2.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN DeptBean t2 ON t0.reportsTo_deptno = t2.deptno WHERE t0.deptno = ? [params=(int) 2]
375 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 1125139216 SELECT t0.bonus, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t0.isManager, t0.name, t0.salary FROM EmpBean t0 WHERE t0.empid = ? [params=(int) 8]
375 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 1682728012 SELECT t0.budget, t0.name FROM DeptBean t0 WHERE t0.deptno = ? [params=(int) 100]
390 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 485498096 SELECT t1.deptno, t1.budget, t1.name FROM EmpBean t0 INNER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE t0.empid = ? [params=(int) 3]
437 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 495459720 SELECT t1.street, t1.city, t1.state, t1.zip FROM EmpBean t0 INNER JOIN AddressBean t1 ON t0.home_street = t1.street WHERE t0.empid = ? [params=(int) 3]
468 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 1370640818 SELECT t1.street, t1.city, t1.state, t1.zip FROM EmpBean t0 INNER JOIN AddressBean t1 ON t0.work_street = t1.street WHERE t0.empid = ? [params=(int) 3]
484 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 492576092 SELECT t0.budget, t1.empid, t1.bonus, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.isManager, t1.name, t1.salary, t0.name, t2.deptno, t2.budget, t2.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN DeptBean t2 ON t0.reportsTo_deptno = t2.deptno WHERE t0.deptno = ? [params=(int) 4]
500 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 1356091604 SELECT t0.bonus, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t0.isManager, t0.name, t0.salary FROM EmpBean t0 WHERE t0.empid = ? [params=(int) 4]
500 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 1002191804 SELECT t0.budget, t1.empid, t1.bonus, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.isManager, t1.name, t1.salary, t0.name, t2.deptno, t2.budget, t2.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN DeptBean t2 ON t0.reportsTo_deptno = t2.deptno WHERE t0.deptno = ? [params=(int) 5]
515 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 1076117540 SELECT t0.bonus, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t0.isManager, t0.name, t0.salary FROM EmpBean t0 WHERE t0.empid = ? [params=(int) 10]
515 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 104728126 SELECT t0.budget, t1.empid, t1.bonus, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.isManager, t1.name, t1.salary, t0.name, t2.deptno, t2.budget, t2.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN DeptBean t2 ON t0.reportsTo_deptno = t2.deptno WHERE t0.deptno = ? [params=(int) 6]
531 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [16 ms] executing prepstmnt 591274814 SELECT t0.budget, t1.empid, t1.bonus, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.isManager, t1.name, t1.salary, t0.name, t2.deptno, t2.budget, t2.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN DeptBean t2 ON t0.reportsTo_deptno = t2.deptno WHERE t0.deptno = ? [params=(int) 7]
531 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 2091941040 SELECT t1.deptno, t1.budget, t1.name FROM EmpBean t0 INNER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE t0.empid = ? [params=(int) 8]
531 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 747777170 SELECT t1.street, t1.city, t1.state, t1.zip FROM EmpBean t0 INNER JOIN AddressBean t1 ON t0.home_street = t1.street WHERE t0.empid = ? [params=(int) 8]
531 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 1392268028 SELECT t1.street, t1.city, t1.state, t1.zip FROM EmpBean t0 INNER JOIN AddressBean t1 ON t0.work_street = t1.street WHERE t0.empid = ? [params=(int) 8]
546 TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1164199268> [0 ms] executing prepstmnt 335680514 SELECT t0.budget, t1.empid, t1.bonus, t1.execLevel, t1.hireDate, t1.hireTime, t1.hireTimestamp, t1.isManager, t1.name, t1.salary, t0.name, t2.deptno, t2.budget, t2.name FROM DeptBean t0 LEFT OUTER JOIN EmpBean t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN DeptBean t2 ON t0.reportsTo_deptno = t2.deptno WHERE t0.deptno = ? [params=(int) 9]