JPQL comparasion between MAX(Long) and null

Question

I am trying to select an entity A with a Long value b and a bunch of other values c. I want to get the A with the largest b value or if no such value exists fall back to getting the A where b is null.

It seems to me that MAX(x) would return null for a Long datatype according to this doc so I was hoping to do this:

SELECT entity FROM A entity
WHERE entity.b = (SELECT MAX(a.b) FROM A a)
AND entity.c = :c

This works well when b has a defined Long value. When b is null however, I get not results. It seems the = operator cannot compare NULL values.

I tried using IS NULL in an OR clause but I only want the entity with the null value if the MAX result is null and the following query returns both the max Long value and the null value:

SELECT entity FROM A entity
WHERE (
           (entity.b = (SELECT MAX(a.b) FROM A a))
           (OR entity.b IS NULL)
      )
AND entity.c = :c

How can I select my entity that has the largest value b or if no b is set, get the entity where b is null?

span span · Accepted Answer · 2016-10-13T14:05:34

NULLS FIRST|LAST can be used to order the result and then limit the result to 1. Then there is no need to use MAX(x).

Since NULL always comes first and NULLS LAST is not supported in my environment I have worked around it using a query like this:

SELECT entity FROM A entity
WHERE entity.b = (SELECT MAX(a.b) FROM A a)
AND entity.c = :c
ORDER BY entity.b ASC

And some code:

try {   
    ...
    List<A> as = q.getResultList();
    result = as.get(as.size() - 1);
catch (NoResultException e) {
    // doo something
}

Still very interested in an answer that uses JSQL comparasion though!

JPQL comparasion between MAX(Long) and null

1 Answers