共通表式を使った再帰SQL

DB2の共通表式中のFROM節では共通表自体を指定することができ、このとき共通表式は再帰共通表式となります。再帰共通表式を使うことで、階層構造を持つテーブルデータを再帰的に探索したりできます。

例

例えば、以下のようなファイルの関連があるとして、

これの関連情報を↓のようなテーブルで保持するとします。

create table file_relation ( 
  from VARCHAR(20) NOT NULL, 
  to   VARCHAR(20) NOT NULL, 
  PRIMARY KEY(from,to) 
) in foo

$ db2 "select * from file_relation" 

FROM                 TO                  
-------------------- --------------------
a                    b                   
a                    c                   
a                    d                   
c                    e                   
c                    f                   
d                    g                   
x                    y                   
x                    z                   

  8 record(s) selected.

ここで、以下の再帰共通表式を使うと「"a"配下のファイル一覧を再帰的に取り出す」ことができます。

WITH X(from,to) AS ( 
  SELECT from, to FROM file_relation WHERE from = 'a' 
  UNION ALL 
  SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to  
) SELECT DISTINCT to FROM X"

実行結果です。「このSQLだと無限ループになる場合があるよ」という内容の警告が表示されていますが、a配下のファイルをちゃんと取得できています。

TO                  
--------------------
SQL0347W  The recursive common table expression "XFYADMIN.X" may contain an 
infinite loop.  SQLSTATE=01605

b                   
c                   
d                   
e                   
f                   
g                   

  6 record(s) selected with 1 warning messages printed.

c,x,bの配下もちゃんととれます。

$ db2 "WITH X(from,to) AS ( SELECT from, to FROM file_relation  WHERE from = 'x' UNION ALL SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to  ) SELECT DISTINCT to FROM X"

TO                  
--------------------
y                   
z                   

  2 record(s) selected with 1 warning messages printed.

$ db2 "WITH X(from,to) AS ( SELECT from, to FROM file_relation  WHERE from = 'c' UNION ALL SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to  ) SELECT DISTINCT to FROM X"

TO                  
--------------------
e                   
f                   

  2 record(s) selected with 1 warning messages printed.

$ db2 "WITH X(from,to) AS ( SELECT from, to FROM file_relation  WHERE from = 'b' UNION ALL SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to  ) SELECT DISTINCT to FROM X"

TO                  
--------------------

  0 record(s) selected with 1 warning messages printed.

内部的な動作は@IT - オブジェクト指向、Javaを取り入れた新しい業界標準「SQL99」詳細解説が詳しいのですが、

1.共通表式内の「SELECT from, to FROM file_relation WHERE from = 'a' 」で「'a'と関連づけられているファイル一覧」を取り出して、X表に追加する。
2.さらに次に続く「SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to」で、1で取り出したファイル一覧に関連づけられているファイル一覧を取り出し、X表に追加する。
3.1〜2を子がなくなるまで繰り返す。
4.最後に、X表から関連先ファイル名を取り出す。

となるらしい。ふむふむ。

循環するとどうなるか。

↑のSQLでは循環をチェックしていないので、データを以下のような構造にすると、さくっと無限ループします。

$ db2 "select * from file_relation" 

FROM                 TO                  
-------------------- --------------------
a                    b                   
a                    c                   
a                    d                   
c                    e                   
c                    f                   
d                    g                   
f                    a                   
x                    y                   
x                    z                   

  9 record(s) selected.

$ db2 "WITH X(from,to) AS ( SELECT from, to FROM file_relation  WHERE from = 'a' UNION ALL SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to  ) SELECT DISTINCT to FROM X"
・・・帰ってこない・・・

回避策を30分くらい考えたけどさっぱり思いつきません。無念..。一般的にはどうするんだろー。「最大XX階層まで辿る」とか? > 今度ググる。