[
https://issues.apache.org/jira/browse/OPENJPA-2416?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Andreas Mader updated OPENJPA-2416:
-----------------------------------
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 in 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
was:
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 in 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
> setFirstResult/setMaxResults (Paging) with Oracle
> -------------------------------------------------
>
> Key: OPENJPA-2416
> URL: https://issues.apache.org/jira/browse/OPENJPA-2416
> Project: OpenJPA
> Issue Type: Improvement
> Components: query
> Affects Versions: 2.1.1
> Reporter: Andreas Mader
>
> 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 in 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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
