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

[Marc Luce (JIRA)]

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.

-- 
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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira