Hibernate version: 3.2.2
I have written a HQL query that retrieves a set of objects that are mapped from a parent object. The SQL that is generated from it is incorrect. I have discovered that the ORDER BY clause is causing the problem. I will include the HQL that generates the incorrect join in SQL and the HQL that generates the correct join in SQL (without an ORDER BY). The first query results in a query that returns each object 3 times. My question is am I defining the ORDER BY incorrectly or is there a bug in Hibernate?
HQL that generates a bad join
Code:
SELECT b.loans FROM Batch AS b WHERE b.id = :id ORDER BY b.loans.loanNumber DESC
Resulting SQL
Code:
select
loan2_.id as id2_,
loan2_.name as name2_
from
dbo.TestBatch batch0_
inner join
TestMapping loans1_
on batch0_.id=loans1_.batchId
inner join
dbo.TestLoan loan2_
on loans1_.loanId=loan2_.id,
TestMapping loans3_,
dbo.TestLoan loan4_
where
batch0_.id=loans3_.batchId
and loans3_.loanId=loan4_.id
and batch0_.id=?
order by
loan4_.name DESC
If I just remove the ORDER BY clause the Resulting SQL includes a valid join
Code:
select
loan2_.id as id2_,
loan2_.name as name2_
from
dbo.TestBatch batch0_
inner join
TestMapping loans1_
on batch0_.id=loans1_.batchId
inner join
dbo.TestLoan loan2_
on loans1_.loanId=loan2_.id
where
batch0_.id=?
Mapping documents:Code:
<class name="testHibernate.Batch"
table="TestBatch"
schema="dbo"
dynamic-insert="true"
dynamic-update="true">
<id name="id" type="java.lang.Integer" column="id" >
<generator class="increment" />
</id>
<property name="name" type="java.lang.String" column="name" length="80" not-null="true" />
<set name="loans" table="TestMapping">
<key column="batchId"/>
<many-to-many column="loanId" unique="true" class="testHibernate.Loan" lazy="proxy"/>
</set>
</class>
<class name="testHibernate.Loan"
table="TestLoan"
schema="dbo"
dynamic-insert="true"
dynamic-update="true">
<id name="id" type="java.lang.Integer" column="id" >
<generator class="increment" />
</id>
<property name="loanNumber" type="java.lang.String" column="name" length="80" not-null="true" />
</class>
By the way, if you think this problem looks familiar, I have already posted it. I don't really know how this forum works, so I thought I may have "flagged" my original post as "answered" when I replied to it to provide more information.
Login
FAQ
Search