revision-id: ec30bfac7e3754682da58f64b6c78ed54809fe8e (mariadb-10.3.10-37-gec30bfac7e3) parent(s): 4c306c51be6fc6554e558783175917551705a1e6 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2018-10-23 13:17:14 +0200 message: MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins Continuation of the fix: Make condition selectivity estimate use the right estimate, too. --- mysql-test/main/join_outer.result | 32 ++++++++++++++++++++++++++++++++ mysql-test/main/join_outer.test | 23 +++++++++++++++++++++++ sql/sql_select.cc | 8 +++++--- 3 files changed, 60 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index 5a123826d79..aa9f30885a9 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2531,5 +2531,37 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 1 SIMPLE t3 range a a 5 NULL 5 Using where +# +# .. part 2: make sure condition selectivity can use the condition too. +# +alter table t3 drop key a; +set @tmp1=@@optimizer_use_condition_selectivity; +set @tmp2=@@use_stat_tables; +set @tmp3=@@histogram_size; +set use_stat_tables=preferably; +set optimizer_use_condition_selectivity=4; +set histogram_size=100; +analyze table t3 persistent for all; +Table Op Msg_type Msg_text +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +# t3.filtered is less than 100%: +explain extended select * from t1 left join t3 on t1.a=t3.b and t3.a<5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 0.99 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` left join `test`.`t3` on(`test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` < 5) where 1 +# t3.filtered must less than 100%, too: +explain extended select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 0.99 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` left join (`test`.`t3` join `test`.`t2`) on(`test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` < 5) where 1 drop table t1,t2,t3; +set optimizer_use_condition_selectivity= @tmp1; +set use_stat_tables= @tmp2; +set histogram_size= @tmp3; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index 28f544dec7d..9118febc800 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -2060,6 +2060,29 @@ explain select * from t1 left join t3 on t1.a=t3.b and t3.a<5; --echo # This must use range for table t3, too: explain select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; +--echo # +--echo # .. part 2: make sure condition selectivity can use the condition too. +--echo # +alter table t3 drop key a; +set @tmp1=@@optimizer_use_condition_selectivity; +set @tmp2=@@use_stat_tables; +set @tmp3=@@histogram_size; +set use_stat_tables=preferably; +set optimizer_use_condition_selectivity=4; +set histogram_size=100; + +analyze table t3 persistent for all; + +--echo # t3.filtered is less than 100%: +explain extended select * from t1 left join t3 on t1.a=t3.b and t3.a<5; + +--echo # t3.filtered must less than 100%, too: +explain extended select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; + drop table t1,t2,t3; +set optimizer_use_condition_selectivity= @tmp1; +set use_stat_tables= @tmp2; +set histogram_size= @tmp3; + SET optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 08e9b8daf73..39c77dc4dc2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4951,9 +4951,10 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, bool impossible_range= FALSE; ha_rows records= HA_POS_ERROR; SQL_SELECT *select= 0; + Item **sargable_cond= NULL; if (!s->const_keys.is_clear_all()) { - Item **sargable_cond= get_sargable_cond(join, s->table); + sargable_cond= get_sargable_cond(join, s->table); select= make_select(s->table, found_const_table_map, found_const_table_map, @@ -4978,10 +4979,11 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, } if (!impossible_range) { + if (!sargable_cond) + sargable_cond= get_sargable_cond(join, s->table); if (join->thd->variables.optimizer_use_condition_selectivity > 1) calculate_cond_selectivity_for_table(join->thd, s->table, - *s->on_expr_ref ? - s->on_expr_ref : &join->conds); + sargable_cond); if (s->table->reginfo.impossible_range) { impossible_range= TRUE;