Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17493

Partition pruning doesn't work for nested outer joins

    Details

      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

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: