summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/suite/period/r/delete.result11
-rw-r--r--mysql-test/suite/period/r/overlaps.result9
-rw-r--r--mysql-test/suite/period/r/update.result3
-rw-r--r--mysql-test/suite/period/t/delete.test18
-rw-r--r--mysql-test/suite/period/t/fk.test7
-rw-r--r--mysql-test/suite/period/t/overlaps.test20
-rw-r--r--mysql-test/suite/period/t/update.test10
-rw-r--r--mysql-test/suite/period/versioning.combinations3
-rw-r--r--mysql-test/suite/period/versioning.inc10
-rw-r--r--mysql-test/suite/period/versioning_finish.inc8
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 cf980afd7f0..00b52f7cd64 100644
--- a/mysql-test/suite/period/r/overlaps.result
+++ b/mysql-test/suite/period/r/overlaps.result
@@ -121,13 +121,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;
@@ -139,7 +136,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;
@@ -150,7 +147,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 ed15c793878..3c651f24b17 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 primary key(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 primary key,
add primary key(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 d89b8bb2b87..c5a065a6b2c 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;
@@ -91,7 +92,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');
@@ -102,7 +103,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');
@@ -119,24 +120,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_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING
@@ -162,3 +158,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
View Lorry Controller Status