]
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: