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.

2 files changed, 55 insertions, 0 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;