MDEV-27937 Assertion failure when executing prepared statement with ? in IN list

This bug affected queries with IN predicates that contain parameter markers
in the value list. Such queries are executed via prepared statements.
The problem appeared only if the number of elements in the value list
was greater than the set value of the system variable
in_predicate_conversion_threshold.

The patch unconditionally prohibits conversion of an IN predicate to the
equivalent IN predicand if the value list of the IN predicate contains
parameters markers.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

5 files changed, 83 insertions, 6 deletions

diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result
index a68e70e8a25..02d9096ed09 100644
--- a/mysql-test/main/opt_tvc.result
+++ b/mysql-test/main/opt_tvc.result
@@ -732,3 +732,30 @@ a	b
 4	4
 drop table t1;
 SET @@in_predicate_conversion_threshold= default;
+#
+# MDEV-27937: Prepared statement with ? in the list if IN predicate
+#
+set in_predicate_conversion_threshold=2;
+create table t1 (id int, a int, b int);
+insert into t1 values (1,3,30), (2,7,70), (3,1,10);
+prepare stmt from "
+select * from t1 where a in (7, ?, 5, 1);
+";
+execute stmt using 3;
+id	a	b
+1	3	30
+2	7	70
+3	1	10
+deallocate prepare stmt;
+prepare stmt from "
+select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10));
+";
+execute stmt using 30;
+id	a	b
+1	3	30
+2	7	70
+3	1	10
+deallocate prepare stmt;
+drop table t1;
+set in_predicate_conversion_threshold=default;
+# End of 10.3 tests
diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test
index e4e8c6d7919..f8469f22aa1 100644
--- a/mysql-test/main/opt_tvc.test
+++ b/mysql-test/main/opt_tvc.test
@@ -428,3 +428,29 @@ eval $query;
 drop table t1;
 SET @@in_predicate_conversion_threshold= default;
 
+--echo #
+--echo # MDEV-27937: Prepared statement with ? in the list if IN predicate
+--echo #
+
+set in_predicate_conversion_threshold=2;
+
+create table t1 (id int, a int, b int);
+insert into t1 values (1,3,30), (2,7,70), (3,1,10);
+
+prepare stmt from "
+select * from t1 where a in (7, ?, 5, 1);
+";
+execute stmt using 3;
+deallocate prepare stmt;
+
+prepare stmt from "
+select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10));
+";
+execute stmt using 30;
+deallocate prepare stmt;
+
+drop table t1;
+
+set in_predicate_conversion_threshold=default;
+
+--echo # End of 10.3 tests
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 38f0a285e84..f41414f8ae9 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4472,10 +4472,11 @@ void Item_func_in::mark_as_condition_AND_part(TABLE_LIST *embedding)
   Query_arena *arena, backup;
   arena= thd->activate_stmt_arena_if_needed(&backup);
 
-  if (to_be_transformed_into_in_subq(thd))
+  if (!transform_into_subq_checked)
   {
-    transform_into_subq= true;
-    thd->lex->current_select->in_funcs.push_back(this, thd->mem_root);
+    if ((transform_into_subq= to_be_transformed_into_in_subq(thd)))
+      thd->lex->current_select->in_funcs.push_back(this, thd->mem_root);
+    transform_into_subq_checked= true;
   }
 
   if (arena)
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 4c88f5b274f..f3d3be44b62 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -2299,6 +2299,7 @@ protected:
   SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param,
                              Field *field, Item *value);
   bool transform_into_subq;
+  bool transform_into_subq_checked;
 public:
   /// An array of values, created when the bisection lookup method is used
   in_vector *array;
@@ -2321,6 +2322,7 @@ public:
     Item_func_opt_neg(thd, list),
     Predicant_to_list_comparator(thd, arg_count - 1),
     transform_into_subq(false),
+    transform_into_subq_checked(false),
     array(0), have_null(0),
     arg_types_compatible(FALSE), emb_on_expr_nest(0)
   { }
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 3866b7c9352..13efd973326 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -900,8 +900,6 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd,
   if (!transform_into_subq)
     return this;
   
-  transform_into_subq= false;
-
   List<List_item> values;
 
   LEX *lex= thd->lex;
@@ -1058,15 +1056,38 @@ uint32 Item_func_in::max_length_of_left_expr()
 
 bool Item_func_in::to_be_transformed_into_in_subq(THD *thd)
 {
+  bool is_row_list= args[1]->type() == Item::ROW_ITEM;
   uint values_count= arg_count-1;
 
-  if (args[1]->type() == Item::ROW_ITEM)
+  if (is_row_list)
     values_count*= ((Item_row *)(args[1]))->cols();
 
   if (thd->variables.in_subquery_conversion_threshold == 0 ||
       thd->variables.in_subquery_conversion_threshold > values_count)
     return false;
 
+  if (!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE))
+    return true;
+
+  /* Occurence of '?' in IN list is checked only for PREPARE <stmt> commands */
+  for (uint i=1; i < arg_count; i++)
+  {
+    if (!is_row_list)
+    {
+      if (args[i]->type() == Item::PARAM_ITEM)
+        return false;
+    }
+    else
+    {
+      Item_row *row_list= (Item_row *)(args[i]);
+      for (uint j=0; j < row_list->cols(); j++)
+      {
+        if (row_list->element_index(j)->type() == Item::PARAM_ITEM)
+          return false;
+      }
+    }
+  }
+
   return true;
 }