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

[Steve Ebersole (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2529?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steve Ebersole closed HHH-2529.
-------------------------------

    Resolution: Rejected

Actually a couple of things wrong here:
1) your "dereference" of the collection property here is illegal syntax.  Somehow a regression was introduced which allowed this to happen.  I have added the checks and proper exceptions again, along with more unit tests; see the documentation for how to properly dereference a collection.
2) If you want distinct results, specify distinct on your select clause.  Again, a documentation item.

> 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