[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2984) Hibernate generates invalid query when using order by through a one-to-one relation, error is "for SELECT DISTINCT, ORDER BY expressions must appear in select list"

[Dobes Vandermeer (JIRA)]

Hibernate generates invalid query when using order by through a one-to-one relation, error is "for SELECT DISTINCT, ORDER BY expressions must appear in select list"
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

                 Key: HHH-2984
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2984
             Project: Hibernate3
          Issue Type: Improvement
    Affects Versions: 3.2.5
         Environment: PostgresQL 8.2 and glassfish running in Windows XP Professional
            Reporter: Dobes Vandermeer


For this query:

select distinct object(b) from Business b where exists (select bur from BusinessUserRole bur where bur.user = :user and bur.business = b) order by b.contactInformation.name ASC

The following SQL is generated:

Hibernate: select distinct business0_.id as id114_, business0_.contactInformation_id as contactI3_114_, business0_.currency_id as currency2_114_ from Business business0_, ContactInformation contactinf2_ where business0_.contactInformation_id=contactinf2_.id and (exists (select businessus1_.id from BusinessUserRole businessus1_ where businessus1_.user_id=? and businessus1_.business_id=business0_.id)) order by contactinf2_.name ASC limit ?

This causes PostgreSQL to issue the error :

for SELECT DISTINCT, ORDER BY expressions must appear in select list

I can see quite clearly that it is correct that contactinf2_.name does not appear in the select list.

I suppose if the query were not using distinct, this issue would not occur.  As a workaround, I can sort the results manually.





-- 
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