Unneeded pushdown condition in execution plan

[Igor Babaev]

If an equality predicate is used exclusively to access a joined table by index
it can be removed from the conditions pushed to this table. Generally
the optimizer performs this removal, but in some cases where equality propagation
is applied the optimizer is getting confused and retains unnecessary
conditions used for index look-ups.

Here's an example where the optimizer does not remove such conditions.

Create and populate tables t1,t2,t3 with the following commands:

CREATE TABLE t1 (a1 int NOT NULL, b1 char(10), INDEX idx (a1));
CREATE TABLE t2 (a2 int NOT NULL, b2 char(10), INDEX idx (a2));
CREATE TABLE t3 (a3 int NOT NULL, b3 char(10), INDEX idx (a3));
INSERT INTO t1 VALUES (2,'xxxxx'), (1,'xxx'), (11,'xxxxxxx');
INSERT INTO t2 VALUES
  (7,'yyyy'), (2,'yyyyyyy'), (3,'yyy'), (1,'yyyyyyyy'), (1,'yyyyy'),
  (3,'yy'), (1,'y'), (4,'yyy'), (7,'y'), (4,'yyyyy'), (7,'yyy'),
  (7,'yyyy'), (2,'yyyyyyy'), (3,'yyy'), (1,'yyyyyyyy'), (1,'yyyyy'),
  (3,'yy'), (1,'y'), (4,'yyy'), (7,'y'), (4,'yyyyy'), (7,'yyy');
INSERT INTO t3 VALUES
  (9,'zzzzzzz'), (2,'zzzzz'), (1,'z'), (9,'zz'), (1,'zz'), (5,'zzzzzzz'),
  (4,'zz'), (3,'z'), (5,'zzzzzz'), (3,'zz'), (4,'zzzz'), (3,'z'),
  (9,'zzzzzzzz'), (2,'zzzzzz'), (1,'zz'), (9,'zzz'), (1,'zzz'), (5,'zzzzzzzz'),
  (4,'zzz'), (3,'zz'), (5,'zzzzzzz'), (3,'zzz'), (4,'zzzzz'), (3,'zz'),
  (9,'zzzzzz'), (2,'zzzz'), (1,'zzz'), (9,'z'), (1,'z'), (5,'zzzzzz'),
  (4,'z'), (3,'zzz'), (5,'zzzzz'), (3,'z'), (4,'zzz'), (3,'zzzz'),
  (9,'zzzzz'), (2,'zzz'), (1,'zzzz'), (9,'zzz'), (1,'zzzz'), (5,'zzzzz'),
  (4,'zzz'), (3,'zzzz'), (5,'zzzz'), (3,'zzz'), (4,'zz'), (3,'zzzzz');

Turn index condition pushdown off:
SET SESSION optimizer_switch='index_condition_pushdown=off';

Now execute:
EXPLAIN SELECT * from t1,t2,t3 WHERE t3.a3=t1.a1 and t2.a2=t1.a1;

You'll get:

MariaDB [test]> EXPLAIN SELECT * from t1,t2,t3 WHERE t3.a3=t1.a1 AND t2.a2=t1.a1;
+----+-------------+-------+------+---------------+------+---------+------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------------+------+-------+
| 1 | SIMPLE | t1 | ALL | idx | NULL | NULL | NULL | 3 | |
| 1 | SIMPLE | t2 | ref | idx | idx | 4 | test.t1.a1 | 2 | |
| 1 | SIMPLE | t3 | ref | idx | idx | 4 | test.t1.a1 | 5 | |
+----+-------------+-------+------+---------------+------+---------+------------+------+-------+

This is an expected result.

Change the order of the conjuncts in the where condition and you'll get:

MariaDB [test]> EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t1.a1;
+----+-------------+-------+------+---------------+------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------------+------+-------------+
| 1 | SIMPLE | t1 | ALL | idx | NULL | NULL | NULL | 3 | |
| 1 | SIMPLE | t2 | ref | idx | idx | 4 | test.t1.a1 | 2 | |
| 1 | SIMPLE | t3 | ref | idx | idx | 4 | test.t2.a2 | 5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------------+------+-------------+

Now table t3 is accessed by the key value test.t2.a2, rather than by the key value
test.t1.a1 and some pushdown condition is checked after rows of t3 are fetched.
This is a slightly less efficient execution plan than the first one.

I also would expect the same execution plan as the first one for the following query:
EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t2.a2;

With the current code I have:

MariaDB [test]> EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t2.a2;
+----+-------------+-------+------+---------------+------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------------+------+-------------+
| 1 | SIMPLE | t1 | ALL | idx | NULL | NULL | NULL | 3 | |
| 1 | SIMPLE | t2 | ref | idx | idx | 4 | test.t1.a1 | 2 | |
| 1 | SIMPLE | t3 | ref | idx | idx | 4 | test.t2.a2 | 5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------------+------+-------------+

This defect can be observed in any current releases of MySQL (5.0,5.1,5.5) and of MariaDB.

I would suggest to fix it in MariaDB 5.3.