Oracle中MINUS正常但MINUS ALL无法生效的原因排查

测试SQL代码 #

SELECT jt.*
  FROM JSON_TABLE (
           TO_CLOB ('[{"A":1,"B":11},{"A":1,"B":11}]'),
           '$[*]'
           COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
       AS jt
MINUS -- 取消下方注释使用MINUS ALL时语句报错
--all
SELECT jt.*
  FROM JSON_TABLE (
           TO_CLOB ('[{"A":1,"B":11},{"A":12,"B":111}]'),
           '$[*]'
           COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
       AS jt

现象说明 #

• 仅使用MINUS（保留ALL的注释）时，语句可正常执行，返回去重后的差集结果：

• 取消注释使用MINUS ALL写法时，语句直接抛出语法错误，无法正常运行。
• 两个子查询单独执行的返回结果：
  • 左表（第一个JSON_TABLE查询）返回2条重复记录：

    A	B
    1	11
    1	11

  • 右表（第二个JSON_TABLE查询）返回2条记录：

    A	B
    1	11
    12	111

• 按照MINUS ALL的运算规则（不对结果去重，左表每匹配到右表1条完全相同的行就抵消1条，剩余行全部返回），预期返回1条(A=1,B=11)的记录。

根本原因 #

测试所用的Oracle 18c版本原生不支持MINUS ALL语法：

1. Oracle在21c之前的版本（包括11g、12c、18c、19c）中，集合运算符只有UNION支持ALL选项（即常用的UNION ALL），INTERSECT（交集）和MINUS（差集）两个运算符默认只返回去重后的结果，没有提供ALL选项来保留重复行。
2. 18c版本的SQL解析器不会将MINUS ALL识别为合法的集合运算语法，MINUS之后出现的ALL会被判定为无效的游离标识符，直接触发语法错误，导致语句无法执行。
3. 如果需要在18c及更早版本实现MINUS ALL的不去重差集效果，可以通过分析函数给重复行加序号后匹配实现，参考写法：

SELECT A, B
FROM (
  SELECT 
    A, 
    B, 
    ROW_NUMBER() OVER(PARTITION BY A, B ORDER BY 1) AS rn
  FROM JSON_TABLE (
           TO_CLOB ('[{"A":1,"B":11},{"A":1,"B":11}]'),
           '$[*]'
           COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
) t1
WHERE (A, B, rn) NOT IN (
  SELECT 
    A, 
    B, 
    ROW_NUMBER() OVER(PARTITION BY A, B ORDER BY 1) AS rn
  FROM JSON_TABLE (
           TO_CLOB ('[{"A":1,"B":11},{"A":12,"B":111}]'),
           '$[*]'
           COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
)

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud