Revert these two changes (wrong push) :

MDEV-5980: EITS: if condition is used for REF access, its selectivity is still in filtered%
MDEV-5985: EITS: selectivity estimates look illogical for join and non-key equalities
MDEV-6003: EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value
- Made a number of fixes in table_cond_selectivity() so that it returns
  correct selectivity estimates.
- Added comments in related code.
Better comments

2 files changed, 0 insertions, 184 deletions

diff --git a/mysql-test/r/selectivity_no_engine.result b/mysql-test/r/selectivity_no_engine.result
index 3f046b0c2b7..6516abbe318 100644
--- a/mysql-test/r/selectivity_no_engine.result
+++ b/mysql-test/r/selectivity_no_engine.result
@@ -139,118 +139,6 @@ Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col2` AS `col2` from `test`.`t2` where ((`test`.`t2`.`a` in (1,2,3)) and (`test`.`t2`.`b` in (1,2,3)))
 drop table t2, t1;
 # 
-# MDEV-5980: EITS: if condition is used for REF access, its selectivity is still in filtered%
-# 
-create table t0(a int);
-insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t1(key1 int, col1 int, key(key1));
-insert into t1 select A.a, A.a from t0 A, t0 B, t0 C;
-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	Table is already up to date
-# 10% is ok
-explain extended select * from t1 where col1=2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	9.90	Using where
-Warnings:
-Note	1003	select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` = 2)
-# Must show 100%, not 10%
-explain extended select * from t1 where key1=2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref	key1	key1	5	const	98	100.00	
-Warnings:
-Note	1003	select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`key1` = 2)
-drop table t0, t1;
-# 
-# MDEV-5985: EITS: selectivity estimates look illogical for join and non-key equalities
-# 
-create table t1(a int);
-insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a int);
-insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
-create table t3 as select * from t2;
-set histogram_size=100;
-set use_stat_tables='preferably';
-set optimizer_use_condition_selectivity=4;
-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
-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
-explain extended select * from t2 A where A.a < 40;
-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
-Warnings:
-Note	1003	select `test`.`A`.`a` AS `a` from `test`.`t2` `A` where (`test`.`A`.`a` < 40)
-explain extended select * from t3 B where B.a < 100;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	B	ALL	NULL	NULL	NULL	NULL	1000	9.90	Using where
-Warnings:
-Note	1003	select `test`.`B`.`a` AS `a` from `test`.`t3` `B` where (`test`.`B`.`a` < 100)
-explain extended select * from t2 A, t3 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`.`t2` `A` join `test`.`t3` `B` where ((`test`.`A`.`a` < 40) and (`test`.`B`.`a` < 100))
-explain extended select * from t2 A, t3 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`.`t2` `A` join `test`.`t3` `B` where ((`test`.`B`.`a` = `test`.`A`.`a`) and (`test`.`A`.`a` < 40) and (`test`.`A`.`a` < 100))
-drop table t1, t2, t3;
-select 1;
-1
-1
-# 
-# MDEV-6003: EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value
-# 
-create table t0(a int);
-insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t1 (
-kp1 int, kp2 int, 
-filler1 char(100),
-filler2 char(100),
-key(kp1, kp2)
-);
-insert into t1 
-select 
-A.a,
-B.a,
-'filler-data-1',
-'filler-data-2'
-from t0 A, t0 B, t0 C;
-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	Table is already up to date
-# NOTE: 10*100%, 10*100% rows is ok
-explain extended select * from t0, t1 where t1.kp1=t0.a and t1.kp2=t0.a+1;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
-1	SIMPLE	t1	ref	kp1	kp1	10	test.t0.a,func	10	100.00	Using index condition
-Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t1`.`kp1` AS `kp1`,`test`.`t1`.`kp2` AS `kp2`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t0` join `test`.`t1` where ((`test`.`t1`.`kp1` = `test`.`t0`.`a`) and (`test`.`t1`.`kp2` = (`test`.`t0`.`a` + 1)))
-# NOTE: t0: 10*100% is ok,  t1: 10*9.90% is bad. t1 should have 10*100%.
-explain extended select * from t0, t1 where t1.kp1=t0.a and t1.kp2=4;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
-1	SIMPLE	t1	ref	kp1	kp1	10	test.t0.a,const	10	100.00	
-Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t1`.`kp1` AS `kp1`,`test`.`t1`.`kp2` AS `kp2`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t0` join `test`.`t1` where ((`test`.`t1`.`kp1` = `test`.`t0`.`a`) and (`test`.`t1`.`kp2` = 4))
-drop table t0, t1;
-# 
 # End of the test file
 # 
 set use_stat_tables= @save_use_stat_tables;
diff --git a/mysql-test/t/selectivity_no_engine.test b/mysql-test/t/selectivity_no_engine.test
index d1e34a7ac6f..eb6642fb5c7 100644
--- a/mysql-test/t/selectivity_no_engine.test
+++ b/mysql-test/t/selectivity_no_engine.test
@@ -101,78 +101,6 @@ analyze table t2 persistent for all;
 explain extended select * from t2 where a in (1,2,3) and b in (1,2,3);
 
 drop table t2, t1;
-
---echo # 
---echo # MDEV-5980: EITS: if condition is used for REF access, its selectivity is still in filtered%
---echo # 
-create table t0(a int);
-insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t1(key1 int, col1 int, key(key1));
-insert into t1 select A.a, A.a from t0 A, t0 B, t0 C;
-
-set histogram_size=100;
-set use_stat_tables='preferably';
-set optimizer_use_condition_selectivity=4;
-analyze table t1 persistent for all;
---echo # 10% is ok
-explain extended select * from t1 where col1=2;
---echo # Must show 100%, not 10%
-explain extended select * from t1 where key1=2;
-drop table t0, t1;
-
-##--disable_parsing
---echo # 
---echo # MDEV-5985: EITS: selectivity estimates look illogical for join and non-key equalities
---echo # 
-create table t1(a int);
-insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a int);
-insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
-create table t3 as select * from t2;
-set histogram_size=100;
-set use_stat_tables='preferably';
-set optimizer_use_condition_selectivity=4;
-analyze table t2 persistent for all;
-analyze table t3 persistent for all;
-explain extended select * from t2 A where A.a < 40;
-explain extended select * from t3 B where B.a < 100;
-explain extended select * from t2 A, t3 B where A.a < 40 and B.a < 100;
-explain extended select * from t2 A, t3 B where A.a < 40 and B.a < 100 and B.a=A.a;
-drop table t1, t2, t3;
-## --enable_parsing
-select 1;
-##--disable_parsing
---echo # 
---echo # MDEV-6003: EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value
---echo # 
-create table t0(a int);
-insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t1 (
-  kp1 int, kp2 int, 
-  filler1 char(100),
-  filler2 char(100),
-  key(kp1, kp2)
-);
-
-insert into t1 
-select 
-  A.a,
-  B.a,
-  'filler-data-1',
-  'filler-data-2'
-from t0 A, t0 B, t0 C;
-set histogram_size=100;
-set use_stat_tables='preferably';
-set optimizer_use_condition_selectivity=4;
-analyze table t1 persistent for all;
---echo # NOTE: 10*100%, 10*100% rows is ok
-explain extended select * from t0, t1 where t1.kp1=t0.a and t1.kp2=t0.a+1;
-
---echo # NOTE: t0: 10*100% is ok,  t1: 10*9.90% is bad. t1 should have 10*100%.
-explain extended select * from t0, t1 where t1.kp1=t0.a and t1.kp2=4;
-drop table t0, t1;
-##--enable_parsing
-
 --echo # 
 --echo # End of the test file
 --echo #