[MDEV-3886] erroneous implicit "fiels is not null" in left join - Jira

XML

Word

Printable

a query like

select t1.id

  from t1

  left join t2 on t1.id = t2.t1_id

  left join t3 on t2.id = t3.t2_id;

is rewritten by the optimizer into

select t1.id

  from t1

  left join t2 on (t2.t1_id = t1.id and t1.id is not null)

the table t3 is correctly eliminated, but the extended condition does not allow the table t2 to be eliminated too.

insert t1 values (1),(2);

create table t2 (t1_id int primary key, id int, key (id));

insert t2 values (1,1),(2,2);

create table t3 (t2_id int primary key);

insert t3 values (1),(2);

set sql_quote_show_create=0;

explain extended select t1.id from t1 left join t2 on t1.id = t2.t1_id left join t3 on t2.id = t3.t2_id;

on 5.1 and 5.2 the table t2 is not eliminated either, but the condition, as shown by EXPLAIN EXTENDED does not have the new clause.