Oracle 11查询报ORA-00904错误，Oracle 19正常，原因何在？

问题背景 #

基于主项表people聚合apples和pears两类明细项时，执行以下SQL在Oracle 11g中报ORA-00904: "P"."NAME" invalid identifier错误，但Oracle 19g可正常运行。

执行的SQL语句 #

with people (name) as (
  select 'Alice' from dual union all
  select 'Bob' from dual
), apples (name, title) as (
  select 'Alice', 'apple1' from dual union all
  select 'Bob', 'apple2' from dual union all
  select 'Bob', 'apple3' from dual
), pears (name, title) as (
  select 'Alice', 'pear4' from dual union all
  select 'Alice', 'pear5' from dual union all
  select 'Alice', 'pear6' from dual union all
  select 'Bob', 'pear7' from dual union all
  select 'Bob', 'pear8' from dual
)
select p.name
     , (
         select listagg(u.title) within group (order by null)
         from (
           select x.title from apples x where x.name = p.name
           union
           select x.title from pears  x where x.name = p.name
         ) u
       ) as unioned
from people p;

预期结果 #

错误原因 #

核心是Oracle版本对嵌套子查询的外部引用作用域支持不同：

• Oracle 11g的作用域解析规则更严格：标量子查询中嵌套了含UNION的内层子查询时，内层子查询无法识别最外层people p的p.name——作用域仅能穿透一层子查询，多层嵌套会导致外部列无法被解析。
• Oracle 19g优化了作用域解析逻辑，允许跨多层嵌套的子查询引用外部表的列，因此可以正常识别p.name。

兼容Oracle 11g的改写方案 #

将apples和pears的合并逻辑提前到CTE中，再通过关联分组聚合，避免多层嵌套的作用域问题：

with people (name) as (
  select 'Alice' from dual union all
  select 'Bob' from dual
), apples (name, title) as (
  select 'Alice', 'apple1' from dual union all
  select 'Bob', 'apple2' from dual union all
  select 'Bob', 'apple3' from dual
), pears (name, title) as (
  select 'Alice', 'pear4' from dual union all
  select 'Alice', 'pear5' from dual union all
  select 'Alice', 'pear6' from dual union all
  select 'Bob', 'pear7' from dual union all
  select 'Bob', 'pear8' from dual
), combined as (
  select name, title from apples
  union all
  select name, title from pears
)
select p.name, listagg(c.title) within group (order by null) as unioned
from people p
left join combined c on p.name = c.name
group by p.name;

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