diff options
| -rw-r--r-- | mysql-test/r/selectivity.result | 34 | ||||
| -rw-r--r-- | mysql-test/r/selectivity_innodb.result | 34 | ||||
| -rw-r--r-- | mysql-test/t/selectivity.test | 31 | ||||
| -rw-r--r-- | sql/sql_statistics.cc | 2 |
4 files changed, 100 insertions, 1 deletions
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 512ef4ffc1b..e59f4310431 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1094,4 +1094,38 @@ f1 f2 f2 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1,t2; set use_stat_tables=@save_use_stat_tables; +# +# Bug mdev-4406: range condition for non-nullable column +# when optimizer_use_condition_selectivity=3 +# +create table t1 (a int not null); +insert into t1 values +(7), (6), (4), (9), (1), (5), (2), (1), (3), (8); +set use_stat_tables='preferably'; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +flush table t1; +set optimizer_use_condition_selectivity=3; +select count(*) from t1 where a between 5 and 7; +count(*) +3 +explain extended select * from t1 where a between 5 and 7; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` between 5 and 7) +alter table t1 change column a a int; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +flush table t1; +explain extended select * from t1 where a between 5 and 7; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` between 5 and 7) +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +set use_stat_tables=@save_use_stat_tables; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 2dce973a587..20efb16914b 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1102,6 +1102,40 @@ f1 f2 f2 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1,t2; set use_stat_tables=@save_use_stat_tables; +# +# Bug mdev-4406: range condition for non-nullable column +# when optimizer_use_condition_selectivity=3 +# +create table t1 (a int not null); +insert into t1 values +(7), (6), (4), (9), (1), (5), (2), (1), (3), (8); +set use_stat_tables='preferably'; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +flush table t1; +set optimizer_use_condition_selectivity=3; +select count(*) from t1 where a between 5 and 7; +count(*) +3 +explain extended select * from t1 where a between 5 and 7; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` between 5 and 7) +alter table t1 change column a a int; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +flush table t1; +explain extended select * from t1 where a between 5 and 7; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` between 5 and 7) +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +set use_stat_tables=@save_use_stat_tables; set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 8bde4718fdd..47f62365816 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -676,5 +676,36 @@ DROP TABLE t1,t2; set use_stat_tables=@save_use_stat_tables; +--echo # +--echo # Bug mdev-4406: range condition for non-nullable column +--echo # when optimizer_use_condition_selectivity=3 +--echo # + +create table t1 (a int not null); +insert into t1 values + (7), (6), (4), (9), (1), (5), (2), (1), (3), (8); + +set use_stat_tables='preferably'; + +analyze table t1; +flush table t1; + +set optimizer_use_condition_selectivity=3; + +select count(*) from t1 where a between 5 and 7; +explain extended select * from t1 where a between 5 and 7; + +alter table t1 change column a a int; +analyze table t1; +flush table t1; + +explain extended select * from t1 where a between 5 and 7; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +drop table t1; + +set use_stat_tables=@save_use_stat_tables; + set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index 4df4b4d257d..f355f2c7760 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3520,7 +3520,7 @@ double get_column_range_cardinality(Field *field, { double sel, min_mp_pos, max_mp_pos; - if (min_endp && !min_endp->key[0]) + if (min_endp && !(field->null_ptr && min_endp->key[0])) { store_key_image_to_rec(field, (uchar *) min_endp->key, min_endp->length); |