Strange ordering error (is that an error?) In postgres when ordering two columns with the same values

Question

I have the following request in postgres:

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

The record in 20th place has identical session_time for the 21st record.

This query returns 20 results, however, if you compare the results with the entire database, the query returns the results of 1-19th and 21st, skipping 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 error occurred? How is Postgres order identical when using offsets and limits? Is this a coincidence? Is this a bug with postgres?

Answer 1

It's not a mistake. The limit and the shift occur after ordering and are not determined which rows are selected in one case compared to the other. In general, you want to have a tie-break so that your order is stable and deterministic (I prefer to use unique tie-breaks, even when I have no limit or offset restrictions, so that every time it runs, every request is the same) .

If you are paginating, add the primary key or surrogate key to the sort as a tie-break. This is really the best way.