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
*/