Closed
Description
The base situation
We have a data model like this one
Entity ---0..* ChildEntity
---0..1 OtherEntity
ChildEntity ---1 GrandChildEntity
The associations to OtherEntity and GrandChildEntity are mapped as many-to-one.
When executing the HQL query ...
SELECT ROOT
FROM Entity AS ROOT
WHERE
EXISTS
(FROM ELEMENTS(ROOT.Children) AS child
LEFT JOIN child.Child AS grandChild
LEFT JOIN ROOT.OtherEntity AS otherEntity
WHERE
grandChild.Name like 'G%'
OR otherEntity.Name like 'G%'
)The problem
... we get the following sql generated:
select entity0_.Id as id1_0_3_, entity0_.Name as name2_0_3_, entity0_.OtherEntity as otherentity3_0_3_
from Entity entity0_
where exists (
select children1_.Id, grandchild2_.Id, otherentit3_.Id
from ChildEntity children1_
left outer join GrandChildEntity grandchild2_ on children1_.Child=grandchild2_.Id,
left outer join OtherEntity otherentit3_ on entity0_.OtherEntity=otherentit3_.Id
where entity0_.Id=children1_.Parent and (grandchild2_.Name like 'G%' or otherentit3_.Name like 'G%'))This leads to an exception because the comma , before the last left outer join is illegal.
Side note on why we don't use direct dot-navigations instead of explicit joins
Using implied joins on the :1-navigations like in the following query does not work because then, the joins are created as inner join or theta join so the condition is not evaluated correctly if there is not OtherEntity associated with Entity:
SELECT ROOT
FROM Entity AS ROOT
WHERE
EXISTS
(FROM ELEMENTS(ROOT.Children) AS child
WHERE
child.Child.Name like 'G%'
OR ROOT.OtherEntity.Name like 'A%'
)I might start a separate discussion on Nullability and many-to-one because of that.