[MDEV-29738] REPLACE under system_versioning_insert_history allows to change existing historical records Created: 2022-10-07  Updated: 2022-12-27  Resolved: 2022-12-27

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: N/A
Fix Version/s: 10.11.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-29721 Inconsistency upon inserting history ... Closed
Problem/Incident
is caused by MDEV-16546 System versioning setting to allow hi... Closed
Relates

 Description   

Contrary to specification that system_versioning_insert_history doesn't do anything that timestamp manipulation couldn't do, it allows to change existing historical records, not just insert new arbitrary ones like timestamp manipulation does.

The possibility to change row_start is already known and indicated in the MTR test, although the comment there doesn't explain how it correlates with the specification. Anything similar to this does the trick:

create or replace table t (a int primary key) with system versioning;
 
insert into t values (1);
 
select a, row_start, row_end from t;
 
set system_versioning_insert_history= on;
 
replace into t (a,row_start,row_end) select a, '2023-01-01', row_end from t;
 
select a, row_start, row_end from t;

bb-10.11-MDEV-16546 2b1d3242

 
a	row_start	row_end
 
1	2022-10-09 00:58:24.584052	2038-01-19 05:14:07.999999
 
set system_versioning_insert_history= on;
 
replace into t (a,row_start,row_end) select a, '2023-01-01', row_end from t;
 
select a, row_start, row_end from t;
 
a	row_start	row_end
 
1	2023-01-01 00:00:00.000000	2038-01-19 05:14:07.999999

A similar approach works for ROW END in tables where the versioning fields are defined explicitly:

create table t (a int primary key, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning;
 
insert into t (a) values (1);
 
delete from t;
 
select * from t for system_time all;
 
set statement system_versioning_alter_history=keep for alter table t add unique (a,s);
 
set system_versioning_insert_history= on;
 
replace into t (a,s,e) select a, s, '2023-01-01' from t for system_time all;
 
select * from t for system_time all;


select * from t for system_time all;
 
a	s	e
 
1	2022-10-09 01:08:08.430845	2022-10-09 01:08:08.433748
 
set statement system_versioning_alter_history=keep for alter table t add unique (a,s);
 
set system_versioning_insert_history= on;
 
replace into t (a,s,e) select a, s, '2023-01-01' from t for system_time all;
 
select * from t for system_time all;
 
a	s	e
 
1	2022-10-09 01:08:08.430845	2023-01-01 00:00:00.000000



 Comments   
Comment by Elena Stepanova [ 2022-10-17 ]

Still can happen with the fix for MDEV-29721.

create table t (a int primary key) with system versioning;
 
set system_versioning_insert_history= on;
 
insert into t (a,row_start,row_end) values (1,'2023-01-01','2024-01-01');
 
replace into t (a,row_start,row_end) values (1,'2022-01-01','2024-01-01');
 
select a, row_start, row_end from t for system_time all;
 
 
 
drop table t;

bb-10.11-MDEV-16546 32090722

 
select a, row_start, row_end from t for system_time all;
 
a	row_start	row_end
 
1	2022-01-01 00:00:00.000000	2024-01-01 00:00:00.000000
 
drop table t;

That is, it happens when row start of the existing record is in the future, so row end cannot be set to "now".
Of course, it is a rather artificial use case and normally implies that the history had already been tampered with before.

Comment by Sergei Golubchik [ 2022-10-18 ]

REPLACE now ignores the value of system_versioning_insert_history, just like DELETE does.

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