[MDEV-29114] Pruning depends on current timestamp for partition by SYSTEM_TIME Created: 2022-07-16  Updated: 2023-11-28

Status: Stalled
Project: MariaDB Server
Component/s: Partitioning, Versioned Tables
Affects Version/s: 10.3, 10.9
Fix Version/s: 10.11

Type: Bug Priority: Major
Reporter: Aleksey Midenkov Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 0
Labels: partition-pruning

Issue Links:
Blocks
blocks MDEV-25529 Auto-create: Pre-existing historical ... Stalled
Relates
relates to MDEV-22247 History partition overflow leads to w... Closed
relates to MDEV-25390 Pruning boundary for history partitio... Closed

 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


Generated at Thu Feb 08 10:06:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.