Description:
If the outer table in an outer join operation of a query contains only 1 row and the on condition for this operation refers only to this table and is evaluated to false
for the row in the table then the query might return a wrong result.
E.g. if tables t1 and t2 is specified by the commands
CREATE TABLE t1(int a)
CREATE TABLE t2(int b)
and table t1 contain row (1), while ttable t2 contains two rows (1) and (2),
then we have:
mysql> SELECT * FROM t1 LEFT JOIN t2 ON a=0;
+----+------+
| a | b |
+----+------+
| 1 | 1 |
| 1 | 2 |
+----+------+
The expected result is:
mysql> SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
+----+------+
| a1 | a2 |
+----+------+
| 1 | NULL |
+----+------+
The same is true if the selection for the outer table is guaranteed to return only 1 row.
How to repeat:
Create the and populate tables t0,t1,t2,t3 by the following commands:
CREATE TABLE t0 (a0 int PRIMARY KEY);
CREATE TABLE t1 (a1 int PRIMARY KEY);
CREATE TABLE t2 (a2 int);
CREATE TABLE t3 (a3 int);
INSERT INTO t0 VALUES (1);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);
The run the commands:
SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
Suggested fix:
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
INSERT INTO t0 VALUES (0);
INSERT INTO t1 VALUES (0);
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
You'll have the following results:
mysql> SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
+----+------+
| a1 | a2 |
+----+------+
| 1 | 1 |
| 1 | 2 |
+----+------+
2 rows in set (7.40 sec)
mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
+----+------+------+
| a1 | a2 | a3 |
+----+------+------+
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 1 | 2 | 2 |
+----+------+------+
4 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
mysql> SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
+----+----+------+------+
| a0 | a1 | a2 | a3 |
+----+----+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 2 |
| 1 | 1 | 2 | 1 |
| 1 | 1 | 2 | 2 |
+----+----+------+------+
4 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t0 | system | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t1 | system | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
+----+----+------+------+
| a0 | a1 | a2 | a3 |
+----+----+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 2 |
| 1 | 1 | 2 | 1 |
| 1 | 1 | 2 | 2 |
+----+----+------+------+
4 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t0 | system | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t1 | system | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
4 rows in set (0.00 sec)
mysql> INSERT INTO t0 VALUES (0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (0);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
+----+----+------+------+
| a0 | a1 | a2 | a3 |
+----+----+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 2 |
| 1 | 1 | 2 | 1 |
| 1 | 1 | 2 | 2 |
+----+----+------+------+
4 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | t0 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
4 rows in set (0.00 sec)
All select statements above will return incorrect result sets.
