Subquery join rewrite produces wrong SQL in some cases

Description

I just found an edge case where the current implementation of the subquery rewrite produces wrong results. The problem occurs when a non-related join alias is used in the ON clause with a key restriction. Something like

1 2 3 4 SELECT c FROM Document d LEFT JOIN d.versions v LEFT JOIN d.contacts c ON KEY(c) = v.idx

currently produces SQL like

1 2 3 4 5 6 7 select person3_.id as id1_6_, person3_.age as age2_6_, person3_.name as name3_6_, person3_.partnerDocument_id as partnerD4_6_ from Document document0_ left outer join document_version versions1_ on document0_.id=versions1_.document_id left outer join ( select contacts2_.*, versions1_.idx as c_0_idx from contacts contacts2_ left outer join Person person3_ on contacts2_.contacts_id=person3_.id ) contacts2_ on document0_.id=contacts2_.Document_id and (contacts2_.contacts_KEY=contacts2_.c_0_idx ) left outer join Person person3_ on contacts2_.contacts_id=person3_.id

The subquery uses a join alias that is only available outside of it. The fix is to prevent inlining such columns. A PR is on it's way...

Environment

None

Status

Assignee

Christian Beikov

Reporter

Christian Beikov

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.2.6

Priority

Major