revision-id: 9545837ad14c64e2c4491eaa1bd1c56bc2cbc589 (mariadb-10.2.31-1070-g9545837) parent(s): 872422dcbbe3681a794935fb2cae422d9d5f4108 author: Igor Babaev committer: Igor Babaev timestamp: 2021-07-20 23:14:43 -0700 message: MDEV-26189 Missing handling of unknown column in WHERE of recursive CTE SQL processor failed to catch references to unknown columns and other errors of the phase of semantic analysis in the specification of a hanging recursive CTE. This happened because the function With_clause::prepare_unreferenced_elements() failed to detect a CTE as a hanging CTE if the CTE was recursive. Fixing this problem in the code of the mentioned function opened another problem: EXPLAIN started including the lines for the specifications of hanging recursive CTEs in its output. This problem also was fixed in this patch. Approved by Dmitry Shulga <dmitry.shulga@mariadb.com> --- mysql-test/r/cte_recursive.result | 46 +++++++++++++++++++++++++++++++++---- mysql-test/t/cte_recursive.test | 48 +++++++++++++++++++++++++++++++++++++-- sql/sql_cte.cc | 3 ++- sql/sql_cte.h | 2 ++ sql/sql_select.cc | 12 ++++++---- 5 files changed, 100 insertions(+), 11 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index a4d821e..1b1fd8b 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3689,7 +3689,7 @@ 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` +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 `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; @@ -3702,10 +3702,10 @@ 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; +( select * from t2 union select s1.* from t2 as s1, cte where s1.i1 = cte.i2 ) +select * from t2 as t; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t ALL NULL NULL NULL NULL 4 +1 PRIMARY t ALL NULL NULL NULL NULL 2 drop table t1,t2; # # MDEV-22042: ANALYZE of query using stored function and recursive CTE @@ -4481,5 +4481,43 @@ b deallocate prepare stmt; drop table t1,t2; # +# MDEV-26189: Unknown column reference within hanging recursive CTE +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) +select * from t1 as t; +ERROR 42S22: Unknown column 'r.b' in 'where clause' +explain with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) +select * from t1 as t; +ERROR 42S22: Unknown column 'r.b' in 'where clause' +create procedure sp1() with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) +select * from t1 as t; +call sp1(); +ERROR 42S22: Unknown column 'r.b' in 'where clause' +call sp1(); +ERROR 42S22: Unknown column 'r.b' in 'where clause' +with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) +select * from t1 as t; +ERROR 42S22: Unknown column 's1.b' in 'where clause' +explain with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) +select * from t1 as t; +ERROR 42S22: Unknown column 's1.b' in 'where clause' +create procedure sp2() with recursive +r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) +select * from t1 as t; +call sp2(); +ERROR 42S22: Unknown column 's1.b' in 'where clause' +call sp2(); +ERROR 42S22: Unknown column 's1.b' in 'where clause' +drop procedure sp1; +drop procedure sp2; +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 49f9c1f..cdd3a07 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2556,8 +2556,8 @@ 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; + ( select * from t2 union select s1.* from t2 as s1, cte where s1.i1 = cte.i2 ) +select * from t2 as t; drop table t1,t2; @@ -2841,5 +2841,49 @@ deallocate prepare stmt; drop table t1,t2; --echo # +--echo # MDEV-26189: Unknown column reference within hanging recursive CTE +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +let $q1= +with recursive + r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) +select * from t1 as t; + +--ERROR ER_BAD_FIELD_ERROR +eval $q1; +--ERROR ER_BAD_FIELD_ERROR +eval explain $q1; + +eval create procedure sp1() $q1; +--ERROR ER_BAD_FIELD_ERROR +call sp1(); +--ERROR ER_BAD_FIELD_ERROR +call sp1(); + +let $q2= +with recursive + r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) +select * from t1 as t; + +--ERROR ER_BAD_FIELD_ERROR +eval $q2; +--ERROR ER_BAD_FIELD_ERROR +eval explain $q2; + +eval create procedure sp2() $q2; +--ERROR ER_BAD_FIELD_ERROR +call sp2(); +--ERROR ER_BAD_FIELD_ERROR +call sp2(); + +drop procedure sp1; +drop procedure sp2; + +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index b720eac..22a9984 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -911,7 +911,8 @@ bool With_clause::prepare_unreferenced_elements(THD *thd) with_elem; with_elem= with_elem->next) { - if (!with_elem->is_referenced() && with_elem->prepare_unreferenced(thd)) + if ((with_elem->is_hanging_recursive() || !with_elem->is_referenced()) && + with_elem->prepare_unreferenced(thd)) return true; } diff --git a/sql/sql_cte.h b/sql/sql_cte.h index 5f30894..d484dcf 100644 --- a/sql/sql_cte.h +++ b/sql/sql_cte.h @@ -242,6 +242,8 @@ class With_element : public Sql_alloc bool is_referenced() { return referenced; } + bool is_hanging_recursive() { return is_recursive && !rec_outer_references; } + void inc_references() { references++; } bool rename_columns_of_derived_unit(THD *thd, st_select_lex_unit *unit); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 2bb01ee..ff584e9 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25276,8 +25276,10 @@ int JOIN::save_explain_data_intern(Explain_query *output, 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->derived || !tmp_unit->derived->derived_result))) // (3) + (!tmp_unit->with_element || + (tmp_unit->derived && + tmp_unit->derived->derived_result && + !tmp_unit->with_element->is_hanging_recursive()))) // (3) { explain->add_child(tmp_unit->first_select()->select_number); } @@ -25342,8 +25344,10 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, */ if (!(unit->item && unit->item->eliminated) && // (1) !(unit->derived && unit->derived->merged_for_insert) && // (2) - !(unit->with_element && - (!unit->derived || !unit->derived->derived_result))) // (3) + (!unit->with_element || + (unit->derived && + unit->derived->derived_result && + !unit->with_element->is_hanging_recursive()))) // (3) { if (mysql_explain_union(thd, unit, result)) DBUG_VOID_RETURN;