MDEV-14629: failing assertion when a user-defined variable is defined by the recursive CTEbb-10.2-mdev14629

During the user-defined variable defined by the recursive CTE handling procedure
check_dependencies_in_with_clauses that checks dependencies between the tables
that are defined in the CTE and find recursive definitions wasn't called.

1 files changed, 51 insertions, 0 deletions

diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 36d948251c3..928f8a4334b 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -1947,3 +1947,54 @@ cte2 as (
 )
 SELECT *
 FROM cte1;
+
+--echo #
+--echo # mdev-14629: a user-defined variable is defined by the recursive CTE
+--echo #
+
+set @var=
+(
+  with recursive cte_tab(a) as (
+    select 1
+    union
+    select a+1 from cte_tab
+    where a<3)
+  select count(*) from cte_tab
+);
+
+select @var;
+
+create table t1(a int, b int);
+insert into t1 values (3,8),(1,5),(5,7),(7,4),(4,3);
+
+set @var=
+(
+  with recursive summ(a,s) as (
+    select 1, 0 union
+    select t1.b, t1.b+summ.s from summ, t1
+    where summ.a=t1.a)
+  select s from summ
+    order by a desc
+    limit 1
+);
+
+select @var;
+
+--ERROR ER_UNACCEPTABLE_MUTUAL_RECURSION
+set @var=
+(
+  with recursive
+  cte_1 as (
+    select 1
+    union
+    select * from cte_2),
+  cte_2 as (
+    select * from cte_1
+    union
+    select a from t1, cte_2
+    where t1.a=cte_2.a)
+  select * from cte_2
+  limit 1
+);
+
+drop table t1;