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

History partition overflow leads to wrong SELECT result

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

          relates to

          Task - A task that needs to be done. MDEV-17554 Auto-create history partitions for system-versioned tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18501 Partition pruning doesn't work for historical queries

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25529 Auto-create: Pre-existing historical data is not partitioned as specified by ALTER

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29114 Pruning depends on current timestamp for partition by SYSTEM_TIME

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment -

            What about this approach: for the purpose of pruning always assume that the last history partition has no end time. That is, if the requested timestamp is after the last history partition — don't prune it anyway. All other partitions can be pruned normally.

            serg Sergei Golubchik added a comment - What about this approach: for the purpose of pruning always assume that the last history partition has no end time. That is, if the requested timestamp is after the last history partition — don't prune it anyway. All other partitions can be pruned normally.
            nikitamalyavin Nikita Malyavin added a comment -

            ok to push

            nikitamalyavin Nikita Malyavin added a comment - ok to push

            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.