Merge

1 files changed, 34 insertions, 0 deletions

diff --git a/mysql-test/r/selectivity_no_engine.result b/mysql-test/r/selectivity_no_engine.result
index e07329422af..173f8f70a23 100644
--- a/mysql-test/r/selectivity_no_engine.result
+++ b/mysql-test/r/selectivity_no_engine.result
@@ -243,6 +243,40 @@ foo	2011-04-12 05:18:08	foo	baz	qux
 bar	2013-09-19 11:37:03	bar	baz	qux
 drop table t1,t2;
 # 
+# MDEV-5985: EITS: selectivity estimates look illogical for join and non-key equalities
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t2 as select * from t1;
+set histogram_size=100;
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=4;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+analyze table t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+# Filtered will be 4.95, 9.90
+explain extended select * from t1 A, t2 B where A.a < 40 and B.a < 100;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	1000	4.95	Using where
+1	SIMPLE	B	ALL	NULL	NULL	NULL	NULL	1000	9.90	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`A`.`a` AS `a`,`test`.`B`.`a` AS `a` from `test`.`t1` `A` join `test`.`t2` `B` where ((`test`.`A`.`a` < 40) and (`test`.`B`.`a` < 100))
+# Here, B.filtered should not become 100%:
+explain extended select * from t1 A, t2 B where A.a < 40 and B.a < 100 and B.a=A.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	1000	4.95	Using where
+1	SIMPLE	B	ALL	NULL	NULL	NULL	NULL	1000	4.95	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`A`.`a` AS `a`,`test`.`B`.`a` AS `a` from `test`.`t1` `A` join `test`.`t2` `B` where ((`test`.`B`.`a` = `test`.`A`.`a`) and (`test`.`A`.`a` < 40) and (`test`.`A`.`a` < 100))
+drop table t0,t1,t2;
+# 
 # End of the test file
 # 
 set use_stat_tables= @save_use_stat_tables;