0.1: SQL-level System Versioning

1 files changed, 73 insertions, 0 deletions

diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index 206c5553100..7a4523a3f49 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -573,3 +573,76 @@ insert ignore into t1 values (1,12) on duplicate key update f2=13;
 set @@old_mode="";
 insert ignore into t1 values (1,12);
 DROP TABLE t1;
+
+--echo #
+--echo # System Versioning Support
+--echo #
+--echo #
+
+-- source include/have_innodb.inc
+
+SET @@session.time_zone='+00:00';
+
+CREATE TABLE t1(x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING;
+INSERT INTO t1(x, y) VALUES(3, 4);
+INSERT INTO t1(x, y) VALUES(2, 3);
+INSERT INTO t1 VALUES(40, 33);
+SELECT x, y, Sys_end FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING ENGINE=InnoDB;
+INSERT INTO t1(x, y) VALUES(3, 4);
+INSERT INTO t1(x, y) VALUES(2, 3);
+INSERT INTO t1 VALUES(40, 33);
+SELECT x, y, Sys_end FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT, x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end), PRIMARY KEY(id)) WITH SYSTEM VERSIONING;
+INSERT INTO t1(x, y) VALUES(33, 44);
+INSERT INTO t1(id, x, y) VALUES(20, 33, 44);
+INSERT INTO t1 VALUES(40, 33, 44);
+SELECT id, x, y, Sys_end FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT, x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end), PRIMARY KEY(id)) WITH SYSTEM VERSIONING ENGINE=InnoDB;
+INSERT INTO t1(x, y) VALUES(33, 44);
+INSERT INTO t1(id, x, y) VALUES(20, 33, 44);
+INSERT INTO t1 VALUES(40, 33, 44);
+SELECT id, x, y, Sys_end FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING;
+CREATE VIEW vt1_1 AS SELECT x, y FROM t1;
+CREATE VIEW vt1_2 AS SELECT x, y, Sys_end FROM t1;
+INSERT INTO t1(x, y) VALUES(8001, 9001);
+--error ER_GENERATED_FIELD_CANNOT_BE_SET_BY_USER
+INSERT INTO t1(x, y, Sys_end) VALUES(8001, 9001, '2015-1-1 0:0:0');
+INSERT INTO vt1_1(x, y) VALUES(1001, 2001);
+INSERT INTO vt1_1 VALUES(1002, 2002);
+INSERT INTO vt1_2(x, y) VALUES(3001, 4001);
+--error ER_GENERATED_FIELD_CANNOT_BE_SET_BY_USER
+INSERT INTO vt1_2 VALUES(3002, 4002, '2015-1-1 0:0:0');
+SELECT x, y, Sys_end FROM t1;
+SELECT x, y FROM vt1_1;
+SELECT x, y, Sys_end FROM vt1_2;
+DROP TABLE t1;
+DROP VIEW vt1_1;
+DROP VIEW vt1_2;
+
+CREATE TABLE t1(x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING ENGINE=InnoDB;
+CREATE VIEW vt1_1 AS SELECT x, y FROM t1;
+CREATE VIEW vt1_2 AS SELECT x, y, Sys_end FROM t1;
+INSERT INTO t1(x, y) VALUES(8001, 9001);
+--error ER_GENERATED_FIELD_CANNOT_BE_SET_BY_USER
+INSERT INTO t1(x, y, Sys_end) VALUES(8001, 9001, '2015-1-1 0:0:0');
+INSERT INTO vt1_1(x, y) VALUES(1001, 2001);
+INSERT INTO vt1_1 VALUES(1002, 2002);
+INSERT INTO vt1_2(x, y) VALUES(3001, 4001);
+--error ER_GENERATED_FIELD_CANNOT_BE_SET_BY_USER
+INSERT INTO vt1_2 VALUES(3002, 4002, '2015-1-1 0:0:0');
+SELECT x, y, Sys_end FROM t1;
+SELECT x, y FROM vt1_1;
+SELECT x, y, Sys_end FROM vt1_2;
+DROP TABLE t1;
+DROP VIEW vt1_1;
+DROP VIEW vt1_2;