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

Pruning depends on current timestamp for partition by SYSTEM_TIME

    XMLWordPrintable

    Details

      Description

      There are 2 problems observed:

      1. Record with row_end on partition boundary is written into different partitions in CREATE and ALTER;
      2. Partition pruning depends on current timestamp. Thus we may miss boundary record in historical query if we did partitioning in ALTER command.

      Reproduce

      --source include/have_partition.inc
      --echo # CREATE case
      set timestamp= unix_timestamp('2000-01-01 00:00:00');
      create or replace table t1 (x int) with system versioning
      partition by system_time interval 1 hour (
        partition p0 history, partition p1 history, partition pn current);
      set timestamp= unix_timestamp('2000-01-01 00:00:00');
      insert t1 values (0);
      set timestamp= unix_timestamp('2000-01-01 00:10:00');
      update t1 set x= 1;
      set timestamp= unix_timestamp('2000-01-01 01:00:00');
      update t1 set x= 2;
      set timestamp= unix_timestamp('2000-01-01 01:30:00');
      update t1 set x= 3;
       
      --echo # CREATE result: row 1 got into p1
      select *, row_start, row_end from t1 partition (p0);
      select *, row_start, row_end from t1 partition (p1);
      flush tables;
      --echo # For CREATE pruning is affected by current timestamp, but SELECT works
      --echo # in any case since row 1 got into p1
      set timestamp= unix_timestamp('2000-01-01 00:00:00');
      explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
      select * from t1 for system_time as of '2000-01-01 00:59:59';
      set timestamp= unix_timestamp('2020-01-01 00:00:00');
      explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
      select * from t1 for system_time as of '2000-01-01 00:59:59';
       
      --echo # ALTER case
      set timestamp= unix_timestamp('2000-01-01 00:00:00');
      alter table t1
      partition by system_time interval 1 hour (
        partition p0 history, partition p1 history, partition pn current);
       
      --echo # ALTER result: row 1 got into p0
      Select *, row_start, row_end from t1 partition (p0);
      Select *, row_start, row_end from t1 partition (p1);
      flush tables;
      Explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
      Select * from t1 for system_time as of '2000-01-01 00:59:59';
       
      --echo # Wrong pruning and wrong SELECT: p0 was pruned out
      flush tables;
      set timestamp= unix_timestamp('2020-01-01 00:00:00');
      EXPLAIN partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
      SELECT * from t1 for system_time as of '2000-01-01 00:59:59';
       
      drop table t1;
      

      Result

      # CREATE result: row 1 got into p1
      select *, row_start, row_end from t1 partition (p0);
      x       row_start       row_end
      0       2000-01-01 00:00:00.000000      2000-01-01 00:10:00.000000
      select *, row_start, row_end from t1 partition (p1);
      x       row_start       row_end
      1       2000-01-01 00:10:00.000000      2000-01-01 01:00:00.000000
      2       2000-01-01 01:00:00.000000      2000-01-01 01:30:00.000000
      flush tables;
      # For CREATE pruning is affected by current timestamp, but SELECT works
      # in any case since row 1 got into p1
      set timestamp= unix_timestamp('2000-01-01 00:00:00');
      explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
      id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
      1       SIMPLE  t1      p0,p1,pn        ALL     NULL    NULL    NULL    NULL    4       Using where
      select * from t1 for system_time as of '2000-01-01 00:59:59';
      x
      1
      set timestamp= unix_timestamp('2020-01-01 00:00:00');
      explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
      id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
      1       SIMPLE  t1      p0,p1,pn        ALL     NULL    NULL    NULL    NULL    4       Using where
      select * from t1 for system_time as of '2000-01-01 00:59:59';
      x
      1
      # ALTER case
      set timestamp= unix_timestamp('2000-01-01 00:00:00');
      alter table t1
      partition by system_time interval 1 hour (
      partition p0 history, partition p1 history, partition pn current);
      # ALTER result: row 1 got into p0
      Select *, row_start, row_end from t1 partition (p0);
      x       row_start       row_end
      0       2000-01-01 00:00:00.000000      2000-01-01 00:10:00.000000
      1       2000-01-01 00:10:00.000000      2000-01-01 01:00:00.000000
      Select *, row_start, row_end from t1 partition (p1);
      x       row_start       row_end
      2       2000-01-01 01:00:00.000000      2000-01-01 01:30:00.000000
      flush tables;
      Explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
      id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
      1       SIMPLE  t1      p0,p1,pn        ALL     NULL    NULL    NULL    NULL    4       Using where
      Select * from t1 for system_time as of '2000-01-01 00:59:59';
      x
      1
      # Wrong pruning and wrong SELECT: p0 was pruned out
      flush tables;
      set timestamp= unix_timestamp('2020-01-01 00:00:00');
      EXPLAIN partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
      id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
      1       SIMPLE  t1      p1,pn   ALL     NULL    NULL    NULL    NULL    2       Using where
      SELECT * from t1 for system_time as of '2000-01-01 00:59:59';
      x
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.