Moodle DML read exception on a bounded query: offset pagination without a unique ORDER BY

Failure condition

A bounded Moodle DML query that is malformed or non-portable surfaces as a DML read exception raised by the database layer at execution time, not as a validation error at the call site. The same path governs offset pagination over a large table.

Goal and constraints

The goal is to page through a large result set using core APIs: get_records_sql()/get_records_select() with limitfrom and limitnum, get_recordset() for streaming, and table_sql plus paging_bar for rendering. Raw LIMIT/OFFSET text is not portable across supported drivers, and a recordset must be exhausted or closed.

Two decisions remain unresolved. Offset pagination is stable only when ORDER BY is deterministic; Moodle does not enforce a unique ordering, so a non-unique sort key can repeat or skip rows between pages. paging_bar also needs a total count, typically a second COUNT query over the same predicate, and core provides no keyset pagination.

Behaviour is described for recent Moodle 4.x releases; confirm method names and defaults against the deployed version.

Open questions

• Does the core table and paging path require a unique ORDER BY for stable pages, or is a non-unique key acceptable?
• When the count query is expensive, what trade-off is intended between full paging_bar and next/previous-only navigation?
• Can keyset pagination be expressed through the core table API, or does it need custom SQL and rendering?