[MDEV-3886] erroneous implicit "fiels is not null" in left join Created: 2012-11-26  Updated: 2012-11-27  Resolved: 2012-11-27

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.28, 5.3.11
Fix Version/s: 5.5.28a

Type: Bug Priority: Major
Reporter: Sergei Golubchik Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

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.
Generated at Thu Feb 08 06:52:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.