Любопытная проблема с Oracle UNION и ORDER BY

Следующий запрос отлично подходит для почти каждой базы данных (дайте или возьмите таблицу фиктивных dual), включая Oracle:

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
order by x asc

Я получаю

ORA-00904: "X": invalid identifier

Это, однако, работает:

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

Я играл с этой проблемой и выяснил, что, по-видимому, по крайней мере, первый подзапрос и второй-последний (??) подселек должны иметь столбец с именем x. В первом примере два подзаголовка, казалось, просто совпадали. Рабочий пример:

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 union all
select 'F' as x from dual union all
select 'G'      from dual
order by x asc

Как вы, возможно, догадались, этот не будет работать:

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' as x from dual union all
select 'F'      from dual union all
select 'G'      from dual
order by x asc

Интересная заметка:

Производные таблицы, похоже, не страдают от этого ограничения. Это работает:

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

Вопрос:

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

Ответы

Ответ 1

Это не отвечает на вопрос, но кажется, что это ошибка парсера (или "функция" ), а не требование языка.

Согласно моей поддержке Oracle, это, похоже, было поднято как ошибка 14196463, но закрыто без разрешения. Он также упоминается в сообществе 3561546. Вам нужна учетная запись MOS или, по крайней мере, учетная запись Oracle, чтобы увидеть любой из них.

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

Документация немного расплывчата, но не указывает, что это будет проблемой:

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

Вы сглаживаете свое выражение и используете это, и он не говорит, что вам нужно иметь псевдоним определенных компонентов (хотя, конечно, он не говорит, что вам тоже этого не нужно).

Поведение кажется несовместимым с тем, что псевдоним действителен для окончательной проекции, а обычное правило о том, что псевдоним действителен только в предложении order by - это, кажется, падает где-то посередине.

Ответ 2

Это не отвечает на то, почему вы получаете непоследовательное поведение из вашего текущего запроса, но в Oracle вы можете легко переписать как следующее (которое никогда не должно терпеть неудачу с ошибкой неправильного идентификатора):

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) один раз.