Resolution: Not A Problem
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)
SELECT * FROM ([my statement]) WHERE ROWNUM <= 26
second Statement: firstResult: 25; maxResults: 26
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
outer WHERE outer.rn > 25 AND outer.rn <= 50