MDEV-20900: IN predicate to IN subquery conversion causes performance regression

Disable the IN predicate to IN subquery conversion when the types on the left and
right hand side of the IN predicate are not of comparable type.

5 files changed, 142 insertions, 6 deletions

diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result
index 5329a9f64be..a68e70e8a25 100644
--- a/mysql-test/main/opt_tvc.result
+++ b/mysql-test/main/opt_tvc.result
@@ -629,11 +629,9 @@ SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
 i
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`_col_1`
+Note	1003	select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL)
 SET in_predicate_conversion_threshold= default;
 DROP TABLE t1;
 #
@@ -687,3 +685,50 @@ f1	f2
 1	1
 DROP TABLE t1,t2,t3;
 SET @@in_predicate_conversion_threshold= default;
+#
+# MDEV-20900: IN predicate to IN subquery conversion causes performance regression
+#
+create table t1(a int, b int);
+insert into t1 select seq-1, seq-1 from seq_1_to_10;
+set in_predicate_conversion_threshold=2;
+explain select * from t1 where t1.a IN ("1","2","3","4");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+select * from t1 where t1.a IN ("1","2","3","4");
+a	b
+1	1
+2	2
+3	3
+4	4
+set in_predicate_conversion_threshold=0;
+explain select * from t1 where t1.a IN ("1","2","3","4");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+select * from t1 where t1.a IN ("1","2","3","4");
+a	b
+1	1
+2	2
+3	3
+4	4
+set in_predicate_conversion_threshold=2;
+explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
+a	b
+1	1
+2	2
+3	3
+4	4
+set in_predicate_conversion_threshold=0;
+explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
+a	b
+1	1
+2	2
+3	3
+4	4
+drop table t1;
+SET @@in_predicate_conversion_threshold= default;
diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test
index 7319dbdc9e8..e4e8c6d7919 100644
--- a/mysql-test/main/opt_tvc.test
+++ b/mysql-test/main/opt_tvc.test
@@ -3,6 +3,7 @@
 #
 source include/have_debug.inc;
 source include/default_optimizer_switch.inc;
+source include/have_sequence.inc;
 
 create table t1 (a int, b int);
 
@@ -397,3 +398,33 @@ SELECT * FROM t3 WHERE (f1,f2) IN ((2, 2), (1, 2), (3, 5), (1, 1));
 DROP TABLE t1,t2,t3;
 
 SET @@in_predicate_conversion_threshold= default;
+
+--echo #
+--echo # MDEV-20900: IN predicate to IN subquery conversion causes performance regression
+--echo #
+
+create table t1(a int, b int);
+insert into t1 select seq-1, seq-1 from seq_1_to_10;
+
+set in_predicate_conversion_threshold=2;
+
+let $query= select * from t1 where t1.a IN ("1","2","3","4");
+eval explain $query;
+eval $query;
+
+set in_predicate_conversion_threshold=0;
+eval explain $query;
+eval $query;
+
+set in_predicate_conversion_threshold=2;
+let $query= select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
+eval explain $query;
+eval $query;
+
+set in_predicate_conversion_threshold=0;
+eval explain $query;
+eval $query;
+
+drop table t1;
+SET @@in_predicate_conversion_threshold= default;
+
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 24a9991640a..29c32be1c80 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -2418,6 +2418,7 @@ public:
   bool to_be_transformed_into_in_subq(THD *thd);
   bool create_value_list_for_tvc(THD *thd, List< List<Item> > *values);
   Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg);
+  uint32 max_length_of_left_expr();
 };
 
 class cmp_item_row :public cmp_item
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index a4ee27950be..50f7efd6d65 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -829,7 +829,12 @@ bool subquery_types_allow_materialization(Item_in_subselect *in_subs)
 
   in_subs->types_allow_materialization= FALSE;  // Assign default values
   in_subs->sjm_scan_allowed= FALSE;
-  
+
+  /*
+    The checks here must be kept in sync with the one in
+    Item_func_in::in_predicate_to_in_subs_transformer().
+  */
+
   bool all_are_fields= TRUE;
   uint32 total_key_length = 0;
   for (uint i= 0; i < elements; i++)
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 816c6fe1089..b4538248e07 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -796,6 +796,38 @@ bool Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl)
 }
 
 
+/*
+  @brief
+   Check whether the items are of comparable type or not
+
+  @details
+    This check are done because materialization is not performed
+    if the left expr and right expr are of the same types.
+    @see subquery_types_allow_materialization()
+
+  @retval
+   0 comparable
+   1 not comparable
+*/
+
+static bool cmp_row_types(Item* item1, Item* item2)
+{
+  uint n= item1->cols();
+  if (item2->check_cols(n))
+    return true;
+
+  for (uint i=0; i < n; i++)
+  {
+    Item *inner= item1->element_index(i);
+    Item *outer= item2->element_index(i);
+    if (!inner->type_handler()->subquery_type_allows_materialization(inner,
+                                                                     outer))
+      return true;
+  }
+  return false;
+}
+
+
 /**
   @brief
     Transform IN predicate into IN subquery
@@ -840,10 +872,22 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd,
   /* SELECT_LEX object where the transformation is performed */
   SELECT_LEX *parent_select= lex->current_select;
   uint8 save_derived_tables= lex->derived_tables;
+
+  /*
+    Make sure that create_tmp_table will not fail due to too long keys.
+    Here the strategy would mainly use materialization, so we need to make
+    sure that the materialized table can be created.
+
+    The checks here are the same as in subquery_type_allows_materialization()
+  */
+  uint32 length= max_length_of_left_expr();
+  if (!length  || length > tmp_table_max_key_length() ||
+      args[0]->cols() > tmp_table_max_key_parts())
+    return this;
   
   for (uint i=1; i < arg_count; i++)
   {
-    if (!args[i]->const_item())
+    if (!args[i]->const_item() || cmp_row_types(args[0], args[i]))
       return this;
   }
 
@@ -948,6 +992,16 @@ err:
 }
 
 
+uint32 Item_func_in::max_length_of_left_expr()
+{
+  uint n= args[0]->cols();
+  uint32 length= 0;
+  for (uint i=0; i < n; i++)
+    length+= args[0]->element_index(i)->max_length;
+  return length;
+}
+
+
 /**
   @brief
     Check if this IN-predicate can be transformed in IN-subquery