[hibernate-issues] [Hibernate-JIRA] Closed: (HHH-2529) HQL resulting in incorrect SQL caused by ORDER BY

HQL resulting in incorrect SQL caused by ORDER BY ------------------------------------------------- Key: HHH-2529 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2529 Project: Hibernate3 Issue Type: Bug Components: query-hql Affects Versions: 3.2.2 Environment: hibernate-3.2.2, Sybase Adaptive Server 12.5 Reporter: Marc Luce Priority: Minor Attachments: SimpleHibernateTest.zip 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. If I remove the ORDER BY clause, I will receive the correct result set, but obviously, it would be unordered. The HQL: SELECT b.loans FROM Batch AS b WHERE b.id = :id ORDER BY b.loans.loanNumber DESC The SQL that is generated: 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 The mapping: <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> I will also attach a zip of my very simple eclipse project that illustrates the problem.