Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

HQL with joins in sub-select creates wrong SQL

Description

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

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

Environment

None

Attachments

4

Activity

Show:

Daniel A. Schilling 
December 4, 2012 at 6:22 PM

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

Daniel A. Schilling 
December 4, 2012 at 6:21 PM
(edited)

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

Daniel A. Schilling 
December 4, 2012 at 4:30 PM
(edited)

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):

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

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

H.M. Müller 
April 27, 2011 at 12:25 AM

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.

H.M. Müller 
April 22, 2011 at 12:45 AM

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

Fixed

Details

Assignee

Reporter

Labels

Components

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created April 13, 2011 at 2:51 AM
Updated July 16, 2019 at 9:57 AM
Resolved July 16, 2019 at 9:57 AM
Who's Looking?