| 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; |