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

Bad selectivity for col IS NULL OR col IS NOT NULL

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None

    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% ?

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.