甲骨文解码与案例。查询返回不同的结果集。

Question

我有以下两个版本的SQL语句。一种使用解码另一种情况。其他的一切都一样。解码按预期工作，而CASE提供不同的输出(基本上是具有相应访问权限的组)。原因何在？

两者都可以在客户机中运行，不依赖于DB架构。只有使用解码或大小写的子查询是不同的，所有其他部分都是相等的。

欢迎任何建议！

输出解码：

​

输出案例：

​

译码(短)

...
SELECT    principal
         || DECODE (MAX (DECODE ("ON", 'N', permission)),
                    NULL, MAX (DECODE ("ON", 'O', permission)),
                    MAX (DECODE ("ON", 'N', permission)))    latest_permission,
            principal
         || DECODE (MAX (DECODE ("ON", 'O', permission)),
                    NULL, MAX (DECODE ("ON", 'N', permission)),
                    MAX (DECODE ("ON", 'O', permission)))    dont_overwrite_existing
    FROM
...

病例短

...
SELECT    principal
     || CASE MAX (
                 CASE "ON" WHEN 'N' THEN permission END)
            WHEN NULL
            THEN
                MAX (
                    CASE "ON"
                        WHEN 'O' THEN permission
                    END)
            ELSE
                MAX (
                    CASE "ON"
                        WHEN 'N' THEN permission
                    END)
        END    latest_permission,
        principal
     || CASE MAX (
                 CASE "ON" WHEN 'O' THEN permission END)
            WHEN NULL
            THEN
                MAX (
                    CASE "ON"
                        WHEN 'N' THEN permission
                    END)
            ELSE
                MAX (
                    CASE "ON"
                        WHEN 'O' THEN permission
                    END)
        END    dont_overwrite_existing

FROM 
...

解码全

WITH
    old
    AS
        (SELECT REGEXP_SUBSTR (acl, '[^\(]+')     principal,
                REGEXP_SUBSTR (acl, '\(.+')       permission
           FROM (    SELECT REGEXP_SUBSTR (
                                ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)',
                                '[^,]+',
                                1,
                                LEVEL)    acl
                       FROM DUAL
                      WHERE ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)'
                                IS NOT NULL
                 CONNECT BY REGEXP_SUBSTR (
                                ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)',
                                '[^,]+',
                                1,
                                LEVEL)
                                IS NOT NULL)),
    new
    AS
        (SELECT REGEXP_SUBSTR (acl, '[^\(]+')     principal,
                REGEXP_SUBSTR (acl, '\(.+')       permission
           FROM (    SELECT REGEXP_SUBSTR (':Group1(R),:Group1(RW),:GroupA(RWDA),:Group5(R)',
                                           '[^,]+',
                                           1,
                                           LEVEL)    acl
                       FROM DUAL
                      WHERE ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4(R)'
                                IS NOT NULL
                 CONNECT BY REGEXP_SUBSTR (
                                ':Group1(R),:Group1(RW),:GroupA(RWDA),:Group5(R)',
                                '[^,]+',
                                1,
                                LEVEL)
                                IS NOT NULL)),
    principalsToDelete
    AS
        (SELECT DISTINCT REGEXP_SUBSTR (acl, '[^\(]+')     principal
           FROM (    SELECT REGEXP_SUBSTR (NULL,
                                           '[^,]+',
                                           1,
                                           LEVEL)    acl
                       FROM DUAL
                      WHERE NULL IS NOT NULL
                 CONNECT BY REGEXP_SUBSTR (NULL,
                                           '[^,]+',
                                           1,
                                           LEVEL)
                                IS NOT NULL))
  SELECT    principal
         || DECODE (MAX (DECODE ("ON", 'N', permission)),
                    NULL, MAX (DECODE ("ON", 'O', permission)),
                    MAX (DECODE ("ON", 'N', permission)))    latest_permission,
            principal
         || DECODE (MAX (DECODE ("ON", 'O', permission)),
                    NULL, MAX (DECODE ("ON", 'N', permission)),
                    MAX (DECODE ("ON", 'O', permission)))    dont_overwrite_existing
    FROM (SELECT o.*, 'O' "ON"
            FROM old o
          UNION
          SELECT n.*, 'N' "ON"
            FROM new n)
   WHERE principal NOT IN (SELECT principal FROM principalsToDelete)
GROUP BY principal
ORDER BY principal;

病例全

WITH
    old
    AS
        (SELECT REGEXP_SUBSTR (acl, '[^\(]+')     principal,
                REGEXP_SUBSTR (acl, '\(.+')       permission
           FROM (    SELECT REGEXP_SUBSTR (
                                ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)',
                                '[^,]+',
                                1,
                                LEVEL)    acl
                       FROM DUAL
                      WHERE ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)'
                                IS NOT NULL
                 CONNECT BY REGEXP_SUBSTR (
                                ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)',
                                '[^,]+',
                                1,
                                LEVEL)
                                IS NOT NULL)),
    new
    AS
        (SELECT REGEXP_SUBSTR (acl, '[^\(]+')     principal,
                REGEXP_SUBSTR (acl, '\(.+')       permission
           FROM (    SELECT REGEXP_SUBSTR (':Group1(R),:Group1(RW),:GroupA(RWDA),:Group5(R)',
                                           '[^,]+',
                                           1,
                                           LEVEL)    acl
                       FROM DUAL
                      WHERE ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4(R)'
                                IS NOT NULL
                 CONNECT BY REGEXP_SUBSTR (
                                ':Group1(R),:Group1(RW),:GroupA(RWDA),:Group5(R)',
                                '[^,]+',
                                1,
                                LEVEL)
                                IS NOT NULL)),
    principalsToDelete
    AS
        (SELECT DISTINCT REGEXP_SUBSTR (acl, '[^\(]+')     principal
           FROM (    SELECT REGEXP_SUBSTR (NULL,
                                           '[^,]+',
                                           1,
                                           LEVEL)    acl
                       FROM DUAL
                      WHERE NULL IS NOT NULL
                 CONNECT BY REGEXP_SUBSTR (NULL,
                                           '[^,]+',
                                           1,
                                           LEVEL)
                                IS NOT NULL))
  SELECT    principal
         || CASE MAX (
                     CASE "ON" WHEN 'N' THEN permission END)
                WHEN NULL
                THEN
                    MAX (
                        CASE "ON"
                            WHEN 'O' THEN permission
                        END)
                ELSE
                    MAX (
                        CASE "ON"
                            WHEN 'N' THEN permission
                        END)
            END    latest_permission,
            principal
         || CASE MAX (
                     CASE "ON" WHEN 'O' THEN permission END)
                WHEN NULL
                THEN
                    MAX (
                        CASE "ON"
                            WHEN 'N' THEN permission
                        END)
                ELSE
                    MAX (
                        CASE "ON"
                            WHEN 'O' THEN permission
                        END)
            END    dont_overwrite_existing

    FROM (SELECT o.*, 'O' "ON"
            FROM old o
          UNION
          SELECT n.*, 'N' "ON"
            FROM new n)
   WHERE principal NOT IN (SELECT principal FROM principalsToDelete)
GROUP BY principal
ORDER BY principal;

Answer 1

这种差异来自与null相比时行为上的差异：

SQL> select decode(null,null,1) from dual;

DECODE(NULL,NULL,1)
-------------------
                  1

SQL> select case null when null then 1 end from dual;

CASENULLWHENNULLTHEN1END
------------------------

第二个查询返回NULL而不是1，因此'case x空时1 end‘在x为空时不返回1，而这是有效的：

SQL> select case when null is null then 1 end from dual;

CASEWHENNULLISNULLTHEN1END
--------------------------
                         1