Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.1, 10.0.2
-
None
Description
If a column is defined with NOT NULL specifier then the estimate for the records in range can be greatly off.
The following test case demonstrates this problem:
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; |
MariaDB [test]> 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 | 75.00 | Using where | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ |
If we remove specifier NOT NULL
alter table t1 change column a a int; |
the estimate becomes good:
MariaDB [test]> 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 | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ |