MDEV-23406 Signal 8 in maria_create after recursive cte query

This bug could cause a crash when executing queries that used mutually
recursive CTEs with system variable big_tables set to 1. It happened due
to several bugs in the code that handled recursive table references
referred mutually recursive CTEs. For each recursive table reference a
temporary table is created that contains all rows generated for the
corresponding recursive CTE table on the previous step of recursion.
This temporary table should be created in the same way as the temporary
table created for a regular materialized derived table using the
method select_union::create_result_table(). In this case when the
temporary table is created it uses the select_union::TMP_TABLE_PARAM
structure as the parameter for the table construction. However the
code created the temporary table using just the function create_tmp_table()
and passed pointers to certain fields of the TMP_TABLE_PARAM structure
used for accumulation of rows of the recursive CTE table as parameters
for update. This was a mistake because now different temporary tables
cannot share some TMP_TABLE_PARAM fields in a general case. Besides,
depending on how mutually recursive CTE tables were defined and which
of them were referred in the executed query the select_union object
allocated for a recursive table reference could be allocated again after
the the temporary table had been created. In this case the TMP_TABLE_PARAM
object associated with the temporary table created for the recursive
table reference contained unassigned fields needed for execution when
Aria engine is employed as the engine for temporary tables.
This patch ensures that
- select_union object is created only once for any recursive table
  reference
- any temporary table created for recursive CTEs uses its own
  TMP_TABLE_PARAM structure
The patch also fixes a problem caused by incomplete cleanup of join tables
associated with recursive table references.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

7 files changed, 370 insertions, 39 deletions

diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 5090a0ffef9..b6b4ed7fb37 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -4235,5 +4235,224 @@ drop database db1;
 create database test;
 use test;
 #
+# MDEV-23406: query with mutually recursive CTEs when big_tables=1
+#
+set @save_big_tables=@@big_tables;
+set big_tables=1;
+create table  folks(id int, name char(32), dob date, father int, mother int);
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(20, 'Dad', '1970-02-02', 10, 9),
+(30, 'Mom', '1975-03-03', 8, 7),
+(10, 'Grandpa Bill', '1940-04-05', null, null),
+(9, 'Grandma Ann', '1941-10-15', null, null),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
+(6, 'Grandgrandma Martha', '1923-05-17', null, null),
+(67, 'Cousin Eddie', '1992-02-28', 25, 27),
+(27, 'Auntie Melinda', '1971-03-29', null, null);
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+explain with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	1728	
+4	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	1728	
+5	RECURSIVE UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	1728	
+NULL	UNION RESULT	<union4,5>	ALL	NULL	NULL	NULL	NULL	NULL	
+3	DERIVED	v	ALL	NULL	NULL	NULL	NULL	12	Using where
+3	DERIVED	h	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
+3	DERIVED	w	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (incremental, BNL join)
+2	RECURSIVE UNION	<derived4>	ALL	NULL	NULL	NULL	NULL	2	
+2	RECURSIVE UNION	h	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
+2	RECURSIVE UNION	w	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (incremental, BNL join)
+NULL	UNION RESULT	<union3,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+prepare stmt from "with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples";
+execute stmt;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+execute stmt;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+deallocate prepare stmt;
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select *
+from folks
+where name = 'Me'
+  union all
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union all
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+explain with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select *
+from folks
+where name = 'Me'
+  union all
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union all
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+3	DERIVED	folks	ALL	NULL	NULL	NULL	NULL	12	Using where
+4	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+5	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+NULL	UNION RESULT	<union3,4,5>	ALL	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	h	ALL	NULL	NULL	NULL	NULL	12	
+2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
+2	DERIVED	w	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (incremental, BNL join)
+prepare stmt from "with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w,  coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select *
+from folks
+where name = 'Me'
+  union all
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union all
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples";
+execute stmt;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+execute stmt;
+h_name	h_dob	w_name	w_dob
+Dad	1970-02-02	Mom	1975-03-03
+Grandpa Bill	1940-04-05	Grandma Ann	1941-10-15
+Grandpa Ben	1940-10-21	Grandma Sally	1943-08-23
+deallocate prepare stmt;
+drop table folks;
+set big_tables=@save_big_tables;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index f902ac25253..849e76b0436 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -2726,5 +2726,99 @@ create database test;
 use test;
 
 --echo #
+--echo # MDEV-23406: query with mutually recursive CTEs when big_tables=1
+--echo #
+
+set @save_big_tables=@@big_tables;
+set big_tables=1;
+
+create table  folks(id int, name char(32), dob date, father int, mother int);
+
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(20, 'Dad', '1970-02-02', 10, 9),
+(30, 'Mom', '1975-03-03', 8, 7),
+(10, 'Grandpa Bill', '1940-04-05', null, null),
+(9, 'Grandma Ann', '1941-10-15', null, null),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
+(6, 'Grandgrandma Martha', '1923-05-17', null, null),
+(67, 'Cousin Eddie', '1992-02-28', 25, 27),
+(27, 'Auntie Melinda', '1971-03-29', null, null);
+
+let q=
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+                 w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+  select h.*, w.*
+    from folks h, folks w,  coupled_ancestors a
+      where a.father = h.id AND a.mother = w.id
+  union
+  select h.*, w.*
+    from folks v, folks h, folks w
+      where v.name = 'Me' and
+            (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select h_id, h_name, h_dob, h_father, h_mother
+    from ancestor_couples
+  union
+  select w_id, w_name, w_dob, w_father, w_mother
+    from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+  from ancestor_couples;
+
+eval $q;
+eval explain $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q=
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+                 w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+  select h.*, w.*
+    from folks h, folks w,  coupled_ancestors a
+      where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+  select *
+    from folks
+      where name = 'Me'
+  union all
+  select h_id, h_name, h_dob, h_father, h_mother
+    from ancestor_couples
+  union all
+  select w_id, w_name, w_dob, w_father, w_mother
+    from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+  from ancestor_couples;
+
+eval $q;
+eval explain $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop table folks;
+
+set big_tables=@save_big_tables;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 35068245830..b68e3553a2d 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -5152,10 +5152,15 @@ class select_union_recursive :public select_union
  public:
   /* The temporary table with the new records generated by one iterative step */
   TABLE *incr_table;
+  /* The TMP_TABLE_PARAM structure used to create incr_table */
+  TMP_TABLE_PARAM incr_table_param;
   /* One of tables from the list rec_tables (determined dynamically) */
   TABLE *first_rec_table_to_update;
-  /* The temporary tables used for recursive table references */
-  List<TABLE> rec_tables;
+  /*
+    The list of all recursive table references to the CTE for whose
+    specification this select_union_recursive was created
+ */
+  List<TABLE_LIST> rec_table_refs;
   /*
     The count of how many times cleanup() was called with cleaned==false
     for the unit specifying the recursive CTE for which this object was created
@@ -5165,7 +5170,8 @@ class select_union_recursive :public select_union
 
   select_union_recursive(THD *thd_arg):
     select_union(thd_arg),
-    incr_table(0), first_rec_table_to_update(0), cleanup_count(0) {};
+    incr_table(0), first_rec_table_to_update(0), cleanup_count(0)
+  { incr_table_param.init(); };
 
   int send_data(List<Item> &items);
   bool create_result_table(THD *thd, List<Item> *column_types,
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index a8bccf0c1dd..dd764dad7cf 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1429,10 +1429,11 @@ void With_element::print(String *str, enum_query_type query_type)
 
 bool With_element::instantiate_tmp_tables()
 {
-  List_iterator_fast<TABLE> li(rec_result->rec_tables);
-  TABLE *rec_table;
-  while ((rec_table= li++))
+  List_iterator_fast<TABLE_LIST> li(rec_result->rec_table_refs);
+  TABLE_LIST *rec_tbl;
+  while ((rec_tbl= li++))
   {
+    TABLE *rec_table= rec_tbl->table;
     if (!rec_table->is_created() &&
         instantiate_tmp_table(rec_table,
                               rec_table->s->key_info,
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 5379dd45bfb..33f323b86a0 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -677,7 +677,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
       if (derived->is_with_table_recursive_reference())
       {
         /* Here 'derived" is a secondary recursive table reference */
-        unit->with_element->rec_result->rec_tables.push_back(derived->table);
+         unit->with_element->rec_result->rec_table_refs.push_back(derived);
       }
     }
     DBUG_ASSERT(derived->table || res);
@@ -733,7 +733,9 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
 
   derived->fill_me= FALSE;
 
-  if (!(derived->derived_result= new (thd->mem_root) select_union(thd)))
+  if ((!derived->is_with_table_recursive_reference() ||
+       !derived->derived_result) &&
+      !(derived->derived_result= new (thd->mem_root) select_union(thd)))
     DBUG_RETURN(TRUE); // out of memory
 
   lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
@@ -752,7 +754,8 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
     Depending on the result field translation will or will not
     be created.
   */
-  if (derived->init_derived(thd, FALSE))
+  if (!derived->is_with_table_recursive_reference() &&
+      derived->init_derived(thd, FALSE))
     goto exit;
 
   /*
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8b4401b1009..d5e5a79eba2 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -11929,10 +11929,6 @@ void JOIN_TAB::cleanup()
 {
   DBUG_ENTER("JOIN_TAB::cleanup");
   
-  if (tab_list && tab_list->is_with_table_recursive_reference() &&
-    tab_list->with->is_cleaned())
-  DBUG_VOID_RETURN;
-
   DBUG_PRINT("enter", ("tab: %p  table %s.%s",
                        this,
                        (table ? table->s->db.str : "?"),
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 7716f792fdc..7baedfb259c 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -211,7 +211,10 @@ select_union_recursive::create_result_table(THD *thd_arg,
                                         create_table, keep_row_order))
     return true;
   
-  if (! (incr_table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
+  incr_table_param.init();
+  incr_table_param.field_count= column_types->elements;
+  incr_table_param.bit_fields_as_long= bit_fields_as_long;
+  if (! (incr_table= create_tmp_table(thd_arg, &incr_table_param, *column_types,
                                       (ORDER*) 0, false, 1,
                                       options, HA_POS_ERROR, "",
                                       true, keep_row_order)))
@@ -221,20 +224,6 @@ select_union_recursive::create_result_table(THD *thd_arg,
   for (uint i=0; i < table->s->fields; i++)
     incr_table->field[i]->flags &= ~(PART_KEY_FLAG | PART_INDIRECT_KEY_FLAG);
 
-  TABLE *rec_table= 0;
-  if (! (rec_table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
-                                     (ORDER*) 0, false, 1,
-                                     options, HA_POS_ERROR, alias,
-                                     true, keep_row_order)))
-    return true;
-
-  rec_table->keys_in_use_for_query.clear_all();
-  for (uint i=0; i < table->s->fields; i++)
-    rec_table->field[i]->flags &= ~(PART_KEY_FLAG | PART_INDIRECT_KEY_FLAG);
-
-  if (rec_tables.push_back(rec_table))
-    return true;
-
   return false;
 }
 
@@ -272,23 +261,25 @@ void select_union_recursive::cleanup()
     free_tmp_table(thd, incr_table);
   }
 
-  List_iterator<TABLE> it(rec_tables);
-  TABLE *tab;
-  while ((tab= it++))
+  List_iterator<TABLE_LIST> it(rec_table_refs);
+  TABLE_LIST *tbl;
+  while ((tbl= it++))
   {
+    TABLE *tab= tbl->table;
     if (tab->is_created())
     {
       tab->file->extra(HA_EXTRA_RESET_STATE);
       tab->file->ha_delete_all_rows();
     }
-    /* 
+    /*
       The table will be closed later in close_thread_tables(),
       because it might be used in the statements like
       ANALYZE WITH r AS (...) SELECT * from r
-      where r is defined through recursion. 
+      where r is defined through recursion.
     */
     tab->next= thd->rec_tables;
     thd->rec_tables= tab;
+    tbl->derived_result= 0;
   }
 }
 
@@ -715,9 +706,29 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
           goto err;
         if (!derived->table)
         {
-          derived->table= with_element->rec_result->rec_tables.head();
-          if (derived->derived_result)
-            derived->derived_result->table= derived->table;
+          bool res= false;
+
+          if ((!derived->is_with_table_recursive_reference() ||
+               !derived->derived_result) &&
+              !(derived->derived_result= new (thd->mem_root) select_union(thd)))
+            goto err; // out of memory
+          thd->create_tmp_table_for_derived= TRUE;
+          res= derived->derived_result->create_result_table(thd,
+                                                            &types,
+                                                            FALSE,
+                                                            create_options,
+                                                            derived->alias,
+                                                            FALSE, FALSE);
+          thd->create_tmp_table_for_derived= FALSE;
+          if (res)
+            goto err;
+          derived->derived_result->set_unit(this);
+          derived->table= derived->derived_result->table;
+          if (derived->is_with_table_recursive_reference())
+          {
+            /* Here 'derived" is the primary recursive table reference */
+            derived->with->rec_result->rec_table_refs.push_back(derived);
+          }
         }
         with_element->mark_as_with_prepared_anchor();
         is_rec_result_table_created= true;
@@ -1272,11 +1283,11 @@ bool st_select_lex_unit::exec_recursive()
   TABLE *incr_table= with_element->rec_result->incr_table;
   st_select_lex *end= NULL;
   bool is_unrestricted= with_element->is_unrestricted();
-  List_iterator_fast<TABLE> li(with_element->rec_result->rec_tables);
+  List_iterator_fast<TABLE_LIST> li(with_element->rec_result->rec_table_refs);
   TMP_TABLE_PARAM *tmp_table_param= &with_element->rec_result->tmp_table_param;
   ha_rows examined_rows= 0;
   bool was_executed= executed;
-  TABLE *rec_table;
+  TABLE_LIST *rec_tbl;
 
   DBUG_ENTER("st_select_lex_unit::exec_recursive");
 
@@ -1335,8 +1346,9 @@ bool st_select_lex_unit::exec_recursive()
   else
     with_element->level++;
 
-  while ((rec_table= li++))
+  while ((rec_tbl= li++))
   {
+    TABLE *rec_table= rec_tbl->table;
     saved_error=
       incr_table->insert_all_rows_into_tmp_table(thd, rec_table,
                                                  tmp_table_param,