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

ALTER puts row into different partition for system_time interval partitioning

    XMLWordPrintable

Details

    Description

      Record with row_end on partition boundary is written into different partitions in CREATE and ALTER;

      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

      ALTER puts row 1 into p0:

      # 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;
      # 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
      drop table t1;
      

      Expected

      ALTER puts row 1 into p1 like CREATE does:

      --- /home/midenok/src/mariadb/10.6c/src/mysql-test/suite/bug/p/29114-2.result   2025-04-01 16:53:32.085377256 +0300
      +++ /home/midenok/src/mariadb/10.6c/src/mysql-test/suite/bug/p/29114-2.reject   2025-04-01 16:53:51.301095943 +0300
      @@ -45,9 +45,9 @@
       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
      +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;
       Explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
      

      Attachments

        Issue Links

          Activity

            People

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