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.

Does this answer your question? Checking for NULL on a Collection in JPQL queries? — Yann39
– Yann39, Commented Oct 4, 2021 at 11:10
@Yann39 Thank you, I also spotted this question. However, the answers 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. — Jens Piegsa
– Jens Piegsa, Commented Oct 4, 2021 at 12:00

Jan-Willem Gmelig Meyling · Accepted Answer · 2021-10-04 15:16:21Z

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.

Thank you, that makes sense. I indeed had a different expectation of the ORM world here, however JPQL cannot modify the semantics of SQL. For my specific case, this means that I need to introduce two corresponding JPQL queries (one with ... IS NULL OR ... and a second without) and decide on the Java side which one to call, right?

Collectives™ on Stack Overflow

JPQL query with input parameter collection containing null

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related