Query with offset returns overlapping data sets

Question

Initial attempts at getting a very simple pagination, using fetch n rows and then a subsequent call with offset, gives overlapping entries in Oracle.

I was expecting the following to give me two unique sets of results. 1-100 and then 101-200 of the results that would have been returned if the first line had been set with a limit of 200.

select  * from "APPR" /*+ index(APPR APPR_IDX01) */ where  ("APPROVER" = 'A') or  ("APPROVER" > 'A') order by "APPROVER"  fetch first 100 rows only ;

select  * from "APPR" /*+ index(APPR APPR_IDX01) */ where  ("APPROVER" = 'A') or  ("APPROVER" > 'A') order by "APPROVER" offset 100 rows fetch next 100 rows only ;

So if there are 150 items for approver A the first results should be:

• A, item1
• ....
• A, item100

The subsequent call (offset by 100) giving

• A, item101
• ...
• A, item150
• B, item1
• B, item2
• ....
• B, item201

Unfortunately the second set contains some entries from the first batch of values. Probably a really silly error, but I can't find an explanation as to why this should happen.

---- Updated as a result of comments The Primary key consists of Approver and several other fields which together form a composite and unique primary.

The code will be called through ODBC and will be used on Oracle and MySQL back-end.

Answer 1

In Oracle, if you make "order by" to a column containing same values (like you have - 'A', 'A', 'A' ...) the order of records inside 'A' values will be random.

Please try to change your queries to ... order by "APPROVER", rowid ...

Answer 2

Presumably, APPROVER is not a unique column. Since there may be duplicates, the order by claus is not stable, and the offset clause might generate duplicates.

A simple solution is to add more columns to the order by to break the ties. Assuming that (approver, item) is a unique set of columns, that would be:

select  * 
from appr
where approver = 'A' or approver > 'A'
order by approver, item
fetch first 100 rows only
-- then: offset 100 rows fetch next 100 rows only

Notes:

• there is no need to surround all-caps identifiers (tables or column names) with double quotes: that's the default in Oracle already

• parentheses around the or conditions are superfluous in this simple case

• if approver is always one character long, then the where clause can be simplified as where approver >= 'A'

• use index hints only if you really know why you are doing it (I am not saying you don't, but I removed it, just in case); most of the time, the database knows better