Postgres LIMIT/OFFSET 奇怪的行为

Question

Postgres LIMIT/OFFSET 奇怪的行为

6

我正在使用PostgreSQL 9.6。我有一个查询如下：

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

这里查询最重要的部分是最后一个，即LIMIT和OFFSET。当我运行这个查询时，我得到所有的数据，最多100行。一切都很好，如下图所示：

请注意id为20的行。现在，如果我尝试OFFSET 10，我会得到预期的从第11行开始的数据，即id为22的对象。一切也很好。但是，如果我尝试OFFSET 10 LIMIT 10，我会得到奇怪的结果。主要是id为20的行也出现了，但它不应该出现。如下图所示：

我不知道出了什么问题。这是Postgres的错误吗？还是我做错了什么？

- yerassyl

1

你正在使用 ORDER BY anon_1.rating_points，那么你为什么期望 anon_1_id 列与返回的记录有任何相关性呢？ - Tim Biegeleisen

@TimBiegeleisen，是的，我知道，但是我的数据从第5行开始有rating_points 1000，只有前5行有更高的rating_points。所以我希望结果与我的第一个查询一致。 - yerassyl

你的样本输出缺少评分列，而且也不清晰可辨。请编辑你的问题，展示有意义的输出，并指出你认为哪里出了问题。 - Tim Biegeleisen

@TimBiegeleisen，我更新了我的截图。 - yerassyl

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tim Biegeleisen · Accepted Answer

这里没有“bug”。您指定了以下顺序，该顺序在应用LIMIT和OFFSET时被使用：

ORDER BY anon_1.rating_points DESC

然而，在多个记录的rating_points值相同时，Postgres不能保证顺序。这就是为什么你看到一个anon_id_1是20的用户似乎会移动位置。Postgres没有做错任何事情；它遵循了你按rating_points排序的请求，但你没有告诉它在出现平局时该怎么办。

要解决这个问题，你可以向ORDER BY添加第二个条件：

ORDER BY
    anon_1.rating_points DESC,
    anon_id_1

这将打破有关排序的平局，假设anon_id_1是主键，则在进行此更改后结果似乎会保持稳定。