[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-567) Duplicate "Order By" clause generated when using native sql statement with paging enabled
Mark Adelsberger (JIRA)
noreply at atlassian.com
Wed Apr 25 11:24:49 EDT 2012
[ https://hibernate.onjira.com/browse/HHH-567?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=46419#comment-46419 ]
Mark Adelsberger commented on HHH-567:
--------------------------------------
I realize this issue is a bit old, but maybe worth noting: prior to v9 DB2 really wouldn't let you put ORDER BY in a subselect. The issue is worded confusingly - it suggests that the appearance of ORDER BY at two paces in the query is the problem, but that is not the case. However, if your attempt to reproduce used a current version of DB2, then that's why you can't reproduce it; the query may "look" correct, but DB2 7 would reject it.
> 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
More information about the hibernate-issues
mailing list