SQL Server применяет ORDER BY .. OFFSET только к подзапросу UNION
В SQL Server 2022 (и более ранних версиях) это ведет себя так, как я и ожидал, в соответствии со стандартом SQL:
select i
from (values (1)) as t (i)
union
select i
from (values (2), (3)) as t (i)
order by i
offset 0 rows
fetch next 1 rows only
Он производит:
|i |
|---|
|1 |
Итак, как и везде, предложение ORDER BY .. OFFSET применяется к результату объединения. Однако когда я вставляю приведенный выше запрос в производную таблицу или CTE, семантика, похоже, меняется (предположим, что фактический запрос намного сложнее):
select *
from (
select i
from (values (1)) as t (i)
union
select i
from (values (2), (3)) as t (i)
order by i
offset 0 rows
fetch next 1 rows only
) t;
Это производит:
|i |
|---|
|1 |
|2 |
Теперь предложение ORDER BY .. OFFSET применяется только ко второму подзапросу UNION. Это заданное поведение? Кажется, я не могу найти авторитетную информацию в документах ORDER BY, которые гласят:
В запросе, использующем операторы UNION, EXCEPT или INTERSECT, ORDER BY допускается только в конце инструкции. Это ограничение применяется только в том случае, если вы указываете UNION, EXCEPT и INTERSECT в запросе верхнего уровня, а не в подзапросе. См. следующий раздел «Примеры».
Но нет ни одного примера, показывающего этот конкретный случай. Синтаксические диаграммы документации SELECT на самом деле не поддерживают ORDER BY в UNION подзапросах, только на верхнем уровне <SELECT statement>, поэтому документация также кажется устаревшей/неполной.
Это известная ошибка?
Я на 99% уверен, что это ошибка SQL Server. Запрос прекрасно работает в Oracle , DB2 , PostgreSQL и MySQL.
@TheImpaler: Это была и моя первая реакция, когда я задал вопрос. Но посмотрите мой ответ, где я показываю пример, намекающий на то, что это сделано намеренно. ORDER BY .. OFFSET кажется локальным для подзапроса UNION, а не глобальным для вывода UNION, когда UNION выполняется в подзапросе. Это не похоже на случайность/баг.
@LukasEder Нужно будет еще раз просмотреть стандарт SQL (zzz), но, насколько я помню, ORDER BY/LIMIT/OFFSET применяется к «комбинированному запросу» (то есть к результату операторов набора), а не к последнему SELECT в комбинированном запросе. Как только у меня появится возможность, я пересмотрю его.
@TheImpaler: Да, именно так это определяет стандарт SQL, и то же самое делает SQL Server для запросов верхнего уровня. Но у SQL Server (исторически Sybase T-SQL) могло быть другое представление о том, как должен работать синтаксис, в зависимости от контекста. T-SQL, как правило, не самый совместимый диалект. Опять же, тот простой факт, что в подзапросе всем UNION подзапросам (включая непоследние) разрешено иметь ORDER BY .. OFFSET предложения, намекает на наличие намерения в этом языковом дизайне, что выглядит просто странно, если только последний UNION подзапрос имеет предложение , не иначе.
Ответы 1
Баг или нет
Вряд ли это ошибка, просто интересный дизайн: как SELECT работает как запрос верхнего уровня (аналогично стандартному SQL), а не как подзапрос. Этот пример показывает, что все подзапросы UNION могут иметь «локальные» предложения ORDER BY .. OFFSET, а завершающее предложение не является глобальным для вывода UNION:
select *
from (
select i
from (values (1), (2)) as t (i)
order by i
offset 0 rows
fetch next 1 rows only
union
select i
from (values (3), (4)) as t (i)
order by i
offset 0 rows
fetch next 1 rows only
) t;
Это производит:
|i |
|---|
|1 |
|3 |
Итак, это намекает на то, что просто отсутствует/несогласованная документация.
Если подзапрос выполняется как запрос верхнего уровня, возникает эта ошибка:
Ошибка SQL [156] [S0001]: неправильный синтаксис рядом с ключевым словом «объединение».
Обходной путь
Просто вложите операцию установки еще раз в производную таблицу:
select *
from (
select *
from (
select i
from (values (1)) as t (i)
union
select i
from (values (2), (3)) as t (i)
) t
order by i
offset 0 rows
fetch next 1 rows only
) t;
Теперь предложение ORDER BY .. OFFSET снова ведет себя и применяется ко всему выводу UNION, давая ожидаемый результат:
|i |
|---|
|1 |
см.: DBFIDDLE, который я создал, но опоздал с ответом 😉
@Luuk: Спасибо за попытку. Я уже знал этот обходной путь и задокументировал его здесь для себя в будущем, который наверняка столкнется с этим вопросом снова, согласно рекомендациям: stackoverflow.blog/2011/07/01/…
Stackoverflow говорит то же самое: Могу ли я ответить на свой вопрос? 😉
Документы находятся на Github, так что, по крайней мере, вы можете поднять проблему и воспользоваться ими.
@Charlieface: Я предоставил отзыв. Я не буду отправлять пиар, потому что это кажется более стратегическим, чем простая опечатка.
Нет, это ошибка SQL Server; результат вообще не имеет смысла. Запрос прекрасно работает в Oracle , DB2 , PostgreSQL и MySQL.
@TheImpaler: Диалектам разрешено реализовывать разную семантику для разных функций языка. В противном случае поведение Oracle 'a' || NULL = 'a' тоже было бы ошибкой, но это просто другая реализация. Я мог бы привести вам сотни подобных примеров.
Я сделал! Выборка должна это разрешать.