Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4363

Bad selectivity for col IS NULL OR col IS NOT NULL

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: None
        • Fix Version/s: None
        • Component/s: None
        • Labels:

          Description

          Take a dataset and settings from MDEV-4362.

          Then lets add another column with another distribution:

          alter table t5 add col2 int;
update t5 set col2=NULL where col2 < 33;
update t5 set col2=NULL where col1 < 33;
update t5 set col2=178 where col1 >= 33 and col1 < 66;
update t5 set col2=47 where col1 >= 66 and col1 < 77;
set @a=11;
update t5 set col2=(@a:=@a+1) where col1 >= 77;
analyze table t5 persistent for all;

          Let's first check for NULLs:

          MariaDB [j10]> explain extended select  * from t5 where col2 IS NULL;
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|    1 | SIMPLE      | t5    | ALL  | NULL          | NULL | NULL    | NULL | 10000 |    33.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

          Correct.

          MariaDB [j10]> explain extended select  * from t5 where col2 IS not NULL;
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|    1 | SIMPLE      | t5    | ALL  | NULL          | NULL | NULL    | NULL | 10000 |    65.01 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

          Also correct, a precise estimate.

          MariaDB [j10]> explain extended select  * from t5 where col2 IS not NULL or col2 is null;
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|    1 | SIMPLE      | t5    | ALL  | NULL          | NULL | NULL    | NULL | 10000 |    65.01 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

          Ooops. Why is selectivity still 65% ?

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Task - A task that needs to be done. MDEV-4145 Take into account the selectivity of single-table range predicates on non-indexed columns when searching for the best execution plan

                • Major - Major loss of function.
                • Closed

                  Activity

                  Hide
                  Permalink
                  igor Igor Babaev added a comment -

                  A fix for the bug has been pushed into maria-10.0-mwl253.

                  Show
                  igor Igor Babaev added a comment - A fix for the bug has been pushed into maria-10.0-mwl253.

                    People

                    • Assignee:
                      igor Igor Babaev
                      Reporter:
                      psergey Sergei Petrunia
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: