Question
Does Firebird's ROWS clause return deterministic rows without an ORDER BY?
Ari Moss
0 reputation · 29 Jul 2021, 21:42 UTC
148.2K views
Loading…
0 reputation · 29 Jul 2021, 21:42 UTC
When building a user‑facing list view that relies on Firebird's ROWS n TO m pagination, the application must present the same slice of data on each request to avoid confusing users.
Although the documentation warns that omitting an ORDER BY clause can lead to inconsistent page contents, it does not specify whether the presence of a clustered primary‑key index or the physical storage order influences the row sequence returned by a bare ROWS filter. This uncertainty makes it hard to decide whether relying on the implicit order is acceptable for a stable UI or whether an explicit sort is always required.
Does Firebird guarantee that repeated execution of a query such as SELECT * FROM MyTable ROWS 1 TO 10 returns identical rows when the table has a primary‑key index and no ORDER BY is supplied? If not, what factors (e.g., index layout, page allocation, concurrent DML) affect the order? Can adding an ORDER BY on the primary‑key column eliminate variability without causing a noticeable performance penalty?
34775 reputation · 30 Jul 2021, 08:40 UTC
Firebird does not guarantee that a query such as
SELECT * FROM MyTable ROWS 1 TO 10 will return the same rows on
every execution when no ORDER BY clause is supplied. The order that
appears is effectively the physical storage order, which can change for a
number of reasons.
To obtain a stable slice of data for pagination, always add an explicit
ORDER BY on a unique column, preferably the primary‑key. Firebird can use the existing PK index to satisfy both the sort and the ROWS clause, so the performance penalty is negligible in typical workloads.
firebirdCREATE TABLE Test (id INTEGER PRIMARY KEY, val VARCHAR(20));
INSERT INTO Test (id, val) SELECT seq, 'row' || seq FROM RDB$DATABASE; -- replace with a proper sequence
ORDER BY and compare the result sets.
SELECT * FROM Test ROWS 1 TO 10;
ORDER BY id and verify that the order is identical.
SELECT * FROM Test ORDER BY id ROWS 1 TO 10;
Although the behavior is consistent across recent Firebird releases, earlier versions (pre‑2.5) exhibited more pronounced non‑determinism. If you are using a legacy version, test the above steps to be sure.
Could you let me know which Firebird version you are running? It helps confirm that the recommendation applies to your environment.
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 30 Jul 2021, 01:23 UTC
One premise worth correcting: Firebird has no clustered indexes. Tables are heap-organized — rows sit on data pages in roughly insertion order — and the primary key is a separate B-tree index, so there is no clustered-PK layout that could stabilize a bare ROWS scan.
Even that natural order is not a contract. The optimizer may satisfy a WHERE via a different index, concurrent updates create old row versions, and garbage collection removes dead rows — any of these can reshuffle the stream that ROWS 1 TO 10 slices. The clause only limits output; it never defines order.
Adding ORDER BY pk is the safe fix: the optimizer can walk the PK index in order and skip the sort, which is usually cheap — though for full scans it may still choose a natural scan plus sort, so check the plan. This design is stable across the Firebird versions I know (2.5–5.x), but verify on your build: run the bare query twice while another session inserts and deletes rows, then compare with the ordered variant.