revision-id: 4b1ab57f1c5ec26503f2c7ebeaea8343fb424a23 (mariadb-10.1.41-34-g4b1ab57f1c5) parent(s): 0e38cd37c7be46ac2b57344476d68a6dc59bc3a4 author: Varun Gupta committer: Varun Gupta timestamp: 2019-09-07 14:41:46 +0530 message: MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 The issue here was that inside the function table_cond_selectivity we were trying to use the ranges estimate for a key for which no sargable condition was present. The fix for this issue is to make sure to first check if range access is possible on the key, if yes only the use its estimate. --- mysql-test/r/stat_tables.result | 45 ++++++++++++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 45 ++++++++++++++++++++++++++++++++++ mysql-test/t/stat_tables.test | 26 ++++++++++++++++++++ sql/sql_select.cc | 2 +- 4 files changed, 117 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index d26221b5f8d..81c93203b4a 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -739,6 +739,51 @@ db_name table_name index_name prefix_arity avg_frequency a b test t1 k1 1 1.0000 2 2 test t1 k1 1 1.0000 3 3 drop table t1; +# +# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +# +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=4; +select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +19 19 +explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1 +1 SIMPLE B ref a a 5 const 1 +explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where +drop table t1,t2; set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set @save_optimizer_switch=@@optimizer_switch; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index b8bed681465..227aada41fd 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -766,6 +766,51 @@ db_name table_name index_name prefix_arity avg_frequency a b test t1 k1 1 1.0000 2 2 test t1 k1 1 1.0000 3 3 drop table t1; +# +# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +# +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=4; +select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +19 19 +explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index +2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1 +1 SIMPLE B ref a a 5 const 1 Using index +explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index +2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where +drop table t1,t2; set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set @save_optimizer_switch=@@optimizer_switch; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index e9f37698a73..2c6ea556e28 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -498,6 +498,32 @@ analyze table t1; select * from mysql.index_stats, t1 where index_name='k1' and t1.a > 1 and t1.b > 1; drop table t1; +--echo # +--echo # MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +--echo # + +--source include/have_sequence.inc + +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; + +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; + +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=4; + +let $query= select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); + +eval $query; +eval explain $query; + +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; + +eval explain $query; + +drop table t1,t2; + set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set @save_optimizer_switch=@@optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5b96c15bff5..cc209c229a3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7623,7 +7623,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, /* Check if we have a prefix of key=const that matches a quick select. */ - if (!is_hash_join_key_no(key)) + if (!is_hash_join_key_no(key) && table->quick_keys.is_set(key)) { table_map quick_key_map= (table_map(1) << table->quick_key_parts[key]) - 1; if (table->quick_rows[key] &&