[Commits] 3a11b49bb5b: MDEV-20900: IN predicate to IN subquery conversion causes performance regression

Varun varunraiko1803 at gmail.com
Mon Nov 4 13:08:02 EET 2019


revision-id: 3a11b49bb5b716538f98c6a212bbbfa6fc9b7a88 (mariadb-10.3.17-145-g3a11b49bb5b)
parent(s): 162f475c4be81dfbceed093ad03d114b4c69a3c0
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-11-04 16:28:25 +0530
message:

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.

---
 mysql-test/main/opt_tvc.result | 53 ++++++++++++++++++++++++++++++++++++++----
 mysql-test/main/opt_tvc.test   | 31 ++++++++++++++++++++++++
 sql/sql_tvc.cc                 | 27 ++++++++++++++++++++-
 3 files changed, 106 insertions(+), 5 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/sql_tvc.cc b/sql/sql_tvc.cc
index 816c6fe1089..78c7c34a81a 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -796,6 +796,31 @@ 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
+
+  @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++)
+  {
+    if (item1->element_index(i)->cmp_type() !=
+        item2->element_index(i)->cmp_type())
+      return true;
+  }
+  return false;
+}
+
+
 /**
   @brief
     Transform IN predicate into IN subquery
@@ -843,7 +868,7 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd,
   
   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;
   }
 


More information about the commits mailing list