Period: add versioning combinationbb-10.6-MDEV-16983-period-fk

10 files changed, 66 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 042c68d4f54..856c5c753a2 100644
--- a/mysql-test/suite/period/r/overlaps.result
+++ b/mysql-test/suite/period/r/overlaps.result
@@ -137,13 +137,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-04-01-2003-02-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;
@@ -155,7 +152,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;
@@ -166,7 +163,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 6efae2fb923..a46ed83d82c 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;
@@ -149,6 +152,7 @@ create or replace table s (x int, y int, z char(200), pk int,
 delete from t;
 insert into t values(1, 1, '2017-01-03', '2017-01-20');
 
+--replace_result " WITH SYSTEM VERSIONING" ""
 show create table s;
 --sorted_result
 select * from t;
@@ -166,15 +170,18 @@ call mtr.add_suppression("In ALTER TABLE .* has or is referenced in foreign key
 --error ER_ERROR_ON_RENAME
 alter table t drop period for p, drop constraint id;
 
+--replace_result " WITH SYSTEM VERSIONING" ""
 show create table s;
 alter table t drop period for p, drop constraint id, add period for pop(s, e),
               add unique(id, x, pop without overlaps);
+--replace_result " WITH SYSTEM VERSIONING" ""
 show create table s;
 
 --replace_regex  /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/
 --error ER_ERROR_ON_RENAME
 alter table t drop period for pop, drop constraint id,
               add unique(id, x, s, e);
+--replace_result " WITH SYSTEM VERSIONING" ""
 show create table s;
 
 --error ER_PERIOD_NOT_FOUND
diff --git a/mysql-test/suite/period/t/overlaps.test b/mysql-test/suite/period/t/overlaps.test
index fdd277350f2..94b4f6e9c4d 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`;
 
@@ -10,7 +11,7 @@ create or replace table t(id int, s date, e date,
                           primary key(id, p without overlaps)
 ) partition by key (id);
 
---replace_result $default_engine DEFAULT_ENGINE
+--replace_result $default_engine DEFAULT_ENGINE " WITH SYSTEM VERSIONING" ""
 show create table t;
 
 
@@ -92,7 +93,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');
@@ -105,7 +106,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 (2, NULL, '2003-03-01', '2003-05-01');
@@ -134,24 +135,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;
 
 create or replace table t1 (x int, s date, e date,
@@ -202,3 +198,5 @@ insert into t values (1, '2020-03-05', '2020-03-10', 'test2');
 insert into t values (1, '2020-03-03', '2020-03-10', 'test3');
 
 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