Oracle 12.2.0.1.0中OUTER APPLY结合TABLE构造器表现异常？

我来帮你分析这个Oracle 12c中遇到的OUTER APPLY和TABLE构造器的行为问题：

环境搭建 #

首先我们先搭建测试环境，执行以下SQL创建自定义类型、表并插入测试数据：

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));

查询对比 #

按常理推断，以下5条查询应该返回完全一致的结果，但实际运行后却出现了差异：

• 1. 使用12c语法结合TABLE构造器

  SELECT x.id, x.list, y.column_value FROM x OUTER APPLY TABLE (x.list) y;
  

  这个查询的表现更像CROSS APPLY，仅返回3行数据（过滤掉了id=0的空列表行）。

• 2. 使用12c语法并显式指定(+)运算符

  SELECT x.id, x.list, y.column_value FROM x OUTER APPLY TABLE (x.list)(+) y;
  

  返回包含id=0的4行预期结果。

• 3. 使用12c语法结合派生表

  SELECT x.id, x.list, y.column_value FROM x OUTER APPLY (SELECT * FROM TABLE (x.list)) y;
  

  返回包含id=0的4行预期结果。

• 4. 使用11g旧版外连接(+)语法

  SELECT x.id, x.list, y.column_value FROM x, TABLE(x.list)(+) y;
  

  返回包含id=0的4行预期结果。

• 5. 使用12c标准LATERAL语法

  SELECT x.id, x.list, y.column_value FROM x LEFT JOIN LATERAL (SELECT * FROM TABLE (x.list)) y ON 1 = 1;
  

  返回包含id=0的4行预期结果。

函数验证 #

为了进一步验证，我们创建一个仅返回输入列表的存储函数：

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

将这个函数代入第1条查询后，修改为：

SELECT x.id, x.list, y.column_value FROM x OUTER APPLY TABLE (f(x.list)) y;

此时查询能够返回预期的4行数据，包括id=0的空列表行。

结论 #

这个现象是Oracle 12.2.0.1.0版本的已知Bug。当OUTER APPLY直接作用于TABLE构造器且传入的集合为空时，Oracle错误地将其处理为CROSS APPLY的逻辑，过滤掉了主表中对应集合为空的行。而通过派生表、显式(+)运算符、LATERAL连接或者用函数包装集合的方式，都能规避这个Bug，让查询按照OUTER APPLY的预期逻辑执行——保留主表的所有行，即使关联的集合为空。

内容的提问来源于stack exchange，提问作者Lukas Eder