JPQL query with input parameter collection containing null

Question

I need to compare a nullable entity property via IN expression within the following JPQL query:

@NamedQuery(name = "query",
        query = "SELECT e FROM MyEntity e WHERE e.status IN :statuses")

Now, I like the shown collection-valued input parameter statuses to optionally contain null as an element:

final List<MyEntity> actual = entityManager.createNamedQuery("query", MyEntity.class)
            .setParameter("statuses", Arrays.asList(null, 1L))
            .getResultList();

However with Hibernate/Derby an actual result list only contains entities with status 1L but not null.

I have not found anything in the JPA 2.2 specification about this case. Did I miss something or is this vendor-specific?

---

The answers to this question only solve part of my problem. In their proposed solutions, the null comparison is hard-baked into the query and cannot be controlled via the collection-valued parameter.

Answer 1

As a Java programmer, where null = null yields true it might come as a surprise that in SQL (and JPQL) null = null is itself null which is "falsy". As a result, null in (null) yields null as well.

Instead you need to treat null seperately with a IS NULL check: e.status IS NULL OR e.status IN :statuses.

This is described in 4.11 Null Values of the JPA Specification:

• Comparison or arithmetic operations with a NULL value always yield an unknown value.
• Two NULL values are not considered to be equal, the comparison yields an unknown value.