LIMIT/OFFSET with DISTINCT and ORDER BY: inconsistent behavior across SQLAlchemy dialects

Direct Answer

SQLAlchemy does not add a cross-dialect semantic rule when you combine distinct(), order_by(), limit(), and offset(). It compiles the Core select using dialect-specific LIMIT/OFFSET syntax, but the database backend enforces SQL semantics. In standard SQL, DISTINCT is logically applied before ORDER BY and OFFSET/LIMIT, so duplicates are removed before pagination.

Confirmed Facts vs. Likely Explanation

Confirmed: SQLAlchemy 1.4+/2.x preserves the logical order of the construct. The library does not rewrite ORDER BY columns to satisfy DISTINCT requirements, nor does it inject a hidden subquery.

Likely explanation for divergence: PostgreSQL and MySQL commonly require every ORDER BY expression in a SELECT DISTINCT query to appear in the select list. SQLite and MariaDB may accept non-selected ORDER BY columns or choose an arbitrary value from collapsed rows. Because SQLAlchemy changes only LIMIT/OFFSET rendering—not this backend rule—the same Core select can produce different row ordering, errors, or duplicate handling across dialects.

Steps for Deterministic Pagination

If every ORDER BY term is already in the SELECT DISTINCT projection, keep a single query. Order by the selected DISTINCT columns themselves to create a total order over the distinct result:

select(User.name, User.email).distinct().order_by(User.name, User.email).limit(10).offset(5)

If ORDER BY depends on columns eliminated by DISTINCT, use a two-step formulation. Select the distinct key with a deterministic aggregate in an inner subquery or CTE, then apply ORDER BY, LIMIT, and OFFSET only on the outer query:

inner = select(Order.user_id, func.min(Order.created_at).label("first_at")).group_by(Order.user_id).subquery()
outer = select(inner.c.user_id, inner.c.first_at).order_by(inner.c.first_at, inner.c.user_id).limit(10).offset(5)

This is a SQL design pattern, not a SQLAlchemy pagination flag.

Verification

Compile the same select for each dialect and inspect whether DISTINCT is preserved and where LIMIT/OFFSET is placed:

from sqlalchemy.dialects import postgresql, sqlite, mysql
print(stmt.compile(dialect=postgresql.dialect()))
print(stmt.compile(dialect=sqlite.dialect()))
print(stmt.compile(dialect=mysql.dialect()))

Run the query on each backend with duplicate rows and ties, then compare page 1 and page 2 key sequences for equality and non-overlap. Pin SQLAlchemy and database versions in tests because dialect compilation and backend strictness can change.

The Missing Diagnostic Detail

One detail changes the recommendation: whether every ORDER BY term is part of the SELECT DISTINCT projection. If yes, a single query is sufficient. If no, switch to the inner-distinct/outer-order pattern above.