[hibernate-issues] [Hibernate-JIRA] Commented: (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"

[Gail Badner (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2984?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29034 ] 

Gail Badner commented on HHH-2984:
----------------------------------

Please attach a runnable test case (Java + mapping) that reproduces this issue.

> 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