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

Partition pruning not working with LEFT JOIN

Details

    • 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

        Issue Links

          Activity

            Transition Time In Source Status Execution Times
            Valerii Kravchuk made transition -
            Open Confirmed
            46s 1
            Sergei Petrunia made transition -
            Confirmed In Progress
            749d 20h 22m 1
            Julien Fritsch made transition -
            In Progress Stalled
            1869d 9h 21m 1

            People

              psergei Sergei Petrunia
              valerii Valerii Kravchuk
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.