Combining an implicit JOIN with a LEFT JOIN which requires a WITH clause triggers an exception.

[bahusoid]

Original problematic query from hibenate issue looks like:

SELECT s.studentId, q.questionId, sa.answer
FROM Student s, Question q
LEFT JOIN s.studentAnswerList AS sa WITH sa.question.questionId = q.questionId

Test simulating such query was added here:

nhibernate-core/src/NHibernate.Test/Hql/EntityJoinHqlTest.cs

Lines 124 to 138 in 11bc49d

	[Test, Ignore("Failing for unrelated reasons")]
	public void CrossJoinAndWithClause()
	{
	//This is about complex theta style join fix that was implemented in hibernate along with entity join functionality
	//https://hibernate.atlassian.net/browse/HHH-7321
	using (var sqlLog = new SqlLogSpy())
	using (var session = OpenSession())
	{
	session.CreateQuery(
	"SELECT s " +
	"FROM EntityComplex s, EntityComplex q " +
	"LEFT JOIN s.SameTypeChild AS sa WITH sa.SameTypeChild.Id = q.SameTypeChild.Id"
	).List();
	}
	}

NHibernate currently generates joins in wrong order

FROM fromTable
LEFT JOIN leftJoin ON leftJoin.someId = thetaJoin.someId
,  thetaJoin

[bahusoid]

I understood the root of the issue. Parser converts flat list of joins to parent-child tree:
1 fromTable -> 2 thetaJoin -> 3 leftJoin

is converted to:
1 fromTable -> 1.1 leftJoin (as leftJoin is mapped association for fromTable)
2 thetaJoin

And final join list is constructed by flattening this tree: fromTable -> leftJoin -> thetaJoin

That means that this issue affects entity join too if it's referenced in association:

FROM  fromTable f
-- Entity JOIN:
INNER JOIN entityJoin  ej WITH f.EntityId = ej.id 
-- Association JOIN that depends on Entity JOIN
LEFT JOIN f.Child  ass WITH ej.SomeID =  ass.SomeID 

The hql above will not be generated properly to SQL - "enityJoin" will be appended after f.Child.
It seem some kind of topological sorting is required here. Or at least user defined order of joins shouldn't be affected .

[hazzik]

Is it this one? https://hibernate.atlassian.net/browse/HHH-1969

[bahusoid]

Looking for provided SQL - very likely. But this issue was closed as duplicate of https://hibernate.atlassian.net/browse/HHH-1480 without much investigations. So I can't be sure.

[hazzik]

Based on a discussion in #2037 here should be "CROSS JOIN". Otherwise it would not be possible to re-order the joins in any way which will allow the query to execute.

[bahusoid]

should be "CROSS JOIN". Otherwise it would not be possible to re-order the joins in any way which will allow the query to execute.

So to me now it looks like 2 issues:

1. Invalid query - as this query can't be written correctly in SQL with implicit joins.
   I don't like the path hibernate took on this - by simply replacing implicit joins with cross joins. It's not the same thing and such fix looks wrong to me.
   So invalid generated SQL is correct behavior to me - NHibernate should not try to fix invalid user queries.
   To me much better way would be adding support for cross join type (only for entity joins) in hql. And linq should generate cross joins instead of implicit joins.

2. Invalid join orders in generated SQL when association joins reference in WITH clause implicit or entity join. Association joins are currently generated before implicit/entity joins.
   Something that needs to be investigated.

@hazzik What do you think on 1? If you agree I can try to implement support for cross joins for entity joins

[hazzik]

Is it the same as #1060?

[bahusoid]

Yes that's it. But as we've seen it's not SQL Server specific and implicit join != cross join (though I'm not anymore convinced that we should really care about it)

[bahusoid]

Closing it as problematic hql query is actually wrong and can't be expressed in valid SQL. I will probably create a separate issue for invalid join orders case with valid query.
TODO: Replace ImplicitJoinAndWithClause with proper test