revision-id: 63bcbd9ef7d15bbe1422f82b65e29e57308f6f57 (mariadb-10.3.10-38-g63bcbd9ef7d) parent(s): ec30bfac7e3754682da58f64b6c78ed54809fe8e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2018-10-23 14:12:59 +0200 message: MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins Update .result files after the previous patch --- mysql-test/main/join_outer_jcl6.result | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index 842ab19e20a..6f5e4b3e85e 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2542,7 +2542,39 @@ 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 Using join buffer (flat, BNL join) 1 SIMPLE t3 range a a 5 NULL 5 Using where; Rowid-ordered scan; Using join buffer (incremental, BNL join) +# +# .. 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 hash_ALL NULL #hash#$hj 5 test.t1.a 1000 0.99 Using where; Using join buffer (flat, BNLH join) +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 and `test`.`t1`.`a` is not null) 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 Using where +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 and `test`.`t1`.`a` is not null) 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; set join_cache_level=default; show variables like 'join_cache_level';