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?

If you want deterministic results you need to include a unique tie breaker in your order by. It is the same as having no order by at all and expecting the results to be sorted in a particular order. No guarantees that they will. — Martin Smith
What you are referring to is called a stable sort. A stable sort keeps records in the original order when they have matching keys. There is definitely no requirement for a stable sort in SQL. I would not depend on it in any product. As Martin suggests, use another column as a tie breaker. — Gordon Linoff
This is most definitely not a bug -- you asked for it only ordered by session_time, and the planner will give you the fastest plan that does that; it makes no assumptions about what else you might want to order by that you didn't state. Just for fun, update one of those rows (even to current values) and do a SELECT without an ORDER BY clause. If you want things in a particular order, you need to specify that. — kgrittn
I think the natural human assumption would be that the query returns the same results regardless of what the view is (i.e. the limit). I understand now that this is not a bug, but it is still unexpected behavior. Do all SQL databases behave in this way and is there any reference material out there which might help me get a better grasp on this? — MintDeparture
There is not only no requirement for a stable sort, the standard explicitely states that an order is not guaranteed in such a case (or the absence of an order by) — a_horse_with_no_name

Chris Travers Chris Travers · Accepted Answer · 2013-04-06T16:09:13

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.

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

1 Answers