These rows are called *pears* in the SQL Standard. The SQL Standard doesn't require any exact ordering of peers and doesn't require any stable ordering rules for them. These things are implementation-dependent.
If some particular DBMS doesn't have own documented ordering rules for pears (I never saw such rules anywhere), your queries rely on undefined behavior. PostgreSQL may and can return peers in any order: # create table test(a int, b int); # create index test_b on test(b); # insert into test values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (0, 4), (-1, 4); # select * from test order by a offset 0 rows; a | b ---+--- -1 | 4 0 | 4 1 | 3 1 | 2 2 | 4 2 | 3 3 | 4 (7 rows) # select * from test order by a offset 0 rows fetch first 3 rows only; a | b ---+--- -1 | 4 0 | 4 1 | 2 (3 rows) # select * from test order by a offset 3 rows fetch first 3 rows only; a | b --+--- 1 | 2 2 | 4 2 | 3 (3 rows) Row (1, 3) is missing. Results in H2 are slightly different: # select * from test order by a offset 0 rows fetch first 3 rows only; A | B -- | - -1 | 4 0 | 4 1 | 3 (3 rows) # select * from test order by a offset 3 rows fetch first 3 rows only; A | B - | - 1 | 3 2 | 4 2 | 3 In H2 row (1, 2) is missing. But there is no bug in PostgreSQL or H2. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/5c04695c-ea06-4a3d-b0d7-ad07190738c9n%40googlegroups.com.
