Issue with coalesce and lists after upgrading from Hibernate v5 to v6

Hibernate ORM
1

Hi at all,

I am using the coalesce function from MariaDB to skip parameters, if they are not set. Through this I have created an optional filter for my native query, for instance:

@Query(value="select * from book where coalesce(:filterAuthorIds, null) is null or author_id in (:filterAuthorIds)", nativeQuery=true)
Page<Book> readBook(List<Integer> filterAuthorIds);

The real query is of course much more complex. I have simplified it to make it easier for you to understand my problem.
“filterAuthorIds” should obviously filter the books by the author_id.

It worked fine, until I upgraded the Java project from Spring 2.7.18 to Spring 3 and thus from Hibernate v5 to v6.

The query generated by Hibernate v5 looks like this:

coalesce(?, ?, null) is null or author_id in (? , ?)

The query generated by Hibernate v6 looks like this:

coalesce((?,?), null) is null or author_id in (?,?)

Now there are parentheses around the list in coalesce. So I got an error from MariaDB:

Operand should contain 1 column(s)

, because the SQL statement doesn’t work in MariaDB:

select * from book where coalesce((25003,25904), null) is null or author_id in (25003,25904);

Now I’m looking for a way to solve this problem. I need an optional filter in my native query for in-statements.

Does anyone have any idea?
Thanks in advance. :slight_smile:

2

This was unsupported behavior that happened to work in Hibernate 5, the coalesce function is not designed to work with anything but scalar values. You should check if your parameter list is empty and decide whether to include the in predicate or not creating the query dynamically through e.g. the Criteria API or, since your using Spring, Specifications:

  public static Specification<Boot> isFromAuthors(List<Integer> filterAuthorIds) {
    return (root, query, builder) -> {
      return filterAuthorIds.isEmpty ?
             null :
             root.get("author").in(departments)
    };
  }

This was reported before, you can find more details in the comments of this Jira: [HHH-15743] - Hibernate JIRA.

3

Hi @mbladel,

that’s not, what I was hoping for to read, but thanks for your explanation and link. I think, I will have to rewrite my statements then - the Specification might help.
Thanks for your reply. :slight_smile: