Ответ 1
Этот ответ может немного расплыться...
Oracle очень разборчива с заданными операциями. Каждый столбец должен иметь тот же тип данных, что и соответствующие во втором, третьем и т.д..
Я думаю, что ваш второй запрос завершился неудачно, потому что Oracle оценивает to_number() как число до выполнения union, но после этого оценивает его значение "null-ness" . Ваш первый запрос завершается успешно, потому что первое значение было оценено для "null-ness" , а затем происходит union. Это означает, что порядок оценки:
- Функции первого выбора
- 1-й выбор типов данных
- Функции второго выбора
- объединение
- 2-й выбор типов данных
Я попытаюсь доказать это шаг за шагом, но я не уверен, что это будет абсолютным доказательством.
Оба следующих запроса
select 1 from dual union select '1' from dual;
select '1' from dual union select 1 from dual;
завершится ошибкой со следующей ошибкой, поскольку не происходит никакого неявного преобразования.
ORA-01790: выражение должно иметь тот же тип данных, что и соответствующий Выражение
Однако оба следующих успеха будут
select null from dual union select '1' from dual;
select null from dual union select 1 from dual;
Если мы выберем dump этих двух запросов, возвращается следующее:
SQL> select dump(a)
2 from ( select null a from dual union select '1' from dual );
DUMP(A)
-------------------------------------------------------------------
Typ=96 Len=1: 49
NULL
SQL> select dump(a)
2 from ( select null a from dual union select 1 from dual );
DUMP(A)
-------------------------------------------------------------------
Typ=2 Len=2: 193,2
NULL
Как видите, столбцы имеют разные типы данных. Первый запрос с символом возвращает a char, а второй возвращает число, но порядок был повернут, а второй select - первым.
Наконец, если мы посмотрим на dump вашего первого запроса
SQL> select substr(dump(ename),1,35) a, substr(dump(loc),1,35) b
2 from ( select ename,to_number(null) as loc from emp
3 union
4 select to_char(null),loc from dept
5 );
A B
----------------------------------- -----------------------------------
Typ=1 Len=6: 104,97,104,97,104,97 NULL
NULL Typ=1 Len=6: 104,97,104,97,104,97
SQL>
Вы можете видеть, что dump(to_number(null)) имеет значение null; но возвращается varchar2 не a char, потому что это тип данных вашего столбца. Интересно отметить, что порядок возвращаемых операторов не был отменен и что, если вы должны были создать этот запрос в качестве таблицы, оба столбца были бы varchar2.
При выборе типа данных столбца в выбранном запросе Oracle берет первый известный тип данных и затем использует его для вычисления общего типа данных. Вот почему запросы, в которых первый select был пустым, изменили свои строки.
Ваш первый запрос завершается успешно, потому что первый select, select ename,to_number(null) from emp, "описывает", как будет выглядеть результирующий набор. |varchar2|null|. Второй запрос затем добавляет |varchar2|varchar2|, что не вызывает проблем.
Второй запрос завершился неудачно, потому что первый select select ename,to_number(null) from emp описывает "результат" как varchar2, null. Однако затем вы пытаетесь добавить нулевой номер и varchar2 в union.
Скачок веры здесь заключается в том, что Oracle решает, что to_number(null) является числом до union и не оценивает его для "null-ness" до следующего. Я действительно не знаю, как проверить, действительно ли это происходит, поскольку вы не можете создать объект с столбцом null, и, как вы заметили, вы также не можете его выбрать.
Поскольку я не могу доказать, что Oracle не разрешает, я попытаюсь получить эмпирические данные. Рассмотрим результаты (или ошибки) следующих запросов.
SQL> select 1 as a from dual union select to_number(null) from dual;
A
----------
1
SQL> select '1' as a from dual union select to_number(null) from dual;
select '1' as a from dual union select to_number(null) from dual
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
SQL> select 1 as a from dual union select to_char(null) from dual;
select 1 as a from dual union select to_char(null) from dual
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
SQL> select '1' as a from dual union select to_char(null) from dual;
A
-
1
Они, как представляется, демонстрируют, что to_char и to_number, независимо от того, выполняются ли они на ядре, неявно определяют тип данных, который затем оценивается для его соответствия в union, до их оценки для "нуль-Несс"
Это объяснение также будет охватывать проблему coalesce, поскольку to_number(null) - это число, прежде чем оно будет равно null.