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

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

Dobes Vandermeer updated HHH-2984:
----------------------------------

    Attachment: HHH-2984-test-case.zip

I've created a test case that reproduces the problem.  I used ejb3unit for the test case, and maven to fetch the dependencies, including hibernate, so I hope you can use maven.  Let me know if that's not the case and I'll try to ZIP up all the dependencies as well.

The ejb3unit.properties contains the database, username, and password, which in my case are all 'ejb3unit'.



> 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: Hibernate Core
>          Issue Type: Improvement
>    Affects Versions: 3.2.5
>         Environment: PostgresQL 8.2 and glassfish running in Windows XP Professional
>            Reporter: Dobes Vandermeer
>         Attachments: HHH-2984-test-case.zip
>
>
> 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