MariaDB Development
  1. MariaDB Development
  2. MDEV-4817

Optimizer fails to optimize expression of the form 'FOO' IS NULL

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.32
    • Fix Version/s: 5.5.33
    • Labels:
      None
    • Environment:
      openSUSE 12.1
      mariadb-5.5.28a-1.4.1.x86_64
    • Global Rank:
      3370

      Description

      This is a copy of this MySQL bug: http://bugs.mysql.com/bug.php?id=69359

      I am filing it here in hopes it will get more attention than from the MySQL folks.

      Note, this bug occurs in BOTH MySQL and MariaDB.

      Restatement of bug follows...

      See

      http://stackoverflow.com/questions/16848190/mysql-why-isnt-foo-is-null-optimized-away

      for a description. Quoting that here:

      I have two tables Person and Message and the latter has a foreign key to the former. Each table has id as the primary key column, and the Person table also has a column personId which is (uniquely) indexed.

      The query below should take advantage of the personId key index, but instead MySQL requires scanning the entire Message table for some reason:

      mysql> EXPLAIN SELECT `m`.*
          -> FROM
          ->   `Message` AS `m`
          -> LEFT JOIN
          ->   `Person` AS `p` ON (`m`.`person` = `p`.`id`)
          -> WHERE
          ->   'M002649397' IS NULL OR
          ->   `p`.`personId` = 'M002649397';
      +----+-------------+-------+--------+---------------+---------+---------+----------------+--------+-------------+
      | id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows   | Extra       |
      +----+-------------+-------+--------+---------------+---------+---------+----------------+--------+-------------+
      |  1 | SIMPLE      | m     | ALL    | NULL          | NULL    | NULL    | NULL           | 273220 |             |
      |  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 8       | pcom.m.person  |      1 | Using where |
      +----+-------------+-------+--------+---------------+---------+---------+----------------+--------+-------------+
      2 rows in set (0.00 sec)
      

      But when I comment out the 'M002649397' IS NULL OR clause (which has no effect on the result), the query suddenly gets more efficient:

      mysql> EXPLAIN SELECT `m`.*
          -> FROM
          ->   `Message` AS `m`
          -> LEFT JOIN
          ->   `Person` AS `p` ON (`m`.`person` = `p`.`id`)
          -> WHERE
          -> --  'M002649397' IS NULL OR
          ->   `p`.`personId` = 'M002649397';
      +----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
      | id | select_type | table | type  | possible_keys      | key                | key_len | ref   | rows | Extra       |
      +----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
      |  1 | SIMPLE      | p     | const | PRIMARY,personId   | personId           | 767     | const |    1 | Using index |
      |  1 | SIMPLE      | m     | ref   | FK9C2397E7A0F6ED11 | FK9C2397E7A0F6ED11 | 9       | const |    3 | Using where |
      +----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
      2 rows in set (0.01 sec)
      

      The bug is that the 'M002649397' IS NULL expression, which is always false, is not being optimized away.

      Here is a schema to test with:

      create table Message (
          type char(1) not null,
          id bigint not null auto_increment,
          createTime datetime not null,
          updateTime datetime not null,
          person bigint,
          primary key (id)
      ) ENGINE=InnoDB default charset=utf8 collate=utf8_bin;
      
      create table Person (
          id bigint not null auto_increment,
          createTime datetime not null,
          updateTime datetime not null,
          firstName varchar(255),
          lastName varchar(255),
          middleName varchar(255),
          personId varchar(255) not null unique,
          primary key (id)
      ) ENGINE=InnoDB default charset=utf8 collate=utf8_bin;
      
      create index idx_Message_createTime on Message (createTime);
      
      alter table Message
          add index FK9C2397E7A0F6ED11 (person),
          add constraint FK9C2397E7A0F6ED11
          foreign key (person)
          references Person (id);
      
      create index idx_Person_lastName on Person (lastName);
      

        Issue Links

          Activity

          Hide
          Sergei Petrunia added a comment -

          Maybe, we could remove items that have

          item->const_item() && !item->is_expensive() && item->val_int() == 0

          from having item->not_null_tables() put into not_null_tables_cache.

          Show
          Sergei Petrunia added a comment - Maybe, we could remove items that have item->const_item() && !item->is_expensive() && item->val_int() == 0 from having item->not_null_tables() put into not_null_tables_cache.
          Hide
          Sergei Petrunia added a comment -

          Committed a patch. It needs to be tested and reviewed.

          Show
          Sergei Petrunia added a comment - Committed a patch. It needs to be tested and reviewed.
          Hide
          Sergei Petrunia added a comment - - edited

          The fix was pushed into 5.5, and will be available in MariaDB 5.5.33.

          Show
          Sergei Petrunia added a comment - - edited The fix was pushed into 5.5, and will be available in MariaDB 5.5.33.
          Hide
          Sergei Petrunia added a comment -

          Archie, thanks for reporting this bug here. The fix should be an improvement for the MariaDB optimizer.

          Show
          Sergei Petrunia added a comment - Archie, thanks for reporting this bug here. The fix should be an improvement for the MariaDB optimizer.
          Hide
          Archie Cobbs added a comment -

          Thanks for the quick turnaround!

          Show
          Archie Cobbs added a comment - Thanks for the quick turnaround!

            People

            • Assignee:
              Sergei Petrunia
              Reporter:
              Archie Cobbs
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: