revision-id: 54ca40934c8f352d4f308fe2fd8b32e68a3351d8 (mariadb-10.1.41-39-g54ca40934c8) parent(s): ae2b88ff3f94253921fed5c48422adeebe7e623d author: Varun Gupta committer: Varun Gupta timestamp: 2019-09-17 07:10:11 +0530 message: MDEV-20595: Assertion `0 < sel && sel <= 2.0' failed in table_cond_selectivity Selectivity is discounted twice for a field when the field has a condition like t1.col1=t2.col1 and t2.col1=const. So if there is ref access on table t1, it should be of type ref(const) [equality propagation is done already] But in the function table_cond_selectivity where we discount the selectivity for ref(const), we also try to discount the selectivity for the condition t1.col=t2.col1, which is incorrect. Just discount the selectivity for a field only ONCE. Fixed by using the tmp_set bitmap that would hold the indexes of fields for which selectivity was already discounted. --- mysql-test/r/selectivity.result | 25 +++++++++++++++++++++++++ mysql-test/r/selectivity_innodb.result | 25 +++++++++++++++++++++++++ mysql-test/t/selectivity.test | 23 +++++++++++++++++++++++ sql/sql_select.cc | 34 ++++++++++++++++++---------------- 4 files changed, 91 insertions(+), 16 deletions(-) diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index d0bbb46cb0a..d9747a309de 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1753,4 +1753,29 @@ a 1991 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1; +# +# MDEV-20595: Assertion `0 < sel && sel <= 2.0' failed in table_cond_selectivity +# +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_use_condition_selectivity=1; +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 +SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id= 65; +id a id a +65 65 65 65 +set optimizer_use_condition_selectivity=2; +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 +SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id= 65; +id a id a +65 65 65 65 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1,t2; # End of 10.1 tests diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 719156a77de..fdd2f2730a9 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1763,6 +1763,31 @@ a 1991 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1; +# +# MDEV-20595: Assertion `0 < sel && sel <= 2.0' failed in table_cond_selectivity +# +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_use_condition_selectivity=1; +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 +SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id= 65; +id a id a +65 65 65 65 +set optimizer_use_condition_selectivity=2; +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 +SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id= 65; +id a id a +65 65 65 65 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1,t2; # End of 10.1 tests set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 0deacc390db..8a2fac2e2a5 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1205,5 +1205,28 @@ set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivit DROP TABLE t1; +--echo # +--echo # MDEV-20595: Assertion `0 < sel && sel <= 2.0' failed in table_cond_selectivity +--echo # + +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; + +let $query= SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id= 65; + +set optimizer_use_condition_selectivity=1; +eval explain $query; +eval $query; + +set optimizer_use_condition_selectivity=2; +eval explain $query; +eval $query; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1,t2; + --echo # End of 10.1 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c6e70c2430c..f58167462fe 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7618,7 +7618,8 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, KEYUSE *keyuse= pos->key; KEYUSE *prev_ref_keyuse= keyuse; uint key= keyuse->key; - bool used_range_selectivity= false; + KEY *keyinfo= table->key_info + key; + bitmap_clear_all(&table->tmp_set); /* Check if we have a prefix of key=const that matches a quick select. @@ -7629,19 +7630,17 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, if (table->quick_rows[key] && !(quick_key_map & ~table->const_key_parts[key])) { - /* - Ok, there is an equality for each of the key parts used by the - quick select. This means, quick select's estimate can be reused to - discount the selectivity of a prefix of a ref access. + KEY_PART_INFO *keypart= keyinfo->key_part; + /* + Set the bitmap tmp_set for all the fields of type field=const + in the chosen ref access. These are the fields for which we will + discount the selectivity. */ - for (; quick_key_map & 1 ; quick_key_map>>= 1) + + for (uint i= 0; i < table->quick_key_parts[key]; i++, keypart++) { - while (keyuse->table == table && keyuse->key == key && - keyuse->keypart == keyparts) - { - keyuse++; - } - keyparts++; + Field *field= keypart->field; + bitmap_set_bit(&table->tmp_set, field->field_index); } /* Here we discount selectivity of the constant range CR. To calculate @@ -7656,7 +7655,6 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, sel /= (double)table->quick_rows[key] / (double) table->stat_records(); DBUG_ASSERT(0 < sel && sel <= 2.0); set_if_smaller(sel, 1.0); - used_range_selectivity= true; } } @@ -7692,7 +7690,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, if (keyparts > keyuse->keypart) { /* Ok this is the keyuse that will be used for ref access */ - if (!used_range_selectivity && keyuse->val->const_item()) + if (keyuse->val->const_item()) { uint fldno; if (is_hash_join_key_no(key)) @@ -7700,9 +7698,11 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, else fldno= table->key_info[key].key_part[keyparts-1].fieldnr - 1; - if (table->field[fldno]->cond_selectivity > 0) - { + if (!bitmap_is_set(&table->tmp_set, fldno) && + table->field[fldno]->cond_selectivity > 0) + { sel /= table->field[fldno]->cond_selectivity; + bitmap_set_bit(&table->tmp_set, fldno); DBUG_ASSERT(0 < sel && sel <= 2.0); set_if_smaller(sel, 1.0); } @@ -7750,6 +7750,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, for (Field **f_ptr=table->field ; (field= *f_ptr) ; f_ptr++) { if (!bitmap_is_set(read_set, field->field_index) || + bitmap_is_set(&table->tmp_set, field->field_index) || !field->next_equal_field) continue; for (Field *next_field= field->next_equal_field; @@ -7761,6 +7762,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, if (field->cond_selectivity > 0) { sel/= field->cond_selectivity; + bitmap_set_bit(&table->tmp_set, field->field_index); DBUG_ASSERT(0 < sel && sel <= 2.0); set_if_smaller(sel, 1.0); }