NH-2648 - HQL with joins in sub-select creates wrong SQL

[nhibernate-bot]

Harald M. Müller created an issue — 13th April 2011, 2:51:24:

The following query is translated to wrong SQL - the join is missing from the sub-select:

    SELECT ROOT FROM NHibernate.Test.NHSpecificTest.HQL_MissingJoinInExists.Sheet AS ROOT 
                            WHERE (EXISTS (FROM NHibernate.Test.NHSpecificTest.HQL_MissingJoinInExists.Shelf AS inv  
                                           JOIN ROOT.Folder AS ROOT_Folder 
                                           WHERE (((ROOT_Folder.Shelf) = (inv) AND inv.Id = 1)) )) 
                                  AND ROOT.Name = 'SomeName'

(The query was created by a HQL generator of ours - hence the superfluous parentheses). The exceotion is:

NHibernate.Exceptions.GenericADOException : could not execute query
< select sheet0*.Id as Id2_, sheet0_.Name as Name2_, sheet0_.Folder as Folder2_ from Sheet sheet0_ where (exists (select shelf1_.Id from Shelf shelf1_ where folder2_.Shelf=shelf1_.Id and shelf1_.Id=1)) and sheet0*.Name='SomeName' >
[SQL: select sheet0*.Id as Id2_, sheet0_.Name as Name2_, sheet0_.Folder as Folder2_ from Sheet sheet0_ where (exists (select shelf1_.Id from Shelf shelf1_ where folder2_.Shelf=shelf1_.Id and shelf1_.Id=1)) and sheet0*.Name='SomeName']
----> System.Data.SqlClient.SqlException : The multi-part identifier "folder2_.Shelf" could not be bound.

Since NHib 0.99 and in Hibernate, it has been possible to use joins inside subqueries. HQL allows it, and therefore, it should produce correct SQL (both syntactically and semantically). Actually, joins in subqueries are necessary if there are OR or NOT operators inside the subquery (some simpler queries can be rewritten with all Joins at top-level).

I consider this bug critical because there is no workaround for

• HQL generated for earlier NHib versions
• manual HQL that needs OR or NOT inside a subquery
• (I assume) the correct translation of Linq's .Any in the NHib.Linq provider

I have attached a test case that shows the behavior.

---

Harald M. Müller added a comment — 13th April 2011, 10:45:33:

I looked a little bit in the code and found the following in HqlSqlWalker.g:

fromElement! 
...:
   | je=joinElement 
      -> //$je

i.e., the return $je of a joinElement is commented, so it returns the null tree. Probably, global joins are handled via some global variable machinery (there is a call to CreateFromJoinElement(...) inside joinElement's production), but this appears not to work for subqueries. Maybe this helps to solve this issue.

---

Harald M. Müller added a comment — 14th April 2011, 13:26:27:

I have attached two images that show that the AST is in in inconsistent state: The "_fromElementsByTableAlias" collection for the subquery contains, as expected, two nodes (the second one is the joined one). But the AST itself contains only one child ...

---

Harald M. Müller added a comment — 22nd April 2011, 0:45:23:

Unfortunately, this bug is already in Hibernate. I have opened issue HHH-6151 there.

---

Harald M. Müller added a comment — 27th April 2011, 0:25:16:

I have attached a new test case NH2648Testcase.zip with (a) cleaner code; (b) a successful and a failing query to show that the problem is the join inside the exists.

---

Daniel A. Schilling added a comment — 4th December 2012, 16:30:46:

I believe the entities specified in an HQL join clause have to be related. See http://nhforge.org/doc/nh/en/index.html#queryhql-joins. All of the examples in that documentation are things like "from Eg.Cat as cat join cat.Mate" - there is a direct relationship between Cat and Cat.Mate, so it can easily figure out how to constrain the join (the "ON" part of the join). In the query above - I'm having trouble figuring out what SQL you're expecting. There is no clear relationship between Shelf and ROOT.Folder specified - at least not in the JOIN - there is later in the WHERE. So are you expecting a CROSS JOIN? HQL doesn't support cross joins.

I think the following HQL would probably work better (but I haven't tested this):

    select root
    from Sheet as root
    where
        exists (
            from Shelf as inv
            where
                root.Folder.Shelf = inv
                and inv.Id = 1)
        and root.Name = 'SomeName'

... using an implicit join instead of an explicit one. Doing so makes it clear that root.Folder.Shelf is directly related to root, not inv, so we could rewrite this as an explicit join like so:

    select root
    from Sheet as root
        join root.Folder as fldr
    where
        exists (
            from Shelf as inv
            where
                fldr.Shelf = inv
                and inv.Id = 1)
        and root.Name = 'SomeName'

So the JOIN should be in the main query, not the sub query.

---

Daniel A. Schilling added a comment — 4th December 2012, 18:21:29:

The subquery is not needed at all here. This simpler query should accomplish the same thing:

    select root
    from Sheet as root
        join root.Folder as fldr
    where
        fldr.Shelf.Id = 1
        and root.Name = 'SomeName'

---

Daniel A. Schilling added a comment — 4th December 2012, 18:22:13:

oops. forgot the "select root" at the beginning.