Description J LM 2012-12-04 12:11:41 EST

Created attachment 224270 [details]
Maven project with JPA Model, and JUnit tests to reproduce the issue

Assume I have a model with an Entity E1 with 2 onetoone on another Entity E2.
E2 is an abstract class (@Inheritance) extended by 2 Entites E21 and E22. They use a discriminator column. This case is a design choice and doesn't sound too messy (to me ).

When retrieving an E1 object (find by id, select in jpql or criteria), the two onetoone objects are retrieved and values of their fields are correct.

As it triggers 3 sql queries, I want to optimize by join fetching the 2 onetoone : tried with jpql, criteria and JoinFetch annotation.

Now one of the two retrieved E2 objects (whatever their subtypes) have incorrect fields : either the two are the same, either one of them is null.

The sql generated contains distinct fields for each entity but values retrieved are badly mapped to the objects.

Only workaround I see, would be to demultiply a onetoone on the abstract class to multiple onetoeones to concrete subclasses. Not really a good solution.

In the attachment, I have placed a project based on maven (pom.xml).
You will find :
in src/main/java/, the classes of the JPA model : sample with a Customer having two addresses that can be sub-typed.
In src/test/java, you will find 3 classes (DBCreateEL, InsertEL, ReadEL).
In the stderr log, when launching ReadEL, you will see the values retrieved and incorrectly setted with join fetch cases (JPQL and Criteria).

Find with ID / Select JPQL / NO FETCHES CRITERIA :
--------------------------------------------------
The SQL :
SELECT CUS_ID, CUS_ACCOUNT, ADD_ID_BA, ADD_ID_DA FROM PRO_T_CUSTOMER WHERE (CUS_ID = ?)
SELECT ADD_ID, ADD_TYPE, STREET1, STREET2 FROM PRO_T_ADDRESS WHERE (ADD_ID = ?)
SELECT ADD_ID, ADD_TYPE, STREET1, STREET2 FROM PRO_T_ADDRESS WHERE (ADD_ID = ?)

The objects :
Customer [51,ACCNUMBER,
AddressType1 [streetT1],
AddressType2 [streetT2]]

WITH FETCHES JPQL :
-------------------
The SQL :
SELECT t1.CUS_ID, t1.CUS_ACCOUNT, t1.ADD_ID_BA, t1.ADD_ID_DA, t0.ADD_ID, t0.ADD_TYPE, t0.STREET1, t0.STREET2, t2.ADD_ID, t2.ADD_TYPE, t2.STREET1, t2.STREET2 FROM PRO_T_CUSTOMER t1 LEFT OUTER JOIN PRO_T_ADDRESS t0 ON (t0.ADD_ID = t1.ADD_ID_DA) LEFT OUTER JOIN PRO_T_ADDRESS t2 ON (t2.ADD_ID = t1.ADD_ID_BA) WHERE (t1.CUS_ID = ?)

The objects :
Customer [51,ACCNUMBER,
AddressType1 [null],
AddressType2 [streetT2]]

WITH FETCHES CRITERIA :
-----------------------
The SQL :
SELECT DISTINCT t1.CUS_ID, t1.CUS_ACCOUNT, t1.ADD_ID_BA, t1.ADD_ID_DA, t0.ADD_ID, t0.ADD_TYPE, t0.STREET1, t0.STREET2, t2.ADD_ID, t2.ADD_TYPE, t2.STREET1, t2.STREET2 FROM PRO_T_CUSTOMER t1 LEFT OUTER JOIN PRO_T_ADDRESS t0 ON (t0.ADD_ID = t1.ADD_ID_BA) LEFT OUTER JOIN PRO_T_ADDRESS t2 ON (t2.ADD_ID = t1.ADD_ID_DA) WHERE (t1.CUS_ID = ?)

The objects :
Customer [51,ACCNUMBER,
AddressType1 [streetT1],
AddressType2 [null]]

In the two last cases, values of addresses are incorrect.
We can see that the order of join declaration affects data mapping. As we inversed the order in the two cases (I think code is clearer than me ;-) ). 


Note :
The bug exists with 2.4.0, but I have placed the version 2.3.3 as I need a patch based on 2.3.x branch (for compatibility with toplink grid 12c).