[Hibernate-JIRA] Created: (HHH-6152) retrieve problem when using <join table clause
by Sathya Balakrishnan (JIRA)
retrieve problem when using <join table clause
----------------------------------------------
Key: HHH-6152
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6152
Project: Hibernate Core
Issue Type: Bug
Components: query-criteria, query-hql
Affects Versions: 3.2.7
Environment: Hibernate 3.2.7, Oracle 10g database
Reporter: Sathya Balakrishnan
I have inheritance relation modeled.
class SuperClass
name
type
Order order;
class SubClass extends SuperClass
attr1
attr2
etc
in the mapping. I have mapped SubClass in SubClass.hbm.xml and used <join table
<join table="Super_CLASS">
<key column="ID" />
<many-to-one name="order" class="MyOrder" />
SubClass further has SubSubClass1 SubSubClass2 and modeled using subclass and discriminators. My database model is
there is one SUPER_CLASS table and many SUB_CLASS tables and within one branch of hierarchy there can be many child classes.
Another main class - Order class which contains all the subclasses.
MyOrder
List<SubClass> subClassList
mapping is done with bag
<bag name="subClassList" table="SUB_CLASS" inverse="true" cascade="all">
<key column="order_id"
<one-to-many class="SubClass" />
</bag>
Now the problem is while retrieving when we traverse the class MyOrder and call getSubClassList() hibernate fires a query to pull this entity but the query generated is using the order_id to query this entity which is correct but
the query generated expects the order_id to be present in SubClass tables alias instead of SuperClass table alias.
for eg : generate query- select subclass_1.order_id, subclass_1.attr1, subclass_1.attr2, superclass_1.name, superclass_2.type ...
from SUB_CLASS subclass_1 INNER JOIN SUPER_CLASS superclass_1
ON subclass_1.id = superclass_1.id
WHERE subclass_1.order_id = ?
order_id field is expected to be found in subclass_1 ( SUB_CLASS ) - This is causing the failure.
How to over come this ?.
Thanks,
Sathya.
--
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
14 years, 11 months
[Hibernate-JIRA] Created: (HHH-6151) HQL with joins in sub-select creates wrong SQL (copy of NH-2648)
by Harald M. Müller (JIRA)
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
14 years, 11 months