Oracle 19c中LISTAGG函数动态条件下结果异常问题咨询

问题场景 #

以下SQL用于将每个角色关联的所有条件节点（含子节点）通过LISTAGG聚合导出：

select 
r.id, 
(
select
    LISTAGG(
        r.id || '##' || cco.id  
            , 
        ' '
    )
    from 
    (
        SELECT
            co.id
       FROM conditions co
       START WITH ID = (select cons.id from conditions cons where cons.role_id = r.id)
       CONNECT BY PRIOR co.id = co.parent_condition_id
    ) cco
) conditions_export

from roles r
where r.id in (570, 571, 569)
--r.id between 569 and 571

• 当WHERE条件使用单个ID或显式ID列表（如r.id in (570, 571, 569)）时，结果符合预期。
• 当使用无WHERE子句、范围条件（如r.id between 569 and 571）或子查询条件（如r.id in (select rr.id from roles rr)）时，每行返回的聚合值完全重复，结果不符合预期。

示例数据 #

roles表 #

id   name
---  -----
569  ROLE1
570  ROLE2
571  ROLE3

conditions表 #

id    parent_condition_id   role_id  rule
------------------------------------------
1657  NULL                  569      deny
1659  NULL                  570      allow
1667  NULL                  571      and
1674  1668                  NULL     match
1673  1670                  NULL     allow
1672  1671                  NULL     allow
1671  1670                  NULL     and
1670  1669                  NULL     and
1669  1668                  NULL     and
1668  1667                  NULL     and

异常现象对比 #

• 显式ID列表查询结果（符合预期）：

569 569##1657
570 570##1659
571 571##1667 571##1668 571##1669 571##1670 571##1671 571##1672 571##1673 571##1674

• 范围条件r.id between 569 and 571查询结果（异常）：

569 569##1657
570 570##1657
571 571##1657

问题原因 #

Oracle优化器在处理范围/子查询条件时，可能将嵌套的CONNECT BY相关子查询误判为非相关查询，仅执行一次并复用结果，导致所有角色行都使用第一个角色的条件聚合值。

解决方案 #

方案1：使用CROSS APPLY强制逐行执行子查询 #

利用CROSS APPLY（Oracle 12c及以上支持）确保每个角色单独执行条件节点查询，避免优化器复用结果：

SELECT 
    r.id,
    LISTAGG(r.id || '##' || cco.id, ' ') AS conditions_export
FROM roles r
CROSS APPLY (
    SELECT co.id
    FROM conditions co
    START WITH ID = (SELECT cons.id FROM conditions cons WHERE cons.role_id = r.id)
    CONNECT BY PRIOR co.id = co.parent_condition_id
) cco
WHERE r.id BETWEEN 569 AND 571 -- 可替换为任意条件
GROUP BY r.id;

方案2：用WITH子句预查询所有角色的条件节点 #

先通过递归查询获取每个角色关联的所有条件节点，再进行聚合，逻辑更清晰且避免关联失效：

WITH role_condition_nodes AS (
    SELECT 
        r.id AS role_id,
        co.id AS condition_id
    FROM roles r
    -- 先关联角色的根条件节点
    JOIN conditions cons ON cons.role_id = r.id
    -- 递归查询所有子节点
    CONNECT BY PRIOR co.id = co.parent_condition_id
    START WITH co.id = cons.id
)
SELECT 
    role_id,
    LISTAGG(role_id || '##' || condition_id, ' ') AS conditions_export
FROM role_condition_nodes
WHERE role_id BETWEEN 569 AND 571 -- 可替换为任意条件
GROUP BY role_id;

验证结果 #

使用上述任一方案执行范围条件查询，均可得到符合预期的结果：

569 569##1657
570 570##1659
571 571##1667 571##1668 571##1669 571##1670 571##1671 571##1672 571##1673 571##1674

内容的提问来源于stack exchange，提问作者tomas