diff options
Diffstat (limited to 'mysql-test/suite/period/r/versioning.result')
| -rw-r--r-- | mysql-test/suite/period/r/versioning.result | 94 |
1 files changed, 94 insertions, 0 deletions
diff --git a/mysql-test/suite/period/r/versioning.result b/mysql-test/suite/period/r/versioning.result new file mode 100644 index 00000000000..efb7a646e87 --- /dev/null +++ b/mysql-test/suite/period/r/versioning.result @@ -0,0 +1,94 @@ +# DELETE +create table t ( +s date, e date, +row_start SYS_TYPE as row start invisible, +row_end SYS_TYPE as row end invisible, +period for apptime(s, e), +period for system_time (row_start, row_end)) with system versioning; +insert into t values('1999-01-01', '2018-12-12'), +('1999-01-01', '1999-12-12'); +select row_start into @ins_time from t limit 1; +select * from t order by s, e; +s e +1999-01-01 1999-12-12 +1999-01-01 2018-12-12 +delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; +select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end) +from t for system_time all +order by s, e, row_start; +s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end) +1999-01-01 1999-12-12 OLD CURRENT ROW +1999-01-01 2000-01-01 NEW CURRENT ROW +1999-01-01 2018-12-12 OLD HISTORICAL ROW +2018-01-01 2018-12-12 NEW CURRENT ROW +# same for trigger case +delete from t; +delete history from t; +insert into t values('1999-01-01', '2018-12-12'), +('1999-01-01', '1999-12-12'); +create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam; +create or replace procedure log(s text) +insert into log_tbl(log) values(s); +create trigger tr1upd_t before update on t +for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e)); +create trigger tr2upd_t after update on t +for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e)); +create trigger tr1del_t before delete on t +for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e)); +create trigger tr2del_t after delete on t +for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e)); +create trigger tr1ins_t before insert on t +for each row call log(CONCAT('>INS: ', new.s, ", ", new.e)); +create trigger tr2ins_t after insert on t +for each row call log(CONCAT('<INS: ', new.s, ", ", new.e)); +select row_start into @ins_time from t limit 1; +select * from t order by s, e; +s e +1999-01-01 1999-12-12 +1999-01-01 2018-12-12 +delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; +select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end) +from t for system_time all +order by s, e, row_start; +s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end) +1999-01-01 1999-12-12 OLD CURRENT ROW +1999-01-01 2000-01-01 NEW CURRENT ROW +1999-01-01 2018-12-12 OLD HISTORICAL ROW +2018-01-01 2018-12-12 NEW CURRENT ROW +select * from log_tbl order by id; +id log +1 >DEL: 1999-01-01, 2018-12-12 +2 >INS: 1999-01-01, 2000-01-01 +3 <INS: 1999-01-01, 2000-01-01 +4 >INS: 2018-01-01, 2018-12-12 +5 <INS: 2018-01-01, 2018-12-12 +6 <DEL: 1999-01-01, 2018-12-12 +# UPDATE +create or replace table t (x int, s date, e date, +row_start SYS_TYPE as row start invisible, +row_end SYS_TYPE as row end invisible, +period for apptime(s, e), +period for system_time(row_start, row_end)) with system versioning; +insert into t values(1, '1999-01-01', '2018-12-12'), +(2, '1999-01-01', '1999-12-12'); +select row_start into @ins_time from t limit 1; +select * from t; +x s e +1 1999-01-01 2018-12-12 +2 1999-01-01 1999-12-12 +update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5; +select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end) +from t for system_time all +order by x, s, e, row_start; +x s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end) +1 1999-01-01 2000-01-01 NEW CURRENT ROW +1 1999-01-01 2018-12-12 OLD HISTORICAL ROW +1 2018-01-01 2018-12-12 NEW CURRENT ROW +2 1999-01-01 1999-12-12 OLD CURRENT ROW +6 2000-01-01 2018-01-01 NEW CURRENT ROW +drop table t,log_tbl; +drop function check_row; +drop function current_row; +drop procedure verify_trt; +drop procedure verify_trt_dummy; +drop procedure log; |