SELECT * и SELECT column возвращает строки в разном порядке

Рейтинг: 0Ответов: 2Опубликовано: 19.01.2023

Есть запрос №1:

WITH cte1 AS
  (SELECT *,
          ROW_NUMBER() OVER (ORDER BY column1) AS rn
   FROM table)
SELECT column0
FROM cte1
WHERE rn = 1022;

есть запрос №2:

WITH cte1 AS
  (SELECT column0,
          ROW_NUMBER() OVER (ORDER BY column1) AS rn
   FROM table)
SELECT column0
FROM cte1
WHERE rn = 1022;

PostgreSQL, в column0 нет NULL-ов, значения уникальны.

Но, почему то, при SELECT * и SELECT column0 в итоге я получаю разные значения на rn 1022 (не только на 1022, это просто для примера). Сортировка в cte1 одна и та же, ничего не меняется, кроме количества колонок. Эта какая-то особенность SQL или с чем может быть связано?

sql postgresql
Источник: Stack Overflow на русском

Ответы

▲ 5Принят

Да, это особенность SQL. Если у вас явно не указана сортировка, то база данных может вернуть строки в любом порядке. Как ей захочется. И это может зависить не только от звездочки в запросе, но и просто от времени когда делается запрос.

Хотите избежать неоднозначности - указывайте сортировку в явном виде.

▲ 2

Проблема возникает из-за того, что сортировка по column1 в вашем случае неоднозначна т.е. в этом поле есть дубликаты и отсортировать по column1 можно разными способами, что БД и делает при разных запросах.

Вот пример:

column0 | column1
--------+---------
  A     |      1
  B     |      1

Если выполнить запрос из CTE, до любой порядок строк будет правильным и если вы будете выбирать, скажем, rn=1 то вы можете получить в результате и A и B.

Чтоб этого избежать нужно задавать сортировку по полям, которые однозначно задают порядок. Обычно достаточно включить последним (т.е. самым низкоприоритетным) полем в сортировке уникальное поле (например, первичный ключ), что-то вроде такого:

SELECT column0,
   ROW_NUMBER() OVER (ORDER BY column1, id) AS rn
FROM table

В этом случае порядок всегда будет определен однозначно, т.к. для записей с одинаковыми значениями column1 сортировка будет происходить по полю id.