postgresqlpsqlpostgresql-16

PostgreSQL: Query with ORDER BY, LIMIT, and OFFSET consistently returns the same record at the end of the result set regardless of OFFSET value

Our team has sat on this for a while and struggling to understand what is going. When ordering by created_at and all the created_ats are the same for the records we see the same record appearing as the last record, regardless of the offset.

Please can someone explain how this is possible? I get that there is an issue with the created_ats all being identical but then surely we would see inconsistent results.

See screenshots attached 1st Query

SELECT * FROM order_supplier_invoices
WHERE supplier_invoice_id = 20340
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;

1st Query

2nd Query

SELECT * FROM order_supplier_invoices
WHERE supplier_invoice_id = 20340
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

Second Query

The record with ID: 101810 appears at the bottom of both queries. One thing we did notice is that when we put a select distinct or instead ordered by the id it gave us expected results

Solution

  • Looks like the issue is that all of the rows shown have the same value for the ordered column (created_at), which leads to inconsistent result order.

    From the docs on LIMIT and OFFSET:

    When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows. You might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY.

    Emphasis mine.

    While this paragraph puts the emphasis on including an ORDER BY clause at all, the important part for this case is "constrains the result rows in a unique order." Your ORDER BY clause does not have this property due to duplicate values. SQL does not make guarantees about ordering without an ORDER BY clause or when ordered values are equal. I can't find a mention of this in the docs but I have found these StackOverflow questions: How does order by clause works if two values are equal? and Is PostgreSQL order fully guaranteed if sorting on a non-unique attribute?

    In short, you need to add a secondary order on another column. I'd pick the id column:

    SELECT * FROM order_supplier_invoices
WHERE supplier_invoice_id = 20340
ORDER BY created_at DESC, id
LIMIT 10 OFFSET 10;