ALTER puts row into different partition for system_time interval partitioning

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

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