当对两个具有相同值的列进行排序时，奇怪的排序错误(是错误吗?) [英] Strange ordering bug (is it a bug?) when ordering two columns with identical values

问题描述

我在postgres中有以下查询:

I have the following query in postgres:

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

第20位的记录的session_time与第21条记录的会话时间相同.

The record in the 20th place is has an identical session_time to the 21st record.

此查询返回20个结果，但是，如果将结果与整个数据库进行比较，该查询将返回第1-19个结果和第21个结果，跳过第20个结果.

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.

此查询可以通过在订单中添加"id"来解决:

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

但是我想知道这个错误是怎么发生的?使用偏移量和限制时，postgres如何订购相同的文件?它是随机的吗?是postgres的错误吗?

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?

推荐答案

这不是错误.限制和偏移发生在订购之后，并且不确定在哪种情况下选择哪一行与另一情况下的行.通常，您希望有一个决胜局，以便您的订单稳定且确定(我更喜欢使用唯一的决胜局，即使我没有限制或抵消问题，也可以确保每次运行查询均相同).

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.

这篇关于当对两个具有相同值的列进行排序时，奇怪的排序错误(是错误吗?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！