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, 5 insertions, 0 deletions

diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index a1f6b202ae6..939b48ba825 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2552,6 +2552,11 @@ struct LEX: public Query_tables_list
      with clause in the current statement
   */
   With_clause **with_clauses_list_last_next;
+  /*
+    When a copy of a with element is parsed this is set to the offset of
+    the with element in the input string, otherwise it's set to 0
+  */
+  uint clone_spec_offset;
 
   /* Query Plan Footprint of a currently running select  */
   Explain_query *explain;