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, 7 insertions, 1 deletions

diff --git a/sql/sql_cte.h b/sql/sql_cte.h
index 6351b65a07c..58f371d936b 100644
--- a/sql/sql_cte.h
+++ b/sql/sql_cte.h
@@ -74,6 +74,11 @@ private:
     It used to build clones of the specification if they are needed.
   */
   LEX_STRING unparsed_spec;
+  /* Offset of the specification in the input string */
+  uint unparsed_spec_offset;
+
+  /* True if the with element is used a prepared statement */
+  bool stmt_prepare_mode;
 
   /* Return the map where 1 is set only in the position for this element */
   table_map get_elem_map() { return (table_map) 1 << number; }
@@ -174,7 +179,8 @@ public:
 
   TABLE_LIST *find_first_sq_rec_ref_in_select(st_select_lex *sel);
 
-  bool set_unparsed_spec(THD *thd, char *spec_start, char *spec_end);
+  bool set_unparsed_spec(THD *thd, char *spec_start, char *spec_end,
+                         uint spec_offset);
 
   st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table);