set @@session.time_zone='+00:00';
select ifnull(max(trx_id), 0) into @start_trx_id from information_schema.innodb_vtq;
create procedure if not exists verify_vtq()
begin
set @i= 0;
select
@i:= @i + 1 as No,
trx_id > 0 as A,
begin_ts > '1-1-1 0:0:0' as B,
commit_ts > begin_ts as C,
concurr_trx is null as D
from information_schema.innodb_vtq
where trx_id > @start_trx_id;
select ifnull(max(trx_id), 0)
into @start_trx_id
from information_schema.innodb_vtq;
end~~
create procedure test_01(
sys_type varchar(255),
engine varchar(255),
fields varchar(255))
begin
set @str= concat('
create table t1(
x int unsigned,
y int unsigned,
sys_start ', sys_type, ' generated always as row start,
sys_end ', sys_type, ' generated always as row end,
period for system_time (sys_start, sys_end))
with system versioning
engine ', engine);
prepare stmt from @str; execute stmt; drop prepare stmt;
insert into t1 (x, y) values
(0, 100),
(1, 101),
(2, 102),
(3, 103),
(4, 104),
(5, 105),
(6, 106),
(7, 107),
(8, 108),
(9, 109);
set @t0= now(6);
delete from t1 where x = 3;
delete from t1 where x > 7;
insert into t1(x, y) values(3, 33);
set @str= concat('select ', fields, ' from t1 where x = 3 and y = 33 into @t1');
prepare stmt from @str; execute stmt; drop prepare stmt;
select x, y from t1;
select x as AS_OF_x, y from t1 for system_time as of timestamp @t0;
select x as FROM_TO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
select x as BETWEEN_AND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
drop table t1;
end~~
create or replace procedure test_02(
sys_type varchar(255),
engine varchar(255),
fields varchar(255))
begin
set @str0= concat('(
x int,
y int,
sys_start ', sys_type, ' generated always as row start,
sys_end ', sys_type, ' generated always as row end,
period for system_time (sys_start, sys_end))
with system versioning
engine ', engine);
set @str= concat('create or replace table t1', @str0);
prepare stmt from @str; execute stmt; drop prepare stmt;
set @str= concat('create or replace table t2', @str0);
prepare stmt from @str; execute stmt; drop prepare stmt;
insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5);
insert into t2 values (1, 2), (2, 1), (3, 1);
set @t0= now(6);
select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x;
select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x;
select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x;
delete from t1;
delete from t2;
select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x
for system_time as of timestamp @t0;
select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x
for system_time as of timestamp @t0;
select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x
for system_time as of timestamp @t0;
drop table t1;
drop table t2;
end~~
call test_01('timestamp(6)', 'myisam', 'sys_start');
x y
0 100
1 101
2 102
4 104
5 105
6 106
7 107
3 33
AS_OF_x y
0 100
1 101
2 102
3 103
4 104
5 105
6 106
7 107
8 108
9 109
FROM_TO_x y
0 100
1 101
2 102
3 103
4 104
5 105
6 106
7 107
8 108
9 109
BETWEEN_AND_x y
0 100
1 101
2 102
3 103
4 104
5 105
6 106
7 107
8 108
9 109
3 33
call test_01('bigint unsigned', 'innodb', 'commit_ts(sys_start)');
x y
0 100
1 101
2 102
4 104
5 105
6 106
7 107
3 33
AS_OF_x y
0 100
1 101
2 102
3 103
4 104
5 105
6 106
7 107
8 108
9 109
FROM_TO_x y
0 100
1 101
2 102
3 103
4 104
5 105
6 106
7 107
8 108
9 109
BETWEEN_AND_x y
0 100
1 101
2 102
3 103
4 104
5 105
6 106
7 107
8 108
9 109
3 33
call test_02('timestamp(6)', 'myisam', 'sys_start');
IJ1_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
LJ1_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
4 4 NULL NULL
5 5 NULL NULL
RJ1_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
NULL NULL 2 1
NULL NULL 3 1
IJ2_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
LJ2_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
4 4 NULL NULL
5 5 NULL NULL
RJ2_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
NULL NULL 2 1
NULL NULL 3 1
call test_02('bigint unsigned', 'innodb', 'commit_ts(sys_start)');
IJ1_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
LJ1_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
4 4 NULL NULL
5 5 NULL NULL
RJ1_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
NULL NULL 2 1
NULL NULL 3 1
IJ2_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
LJ2_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
4 4 NULL NULL
5 5 NULL NULL
RJ2_x1 y1 x2 y2
1 1 1 2
1 2 1 2
1 3 1 2
NULL NULL 2 1
NULL NULL 3 1
create table t1(
A int
) with system versioning engine=myisam;
insert into t1 values(1);
select * from t1;
A
1
create or replace table t1(
A int
) with system versioning engine=innodb;
insert into t1 values(1);
select * from t1;
A
1
drop table t1;
call verify_vtq;
No A B C D
1 1 1 1 1
2 1 1 1 1
3 1 1 1 1
4 1 1 1 1
5 1 1 1 1
6 1 1 1 1
7 1 1 1 1
8 1 1 1 1
9 1 1 1 1
drop procedure test_01;
drop procedure test_02;
drop procedure verify_vtq;