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.

You are using ORDER BY anon_1.rating_points, so why would you expect the anon_1_id column to have any relevance to the records which are being returned? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 20, 2017 at 5:42
@TimBiegeleisen, yeah, I know, but my data starting row 5 has rating_points 1000, only first 5 have higher rating_points. So I expect result be consistent with my first query — yerassyl
– yerassyl, Commented Jun 20, 2017 at 5:45
Your sample output is missing the ratings column and is also illegible. Please edit your question and show us meaningful output, and then point out why you think this is wrong. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 20, 2017 at 5:46

Tim Biegeleisen · Accepted Answer · 2017-06-20 05:52:23Z

21

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.

answered Jun 20, 2017 at 5:52

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yerassyl Over a year ago

Thanks a lot :), I was sure that it is something so simple that I am missing.

Collectives™ on Stack Overflow

Postgres LIMIT/OFFSET strange behaviour

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related