MDEV-17154 Multiple selects from parametrized CTE fails with syntax error

This patch fills a serious flaw in the implementation of common table
expressions. Before this patch an attempt to prepare a statement from
a query with a parameter marker in a CTE that was used more than once
in the query ended up with a bogus error message. Similarly if a statement
in a stored procedure contained a CTE whose specification used a
local variables and this CTE was referred to more than once in the
statement then the server failed to execute the stored procedure returning
a bogus error message on a non-existing field.

The problems appeared due to incorrect handling of parameter markers /
local variables in CTEs that were referred more than once.

This patch fixes the problems by differentiating between the original
occurrences of a parameter marker / local variable used in the
specification of a CTE and the corresponding occurrences used
in copies of this specification. These copies are substituted
instead of non-first references to the CTE.

The idea of the fix and even some code were taken from the MySQL
implementation of the common table expressions.

1 files changed, 31 insertions, 6 deletions

diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index 45f24c3a248..5a590bfea93 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -726,9 +726,10 @@ bool With_clause::prepare_unreferenced_elements(THD *thd)
   @brief
     Save the specification of the given with table as a string
 
-  @param thd        The context of the statement containing this with element
-  @param spec_start The beginning of the specification in the input string
-  @param spec_end   The end of the specification in the input string
+  @param thd         The context of the statement containing this with element
+  @param spec_start  The beginning of the specification in the input string
+  @param spec_end    The end of the specification in the input string
+  @param spec_offset The offset of the specification in the input string
 
   @details
     The method creates for a string copy of the specification used in this
@@ -740,11 +741,19 @@ bool With_clause::prepare_unreferenced_elements(THD *thd)
     true    on failure
 */
   
-bool With_element::set_unparsed_spec(THD *thd, char *spec_start, char *spec_end)
+bool With_element::set_unparsed_spec(THD *thd, char *spec_start, char *spec_end,
+                                     uint spec_offset)
 {
+  stmt_prepare_mode= thd->m_parser_state->m_lip.stmt_prepare_mode;
   unparsed_spec.length= spec_end - spec_start;
-  unparsed_spec.str= (char*) thd->memdup(spec_start, unparsed_spec.length+1);
-  unparsed_spec.str[unparsed_spec.length]= '\0';
+  if (stmt_prepare_mode || !thd->lex->sphead)
+    unparsed_spec.str= spec_start;
+  else
+  {
+    unparsed_spec.str= (char*) thd->memdup(spec_start, unparsed_spec.length+1);
+    unparsed_spec.str[unparsed_spec.length]= '\0';
+  }
+  unparsed_spec_offset= spec_offset;
 
   if (!unparsed_spec.str)
   {
@@ -814,13 +823,28 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd,
   TABLE_LIST *spec_tables_tail;
   st_select_lex *with_select;
 
+  char save_end= unparsed_spec.str[unparsed_spec.length];
+  unparsed_spec.str[unparsed_spec.length]= '\0';
   if (parser_state.init(thd, unparsed_spec.str, unparsed_spec.length))
     goto err;
+  parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode;
+  parser_state.m_lip.multi_statements= false;
+  parser_state.m_lip.m_digest= NULL;
+
   lex_start(thd);
+  lex->clone_spec_offset= unparsed_spec_offset;
+  lex->param_list= old_lex->param_list;
+  lex->sphead= old_lex->sphead;
+  lex->spname= old_lex->spname;
+  lex->spcont= old_lex->spcont;
+  lex->sp_chistics= old_lex->sp_chistics;
+
   lex->stmt_lex= old_lex;
   with_select= &lex->select_lex;
   with_select->select_number= ++thd->lex->stmt_lex->current_select_number;
   parse_status= parse_sql(thd, &parser_state, 0);
+  unparsed_spec.str[unparsed_spec.length]= save_end;
+
   if (parse_status)
     goto err;
 
@@ -865,6 +889,7 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd,
                         with_select));
   if (check_dependencies_in_with_clauses(lex->with_clauses_list))
     res= NULL;
+  lex->sphead= NULL;    // in order not to delete lex->sphead
   lex_end(lex);
 err:
   if (arena)