Generated SQL using setMaxResults/setFirstResult possible duplicate entities in pages in Oracle when using rownum and ordering by non unique column - adding rowid would remove chances of duplicates in oracle
Description
Attachments
1
Activity
Show:
Att May 14, 2015 at 2:56 PM
Same problem in 3.6 and Oracle
A workaround is build the query in this manner:
– between 101 and 200
select * from ( select row_., rownum rownum_ from ( / criteria query */ select this_.field as ...., ... from TABLE this_ where this_...=filter
order by this_.orderColumn asc
) row_ )
where rownum_ > 100 and rownum <= 200;
instead of:
select * from ( select row_., rownum rownum_ from ( / criteria query */ select this_.field as ...., ... from TABLE this_ where this_...=filter
order by this_.orderColumn asc ) row_ where rownum <= 200) where rownum_ > 100;
Will it be fixed?
Details
Details
Assignee
Unassigned
UnassignedReporter
kurtkashiComponents
Priority
MinorCreated August 6, 2014 at 9:08 PM
Updated May 14, 2015 at 2:56 PM
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html contains a section titled 'Pagination with ROWNUM' where an example is given on some possible scenarios that can occur when using rownum in oracle.
Specifically looking at "One important thing about using this pagination query is that the ORDER BY statement should order by something unique. If what you are ordering by is not unique, you should add something to the end of the ORDER BY to make it so. If you sort 100 records by SALARY, for example, and they all have the same SALARY value, then specifying rows 20 to 25 does not really have any meaning. In order to see this, use a small table with lots of duplicated ID values...."
Example oracle sql from link using oracle rowid:
I have attached an example test project which is based on example in link and uses hibernate. Test runs similar sql and shows the differences when rowid is appended at the end of order by.
Can always add another order by that is on a unique column like link states, but was wondering if this might be something to always add to sql with order by when using oracle and hibernate setMaxResults/setFirstResult.