Null check against collection query param

Hibernate ORM
1

I’m creating a query using EntityManager:

@Entity
public class Branch {
  @Id
  @GeneratedValue
  private long id;
}

@Entity
public class Invoice {
  @Id
  @GeneratedValue
  private long id;

  @ManyToOne(fetch = FetchType.LAZY, optional = false)
  @JoinColumn(updatable = false)
  private Branch branch;
}

    TypedQuery<Invoice> query =
        entityManager
            .createQuery(
                " SELECT i " +
                " FROM Invoice i " +
                " WHERE (:branchIds IS NULL OR i.branch.id IN :branchIds) ",
                Invoice.class)
            .setParameter("branchIds", List.of(123L));

Which gives the error:

java.lang.IllegalArgumentException: Parameter value [[123]] did not match expected type [SqmBasicValuedSimplePath(org.hibernate.bugs.Invoice(i).branch.id)]
	at org.hibernate.query.internal.QueryParameterBindingImpl.setBindValue(QueryParameterBindingImpl.java:129)
	at org.hibernate.query.spi.AbstractCommonQueryContract.setParameter(AbstractCommonQueryContract.java:835)
	at org.hibernate.query.spi.AbstractSelectionQuery.setParameter(AbstractSelectionQuery.java:602)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.setParameter(QuerySqmImpl.java:1061)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.setParameter(QuerySqmImpl.java:125)
	at org.hibernate.bugs.JPAUnitTestCase.hhh123Test(JPAUnitTestCase.java:52)

Is there documentation somewhere about why this query is not expected to work, and how I should write it? I’m confused why it wouldn’t work as written.

2

Well, I could ask you the other way around, where it is written in the documentation that this is supposed to work?
This sort of conditional querying is just broken by design. It might have worked by accident in previous versions of Hibernate ORM, but it is not how you should write this. Instead, use the JPA Criteria API and add predicates as well as potentially necessary joins for these predicates only when you have an actual value given. This will actually make it easier for your database to optimize the final SQL query.

Having said that, binding a list here should actually work though, so if you reproduced this with ORM 6.6+, please create a bug ticket in our issue tracker and attach that reproducer.

3

Also see similar questions that have been asked before: Issue with coalesce and lists after upgrading from Hibernate v5 to v6 - #2 by mbladel

4

This sort of conditional querying is just broken by design

I know you don’t like this justification in the hibernate forums, but this query works (and works well as far as I can tell) using a spring-data-jpa repository. I can’t find anything explaining why it shouldn’t work in hibernate directly so I’m asking. I had to move this particular query from spring-data-jpa to hibenate to take advantage of COLLATE in hql, which jpql doesn’t support.

It might have worked by accident in previous versions of Hibernate ORM

I am not upgrading hibernate, so that is not my complaint this time around :wink:. I have no idea if this is a regression or not.

5

I don’t know what Spring Data does or how you modeled this, but clearly it can only work if it does something different than what you’re doing right now. So maybe share some more details? What worked before?

What Hibernate ORM version are you using?