Oracle 以开头的子查询连接

嗨，汤姆，嗯，克里斯，当然，

将我们的生产数据库迁移到12.2.0.1之后，我发现在START that子句中使用子查询进行连接存在问题。

仅供参考: 当我复制它时，LiveSQL链接不起作用，这是正确的链接:
https://livesql.oracle.com/apex/livesql/s/g8qg94z3f0vxh7o70gi2u5gxk

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

我有一个表TW_GROUP，其中包含文章及其产品组。然后我有一个分层表TW_GROUP_TREE的所有产品组与列GRP，父和代码。乘积组0是根组。对于我的测试用例，我只需要2个级别，当然，在我们的系统中，我们还有更多级别。

CREATE TABLE TW_GROUP (Article NUMBER, Subgrp NUMBER);
INSERT INTO TW_GROUP VALUES (1, 20);
INSERT INTO TW_GROUP VALUES (2, 23);


CREATE TABLE TW_GROUP_TREE (Grp NUMBER, Parent NUMBER, Code NUMBER);
INSERT INTO TW_GROUP_TREE VALUES (20, 0, 6);
INSERT INTO TW_GROUP_TREE VALUES (23, 0, NULL);
INSERT INTO TW_GROUP_TREE VALUES (0, NULL, 99);

列代码可以在每个层次结构中包含一个值，但它不是必须的，正如您在产品组23中看到的那样。所以我必须使用CONNECT BY来照顾它。我想使用我可以在层次结构中找到的第一个值，从TW_GROUP中的子组开始。

在START w跟子句中，我使用子查询来获取文章的产品组。

这适用于第1条:

SELECT Article,
       (SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
          FROM TW_GROUP_TREE
         WHERE Code IS NOT NULL
           AND RowNum = 1
    CONNECT BY PRIOR Parent = Grp
    START WITH Grp  = (SELECT SubGrp
                         FROM TW_GROUP
                        WHERE Article  = u1.Article)) AS Code_with_Subquery
  FROM (SELECT 1 AS Article FROM DUAL) u1;

   ARTICLE CODE_WITH_SUBQUERY
---------- ------------------
         1                  6
1 row selected.

它也适用于第2条。

SELECT Article,
       (SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
          FROM TW_GROUP_TREE
         WHERE Code IS NOT NULL
           AND RowNum = 1
    CONNECT BY PRIOR Parent = Grp
    START WITH Grp  = (SELECT SubGrp
                         FROM TW_GROUP
                        WHERE Article  = u1.Article)) AS Code_with_Subquery
  FROM (SELECT 2 AS Article FROM DUAL) u1;

   ARTICLE CODE_WITH_SUBQUERY
---------- ------------------
         2                 99
1 row selected.

这里从根产品组中选择代码99，因为产品组23没有代码。


但它不能同时对两篇文章正常工作:

SELECT Article,
       (SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
          FROM TW_GROUP_TREE
         WHERE Code IS NOT NULL
           AND RowNum = 1
    CONNECT BY PRIOR Parent = Grp
    START WITH Grp  = (SELECT SubGrp
                         FROM TW_GROUP
                        WHERE Article  = u1.Article)) AS Code_with_Subquery
  FROM (SELECT 1 Article FROM DUAL UNION ALL SELECT 2 Article FROM DUAL) u1;

   ARTICLE CODE_WITH_SUBQUERY
---------- ------------------
         1                  6
         2                  6
2 rows selected.

这里的两篇文章的结果都是6，这对于第2条来说肯定是错误的，如上所示。

当我从START w与子句中删除子查询并将其放入查询u1时，它可以正常工作。

SELECT Article,
       (SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
          FROM TW_GROUP_TREE
         WHERE Code IS NOT NULL
           AND RowNum = 1
    CONNECT BY PRIOR Parent = Grp
    START WITH Grp  = u1.SubGrp) AS Code_without_Subquery
  FROM (SELECT 1 AS Article,
               (SELECT SubGrp
                 FROM TW_GROUP
                WHERE Article  = 1) AS SubGrp
          FROM DUAL
        UNION ALL
        SELECT 2 AS Article,
               (SELECT SubGrp
                 FROM TW_GROUP
                WHERE Article  = 2) AS SubGrp
          FROM DUAL) u1;

   ARTICLE CODE_WITHOUT_SUBQUERY
---------- ---------------------
         1                     6
         2                    99

2 rows selected.

两个版本在同一个SQL:

SELECT Article,
       (SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
          FROM TW_GROUP_TREE
         WHERE Code IS NOT NULL
           AND RowNum = 1
    CONNECT BY PRIOR Parent = Grp
    START WITH Grp  = (SELECT SubGrp
                         FROM TW_GROUP
                        WHERE Article  = u1.Article)) AS Code_with_Subquery,
       (SELECT FIRST_VALUE(CODE) OVER(ORDER BY Level)
          FROM TW_GROUP_TREE
         WHERE Code IS NOT NULL
           AND RowNum = 1
    CONNECT BY PRIOR Parent = Grp
    START WITH Grp  = u1.SubGrp) AS Code_without_Subquery
  FROM (SELECT 1 AS Article,
               (SELECT SubGrp
                 FROM TW_GROUP
                WHERE Article  = 1) SubGrp
          FROM DUAL d
        UNION ALL
        SELECT 2 AS Article,
               (SELECT SubGrp
                 FROM TW_GROUP
                WHERE Article  = 2)
          FROM DUAL) u1;

这就是我在12.2.0.1中的结果:

   ARTICLE CODE_WITH_SUBQUERY CODE_WITHOUT_SUBQUERY
---------- ------------------ ---------------------
         1                  6                     6
         2                  6                    99

2 rows selected.

正如您在livesql.oracle.com上的测试用例中看到的那样，该问题不再出现在18.0.0.0中。

再次正确链接:https://livesql.oracle.com/apex/livesql/s/g8qg94z3f0vxh7o70gi2u5gxk



现在我的问题是:

这是12.2.0.1中已知的bug吗？有没有一个补丁可以解决这个问题？
我不想手动检查我们系统中使用CONNECT BY的所有sql，因为结果可能不正确。


最好的问候，托比亚斯

肯定有某种错误。我可以重现12.2上的行为和18.1上的修复。

不过，我无法找到根本原因。您的查询中发生了很多事情。您可以在您的start与子句fine (从至少11.2.0.4) 中使用子查询:

SELECT t.*,
       connect_by_root grp,
       level
FROM   TW_GROUP_TREE t
CONNECT BY  PRIOR Parent = Grp
START WITH Grp in (
  SELECT t.SubGrp
  FROM   TW_GROUP t
);

GRP   PARENT   CODE     CONNECT_BY_ROOTGRP   LEVEL   
   20        0        6                   20       1 
    0          99                   20       2 
   23        0                      23       1 
    0          99                   23       2

如果你想在层次结构中找到代码的第一个非null值，你可以这样做:

with tree as (
  select t.*,
         connect_by_root grp root,
         level lvl
  from   tw_group_tree t
  connect by  prior parent = grp
  start with grp in (
    select t.subgrp
    from   tw_group t
  )
)
  select t.*, 
         first_value ( code ) ignore nulls over ( 
           partition by root 
           order by lvl rows between unbounded preceding and unbounded following
         ) first_code_val
  from   tree t;

GRP   PARENT   CODE     ROOT   LVL   FIRST_CODE_VAL   
   20        0        6     20     1                6 
    0          99     20     2                6 
   23        0        23     1               99 
    0          99     23     2               99

同样，至少从11.2.0.4 (我测试过的最旧版本) 开始工作。所以，如果你发现你得到这个，我相信有一种方法来重写查询，以避免问题。

如果您想获得更多确定性，请与支持人员联系以了解补丁程序。