diff options
| author | Varun Gupta <varun.gupta@mariadb.com> | 2019-12-04 20:04:45 +0530 |
|---|---|---|
| committer | Varun Gupta <varun.gupta@mariadb.com> | 2019-12-10 11:28:04 +0530 |
| commit | 246e2ae12b514ed3010ffcf6473abbfd9f648340 (patch) | |
| tree | 157e8281a3278bf6912cf22a80930cfc2b61bbdc /mysql-test/main/opt_tvc.result | |
| parent | e5e5877740f248de848219ee3a1d2881cd5c5b82 (diff) | |
| download | mariadb-git-246e2ae12b514ed3010ffcf6473abbfd9f648340.tar.gz | |
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.
Diffstat (limited to 'mysql-test/main/opt_tvc.result')
| -rw-r--r-- | mysql-test/main/opt_tvc.result | 53 |
1 files changed, 49 insertions, 4 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; |