Table scan when NULL appears in IN clause

[Stewart Smith]

http://bugs.mysql.com/bug.php?id=44139

Optimizer chooses a table scan when there is a NULL in the IN clause.

This is a regression. Compare EXPLAIN output from 5.1.79:

mysql [localhost] {msandbox} (test) > explain select * from foo where a in (160000,
160001, 160002)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select * from foo where a in (null, 160000,
160001, 160002)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.01 sec)

To 5.1.32:

mysql [localhost] {msandbox} (test) > explain select * from foo where a in (160000,
160001, 160002)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.06 sec)

mysql [localhost] {msandbox} (test) > explain select * from foo where a in (null, 160000,
160001, 160002)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 327680
        Extra: Using where
1 row in set (0.00 sec)

How to repeat:
use test;
drop table if exists foo;

create table foo (
 a int not null auto_increment,
 b int,
 primary key( a )
);

insert into foo( b ) values (1),(1),(1),(1),(1);
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;
insert into foo( b ) select b from foo;

explain select * from foo where a in (160000, 160001, 160002)\G
explain select * from foo where a in (null, 160000, 160001, 160002)\G

[Stewart Smith (stewart)]

verified on trunk