We're updating the issue view to help you get more done. 

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

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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);

Environment

openSUSE 12.1 mariadb-5.5.28a-1.4.1.x86_64

Status

Assignee

Sergei Petrunia

Reporter

Archie Cobbs

Fix versions

Affects versions

5.5.32

Priority

Major