OR not working with isNull


Given the following:

Predicate isOwnedByUser = cb.equal(root.get("owner").get("id"), userId);
Predicate ownerIsNull = cb.isNull(root.get("owner"));
Predicate ownerMatchesOrIsNull = cb.or(ownerIsNull, isOwnedByUser);

The isNull predicate will only match records when NOT inside the OR predicate.  The other predicate works normally within or outside the OR.


Your query is probably converted to something such as:

SELECT e FROM MyEntity e JOIN e.owner o
WHERE o IS NULL OR o.id = :userId

Notice that the navigation root.get("owner").get("id") is implemented as INNER JOIN. This is a requirement of the JPA specification, which is known to cause some confusion.

You have to use LEFT OUTER JOIN explicitly to cover entities with a null owner.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)

Post Reply

To post a reply and/or subscribe to update notifications - please login