E0028 limit-without-order-by

LIMIT or OFFSET without ORDER BY.

What it reports

A query with LIMIT, OFFSET or FETCH FIRST and no ORDER BY, also in subqueries and CTEs. Which rows it returns is up to the database and can change between runs; with OFFSET pagination, pages can repeat or skip rows.

Example

SELECT id, total FROM orders LIMIT 20 OFFSET 40;

warning[E0028]: LIMIT without ORDER BY returns an arbitrary set of rows
  --> q.sql:1:36
    |
  1 | SELECT id, total FROM orders LIMIT 20 OFFSET 40;
    |                                    ^^
    = help: Add an ORDER BY clause that puts the rows in a fixed order

Fixed:

SELECT id, total FROM orders ORDER BY id LIMIT 20 OFFSET 40;

Notes

The query of an EXISTS (...) is not reported, since which of its rows come back doesn't matter. (SELECT ... ORDER BY id) LIMIT 5 counts as ordered. The rule doesn't check that the ORDER BY columns are unique, so rows that tie can still come back in any order.

Configuring

This rule is off by default. To enable it:

# sqlsift.toml
[rules]
limit-without-order-by = "warn"   # or "error"

Or on the command line: -W limit-without-order-by, or -W restriction for every restriction rule. The examples use the schema on the Rules page.