don't allow TIMESTAMP(6) versioning and FK with CASCADE or SET NULL

3 files changed, 188 insertions, 74 deletions

diff --git a/mysql-test/suite/versioning/r/foreign,trx_id.rdiff b/mysql-test/suite/versioning/r/foreign,trx_id.rdiff
new file mode 100644
index 00000000000..c2c66ed11e1
--- /dev/null
+++ b/mysql-test/suite/versioning/r/foreign,trx_id.rdiff
@@ -0,0 +1,166 @@
+--- suite/versioning/r/foreign.result
++++ suite/versioning/r/foreign,trx_id.reject
+@@ -6,8 +6,8 @@
+ ) engine innodb;
+ create table child(
+ parent_id int,
+-sys_start timestamp(6) as row start invisible,
+-sys_end timestamp(6) as row end invisible,
++sys_start bigint(20) unsigned as row start invisible,
++sys_end bigint(20) unsigned as row end invisible,
+ period for system_time(sys_start, sys_end),
+ foreign key(parent_id) references parent(id)
+ on delete restrict
+@@ -39,8 +39,8 @@
+ ) engine innodb;
+ create table child(
+ parent_id int(10) unsigned primary key,
+-sys_start timestamp(6) as row start invisible,
+-sys_end timestamp(6) as row end invisible,
++sys_start bigint(20) unsigned as row start invisible,
++sys_end bigint(20) unsigned as row end invisible,
+ period for system_time(sys_start, sys_end),
+ foreign key(parent_id) references parent(id)
+ ) engine innodb with system versioning;
+@@ -58,19 +58,39 @@
+ ) engine innodb;
+ create table child(
+ parent_id int,
+-sys_start timestamp(6) as row start invisible,
+-sys_end timestamp(6) as row end invisible,
++sys_start bigint(20) unsigned as row start invisible,
++sys_end bigint(20) unsigned as row end invisible,
+ period for system_time(sys_start, sys_end),
+ foreign key(parent_id) references parent(id)
+ on delete cascade
+ on update cascade
+ ) engine innodb with system versioning;
+-ERROR HY000: CASCADE is not supported for TIMESTAMP(6) AS ROW START/END system-versioned tables
++insert into parent values(1);
++insert into child values(1);
++delete from parent where id = 1;
++delete from child where parent_id = 1;
++delete from parent where id = 1;
++select * from child;
++parent_id
++select * from child for system_time all;
++parent_id
++1
++insert into parent values(1);
++insert into child values(1);
++update parent set id = id + 1;
++select * from child;
++parent_id
++2
++select * from child for system_time all;
++parent_id
++1
++2
++drop table child;
+ drop table parent;
+ create or replace table parent (
+ id int primary key,
+-sys_start timestamp(6) as row start invisible,
+-sys_end timestamp(6) as row end invisible,
++sys_start bigint(20) unsigned as row start invisible,
++sys_end bigint(20) unsigned as row end invisible,
+ period for system_time(sys_start, sys_end)
+ ) with system versioning
+ engine innodb;
+@@ -97,8 +117,8 @@
+ create or replace table child (
+ id int primary key,
+ parent_id int not null,
+-row_start timestamp(6) as row start invisible,
+-row_end timestamp(6) as row end invisible,
++row_start bigint(20) unsigned as row start invisible,
++row_end bigint(20) unsigned as row end invisible,
+ period for system_time(row_start, row_end),
+ constraint `parent-fk`
+   foreign key (parent_id) references parent (id)
+@@ -106,7 +126,18 @@
+ on update restrict
+ ) with system versioning
+ engine innodb;
+-ERROR HY000: CASCADE is not supported for TIMESTAMP(6) AS ROW START/END system-versioned tables
++insert into parent (id) values (3);
++insert into child (id, parent_id) values (3, 3);
++## FIXME: #415 update of foreign constraints is disabled
++delete from child;
++## FIXME END
++delete from parent;
++select * from child;
++id	parent_id
++select *, row_start < row_end, row_end < MAXVAL from child for system_time all;
++id	parent_id	row_start < row_end	row_end < MAXVAL
++3	3	1	1
++drop table child;
+ drop table parent;
+ #################
+ # Test SET NULL #
+@@ -116,22 +147,39 @@
+ ) engine innodb;
+ create table child(
+ parent_id int,
+-sys_start timestamp(6) as row start invisible,
+-sys_end timestamp(6) as row end invisible,
++sys_start bigint(20) unsigned as row start invisible,
++sys_end bigint(20) unsigned as row end invisible,
+ period for system_time(sys_start, sys_end),
+ foreign key(parent_id) references parent(id)
+ on delete set null
+ on update set null
+ ) engine innodb with system versioning;
+-ERROR HY000: SET NULL is not supported for TIMESTAMP(6) AS ROW START/END system-versioned tables
++insert into parent values(1);
++insert into child values(1);
++delete from child;
++insert into child values(1);
++## FIXME: #415 update of foreign constraints is disabled
++delete from child where parent_id = 1;
++## FIXME END
++delete from parent where id = 1;
++select * from child;
++parent_id
++select * from child for system_time from timestamp 0 to timestamp now(6);
++parent_id
++1
++1
++delete from child;
++insert into parent values(1);
++insert into child values(1);
++drop table child;
+ drop table parent;
+ ###########################
+ # Parent table is foreign #
+ ###########################
+ create or replace table parent(
+ id int unique key,
+-sys_start timestamp(6) as row start invisible,
+-sys_end timestamp(6) as row end invisible,
++sys_start bigint(20) unsigned as row start invisible,
++sys_end bigint(20) unsigned as row end invisible,
+ period for system_time(sys_start, sys_end)
+ ) engine innodb with system versioning;
+ create or replace table child(
+@@ -162,16 +210,16 @@
+ create or replace table a (
+ cola int(10) primary key,
+ v_cola int(10) as (cola mod 10) virtual,
+-sys_start timestamp(6) as row start invisible,
+-sys_end timestamp(6) as row end invisible,
++sys_start bigint(20) unsigned as row start invisible,
++sys_end bigint(20) unsigned as row end invisible,
+ period for system_time(sys_start, sys_end)
+ ) engine=innodb with system versioning;
+ create index v_cola on a (v_cola);
+ create or replace table b(
+ cola int(10),
+ v_cola int(10),
+-sys_start timestamp(6) as row start invisible,
+-sys_end timestamp(6) as row end invisible,
++sys_start bigint(20) unsigned as row start invisible,
++sys_end bigint(20) unsigned as row end invisible,
+ period for system_time(sys_start, sys_end)
+ ) engine=innodb with system versioning;
+ alter table b add constraint `v_cola_fk`
diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result
index 43b05d06ca3..bb9042fff9f 100644
--- a/mysql-test/suite/versioning/r/foreign.result
+++ b/mysql-test/suite/versioning/r/foreign.result
@@ -6,8 +6,8 @@ id int unique key
 ) engine innodb;
 create table child(
 parent_id int,
-sys_start SYS_DATATYPE as row start invisible,
-sys_end SYS_DATATYPE as row end invisible,
+sys_start timestamp(6) as row start invisible,
+sys_end timestamp(6) as row end invisible,
 period for system_time(sys_start, sys_end),
 foreign key(parent_id) references parent(id)
 on delete restrict
@@ -39,8 +39,8 @@ id int(10) unsigned unique key
 ) engine innodb;
 create table child(
 parent_id int(10) unsigned primary key,
-sys_start SYS_DATATYPE as row start invisible,
-sys_end SYS_DATATYPE as row end invisible,
+sys_start timestamp(6) as row start invisible,
+sys_end timestamp(6) as row end invisible,
 period for system_time(sys_start, sys_end),
 foreign key(parent_id) references parent(id)
 ) engine innodb with system versioning;
@@ -58,43 +58,19 @@ id int unique key
 ) engine innodb;
 create table child(
 parent_id int,
-sys_start SYS_DATATYPE as row start invisible,
-sys_end SYS_DATATYPE as row end invisible,
+sys_start timestamp(6) as row start invisible,
+sys_end timestamp(6) as row end invisible,
 period for system_time(sys_start, sys_end),
 foreign key(parent_id) references parent(id)
 on delete cascade
 on update cascade
 ) engine innodb with system versioning;
-insert into parent values(1);
-insert into child values(1);
-## FIXME: #415 update of foreign constraints is disabled
-call mtr.add_suppression("foreign key constraints in timestamp-based temporal table");
-delete from parent where id = 1;
-ERROR 42000: Table 'parent' uses an extension that doesn't exist in this MariaDB version
-delete from child where parent_id = 1;
-## FIXME END
-delete from parent where id = 1;
-select * from child;
-parent_id
-select * from child for system_time all;
-parent_id
-1
-insert into parent values(1);
-insert into child values(1);
-update parent set id = id + 1;
-select * from child;
-parent_id
-2
-select * from child for system_time all;
-parent_id
-1
-2
-drop table child;
+ERROR HY000: CASCADE is not supported for TIMESTAMP(6) AS ROW START/END system-versioned tables
 drop table parent;
 create or replace table parent (
 id int primary key,
-sys_start SYS_DATATYPE as row start invisible,
-sys_end SYS_DATATYPE as row end invisible,
+sys_start timestamp(6) as row start invisible,
+sys_end timestamp(6) as row end invisible,
 period for system_time(sys_start, sys_end)
 ) with system versioning
 engine innodb;
@@ -121,8 +97,8 @@ engine innodb;
 create or replace table child (
 id int primary key,
 parent_id int not null,
-row_start SYS_DATATYPE as row start invisible,
-row_end SYS_DATATYPE as row end invisible,
+row_start timestamp(6) as row start invisible,
+row_end timestamp(6) as row end invisible,
 period for system_time(row_start, row_end),
 constraint `parent-fk`
   foreign key (parent_id) references parent (id)
@@ -130,18 +106,7 @@ on delete cascade
 on update restrict
 ) with system versioning
 engine innodb;
-insert into parent (id) values (3);
-insert into child (id, parent_id) values (3, 3);
-## FIXME: #415 update of foreign constraints is disabled
-delete from child;
-## FIXME END
-delete from parent;
-select * from child;
-id	parent_id
-select *, row_start < row_end, row_end < MAXVAL from child for system_time all;
-id	parent_id	row_start < row_end	row_end < MAXVAL
-3	3	1	1
-drop table child;
+ERROR HY000: CASCADE is not supported for TIMESTAMP(6) AS ROW START/END system-versioned tables
 drop table parent;
 #################
 # Test SET NULL #
@@ -151,39 +116,22 @@ id int unique key
 ) engine innodb;
 create table child(
 parent_id int,
-sys_start SYS_DATATYPE as row start invisible,
-sys_end SYS_DATATYPE as row end invisible,
+sys_start timestamp(6) as row start invisible,
+sys_end timestamp(6) as row end invisible,
 period for system_time(sys_start, sys_end),
 foreign key(parent_id) references parent(id)
 on delete set null
 on update set null
 ) engine innodb with system versioning;
-insert into parent values(1);
-insert into child values(1);
-delete from child;
-insert into child values(1);
-## FIXME: #415 update of foreign constraints is disabled
-delete from child where parent_id = 1;
-## FIXME END
-delete from parent where id = 1;
-select * from child;
-parent_id
-select * from child for system_time from timestamp 0 to timestamp now(6);
-parent_id
-1
-1
-delete from child;
-insert into parent values(1);
-insert into child values(1);
-drop table child;
+ERROR HY000: SET NULL is not supported for TIMESTAMP(6) AS ROW START/END system-versioned tables
 drop table parent;
 ###########################
 # Parent table is foreign #
 ###########################
 create or replace table parent(
 id int unique key,
-sys_start SYS_DATATYPE as row start invisible,
-sys_end SYS_DATATYPE as row end invisible,
+sys_start timestamp(6) as row start invisible,
+sys_end timestamp(6) as row end invisible,
 period for system_time(sys_start, sys_end)
 ) engine innodb with system versioning;
 create or replace table child(
@@ -214,16 +162,16 @@ drop table parent;
 create or replace table a (
 cola int(10) primary key,
 v_cola int(10) as (cola mod 10) virtual,
-sys_start SYS_DATATYPE as row start invisible,
-sys_end SYS_DATATYPE as row end invisible,
+sys_start timestamp(6) as row start invisible,
+sys_end timestamp(6) as row end invisible,
 period for system_time(sys_start, sys_end)
 ) engine=innodb with system versioning;
 create index v_cola on a (v_cola);
 create or replace table b(
 cola int(10),
 v_cola int(10),
-sys_start SYS_DATATYPE as row start invisible,
-sys_end SYS_DATATYPE as row end invisible,
+sys_start timestamp(6) as row start invisible,
+sys_end timestamp(6) as row end invisible,
 period for system_time(sys_start, sys_end)
 ) engine=innodb with system versioning;
 alter table b add constraint `v_cola_fk`
diff --git a/mysql-test/suite/versioning/r/simple.result b/mysql-test/suite/versioning/r/simple.result
index f6a1ba37f53..7b42fed45c5 100644
--- a/mysql-test/suite/versioning/r/simple.result
+++ b/mysql-test/suite/versioning/r/simple.result
@@ -11,7 +11,7 @@ name varchar(100),
 salary int(10),
 constraint `dept-emp-fk`
     foreign key (dept_id) references dept (dept_id)
-on delete cascade
+on delete restrict
 on update restrict
 ) 
 with system versioning;