diff options
Diffstat (limited to 'mysql-test/r/selectivity_no_engine.result')
| -rw-r--r-- | mysql-test/r/selectivity_no_engine.result | 34 |
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; |