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

History partition overflow leads to wrong SELECT result

    XMLWordPrintable

Details

    Description

      Reproduce

      -- source include/have_partition.inc
       
      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;
       
      insert into t1 values (0);
      update t1 set x= x + 1;
       
      set timestamp= unix_timestamp('2000-01-01 01:00:01');
      update t1 set x= x + 1;
       
      select *, row_start, row_end from t1 for system_time as of '2000-01-01 01:00:00';
      --replace_column 10 #
      explain partitions select * from t1 for system_time as of '2000-01-01 01:00:00';
       
      drop tables t1;
      

      Result

      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;
      insert into t1 values (0);
      update t1 set x= x + 1;
      set timestamp= unix_timestamp('2000-01-01 01:00:01');
      update t1 set x= x + 1;
      Warnings:
      Warning 4114    Versioned table `test`.`t1`: last HISTORY partition (`p0`) is out of INTERVAL, need more HISTORY partitions
      select *, row_start, row_end from t1 for system_time as of '2000-01-01 01:00:00';
      x       row_start       row_end
      explain partitions select * from t1 for system_time as of '2000-01-01 01:00:00';
      id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
      1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    #       Impossible WHERE noticed after reading const tables
      drop tables t1;
      

      Expected

      SELECT displays "1" record.
      Compare with result from 3 partitions:

      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 partitions 3;
      insert into t1 values (0);
      update t1 set x= x + 1;
      set timestamp= unix_timestamp('2000-01-01 01:00:01');
      update t1 set x= x + 1;
      select *, row_start, row_end from t1 for system_time as of '2000-01-01 01:00:00';
      x       row_start       row_end
      1       2000-01-01 00:00:00.000000      2000-01-01 01:00:01.000000
      explain partitions select * from t1 for system_time as of '2000-01-01 01:00:00';
      id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
      1       SIMPLE  t1      p1,pn   ALL     NULL    NULL    NULL    NULL    #       Using where
      drop tables t1;
      

      Proposed fix

      1. When overflow warning occurs touch file t1.no_pruning.
      2. When share open sees that file it sets no_pruning property to TABLE_SHARE.
      3. Pruning sees that property, selects all partitions and issues warning that pruning is off.
      4. After partitioning rebuild this file is removed.

      Attachments

        Issue Links

          Activity

            People

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