JPA: Left join not working with “is null” in where

问题:

I am using EclipseLink (2.4, 2.5 and 2.6) on a very simple project where I have a Department entity and each Department links to an Employee entity which is the manager of the Department.

I am currently unable to make this simple query work:

select d from Department d where d.manager is null

Returns 1 row

select d from Department d left join fetch d.manager where d.manager is null

Returns 0 row

I am using Eclipselink over an H2 database. The SQL query generated does not seem to create a left join but rather an inner join which obviously will fail.

SELECT t1.ID, t1.MANAGER_ID, t0.ID, t0.NAME FROM EMPLOYEE t0, DEPARTMENT t1
WHERE ((t1.MANAGER_ID IS NULL) AND (t0.ID = t1.MANAGER_ID))

Is this a bug or is it something wanted? Or could someone help me fixing this?

Happy to provide the code and example if anyone wants it, or more information.

回答1:

It seems unsafe to reference an entity that was returned in a query as a side effect by using a FETCH JOIN. The JPA specification tries to prohibit such queries at least for multi-valued associations (JSR 338, section 4.4.5.3):

It is not permitted to specify an identification variable for the objects referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched entities or elements cannot appear elsewhere in the query.

Your query executed on EclipseLink and Hibernate yields different results (EclipseLink 2.6.3: no results, Hibernate 4.3.11: all departments with no manager):

select d from Department d left join fetch d.manager where d.manager is null

To solve your problem a subquery could be used. A portable JPQL query could look like this (same results in EclipseLink and Hibernate):

select d1 from Department d1 left join fetch d1.manager where exists 
(select d2 from Department d2 where d2.manager is null and d1 = d2)