Description:
Since we upgraded from 5.0.18 to 5.0.22, I have an issue with WHERE conditions on LEFT (OUTER) JOINs.
I generate lots of different joins and clauses and some cause the optimizer to compute an inner join.
I have created a simplified example where I get different results for "WHERE 1" and "WHERE 1 or 0".
Note, my real clauses are more complex, I just have the WHERE 1 in front to programmatically add generated conditions.
Best,
Christian
How to repeat:
CREATE TABLE IF NOT EXISTS a (
id int(11) NOT NULL,
KEY id (id)
) TYPE=MyISAM;
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);
CREATE TABLE IF NOT EXISTS b (
a_id int(11) NOT NULL,
KEY a_id (a_id)
) TYPE=MyISAM;
INSERT INTO b VALUES (2);
SELECT *
FROM a
LEFT OUTER JOIN b ON a.id = b.a_id
WHERE 1;
Result is CORRECT:
+----+------+
| id | a_id |
+----+------+
| 1 | NULL |
| 2 | 2 |
+----+------+
SELECT *
FROM a
LEFT OUTER JOIN b ON a.id = b.a_id
WHERE 1 or 0;
Result is WRONG:
+----+------+
| id | a_id |
+----+------+
| 2 | 2 |
+----+------+
EXPLAIN SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.a_id WHERE 1 ;
+----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
| 1 | SIMPLE | a | index | NULL | id | 4 | NULL | 2 | Using index |
| 1 | SIMPLE | b | ref | a_id | a_id | 4 | cms_be.a.id | 2 | Using index |
+----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
EXPLAIN SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.a_id WHERE 1 or 0 ;
+----+-------------+-------+--------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | b | system | a_id | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | a | ref | id | id | 4 | const | 2 | Using index |
+----+-------------+-------+--------+---------------+------+---------+-------+------+-------------+
Suggested fix:
Fix the optimizer
