CREATE TABLE local ( id bigint(20) NOT NULL AUTO_INCREMENT, base_id int(11), PRIMARY KEY (id), KEY local_m1 (base_id) ) ENGINE=InnoDB; CREATE TABLE intersection ( base_id int(11) NOT NULL, child_id int(11) NOT NULL, UNIQUE KEY intersection_u1 (base_id,child_id), KEY intersection_m1 (child_id) ) ENGINE=InnoDB; CREATE TABLE base ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB; INSERT INTO local VALUES (1, null); INSERT INTO intersection VALUES (1, 1); INSERT INTO base VALUES (1); -- Original problem query SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = i.base_id AND b.id = 1 WHERE l.id = 1; -- All queries below are the same as above, except for the WHERE condition. Some will show bogus value "1" for b.id in the results. SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = 1 AND b.id = i.base_id WHERE l.id = 1 OR l.id = 1; SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = 1 AND b.id = i.base_id WHERE l.id = 0 OR l.id = 1; SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = 1 AND b.id = i.base_id WHERE l.id = 1 OR l.id = 0; SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = 1 AND b.id = i.base_id WHERE l.id = 1 AND l.id = 1; SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = 1 AND b.id = i.base_id WHERE l.id IN (1); SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = 1 AND b.id = i.base_id WHERE l.id IN (1,1); SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = 1 AND b.id = i.base_id WHERE l.id IN (0,1); SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = 1 AND b.id = i.base_id WHERE l.id IN (1,0); -- "Fixed" query which drops the constant expression 'AND b.id = 1' in the ON for "base" table LEFT JOIN SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = i.base_id WHERE l.id = 1; -- None of these produces the error SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = i.base_id WHERE l.id = 1 OR l.id = 1; SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = i.base_id WHERE l.id = 0 OR l.id = 1; SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = i.base_id WHERE l.id = 1 OR l.id = 0; SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = i.base_id WHERE l.id = 1 AND l.id = 1; SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = i.base_id WHERE l.id IN (1); SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = i.base_id WHERE l.id IN (1,1); SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = i.base_id WHERE l.id IN (0,1); SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = i.base_id WHERE l.id IN (1,0); /* I also flipped optimizer_switch with this bash snippet to prove they have no effect #!/bin/bash switches="index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=on,materialization=off,semijoin=off,loosescan=off,firstmatch=off,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off" sql="SELECT 'local ->' as 't1',l.*, 'intersection ->' as 't2', i.*, 'base ->' as 't3', b.* FROM local AS l LEFT JOIN intersection AS i ON l.base_id = i.child_id LEFT JOIN base AS b ON b.id = 1 AND b.id = i.base_id WHERE l.id = 1 OR l.id = 1"; tr "," "\n" <<< $switches |while read switch; do { echo -e "==========================\n${switch}\n========================="; ./use -BNe "SET optimizer_switch=\"${switch}\"; ${sql};" test; } done */