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

Goal

To paginate a distinct result set in a cross‑dialect application using SQLAlchemy’s core select construct.

Constraints

SQLAlchemy generates dialect‑specific SQL for LIMIT/OFFSET. The interaction of DISTINCT, ORDER BY, and pagination is documented but not fully unified.

Unresolved Behavior

When a query contains SELECT DISTINCT column FROM table ORDER BY column LIMIT 10 OFFSET 5, the generated SQL and the returned ordering can vary between PostgreSQL, SQLite, and MySQL. This divergence may lead to unexpected duplicate elimination or row ordering, breaking deterministic pagination.

Questions

• What rule does SQLAlchemy apply when combining DISTINCT with ORDER BY and LIMIT/OFFSET across different dialects?
• Is there a documented approach to enforce consistent pagination for distinct result sets in SQLAlchemy 1.4+?
• Should developers rely on subqueries or CTEs to achieve deterministic results, or is there a dialect‑agnostic solution?