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

    • Type: Bug
    • Status: Closed (View Workflow)
    • 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% ?

        Attachments

          Issue Links

            Activity

              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: