Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Description
Consider this example
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; |
Let's put the partitioned table on the inner side of the outer join, and let the ON expression have a condition that's usable by partition pruning:
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) |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
tp.part_id=3 gets us tp.partitions=p3. Good so far.
Now, let's change the outer join to have two tables:
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) |
|
+------+-------------+-------+----------------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
now, tp.partitions includes all partitions even if the ON expression has a condition that restricts it to one.
Attachments
Issue Links
- relates to
-
MDEV-10946 Partition pruning not working with LEFT JOIN
- Stalled
-
MDEV-17518 Range optimization doesn't use ON expressions from nested outer joins
- Closed