[OPENJPA-2416] setFirstResult/setMaxResults (Paging) with Oracle

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Not A Problem
• Affects Version/s: 2.1.1
• Component/s: query
• Assignee: Unassigned
• Reporter: Andreas Mader
• Created: 29/Jul/13 15:13
• Updated: 31/Jul/13 12:33
• Resolved: 31/Jul/13 12:32

Description

We are using OpenJPA with Oracle 11g Database. For Paging we use setFirstResult and setMaxResults to Page through the result set.

Lets take an example: PageSize 25.

first Statement: firstResult: 0; maxResults: 26 (1 more to check if it is truncated)
SQL generated:
SELECT * FROM ([my statement]) WHERE ROWNUM <= 26

second Statement: firstResult: 25; maxResults: 26
SQL generated:
SELECT * FROM (SELECT r.*, ROWNUM RNUM FROM ([my statement]) r WHERE ROWNUM <= 50) WHERE RNUM > 25

The way limiting the resultset for paging is sometimes not correct. The last result of the statement should be equal to the first result of the new page - but it isn't always. If the sorting is done on a non-unique column in the database, there are different sortings in the resultset of these two statements. I don't know why it is done this way, I would prefer the following statement (this works for all values of firstResult and maxResults with non-unique sort column):

SELECT outer.* FROM (
SELECT ROWNUM rn, inner.* FROM
([my statement])
inner)
outer WHERE outer.rn > 25 AND outer.rn <= 50