openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andreas Mader (JIRA)" <j...@apache.org>
Subject [jira] [Created] (OPENJPA-2416) setFirstResult/setMaxResults (Paging) with Oracle
Date Mon, 29 Jul 2013 15:13:52 GMT
Andreas Mader created OPENJPA-2416:
--------------------------------------

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

Mime
View raw message