Pruning depends on current timestamp for partition by SYSTEM_TIME

--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;

# 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