Details
Major
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 | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The bug was fixed back then, in 10.0.2:
revno: 3427.18.37
revision-id: igor@askmonty.org-20130420091655-ir0aup5jdrl9skjz
parent: sanja@askmonty.org-20130418192204-g02z43zzxih2kbqn
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-10.0-mwl253-test
timestamp: Sat 2013-04-20 02:16:55 -0700
message:
Fixed bug mdev-4406.
This bug in the code of get_column_range_cardinality() could lead to
wrong estimates of number of records in ranges for non-nullable columns.
(Setting fix version to N/A because 10.0.2 is not on the list anymore).
fix idea:
=== modified file 'sql/sql_statistics.cc'
— sql/sql_statistics.cc 2013-04-16 05:43:07 +0000
+++ sql/sql_statistics.cc 2013-04-20 14:10:42 +0000
@@ -3520,7 +3520,7 @@
{
double sel, min_mp_pos, max_mp_pos;
+ if (min_endp && (!min_endp->key[0] || !field->null_ptr))
{
store_key_image_to_rec(field, (uchar *) min_endp->key,
min_endp->length);