diff options
author | Nikita Malyavin <nikitamalyavin@gmail.com> | 2020-01-21 19:48:47 +1000 |
---|---|---|
committer | Nikita Malyavin <nikitamalyavin@gmail.com> | 2020-01-21 23:33:56 +1000 |
commit | 0b348fc494ce0e2c47abf69cd666eecfec0f4901 (patch) | |
tree | ded9c7abb203167999b4b13d60188d0b6bb50731 | |
parent | 7975dd1f47af1458eeb4363add3301b98516e143 (diff) | |
download | mariadb-git-nm/period-fk-linear.tar.gz |
Period: add versioning combinationnm/period-fk-linear
-rw-r--r-- | mysql-test/suite/period/r/delete.result | 11 | ||||
-rw-r--r-- | mysql-test/suite/period/r/overlaps.result | 9 | ||||
-rw-r--r-- | mysql-test/suite/period/r/update.result | 3 | ||||
-rw-r--r-- | mysql-test/suite/period/t/delete.test | 18 | ||||
-rw-r--r-- | mysql-test/suite/period/t/fk.test | 4 | ||||
-rw-r--r-- | mysql-test/suite/period/t/overlaps.test | 20 | ||||
-rw-r--r-- | mysql-test/suite/period/t/update.test | 10 | ||||
-rw-r--r-- | mysql-test/suite/period/versioning.combinations | 3 | ||||
-rw-r--r-- | mysql-test/suite/period/versioning.inc | 10 | ||||
-rw-r--r-- | mysql-test/suite/period/versioning_finish.inc | 8 |
10 files changed, 63 insertions, 33 deletions
diff --git a/mysql-test/suite/period/r/delete.result b/mysql-test/suite/period/r/delete.result index 451017e5340..8f8c137f2d2 100644 --- a/mysql-test/suite/period/r/delete.result +++ b/mysql-test/suite/period/r/delete.result @@ -209,7 +209,8 @@ select * from t; id s e 2 1999-01-01 2000-01-01 3 2018-01-01 2018-12-12 -truncate t; +create or replace table t (id int primary key auto_increment, s date, e date, +period for apptime(s, e)); # 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; @@ -253,7 +254,7 @@ select * from t; s e xs xe 1999-01-01 2000-01-01 1999-01-01 2000-01-01 2018-01-01 2018-12-12 2018-01-01 2018-12-12 -truncate t; +delete from 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; @@ -322,7 +323,7 @@ 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; +create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam; 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; @@ -339,12 +340,12 @@ insert into t values(1, '1999-01-01', '2018-12-12'), delete from t for portion of apptime from '1999-01-02' to '2018-12-12'; ERROR 23000: CONSTRAINT `dist2days` failed for `test`.`t` # negotiate side effects of non-transactional MyISAM engine -truncate t; +delete from t; insert into t values(1, '1999-01-01', '2018-12-12'), (2, '1999-01-01', '1999-12-12'); delete from t for portion of apptime from '1999-01-01' to '2018-12-11'; ERROR 23000: CONSTRAINT `dist2days` failed for `test`.`t` -truncate t; +delete from t; insert into t values(1, '1999-01-01', '2018-12-12'), (2, '1999-01-01', '1999-12-12'); delete from t for portion of apptime from '1999-01-03' to '2018-12-10'; diff --git a/mysql-test/suite/period/r/overlaps.result b/mysql-test/suite/period/r/overlaps.result index 5a7f62cd269..ce03a59e5e2 100644 --- a/mysql-test/suite/period/r/overlaps.result +++ b/mysql-test/suite/period/r/overlaps.result @@ -116,13 +116,10 @@ insert into t values (1, '2003-01-01', '2003-03-01'), (1, '2003-02-01', '2003-04-01'); alter table t add primary key(id, p without overlaps); ERROR 23000: Duplicate entry '1-2003-02-01-2003-04-01' for key 'PRIMARY' -# Historical rows are not checked against constraints -set @@system_versioning_alter_history= keep; -alter table t add system versioning; delete from t; -alter table t add primary key(id, p without overlaps); insert into t values (1, '2003-01-01', '2003-03-01'), (1, '2003-03-01', '2003-05-01'); +alter table t add primary key(id, p without overlaps); # `without overlaps` is not lost on alter table alter table t add y int; show create table t; @@ -134,7 +131,7 @@ t CREATE TABLE `t` ( `y` int(11) DEFAULT NULL, PERIOD FOR `p` (`s`, `e`), PRIMARY KEY (`id`,`p` WITHOUT OVERLAPS) -) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 alter table t drop y; create or replace table t1 like t; show create table t1; @@ -145,7 +142,7 @@ t1 CREATE TABLE `t1` ( `e` date NOT NULL, PERIOD FOR `p` (`s`, `e`), PRIMARY KEY (`id`,`p` WITHOUT OVERLAPS) -) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 create or replace table t1 (x int, s date, e date, period for p(s,e), primary key(x, p without overlaps) diff --git a/mysql-test/suite/period/r/update.result b/mysql-test/suite/period/r/update.result index b86537fc9fb..56453a290d6 100644 --- a/mysql-test/suite/period/r/update.result +++ b/mysql-test/suite/period/r/update.result @@ -241,7 +241,8 @@ id x s e 1 6 2000-01-01 2018-01-01 2 1 1999-01-01 2000-01-01 3 1 2018-01-01 2018-12-12 -truncate t; +create or replace table t (id int primary key auto_increment, x int, +s date, e date, period for apptime(s, e)); insert into t values (default, 1, '1999-01-01', '2018-12-12'); update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= 1; select * from t; diff --git a/mysql-test/suite/period/t/delete.test b/mysql-test/suite/period/t/delete.test index 738d77d2c19..9724d3c7ede 100644 --- a/mysql-test/suite/period/t/delete.test +++ b/mysql-test/suite/period/t/delete.test @@ -1,5 +1,6 @@ -source suite/period/engines.inc; -source include/have_log_bin.inc; +--source suite/period/engines.inc +--source include/have_log_bin.inc +--source suite/period/versioning.inc create table t (id int, s date, e date, period for apptime(s,e)); @@ -95,7 +96,8 @@ select * from t; delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; --sorted_result select * from t; -truncate t; +create or replace table t (id int primary key auto_increment, s date, e date, + period for apptime(s, e)); --echo # same for trigger case insert into t values (default, '1999-01-01', '2018-12-12'); --let $trig_table=t @@ -116,7 +118,7 @@ select * from t; delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; --sorted_result select * from t; -truncate t; +delete from t; --echo # same for trigger case insert into t values('1999-01-01', '2018-12-12', default, default); --let $trig_table=t @@ -153,7 +155,7 @@ select * from t; --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; +create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam; --error HA_ERR_AUTOINC_ERANGE delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; @@ -168,12 +170,12 @@ insert into t values(1, '1999-01-01', '2018-12-12'), --error ER_CONSTRAINT_FAILED delete from t for portion of apptime from '1999-01-02' to '2018-12-12'; --echo # negotiate side effects of non-transactional MyISAM engine -truncate t; +delete from t; insert into t values(1, '1999-01-01', '2018-12-12'), (2, '1999-01-01', '1999-12-12'); --error ER_CONSTRAINT_FAILED delete from t for portion of apptime from '1999-01-01' to '2018-12-11'; -truncate t; +delete from t; insert into t values(1, '1999-01-01', '2018-12-12'), (2, '1999-01-01', '1999-12-12'); @@ -198,3 +200,5 @@ drop procedure sp; drop table t,t2,t3,log_tbl; drop view v; drop procedure log; + +--source suite/period/versioning_finish.inc diff --git a/mysql-test/suite/period/t/fk.test b/mysql-test/suite/period/t/fk.test index af0f9dfbbf4..321eb2c343d 100644 --- a/mysql-test/suite/period/t/fk.test +++ b/mysql-test/suite/period/t/fk.test @@ -1,4 +1,6 @@ --source include/have_innodb.inc +--source suite/period/versioning.inc + set default_storage_engine= innodb; create or replace table t (id int, x int, s date, e date, period for p(s,e), @@ -9,6 +11,7 @@ create or replace table s (id int, x int, s date, e date, period for fp(s,e), references t(id, x, period p) on delete restrict); +--replace_result " WITH SYSTEM VERSIONING" "" show create table s; flush tables; @@ -143,6 +146,7 @@ insert into t values(1, 1, '2017-01-03', '2017-01-20'); delete from t; insert into t values(1, 1, '2017-01-03', '2017-01-20'); +--replace_result " WITH SYSTEM VERSIONING" "" show create table s; select * from t; insert into s values (-1, 1, '0', 1, '2017-01-15', '2017-01-05'); diff --git a/mysql-test/suite/period/t/overlaps.test b/mysql-test/suite/period/t/overlaps.test index 6046f5570e8..002c8398452 100644 --- a/mysql-test/suite/period/t/overlaps.test +++ b/mysql-test/suite/period/t/overlaps.test @@ -2,6 +2,7 @@ # Test both myisam and innodb --source suite/period/engines.inc +--source suite/period/versioning.inc let $default_engine= `select @@default_storage_engine`; @@ -9,7 +10,7 @@ create or replace table t(id int, s date, e date, period for p(s,e), primary key(id, p without overlaps)); ---replace_result $default_engine DEFAULT_ENGINE +--replace_result $default_engine DEFAULT_ENGINE " WITH SYSTEM VERSIONING" "" show create table t; @@ -89,7 +90,7 @@ create or replace table t(id int, u int, s date, e date, period for p(s,e), primary key(id, p without overlaps), unique(u)); ---replace_result $default_engine DEFAULT_ENGINE +--replace_result $default_engine DEFAULT_ENGINE " WITH SYSTEM VERSIONING" "" show create table t; insert into t values (1, 1, '2003-03-01', '2003-05-01'); insert into t values (1, 2, '2003-05-01', '2003-07-01'); @@ -100,7 +101,7 @@ create or replace table t(id int, u int, s date, e date, period for p(s,e), primary key(id, p without overlaps), unique(u, p without overlaps)); ---replace_result $default_engine DEFAULT_ENGINE +--replace_result $default_engine DEFAULT_ENGINE " WITH SYSTEM VERSIONING" "" show create table t; insert into t values (1, 1, '2003-03-01', '2003-05-01'); insert into t values (1, 2, '2003-05-01', '2003-07-01'); @@ -117,24 +118,19 @@ insert into t values (1, '2003-01-01', '2003-03-01'), --error ER_DUP_ENTRY alter table t add primary key(id, p without overlaps); ---echo # Historical rows are not checked against constraints -set @@system_versioning_alter_history= keep; -alter table t add system versioning; delete from t; -alter table t add primary key(id, p without overlaps); - insert into t values (1, '2003-01-01', '2003-03-01'), (1, '2003-03-01', '2003-05-01'); - +alter table t add primary key(id, p without overlaps); --echo # `without overlaps` is not lost on alter table alter table t add y int; ---replace_result $default_engine DEFAULT_ENGINE +--replace_result $default_engine DEFAULT_ENGINE " WITH SYSTEM VERSIONING" "" show create table t; alter table t drop y; create or replace table t1 like t; ---replace_result $default_engine DEFAULT_ENGINE +--replace_result $default_engine DEFAULT_ENGINE " WITH SYSTEM VERSIONING" "" show create table t1; --error ER_PERIOD_WITHOUT_OVERLAPS_PARTITIONED @@ -160,3 +156,5 @@ create or replace table t2 (x int, s date, e date, key(x, p without overlaps)); create or replace database test; + +--source suite/period/versioning_finish.inc diff --git a/mysql-test/suite/period/t/update.test b/mysql-test/suite/period/t/update.test index 5730387dfda..5c5fe3e1622 100644 --- a/mysql-test/suite/period/t/update.test +++ b/mysql-test/suite/period/t/update.test @@ -1,5 +1,6 @@ -source suite/period/engines.inc; -source include/have_log_bin.inc; +--source suite/period/engines.inc +--source include/have_log_bin.inc +--source suite/period/versioning.inc create table t (id int, s date, e date, period for apptime(s,e)); @@ -134,7 +135,8 @@ update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5; --sorted_result select * from t; -truncate t; +create or replace table t (id int primary key auto_increment, x int, + s date, e date, period for apptime(s, e)); insert into t values (default, 1, '1999-01-01', '2018-12-12'); update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= 1; --sorted_result @@ -163,3 +165,5 @@ drop function f; drop function g; drop function h; drop procedure log; + +--source suite/period/versioning_finish.inc diff --git a/mysql-test/suite/period/versioning.combinations b/mysql-test/suite/period/versioning.combinations new file mode 100644 index 00000000000..ba8bbbd10f7 --- /dev/null +++ b/mysql-test/suite/period/versioning.combinations @@ -0,0 +1,3 @@ +[plain] + +[vers] diff --git a/mysql-test/suite/period/versioning.inc b/mysql-test/suite/period/versioning.inc new file mode 100644 index 00000000000..10948631504 --- /dev/null +++ b/mysql-test/suite/period/versioning.inc @@ -0,0 +1,10 @@ +--source include/have_debug.inc +--disable_query_log +if ($MTR_COMBINATION_VERS) +{ + set @mtr_vers_saved_dbug= @debug_dbug; + set @mtr_vers_saved_system_versioning_alter_history= @@system_versioning_alter_history; + set debug_dbug= "d,sysvers_force"; + set @@system_versioning_alter_history= keep; +} +--enable_query_log diff --git a/mysql-test/suite/period/versioning_finish.inc b/mysql-test/suite/period/versioning_finish.inc new file mode 100644 index 00000000000..ae96e70595c --- /dev/null +++ b/mysql-test/suite/period/versioning_finish.inc @@ -0,0 +1,8 @@ +--source include/have_debug.inc +--disable_query_log +if ($MTR_COMBINATION_VERS) +{ + set debug_dbug= @mtr_vers_saved_dbug; + set @@system_versioning_alter_history= @mtr_vers_saved_system_versioning_alter_history; +} +--enable_query_log |