MySQL Bugs: #19346: Range optimizer decides to scan NULL key values for t.key IN (NULL, ...)

Bug #19346	Range optimizer decides to scan NULL key values for t.key IN (NULL, ...)
Submitted:	25 Apr 2006 17:41	Modified:	10 Dec 2009 21:26
Reporter:	Sergey Petrunya	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5.0.89-bzr	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Range optimizer decides to scan NULL values for  t.key IN (NULL, ...) while it is not neccesary.

How to repeat:
# Fill the test table
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int, filler char(200), key(a));
insert into t2 select A.a + 10*(B.a + 10*C.a), 'filler' from t1 A, t1 B, t1 C;
insert into t2 select NULL, 'filler' from t1 A, t1 B;

# Explore:
mysql> explain select * from t2 where a IN (NULL,1,2);
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | range | a             | a    | 5       | NULL |  120 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

# Note the #rows. Now let's see where this number came from:

mysql> explain select * from t2 where a=1 or a=2;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | range | a             | a    | 5       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t2 where a IS NULL;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t2    | ref  | a             | a    | 5       | const |  117 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

# This means that for " a IN (NULL, 1, 2)" we scan ranges:
 1) a IS NULL 
 2) a=1
 3) a=2

The "a IS NULL" range should not be scanned because for NULL 'value' the WHERE
 clause evaluates to 
 NULL IN (NULL,1,2) = NULL 
i.e. it is apparent that rows with a IS NULL will be filtered out.

Suggested fix:
Consider making range optimizer not to scan NULL ranges for t.key IN (... NULL ...).

Thank you for the bug report.

Setting to "To be fixed later" - we'll need to fix this someday but we're not aware about anyone waiting for it. There are even 5.2 features that people wait for, and those features in my understanding should be fixed before this bug.

This might be already fixed together with Bug 19375

This is fixed in recent 6.0.x and 5.1.x:

77-52-7-73:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.43-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1(a int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> create table t2 (a int, filler char(200), key(a));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t2 select A.a + 10*(B.a + 10*C.a), 'filler' from t1 A, t1 B, t1 C;
Query OK, 1000 rows affected (0.01 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> insert into t2 select NULL, 'filler' from t1 A, t1 B;
Query OK, 100 rows affected (0.00 sec)
Records: 100  Duplicates: 0  Warnings: 0

mysql> explain select * from t2 where a IN (NULL,1,2);
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | range | a             | a    | 5       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

But with 5.0.x it is still repeatable:

77-52-7-73:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.89-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1, t2;
Query OK, 0 rows affected (0.18 sec)

mysql> create table t1(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> create table t2 (a int, filler char(200), key(a));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 select A.a + 10*(B.a + 10*C.a), 'filler' from t1 A, t1 B, t1 C;
Query OK, 1000 rows affected (0.02 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> insert into t2 select NULL, 'filler' from t1 A, t1 B;
Query OK, 100 rows affected (0.00 sec)
Records: 100  Duplicates: 0  Warnings: 0

mysql>  explain select * from t2 where a IN (NULL,1,2);
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | range | a             | a    | 5       | NULL |  120 | Using where | 
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.02 sec)

So, we should still fix this in 5.0.

Bug fixed in 5.1 - will not be fixed in 5.0