Performance degration when querying field IN emptyParameter after upgrading to 6.5 from 6.3

Hibernate ORM
1

Good morning
Recently I have tried to upgrade my app from Hibernate 6.3 to Hibernate 6.5 but I faced a performance degration when querying a field with IN clause and pasing an emptyParameter
The query is similar to
SELECT p FROM processes p where p.jobId in (:jobIds) order by p.id desc
where jobIds can be an empty collection.

When using Hibernate 6.3 the query generated was

select
        p1_0.id,
        p1_0.XXX,
...
        p1_0.jobId
    from
        schema.processes p1_0 
    where 1=0 order by p1_0.id desc

but after moving up to Hibernate 6.5 the query generated is

select
        p1_0.id,
        p1_0.XXX,
...
        p1_0.jobId
    from
        schema.processes p1_0 
where (1 = case when p1_0.jobId is not null then 0  end)
order by p1_0.id desc

When the table has 0.5M records it degradates from 0.1s to 20s getting worse when the table size is bigger.

Let me know if you need more information.
Thanks.

2

This was changed with [HHH-17804] - Hibernate JIRA, so you can comment on that Jira issue if you want, but ultimately, passing an empty list is not a good idea and will produce no results.

3

Hi Beikov,
How can I contact Gavin?
HHH-17804 seems closed and I don’t understand the explanation given in the description.
I don’t understand why this has been changed.
foo in () when foo is null will never be true, as 1=0.
null in empty list is always false, isn’t it? empty collection != null, isn’t it?

1 = case when p1_0.jobId is not null then 0 end → when p1_0.jobId is not null → 1 = 0 as before.
1 = case when p1_0.jobId is not null then 0 end → when p1_0.jobId is null → 1 = null?

So, for every row this clause is false but the execution time is much higher because it checks every row instead of having an easy 1=0 condition to execute.

4

Just comment on the Jira issue, he will receive a notification.