JPA 2 Criteria API: why is isNull being ignored when in conjunction with equal?

Question

I have the following entity class (ID inherited from PersistentObjectSupport class):

@Entity
public class AmbulanceDeactivation extends PersistentObjectSupport implements Serializable {
    private static final long serialVersionUID = 1L;

    @Temporal(TemporalType.DATE) @NotNull
    private Date beginDate;

    @Temporal(TemporalType.DATE)
    private Date endDate;

    @Size(max = 250)
    private String reason;

    @ManyToOne @NotNull
    private Ambulance ambulance;

    /* Get/set methods, etc. */
}

If I do the following query using the Criteria API:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<AmbulanceDeactivation> cq = cb.createQuery(AmbulanceDeactivation.class);
Root<AmbulanceDeactivation> root = cq.from(AmbulanceDeactivation.class);
EntityType<AmbulanceDeactivation> model = root.getModel();
cq.where(cb.isNull(root.get(model.getSingularAttribute("endDate", Date.class))));
return em.createQuery(cq).getResultList();

I get the following SQL printed in the log:

FINE: SELECT ID, REASON, ENDDATE, UUID, BEGINDATE, VERSION, AMBULANCE_ID FROM AMBULANCEDEACTIVATION WHERE (ENDDATE IS NULL)

However, if I change the where() line in the previous code to this one:

cq.where(cb.isNull(root.get(model.getSingularAttribute("endDate", Date.class))),
    cb.equal(root.get(model.getSingularAttribute("ambulance", Ambulance.class)), ambulance));

I get the following SQL:

FINE: SELECT ID, REASON, ENDDATE, UUID, BEGINDATE, VERSION, AMBULANCE_ID FROM AMBULANCEDEACTIVATION WHERE (AMBULANCE_ID = ?)

That is, the isNull criterion is totally ignored. It is as if it wasn't even there (if I provide only the equal criterion to the where() method I get the same SQL printed).

Why is that? Is it a bug or am I missing something?

Answer 1

I tested your code and criteria query with EclipseLink (you're using EclipseLink, right?) and I reproduced the behavior: the isNull part is just ignored.

However, with Hibernate Entity Manager 3.5.1, the following query is generated:

select ambulanced0_.id as id7_, ambulanced0_.ambulance_id as ambulance5_7_, ambulanced0_.beginDate as beginDate7_, ambulanced0_.endDate as endDate7_, ambulanced0_.reason as reason7_ 
from AmbulanceDeactivation ambulanced0_ 
where (ambulanced0_.endDate is null) and ambulanced0_.ambulance_id=?

Which is the expected result. So I guess that we can assume this to be a bug of your JPA 2.0 provider.