[Hibernate-JIRA] Created: (HHH-6151) HQL with joins in sub-select creates wrong SQL (copy of NH-2648)
2:45 a.m.
HQL with joins in sub-select creates wrong SQL (copy of NH-2648)
----------------------------------------------------------------
Key: HHH-6151
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6151
Project: Hibernate Core
Issue Type: Bug
Components: query-hql
Affects Versions: 3.6.3
Environment: Hib 3.6.3 final; SQL Server 2008
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:
s.createQuery(
"SELECT ROOT FROM hmm.Sheet AS ROOT " +
"WHERE (EXISTS (FROM hmm.Shelf AS inv " +
" left JOIN ROOT.Folder AS ROOT_Folder " +
" WHERE (((ROOT_Folder.Shelf) = (inv) AND inv.Id = 1)) )) " +
" AND ROOT.Name = 'SomeName'").list();
(The query was created by a HQL generator of ours - hence the superfluous parentheses).
The inner exception is:
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier
"folder2_.Shelf" could not be bound.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
...
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
... at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 30 more
and the (wrong) SQL is
declare @p1 int
set @p1=0
exec sp_prepexec @p1 output,NULL,N'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'''
select @p1
----
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://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:20 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-6151) HQL with joins in sub-select creates wrong SQL (copy of NH-2648)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-6151?page=c...
]
Harald M. Müller updated HHH-6151:
----------------------------------
Attachment: hhh-5161.gif
I have attached a screenshot from debugging that looks at sqlAst in
QueryTranslatorImpl.doCompile:200. It shows, if I interpret it correctly,
* that we are currently looking at the from clause of the subselect
(classAlias="inv"; I forgot to include level=2);
* that right is null - i.e., there is no node in the from part other than the "from
... inv";
* but that the fromElements contains two entries (namely for both Shelf and Folder.
[Other debugging output might show this more convincingly ...]
HQL with joins in sub-select creates wrong SQL (copy of NH-2648)
----------------------------------------------------------------
Key: HHH-6151
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6151
Project: Hibernate Core
Issue Type: Bug
Components: query-hql
Affects Versions: 3.6.3
Environment: Hib 3.6.3 final; SQL Server 2008
Reporter: Harald M. Müller
Priority: Critical
Attachments: hhh-5161.gif, HQL_MissingJoinInExists.zip
The following query is translated to wrong SQL - the join is missing from the
sub-select:
s.createQuery(
"SELECT ROOT FROM hmm.Sheet AS ROOT " +
"WHERE (EXISTS (FROM hmm.Shelf AS inv " +
" left JOIN ROOT.Folder AS ROOT_Folder " +
" WHERE (((ROOT_Folder.Shelf) = (inv) AND inv.Id = 1)) )) "
+
" AND ROOT.Name = 'SomeName'").list();
(The query was created by a HQL generator of ours - hence the superfluous parentheses).
The inner exception is:
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier
"folder2_.Shelf" could not be bound.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
...
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
... at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 30 more
and the (wrong) SQL is
declare @p1 int
set @p1=0
exec sp_prepexec @p1 output,NULL,N'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'''
select @p1
----
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://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:36 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-6151) HQL with joins in sub-select creates wrong SQL (copy of NH-2648)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-6151?page=c...
]
Harald M. Müller commented on HHH-6151:
---------------------------------------
One more info to show that the JOIN inside the FROM is the culprit: The following DOES
work
"SELECT ROOT FROM hmm.Sheet AS ROOT " +
"WHERE (EXISTS (FROM Folder AS onv " +
" WHERE ROOT.Folder = onv AND onv.Id = 1 )) " +
" AND ROOT.Name = 'SomeName'").list();
and creates correct (MS SQL Server) SQL:
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,NULL,N'select sheet0_.Id as Id2_, sheet0_.Name as Name2_,
sheet0_.Folder as Folder2_ from Sheet sheet0_ where (exists (select folder1_.Id from
Folder folder1_ where sheet0_.Folder=folder1_.Id and folder1_.Id=1)) and
sheet0_.Name=''SomeName'''
select @p1
HQL with joins in sub-select creates wrong SQL (copy of NH-2648)
----------------------------------------------------------------
Key: HHH-6151
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6151
Project: Hibernate Core
Issue Type: Bug
Components: query-hql
Affects Versions: 3.6.3
Environment: Hib 3.6.3 final; SQL Server 2008
Reporter: Harald M. Müller
Priority: Critical
Attachments: hhh-5161.gif, HQL_MissingJoinInExists.zip
The following query is translated to wrong SQL - the join is missing from the
sub-select:
s.createQuery(
"SELECT ROOT FROM hmm.Sheet AS ROOT " +
"WHERE (EXISTS (FROM hmm.Shelf AS inv " +
" left JOIN ROOT.Folder AS ROOT_Folder " +
" WHERE (((ROOT_Folder.Shelf) = (inv) AND inv.Id = 1)) )) "
+
" AND ROOT.Name = 'SomeName'").list();
(The query was created by a HQL generator of ours - hence the superfluous parentheses).
The inner exception is:
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier
"folder2_.Shelf" could not be bound.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
...
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
... at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 30 more
and the (wrong) SQL is
declare @p1 int
set @p1=0
exec sp_prepexec @p1 output,NULL,N'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'''
select @p1
----
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://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5014
days inactive
5014
days old
hibernate-issues@lists.jboss.org
2 comments
1 participants
participants (1)
-
Harald M. Müller (JIRA)