[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-567) Duplicate "Order By" clause generated when using native sql statement with paging enabled

[Mark Adelsberger (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-567?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=46420#comment-46420 ] 

Mark Adelsberger commented on HHH-567:
--------------------------------------

(And as I misinterpreted the forum's date format, the issue is even older than I thought.  Maybe best to disregard; sorry)

> Duplicate "Order By" clause generated when using native sql statement with paging enabled
> -----------------------------------------------------------------------------------------
>
>                 Key: HHH-567
>                 URL: https://hibernate.onjira.com/browse/HHH-567
>             Project: Hibernate ORM
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.0.3
>         Environment: Hibernate 3.0.3, RDBMS: DB2/NT, version: 07.02.0000
>            Reporter: steven
>
> When a native sql is issued as following:
> select person.oid as {personData.oid}, pn.firstName as {personData.firstName}, pn.lastName as {personData.lastName},person.dateOfBirth as {personData.birthDate}, person.gender as {personData.gender}, person.maritalStatus as {personData.maritalStatus}, pa.region as {personData.region}, pa.city as {personData.city}, pa.postalCode as {personData.zip} from Person person left outer join CPref2PostalAddress m on m.cprefId = person.defaultCPrefId left outer join PostalAddress pa on m.postalAddressId = pa.oid left outer join PersonName pn on pn.personId = person.oid where person.gender = :gender order by {personData.lastName} desc]
> the generated sql is:
> select * from ( select rownumber() over(order by lastName22_0_ desc) as rownumber_, person.oid as oid0_, pn.firstName as firstName22_0_, pn.lastName as lastName22_0_,person.dateOfBirth as birthDate22_0_,  person.gender as gender22_0_, person.maritalStatus as maritalS7_22_0_, pa.region as region22_0_, pa.city as city22_0_, pa.postalCode as zip22_0_ from Person person  left outer join CPref2PostalAddress m on m.cprefId = person.defaultCPrefId left outer join PostalAddress pa on m.contactPointId = pa.oid left outer join PersonName pn on pn.personId = person.oid order by lastName22_0_ desc ) as temp_ where rownumber_ <= ?
> but the db2 complains that the second "order by" is illegal.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira