Странная ошибка с UNION и ORDER BY: ORA-00904: "X": invalid identifier

Question

Этот запрос полностью стандартный практически в любой БД (не считая таблицы dual):

select 'A' as x from dual union all
select 'B'      from dual
order by x asc;

X
-
A
B

А этот запрос тоже является вполне стандартным SQL, но не работает в Oracle:

select 'A' as x from dual union all
select 'B'      from dual union all
select 'C'      from dual union all
select 'D'      from dual union all
select 'E'      from dual
order by x asc

ORA-00904: "X": invalid identifier

А вот такой работает:

select 'A' as x from dual union all
select 'B'      from dual union all
select 'C'      from dual union all
select 'D' as x from dual union all
select 'E'      from dual
order by x asc;

X
-
A
B
C
D
E

Вобщем, перепробовав различные варианты выяснил, что если выборок в союзе более двух, то как минимум первя выборка и предпоследняя (именно предпоследняя, а не последняя или не пред-предпоследняя) выборка должны иметь псевдоним столбца все примеры на db<>fiddle.

Вопрос:

Является ли это (известной?) ошибкой в парсере Oracle SQL, или есть какая-то очень тонкая деталь в синтаксисе языка, которая абсолютно точно требует, чтобы первая и предпоследнияя выборка союза содержали псевдоним столбца, на который ссылается клауза ORDER BY?

_{Свободный перевод вопроса Curious issue with Oracle UNION and ORDER BY от участника @Lukas Eder}

Answer 1

Похоже, что это ошибка парсера (или "особенность"), а не требование языка SQL.

На MOS (My Oracle Support), насколько помню, был открыт инцидент 14196463, но закрыт без конечного решения. Этот также упоминается в ветке MOS Community 3561546. Для просмотра любой из тем нужна учетная запись MOS, или по крайней мере, базовая учетная запись Oracle.

Это также обсуждалось в ветке OTN. Там также не так много информации, но она предполагает, что такое поведение существовало по крайней мере до версии 9.2.0.8, а возможно, и гораздо раньше.

В документации не совсем всё ясно, но и не указано явно, где должен стоять псевдоним:

For compound queries containing set operators UNION, INTERSECT, MINUS или UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

Для составных запросов, содержащих операторы множеств UNION, INTERSECT, MINUS или UNION ALL, в клаузе ORDER BY должны указываться позиции или псевдонимы, а не явные выражения. Кроме того, условие ORDER BY может встречаться только в последнем компоненте запроса. Предложение ORDER BY упорядочивает все строки, возвращаемые всем составным запросом.

В запросе есть псевдоним столбца и он используется, нигде не говорится о том, что надо использовать псевдоним в каких-то конкретных компонентах.

Поведение кажется противоречит тому, что псевдоним действителен для конечной проекции запроса, особенно в сочетании с общим правилом о том, что псевдоним этой конечной проекции запроса действителен только в клаузе ORDER BY.

Прим.ред.: в подглаве Restrictions on the Set Operators указано явно, где должен быть указан псевдоним столбца:

If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause.

Все выборки множества включая предпоследнюю должны указывать псевдоним столбца, так как за ними следует оператор множества. Только последняя выборка избавлена от этого ограничения.

Возможность не указывать псевдоним столбца в выборках множества между первой и предпоследней противоречит этому пункту документации и не должна использоваться.

_{Свободный перевод ответа от участника @Alex Poole}

Answer 2

Это не совсем ответ на вопрос, почему неожидаемое поведение в запросе, но его можно легко переписать следующим образом (на db<>fiddle):

with t (x) as (
    select 'A' from dual union all
    select 'B' from dual union all
    select 'C' from dual
)
select * from t
order by x asc

Так надо указать псевдоним столбца x только один раз, и этот запрос никогда не должен завершиться ошибкой недопустимого идентификатора.

_{Свободный перевод ответа от участника @tbone}

Answer 3

А так работает:

select 'A' as x from dual union all
select 'B'      from dual union all
select 'C'      from dual union all
select 'D'      from dual union all
select 'E'      from dual
order by 1 asc

БЛОГ НА HUSL

Странная ошибка с UNION и ORDER BY: ORA-00904: "X": invalid identifier

Ответы (3 шт):