Debian Codegraph @

mysql-test/suite/sys_vars/r/cte_max_recursion_depth_func.result

raw
1 #
2 # Bug#26136509 ADD A MAX_RECURSION VARIABLE TO LIMIT RECURSION IN CTES
3 #
4 SELECT @@cte_max_recursion_depth;
5 @@cte_max_recursion_depth
6 1000
7 SET @m = 1001;
8 WITH RECURSIVE qn AS (
9 SELECT 1 AS a UNION ALL
10 SELECT 1+a FROM qn WHERE a<@m)
11 SELECT COUNT(*) FROM qn;
12 ERROR HY000: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
13 SET @m = 1000;
14 WITH RECURSIVE qn AS (
15 SELECT 1 AS a UNION ALL
16 SELECT 1+a FROM qn WHERE a<@m)
17 SELECT COUNT(*) FROM qn;
18 COUNT(*)
19 1000
20 SET cte_max_recursion_depth=10;
21 SET @m = 11;
22 WITH RECURSIVE qn AS (
23 SELECT 1 AS a UNION ALL
24 SELECT 1+a FROM qn WHERE a<@m)
25 SELECT COUNT(*) FROM qn;
26 ERROR HY000: Recursive query aborted after 11 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
27 SET @m = 10;
28 WITH RECURSIVE qn AS (
29 SELECT 1 AS a UNION ALL
30 SELECT 1+a FROM qn WHERE a<@m)
31 SELECT COUNT(*) FROM qn;
32 COUNT(*)
33 10
34 set cte_max_recursion_depth=default;
35 SET @m = 1001;
36 WITH RECURSIVE qn AS
37 (SELECT 1 AS a, 0 AS b UNION ALL
38 SELECT 1+a, 1 FROM qn WHERE b IN (0,1) AND a<@m UNION ALL
39 SELECT 1+a, 2 FROM qn WHERE b IN (0,2) AND a<@m UNION ALL
40 SELECT 1+a, 3 FROM qn WHERE b IN (0,3) AND a<@m
41 ) SELECT COUNT(*) FROM qn;
42 ERROR HY000: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
43 SET @m = 999;
44 WITH RECURSIVE qn AS
45 (SELECT 1 AS a, 0 AS b UNION ALL
46 SELECT 1+a, 1 FROM qn WHERE b IN (0,1) AND a<@m UNION ALL
47 SELECT 1+a, 2 FROM qn WHERE b IN (0,2) AND a<@m UNION ALL
48 SELECT 1+a, 3 FROM qn WHERE b IN (0,3) AND a<@m
49 ) SELECT COUNT(*) FROM qn;
50 COUNT(*)
51 2995
52 SET cte_max_recursion_depth=10;
53 SET @m = 11;
54 WITH RECURSIVE qn AS
55 (SELECT 1 AS a, 0 AS b UNION ALL
56 SELECT 1+a, 1 FROM qn WHERE b IN (0,1) AND a<@m UNION ALL
57 SELECT 1+a, 2 FROM qn WHERE b IN (0,2) AND a<@m UNION ALL
58 SELECT 1+a, 3 FROM qn WHERE b IN (0,3) AND a<@m
59 ) SELECT COUNT(*) FROM qn;
60 ERROR HY000: Recursive query aborted after 11 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
61 SET @m = 9;
62 WITH RECURSIVE qn AS
63 (SELECT 1 AS a, 0 AS b UNION ALL
64 SELECT 1+a, 1 FROM qn WHERE b IN (0,1) AND a<@m UNION ALL
65 SELECT 1+a, 2 FROM qn WHERE b IN (0,2) AND a<@m UNION ALL
66 SELECT 1+a, 3 FROM qn WHERE b IN (0,3) AND a<@m
67 ) SELECT COUNT(*) FROM qn;
68 COUNT(*)
69 25
70 SET cte_max_recursion_depth=DEFAULT;
71 SET cte_max_recursion_depth=10;
72 SET @m = 11;
73 WITH RECURSIVE
74 onetwo(b) AS (SELECT 1 UNION SELECT 2),
75 qn AS (
76 SELECT 1 AS a, 0 AS b UNION ALL
77 SELECT 1+a, onetwo.b FROM onetwo, qn WHERE a<@m)
78 SELECT COUNT(*) FROM qn;
79 ERROR HY000: Recursive query aborted after 11 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
80 SET @m = 10;
81 WITH RECURSIVE
82 onetwo(b) AS (SELECT 1 UNION SELECT 2),
83 qn AS (
84 SELECT 1 AS a, 0 AS b UNION ALL
85 SELECT 1+a, onetwo.b FROM onetwo, qn WHERE a<@m)
86 SELECT COUNT(*) FROM qn;
87 COUNT(*)
88 1023
89 SET cte_max_recursion_depth=default;
90 SET @m = 1001;
91 WITH RECURSIVE qn AS (
92 SELECT 1 AS a UNION DISTINCT
93 SELECT 1+a FROM qn WHERE a<@m)
94 SELECT COUNT(*) FROM qn;
95 ERROR HY000: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
96 SET @m = 1000;
97 WITH RECURSIVE qn AS (
98 SELECT 1 AS a UNION DISTINCT
99 SELECT 1+a FROM qn WHERE a<@m)
100 SELECT COUNT(*) FROM qn;
101 COUNT(*)
102 1000
103 SET cte_max_recursion_depth=10;
104 SET @m = 11;
105 WITH RECURSIVE qn AS (
106 SELECT 1 AS a UNION DISTINCT
107 SELECT 1+a FROM qn WHERE a<@m)
108 SELECT COUNT(*) FROM qn;
109 ERROR HY000: Recursive query aborted after 11 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
110 SET @m = 10;
111 WITH RECURSIVE qn AS (
112 SELECT 1 AS a UNION DISTINCT
113 SELECT 1+a FROM qn WHERE a<@m)
114 SELECT COUNT(*) FROM qn;
115 COUNT(*)
116 10
117 set cte_max_recursion_depth=default;
118 SET @m = 1001;
119 WITH RECURSIVE qn AS
120 (SELECT 1 AS a, 0 AS b UNION DISTINCT
121 SELECT 1+a, 1 FROM qn WHERE b IN (0,1) AND a<@m UNION DISTINCT
122 SELECT 1+a, 2 FROM qn WHERE b IN (0,2) AND a<@m UNION DISTINCT
123 SELECT 1+a, 3 FROM qn WHERE b IN (0,3) AND a<@m
124 ) SELECT COUNT(*) FROM qn;
125 ERROR HY000: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
126 SET @m = 999;
127 WITH RECURSIVE qn AS
128 (SELECT 1 AS a, 0 AS b UNION DISTINCT
129 SELECT 1+a, 1 FROM qn WHERE b IN (0,1) AND a<@m UNION DISTINCT
130 SELECT 1+a, 2 FROM qn WHERE b IN (0,2) AND a<@m UNION DISTINCT
131 SELECT 1+a, 3 FROM qn WHERE b IN (0,3) AND a<@m
132 ) SELECT COUNT(*) FROM qn;
133 COUNT(*)
134 2995
135 SET cte_max_recursion_depth=10;
136 SET @m = 11;
137 WITH RECURSIVE qn AS
138 (SELECT 1 AS a, 0 AS b UNION DISTINCT
139 SELECT 1+a, 1 FROM qn WHERE b IN (0,1) AND a<@m UNION DISTINCT
140 SELECT 1+a, 2 FROM qn WHERE b IN (0,2) AND a<@m UNION DISTINCT
141 SELECT 1+a, 3 FROM qn WHERE b IN (0,3) AND a<@m
142 ) SELECT COUNT(*) FROM qn;
143 ERROR HY000: Recursive query aborted after 11 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
144 SET @m = 9;
145 WITH RECURSIVE qn AS
146 (SELECT 1 AS a, 0 AS b UNION DISTINCT
147 SELECT 1+a, 1 FROM qn WHERE b IN (0,1) AND a<@m UNION DISTINCT
148 SELECT 1+a, 2 FROM qn WHERE b IN (0,2) AND a<@m UNION DISTINCT
149 SELECT 1+a, 3 FROM qn WHERE b IN (0,3) AND a<@m
150 ) SELECT COUNT(*) FROM qn;
151 COUNT(*)
152 25
153 SET cte_max_recursion_depth=DEFAULT;
154 SET cte_max_recursion_depth=10;
155 SET @m = 11;
156 WITH RECURSIVE
157 onetwo(b) AS (SELECT 1 UNION SELECT 2),
158 qn AS (
159 SELECT 1 AS a, 0 AS b UNION DISTINCT
160 SELECT 1+a, onetwo.b FROM onetwo, qn WHERE a<@m)
161 SELECT COUNT(*) FROM qn;
162 ERROR HY000: Recursive query aborted after 11 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
163 SET @m = 10;
164 WITH RECURSIVE
165 onetwo(b) AS (SELECT 1 UNION SELECT 2),
166 qn AS (
167 SELECT 1 AS a, 0 AS b UNION DISTINCT
168 SELECT 1+a, onetwo.b FROM onetwo, qn WHERE a<@m)
169 SELECT COUNT(*) FROM qn;
170 COUNT(*)
171 19
172 SET cte_max_recursion_depth=default;
173 SET cte_max_recursion_depth=0;
174 WITH RECURSIVE qn AS (
175 SELECT 1 AS a UNION ALL
176 SELECT 1+a FROM qn WHERE a<0)
177 SELECT COUNT(*) FROM qn;
178 ERROR HY000: Recursive query aborted after 1 iterations. Try increasing @@cte_max_recursion_depth to a larger value.