Debian Codegraph @

mysql-test/suite/period/r/versioning.result

raw
1 # DELETE
2 create table t (
3 s date, e date,
4 row_start SYS_TYPE as row start invisible,
5 row_end SYS_TYPE as row end invisible,
6 period for apptime(s, e),
7 period for system_time (row_start, row_end)) with system versioning;
8 insert into t values('1999-01-01', '2018-12-12'),
9 ('1999-01-01', '1999-12-12');
10 select row_start into @ins_time from t limit 1;
11 select * from t order by s, e;
12 s e
13 1999-01-01 1999-12-12
14 1999-01-01 2018-12-12
15 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
16 select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
17 from t for system_time all
18 order by s, e, row_start;
19 s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end)
20 1999-01-01 1999-12-12 OLD CURRENT ROW
21 1999-01-01 2000-01-01 NEW CURRENT ROW
22 1999-01-01 2018-12-12 OLD HISTORICAL ROW
23 2018-01-01 2018-12-12 NEW CURRENT ROW
24 # same for trigger case
25 delete from t;
26 delete history from t;
27 insert into t values('1999-01-01', '2018-12-12'),
28 ('1999-01-01', '1999-12-12');
29 create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
30 create or replace procedure log(s text)
31 insert into log_tbl(log) values(s);
32 create trigger tr1upd_t before update on t
33 for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
34 create trigger tr2upd_t after update on t
35 for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
36 create trigger tr1del_t before delete on t
37 for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
38 create trigger tr2del_t after delete on t
39 for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
40 create trigger tr1ins_t before insert on t
41 for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
42 create trigger tr2ins_t after insert on t
43 for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
44 select row_start into @ins_time from t limit 1;
45 select * from t order by s, e;
46 s e
47 1999-01-01 1999-12-12
48 1999-01-01 2018-12-12
49 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
50 select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
51 from t for system_time all
52 order by s, e, row_start;
53 s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end)
54 1999-01-01 1999-12-12 OLD CURRENT ROW
55 1999-01-01 2000-01-01 NEW CURRENT ROW
56 1999-01-01 2018-12-12 OLD HISTORICAL ROW
57 2018-01-01 2018-12-12 NEW CURRENT ROW
58 select * from log_tbl order by id;
59 id log
60 1 >DEL: 1999-01-01, 2018-12-12
61 2 >INS: 1999-01-01, 2000-01-01
62 3 <INS: 1999-01-01, 2000-01-01
63 4 >INS: 2018-01-01, 2018-12-12
64 5 <INS: 2018-01-01, 2018-12-12
65 6 <DEL: 1999-01-01, 2018-12-12
66 # UPDATE
67 create or replace table t (x int, s date, e date,
68 row_start SYS_TYPE as row start invisible,
69 row_end SYS_TYPE as row end invisible,
70 period for apptime(s, e),
71 period for system_time(row_start, row_end)) with system versioning;
72 insert into t values(1, '1999-01-01', '2018-12-12'),
73 (2, '1999-01-01', '1999-12-12');
74 select row_start into @ins_time from t limit 1;
75 select * from t;
76 x s e
77 1 1999-01-01 2018-12-12
78 2 1999-01-01 1999-12-12
79 update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
80 select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
81 from t for system_time all
82 order by x, s, e, row_start;
83 x s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end)
84 1 1999-01-01 2000-01-01 NEW CURRENT ROW
85 1 1999-01-01 2018-12-12 OLD HISTORICAL ROW
86 1 2018-01-01 2018-12-12 NEW CURRENT ROW
87 2 1999-01-01 1999-12-12 OLD CURRENT ROW
88 6 2000-01-01 2018-01-01 NEW CURRENT ROW
89 drop table t,log_tbl;
90 drop procedure log;