OR not working with isNull

#1
bhamilton
bhamilton
Joined on 2018‑11‑05

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
support
support
Joined on 2010‑05‑03

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

Reply