Debian Codegraph @

mysql-test/suite/sys_vars/t/cte_max_recursion_depth_func.test

raw
1 --echo #
2 --echo # Bug#26136509 ADD A MAX_RECURSION VARIABLE TO LIMIT RECURSION IN CTES
3 --echo #
4
5 SELECT @@cte_max_recursion_depth;
6
7 # UNION ALL then UNION DISTINCT
8
9 let $c=2;
10 while ($c)
11 {
12 if ($c == 2)
13 {
14 let $union=ALL;
15 }
16 if ($c == 1)
17 {
18 let $union=DISTINCT;
19 }
20 dec $c;
21
22 # One recursive query block which generates 1 row per input row.
23 # With the @m variable, we control how many iterations this query will
24 # want to do. By varying @m we can thus make it hit the cap or not.
25
26 let $q=
27 WITH RECURSIVE qn AS (
28 SELECT 1 AS a UNION $union
29 SELECT 1+a FROM qn WHERE a<@m)
30 SELECT COUNT(*) FROM qn;
31
32 SET @m = 1001;
33 --error ER_CTE_MAX_RECURSION_DEPTH
34 eval $q;
35
36 SET @m = 1000;
37 eval $q;
38
39 SET cte_max_recursion_depth=10;
40
41 SET @m = 11;
42 --error ER_CTE_MAX_RECURSION_DEPTH
43 eval $q;
44
45 SET @m = 10;
46 eval $q;
47
48 set cte_max_recursion_depth=default;
49
50 # 3 recursive query blocks. Each of them operates on its own data only.
51
52 let $q=
53 WITH RECURSIVE qn AS
54 (SELECT 1 AS a, 0 AS b UNION $union
55 SELECT 1+a, 1 FROM qn WHERE b IN (0,1) AND a<@m UNION $union
56 SELECT 1+a, 2 FROM qn WHERE b IN (0,2) AND a<@m UNION $union
57 SELECT 1+a, 3 FROM qn WHERE b IN (0,3) AND a<@m
58 ) SELECT COUNT(*) FROM qn;
59
60 SET @m = 1001;
61 --error ER_CTE_MAX_RECURSION_DEPTH
62 eval $q;
63
64 SET @m = 999;
65 eval $q;
66
67 # You may wonder why there is a difference of 2, instead of 1, in the
68 # above values of @m. It is because with @m=1000, UNION ALL will throw
69 # error, while DISTINCT will not; which is because in UNION ALL, the
70 # first recursive query blocks need an extra pass over what the last
71 # one has produced, whereas in UNION DISTINCT, the three blocks walk
72 # in lock-step. So the count of iterations is higher for ALL. This
73 # difference is bounded by the number of query blocks, which is small
74 # enough. cte_max_recursion_depth does not intend to be 100% exact.
75
76 SET cte_max_recursion_depth=10;
77
78 SET @m = 11;
79 --error ER_CTE_MAX_RECURSION_DEPTH
80 eval $q;
81
82 SET @m = 9;
83 eval $q;
84
85 SET cte_max_recursion_depth=DEFAULT;
86
87 # One recursive query block which generates 2 rows per input row.
88 # Each iteration thus generates twice more rows than the previous.
89
90 let $q=
91 WITH RECURSIVE
92 onetwo(b) AS (SELECT 1 UNION SELECT 2),
93 qn AS (
94 SELECT 1 AS a, 0 AS b UNION $union
95 SELECT 1+a, onetwo.b FROM onetwo, qn WHERE a<@m)
96 SELECT COUNT(*) FROM qn;
97
98 SET cte_max_recursion_depth=10;
99
100 SET @m = 11;
101 --error ER_CTE_MAX_RECURSION_DEPTH
102 eval $q;
103
104 SET @m = 10;
105 eval $q;
106
107 SET cte_max_recursion_depth=default;
108
109 }
110
111 # Test that we can block any recursive query
112 SET cte_max_recursion_depth=0;
113 --error ER_CTE_MAX_RECURSION_DEPTH
114 WITH RECURSIVE qn AS (
115 SELECT 1 AS a UNION ALL
116 SELECT 1+a FROM qn WHERE a<0)
117 SELECT COUNT(*) FROM qn;