Description:
Outer join elimination is not applied to queries with the IN preducate and the IF flow
control function the the cases where it can be apllied.
The query
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
WHERE t1.a IN (t2.a,t2.b)
has the following EXPLAIN outut:
mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.a IN (t2.a,t2.b);
+----+-------------+-------+--------+---------------+---------+---------+-----------+-----
-+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+-----
-+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9
| |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.a | 1
| Using where |
+----+-------------+-------+--------+---------------+---------+---------+-----------+-----
-+-------------+
An equivalent querY
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
WHERE t1.a=t2.a OR t1.a=t2.b;
has a different execution plan:
mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.a=t2.a OR t1.a=t2.b;
+----+-------------+-------+--------+---------------+---------+---------+-----------+-----
-+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+-----
-+-------------+
| 1 | SIMPLE | t2 | ALL | PRIMARY | NULL | NULL | NULL | 4
| Using where |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.a | 1
| |
+----+-------------+-------+--------+---------------+---------+---------+-----------+-----
-+-------------+
Yet accessing table t2 before table t1 is more efficient for both queries.
The fact is that a possible outer join elimination is not applied for the first query due
to a deficiency of the code that calculates the not_null_tables attribute for IN
predicates.
The same is true fir the IF function:
mysql> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.a >
IF(t1.a=t2.b-2,t2.b,t2.b-1);
+----+-------------+-------+--------+---------------+---------+---------+-----------+-----
-+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+-----
-+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9
| |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.a | 1
| Using where |
+----+-------------+-------+--------+---------------+---------+---------+-----------+-----
-+-------------+
How to repeat:
The following commands can demonstrate the problem:
CREATE TABLE t1 (a int PRIMARY KEY, b int);
INSERT INTO t1 VALUES
(1, 2), (2, 1), (3, 2), (4, 3), (5,6), (6,5), (7,8), (8,7), (9,10);
CREATE TABLE t2 (a int PRIMARY KEY, b int);
INSERT INTO t2 VALUES
(3,0), (4,1), (6,4), (7,5);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
WHERE t1.a IN (t2.a,t2.b);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
WHERE t1.a=t2.a OR t1.a=t2.b;
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
WHERE t1.a > IF(t1.a=t2.b-2,t2.b,t2.b-1);
Suggested fix:
Correct the calculation of the not_null_tables attribute for the IN and NOT IN predicates
and the IF functions.
