Exception executing HQL query with uncorrelated left joins in subselect

[csharper2010]

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.

[bahusoid]

 from ChildEntity children1_
--...
 LEFT JOIN ROOT.OtherEntity AS otherEntity

I would say it's invalid hql. What does this join do in subquery? It makes no sense to me.
Why is it not part of main query?

SELECT ROOT 
FROM Entity AS ROOT 
 LEFT JOIN ROOT.OtherEntity AS otherEntity -- MOVED HERE
WHERE
EXISTS 
    (FROM ELEMENTS(ROOT.Children) AS child
        LEFT JOIN child.Child AS grandChild       
    WHERE
        grandChild.Name like 'G%'
        OR otherEntity.Name like 'G%'
    )

[csharper2010]

I would say it's invalid hql. What does this join do in subquery? It makes no sense to me.
Why is it not part of main query?

HQL documentation does not mention any restrictions on such joins and the same way you can place outer joins in SQL wherever you want, it could be handled in HQL.

That query worked fine in NHibernate 1.2.1. and it is not hand-written HQL but generated from our application.

[bahusoid]

It's another case of #1228

It's actually mentioned in original issue discussion that provided hql looks wrong. I also already mentioned it: #2146 (comment)

[csharper2010]

There is nothing mentioned in HQL doc that it is illegal and it worked (quite far) in the past.

I guess you would allow a query where an implied join is made like so:

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%'
)

Then why an explicit left join should be forbidden? In some cases (depending on the mapping), this would even lead to an outer join in the generated SQL inside the subselect. But not in mine... I get a theta join with that hql so I would like to have the explicit outer join.

[bahusoid]

Ok. Ideally, we should support such nonsensical joins too. Only your initial fix is wrong. In #2146 implicit join is generated instead of requested explicit join. I had to add some hacks too properly handle it in #2990 (see 32fdfec)

Your current PR is just another hack supporting your specific case. If you need such joins fix it properly and remove my hack.

The following query would still fail with your suggested fix:

q = session.CreateQuery(
	@"
	SELECT ROOT 
	FROM Entity AS ROOT 
	WHERE
		EXISTS 
			(FROM ChildEntity AS child
				LEFT JOIN child.Child AS grandChild
				LEFT JOIN ROOT.OtherEntity AS otherEntity
			WHERE
				grandChild.Name like 'A%'
				OR otherEntity.Name like 'A%'
			)");
Assert.AreEqual(1, q.List().Count);

[csharper2010]

Yep, I also have a feeling that my fix won't handle all the posssible cases...
The tree generation is very complex and I don't have the knowledge about corner cases that may exist so it's hard to estimate all the consequences of a change.

It would be ideal if the code in #2146 would not be required, it was a workaround already then but it was driven by the comment that the from clause processor will take care of adding it to the right place.

I would be willing to do some further steps but it's really not an easy task because of those complex interdependencies.

[csharper2010]

Now I had some time to work on a cleaner fix: #3338

@bahusoid: the query you gave in your comment now also works because I do the translation based on the explicit case where necessary.