这是奇怪的Oracle 11g SQL查询集合的另一个补充。假设有一个空表和用户定义的类型
create table tz_exp (p_id number(38,0) not null);
create or replace type rms.joedcn_number as table of number;
然后遵循查询(尽可能从实际查询中最小化)
with v (r_id, p_id) as (
select 123, e.p_id from dual left join tz_exp e on 0=1
), u as (
select v.r_id from dual join v on 0=1
union all
select v.r_id from dual join v on v.p_id is null
), w as (
select cast(collect(cast(u.r_id as number)) as rms.joedcn_number) as r_ids
from u
)
select w.r_ids
--, (select max(column_value) from table(w.r_ids)) max_val -- comment out this and r_ids disappears
from w
返回一行和一列带有嵌套表，这是正确的结果：
+-----+
|R_IDS|
+-----+
|{123}|
+-----+
但是，如果我们想在相关子查询中计算集合的最大元素并取消注释注释行，则集合突然显示为空：
+-----+-------+
|R_IDS|MAX_VAL|
+-----+-------+
|{} |null |
+-----+-------+
(注意：问题已编辑，基于count(*)r_ids元素的相关子查询的先前版本可由cardinality函数替换，并且不描述实际问题。 - 谢谢@MT0，见评论。
这种行为的原因很难找到。到目前为止，我的观察是：
- 只能在Oracle 11g sqlfiddle中重现。
tz_exp必须是真实的表。如果替换为 CTE、子查询或select object_id from dba_objects where 0=1，则查询有效。v.p_id列不得null文本，否则查询有效union中必须有第一个空选择 ，否则查询有效
目前，我们将在不久的将来迁移到Oracle 19c，因此这不是一个长期存在的问题。我可以在应用程序级别解决它。我很好奇这是否是一些已知的错误，或者是否有可能在SQL级别解决它或更好地针对其原因。
初始问题链接
我很好奇这是否是一些已知的错误，或者是否有可能在SQL级别解决它或更好地针对其原因。
我不知道为什么您的查询会按原样运行，而且它肯定是一个错误。
但是，如果您使用嵌套表集合(而不是VARRAY，这是SYS.ODCI*LIST类型)，则可以使用CARDINALITY函数来计算数组中的元素：
create table tz_exp (p_id number(38,0) not null);
CREATE TYPE number_list IS TABLE OF NUMBER;
然后：
with v (r_id, p_id) as (
select 123, e.p_id from dual left join tz_exp e on 0=1
), u as (
select v.r_id from dual join v on 0=1
union all
select v.r_id from dual join v on v.p_id is null
), w as (
select cast(collect(cast(u.r_id as number)) as number_list) as r_ids
from u
)
select w.r_ids
, CARDINALITY(w.r_ids) cnt
from w;
输出：
| R_IDS | CNT |
|---|---|
| 123 | 1 |