MDEV-20632: Recursive CTE cycle detection using CYCLE clause (nonstandard)

Added CYCLE ... RESTRICT (nonstandard) clause to recursive CTE.

1 files changed, 106 insertions, 30 deletions

diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index c7a0f9186e2..93344956468 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -21,6 +21,7 @@
 #include "sql_view.h"    // for make_valid_column_names
 #include "sql_parse.h"
 #include "sql_select.h"
+#include "sql_show.h"                // append_definer, append_identifier
 
 
 /**
@@ -944,9 +945,9 @@ err:
     false  otherwise
 */    
 
-bool 
-With_element::rename_columns_of_derived_unit(THD *thd, 
-                                             st_select_lex_unit *unit)
+bool
+With_element::process_columns_of_derived_unit(THD *thd,
+                                              st_select_lex_unit *unit)
 {
   if (unit->columns_are_renamed)
     return false;
@@ -973,7 +974,7 @@ With_element::rename_columns_of_derived_unit(THD *thd,
     while ((item= it++, name= nm++))
     {
       item->set_name(thd, *name);
-      item->is_autogenerated_name= false;
+      item->common_flags&= ~IS_AUTO_GENERATED_NAME;
     }
 
     if (arena)
@@ -982,6 +983,43 @@ With_element::rename_columns_of_derived_unit(THD *thd,
   else
     make_valid_column_names(thd, select->item_list);
 
+  if (cycle_list)
+  {
+    List_iterator_fast<Item> it(select->item_list);
+    List_iterator_fast<Lex_ident_sys> nm(*cycle_list);
+    List_iterator_fast<Lex_ident_sys> nm_check(*cycle_list);
+    DBUG_ASSERT(cycle_list->elements != 0);
+    while (LEX_CSTRING *name= nm++)
+    {
+      Item *item;
+      /*
+        Check for uniqueness of each element in the cycle list:
+        It's sufficient to check that there is no duplicate of 'name'
+        among the elements that precede it.
+      */
+      LEX_CSTRING *check;
+      nm_check.rewind();
+      while ((check= nm_check++) && check != name)
+      {
+        if (check->length == name->length &&
+            strncmp(check->str, name->str, name->length) == 0)
+        {
+          my_error(ER_DUP_FIELDNAME, MYF(0), check->str);
+          return true;
+        }
+      }
+      /* Check that 'name' is the name of a column of the processed CTE */
+      while ((item= it++) &&
+             (item->name.length != name->length ||
+              strncmp(item->name.str, name->str, name->length) != 0));
+      if (item == NULL)
+      {
+        my_error(ER_BAD_FIELD_ERROR, MYF(0), name->str, "CYCLE clause");
+        return true;
+      }
+      item->common_flags|= IS_IN_WITH_CYCLE;
+    }
+  }
   unit->columns_are_renamed= true;
 
   return false;
@@ -1018,7 +1056,7 @@ bool With_element::prepare_unreferenced(THD *thd)
   thd->lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
   if (!spec->prepared &&
       (spec->prepare(spec->derived, 0, 0) ||
-       rename_columns_of_derived_unit(thd, spec) ||
+       process_columns_of_derived_unit(thd, spec) ||
        check_duplicate_names(thd, first_sl->item_list, 1)))
     rc= true;
 
@@ -1394,16 +1432,17 @@ bool st_select_lex::check_subqueries_with_recursive_references()
 /**
   @brief
     Print this with clause
-  
+
+  @param thd         Thread handle
   @param str         Where to print to
-  @param query_type  The mode of printing 
-     
+  @param query_type  The mode of printing
+
   @details
-    The method prints a string representation of this clause in the 
+    The method prints a string representation of this clause in the
     string str. The parameter query_type specifies the mode of printing.
-*/    
+*/
 
-void With_clause::print(String *str, enum_query_type query_type)
+void With_clause::print(THD *thd, String *str, enum_query_type query_type)
 {
   /*
     Any with clause contains just definitions of CTE tables.
@@ -1420,7 +1459,22 @@ void With_clause::print(String *str, enum_query_type query_type)
   {
     if (with_elem != with_list.first)
       str->append(", ");
-    with_elem->print(str, query_type);
+    with_elem->print(thd, str, query_type);
+  }
+}
+
+
+static void list_strlex_print(THD *thd, String *str, List<Lex_ident_sys> *list)
+{
+  List_iterator_fast<Lex_ident_sys> li(*list);
+  bool first= TRUE;
+  while(Lex_ident_sys *col_name= li++)
+  {
+    if (first)
+      first= FALSE;
+    else
+      str->append(',');
+    append_identifier(thd, str, col_name);
   }
 }
 
@@ -1428,38 +1482,37 @@ void With_clause::print(String *str, enum_query_type query_type)
 /**
    @brief
      Print this with element
-  
+
+  @param thd         Thread handle
   @param str         Where to print to
-  @param query_type  The mode of printing 
-     
+  @param query_type  The mode of printing
+
   @details
-    The method prints a string representation of this with element in the 
+    The method prints a string representation of this with element in the
     string str. The parameter query_type specifies the mode of printing.
 */
 
-void With_element::print(String *str, enum_query_type query_type)
+void With_element::print(THD *thd, String *str, enum_query_type query_type)
 {
   str->append(query_name);
   if (column_list.elements)
   {
     List_iterator_fast<Lex_ident_sys> li(column_list);
     str->append('(');
-    for (LEX_CSTRING *col_name= li++; ; )
-    {
-      str->append(col_name);
-      col_name= li++;
-      if (!col_name)
-      {
-        str->append(')');
-        break;
-      }
-      str->append(',');
-    }
+    list_strlex_print(thd, str, &column_list);
+    str->append(')');
   }
-  str->append(STRING_WITH_LEN(" as "));
-  str->append('(');
+  str->append(STRING_WITH_LEN(" as ("));
   spec->print(str, query_type);
   str->append(')');
+
+  if (cycle_list)
+  {
+    DBUG_ASSERT(cycle_list->elements != 0);
+    str->append(STRING_WITH_LEN(" CYCLE "));
+    list_strlex_print(thd, str, cycle_list);
+    str->append(STRING_WITH_LEN(" RESTRICT "));
+  }
 }
 
 
@@ -1483,3 +1536,26 @@ bool With_element::instantiate_tmp_tables()
   return false;
 }
 
+void With_element::set_cycle_list(List<Lex_ident_sys> *cycle_list_arg)
+{
+  cycle_list= cycle_list_arg;
+
+  /*
+    If a CTE table with columns c1,...,cn is defined with a cycle
+    clause CYCLE(ci1,...,cik) then no two rows r1 and r2 from the
+    table shall have r1.ci1=r2.ci1 && ... && r1.cik=r2.cik.
+
+    If a cycle clause is used in the specification of a CTE then
+    each UNION ALL at the top level of the specification is interpreted
+    as a UNION DISTINCT over the cycle columns.
+  */
+  for (st_select_lex *sl=  spec->first_select(); sl; sl= sl->next_select())
+  {
+    spec->union_distinct= sl;
+    if (sl != spec->first_select())
+    {
+      sl->distinct= TRUE;
+      sl->with_all_modifier= FALSE;
+    }
+  }
+}