这 是 对 奇怪 的 Oracle11gSQL 查询 集合 的 另 一 个 补充 。
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}|
+-----+
格式
但是 , 如果 我们 想 计算 相关 子 查询 中 集合 的 max 元素 并 取消 注解 注解 行 , 则 集合 会 突然 显示 为 空 :
+-----+-------+
|R_IDS|MAX_VAL|
+-----+-------+
|{} |null |
+-----+-------+
格式
(Note: 问题 已 被 编辑 , 基于 count(*)
ing r_ids
元素 得 先前 版本 得 相关 子 查询 已 被 cardinality
函数 替换 , 并且 未 描述 实际 问题 . - thx to@MT0 , 请 参阅 注解 . )
这种 行为 的 原因 很 难 找到 。 我 到 目前 为止 的 观察 是 :
- 只能 在 Oracle 11g sqlfiddle 中 重现 。
tz_exp
必须 是 * real * 表 。 如果 被 CTE 、 子 查询 或select object_id from dba_objects where 0=1
替换 , 则 查询 有效 。v.p_id
列 不能 是null
文字 , 否则 查询 有效- 在
union
中 必须 有 第 一 个 空 select , 否则 查询 有效
目前 , 我们 打算 在 不久 的 将来 迁移 到 Oracle 19c , 因此 这 不是 一 个 长期 存在 的 问题 。 我 可以 在 应用 程序 级别 解决 它 。 我 很 好奇 这 是否 是 某 个 已知 的 错误 , 或者 是否 可以 在 SQL 级别 解决 它 , 或者 更 好 地 针对 其 原因 。
1条答案
按热度按时间u59ebvdq1#
# # # 初始 问题 link
我 很 好奇 这 是否 是 一些 已知 的 bug , 或者 是否 有 可能 在 SQL 级别 上 解决 它 , 或者 更 好 地 针对 其 原因 。
我 不 知道 为什么 你 的 查询 是 这样 的 行为 , 它 肯定 是 一 个 错误 。
但是 , 如果 使用 嵌套 表 集合 ( 而 不是
SYS.ODCI*LIST
类型 的VARRAY
) , 则 可以 使用CARDINALITY
函数 对 数组 中 的 元素 进行 计数 :中 的 每 一 个
然后 道 :
格式
输出 :
| R _ ID 识别 码|碳 纳米 管|
| - -| - -|
| 一百 二十三|一 个|
小 提琴
# # # 已 更新 问题 link
对于 更新 后 的 查询 , 您 同样 可以 解决 错误 ;这 一 次 是 通过 计算 前 一 子 查询 分解 子句 中 的 最 大 值 ( 而 不是 在 最 后 使用 相关 子 查询 ) :
格式
| R _ ID 识别 码|最 大 值|
| - -| - -|
| 一百 二十三|一百 二十三|
或者 , 根据 您 的 注解 , 如果 您 要 连接 到 另 一 个 表 , 则 可以 尝试 使用
MEMBER OF
运算 符 进行 过滤 , 而 不是 连接 到 表 表达式 :格式
小 提琴