Bug #27939 Early NULLs filtering doesn't work for eq_ref access
Submitted: 18 Apr 2007 22:22 Modified: 8 May 2007 1:28
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0-bk OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any

[18 Apr 2007 22:22] Sergey Petrunya
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.
[18 Apr 2007 23:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24871

ChangeSet@1.2456, 2007-04-19 03:04:23+04:00, sergefp@mysql.com +3 -0
  BUG#27939: Early NULLs filtering doesn't work for eq_ref access
   - Turn it on for JT_EQ_REF access method
[30 Apr 2007 12:58] Bugs System
Pushed into 5.0.42
[30 Apr 2007 12:58] Bugs System
Pushed into 5.1.18-beta
[8 May 2007 1:28] Paul Dubois
Noted in 5.0.42, 5.1.18 changelogs.