[PROD-SER] [marcos.albe@bm-support01 mysql_5_7_29]$ ./use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 43 Server version: 5.7.29 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql- [localhost:5730] {msandbox} (test) > drop database test; Query OK, 3 rows affected (0.02 sec) mysql- [localhost:5730] {msandbox} ((none)) > create database test; Query OK, 1 row affected (0.00 sec) mysql- [localhost:5730] {msandbox} ((none)) > use test; Database changed mysql- [localhost:5730] {msandbox} (test) > CREATE TABLE local ( -> id bigint(20) NOT NULL AUTO_INCREMENT, -> base_id int(11), -> * -> PRIMARY KEY (id), -> KEY local_m1 (base_id) -> ) ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > CREATE TABLE intersection ( -> base_id int(11) NOT NULL, i. -> child_id int(11) NOT NULL, -> -> UNIQUE KEY intersection_u1 (base_id,child_id), -> KEY intersection_m1 (child_id) -> ) ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > CREATE TABLE base ( -> id int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (id) -> ) ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > INSERT INTO local VALUES (1, null); Query OK, 1 row affected (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > INSERT INTO intersection VALUES (1, 1); Query OK, 1 row affected (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > INSERT INTO base VALUES (1); Query OK, 1 row affected (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > -- Original problem query mysql- [localhost:5730] {msandbox} (test) > 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; +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | 1 | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > -- 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. mysql- [localhost:5730] {msandbox} (test) > 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; +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | 1 | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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; +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | 1 | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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; +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | 1 | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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; +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | 1 | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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); +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | 1 | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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); +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | 1 | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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); +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | 1 | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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); +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | 1 | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > -- "Fixed" query which drops the constant expression 'AND b.id = 1' in the ON for "base" table LEFT JOIN mysql- [localhost:5730] {msandbox} (test) > 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; +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | NULL | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > -- None of these produces the error mysql- [localhost:5730] {msandbox} (test) > 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; +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | NULL | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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; +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | NULL | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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; +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | NULL | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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; +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | NULL | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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); +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | NULL | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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); +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | NULL | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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); +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | NULL | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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); +----------+----+---------+-----------------+---------+----------+---------+------+ | t1 | id | base_id | t2 | base_id | child_id | t3 | id | +----------+----+---------+-----------------+---------+----------+---------+------+ | local -> | 1 | NULL | intersection -> | NULL | NULL | base -> | NULL | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.01 sec) mysql- [localhost:5730] {msandbox} (test) >