diff options
Diffstat (limited to 'mysql-test/r/selectivity.result')
| -rw-r--r-- | mysql-test/r/selectivity.result | 63 |
1 files changed, 63 insertions, 0 deletions
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 9899b894ff6..3f2343fa365 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1346,4 +1346,67 @@ foo foo 1 foo foo 2 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1,t2; +# +# Bug mdev-6325: wrong selectivity of a column with ref access +# +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 (a int, b int, key(a)); +insert into t2 select A.a + 10*B.a, 12345 from t0 A, t0 B, t0 C; +set use_stat_tables='preferably'; +set histogram_size=100; +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 Table is already up to date +explain extended +select * from t1 straight_join t2 where t1.a=t2.a and t1.a<10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 0.99 Using where +1 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 10)) +explain extended +select * from t1 straight_join t2 where t1.a=t2.a and t2.a<10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 0.99 Using where +1 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 10)) +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t0,t1,t2; +# +# Bug mdev-6843: col IS NULL in where condition when col is always NULL +# +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 (a int, b int); +insert into t2 select NULL, a from t1; +set use_stat_tables='preferably'; +set histogram_size=100; +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 +explain extended +select * from t2 a straight_join t2 b where a.a is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE a ALL NULL NULL NULL NULL 1000 100.00 Using where +1 SIMPLE b ALL NULL NULL NULL NULL 1000 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`a`.`a` AS `a`,`test`.`a`.`b` AS `b`,`test`.`b`.`a` AS `a`,`test`.`b`.`b` AS `b` from `test`.`t2` `a` straight_join `test`.`t2` `b` where isnull(`test`.`a`.`a`) +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t0,t1,t2; set use_stat_tables=@save_use_stat_tables; |