またまたOracleか

CONNECT_BY_ISCYCLEの動きがおかしいです。試したのは10g(10.1.0.2.0)です。

準備は以下の通り。

SQL> create table connect_test (parent varchar2(10), child varchar2(10));

表が作成されました。

SQL> insert into connect_test values ('A', 'B');

1行が作成されました。

SQL> insert into connect_test values ('A', 'C');

1行が作成されました。

SQL> insert into connect_test values ('C', 'D');

1行が作成されました。

SQL> insert into connect_test values ('D', 'A');

1行が作成されました。

SQL> commit;

コミットが完了しました。

以下のような循環するツリー構造です。

+--------------+
|              |
+-> A +-> B    |
      |        |
      +-> C -> D

これを、CONNECT BY で展開すると、とーぜんループするんですが、Aからの展開とCからの展開で結果が変わります。

SQL> select parent,child, connect_by_iscycle from connect_test start with parent = 'A' connect by nocycle prior child = parent;

PARENT     CHILD      CONNECT_BY_ISCYCLE
---------- ---------- ------------------
A          B                           0
A          C                           0
C          D                           0
D          A                           0
A          B                           0

SQL> select parent,child, connect_by_iscycle from connect_test start with parent = 'C' connect by nocycle prior child = parent;

PARENT     CHILD      CONNECT_BY_ISCYCLE
---------- ---------- ------------------
C          D                           0
D          A                           0
A          B                           0
A          C                           1

Cから展開した場合は、ループを検出した時点でCONNECT_BY_ISCYCLEから1が返ってきていますが、Aから展開すると0が返ってきてしまい、ループが起こってることがわかりません。

ここから、予想。CONNECT_BY_ISCYCLEは子行がすでに展開済みの行であれば1を返しますが、子行が複数行ある場合は、たとえ展開済みの行が混ざっていても1を返してくれないんだと思います。

1. A から展開を始めると、A-B と A-Cの二行が引っかかる
2. A-B はこれ以上展開出来ない。
3. A-C から展開すると、 C-D が引っかかる。
4. C-D から展開すると、 D-A が引っかかる
5. D-A から展開すると、A-B と A-C が引っかかる。どちらも 1. で既に登場した行なので、ループが検出される。が、CONNECT_BY_ISCYCLEは0を返す。
6. なぜか A-B だけは結果行として返して、終了。なぜ？？

この 5. で、1行だけ引っかかるとうまく 1 を返します。Cからの展開は以下。

1. C から展開を始めると、 C-D が引っかかる
2. C-D から展開すると、 D-A が引っかかる
3. D-A から展開すると、 A-B と A-C の二行が引っかかる
4. A-B はこれ以上展開出来ない
5. A-C を展開すると、 C-D が引っかかる。これは 1. で既に登場した行なので、ループが検出される。CONNECT_BY_ISCECLEは1を返す。