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

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.