revision-id: 4e2c026a4cbde7ea6b2e3e1be87333c48a719c08 (mariadb-10.4.11-27-g4e2c026a4cb) parent(s): 983163209d026bfd979b4298053fcbdb373efa9d author: Varun Gupta committer: Varun Gupta timestamp: 2020-01-10 17:27:28 +0530 message: MDEV-21377: Eq_ref access not picked by query with optimizer_use_condition_selectivity > 1 The issue here is the estimate of the number of distinct ref access made by the optimizer are greater than the records in the join prefix. This is incorrect, we need to make sure that the number of distinct ref accesses are less than the records in the join prefix --- mysql-test/main/selectivity.result | 56 +++++++++++++++++++++++++++++++ mysql-test/main/selectivity.test | 38 +++++++++++++++++++++ mysql-test/main/selectivity_innodb.result | 56 +++++++++++++++++++++++++++++++ sql/sql_select.cc | 3 ++ 4 files changed, 153 insertions(+) diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 4366ef6a564..95069c74391 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1867,4 +1867,60 @@ set optimizer_switch= @save_optimizer_switch; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1,t2; # End of 10.1 tests +# +# MDEV-21377: Eq_ref access not picked by query with +# optimizer_use_condition_selectivity > 1 +# +create table t1(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t1 select seq, seq, seq from seq_1_to_100; +create table t2(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t2 select seq, seq, seq from seq_1_to_1000; +create table t3(a int, b int, primary key(a)); +insert into t3 select seq, seq from seq_1_to_100; +analyze table t1,t2,t3; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='rowid_filter=off'; +set optimizer_use_condition_selectivity=1; +explain SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b,c b 5 NULL 5 Using index condition; Using where +1 SIMPLE t2 ref b,c c 5 test.t1.c 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +a b c a b c a b +1 1 1 1 1 1 1 1 +2 2 2 2 2 2 2 2 +3 3 3 3 3 3 3 3 +4 4 4 4 4 4 4 4 +5 5 5 5 5 5 5 5 +6 6 6 6 6 6 6 6 +7 7 7 7 7 7 7 7 +8 8 8 8 8 8 8 8 +9 9 9 9 9 9 9 9 +set optimizer_use_condition_selectivity=4; +explain SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b,c b 5 NULL 5 Using index condition; Using where +1 SIMPLE t2 ref b,c c 5 test.t1.c 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +a b c a b c a b +1 1 1 1 1 1 1 1 +2 2 2 2 2 2 2 2 +3 3 3 3 3 3 3 3 +4 4 4 4 4 4 4 4 +5 5 5 5 5 5 5 5 +6 6 6 6 6 6 6 6 +7 7 7 7 7 7 7 7 +8 8 8 8 8 8 8 8 +9 9 9 9 9 9 9 9 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set optimizer_switch=@save_optimizer_switch; +drop table t1,t2,t3; +# End of 10.4 tests set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index d0158fb717e..6ed3ae67d83 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1269,6 +1269,44 @@ drop table t1,t2; --echo # End of 10.1 tests +--echo # +--echo # MDEV-21377: Eq_ref access not picked by query with +--echo # optimizer_use_condition_selectivity > 1 +--echo # + +create table t1(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t1 select seq, seq, seq from seq_1_to_100; + +create table t2(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t2 select seq, seq, seq from seq_1_to_1000; + +create table t3(a int, b int, primary key(a)); +insert into t3 select seq, seq from seq_1_to_100; +--disable_result_log +analyze table t1,t2,t3; +--enable_result_log + +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='rowid_filter=off'; +let $q= SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and + t2.b < 10 AND t1.b < 10; + +set optimizer_use_condition_selectivity=1; +eval explain $q; +eval $q; + +set optimizer_use_condition_selectivity=4; +eval explain $q; +eval $q; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set optimizer_switch=@save_optimizer_switch; + +drop table t1,t2,t3; + +--echo # End of 10.4 tests + # # Clean up # diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 062b2da7b4f..d48848a92bf 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1877,6 +1877,62 @@ set optimizer_switch= @save_optimizer_switch; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1,t2; # End of 10.1 tests +# +# MDEV-21377: Eq_ref access not picked by query with +# optimizer_use_condition_selectivity > 1 +# +create table t1(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t1 select seq, seq, seq from seq_1_to_100; +create table t2(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t2 select seq, seq, seq from seq_1_to_1000; +create table t3(a int, b int, primary key(a)); +insert into t3 select seq, seq from seq_1_to_100; +analyze table t1,t2,t3; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='rowid_filter=off'; +set optimizer_use_condition_selectivity=1; +explain SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b,c b 5 NULL 9 Using index condition; Using where +1 SIMPLE t2 ref b,c c 5 test.t1.c 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +a b c a b c a b +1 1 1 1 1 1 1 1 +2 2 2 2 2 2 2 2 +3 3 3 3 3 3 3 3 +4 4 4 4 4 4 4 4 +5 5 5 5 5 5 5 5 +6 6 6 6 6 6 6 6 +7 7 7 7 7 7 7 7 +8 8 8 8 8 8 8 8 +9 9 9 9 9 9 9 9 +set optimizer_use_condition_selectivity=4; +explain SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b,c b 5 NULL 9 Using index condition; Using where +1 SIMPLE t2 ref b,c c 5 test.t1.c 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +a b c a b c a b +1 1 1 1 1 1 1 1 +2 2 2 2 2 2 2 2 +3 3 3 3 3 3 3 3 +4 4 4 4 4 4 4 4 +5 5 5 5 5 5 5 5 +6 6 6 6 6 6 6 6 +7 7 7 7 7 7 7 7 +8 8 8 8 8 8 8 8 +9 9 9 9 9 9 9 9 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set optimizer_switch=@save_optimizer_switch; +drop table t1,t2,t3; +# End of 10.4 tests set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d9d9c229c2f..f5b08dcdd79 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7370,6 +7370,7 @@ best_access_path(JOIN *join, double tmp2= prev_record_reads(join_positions, idx, (found_ref | keyuse->used_tables)); + tmp2= MY_MIN(tmp2, record_count); if (tmp2 < best_prev_record_reads) { best_part_found_ref= keyuse->used_tables & ~join->const_table_map; @@ -7411,6 +7412,7 @@ best_access_path(JOIN *join, but 1.0 would be probably safer */ tmp= prev_record_reads(join_positions, idx, found_ref); + MY_MIN(tmp, record_count); records= 1.0; type= JT_FT; trace_access_idx.add("access_type", join_type_str[type]) @@ -7440,6 +7442,7 @@ best_access_path(JOIN *join, trace_access_idx.add("access_type", join_type_str[type]) .add("index", keyinfo->name); tmp = prev_record_reads(join_positions, idx, found_ref); + tmp= MY_MIN(tmp, record_count); records=1.0; } else