We have the following relationship in our model
Equipment has 1:1 relationship with EquipmentHolder. In the current database we have an Equipment with entityId 9000001 which does NOT have any EquipmentHolder related.
Our Query is to retrieve EquipmentHolders of an Equipment with JPQL
The JPQL query for this is
select o.equipmentHolders from Equipment o where o.entityId = 9000001
Here is the code we are using to execute the above query
javax.persistence.EntityManager em = PersistenceHelper.makePersistenceManager().getPersistenceManager();
javax.persistence.Query query = em.createQuery("select o.equipmentHolders from Equipment o where o.entityId = 9000001");
Collection result = query.getResultList();
The corresponding SQL generated by EL is
SELECT t0.ENTITYID, t0.ENTITYCLASS, t0.ACTIVITY, t0.ADMINSTATE, t0.CREATEDDATE, t0.CREATEDUSER, t0.DESCRIPTION, t0.ENDDATE, t0.ENTITYVERSION, t0.ID, t0.LASTMODIFIEDDATE, t0.LASTMODIFIEDUSER, t0.NAME, t0.NATIVEEMSNAME, t0.NOSPEC, t0.OBJECTSTATE, t0.OWNER, t0.PARTITION, t0.PERMISSIONS, t0.PHYSICALLOCATION, t0.SERIALNUMBER, t0.STARTDATE, t0.EQUIPMENT, t0.CURRENTASSIGNMENT, t0.SPECIFICATION, t0.E_EQUIPMENTHOLDERS_ORDER FROM Equipment t1 LEFT OUTER JOIN EquipmentHolder t0 ON (t0.EQUIPMENT = t1.ENTITYID) WHERE ((t1.ENTITYID = ?) AND (t1.ENTITYCLASS = ?))
bind => 9000001, EquipmentDAO
In 2.1.3 the generated SQL used to be like this
SELECT t0.ENTITYID, t0.ENTITYCLASS, t0.ACTIVITY, t0.ADMINSTATE, t0.CREATEDDATE, t0.CREATEDUSER, t0.DESCRIPTION, t0.ENDDATE, t0.ENTITYVERSION, t0.ID, t0.LASTMODIFIEDDATE, t0.LASTMODIFIEDUSER, t0.NAME, t0.NATIVEEMSNAME, t0.NOSPEC, t0.OBJECTSTATE, t0.OWNER, t0.PARTITION, t0.PERMISSIONS, t0.PHYSICALLOCATION, t0.SERIALNUMBER, t0.STARTDATE, t0.EQUIPMENT, t0.CURRENTASSIGNMENT, t0.SPECIFICATION, t0.E_EQUIPMENTHOLDERS_ORDER FROM EquipmentHolder t0, Equipment t1 WHERE (((t1.ENTITYID = ?) AND (t1.ENTITYCLASS = ?)) AND ((t0.EQUIPMENT = t1.ENTITYID) AND (t0.ENTITYCLASS = ?)))
Please note that highlighted LEFT OUTER JOIN added newly in 2.5.2 release.
As a result of this, we are now getting a Collection with size 1 (where as in 2.3.1 we used to get empty collection) and when we try to iterate the collection we do NOT get any elements (null row).
Is there a change in EL behavior as a result of new JPA Spec or could this be a bug?
Are there any workarounds/alternate solutions to this problem?
Thanks,
Rama