Oracle not returning expected results when using FETCH NEXT X ROWS ONLY in combination with ORDER BY

Question

When working with Oracle 12c and implementing pagination I came across a problem when using ORDER BY in combination with OFFSET and FETCH NEXT <X> ROWS ONLY.

Example

My testing table test:

+----+---------------------+
| ID |        NAME         |
+----+---------------------+
|  1 | Customer Support    |
|  2 | Property Management |
|  3 | Security            |
|  4 | Security            |
|  5 | Security            |
|  6 | Security            |
|  7 | Tax Compliance      |
+----+---------------------+

I am running following SQL:

SELECT  id
FROM            test
ORDER BY        name ASC   
OFFSET <offset> ROWS   
FETCH NEXT 1 ROWS ONLY;

With these results:

+--------+--------+-----------------+
| Offset | Result | Expected result |
+--------+--------+-----------------+
|      0 |      1 |               1 |
|      1 |      2 |               2 |
|      2 |      3 |               3 |
|      3 |      4 |               4 |
|      4 |      4 |               5 |
|      5 |      4 |               6 |
|      6 |      7 |               7 |
+--------+--------+-----------------+

Question

The question is why is the DB not returning the expected result?

When no ORDER BY is used it works flawlessly. Is this kind of use prohibited? Am I using it wrongly? If so could you please point me to the documentation where it says that this kind of usage is not possible. The version I am using is 12c but the same behaviour applies to 19c.

More example queries

Query with OFFSET only:

SELECT  id
FROM            test
ORDER BY        name ASC   
OFFSET 4 ROWS;

Result:

+----+
| ID |
+----+
|  5 |
|  6 |
|  7 |
+----+

Query with OFFSET and FETCH NEXT 1 ROWS ONLY:

SELECT  id
FROM            test
ORDER BY        name ASC   
OFFSET 4 ROWS
FETCH NEXT 1 ROWS ONLY;

Result:

+----+
| ID |
+----+
|  4 |
+----+

Thank you

Adam

Answer 1

Your ORDER BY clause produces ties since multiple records have the same name. In this case, it is undefined which record will be picked.

From your expected results, I understand that you want a second ordering criteria by id:

SELECT id
FROM test
ORDER BY name, id
OFFSET <offset> ROWS
FETCH NEXT 1 ROWS ONLY;