revision-id: 5aa4431160f9fb9ff96f46f7b898542445d985cd (mariadb-10.2.31-263-g5aa4431) parent(s): 187b9c924ebaff8f02fb4e2139a01fd1512e3dc9 author: Igor Babaev committer: Igor Babaev timestamp: 2020-06-06 10:41:08 -0700 message: MDEV-22748 MariaDB crash on WITH RECURSIVE large query This bug is the same as the bug MDEV-17024. The crashes caused by these bugs were due to premature cleanups of the unit specifying recursive CTEs that happened in some cases when there were several outer references the same recursive CTE. The problem of premature cleanups for recursive CTEs could be already resolved by the correction in TABLE_LIST::set_as_with_table() introduced in this patch. ALL other changes introduced by the patches for MDEV-17024 and MDEV-22748 guarantee that this clean-ups are performed as soon as possible: when the select containing the last outer reference to a recursive CTE is being cleaned up the specification of the recursive CTE should be cleaned up as well. --- mysql-test/r/cte_recursive.result | 170 ++++++++++++++++++++++++++++++++++++-- mysql-test/t/cte_recursive.test | 70 +++++++++++++++- sql/sql_cte.cc | 1 + sql/sql_derived.cc | 6 +- sql/sql_union.cc | 13 +++ 5 files changed, 247 insertions(+), 13 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 0b8bc3f..6404931 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00 1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00 -2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where 3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where Warnings: Note 1003 with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name ` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` # simple mutual recursion @@ -1302,12 +1302,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 +NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12 2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL -5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 -NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL explain FORMAT=JSON with recursive prev_gen @@ -3326,13 +3326,13 @@ select * from cte1, cte2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used -3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` prepare stmt from "with recursive @@ -3391,6 +3391,65 @@ cte2 as (select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) select * from cte1, cte2 where cte1.c1 = 3; c1 c2 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +c2 c1 +1 2 +explain extended with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1` +prepare stmt from "with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1"; +execute stmt; +c2 c1 +1 2 +execute stmt; +c2 c1 +1 2 +drop procedure p; +drop table t2; +create table t2 (c1 int, c2 int); +create procedure p() insert into t2 with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +call p(); +select * from t2; +c1 c2 +1 2 drop procedure p; drop table t1,t2; # @@ -3809,4 +3868,101 @@ ANALYZE } drop function f1; drop table t1,t2; -End of 10.2 tests +# +# MDEV-22748: two materialized CTEs using the same recursive CTE +# (see also test case for MDEV-17024) +# +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); +WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXPLAIN EXTENDED WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY <derived5> ref key0 key0 9 const 0 0.00 Using where +1 PRIMARY <derived4> ref key0 key0 9 const 0 0.00 Using where +2 DERIVED t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive cte as (select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where 2018 = `cte3`.`YEAR` and 2018 = `cte2`.`YEAR` +PREPARE stmt FROM "WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)"; +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +CALL p(); +SELECT * FROM t4; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; +# +# End of 10.2 tests +# diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 1c0280f..d190458 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2390,6 +2390,30 @@ select * from cte1, cte2 where cte1.c1 = 3; eval $q3; +let $q4= +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; + +eval $q4; +eval explain extended $q4; +eval prepare stmt from "$q4"; +execute stmt; +execute stmt; + +drop procedure p; +drop table t2; + +create table t2 (c1 int, c2 int); +eval create procedure p() insert into t2 $q4; +call p(); +select * from t2; + drop procedure p; drop table t1,t2; @@ -2574,4 +2598,48 @@ eval analyze format=json $q; drop function f1; drop table t1,t2; ---echo End of 10.2 tests +--echo # +--echo # MDEV-22748: two materialized CTEs using the same recursive CTE +--echo # (see also test case for MDEV-17024) +--echo # + +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); + +let $q= +WITH RECURSIVE +cte AS + (SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn + FROM t1 + UNION ALL + SELECT YEAR(cte.st + INTERVAL 1 MONTH), + cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY + FROM cte JOIN t1 + WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); + +eval $q; +eval EXPLAIN EXTENDED $q; +eval PREPARE stmt FROM "$q"; +EXECUTE stmt; +EXECUTE stmt; + +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +eval CREATE PROCEDURE p() INSERT INTO t4 $q; +CALL p(); +SELECT * FROM t4; + +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index d922a7a..fe8e0de 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1099,6 +1099,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) { derived= with_elem->spec; if (derived != select_lex->master_unit() && + !with_elem->is_recursive && !is_with_table_recursive_reference()) { derived->move_as_slave(select_lex); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 6785bf8..39499e6 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1054,7 +1054,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_ASSERT(derived->table && derived->table->is_created()); select_union *derived_result= derived->derived_result; SELECT_LEX *save_current_select= lex->current_select; - bool derived_recursive_is_filled= false; if (derived_is_recursive) { @@ -1067,7 +1066,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) { /* In this case all iteration are performed */ res= derived->fill_recursive(thd); - derived_recursive_is_filled= true; } } else if (unit->is_union()) @@ -1123,9 +1121,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) } } - if (res || (!lex->describe && - (!derived_is_recursive || - derived_recursive_is_filled))) + if (res || (!lex->describe && !derived_is_recursive)) unit->cleanup(); lex->current_select= save_current_select; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7e4d06b..b2198eb 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1540,6 +1540,19 @@ bool st_select_lex::cleanup() delete join; join= 0; } + for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local) + { + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + /* + If query is killed before open_and_process_table() for tbl + is called then 'with' is already set, but 'derived' is not. + */ + st_select_lex_unit *unit= tbl->with->spec; + error|= (bool) error | (uint) unit->cleanup(); + } + } for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) {