MDEV-22779: Crash: Prepared Statement with a '?' parameter inside a re-used CTE

When a prepared statement parameter '?' is used in a CTE that is used
multiple times, the following happens:
- The CTE definition is re-parsed multiple times.
- There are multiple Item_param objects referring to the same "?" in
the original query.
- Prepared_statement::param has a pointer to the first of them, the
  others are "clones".
- When prepared statement parameter gets the value, it should be passed
  over to clones with param->sync_clones() call.

This call is made in insert_params(), etc. It was not made in
insert_params_with_log().

This would cause Item_param to not have any value which would confuse
the query optimizer.

Added the missing call.

3 files changed, 55 insertions, 0 deletions

diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 6ac85f1331e..79d18fcb799 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -903,6 +903,8 @@ static bool insert_params_with_log(Prepared_statement *stmt, uchar *null_array,
 
     if (param->convert_str_value(thd))
       DBUG_RETURN(1);                           /* out of memory */
+
+    param->sync_clones();
   }
   if (acc.finalize())
     DBUG_RETURN(1);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b558445540d..4cca2d67eb8 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5997,6 +5997,7 @@ static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array)
       uint n_tables= my_count_bits(map);
       if (n_tables == 1)			// Only one table
       {
+        DBUG_ASSERT(!(map & PSEUDO_TABLE_BITS)); // Must be a real table
         Table_map_iterator it(map);
         int tablenr= it.next_bit();
         DBUG_ASSERT(tablenr != Table_map_iterator::BITMAP_END);
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index c544e20f2fe..33655b80662 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -19843,6 +19843,57 @@ static void test_bulk_replace()
 }
 #endif
 
+
+static void test_ps_params_in_ctes()
+{
+  int rc;
+  const char *query;
+  MYSQL_BIND ps_params[1];
+  int int_data[1];
+  MYSQL_STMT *stmt;
+
+  rc= mysql_query(mysql, "create table t1(a int, b int, key(a))");
+  myquery(rc);
+
+  rc= mysql_query(mysql, "insert into t1 (a) values "
+                         "(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)");
+  myquery(rc);
+
+  query=
+    "explain "
+    "with T as "
+    "( "
+    "  select * from t1 where t1.a=? limit 2 "
+    ") "
+    "select * from T as TA, T as TB;";
+
+  stmt= mysql_stmt_init(mysql);
+  check_stmt(stmt);
+
+  rc= mysql_stmt_prepare(stmt, query, (uint) strlen(query));
+  check_execute(stmt, rc);
+
+  int_data[0]=2;
+
+  ps_params[0].buffer_type= MYSQL_TYPE_LONG;
+  ps_params[0].buffer= (char *) &int_data[0];
+  ps_params[0].length= 0;
+  ps_params[0].is_null= 0;
+
+  rc= mysql_stmt_bind_param(stmt, ps_params);
+  check_execute(stmt, rc);
+
+  rc= mysql_stmt_execute(stmt);
+  check_execute(stmt, rc);
+
+  rc= mysql_stmt_store_result(stmt);
+  check_execute(stmt, rc);
+
+  rc= mysql_query(mysql, "drop table t1");
+  myquery(rc);
+}
+
+
 static struct my_tests_st my_tests[]= {
   { "disable_query_logs", disable_query_logs },
   { "test_view_sp_list_fields", test_view_sp_list_fields },
@@ -20127,6 +20178,7 @@ static struct my_tests_st my_tests[]= {
   { "test_bulk_delete", test_bulk_delete },
   { "test_bulk_replace", test_bulk_replace },
 #endif
+  { "test_ps_params_in_ctes", test_ps_params_in_ctes },
   { 0, 0 }
 };