OR not working with isNull

#1

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.

#2

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