MDEV-15969 System versioning and FEDERATED don't work well together: DML and discovery fail

* fixed discovery
* DELETE fix
* federated.test

Closes tempesta-tech/mariadb#490

4 files changed, 177 insertions, 17 deletions

diff --git a/mysql-test/suite/federated/assisted_discovery.result b/mysql-test/suite/federated/assisted_discovery.result
index d44f69effcd..4818ff7bb02 100644
--- a/mysql-test/suite/federated/assisted_discovery.result
+++ b/mysql-test/suite/federated/assisted_discovery.result
@@ -72,14 +72,6 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 connection slave;
 drop table t1;
-create or replace table t1 (x int) with system versioning;
-connection master;
-create table t1 engine=federated connection='mysql://root@127.0.0.1:SLAVE_MYPORT/test/t1';
-ERROR HY000: Engine FEDERATED failed to discover table `test`.`t1` with 'CREATE TABLE `t1` (
-  `x` int(11) DEFAULT NULL
-) WITH SYSTEM VERSIONING CONNECTION='mysql://root@127.0.0.1:SLAVE_MYPORT/test/t1''
-connection slave;
-drop table t1;
 connection master;
 DROP TABLE IF EXISTS federated.t1;
 DROP DATABASE IF EXISTS federated;
diff --git a/mysql-test/suite/federated/assisted_discovery.test b/mysql-test/suite/federated/assisted_discovery.test
index 7099cfedb23..fa83a2a8e19 100644
--- a/mysql-test/suite/federated/assisted_discovery.test
+++ b/mysql-test/suite/federated/assisted_discovery.test
@@ -54,14 +54,5 @@ drop table t1;
 connection slave;
 drop table t1;
 
-create or replace table t1 (x int) with system versioning;
-connection master;
---replace_result $SLAVE_MYPORT SLAVE_MYPORT
---error ER_SQL_DISCOVER_ERROR
-eval create table t1 engine=federated connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/t1';
-
-connection slave;
-drop table t1;
-
 source include/federated_cleanup.inc;
 
diff --git a/mysql-test/suite/federated/federatedx_versioning.result b/mysql-test/suite/federated/federatedx_versioning.result
new file mode 100644
index 00000000000..7af5a5f3f0c
--- /dev/null
+++ b/mysql-test/suite/federated/federatedx_versioning.result
@@ -0,0 +1,100 @@
+create or replace table t1 (
+x int,
+row_start SYS_TYPE as row start invisible,
+row_end SYS_TYPE as row end invisible,
+period for system_time (row_start, row_end))
+with system versioning;
+create or replace table tf engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t1';
+show create table tf;
+Table	Create Table
+tf	CREATE TABLE `tf` (
+  `x` int(11) DEFAULT NULL,
+  `row_start` SYS_TYPE NOT NULL INVISIBLE DEFAULT 0,
+  `row_end` SYS_TYPE NOT NULL INVISIBLE DEFAULT 0
+) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:MASTER_MYPORT/test/t1'
+# INSERT
+insert into t1 values (1);
+select * from tf;
+x
+1
+insert into tf (x) values (2);
+select * from t1;
+x
+1
+2
+select * from tf;
+x
+1
+2
+# UPDATE
+update tf set x= x + 2;
+select *, check_row(row_start, row_end) from t1 for system_time all
+order by x;
+x	check_row(row_start, row_end)
+1	HISTORICAL ROW
+2	HISTORICAL ROW
+3	CURRENT ROW
+4	CURRENT ROW
+# DELETE
+delete from tf;
+select *, check_row(row_start, row_end) from t1 for system_time all
+order by x;
+x	check_row(row_start, row_end)
+1	HISTORICAL ROW
+2	HISTORICAL ROW
+3	HISTORICAL ROW
+4	HISTORICAL ROW
+select * from tf;
+x
+# TRUNCATE
+truncate tf;
+select * from t1 for system_time all;
+x
+# REPLACE
+create or replace table t2 (
+id int primary key, y int,
+row_start SYS_TYPE as row start invisible,
+row_end SYS_TYPE as row end invisible,
+period for system_time (row_start, row_end))
+with system versioning;
+create or replace table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t2';
+insert t2f (id, y) values (1, 2);
+replace t2f (id, y) values (1, 3);
+select *, check_row(row_start, row_end) from t2 for system_time all
+order by y;
+id	y	check_row(row_start, row_end)
+1	2	HISTORICAL ROW
+1	3	CURRENT ROW
+# VIEW
+create or replace view vt1 as select * from tf;
+insert into vt1 values (3);
+update vt1 set x= x + 1;
+select *, check_row(row_start, row_end) from t1 for system_time all
+order by x;
+x	check_row(row_start, row_end)
+3	HISTORICAL ROW
+4	CURRENT ROW
+delete from vt1;
+select *, check_row(row_start, row_end) from t1 for system_time all
+order by x;
+x	check_row(row_start, row_end)
+3	HISTORICAL ROW
+4	HISTORICAL ROW
+# multi-UPDATE
+truncate t1;
+truncate t2;
+insert into t1 values (1);
+insert into t2 values (2, 2);
+update tf, t2f set tf.x= 11, t2f.y= 22;
+select *, check_row(row_start, row_end) from t1 for system_time all
+order by x;
+x	check_row(row_start, row_end)
+1	HISTORICAL ROW
+11	CURRENT ROW
+select *, check_row(row_start, row_end) from t2 for system_time all
+order by y;
+id	y	check_row(row_start, row_end)
+2	2	HISTORICAL ROW
+2	22	CURRENT ROW
+drop database test;
+create database test;
diff --git a/mysql-test/suite/federated/federatedx_versioning.test b/mysql-test/suite/federated/federatedx_versioning.test
new file mode 100644
index 00000000000..692edb5b00a
--- /dev/null
+++ b/mysql-test/suite/federated/federatedx_versioning.test
@@ -0,0 +1,77 @@
+--source include/not_embedded.inc
+--source have_federatedx.inc
+--source suite/versioning/engines.inc
+--source suite/versioning/common.inc
+
+--replace_result $sys_datatype_expl SYS_TYPE
+eval create or replace table t1 (
+  x int,
+  row_start $sys_datatype_expl as row start invisible,
+  row_end $sys_datatype_expl as row end invisible,
+  period for system_time (row_start, row_end))
+with system versioning;
+--replace_result $MASTER_MYPORT MASTER_MYPORT
+eval create or replace table tf engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t1';
+--replace_result $MASTER_MYPORT MASTER_MYPORT $sys_datatype_expl SYS_TYPE "'0000-00-00 00:00:00.000000'" 0
+show create table tf;
+--echo # INSERT
+insert into t1 values (1);
+select * from tf;
+insert into tf (x) values (2);
+select * from t1;
+select * from tf;
+
+--echo # UPDATE
+update tf set x= x + 2;
+select *, check_row(row_start, row_end) from t1 for system_time all
+order by x;
+
+--echo # DELETE
+delete from tf;
+select *, check_row(row_start, row_end) from t1 for system_time all
+order by x;
+select * from tf;
+
+--echo # TRUNCATE
+truncate tf;
+select * from t1 for system_time all;
+
+--echo # REPLACE
+--replace_result $sys_datatype_expl SYS_TYPE
+eval create or replace table t2 (
+  id int primary key, y int,
+  row_start $sys_datatype_expl as row start invisible,
+  row_end $sys_datatype_expl as row end invisible,
+  period for system_time (row_start, row_end))
+with system versioning;
+--replace_result $MASTER_MYPORT MASTER_MYPORT
+eval create or replace table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t2';
+insert t2f (id, y) values (1, 2);
+replace t2f (id, y) values (1, 3);
+select *, check_row(row_start, row_end) from t2 for system_time all
+order by y;
+
+--echo # VIEW
+create or replace view vt1 as select * from tf;
+insert into vt1 values (3);
+update vt1 set x= x + 1;
+select *, check_row(row_start, row_end) from t1 for system_time all
+order by x;
+delete from vt1;
+select *, check_row(row_start, row_end) from t1 for system_time all
+order by x;
+
+--echo # multi-UPDATE
+truncate t1;
+truncate t2;
+insert into t1 values (1);
+insert into t2 values (2, 2);
+update tf, t2f set tf.x= 11, t2f.y= 22;
+select *, check_row(row_start, row_end) from t1 for system_time all
+order by x;
+select *, check_row(row_start, row_end) from t2 for system_time all
+order by y;
+
+--source suite/versioning/common_finish.inc
+drop database test;
+create database test;