[PROD-SER] [marcos.albe@bm-support01 sandboxes]$ dbdeployer deploy single /opt/mysql/5.7.29/ --sandbox-directory=mysql_5_7_29 --flavor mysql --flavor-in-prompt Database installed in $HOME/sandboxes/mysql_5_7_29 run 'dbdeployer usage single' for basic instructions' . sandbox server started [PROD-SER] [marcos.albe@bm-support01 sandboxes]$ cd mysql_5_7_29/ [PROD-SER] [marcos.albe@bm-support01 mysql_5_7_29]$ ./use test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 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) > CREATE TABLE local ( -> id bigint(20) NOT NULL AUTO_INCREMENT, -> base_id int(11), -> -> PRIMARY KEY (id), -> KEY local_m1 (base_id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > 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; Query OK, 0 rows affected (0.01 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=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql- [localhost:5730] {msandbox} (test) > mysql- [localhost:5730] {msandbox} (test) > INSERT INTO local VALUES (1, null); Query OK, 1 row affected (0.01 sec) mysql- [localhost:5730] {msandbox} (test) > INSERT INTO intersection VALUES (1, 1); Query OK, 1 row affected (0.01 sec) mysql- [localhost:5730] {msandbox} (test) > INSERT INTO base VALUES (1); Query OK, 1 row affected (0.00 sec) 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) What is that base.id 1 doing there?? 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 = 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 -> | 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 = 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 -> | 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 = 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 -> | 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 = 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 -> | 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 = 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 -> | NULL | +----------+----+---------+-----------------+---------+----------+---------+------+ 1 row in set (0.00 sec) -- ===================================================================================================== -- Working/"fixed" query where we removed "AND b.id = 1 " 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) > 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.01 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.00 sec)