在Oracle 11上查询ORA-00904失败，但在Oracle 19上没有

Question

设p为一个主项目表，对于每个主项目，我们将汇总详细项目的集合。细节项有两种，来自两个不同的来源。此查询在Oracle 11上的ORA-00904: "P"."NAME" invalid identifier上失败，但在Oracle 19上正常工作。为什么?

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;

联合apple1pear4pear5pear6apple2apple3pear7pear8

根据AskTom，似乎有深度的限制，从外部表的别名是可见的相关子查询。这个限制在Oracle12c中被移除。对于Oracle11g，仍然可以重写查询条件，以便将p.name列提取到更高的级别

select p.name
, (
select listagg(u.title) within group (order by null)
from (
select x.title, x.name from apples x
union
select x.title, x.name from pears  x
) u
where u.name = p.name
) unioned
from people p;

或将union拆分为两个部分，然后再将它们合并:

(with ...)
, parts as (
select p.name
, (
select listagg(x.title) within group (order by null)
from apples x 
where x.name = p.name
) x
, (
select listagg(x.title) within group (order by null)
from pears x 
where x.name = p.name
) y
from people p
)
select name, x || y from parts;

这实际上是我的第一个想法，但现在似乎第一个解决方案对所有情况都足够了(至少我想不出任何反例)。在实际情况下，聚合函数是collect而不是listagg，所以合并是使用multiset union而不是||完成的。

---

还有:这个问题