MDEV-29361 Infinite recursive calls when detecting CTE dependencies

This patch resolves the problem of improper name resolution of table
references to embedded CTEs for some queries. This improper binding could
lead to
  - infinite sequence of calls of recursive functions
  - crashes due to resolution of null pointers
  - wrong result sets returned by queries
  - bogus error messages

If the definition of a CTE contains with clauses then such CTE is called
embedding CTE while CTEs from the with clauses are called embedded CTEs.
If a table reference used in the definition of an embedded CTE cannot be
resolved within the unit that contains this reference it still may be
resolved against a CTE definition from the with clause with one of the
embedding CTEs.
A table reference can be resolved against a CTE definition if it used in
the the scope of this definition and it refers to the name of the CTE.
Table reference t is in the scope of the CTE definition of CTE cte if
- the definition of cte is an element of a with clause declared as
  RECURSIVE and the reference t belongs either to the unit to which
  this with clause is attached or to one of the elements of this clause
- the definition of cte is an element of a with clause without RECURSIVE
  specifier and the reference t belongs either to the unit to which this
  with clause is attached or to one of the elements from this clause that
  are placed before the definition of cte.
If a table reference can be resolved against several CTE definitions then
it is bound to the most embedded.

The code before this patch not always resolved table references used in
embedded CTE according to the above rules.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

1 files changed, 664 insertions, 0 deletions

diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index ca97c2d2900..f5babc6cb65 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -3207,5 +3207,669 @@ set @@sql_mode=default;
 drop table t1,t2;
 
 --echo #
+--echo # MDEV-29361: Embedded recursive / non-recursive CTE within
+--echo #             the scope of another embedded CTE with the same name
+--echo #
+
+create table t1 (a int);
+insert into t1 values (4), (5);
+create table t2 (a int);
+insert into t2 values (6), (8);
+create table t3 (a int);
+insert into t3 values (1), (9);
+
+
+with recursive
+x as
+(
+  select a from t1 union select a+1 from x as r1 where a < 7
+)
+select * from x as s1;
+
+with recursive
+x as
+(
+  select a from t2
+  union
+  select a+2 from x as r2 where a < 10
+)
+select a from x as s2;
+
+# All recursive CTEs with name x are embedded in in the definition of 'cte',
+# without this embedding CTE the bug could not be reproduced
+
+# two recursive CTEs with name x, the second CTE is in the scope
+# of the first one, but does not use it
+# before fix of this bug: wrong result set
+
+with
+cte as
+(
+  with recursive
+  x as
+  (
+    select a from t1 union select a+1 from x as r1 where a < 7
+  )
+  select * from x as s1
+  where s1.a in (
+                 with recursive
+                 x as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# two recursive CTEs with name x, the second CTE is in the scope of the first
+# one, but does not use it; there are two non-recursive references to the latter
+# before fix of this bug: wrong result set
+
+with
+cte as
+(
+  with recursive
+  x(a) as
+  (
+    select a from t1 union select a+1 from x as r1 where a < 7
+  )
+  select s1.a from x as s1, x
+  where s1.a = x.a and
+        x.a in (
+                 with recursive
+                 x(a) as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# x as r1 belongs to the definition of CTE x from non-RECURSIVE with clause
+# before fix of this bug: infinite sequence of recursive calls
+
+--error ER_NO_SUCH_TABLE
+with
+cte as
+(
+  with
+  x as
+  (
+    select a from t1 union select a+1 from x as r1 where a < 7
+  )
+  select * from x as s1
+  where s1.a in (
+                 with recursive
+                 x as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# x as r2 belongs to the definition of CTE x from non-RECURSIVE with clause
+# yet it is in the scope of another CTE with the same name
+# before fix of this bug: crash in With_element::get_name()
+
+with
+cte as
+(
+  with recursive
+  x as
+  (
+    select a from t1 union select a+1 from x as r1 where a < 7
+  )
+  select * from x as s1
+  where s1.a in (
+                 with
+                 x as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# x as r1 is in the definition of CTE x from non-RECURSIVE with clause, thus
+# although x as r2 is in the scope of the first CTE x an error is expected
+# before fix of this bug: crash in With_element::get_name()
+
+--error ER_NO_SUCH_TABLE
+with
+cte as
+(
+  with
+  x as
+  (
+    select a from t1 union select a+1 from x as r1 where a < 7
+  )
+  select * from x as s1
+  where s1.a in (
+                 with
+                 x as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# x as r2 belongs to the definition of CTE x from non-RECURSIVE with clause
+# and in the scope of recursive CTE y, but does not use the latter
+# before fix of this bug: crash in With_element::get_name()
+
+--error ER_NO_SUCH_TABLE
+with
+cte as
+(
+  with recursive
+  y as
+  (
+    select a from t1 union select a+1 from y as r1 where a < 7
+  )
+  select * from y as s1
+  where s1.a in (
+                 with
+                 x as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# x as r2 belongs to the definition of CTE x from non-RECURSIVE with clause
+# and in the scope of non-recursive CTE y, but does not use the latter
+# before fix of this bug: crash in With_element::get_name()
+
+--error ER_NO_SUCH_TABLE
+with
+cte as
+(
+  with
+  y(a) as
+  (
+    select a+5 from t1
+  )
+  select * from y as s1
+  where s1.a in (
+                 with
+                 x as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# in the subquery of the embedding CTE cte:
+# x as r1 is in the definition of CTE x from non-RECURSIVE with clause;
+# x as t2 is in the definition of CTE x from RECURSIVE with clause;
+# an error is expected to be reported for x as r1
+# before fix of this bug: infinite sequence of recursive calls
+
+--error ER_NO_SUCH_TABLE
+with
+cte as
+(
+  select (
+           with
+           x as
+           (
+             select a from x as r1
+           )
+           select * from x as s1
+           where s1.a in (
+                          with recursive
+                          x as
+                          (
+                            select a from t2
+                            union
+                            select a+2 from x as r2 where a < 10
+                          )
+                          select a from x as s2
+                        )
+         ) as r
+  from t3
+)
+select * from cte;
+
+
+# in the subquery of the embedding CTE cte:
+# x as r1 is in the definition of CTE x from non-RECURSIVE with clause, thus
+# although x as r2 is in the scope of the first CTE x an error is expected
+# before fix of this bug: crash in With_element::get_name()
+
+--error ER_NO_SUCH_TABLE
+with
+cte as
+(
+  select (
+           with
+           x as
+           (
+             select a from x as r1
+           )
+           select * from x as s1
+           where s1.a < 5 and
+                 s1.a in (
+                          with
+                          x as
+                          (
+                            select a from t2
+                            union
+                            select a+2 from x as r2 where a < 10
+                          )
+                          select a from x as s2
+                        )
+         ) as r
+  from t3
+)
+select * from cte;
+
+
+# in the subquery of the embedding CTE cte:
+# two recursive CTEs with name x, the second CTE is in the scope
+# of the first one, but does not use it
+# before fix of this bug: wrong result set
+
+with
+cte as
+(
+  select (
+           with recursive
+           x(a) as
+           (
+             select a+3 from t1 union select a+1 from x as r1 where a < 7
+           )
+           select * from x as s1
+           where s1.a < 8 and
+                 s1.a in (
+                           with recursive
+                           x(a) as
+                           (
+                             select a-2 from t2
+                             union
+                             select a+1 from x as r2 where a < 10
+                           )
+                           select a from x as s2
+                         )
+         ) as r
+  from t3
+)
+select * from cte;
+
+
+# in the subquery of the embedding CTE cte:
+# two recursive CTEs with name x, the second CTE is in the scope
+# of the first one, but does not use it
+# before fix of this bug: Subquery returns more than 1 row
+
+with
+cte as
+(
+  select (
+           with recursive
+           x as
+           (
+             select a from t1 union select a+1 from x as r1 where a < 7
+           )
+           select * from x as s1
+           where s1.a in (
+                           with recursive
+                           x as
+                           (
+                             select a from t2
+                             union
+                             select a+2 from x as r2 where a < 10
+                           )
+                           select a from x as s2
+                         )
+         ) as r
+  from t3
+)
+select * from cte;
+
+
+create table x (a int);
+insert into x values (3), (7), (1), (5), (6);
+
+
+# in the subquery of the embedding CTE cte:
+# one non-recursive CTEs with name x using table t in a subquery, the second
+# CTE x is recursive and is in the scope of the first one, but does not use it;
+# the query uses both CTE with name x.
+# before fix of this bug: infinite sequence of recursive calls
+
+--error ER_SUBQUERY_NO_1_ROW
+with
+cte as
+(
+  select (
+           with
+           x as
+           (
+             select ( select a from x as r1 ) as a from t1
+           )
+           select * from x as s1
+           where s1.a in (
+                           with recursive
+                           x as
+                           (
+                             select a from t2
+                             union
+                             select a+2 from x as r2 where a < 10
+                           )
+                           select a from x s2
+                         )
+         ) as r
+  from t3
+)
+select * from cte;
+
+
+# in the subquery of the embedding CTE cte:
+# one non-recursive CTEs with name x using table t, the second CTE x is
+# recursive and is in the scope of the first one, but does not use it;
+# the query uses only the second CTE with name x.
+# before fix of this bug: Subquery returns more than 1 row
+
+with
+cte as
+(
+  select (
+           with
+           x as
+           (
+             select ( select a from x ) as a from t1
+           )
+           select exists (
+                           with recursive
+                           x as
+                           (
+                             select a from t2
+                             union
+                             select a+2 from x as r2 where a < 10
+                           )
+                           select a from x
+                         )
+         ) as r
+  from t3
+)
+select * from cte;
+
+
+# in the subquery of the embedding CTE cte embedded in the CTE cte_e
+# one non-recursive CTEs with name x uses table t1, the second CTE x is
+# recursive and is in the scope of the first one, but does not use it;
+# CTE cte uses only the second CTE with name x;
+# the query has two refeences to cte_e
+# before fix of this bug: infinite sequence of recursive calls
+
+with
+cte_e as
+(
+  with
+  cte as
+  (
+    select (
+             with
+             x as
+             (
+               select ( select a from x ) from t1
+             )
+             select exists (
+                             with recursive
+                             x as
+                             (
+                               select a from t2
+                               union
+                               select a+2 from x as r2 where a < 10
+                             )
+                             select a from x
+                           )
+           ) as r
+    from t3
+  )
+  select * from cte
+)
+select s1.*, s2.*  from cte_e as s1, cte_e as s2;
+
+
+# check : with base table x all queries abobe that returned error
+# message ER_NO_SUCH_TABLE now return proper result sets
+
+with
+x as
+(
+  select a from t1 union select a+1 from x as r1 where a < 7
+)
+select * from x as s1;
+
+with
+x as
+(
+  select a from t2
+  union
+  select a+2 from x as r2 where a < 10
+)
+select a from x as s2;
+
+with recursive
+x as
+(
+  select a from t1 union select a+1 from x as r1 where a < 7
+)
+select * from x as s1;
+
+with recursive
+x as
+(
+  select a from t2
+  union
+  select a+2 from x as r2 where a < 10
+)
+select a from x as s2;
+
+
+# x as r1 is bound to table x, x as s1 is bound to the first CTE x
+# x as r2 and x as s2 are bound to the second CTE x
+# before fix of this bug: infinite sequence of recursive calls
+
+with
+cte as
+(
+  with
+  x as
+  (
+    select a from t1 union select a+1 from x as r1 where a < 7
+  )
+  select * from x as s1
+  where s1.a in (
+                 with recursive
+                 x as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# x as r1 is bound to table x, x as s1 is bound to the first CTE x
+# x as r1 is bound to the first CTE x, x as s2 is bound to the second CTE x
+# before fix of this bug: crash in With_element::get_name()
+
+with
+cte as
+(
+  with
+  x as
+  (
+    select a from t1 union select a+1 from x as r1 where a < 7
+  )
+  select * from x as s1
+  where s1.a in (
+                 with
+                 x as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# x as r2 is bound to table x, x as s2 is bound to CTE x
+# before fix of this bug: crash in With_element::get_name()
+
+with
+cte as
+(
+  with recursive
+  y as
+  (
+    select a from t1 union select a+1 from y as r1 where a < 7
+  )
+  select * from y as s1
+  where s1.a in (
+                 with
+                 x as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# x as r2 is bound to table x, x as s2 is bound to CTE x
+# before fix of this bug: crash in With_element::get_name()
+
+with
+cte as
+(
+  with
+  y(a) as
+  (
+    select a+5 from t1
+  )
+  select * from y as s1
+  where s1.a in (
+                 with
+                 x as
+                 (
+                   select a from t2
+                   union
+                   select a+2 from x as r2 where a < 10
+                 )
+                 select a from x as s2
+               )
+)
+select * from cte;
+
+
+# x as r1 is bound to table x, x as s1 is bound to the first CTE x
+# x as r2 and x as s2 are bound to the second CTE x
+# before fix of this bug: infinite sequence of recursive calls
+
+with
+cte as
+(
+  select (
+           with
+           x as
+           (
+             select a from x as r1
+           )
+           select * from x as s1
+           where s1.a in (
+                          with
+                          recursive x as
+                          (
+                            select a from t2
+                            union
+                            select a+2 from x as r2 where a < 10
+                          )
+                          select a from x as s2
+                        )
+         ) as r
+  from t3
+)
+select * from cte;
+
+
+# x as r1 is bound to table x, x as s1 is bound to the first CTE x
+# x as r2 is bound to the first CTE x, x as s2 is bound to the second CTE x
+# before fix of this bug: crash in With_element::get_name()
+
+with
+cte as
+(
+  select (
+           with
+           x as
+           (
+             select a from x as r1
+           )
+           select * from x as s1
+           where s1.a < 5 and
+                 s1.a in (
+                          with
+                          x as
+                          (
+                            select a from t2
+                            union
+                            select a+2 from x as r2 where a < 10
+                          )
+                          select a from x as s2
+                        )
+         ) as r
+  from t3
+)
+select * from cte;
+
+
+drop table t1,t2,t3,x;
+
+--echo #
 --echo # End of 10.3 tests
 --echo #