diff options
| -rw-r--r-- | mysql-test/suite/period/create_triggers.inc | 5 | ||||
| -rw-r--r-- | mysql-test/suite/period/engines.combinations | 6 | ||||
| -rw-r--r-- | mysql-test/suite/period/engines.inc | 3 | ||||
| -rw-r--r-- | mysql-test/suite/period/r/delete,myisam.rdiff | 18 | ||||
| -rw-r--r-- | mysql-test/suite/period/r/delete.result | 153 | ||||
| -rw-r--r-- | mysql-test/suite/period/r/update.result | 53 | ||||
| -rw-r--r-- | mysql-test/suite/period/r/versioning.result | 89 | ||||
| -rw-r--r-- | mysql-test/suite/period/t/delete.test | 49 | ||||
| -rw-r--r-- | mysql-test/suite/period/t/update.test | 22 | ||||
| -rw-r--r-- | mysql-test/suite/period/t/versioning.test | 59 |
10 files changed, 316 insertions, 141 deletions
diff --git a/mysql-test/suite/period/create_triggers.inc b/mysql-test/suite/period/create_triggers.inc index 7263c5de7a4..1126ae0845b 100644 --- a/mysql-test/suite/period/create_triggers.inc +++ b/mysql-test/suite/period/create_triggers.inc @@ -1,6 +1,4 @@ -disable_query_log; - -create or replace table log_tbl(id int auto_increment primary key, log text); +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); @@ -38,4 +36,3 @@ eval create trigger tr2ins_$trig_table after insert on $trig_table let trig_cols= 0; let trig_table= 0; -enable_query_log; diff --git a/mysql-test/suite/period/engines.combinations b/mysql-test/suite/period/engines.combinations new file mode 100644 index 00000000000..b740ec62a42 --- /dev/null +++ b/mysql-test/suite/period/engines.combinations @@ -0,0 +1,6 @@ +[innodb] +innodb +default-storage-engine=innodb + +[myisam] +default-storage-engine=myisam diff --git a/mysql-test/suite/period/engines.inc b/mysql-test/suite/period/engines.inc new file mode 100644 index 00000000000..9a52c7d0640 --- /dev/null +++ b/mysql-test/suite/period/engines.inc @@ -0,0 +1,3 @@ +# +# see engines.combinations +# diff --git a/mysql-test/suite/period/r/delete,myisam.rdiff b/mysql-test/suite/period/r/delete,myisam.rdiff new file mode 100644 index 00000000000..78fb972b0bc --- /dev/null +++ b/mysql-test/suite/period/r/delete,myisam.rdiff @@ -0,0 +1,18 @@ +--- suite/period/r/delete.result 2019-02-16 11:14:23.511258191 +0100 ++++ suite/period/r/delete.reject 2019-02-16 11:14:32.869258690 +0100 +@@ -250,7 +250,6 @@ + ERROR 22003: Out of range value for column 'id' at row 1 + select * from t; + id s e +-127 1999-01-01 2018-12-12 + # same for trigger case + create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam; + create or replace procedure log(s text) +@@ -277,7 +276,6 @@ + ERROR 22003: Out of range value for column 'id' at row 1 + select * from t; + id s e +-127 1999-01-01 2018-12-12 + select * from log_tbl order by id; + id log + 1 >DEL: 1999-01-01, 2018-12-12 diff --git a/mysql-test/suite/period/r/delete.result b/mysql-test/suite/period/r/delete.result index f34a89ae2f0..c5333dcf39a 100644 --- a/mysql-test/suite/period/r/delete.result +++ b/mysql-test/suite/period/r/delete.result @@ -12,6 +12,21 @@ create or replace table t2 (id int, s date, e date, period for apptime(s,e)); insert t2 select * from t; create or replace table t3 (id int, s date, e date, period for apptime(s,e)); insert t3 select * from t; +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_t1 before update on t1 +for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr2upd_t1 after update on t1 +for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr1del_t1 before delete on t1 +for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e)); +create trigger tr2del_t1 after delete on t1 +for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e)); +create trigger tr1ins_t1 before insert on t1 +for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr2ins_t1 after insert on t1 +for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e)); delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; delete from t1 for portion of APPTIME from '2000-01-01' to '2018-01-01'; select * from t; @@ -67,6 +82,21 @@ id log 29 >DEL: 5, 2010-01-01, 2015-01-01 30 <DEL: 5, 2010-01-01, 2015-01-01 # INSERT trigger only also works +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_t2 before update on t2 +for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr2upd_t2 after update on t2 +for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr1del_t2 before delete on t2 +for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e)); +create trigger tr2del_t2 after delete on t2 +for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e)); +create trigger tr1ins_t2 before insert on t2 +for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr2ins_t2 after insert on t2 +for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e)); drop trigger tr1del_t2; drop trigger tr2del_t2; delete from t2 for portion of APPTIME from '2000-01-01' to '2018-01-01'; @@ -91,6 +121,21 @@ id log # removing BEFORE INSERT trigger enables internal substitution # DELETE+INSERT -> UPDATE, but without any side effects. # The optimization is disabled for non-transactional engines +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_t3 before update on t3 +for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e)); +create trigger tr2upd_t3 after update on t3 +for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e)); +create trigger tr1del_t3 before delete on t3 +for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e)); +create trigger tr2del_t3 after delete on t3 +for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e)); +create trigger tr1ins_t3 before insert on t3 +for each row call log(CONCAT('>INS: ', new.s, ", ", new.e)); +create trigger tr2ins_t3 after insert on t3 +for each row call log(CONCAT('<INS: ', new.s, ", ", new.e)); drop trigger tr1ins_t3; delete from t3 for portion of APPTIME from '2000-01-01' to '2018-01-01'; select * from log_tbl order by id; @@ -167,6 +212,21 @@ id s e truncate t; # same for trigger case insert into t values (default, '1999-01-01', '2018-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)); delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; select * from t; id s e @@ -196,6 +256,21 @@ s e xs xe truncate t; # same for trigger case insert into t values('1999-01-01', '2018-12-12', default, default); +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)); delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; select * from t; s e xs xe @@ -219,11 +294,39 @@ s date, e date, period for apptime(s,e)); insert into t values(127, '1999-01-01', '2018-12-12'); delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; ERROR 22003: Out of range value for column 'id' at row 1 +select * from t; +id s e +127 1999-01-01 2018-12-12 # same for trigger case +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)); # negotiate side effects of non-transactional MyISAM engine replace into t values(127, '1999-01-01', '2018-12-12'); +select * from t; +id s e +127 1999-01-01 2018-12-12 +truncate table log_tbl; delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; ERROR 22003: Out of range value for column 'id' at row 1 +select * from t; +id s e +127 1999-01-01 2018-12-12 +select * from log_tbl order by id; +id log +1 >DEL: 1999-01-01, 2018-12-12 # custom constraint for period fields create or replace table t(id int, s date, e date, period for apptime(s,e), constraint dist2days check (datediff(e, s) >= 2)); @@ -246,54 +349,4 @@ id s e datediff(e, s) 1 1999-01-01 1999-01-03 2 1 2018-12-10 2018-12-12 2 2 1999-01-01 1999-01-03 2 -# system_time columns are updated -create or replace 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'); -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 create or replace database test; diff --git a/mysql-test/suite/period/r/update.result b/mysql-test/suite/period/r/update.result index 2ad5f420b41..6b6e8de3081 100644 --- a/mysql-test/suite/period/r/update.result +++ b/mysql-test/suite/period/r/update.result @@ -30,6 +30,21 @@ id s e 8 2000-01-01 2018-01-01 9 2000-01-01 2015-01-01 # Check triggers +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_t1 before update on t1 +for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr2upd_t1 after update on t1 +for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr1del_t1 before delete on t1 +for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e)); +create trigger tr2del_t1 after delete on t1 +for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e)); +create trigger tr1ins_t1 before insert on t1 +for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr2ins_t1 after insert on t1 +for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e)); update t1 for portion of apptime from '2000-01-01' to '2018-01-01' set id=id + 6; select * from t1; @@ -82,6 +97,21 @@ id log 29 >UPD: 5, 2010-01-01, 2015-01-01 -> 11, 2010-01-01, 2015-01-01 30 <UPD: 5, 2010-01-01, 2015-01-01 -> 11, 2010-01-01, 2015-01-01 # INSERT trigger only also works +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_t2 before update on t2 +for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr2upd_t2 after update on t2 +for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr1del_t2 before delete on t2 +for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e)); +create trigger tr2del_t2 after delete on t2 +for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e)); +create trigger tr1ins_t2 before insert on t2 +for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e)); +create trigger tr2ins_t2 after insert on t2 +for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e)); drop trigger tr1upd_t2; drop trigger tr2upd_t2; update t2 for portion of apptime from '2000-01-01' to '2018-01-01' @@ -225,27 +255,4 @@ x s e xs xe xs=s and xe=e 1 1999-01-01 2000-01-01 1999-01-01 2000-01-01 1 1 2018-01-01 2018-12-12 2018-01-01 2018-12-12 1 6 2000-01-01 2018-01-01 2000-01-01 2018-01-01 1 -# system_time columns are updated -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 create or replace database test; diff --git a/mysql-test/suite/period/r/versioning.result b/mysql-test/suite/period/r/versioning.result new file mode 100644 index 00000000000..5494e9cbc99 --- /dev/null +++ b/mysql-test/suite/period/r/versioning.result @@ -0,0 +1,89 @@ +# DELETE +create or replace 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 <DEL: 1999-01-01, 2018-12-12 +3 >INS: 1999-01-01, 2000-01-01 +4 <INS: 1999-01-01, 2000-01-01 +5 >INS: 2018-01-01, 2018-12-12 +6 <INS: 2018-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 +create or replace database test; diff --git a/mysql-test/suite/period/t/delete.test b/mysql-test/suite/period/t/delete.test index 689b06608fc..e4fc17decf2 100644 --- a/mysql-test/suite/period/t/delete.test +++ b/mysql-test/suite/period/t/delete.test @@ -1,5 +1,4 @@ -source suite/versioning/engines.inc; -source suite/versioning/common.inc; +source suite/period/engines.inc; create or replace table t (id int, s date, e date, period for apptime(s,e)); @@ -141,18 +140,20 @@ insert into t values(127, '1999-01-01', '2018-12-12'); --error HA_ERR_AUTOINC_ERANGE delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; -#select * from t; +select * from t; --echo # same for trigger case --let $trig_table=t --source suite/period/create_triggers.inc --echo # negotiate side effects of non-transactional MyISAM engine replace into t values(127, '1999-01-01', '2018-12-12'); +select * from t; +truncate table log_tbl; --error HA_ERR_AUTOINC_ERANGE delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; -#select * from t; -#select * from log_tbl order by id; +select * from t; +select * from log_tbl order by id; --echo # custom constraint for period fields create or replace table t(id int, s date, e date, period for apptime(s,e), @@ -175,42 +176,4 @@ delete from t for portion of apptime from '1999-01-03' to '2018-12-10'; --sorted_result select *, datediff(e, s) from t; - ---echo # system_time columns are updated ---replace_result $sys_datatype_expl SYS_TYPE -eval create or replace table t ( - s date, e date, - row_start $sys_datatype_expl as row start invisible, - row_end $sys_datatype_expl 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; - -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; - ---echo # 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'); ---let $trig_table=t ---source suite/period/create_triggers.inc - -select row_start into @ins_time from t limit 1; -select * from t order by s, e; - -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; -select * from log_tbl order by id; - - create or replace database test; diff --git a/mysql-test/suite/period/t/update.test b/mysql-test/suite/period/t/update.test index e50060791a7..c2616cfe4a8 100644 --- a/mysql-test/suite/period/t/update.test +++ b/mysql-test/suite/period/t/update.test @@ -1,5 +1,4 @@ -source suite/versioning/engines.inc; -source suite/versioning/common.inc; +source suite/period/engines.inc; create or replace table t (id int, s date, e date, period for apptime(s,e)); @@ -141,23 +140,4 @@ update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5; --sorted_result select *, xs=s and xe=e from t; ---echo # system_time columns are updated ---replace_result $sys_datatype_expl SYS_TYPE -eval create or replace table t (x int, s date, e date, - row_start $sys_datatype_expl as row start invisible, - row_end $sys_datatype_expl 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; ---sorted_result -select * from t; - -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; - create or replace database test; diff --git a/mysql-test/suite/period/t/versioning.test b/mysql-test/suite/period/t/versioning.test new file mode 100644 index 00000000000..298f49efe13 --- /dev/null +++ b/mysql-test/suite/period/t/versioning.test @@ -0,0 +1,59 @@ +source suite/versioning/engines.inc; +source suite/versioning/common.inc; + +--echo # DELETE +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t ( + s date, e date, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl 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; + +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; + +--echo # 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'); +--let $trig_table=t +--source suite/period/create_triggers.inc + +select row_start into @ins_time from t limit 1; +select * from t order by s, e; + +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; +select * from log_tbl order by id; + +--echo # UPDATE +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t (x int, s date, e date, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl 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; +--sorted_result +select * from t; + +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; + +create or replace database test; |