Postgres LIMIT/OFFSET strange behaviour

Question

I am using PostgreSQL 9.6. I have a query Like this:

SELECT anon_1.id AS anon_1_id, anon_1.is_valid AS anon_1_is_valid, anon_1.first_name AS anon_1_first_name, anon_1.last_name AS anon_1_last_name,
anon_1.patronymic_name AS anon_1_patronymic_name,
anon_1.experience AS anon_1_experience, anon_1.user_id AS anon_1_user_id, anon_1.rating_points as rating_points

FROM (SELECT DISTINCT ON (doctors.id) doctors.id AS id, doctors.created_at AS created_at, doctors.updated_at AS updated_at, doctors.is_valid AS is_valid, doctors.pretty_url AS pretty_url, doctors.first_name AS first_name, doctors.last_name AS last_name, doctors.patronymic_name AS patronymic_name, doctors.phone AS phone, doctors.birthday AS birthday, doctors.avatar AS avatar, doctors.experience AS experience, doctors.science_degree AS science_degree, doctors.sex_id AS sex_id, doctors.yclients_staff_id AS yclients_staff_id, doctors.user_id AS user_id, doctor_has_specialties.rating_points AS rating_points, clinic_branch_has_doctors.price AS price, clinic_branch_has_doctors.doctor_type AS doctor_type, clinic_branch_has_doctors.is_house_call AS is_house_call, clinic_branch_has_doctors.house_call_price AS house_call_price 
FROM doctors
      JOIN doctor_has_specialties ON doctors.id = doctor_has_specialties.doctor_id 
      JOIN clinic_branch_has_doctors ON doctor_has_specialties.id = clinic_branch_has_doctors.doctor_has_specialty_id 
      JOIN clinic_branches ON clinic_branches.id = clinic_branch_has_doctors.clinic_branch_id 
      JOIN city_areas ON city_areas.id = clinic_branches.city_area_id 
      JOIN cities ON cities.id = city_areas.city_id 
WHERE doctors.is_valid = true 
      AND clinic_branch_has_doctors.is_valid = true 
      AND clinic_branches.is_valid = true 
      AND doctor_has_specialties.specialty_id = 1
      AND cities.id = 1) AS anon_1 ORDER BY anon_1.rating_points DESC 
 LIMIT 100 OFFSET 0

Here the most important part of the query is the last one, LIMIT and OFFSET. When I run this query I get all my data up to 100 rows. Everything is fine not. Here is the screenshot from pgAdmin:

Here notice row with id 20. Now If I try to OFFSET 10, I get my data from 11th row as expected which is object with id 22. Everything is fine too. But If I try OFFSET 10 LIMIT 10, I get strange result. Mainly row with id 20 appears too, but it shouldn't. Here is the screenshot:

Have no idea what is wrong with that. Is it Postgres bug? Or I am doing something wrong.

Answer 1

There is no "bug" at work here. You specified the following ordering, which was used when LIMIT and OFFSET were being applied:

ORDER BY anon_1.rating_points DESC

However, in the case two or more records are tied with the same rating_points values, Postgres makes no guarantee about what the ordering will be. This is why you are seeing a user with anon_id_1 of 20 apparently moving around. Postgres has done nothing wrong; it has honored your request to order by the rating_points, but you never told it what to do in the case of tie.

To resolve this, you could add a second condition to the ORDER BY:

ORDER BY
    anon_1.rating_points DESC,
    anon_id_1

This would break the tie with regard to ordering, and, assuming anon_id_1 is a primary key, the results would appear to be stable after making this change.