nhibernate-issues

[nhibernate-issues] [NHJIRA] Created: (NH-2648) HQL with joins in sub-select creates wrong SQL

[Harald M. M. (J. <nh...@gm...>]

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

                 Key: NH-2648
                 URL: http://216.121.112.228/browse/NH-2648
             Project:  NHibernate
          Issue Type: Bug
          Components: Core
    Affects Versions: 3.2.0Beta1
            Reporter: Harald M. Müller
            Priority: Critical
         Attachments: HQL_MissingJoinInExists.zip

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.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[nhibernate-issues] [NHJIRA] Commented: (NH-2648) HQL with joins in sub-select creates wrong SQL

[Harald M. M. (J. <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2648?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20904#action_20904 ] 

Harald M. Müller commented on NH-2648:
--------------------------------------

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.


> HQL with joins in sub-select creates wrong SQL
> ----------------------------------------------
>
>                 Key: NH-2648
>                 URL: http://216.121.112.228/browse/NH-2648
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: Core
>    Affects Versions: 3.2.0Beta1
>            Reporter: Harald M. Müller
>            Priority: Critical
>         Attachments: HQL_MissingJoinInExists.zip
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[nhibernate-issues] [NHJIRA] Updated: (NH-2648) HQL with joins in sub-select creates wrong SQL

[Harald M. M. (J. <nh...@gm...>]

     [ http://216.121.112.228/browse/NH-2648?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Harald M. Müller updated NH-2648:
---------------------------------

    Attachment: FromElementsForSubqueryFrom.gif
                ASTForSubqueryFrom.gif

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 ...

> HQL with joins in sub-select creates wrong SQL
> ----------------------------------------------
>
>                 Key: NH-2648
>                 URL: http://216.121.112.228/browse/NH-2648
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: Core
>    Affects Versions: 3.2.0Beta1
>            Reporter: Harald M. Müller
>            Priority: Critical
>         Attachments: ASTForSubqueryFrom.gif, FromElementsForSubqueryFrom.gif, HQL_MissingJoinInExists.zip
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[nhibernate-issues] [NHJIRA] Commented: (NH-2648) HQL with joins in sub-select creates wrong SQL

[Harald M. M. (J. <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2648?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=20934#action_20934 ] 

Harald M. Müller commented on NH-2648:
--------------------------------------

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

> HQL with joins in sub-select creates wrong SQL
> ----------------------------------------------
>
>                 Key: NH-2648
>                 URL: http://216.121.112.228/browse/NH-2648
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: Core
>    Affects Versions: 3.2.0Alpha2
>            Reporter: Harald M. Müller
>            Priority: Critical
>         Attachments: ASTForSubqueryFrom.gif, FromElementsForSubqueryFrom.gif, HQL_MissingJoinInExists.zip
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[nhibernate-issues] [NHJIRA] Updated: (NH-2648) HQL with joins in sub-select creates wrong SQL

[Harald M. M. (J. <nh...@gm...>]

     [ http://216.121.112.228/browse/NH-2648?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Harald M. Müller updated NH-2648:
---------------------------------

    Attachment: NH2648Testcase.zip

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.

> HQL with joins in sub-select creates wrong SQL
> ----------------------------------------------
>
>                 Key: NH-2648
>                 URL: http://216.121.112.228/browse/NH-2648
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: Core
>    Affects Versions: 3.2.0Alpha2
>            Reporter: Harald M. Müller
>            Priority: Critical
>         Attachments: ASTForSubqueryFrom.gif, FromElementsForSubqueryFrom.gif, HQL_MissingJoinInExists.zip, NH2648Testcase.zip
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira