Versioning test suite cleanups

Merged truncate_privilege and sysvars-notembedded into not_embedded.test
Merged partition_innodb into trx_id.test

11 files changed, 293 insertions, 276 deletions

diff --git a/mysql-test/suite/versioning/r/truncate_privilege.result b/mysql-test/suite/versioning/r/not_embedded.result
index e2b48c494be..f17ab18349c 100644
--- a/mysql-test/suite/versioning/r/truncate_privilege.result
+++ b/mysql-test/suite/versioning/r/not_embedded.result
@@ -1,3 +1,39 @@
+# 
+# SYSTEM_VERSIONING_ASOF sysvar
+#
+create table t (a int) with system versioning;
+set @before= UNIX_TIMESTAMP(now(6));
+insert into t values (1);
+set @after= UNIX_TIMESTAMP(now(6));
+update t set a= 2;
+set global system_versioning_asof= FROM_UNIXTIME(@after);
+set system_versioning_asof= FROM_UNIXTIME(@after);
+select * from t as nonempty;
+a
+1
+connect  subcon,127.0.0.1,root,,,$SERVER_MYPORT_1;
+connection subcon;
+select * from t as nonempty;
+a
+1
+disconnect subcon;
+connection default;
+set global system_versioning_asof= FROM_UNIXTIME(@before);
+select * from t as nonempty;
+a
+1
+connect  subcon,127.0.0.1,root,,,$SERVER_MYPORT_1;
+connection subcon;
+select * from t as empty;
+a
+disconnect subcon;
+connection default;
+drop table t;
+set global system_versioning_asof= DEFAULT;
+set system_versioning_asof= DEFAULT;
+#
+# DELETE HISTORY and privileges
+#
 connect  root,localhost,root,,test;
 connection root;
 create database mysqltest;
@@ -31,3 +67,6 @@ GRANT DELETE HISTORY ON `mysqltest`.* TO `mysqltest_1`@`localhost`
 GRANT DELETE HISTORY ON `mysqltest`.`t` TO `mysqltest_1`@`localhost`
 drop user mysqltest_1@localhost;
 drop database mysqltest;
+disconnect user1;
+disconnect root;
+connection default;
diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result
index 8344e91a34f..df45ae4f00e 100644
--- a/mysql-test/suite/versioning/r/partition.result
+++ b/mysql-test/suite/versioning/r/partition.result
@@ -551,6 +551,18 @@ t1	CREATE TABLE `t1` (
 (PARTITION `ver_p1` HISTORY ENGINE = DEFAULT_ENGINE,
  PARTITION `ver_pn` CURRENT ENGINE = DEFAULT_ENGINE)
 #
+# MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table
+#
+create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i))
+engine=innodb with system versioning partition by key() partitions 2;
+insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b');
+alter table t1 drop system versioning;
+replace into t1 select * from t1;
+select * from t1 where i > 0 or pk = 1000 limit 1;
+pk	i	c
+1	1	a
+drop table t1;
+#
 # MDEV-19175 Server crashes in ha_partition::vers_can_native upon INSERT DELAYED into versioned partitioned table
 #
 create or replace table t1 (f int) with system versioning partition by hash(f);
diff --git a/mysql-test/suite/versioning/r/partition_innodb.result b/mysql-test/suite/versioning/r/partition_innodb.result
deleted file mode 100644
index b9870797cd2..00000000000
--- a/mysql-test/suite/versioning/r/partition_innodb.result
+++ /dev/null
@@ -1,80 +0,0 @@
-# MDEV-15951 system versioning by trx id doesn't work with partitioning
-# currently trx_id does not support partitioning by system_time
-create or replace table t1(
-i int,
-row_start bigint unsigned generated always as row start,
-row_end bigint unsigned generated always as row end,
-period for system_time(row_start, row_end)
-) engine=InnoDB with system versioning partition by system_time (
-partition p0 history,
-partition pn current
-);
-ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1`
-create or replace table t1(
-i int,
-row_start bigint unsigned generated always as row start,
-row_end bigint unsigned generated always as row end,
-period for system_time(row_start, row_end)
-) engine=InnoDB with system versioning;
-alter table t1  partition by system_time (
-partition p0 history,
-partition pn current
-);
-ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `#sql-temporary`
-drop table t1;
-create or replace table t (
-a int primary key,
-row_start bigint unsigned as row start invisible,
-row_end bigint unsigned as row end invisible,
-period for system_time(row_start, row_end)
-) engine=innodb with system versioning
-partition by key() (
-partition p1,
-partition p2
-);
-ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END
-create or replace table t (
-a int primary key,
-row_start bigint unsigned as row start invisible,
-row_end bigint unsigned as row end invisible,
-period for system_time(row_start, row_end)
-) engine=innodb with system versioning
-partition by key(a, row_start) (
-partition p1,
-partition p2
-);
-ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END
-create or replace table t (
-a int primary key,
-row_start bigint unsigned as row start invisible,
-row_end bigint unsigned as row end invisible,
-period for system_time(row_start, row_end)
-) engine=innodb with system versioning
-partition by hash(a + row_end * 2) (
-partition p1,
-partition p2
-);
-ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END
-create or replace table t (
-a int primary key,
-row_start bigint unsigned as row start invisible,
-row_end bigint unsigned as row end invisible,
-period for system_time(row_start, row_end)
-) engine=innodb with system versioning
-partition by range columns (a, row_start) (
-partition p1 values less than (100, 100)
-);
-ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END
-#
-# MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table
-#
-create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i))
-engine=innodb with system versioning partition by key() partitions 2;
-insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b');
-alter table t1 drop system versioning;
-replace into t1 select * from t1;
-select * from t1 where i > 0 or pk = 1000 limit 1;
-pk	i	c
-1	1	a
-drop table t1;
-# End of 10.3 tests
diff --git a/mysql-test/suite/versioning/r/sysvars-notembedded.result b/mysql-test/suite/versioning/r/sysvars-notembedded.result
deleted file mode 100644
index 8b1ad6cfc58..00000000000
--- a/mysql-test/suite/versioning/r/sysvars-notembedded.result
+++ /dev/null
@@ -1,30 +0,0 @@
-create table t (a int) with system versioning;
-set @before= UNIX_TIMESTAMP(now(6));
-insert into t values (1);
-set @after= UNIX_TIMESTAMP(now(6));
-update t set a= 2;
-set global system_versioning_asof= FROM_UNIXTIME(@after);
-set system_versioning_asof= FROM_UNIXTIME(@after);
-select * from t as nonempty;
-a
-1
-connect  subcon,127.0.0.1,root,,,$SERVER_MYPORT_1;
-connection subcon;
-select * from t as nonempty;
-a
-1
-disconnect subcon;
-connection default;
-set global system_versioning_asof= FROM_UNIXTIME(@before);
-select * from t as nonempty;
-a
-1
-connect  subcon,127.0.0.1,root,,,$SERVER_MYPORT_1;
-connection subcon;
-select * from t as empty;
-a
-disconnect subcon;
-connection default;
-drop table t;
-set global system_versioning_asof= DEFAULT;
-set system_versioning_asof= DEFAULT;
diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result
index bad7272419e..fbda88fb1d7 100644
--- a/mysql-test/suite/versioning/r/trx_id.result
+++ b/mysql-test/suite/versioning/r/trx_id.result
@@ -161,7 +161,78 @@ select x, row_start <  row_end from t1 for system_time all;
 x	row_start <  row_end
 4	1
 2	1
+#
+# MDEV-15951 system versioning by trx id doesn't work with partitioning
+# currently trx_id does not support partitioning by system_time
+#
+create or replace table t1(
+i int,
+row_start bigint unsigned generated always as row start,
+row_end bigint unsigned generated always as row end,
+period for system_time(row_start, row_end)
+) engine=InnoDB with system versioning partition by system_time (
+partition p0 history,
+partition pn current
+);
+ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1`
+create or replace table t1(
+i int,
+row_start bigint unsigned generated always as row start,
+row_end bigint unsigned generated always as row end,
+period for system_time(row_start, row_end)
+) engine=InnoDB with system versioning;
+alter table t1  partition by system_time (
+partition p0 history,
+partition pn current
+);
+ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `#sql-temporary`
+drop table t1;
+create or replace table t (
+a int primary key,
+row_start bigint unsigned as row start invisible,
+row_end bigint unsigned as row end invisible,
+period for system_time(row_start, row_end)
+) engine=innodb with system versioning
+partition by key() (
+partition p1,
+partition p2
+);
+ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END
+create or replace table t (
+a int primary key,
+row_start bigint unsigned as row start invisible,
+row_end bigint unsigned as row end invisible,
+period for system_time(row_start, row_end)
+) engine=innodb with system versioning
+partition by key(a, row_start) (
+partition p1,
+partition p2
+);
+ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END
+create or replace table t (
+a int primary key,
+row_start bigint unsigned as row start invisible,
+row_end bigint unsigned as row end invisible,
+period for system_time(row_start, row_end)
+) engine=innodb with system versioning
+partition by hash(a + row_end * 2) (
+partition p1,
+partition p2
+);
+ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END
+create or replace table t (
+a int primary key,
+row_start bigint unsigned as row start invisible,
+row_end bigint unsigned as row end invisible,
+period for system_time(row_start, row_end)
+) engine=innodb with system versioning
+partition by range columns (a, row_start) (
+partition p1 values less than (100, 100)
+);
+ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END
+#
 # MDEV-16010 Too many rows with AS OF point_in_the_past_or_NULL
+#
 create or replace table t1 (
 x int,
 row_start bigint unsigned as row start invisible,
diff --git a/mysql-test/suite/versioning/t/not_embedded.test b/mysql-test/suite/versioning/t/not_embedded.test
new file mode 100644
index 00000000000..2afae013e70
--- /dev/null
+++ b/mysql-test/suite/versioning/t/not_embedded.test
@@ -0,0 +1,79 @@
+--source include/not_embedded.inc
+--source include/have_innodb.inc
+
+--echo # 
+--echo # SYSTEM_VERSIONING_ASOF sysvar
+--echo #
+create table t (a int) with system versioning;
+set @before= UNIX_TIMESTAMP(now(6));
+insert into t values (1);
+set @after= UNIX_TIMESTAMP(now(6));
+update t set a= 2;
+
+set global system_versioning_asof= FROM_UNIXTIME(@after);
+set system_versioning_asof= FROM_UNIXTIME(@after);
+select * from t as nonempty;
+
+--connect (subcon,127.0.0.1,root,,,$SERVER_MYPORT_1)
+--connection subcon
+select * from t as nonempty;
+--disconnect subcon
+--connection default
+
+set global system_versioning_asof= FROM_UNIXTIME(@before);
+select * from t as nonempty;
+
+--connect (subcon,127.0.0.1,root,,,$SERVER_MYPORT_1)
+--connection subcon
+select * from t as empty;
+--disconnect subcon
+--connection default
+
+drop table t;
+
+set global system_versioning_asof= DEFAULT;
+set system_versioning_asof= DEFAULT;
+
+--echo #
+--echo # DELETE HISTORY and privileges
+--echo #
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+
+connect (root,localhost,root,,test);
+connection root;
+
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create user mysqltest_1@localhost;
+connect (user1,localhost,mysqltest_1,,test);
+connection user1;
+
+connection root;
+create table mysqltest.t (a int) with system versioning;
+
+connection user1;
+show grants;
+--error ER_TABLEACCESS_DENIED_ERROR
+delete history from mysqltest.t before system_time now();
+
+connection root;
+grant delete history on mysqltest.* to mysqltest_1@localhost;
+grant delete history on mysqltest.t to mysqltest_1@localhost;
+
+connection user1;
+show grants;
+delete history from mysqltest.t before system_time now();
+
+connection root;
+grant all on *.* to mysqltest_1@localhost;
+show grants for mysqltest_1@localhost;
+
+drop user mysqltest_1@localhost;
+drop database mysqltest;
+--disconnect user1
+--disconnect root
+--connection default
diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test
index 6eeb305c218..f415ea131ba 100644
--- a/mysql-test/suite/versioning/t/partition.test
+++ b/mysql-test/suite/versioning/t/partition.test
@@ -499,6 +499,17 @@ partition by system_time interval column_get(column_create(7,7), 7 as int) secon
 show create table t1;
 
 --echo #
+--echo # MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table
+--echo #
+create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i))
+engine=innodb with system versioning partition by key() partitions 2;
+insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b');
+alter table t1 drop system versioning;
+replace into t1 select * from t1;
+select * from t1 where i > 0 or pk = 1000 limit 1;
+drop table t1;
+
+--echo #
 --echo # MDEV-19175 Server crashes in ha_partition::vers_can_native upon INSERT DELAYED into versioned partitioned table
 --echo #
 create or replace table t1 (f int) with system versioning partition by hash(f);
diff --git a/mysql-test/suite/versioning/t/partition_innodb.test b/mysql-test/suite/versioning/t/partition_innodb.test
deleted file mode 100644
index 29ec58af13c..00000000000
--- a/mysql-test/suite/versioning/t/partition_innodb.test
+++ /dev/null
@@ -1,94 +0,0 @@
---source include/have_innodb.inc
---source include/have_partition.inc
---source suite/versioning/common.inc
-
---echo # MDEV-15951 system versioning by trx id doesn't work with partitioning
---echo # currently trx_id does not support partitioning by system_time
---error ER_VERS_FIELD_WRONG_TYPE
-create or replace table t1(
-  i int,
-  row_start bigint unsigned generated always as row start,
-  row_end bigint unsigned generated always as row end,
-  period for system_time(row_start, row_end)
-) engine=InnoDB with system versioning partition by system_time (
-  partition p0 history,
-  partition pn current
-);
-
-create or replace table t1(
-  i int,
-  row_start bigint unsigned generated always as row start,
-  row_end bigint unsigned generated always as row end,
-  period for system_time(row_start, row_end)
-) engine=InnoDB with system versioning;
-
---replace_regex /#sql-[0-9a-f_]*/#sql-temporary/
---error ER_VERS_FIELD_WRONG_TYPE
-alter table t1  partition by system_time (
-  partition p0 history,
-  partition pn current
-);
-
-drop table t1;
-
---error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
-create or replace table t (
-  a int primary key,
-  row_start bigint unsigned as row start invisible,
-  row_end bigint unsigned as row end invisible,
-  period for system_time(row_start, row_end)
-) engine=innodb with system versioning
-partition by key() (
-  partition p1,
-  partition p2
-);
-
---error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
-create or replace table t (
-  a int primary key,
-  row_start bigint unsigned as row start invisible,
-  row_end bigint unsigned as row end invisible,
-  period for system_time(row_start, row_end)
-) engine=innodb with system versioning
-partition by key(a, row_start) (
-  partition p1,
-  partition p2
-);
-
---error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
-create or replace table t (
-  a int primary key,
-  row_start bigint unsigned as row start invisible,
-  row_end bigint unsigned as row end invisible,
-  period for system_time(row_start, row_end)
-) engine=innodb with system versioning
-partition by hash(a + row_end * 2) (
-  partition p1,
-  partition p2
-);
-
---error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
-create or replace table t (
-  a int primary key,
-  row_start bigint unsigned as row start invisible,
-  row_end bigint unsigned as row end invisible,
-  period for system_time(row_start, row_end)
-) engine=innodb with system versioning
-partition by range columns (a, row_start) (
-  partition p1 values less than (100, 100)
-);
-
---echo #
---echo # MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table
---echo #
-create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i))
-engine=innodb with system versioning partition by key() partitions 2;
-insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b');
-alter table t1 drop system versioning;
-replace into t1 select * from t1;
-select * from t1 where i > 0 or pk = 1000 limit 1;
-drop table t1;
-
---echo # End of 10.3 tests
-
---source suite/versioning/common_finish.inc
diff --git a/mysql-test/suite/versioning/t/sysvars-notembedded.test b/mysql-test/suite/versioning/t/sysvars-notembedded.test
deleted file mode 100644
index 314972bc375..00000000000
--- a/mysql-test/suite/versioning/t/sysvars-notembedded.test
+++ /dev/null
@@ -1,31 +0,0 @@
-source include/not_embedded.inc;
-
-create table t (a int) with system versioning;
-set @before= UNIX_TIMESTAMP(now(6));
-insert into t values (1);
-set @after= UNIX_TIMESTAMP(now(6));
-update t set a= 2;
-
-set global system_versioning_asof= FROM_UNIXTIME(@after);
-set system_versioning_asof= FROM_UNIXTIME(@after);
-select * from t as nonempty;
-
---connect (subcon,127.0.0.1,root,,,$SERVER_MYPORT_1)
---connection subcon
-select * from t as nonempty;
---disconnect subcon
---connection default
-
-set global system_versioning_asof= FROM_UNIXTIME(@before);
-select * from t as nonempty;
-
---connect (subcon,127.0.0.1,root,,,$SERVER_MYPORT_1)
---connection subcon
-select * from t as empty;
---disconnect subcon
---connection default
-
-drop table t;
-
-set global system_versioning_asof= DEFAULT;
-set system_versioning_asof= DEFAULT;
diff --git a/mysql-test/suite/versioning/t/truncate_privilege.test b/mysql-test/suite/versioning/t/truncate_privilege.test
deleted file mode 100644
index dcdad59039a..00000000000
--- a/mysql-test/suite/versioning/t/truncate_privilege.test
+++ /dev/null
@@ -1,41 +0,0 @@
-# Can't test with embedded server
--- source include/not_embedded.inc
-
---source include/have_innodb.inc
-
-# Save the initial number of concurrent sessions
---source include/count_sessions.inc
-
-connect (root,localhost,root,,test);
-connection root;
-
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create user mysqltest_1@localhost;
-connect (user1,localhost,mysqltest_1,,test);
-connection user1;
-
-connection root;
-create table mysqltest.t (a int) with system versioning;
-
-connection user1;
-show grants;
---error ER_TABLEACCESS_DENIED_ERROR
-delete history from mysqltest.t before system_time now();
-
-connection root;
-grant delete history on mysqltest.* to mysqltest_1@localhost;
-grant delete history on mysqltest.t to mysqltest_1@localhost;
-
-connection user1;
-show grants;
-delete history from mysqltest.t before system_time now();
-
-connection root;
-grant all on *.* to mysqltest_1@localhost;
-show grants for mysqltest_1@localhost;
-
-drop user mysqltest_1@localhost;
-drop database mysqltest;
diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test
index 08f183536f6..eb0174f2851 100644
--- a/mysql-test/suite/versioning/t/trx_id.test
+++ b/mysql-test/suite/versioning/t/trx_id.test
@@ -3,6 +3,7 @@ if (!$TEST_VERSIONING_SO)
   --skip needs test_versioning plugin
 }
 --source include/have_innodb.inc
+--source include/have_partition.inc
 --source include/default_charset.inc
 
 --disable_query_log
@@ -155,7 +156,87 @@ update t1 set x= 4;
 commit;
 select x, row_start <  row_end from t1 for system_time all;
 
+--echo #
+--echo # MDEV-15951 system versioning by trx id doesn't work with partitioning
+--echo # currently trx_id does not support partitioning by system_time
+--echo #
+--error ER_VERS_FIELD_WRONG_TYPE
+create or replace table t1(
+  i int,
+  row_start bigint unsigned generated always as row start,
+  row_end bigint unsigned generated always as row end,
+  period for system_time(row_start, row_end)
+) engine=InnoDB with system versioning partition by system_time (
+  partition p0 history,
+  partition pn current
+);
+
+create or replace table t1(
+  i int,
+  row_start bigint unsigned generated always as row start,
+  row_end bigint unsigned generated always as row end,
+  period for system_time(row_start, row_end)
+) engine=InnoDB with system versioning;
+
+--replace_regex /#sql-[0-9a-f_]*/#sql-temporary/
+--error ER_VERS_FIELD_WRONG_TYPE
+alter table t1  partition by system_time (
+  partition p0 history,
+  partition pn current
+);
+
+drop table t1;
+
+--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
+create or replace table t (
+  a int primary key,
+  row_start bigint unsigned as row start invisible,
+  row_end bigint unsigned as row end invisible,
+  period for system_time(row_start, row_end)
+) engine=innodb with system versioning
+partition by key() (
+  partition p1,
+  partition p2
+);
+
+--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
+create or replace table t (
+  a int primary key,
+  row_start bigint unsigned as row start invisible,
+  row_end bigint unsigned as row end invisible,
+  period for system_time(row_start, row_end)
+) engine=innodb with system versioning
+partition by key(a, row_start) (
+  partition p1,
+  partition p2
+);
+
+--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
+create or replace table t (
+  a int primary key,
+  row_start bigint unsigned as row start invisible,
+  row_end bigint unsigned as row end invisible,
+  period for system_time(row_start, row_end)
+) engine=innodb with system versioning
+partition by hash(a + row_end * 2) (
+  partition p1,
+  partition p2
+);
+
+--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
+create or replace table t (
+  a int primary key,
+  row_start bigint unsigned as row start invisible,
+  row_end bigint unsigned as row end invisible,
+  period for system_time(row_start, row_end)
+) engine=innodb with system versioning
+partition by range columns (a, row_start) (
+  partition p1 values less than (100, 100)
+);
+
+--echo #
 --echo # MDEV-16010 Too many rows with AS OF point_in_the_past_or_NULL
+--echo #
 create or replace table t1 (
   x int,
   row_start bigint unsigned as row start invisible,