Bug #926 wrong "Impossible WHERE"
Submitted: 25 Jul 2003 5:21 Modified: 25 Jul 2003 5:22
Reporter: Sergei Golubchik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:
Assigned to: Sergei Golubchik CPU Architecture:Any

[25 Jul 2003 5:21] Sergei Golubchik
Description:
Found a very interesting bug.  MySQL 3.23.57-Max, 4.0.13, and 4.0.14 all
seem to show the problem.

To reproduce:

drop table table_a;
drop table table_b;
drop table table_c;
drop table table_d;

create table table_a (id int NOT NULL);
create table table_b (id int NOT NULL);
create table table_c (id int NOT NULL);
create table table_d (id int NOT NULL, other_id int NOT NULL, KEY (id));

insert into table_a values (1);
insert into table_a values (2);
insert into table_b values (1);
insert into table_d values (1,1);

select a.*
from table_a a
left join table_b b on a.id = b.id
left join table_c c on a.id = c.id
left join table_d d on c.id = d.id
where
                d.id = 1
        or
                b.id = 1;

This should return one row, but returns nothing, and "explain" shows
"Impossible WHERE noticed after reading const tables".  This depends on:

- A minimum of two rows in table_a.  Removing the second row in this
  table seems to make it work.

- A minimum of two columns in table_d.  Removing the second column in
  this table seems to make it work.

- An index must exist on the id field in table_d.  Removing this index
  seems to make it work.

- The join must be in the above order.  Reodering the join of table_c and
  table_d to before the join of table_b seems to make it work, even
  though this should have no effect on the results.  For example, this
  query works:

select a.*
from table_a a
left join table_c c on a.id = c.id
left join table_d d on c.id = d.id
left join table_b b on a.id = b.id
where
                d.id = 1
        or
                b.id = 1;

Very strange. :)

How to repeat:
[25 Jul 2003 5:22] Sergei Golubchik
fixed in 4.0.15