ClickHouse DB::Exception: OFFSET/FETCH requires ORDER BY

The "DB::Exception: OFFSET/FETCH requires ORDER BY" error in ClickHouse occurs when you use the SQL standard OFFSET ... FETCH clause without an accompanying ORDER BY. The OFFSET_FETCH_WITHOUT_ORDER_BY error code enforces the SQL standard requirement that result ordering must be defined before rows can be meaningfully skipped or limited.

Impact

The query fails immediately without returning any results. This is a syntax-level validation error, so no server resources are consumed beyond parsing. It typically affects applications or BI tools that generate pagination queries using the SQL standard OFFSET/FETCH syntax rather than ClickHouse's native LIMIT/OFFSET syntax.

Common Causes

1. Using OFFSET ... ROWS FETCH NEXT ... ROWS ONLY syntax without specifying ORDER BY
2. A BI tool or ORM generating SQL standard pagination queries that omit the ORDER BY clause
3. Migrating queries from databases like SQL Server or PostgreSQL where OFFSET/FETCH behavior without ORDER BY may differ
4. Removing the ORDER BY clause during query optimization without realizing OFFSET/FETCH depends on it

Troubleshooting and Resolution Steps

1. Add an ORDER BY clause to your query before the OFFSET/FETCH:

   -- This will fail:
   SELECT * FROM your_table
   OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
   
   -- This works:
   SELECT * FROM your_table
   ORDER BY id
   OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
   

2. Alternatively, switch to ClickHouse's native LIMIT ... OFFSET syntax, which does not require ORDER BY:

   SELECT * FROM your_table
   LIMIT 5 OFFSET 10;
   

3. If you are using a BI tool or ORM that generates the OFFSET/FETCH syntax, configure it to always include ORDER BY in paginated queries, or switch the SQL dialect to use LIMIT/OFFSET instead.

4. If you genuinely do not care about row order but need to paginate, add an ORDER BY on a column with a clustered or primary key index for minimal performance impact:

   SELECT * FROM your_table
   ORDER BY tuple()
   OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
   

   Note that ORDER BY tuple() effectively means no sorting, but satisfies the syntax requirement.

Best Practices

• Prefer ClickHouse's LIMIT ... OFFSET syntax over the SQL standard OFFSET/FETCH, as it is more natural in ClickHouse and does not mandate ORDER BY.
• Always include an explicit ORDER BY when paginating results to ensure consistent and predictable page boundaries.
• When using ORMs or query builders, verify the SQL dialect is configured for ClickHouse to avoid SQL standard constructs that may cause issues.
• For high-offset pagination, consider keyset pagination (using WHERE filters on the last seen value) instead of OFFSET, which requires scanning skipped rows.

Frequently Asked Questions

Q: Why does ClickHouse require ORDER BY with OFFSET/FETCH but not with LIMIT/OFFSET?
A: The OFFSET/FETCH syntax follows the SQL standard, which mandates ORDER BY because skipping rows without a defined order is non-deterministic and considered a programming error. ClickHouse's LIMIT/OFFSET is a non-standard extension that is more lenient.

Q: Is there a performance difference between OFFSET/FETCH and LIMIT/OFFSET?
A: There is no meaningful performance difference. Both approaches skip and return the same number of rows. The performance characteristics depend on the ORDER BY clause and the underlying table engine, not the pagination syntax.

Q: Can I disable this check?
A: No, ClickHouse does not provide a setting to bypass the OFFSET/FETCH ORDER BY requirement. Use the LIMIT/OFFSET syntax if you want to skip rows without ordering.