create or replace table t1( x int unsigned, y int unsigned, sys_start SYS_DATATYPE as row start invisible, sys_end SYS_DATATYPE as row end invisible, period for system_time (sys_start, sys_end)) with system versioning; insert into t1(x, y) values(3, 4); insert into t1(x, y) values(2, 3); insert into t1 values(40, 33); select x, y, sys_end < MAXVAL from t1; x y sys_end < MAXVAL 3 4 0 2 3 0 40 33 0 create or replace table t1( id int unsigned auto_increment primary key, x int unsigned, y int unsigned, sys_start SYS_DATATYPE as row start invisible, sys_end SYS_DATATYPE as row end invisible, period for system_time (sys_start, sys_end)) with system versioning; insert into t1(x, y) values(33, 44); insert into t1(id, x, y) values(20, 33, 44); insert into t1 values(40, 33, 44); select id, x, y, sys_end < MAXVAL from t1; id x y sys_end < MAXVAL 1 33 44 0 20 33 44 0 40 33 44 0 create or replace table t1( x int unsigned, y int unsigned, sys_start SYS_DATATYPE as row start invisible, sys_end SYS_DATATYPE as row end invisible, period for system_time (sys_start, sys_end)) with system versioning; create view vt1_1 as select x, y from t1; insert into t1(x, y) values(8001, 9001); insert into vt1_1(x, y) values(1001, 2001); insert into vt1_1 values(1002, 2002); select x, y, sys_end < MAXVAL from t1; x y sys_end < MAXVAL 8001 9001 0 1001 2001 0 1002 2002 0 select x, y from vt1_1; x y 8001 9001 1001 2001 1002 2002 drop view vt1_1; create or replace table t1( id bigint primary key, a int, b int) with system versioning; insert into t1 values(1, 1, 1); select row_start, row_end from t1 into @sys_start, @sys_end; select id, a, b from t1; id a b 1 1 1 insert into t1 values(2, 2, 2); select id, a, b, row_start > @sys_start as C, row_end = @sys_end as D from t1 where id = 2; id a b C D 2 2 2 1 1 drop table t1; create or replace table t1( x int unsigned, y int unsigned, sys_start SYS_DATATYPE as row start invisible, sys_end SYS_DATATYPE as row end invisible, period for system_time (sys_start, sys_end)) with system versioning; create or replace table t2 like t1; insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000); delete from t1 where x >= 1; insert into t1(x, y) values (1, 1001), (2, 2001), (3, 3001), (4, 4001), (5, 5001), (6, 6001); insert into t1(x, y, sys_start) values (7, 7001, DEFAULT); insert into t1(x, y, sys_end) values (8, 8001, DEFAULT); insert into t1(x, y, sys_start, sys_end) values (9, 9001, DEFAULT, DEFAULT); insert into t2 select x, y from t1 for system_time all; select x, y from t1; x y 1 1001 2 2001 3 3001 4 4001 5 5001 6 6001 7 7001 8 8001 9 9001 select x, y from t2; x y 1 1000 2 2000 3 3000 4 4000 5 5000 6 6000 7 7000 8 8000 9 9000 1 1001 2 2001 3 3001 4 4001 5 5001 6 6001 7 7001 8 8001 9 9001 drop table t1; drop table t2; # # MDEV-16546 System versioning setting to allow history modification # set @@session.time_zone='+00:00'; create table t1(x int primary key) with system versioning; create table t2(y int primary key, row_start timestamp(6) as row start invisible, row_end timestamp(6) as row end invisible, period for system_time (row_start, row_end)) with system versioning; create table t3(z int primary key, row_start timestamp(6) as row start, row_end timestamp(6) as row end, period for system_time (row_start, row_end)) with system versioning; insert into t1(x, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); ERROR 42S22: Unknown column 'row_start' in 'field list' insert into t2(y, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored set @@system_versioning_insert_history= 1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `x` int(11) NOT NULL, PRIMARY KEY (`x`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING insert into t1(x, row_start, row_end) values (3, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); insert into t2(y, row_start, row_end) values (4, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); insert into t3 values (5, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); insert into t3 values (5, '1980-01-02 00:00:00', '1980-01-01 00:00:01'); ERROR HY000: Incorrect row_start value: '1980-01-02 00:00:00.000000' select x, row_start, row_end from t1 for system_time all; x row_start row_end 3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000 select y, row_start, row_end from t2 for system_time all; y row_start row_end 4 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000 select z, row_start, row_end from t3 for system_time all; z row_start row_end 5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000 insert into t1(x) values (1); insert into t2(y) values (1); update t1 set x= x + 1; update t1 set row_start= '1971-01-01 00:00:00'; ERROR 42S22: Unknown column 'row_start' in 'field list' update t2 set row_start= '1971-01-01 00:00:00'; ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored insert t1 (x) values (2) on duplicate key update x= 3, row_end= '1970-01-01 00:00:00'; ERROR 42S22: Unknown column 'row_end' in 'field list' insert t2 (y) values (1) on duplicate key update y= 3, row_end= '1970-01-01 00:00:00'; ERROR HY000: The value specified for generated column 'row_end' in table 't2' has been ignored insert t2 (y,row_end) values (1, '1970-01-01 00:00:00') on duplicate key update y= 3; ERROR HY000: The value specified for generated column 'row_end' in table 't2' has been ignored insert into t1 values (4); insert into t1 set x= 5, row_start= '1980-01-01 00:00:00', row_end= '1980-01-01 00:00:01'; insert into t1(x, row_start, row_end) values (6, '1980-01-01 00:00:01', '1980-01-01 00:00:00'); ERROR HY000: Incorrect row_start value: '1980-01-01 00:00:01.000000' insert into t1(x, row_start, row_end) values (7, '1980-01-01 00:00:11', '1980-01-01 00:00:11'); ERROR HY000: Incorrect row_start value: '1980-01-01 00:00:11.000000' insert into t1(x, row_start) values (8, '1980-01-01 00:00:22'); insert into t1(x, row_end) values (9, '1980-01-01 00:00:33'); ERROR HY000: Incorrect row_start value: 'now' insert into t1(x, row_end) values (10, TIMESTAMP'2038-01-19 03:14:07.999999'); select x, check_row_ts(row_start, row_end) from t1 for system_time all order by x; x check_row_ts(row_start, row_end) 1 HISTORICAL ROW 2 CURRENT ROW 3 HISTORICAL ROW 4 CURRENT ROW 5 HISTORICAL ROW 8 CURRENT ROW 10 CURRENT ROW select x, row_start, row_end from t1 for system_time all where x > 1 and row_end < TIMESTAMP'2038-01-19 03:14:07.999999' order by x, row_start, row_end; x row_start row_end 3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000 5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000 # Direct insert is not possible for TRX_ID versioning create or replace table t2(y int primary key, row_start bigint unsigned as row start, row_end bigint unsigned as row end, period for system_time (row_start, row_end)) with system versioning engine innodb; insert into t2(y, row_start, row_end) values (0, 1, 2); ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored set @@system_versioning_insert_history= 0; ## INSERT..SELECT create or replace table t2 like t1; set @@system_versioning_insert_history= 1; insert into t2 (x, row_start, row_end) select x, row_start, row_end from t1 for system_time all; select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x; x check_row_ts(row_start, row_end) 1 HISTORICAL ROW 2 CURRENT ROW 3 HISTORICAL ROW 4 CURRENT ROW 5 HISTORICAL ROW 8 CURRENT ROW 10 CURRENT ROW select x, row_start, row_end from t2 for system_time all where x > 1 and row_end < TIMESTAMP'2038-01-19 03:14:07.999999' order by x, row_start, row_end; x row_start row_end 3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000 5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000 set @@system_versioning_insert_history= 0; # REPLACE / REPLACE .. SELECT create or replace table t2(a int primary key, row_start timestamp(6) as row start invisible, row_end timestamp(6) as row end invisible, period for system_time (row_start, row_end)) with system versioning; replace into t2 (a, row_start, row_end) values (1, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored replace into t2 (a, row_start, row_end) select x, row_start, row_end from t1; ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored create or replace table t2 (a int primary key) with system versioning; replace into t2 (a, row_start, row_end) values (1, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); ERROR 42S22: Unknown column 'row_start' in 'field list' replace into t2 (a, row_start, row_end) select x, row_start, row_end from t1; ERROR 42S22: Unknown column 'row_start' in 'field list' set @@system_versioning_insert_history= 1; # REPLACE ignores system_versioning_insert_history replace into t2 (a, row_end) values (0, '1980-01-01 00:00:00'); ERROR 42S22: Unknown column 'row_end' in 'field list' replace into t3 (z, row_start) values (0, '1980-01-01 00:00:00'); ERROR HY000: The value specified for generated column 'row_start' in table 't3' has been ignored replace into t3 values (0, '1980-01-01 00:00:00', '1981-01-01 00:00:00'); ERROR HY000: The value specified for generated column 'row_start' in table 't3' has been ignored # LOAD DATA select x, row_start, row_end into outfile 'DATAFILE' from t1 for system_time all; create or replace table t2 like t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `x` int(11) NOT NULL, PRIMARY KEY (`x`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING set @@system_versioning_insert_history= 1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `x` int(11) NOT NULL, PRIMARY KEY (`x`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING load data infile 'DATAFILE' into table t2 (x, row_start, row_end); select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x; x check_row_ts(row_start, row_end) 1 HISTORICAL ROW 2 CURRENT ROW 3 HISTORICAL ROW 4 CURRENT ROW 5 HISTORICAL ROW 8 CURRENT ROW 10 CURRENT ROW select row_start = '1980-01-01 00:00:00', row_end = '1980-01-01 00:00:01' from t2 for system_time all where x = 3; row_start = '1980-01-01 00:00:00' row_end = '1980-01-01 00:00:01' 1 1 # Honor secure_timestamp option # restart: --secure-timestamp=YES set @@system_versioning_insert_history= 1; insert into t3(z, row_start, row_end) values (8, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); ERROR HY000: The MariaDB server is running with the --secure-timestamp=YES option so it cannot execute this statement insert into t3 values (8, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); ERROR HY000: The MariaDB server is running with the --secure-timestamp=YES option so it cannot execute this statement # restart: --secure-timestamp=REPLICATION create user nobody; grant all privileges on test.* to nobody; set @@system_versioning_insert_history= 1; insert into test.t3(z, row_start, row_end) values (9, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); ERROR 42000: Access denied; you need (at least one of) the BINLOG REPLAY privilege(s) for this operation insert into test.t3 values (9, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); ERROR 42000: Access denied; you need (at least one of) the BINLOG REPLAY privilege(s) for this operation # restart: --secure-timestamp=SUPER set @@system_versioning_insert_history= 1; insert into test.t3(z, row_start, row_end) values (10, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); set @@system_versioning_insert_history= 1; insert into test.t3(z, row_start, row_end) values (7, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG REPLAY privilege(s) for this operation insert into test.t3 values (7, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG REPLAY privilege(s) for this operation use test; # restart: --secure-timestamp=NO drop tables t1, t2, t3; # # MDEV-29813 REPLACE/IGNORE does not work with historical records in InnoDB # set sql_mode='STRICT_ALL_TABLES'; create or replace table t1 (a int) with system versioning; set system_versioning_insert_history= on; insert into t1 (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01'); select a,row_start,row_end into outfile 'mdev29813.txt' from t1 for system_time all; create or replace table t1 (a int primary key) with system versioning; load data infile 'mdev29813.txt' ignore into table t1 (a,row_start,row_end); Warnings: Warning 1062 Duplicate entry '1' for key 'PRIMARY' select a,row_start,row_end from t1 for system_time all; a row_start row_end 1 2022-01-01 00:00:00.000000 2023-01-01 00:00:00.000000 create or replace table t1 (a int primary key) with system versioning; insert ignore into t1 (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01'); Warnings: Warning 1062 Duplicate entry '1' for key 'PRIMARY' select a,row_start,row_end from t1 for system_time all; a row_start row_end 1 2022-01-01 00:00:00.000000 2023-01-01 00:00:00.000000 drop table t1; set sql_mode=default;