含嵌套CTE与集合的Oracle查询报ORA-00904错误原因排查

错误复现SQL #

with t (id, parent_id) as (
  select 1, 2 from dual
)
, wrapper (id, parent_id) as (
  select * from t where parent_id = 2
)
, wrapper_with_elements (id, elements) as (
  select u.id, sys.odcinumberlist(1) as elements
  from wrapper u
)
select (
         select cast(collect(cast(ru.id as number)) as sys.odcinumberlist)
         from wrapper_with_elements ru
       ) as agg1
     , (
         select cast(collect(cast(ru.id as number)) as sys.odcinumberlist)
         from wrapper_with_elements ru
       ) as agg2
from wrapper w

错误原因 #

这是Oracle查询优化器的固有bug，核心触发逻辑是：当主查询中存在多个引用同一含集合类型列的CTE的标量子查询时，优化器会错误地将外层查询（from wrapper w）的列上下文混入到CTE的解析过程中，导致解析时错误地尝试在wrapper_with_elements中查找不存在的parent_id列，最终抛出ORA-00904错误。自定义嵌套表也会触发该问题，说明bug与集合类型的处理逻辑直接相关。

为什么这些操作能解决错误 #

• 将子查询中的from wrapper替换为from t：绕过了中间CTEwrapper的上下文干扰，优化器不会错误关联外层查询的列
• 将sys.odcinumberlist(1)替换为null或任意标量值：消除了集合类型带来的特殊解析逻辑，标量列不会触发优化器的上下文混淆
• 将wrapper_with_elements内联到主查询子查询的from子句中：避免了独立CTE被多次引用时的解析冲突，子查询内定义的集合不会和外层上下文产生混淆
• 移除agg1或agg2任意一列：减少了对同一集合CTE的重复引用，优化器仅处理单次引用时不会触发上下文冲突的bug

内容的提问来源于stack exchange，提问作者Tomáš Záluský