2018年4月13日金曜日

MySQL 8.0の再帰CTE(WITH RECURSIVE)で1000行以上の結果セットを作りたいとき

TL;DR


単なる連番のテストデータを作りたい時とか、再帰CTEは便利(というかMySQLerは今まで再帰CTEが使えなかったので、そもそもそれ以外の使い方は思いつかないわけだが)だけれど、 ↓ を訳した時点ではWHERE句を間違えるとさっくりとクエリーが逝きっぱなしになっていた。
それが、MySQL 8.0.3から cte_max_recursion_depth が追加されて、「これを超えるステップの再起CTEはエラー」にするようになっていた。
mysql80 36> SELECT @@session.cte_max_recursion_depth;
+-----------------------------------+
| @@session.cte_max_recursion_depth |
+-----------------------------------+
|                              1000 |
+-----------------------------------+
1 row in set (0.01 sec)

mysql80 36> WITH RECURSIVE t AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM t WHERE num < 10) SELECT * FROM t;
+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

mysql80 36> WITH RECURSIVE t AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM t WHERE num < 1001) SELECT * FROM t;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
ちなみにこれ系のパラメーターって 0 にセットすると無制限になると思うじゃろ? 何故かこいつは違うんじゃ
mysql80 36> SELECT @@session.cte_max_recursion_depth;
+-----------------------------------+
| @@session.cte_max_recursion_depth |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql80 36> WITH RECURSIVE t AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM t WHERE num < 10) SELECT * FROM t;
ERROR 3636 (HY000): Recursive query aborted after 1 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

mysql80 36> WITH RECURSIVE t AS (SELECT 1 AS num UNION ALL SELECT num + 1 FROM t WHERE num < -1) SELECT * FROM t; -- シードSELECTの1行しか返さないような条件でもアウト
ERROR 3636 (HY000): Recursive query aborted after 1 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
0にすると再帰CTEが一切合切拒否される仕様になっております。
ほわー。

0 件のコメント :

コメントを投稿