Oracle OUTER APPLY с конструктором TABLE ведет себя как CROSS APPLY. Ошибка?

Question

Рассмотрим следующую настройку:

CREATE TYPE list_t AS VARRAY(10) OF NUMBER(10);
/
CREATE TABLE x (
  id NUMBER(10),
  list list_t
);
INSERT INTO x VALUES (0, list_t());
INSERT INTO x VALUES (1, list_t(1));
INSERT INTO x VALUES (2, list_t(1, 2));

Моя интуиция подсказывает мне, что следующие запросы должны давать одинаковый результат:

-- 1: Using 12c syntax with table constructor
SELECT x.id, x.list, y.column_value
FROM x
OUTER APPLY TABLE (x.list) y;

-- 2: Using 12c syntax with an explicit (+) operator
SELECT x.id, x.list, y.column_value
FROM x
OUTER APPLY TABLE (x.list)(+) y;

-- 3: Using 12c syntax with derived table
SELECT x.id, x.list, y.column_value
FROM x
OUTER APPLY (SELECT * FROM TABLE (x.list)) y;

-- 4: Using 11g syntax with old outer join (+)
SELECT x.id, x.list, y.column_value
FROM x, TABLE(x.list)(+) y;

-- 5: Using 12c standard syntax with LATERAL (works only on derived tables in this case)
SELECT x.id, x.list, y.column_value
FROM x
LEFT JOIN LATERAL (SELECT * FROM TABLE (x.list)) y ON 1 = 1;

Однако они не возвращают тот же результат. Результат № 1 кажется неправильным:

Результат 1

(ведет себя как CROSS APPLYВроде неправильно)

ID  LIST                COLUMN_VALUE
------------------------------------
1   TEST.LIST_T(1)      1
2   TEST.LIST_T(1, 2)   1
2   TEST.LIST_T(1, 2)   2

Результат 2, 3, 4 и 5

ID  LIST                COLUMN_VALUE
------------------------------------
0   TEST.LIST_T()
1   TEST.LIST_T(1)      1
2   TEST.LIST_T(1, 2)   1
2   TEST.LIST_T(1, 2)   2

Альтернатива с использованием сохраненной функции

Чтобы проиллюстрировать мою путаницу, я могу добавить сохраненную функцию, которая просто возвращает список ввода:

CREATE OR REPLACE FUNCTION f (list list_t) RETURN list_t IS
BEGIN
  RETURN list;
END f;
/

И затем используйте это в исходном запросе:

-- 1: Using 12c syntax with table constructor
SELECT x.id, x.list, y.column_value
FROM x
OUTER APPLY TABLE (f (x.list)) y;

Теперь я получаю ожидаемый результат с 4 рядами:

ID  LIST                COLUMN_VALUE
------------------------------------
0   TEST.LIST_T()
1   TEST.LIST_T(1)      1
2   TEST.LIST_T(1, 2)   1
2   TEST.LIST_T(1, 2)   2

Вопрос

Это ошибка в Oracle 12.2.0.1.0, или это как OUTER APPLY и TABLE конструктор должен работать?

Answer 1

Это ошибка парсера, на стадии расширения. OUTER APPLY (как и некоторые другие функции, добавленные в 12.1), по-видимому, реализованы путем преобразования в синтаксис, который оптимизатор знал в 11g. Первый запрос (неправильно) преобразуется в соединение INNER, а третий (правильно) преобразуется в соединение OUTER.

-- select x.id,x.list,y.column_value from x outer apply table (list) y
select "A1"."ID_0"           "ID",
       "A1"."LIST_1"         "LIST",
       "A1"."COLUMN_VALUE_2" "COLUMN_VALUE"
from   (select "A3"               ."ID" "ID_0",
               "A3"."LIST"         "LIST_1",
               "A2"."COLUMN_VALUE" "COLUMN_VALUE_2"
        from   "DEMO"."X" "A3",
               (select value(a4) "COLUMN_VALUE"
                from   table("A3"."LIST") "A4") "A2"
        where  1 = 1) "A1"

-- select x.id,x.list,y.column_value from x outer apply (select * from table (list)) y
select "A1"."ID_0"           "ID",
       "A1"."LIST_1"         "LIST",
       "A1"."COLUMN_VALUE_2" "COLUMN_VALUE"
from   (select "A3"                 ."ID" "ID_0",
               "A3"."LIST"           "LIST_1",
               "A2"."COLUMN_VALUE_0" "COLUMN_VALUE_2"
        from   "DEMO"."X" "A3",
               lateral((select "A4"."COLUMN_VALUE_0" "COLUMN_VALUE_0"
                       from   lateral((select "A5"."COLUMN_VALUE" "COLUMN_VALUE_0"
                                      from   (select value(a6) "COLUMN_VALUE"
                                              from   table("A3"."LIST") "A6") "A5")) "A4"
                       where  1 = 1))(+) "A2") "A1"