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:
© 2025, Oracle Corporation and/or its affiliates
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: