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

Partition pruning doesn't work for historical queries

    XMLWordPrintable

Details

    Description

      Reproduce

      set timestamp= unix_timestamp('2001-01-01 00:00:00');
      create or replace table t1 (i int) with system versioning
      partition by system_time interval 1 day
          (partition p0 history, partition p1 history, partition p2 history, partition pn current);
      insert t1 values (0); delete from t1;
      set timestamp= unix_timestamp('2001-01-02 00:00:00');
      insert t1 values (1); delete from t1;
      set timestamp= unix_timestamp('2001-01-03 00:00:00');
      insert t1 values (2); delete from t1;
      select *, row_start, row_end from t1 partition (pn);
      select *, row_start, row_end from t1 partition (p0);
      select *, row_start, row_end from t1 partition (p1);
      select *, row_start, row_end from t1 partition (p2);
       
      explain partitions select * from t1 for system_time as of '2001-01-02 00:00:00';
      

      Result

      +------+-------------+-------+-------------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | p0,p1,p2,pn | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
      +------+-------------+-------+-------------+------+---------------+------+---------+------+------+-------------+
      

      Expected

      Since row_end >= '2001-01-02 00:00:00' partition p0 must be pruned from query.

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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