PostgreSQL: запрос с ORDER BY, LIMIT и OFFSET последовательно возвращает одну и ту же запись в конце набора результатов независимо от значения OFFSET
Наша команда некоторое время размышляла над этим и пыталась понять, что происходит. При упорядочивании по созданному_ату и тому, что все созданные_аты одинаковы для записей, мы видим ту же запись, что и последняя запись, независимо от смещения.
Пожалуйста, может кто-нибудь объяснить, как это возможно? Я понимаю, что существует проблема с тем, что все созданные объекты идентичны, но тогда мы наверняка увидим противоречивые результаты.
Смотрите скриншоты во вложении 1-й запрос
SELECT * FROM order_supplier_invoices
WHERE supplier_invoice_id = 20340
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;
2-й запрос
SELECT * FROM order_supplier_invoices
WHERE supplier_invoice_id = 20340
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;
Запись с идентификатором: 101810 появляется внизу обоих запросов. Одна вещь, которую мы заметили, это то, что когда мы помещаем отдельный элемент select или вместо этого упорядочиваем его по идентификатору, это дает нам ожидаемые результаты.
Ответы 1
Похоже, проблема в том, что все показанные строки имеют одинаковое значение для упорядоченного столбца (created_at), что приводит к противоречивому порядку результатов.
Из документации по LIMIT и OFFSET:
При использовании
LIMITважно использовать предложениеORDER BY, которое ограничивает строки результатов в уникальном порядке. В противном случае вы получите непредсказуемое подмножество строк запроса. Вы можете попросить строки с десятой по двадцатую, но в каком порядке? Порядок заказа неизвестен, если вы не указалиORDER BY.
Акцент мой.
Хотя в этом абзаце вообще делается упор на включение предложения ORDER BY, важной частью этого случая является «ограничение строк результатов в уникальном порядке». Ваше предложение ORDER BY не имеет этого свойства из-за повторяющихся значений. SQL не дает гарантий относительно порядка без предложения ORDER BY или равенства упорядоченных значений. Я не могу найти упоминание об этом в документации, но нашел вопросы по StackOverflow: Как работает предложение order by, если два значения равны? и Полностью ли гарантирован порядок в PostgreSQL при сортировке по неуникальному атрибуту?
Короче говоря, вам нужно добавить дополнительный порядок в другой столбец. Я бы выбрал столбец id:
SELECT * FROM order_supplier_invoices
WHERE supplier_invoice_id = 20340
ORDER BY created_at DESC, id
LIMIT 10 OFFSET 10;