Strange ordering bug (is it a bug?) when ordering two columns with identical values

Question

I have the following query in postgres:

SELECT * 
FROM "bookings"
WHERE ("bookings".client_id = 50) 
ORDER BY session_time DESC 
LIMIT 20 OFFSET 0

The record in the 20th place is has an identical session_time to the 21st record.

This query returns 20 results, however if you compare the results to the whole database the query returns the 1st-19th results and the 21st, skipping over the 20th.

This query can be fixed by adding, "id" to the order:

SELECT * 
FROM "bookings" 
WHERE ("bookings".client_id = 50) 
ORDER BY session_time DESC, id 
LIMIT 20 OFFSET 0

However I was wondering how this bug occurred? How does postgres order identical filed when using offsets and limits? Is it random? Is it a bug with postgres?

Answer 1

This is not a bug. The limit and offset happen after ordering and it is not deterministic which rows are selected in one case vs another. In general you want to have a tiebreaker so that your ordering is stable and deterministic (I prefer to use unique tiebreakers even when I don't have limit or offset issues in order to ensure the query is the same each time it is run).

If you are doing pagination, add the primary key or surrogate key to the sort as a tiebreaker. That is really the best way.