revision-id: 46960365b102b1b446c300ed4da606e63ddfab5d (mariadb-10.2.18-117-g4696036) parent(s): 3e5162d814c522da6b0f19c3f6baae1ab5035db8 author: Igor Babaev committer: Igor Babaev timestamp: 2018-12-01 15:06:04 -0800 message: MDEV-17871 Crash when running explain with CTE When the with clause of a query contains a recursive CTE that is not used then processing of EXPLAIN for this query does not require optimization of the unit specifying this CTE. In this case if 'derived' is the TABLE_LIST object created for this CTE then derived->derived_result is NULL and any assignment to derived->derived_result->table causes a crash. After fixing this problem in the code of st_select_lex_unit::prepare() EXPLAIN for such a query worked without crashes. Yet an execution plan for the recursive CTE appeared there. The cause of this problem was an incorrect condition used in JOIN::save_explain_data_intern() that determined whether CTE was to be optimized or not. A similar condition was used in select_describe() and this patch has corrected it as well. --- mysql-test/r/cte_recursive.result | 48 +++++++++++++++++++++++++++++++++++++++ mysql-test/t/cte_recursive.test | 39 +++++++++++++++++++++++++++++++ sql/sql_select.cc | 11 +++++---- sql/sql_union.cc | 7 ++++-- 4 files changed, 98 insertions(+), 7 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 7853026..667b8c4 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3600,3 +3600,51 @@ Mandelbrot Set .............................................................................. ........................................................................... ........................................................................ +# +# MDEV-17871: EXPLAIN for query with not used recursive cte +# +create table t1 (a int); +insert into t1 values (2), (1), (4), (3); +explain extended +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from cte as t; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 4 100.00 Using where +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with recursive cte as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union select `cte`.`a` + 1 AS `a+1` from `cte` where `cte`.`a` < 3)select `t`.`a` AS `a` from `cte` `t` +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from cte as t; +a +1 +2 +3 +explain extended +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from t1 as t; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 with recursive cte as (select `*` AS `*` from `test`.`t1` where `a` = 1 union select `a` + 1 AS `a+1` from `cte` where `a` < 3)select `test`.`t`.`a` AS `a` from `test`.`t1` `t` +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from t1 as t; +a +2 +1 +4 +3 +create table t2 ( i1 int, i2 int); +insert into t2 values (1,1),(2,2); +explain +with recursive cte as +( select * from t1 union select s1.* from t1 as s1, cte where s1.i1 = cte.i2 ) +select * from t1 as t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t ALL NULL NULL NULL NULL 4 +drop table t1,t2; diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 3c5bc62..0923ca3 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2495,3 +2495,42 @@ SELECT GROUP_CONCAT( FROM Zt GROUP BY Iy ORDER BY Iy; + +--echo # +--echo # MDEV-17871: EXPLAIN for query with not used recursive cte +--echo # + +create table t1 (a int); +insert into t1 values (2), (1), (4), (3); + +let $rec_cte = +with recursive cte as + (select * from t1 where a=1 union select a+1 from cte where a<3); + +eval +explain extended +$rec_cte +select * from cte as t; + +eval +$rec_cte +select * from cte as t; + +eval +explain extended +$rec_cte +select * from t1 as t; + +eval +$rec_cte +select * from t1 as t; + +create table t2 ( i1 int, i2 int); +insert into t2 values (1,1),(2,2); + +explain +with recursive cte as + ( select * from t1 union select s1.* from t1 as s1, cte where s1.i1 = cte.i2 ) +select * from t1 as t; + +drop table t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index db3ed8a..1309c7b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25041,13 +25041,13 @@ int JOIN::save_explain_data_intern(Explain_query *output, (1) they are not parts of ON clauses that were eliminated by table elimination. (2) they are not merged derived tables - (3) they are not unreferenced CTE + (3) they are not hanging CTEs (they are needed for execution) */ if (!(tmp_unit->item && tmp_unit->item->eliminated) && // (1) (!tmp_unit->derived || tmp_unit->derived->is_materialized_derived()) && // (2) - !(tmp_unit->with_element && - !tmp_unit->with_element->is_referenced())) // (3) + !(tmp_unit->with_element && + (!tmp_unit->derived || !tmp_unit->derived->derived_result))) // (3) { explain->add_child(tmp_unit->first_select()->select_number); } @@ -25108,11 +25108,12 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, Save plans for child subqueries, when (1) they are not parts of eliminated WHERE/ON clauses. (2) they are not VIEWs that were "merged for INSERT". - (3) they are not unreferenced CTE. + (3) they are not hanging CTEs (they are needed for execution) */ if (!(unit->item && unit->item->eliminated) && // (1) !(unit->derived && unit->derived->merged_for_insert) && // (2) - !(unit->with_element && !unit->with_element->is_referenced())) // (3) + !(unit->with_element && + (!unit->derived || !unit->derived->derived_result))) // (3) { if (mysql_explain_union(thd, unit, result)) DBUG_VOID_RETURN; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 44879f6..eb66dce 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -694,8 +694,11 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, instantiate_tmp_table, false)) goto err; if (!derived->table) - derived->table= derived->derived_result->table= - with_element->rec_result->rec_tables.head(); + { + derived->table= with_element->rec_result->rec_tables.head(); + if (derived->derived_result) + derived->derived_result->table= derived->table; + } with_element->mark_as_with_prepared_anchor(); is_rec_result_table_created= true; }