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

Partition pruning not working with LEFT JOIN

    Details

    • Sprint:
      10.2.3-2, 10.2.4-4, 10.2.4-1

      Description

      Partition pruning does not happen in case of LEFT JOIN in the following simple case:

      drop table a, b, c;
      create table a (
      a_id bigint auto_increment not null,
      d date,
      b_id bigint,
      c_id bigint,
      primary key (a_id, d),
      key(b_id, d),
      key(c_id, d)
      )
      /*!50500 PARTITION BY RANGE COLUMNS(d)
      (PARTITION p160709 VALUES LESS THAN ('2016-07-10') ENGINE = InnoDB,
      PARTITION p160710 VALUES LESS THAN ('2016-07-11') ENGINE = InnoDB,
      PARTITION p160711 VALUES LESS THAN ('2016-07-12') ENGINE = InnoDB,
      PARTITION p160712 VALUES LESS THAN ('2016-07-13') ENGINE = InnoDB,
      PARTITION p9999 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
       
      create table b (
      b_id bigint auto_increment not null,
      d date,
      primary key (b_id, d))
      /*!50500 PARTITION BY RANGE COLUMNS(d)
      (PARTITION p160709 VALUES LESS THAN ('2016-07-10') ENGINE = InnoDB,
      PARTITION p160710 VALUES LESS THAN ('2016-07-11') ENGINE = InnoDB,
      PARTITION p160711 VALUES LESS THAN ('2016-07-12') ENGINE = InnoDB,
      PARTITION p160712 VALUES LESS THAN ('2016-07-13') ENGINE = InnoDB,
      PARTITION p9999 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
       
      create table c (
      c_id bigint auto_increment not null,
      d date,
      primary key (c_id, d))
      /*!50500 PARTITION BY RANGE COLUMNS(d)
      (PARTITION p160709 VALUES LESS THAN ('2016-07-10') ENGINE = InnoDB,
      PARTITION p160710 VALUES LESS THAN ('2016-07-11') ENGINE = InnoDB,
      PARTITION p160711 VALUES LESS THAN ('2016-07-12') ENGINE = InnoDB,
      PARTITION p160712 VALUES LESS THAN ('2016-07-13') ENGINE = InnoDB,
      PARTITION p9999 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
       
      insert into c(d) values ( date_add('2016-07-13', INTERVAL -floor(rand()*5) DAY));
      insert into c(d) values ( date_add('2016-07-13', INTERVAL -floor(rand()*5) DAY));
      replace into c(d) select date_add('2016-07-13', INTERVAL -floor(rand()*5) DAY) from c t1, c t2, c t3, c t4, c t5, c t6, c t7, c t8, c t9, c t10, c t11, c t12, c t13, c t14, c t15, c t16, c t17, c t18;
       
      insert into b(d) values ( date_add('2016-07-13', INTERVAL -floor(rand()*5) DAY));
      insert into b(d) values ( date_add('2016-07-13', INTERVAL -floor(rand()*5) DAY));
      replace into b(d) select date_add('2016-07-13', INTERVAL -floor(rand()*5) DAY) from b t1, b t2, b t3, b t4, b t5, b t6, b t7, b t8, b t9, b t10, b t11, b t12, b t13, b t14, b t15, b t16, b t17, b t18;
       
      insert into a(d, b_id, c_id) select b.d, b_id, c_id from b LEFT JOIN c on b.d = c.d and mod(c_id, 3) = 0 limit 1000000;
      

      Now with these tables and data we have the following for INNER JOIN:

      MariaDB [test]> select version();
      +-----------------+
      | version()       |
      +-----------------+
      | 10.1.18-MariaDB |
      +-----------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> explain partitions select a.a_id , a.b_id, c.c_id from a JOIN b on b.b_id = a.b_id and a.d = b.d INNER JOIN c on a.c_id = c.c_id and a.d = c.d where a.d BETWEEN '2016-07-11' AND '2016-07-11' LIMIT 10;
      +------+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+--------------------------+
      | id   | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | Extra                    |
      +------+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+--------------------------+
      |    1 | SIMPLE      | a     | p160711    | ALL    | b_id,c_id     | NULL    | NULL    | NULL              |    2 | Using where              |
      |    1 | SIMPLE      | b     | p160711    | eq_ref | PRIMARY       | PRIMARY | 11      | test.a.b_id,const |    1 | Using where; Using index |
      |    1 | SIMPLE      | c     | p160711    | eq_ref | PRIMARY       | PRIMARY | 11      | test.a.c_id,const |    1 | Using where; Using index |
      +------+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+--------------------------+
      3 rows in set (0.00 sec)
      

      But if we use LEFT JOIN to c:

      MariaDB [test]> explain partitions select a.a_id , a.b_id, c.c_id from a JOIN b on b.b_id = a.b_id and a.d = b.d LEFT JOIN c on a.c_id = c.c_id and a.d = c.d where a.d BETWEEN '2016-07-11' AND '2016-07-11' LIMIT 10;
      +------+-------------+-------+---------------------------------------+--------+---------------+---------+---------+----------------------+------+--------------------------+
      | id   | select_type | table | partitions                            | type   | possible_keys | key     | key_len | ref                  | rows | Extra                    |
      +------+-------------+-------+---------------------------------------+--------+---------------+---------+---------+----------------------+------+--------------------------+
      |    1 | SIMPLE      | a     | p160711                               | ALL    | b_id          | NULL    | NULL    | NULL                 |    2 | Using where              |
      |    1 | SIMPLE      | b     | p160711                               | eq_ref | PRIMARY       | PRIMARY | 11      | test.a.b_id,const    |    1 | Using where; Using index |
      |    1 | SIMPLE      | c     | p160709,p160710,p160711,p160712,p9999 | eq_ref | PRIMARY       | PRIMARY | 11      | test.a.c_id,test.a.d |    1 | Using where; Using index |
      +------+-------------+-------+---------------------------------------+--------+---------------+---------+---------+----------------------+------+--------------------------+
      3 rows in set (0.00 sec)
      

      So, pruning do not happen while accessing c table. There is an easy workaround - just add the condition to ON clause in any of the following ways:

      MariaDB [test]> explain partitions select a.a_id , a.b_id, c.c_id from a JOIN b on b.b_id = a.b_id and a.d = b.d LEFT JOIN c on a.c_id = c.c_id and a.d = c.d and c.d BETWEEN '2016-07-11' AND '2016-07-11' where a.d BETWEEN '2016-07-11' AND '2016-07-11' LIMIT 10;
      +------+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+--------------------------+
      | id   | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | Extra                    |
      +------+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+--------------------------+
      |    1 | SIMPLE      | a     | p160711    | ALL    | b_id          | NULL    | NULL    | NULL              |    2 | Using where              |
      |    1 | SIMPLE      | b     | p160711    | eq_ref | PRIMARY       | PRIMARY | 11      | test.a.b_id,const |    1 | Using where; Using index |
      |    1 | SIMPLE      | c     | p160711    | eq_ref | PRIMARY       | PRIMARY | 11      | test.a.c_id,const |    1 | Using where; Using index |
      +------+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+--------------------------+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain partitions select a.a_id , a.b_id, c.c_id from a JOIN b on b.b_id = a.b_id and a.d = b.d LEFT JOIN c on a.c_id = c.c_id and a.d = c.d and a.d BETWEEN '2016-07-11' AND '2016-07-11' where a.d BETWEEN '2016-07-11' AND '2016-07-11' LIMIT 10;
      +------+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+--------------------------+
      | id   | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | Extra                    |
      +------+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+--------------------------+
      |    1 | SIMPLE      | a     | p160711    | ALL    | b_id          | NULL    | NULL    | NULL              |    2 | Using where              |
      |    1 | SIMPLE      | b     | p160711    | eq_ref | PRIMARY       | PRIMARY | 11      | test.a.b_id,const |    1 | Using where; Using index |
      |    1 | SIMPLE      | c     | p160711    | eq_ref | PRIMARY       | PRIMARY | 11      | test.a.c_id,const |    1 | Using where; Using index |
      +------+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+--------------------------+
      3 rows in set (0.00 sec)
      

      There are reasons to think that upstream MySQL (5.7.15) is also affected.

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              valerii Valerii Kravchuk
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: