[Commits] 9545837: MDEV-26189 Missing handling of unknown column in WHERE of recursive CTE

IgorBabaev igor at mariadb.com
Wed Jul 21 09:14:43 EEST 2021


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


More information about the commits mailing list