DB2 Named Queries different upon Hibernate 6 upgrade

Hibernate ORM
1

After upgrading from hibernate 5 to hibernate 6 (6.6.28.Final), DB2 Named queries that had the following sql are drastically changed into a completely different query.

The named query looks something like this:

select A from <entity> A
where (A.id, A.dateTime) in (
    select B.id, max(B.dateTime) from <entity> B
    group by B.id
)

In hibernate 5, it would be translated to the following SQL:

select <ID_COLUMN>, <DATE_TIME_COLUMN>, …, <OTHER COLUMN n>
from <table> A
where (A.<ID_COLUMN>, A.<DATE_TIME_COLUMN>) in (
    select B.<ID_COLUMN>, max(B.<DATE_TIME_COLUMN>) from <table> B
    group by B.<ID_COLUMN>
)

In hibernate 6, it is getting translated to a completely different SQL:

select <ID_COLUMN>, <DATE_TIME_COLUMN>, …, <OTHER COLUMN n>
from <table> A
exists (
    select B.<ID_COLUMN>, max(B.<DATE_TIME_COLUMN>) from <table> B
    group by B.<ID_COLUMN>
)
having B.<ID_COLUMN> = A.<ID_COLUMN>
and max(B.DATE_TIME_COLUMN) = A.DATE_TIME_COLUMN

I have confirmed that I am still using DB2Dialect in hibernate 6 that I was using in hibernate5. One odd thing I did notice is that the hibernate ORM log says 7.0.3, where the hibernate version is 6.6.28. Is this expected?

Is this a known issue? Is there anything I can do to get the query to generate the same way as it did in hibernate 5?

Thanks!

2

Hi and thanks for reporting the problem. I looked into it and figured, that something got lost in the development of ORM 6.0. I created this Jira issue that you can track: HHH-19768