create table ten(a int);

insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (a int, b int, c int);

insert into t1 select a,a,a from ten;

create table t2 (a int, b int, c int);

insert into t2 select a,a,a from ten;

create table tp (

  part_id int,

  a int

) partition by list (part_id) (

  partition p0 values in (0),

  partition p1 values in (1),

  partition p2 values in (2),

  partition p3 values in (3),

  partition p4 values in (4)

);

insert into tp select mod(a,5), a from ten;

 explain partitions  select * from t1 left join tp on (t1.a=tp.a and tp.part_id=3);

+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+

| id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |

+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+

|    1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                                 |

|    1 | SIMPLE      | tp    | p3         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using join buffer (flat, BNL join) |

+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+

mysql> explain partitions

    -> select * from t1 left join (tp join t2) on (t1.a=tp.a and tp.a=t2.b and tp.part_id=3);

+------+-------------+-------+----------------+------+---------------+------+---------+------+------+--------------------------------------------------------+

| id   | select_type | table | partitions     | type | possible_keys | key  | key_len | ref  | rows | Extra                                                  |

+------+-------------+-------+----------------+------+---------------+------+---------+------+------+--------------------------------------------------------+

|    1 | SIMPLE      | t1    | NULL           | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                                        |

|    1 | SIMPLE      | tp    | p0,p1,p2,p3,p4 | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (flat, BNL join)        |

|    1 | SIMPLE      | t2    | NULL           | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (incremental, BNL join) |

+------+-------------+-------+----------------+------+---------------+------+---------+------+------+--------------------------------------------------------+