revision-id: 0f9542ee33c6f0582d60b2244db8733281ecbb46 (mariadb-10.2.31-273-g0f9542ee33c) parent(s): b68f1d847f1fc00eed795e20162effc8fbc4119b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-06-13 16:45:55 +0300 message: 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. --- sql/sql_prepare.cc | 2 ++ sql/sql_select.cc | 1 + tests/mysql_client_test.c | 52 +++++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 55 insertions(+) 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 } };