Description:
Early NULLs filtering doesn't work when the referring table is acessed using eq_ref access method. Ref method works.
How to repeat:
Run those queries:
create table t0 (a int, b int);
insert into t0 values
(NULL, 1),
(NULL, 2),
(NULL, 3),
(NULL, 4);
create table t1 (a int not null, primary key(a));
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int not null, primary key(a));
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
flush status;
mysql> select * from t0, t1, t2 where t2.a=t0.a and t1.a=t0.b;
Empty set (2.43 sec)
mysql> show status like 'Handler_%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 4 | (*)
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 5 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 14 |
+----------------------------+-------+
15 rows in set (1.59 sec)
The EXPLAIN is:
mysql> explain select * from t0, t1, t2 where t2.a=t0.a and t1.a=t0.b;
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | j312.t0.b | 1 | Using index |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | j312.t0.a | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
3 rows in set (2.75 sec)
Early nulls filtering optimization is supposed to attach this condition to table t0:
(t0.a IS NOT NULL) AND (t0.b IS NOT NULL)
The statistics shows that
Handler_read_key=4
which means that the condition wasn't attached. (4 is the number of index lookups done in table t1. Lookups in table t2 are not done because of Late NULLs Filtering).
Suggested fix:
Make Early NULLs Filtering work for eq_ref access.
