Upgrading from 5.4.9 to 5.4.29, HQL no longer correctly generates inner joins

Description

Same issue manifests when I tried with theta-style join

HQL: select distinct tableOneBO.id FROM tableTwoBO as tableTwoBO
INNER JOIN tableOneBO as tableOneBO on tableTwoBO.someVarchar=tableOneBO.someVarcharWithSlightlyDifferentMappingNameButSameDBFieldName
where tableTwoBO.manyToOneAssocTable.id = 1
AND tableOneBO.typeCode in ('ABC', 'DEF', 'GHI')
AND tableOneBO.somVal = 0

SQL for 5.4.9 (correct):
select
distinct t2alias.ID as col_0_0_
from
TABLE_ONE t1alias
inner join
TABLE_TWO t2alias
on
where
t1alias.ID=123
and t1alias.SOME_VARCHAR=t2alias.SOME_VARCHAR
and (
t2alias.TYPE_CD in (
'ABC', 'DEF', 'GHI'
)
)
and t2alias.ACTIVE_IND=0 for fetch only

SQL for 5.4.29(incorrect):
select
distinct t2alias.ID as col_0_0_
from
TABLE_ONE t1alias
where
t1alias.ID=123
and t1alias.SOME_VARCHAR=t2alias.SOME_VARCHAR
and (
t2alias.TYPE_CD in (
'ABC', 'DEF', 'GHI'
)
)
and t2alias.ACTIVE_IND=0 for fetch only

Activity

Show:

SGilbert June 21, 2021 at 9:35 PM

After attempting to upgrade to 5.2.2, I found that my legacy project code was using a custom version of SQLGeneratorBase in order to append a statement to each generated SQL. This no longer appears to be a bug, and I think this can be closed.

SGilbert May 28, 2021 at 3:10 PM

I’m still unable to reproduce this issue without my legacy project code, so I’ve started debugging in the hibernate source. It looks like something is assigning type=142 to the AST object associated with the tableTwoBO, maybe inside HqlSqlBaseWalker, but I haven’t had time to sort that out yet.

SGilbert March 16, 2021 at 6:40 PM

Yes, I’ll work on creating a test case. It might take me some time. In the meantime, I’ve narrowed down the issue to which version it starts. It looks like the query works until version 5.4.22; version 5.4.21 correctly renders the SQL, but 5.4.22 does not.

Christian Beikov March 12, 2021 at 8:24 AM

Thanks for the report. Could you please attach a test case created with the test case template or create a PR with a test against the hibernate-orm repository?