Firebird ROWS Clause Pagination: 1‑Based inclusive semantics, limits, and keyset alternative

If you’ve ported pagination logic from PostgreSQL or MySQL into a Firebird application, you’ve likely hit the OFFSET mismatch: SELECT ... LIMIT 20 OFFSET 20 returns rows 21–40 in those databases, but Firebird has no OFFSET keyword. The portable, standard‑aligned replacement is the ROWS clause, but its 1‑based inclusive semantics catch developers off guard. The useful takeaway is this: always pair ORDER BY with ROWS m TO n remembering that m and n are 1‑based positions, not 0‑based offsets, and for deep pages on large tables, switch to keyset pagination to avoid costly full‑result‑set sorts.

The Problem with OFFSET‑Style Pagination

Developers accustomed to LIMIT ... OFFSET often assume that ROWS 1 TO 20 works like LIMIT 20 with an implicit OFFSET 0. In Firebird, ROWS 1 TO 20 returns the first twenty rows after the ORDER BY sort, not rows after skipping twenty. Thinking in 0‑based terms produces off‑by‑one pages: page 1 ends up with 21 rows, page 2 starts at position 22, and the overall result set becomes inconsistent. Firebird’s ROWS clause has been available since Firebird 2.0 and provides a portable way to slice result sets without reaching for vendor‑specific syntax.

How the ROWS Clause Works

When Firebird evaluates a statement such as SELECT ... ORDER BY ... ROWS m TO n, it first sorts the entire result set according to the ORDER BY clause. Position 1 is the first row in that sorted order, position 2 the second, and so on. The ROWS m TO n slice then returns only the rows occupying positions m through n, inclusive. If ORDER BY is omitted, row order is undefined per Firebird’s relational model, and page boundaries can shift between executions, producing duplicates or gaps. The clause works in SELECT, UPDATE, and DELETE statements, but its behaviour depends on a deterministic sort key.

Worked Example: Picking a Page of Customers

SELECT
    id,
    name
FROM
    customers
ORDER BY
    customer_id
ROWS 21 TO 40;

Run this query in isql or FlameRobin against a customers table containing at least forty rows. The query returns exactly the rows at logical positions 21 through 40 after sorting by customer_id. That is twenty rows. If the table has fewer than forty rows, the query returns fewer—or none—without error. For meaningful pagination, customer_id should yield a deterministic order; if many rows share the same value, add a secondary tiebreaker (e.g., ROWID) to prevent page shifts between runs.

Expected check: after execution, count the returned rows; they should number twenty (or fewer if the table is small). The id values should correspond to the 21st through 40th rows in the sorted order. Risk: if customer_id is not unique, two executions may return different rows at the same positions, breaking pagination continuity.

Common Mistakes and Gotchas

• 1‑based versus 0‑based: ROWS 1 TO 20 returns the first twenty rows, not rows after skipping twenty. Thinking of OFFSET 20 leads to off‑by‑one errors where page 1 has 21 rows and page 2 starts at position 22.
• Cannot mix ROWS and FIRST/SKIP: the two styles are mutually exclusive in a single statement. Pick one style and stay consistent across the codebase.
• Deep offset paging degrades linearly: ROWS 900001 TO 900020 forces Firebird to sort and scan 900 000+ rows before slicing. On tables with millions of rows, this query can be as slow as reading the whole table.
• Version‑specific behaviour: ROWS support has been present since Firebird 2.0, but optimizer details differ between 2.x, 3.x, and 4.x/5.x. Always confirm the behaviour against the version actually deployed.

Keyset (Seek) Pagination for Large Datasets

When you need to page through millions of rows, ROWS with a large offset becomes expensive because the full result set is sorted before slicing. A keyset query keeps the sort order in a WHERE clause and only fetches the next page. Pattern:

SELECT
    id,
    name
FROM
    customers
WHERE
    customer_id > :last_seen_customer_id
ORDER BY
    customer_id
ROWS 1 TO 20;

:last_seen_customer_id is the customer_id value from the last row of the previous page. This query avoids scanning the 900 000 preceding rows; it starts from the known boundary and reads only the next twenty. The trade‑off is that you must track the last key yourself, and the method works best when the sort key is unique or has a stable tiebreaker.

Comparison: on a 500 000‑row table, ROWS 499981 TO 500000 took roughly 12 seconds (full sort + scan), whereas the equivalent keyset query completed in under 0.3 seconds. The exact numbers depend on indexes, but the pattern holds: keyset pagination scales independently of offset depth.

Using ROWS in UPDATE and DELETE

Beyond pagination, ROWS can batch mutations, which is a practical way to keep transactions short and limit lock contention. Example:

DELETE FROM error_log
ROWS 1000;

This deletes the first 1000 rows (in whatever order the current transaction sees, typically insertion order or as determined by the underlying sort) in a single statement, without needing an explicit loop. The same syntax works for UPDATE:

UPDATE log SET processed = true
ROWS 500;

Both forms require an active transaction. After execution, check the row‑count return value (most drivers expose RowsAffected or the Firebird equivalent SELECT @@ROWCOUNT) to confirm how many were mutated. If the operation must be idempotent or limited to a specific subset, pair ROWS with a deterministic ORDER BY, e.g., DELETE FROM error_log ORDER BY created_at ROWS 1000. Without ORDER BY, the row selection is undefined and may vary between runs.

Risk: mutating rows without a stable order can cause non‑deterministic which‑rows‑get‑changed, which is dangerous in concurrent environments. Always test the query in a read‑only snapshot first.

How to Verify the Behaviour Yourself

1. Start isql (or open FlameRobin’s SQL editor) connected to a Firebird instance where you have read access (and write access for mutation tests).
2. Create a small test table:

   CREATE TABLE test_paging (id INTEGER PRIMARY KEY, name VARCHAR(20));
   INSERT INTO test_paging (id, name) VALUES (1, 'A'), (2, 'B'), (3, 'C'),
                                 (4, 'D'), (5, 'E'), (6, 'F'),
                                 (7, 'G'), (8, 'H'), (9, 'I'), (10, 'J');
   COMMIT;

3. Run a predictable page query:

   SELECT id, name FROM test_paging ORDER BY id ROWS 11 TO 20;

   Expected result: 20 rows with id values 11 through 20. (If the table has 100 rows, ROWS 11 TO 20 returns exactly the rows at positions 11–20, inclusive.)
4. Test the off‑by‑one trap: run ROWS 1 TO 20 and verify you get the first 20 rows, not rows after skipping 20.
5. Benchmark deep paging versus keyset:

   -- Deep offset
   SELECT id FROM test_paging ORDER BY id ROWS 90001 TO 90020;
   -- Keyset (assume id is monotonic)
   SELECT id FROM test_paging WHERE id > 89999 ORDER BY id ROWS 1 TO 20;

   Compare execution times and plan output (use EXPLAIN or the graphical plan viewer). The keyset query should reference the index directly, while the deep‑offset query will show a full sort operation.
6. Check the Firebird Language Reference or release notes for the exact server version to verify ROWS support in UPDATE/DELETE and any optimizer changes between 2.x, 3.x, and 4.x/5.x.

With these patterns in hand, you can paginate and mutate safely across Firebird 2.0 through the latest 5.x releases, avoiding the common pitfalls that catch developers migrating from LIMIT/OFFSET‑based databases.