revision-id: 2056c3cdcb8a16e017ae56c5367ab67091a29efd (mariadb-10.2.16-110-g2056c3c) parent(s): b245023fe0bc6fa0bd6e2dfa9352b30b71d0d27d author: Igor Babaev committer: Igor Babaev timestamp: 2018-09-07 20:10:04 -0700 message: MDEV-17024 Crash on large query This problem manifested itself when a join query used two or more materialized CTE such that each of them employed the same recursive CTE. The bug caused a crash. The crash happened because the cleanup() function was performed premature for recursive CTE. This clean up was induced by the cleanup of the first CTE referenced the recusrsive CTE. This cleanup destroyed the structures that would allow to read from the temporary table containing the rows of the recursive CTE and an attempt to read these rows for the second CTE referencing the recursive CTE triggered a crash. The clean up for a recursive CTE R should be performed after the cleanup of the last materialized CTE that uses R. --- mysql-test/r/cte_recursive.result | 93 +++++++++++++++++++++++++++++++++++++++ mysql-test/t/cte_recursive.test | 67 ++++++++++++++++++++++++++++ sql/sql_base.cc | 9 ++++ sql/sql_class.h | 8 +++- sql/sql_cte.h | 11 ++++- sql/sql_derived.cc | 6 ++- sql/sql_union.cc | 33 +++++++++++++- 7 files changed, 222 insertions(+), 5 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index c892c76..55733be 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3300,3 +3300,96 @@ SELECT func(); func() 1 DROP FUNCTION func; +# +# MDEV-17024: two materialized CTEs using the same recursive CTE +# +create table t1 (id int); +insert into t1 values (1), (2), (3); +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 cte1, cte2; +c1 c2 +2 1 +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 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) +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 +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 cte1, cte2"; +execute stmt; +c1 c2 +2 1 +execute stmt; +c1 c2 +2 1 +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 cte1, cte2; +call p(); +select * from t2; +c1 c2 +2 1 +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 cte1; +c1 +2 +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 t1), +cte2 as +(select count(*) as c2 from t2) +select * from cte1,cte2; +c1 c2 +3 1 +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 cte1, cte2 where cte1.c1 = 3; +c1 c2 +drop procedure p; +drop table t1,t2; diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 4eee9ef..e3a9349 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2323,3 +2323,70 @@ RETURN SELECT func(); DROP FUNCTION func; + +--echo # +--echo # MDEV-17024: two materialized CTEs using the same recursive CTE +--echo # + +create table t1 (id int); +insert into t1 values (1), (2), (3); + +let $q= +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 cte1, cte2; + +eval $q; +eval explain extended $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; + +create table t2 (c1 int, c2 int); +eval create procedure p() insert into t2 $q; +call p(); +select * from t2; + +let $q1= +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 cte1; + +eval $q1; + +let $q2= +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 t1), +cte2 as +(select count(*) as c2 from t2) +select * from cte1,cte2; + +eval $q2; + +let $q3= +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 cte1, cte2 where cte1.c1 = 3; + +eval $q3; + +drop procedure p; +drop table t1,t2; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 36bf39e..cae5b4a 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3295,6 +3295,15 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables, */ if (tables->with) { + if (tables->is_recursive_with_table() && + !tables->is_with_table_recursive_reference()) + { + tables->with->rec_outer_references++; + With_element *with_elem= tables->with; + while ((with_elem= with_elem->get_next_mutually_recursive()) != + tables->with) + with_elem->rec_outer_references++; + } if (tables->set_as_with_table(thd, tables->with)) DBUG_RETURN(1); else diff --git a/sql/sql_class.h b/sql/sql_class.h index 9a10083..674ae02 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -5106,10 +5106,16 @@ class select_union_recursive :public select_union TABLE *first_rec_table_to_update; /* The temporary tables used for recursive table references */ List<TABLE> rec_tables; + /* + The count of how many times cleanup() was called with cleaned==false + for the unit specifying the recursive CTE for which this object was created + or for the unit specifying a CTE that mutually recursive with this CTE. + */ + uint cleanup_count; select_union_recursive(THD *thd_arg): select_union(thd_arg), - incr_table(0), first_rec_table_to_update(0) {}; + incr_table(0), first_rec_table_to_update(0), cleanup_count(0) {}; int send_data(List<Item> &items); bool create_result_table(THD *thd, List<Item> *column_types, diff --git a/sql/sql_cte.h b/sql/sql_cte.h index 70526e8..6351b65 100644 --- a/sql/sql_cte.h +++ b/sql/sql_cte.h @@ -98,7 +98,14 @@ class With_element : public Sql_alloc for the definition of this element */ bool is_recursive; - + /* + For a simple recursive CTE: the number of references to the CTE from + outside of the CTE specification. + For a CTE mutually recursive with other CTEs : the total number of + references to all these CTEs outside of their specification. + Each of these mutually recursive CTEs has the same value in this field. + */ + uint rec_outer_references; /* Any non-recursive select in the specification of a recursive with element is a called anchor. In the case mutually recursive @@ -140,7 +147,7 @@ class With_element : public Sql_alloc top_level_dep_map(0), sq_rec_ref(NULL), next_mutually_recursive(NULL), references(0), query_name(name), column_list(list), spec(unit), - is_recursive(false), with_anchor(false), + is_recursive(false), rec_outer_references(0), with_anchor(false), level(0), rec_result(NULL) { unit->with_element= this; } diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 0147271..44f8d74 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1083,6 +1083,7 @@ 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) { @@ -1095,6 +1096,7 @@ 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()) @@ -1150,7 +1152,9 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) } } - if (res || (!lex->describe && !derived_is_recursive)) + if (res || (!lex->describe && + (!derived_is_recursive || + derived_recursive_is_filled))) unit->cleanup(); lex->current_select= save_current_select; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index b409790..419ccf4 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1337,6 +1337,37 @@ bool st_select_lex_unit::cleanup() { DBUG_RETURN(FALSE); } + /* + When processing a PS/SP or an EXPLAIN command cleanup of a unit can + be performed immediately when the unit is reached in the cleanup + traversal initiated by the cleanup of the main unit. + */ + if (!thd->stmt_arena->is_stmt_prepare() && !thd->lex->describe && + with_element && with_element->is_recursive && union_result) + { + select_union_recursive *result= with_element->rec_result; + if (++result->cleanup_count == with_element->rec_outer_references) + { + /* + Perform cleanup for with_element and for all with elements + mutually recursive with it. + */ + cleaned= 1; + with_element->get_next_mutually_recursive()->spec->cleanup(); + } + else + { + /* + Just increment by 1 cleanup_count for with_element and + for all with elements mutually recursive with it. + */ + With_element *with_elem= with_element; + while ((with_elem= with_elem->get_next_mutually_recursive()) != + with_element) + with_elem->rec_result->cleanup_count++; + DBUG_RETURN(FALSE); + } + } cleaned= 1; for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select()) @@ -1367,7 +1398,7 @@ bool st_select_lex_unit::cleanup() if (with_element && with_element->is_recursive) { - if (union_result ) + if (union_result) { ((select_union_recursive *) union_result)->cleanup(); delete union_result;