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;
 }