[MDEV-4406] Wrong estimate of records in range for non-nullable column Created: 2013-04-20  Updated: 2014-10-14  Resolved: 2014-10-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.1, 10.0.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: 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 |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+



 Comments   
Comment by Patryk Pomykalski [ 2013-04-20 ]

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])
    + if (min_endp && (!min_endp->key[0] || !field->null_ptr))
    {
    store_key_image_to_rec(field, (uchar *) min_endp->key,
    min_endp->length);
Comment by Elena Stepanova [ 2014-10-14 ]

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).

Generated at Thu Feb 08 06:56:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.