Wrong SQL for optional OneToOne relations in combination with discriminator columns

[Georg Nozicka <ge...@nozicka.at>]

We think OpenJpa generates a wrong SQL for optional 1:1 relations in
combination with discriminator columns.

Imagine the following entities: A Company with an optional 1:1 to
AddressLocation and another optional 1:1 to AddressPostal. AddressLocation
and AddressPostal are two different kind of addresses. They are both
subclasses of Address. I added the relevant entities in a zip file.
http://openjpa.208410.n2.nabble.com/file/n7432717/Sample.zip Sample.zip 

The goal is to retrieve a company by id and also its optional
AddressLocation and AddressPostal.
Imagine that we have for a specific company only an AddressLocation for the
company, no AddressPostal.

The retrieval works as follows:

OpenJPAEntityManager oem = OpenJPAPersistence.cast(getEm());
FetchPlan fp = oem.getFetchPlan();
fp.addField(CompanySimple.class, "addressesLocation");
fp.addField(CompanySimple.class, "addressesPostal");

CompanySimple cs1 = getEm().find(CompanySimple.class, new Long(1L));

OpenJpa generates the following query (I substituted the values for you).

SELECT t1.id, t1.kind, t1.street, t1.compoid, t1.localAttr, t2.id, 
        t2.kind, t2.street, t2.compoid, t2.postalAttr, t0.name 
    FROM CompanySimple t0 LEFT OUTER JOIN AddressSimple t1 ON t0.id = 
        t1.compoid LEFT OUTER JOIN AddressSimple t2 ON t0.id = 
        t2.compoid 
    WHERE (t1.kind IS NULL OR t1.kind IN ('AL')) AND (t2.kind IS NULL OR 
        t2.kind IN ('AP')) AND t0.id = 1 

For the case that a company has only one address (i.e. AddressLocation), the
generated sql does not retrieve the company for the following reasons.
The left part of the where clause is intended for AddressLocation, the right
part for AddressPostal. Unluckily there exists only AddressLocation,
therefore t2.kind is not null so it comes to a comparison of t2.kind with
'AP' which is false because the left join to t2 is only done by compoid and
the retrieved entity is AddressLocation with kind equals 'AL'. 

In our opinion the failure occurs because the join criteria takes only the
id into account, but when using inheritance and single table strategy also
the discriminator column must be taken into account. The query would work,
if it would look like that:

SELECT t1.id, t1.kind, t1.street, t1.compoid, t1.localAttr, t2.id, 
        t2.kind, t2.street, t2.compoid, t2.postalAttr, t0.name 
    FROM CompanySimple t0 LEFT OUTER JOIN AddressSimple t1 ON t0.id = 
        t1.compoid and t1.kind = 'AL' LEFT OUTER JOIN AddressSimple t2 ON
t0.id = 
        t2.compoid and t2.kind = 'AP' 
    WHERE  t0.id = 1 

Are we doing here something wrong or is there another way to solve our goal
or is this a failure of the OpenJpa implementation?

Georg

--
View this message in context: http://openjpa.208410.n2.nabble.com/Wrong-SQL-for-optional-OneToOne-relations-in-combination-with-discriminator-columns-tp7432717p7432717.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.