MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute

Changing columns WITH/WITHOUT SYSTEM VERSIONING doens't require to read data at
all. Thus it should be an instant operation.

Patch also fixes a bug when ALTER_COLUMN_UNVERSIONED wasn't passed to InnoDB
to change its internal structures.

change_field_versioning_try(): apply WITH/WITHOUT SYSTEM VERSIONING
change in SYS_COLUMNS for one field.

change_fields_versioning_try(): apply WITH/WITHOUT SYSTEM VERSIONING
change in SYS_COLUMNS for every changed field in a table.

change_fields_versioning_cache(): update cache for versioning property
of columns.

4 files changed, 351 insertions, 1 deletions

diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result
index 7b2ea04985d..2e0db5f2cde 100644
--- a/mysql-test/suite/versioning/r/trx_id.result
+++ b/mysql-test/suite/versioning/r/trx_id.result
@@ -379,3 +379,99 @@ DROP TABLE tts;
 DROP TABLE ttx;
 DROP FUNCTION fts;
 DROP FUNCTION ftx;
+#
+# MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute
+#
+SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
+CREATE TABLE t (
+a INT,
+b INT,
+row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+PERIOD FOR SYSTEM_TIME(row_start, row_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+INSERT INTO t VALUES (1,1);
+# without table rebuild
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+ON c.table_id=t.table_id
+WHERE t.name='test/t' AND c.name='a';
+prtype
+50179
+ALTER TABLE t
+CHANGE a a INT WITHOUT SYSTEM VERSIONING;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+ON c.table_id=t.table_id
+WHERE t.name='test/t' AND c.name='a';
+prtype
+1027
+UPDATE t SET a=11;
+SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
+COUNT(*)
+1
+# with table rebuild
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+ON c.table_id=t.table_id
+WHERE t.name='test/t' AND c.name='a';
+prtype
+1027
+ALTER TABLE t
+CHANGE a a INT WITH SYSTEM VERSIONING,
+ADD PRIMARY KEY pk(a);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+ON c.table_id=t.table_id
+WHERE t.name='test/t' AND c.name='a';
+prtype
+50435
+UPDATE t SET a=1;
+SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
+COUNT(*)
+2
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `row_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START INVISIBLE,
+  `row_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END INVISIBLE,
+  PRIMARY KEY (`a`,`row_end`),
+  PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+# handles VIRTUAL columns too
+CREATE OR REPLACE TABLE t (
+a INT AS (b + 1),
+b INT,
+row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+PERIOD FOR SYSTEM_TIME(row_start, row_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+INSERT INTO t VALUES (DEFAULT, 1);
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+ON c.table_id=t.table_id
+WHERE t.name='test/t' AND c.name='b';
+prtype
+50179
+ALTER TABLE t
+CHANGE b b INT WITHOUT SYSTEM VERSIONING;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+ON c.table_id=t.table_id
+WHERE t.name='test/t' AND c.name='b';
+prtype
+1027
+UPDATE t SET b=11;
+SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
+COUNT(*)
+1
+DROP TABLE t;
+SET @@SYSTEM_VERSIONING_ALTER_HISTORY=ERROR;
diff --git a/mysql-test/suite/versioning/r/trx_id_versioning_attribute_persistence.result b/mysql-test/suite/versioning/r/trx_id_versioning_attribute_persistence.result
new file mode 100644
index 00000000000..bf555fb2a7d
--- /dev/null
+++ b/mysql-test/suite/versioning/r/trx_id_versioning_attribute_persistence.result
@@ -0,0 +1,86 @@
+CREATE OR REPLACE TABLE t1 (
+a INT,
+b INT,
+row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+PERIOD FOR SYSTEM_TIME(row_start, row_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+CREATE OR REPLACE TABLE t2 (
+a INT WITHOUT SYSTEM VERSIONING,
+b INT,
+row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+PERIOD FOR SYSTEM_TIME(row_start, row_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t2 VALUES (1,1);
+SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
+# without rebuild
+ALTER TABLE t1
+CHANGE a a INT WITHOUT SYSTEM VERSIONING,
+ALGORITHM=INSTANT;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t2
+CHANGE a a INT WITH SYSTEM VERSIONING,
+ADD PRIMARY KEY pk (a),
+ALGORITHM=INSTANT;
+ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
+# with rebuild
+ALTER TABLE t2
+CHANGE a a INT WITH SYSTEM VERSIONING,
+ADD PRIMARY KEY pk (a);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+UPDATE t1 SET a=2;
+SELECT COUNT(*) FROM t1 FOR SYSTEM_TIME ALL;
+COUNT(*)
+1
+UPDATE t2 SET a=2;
+SELECT COUNT(*) FROM t2 FOR SYSTEM_TIME ALL;
+COUNT(*)
+2
+DROP TABLE t1, t2;
+# rollback ALTER TABLE: nothing should change
+CREATE TABLE t (
+a INT,
+b INT,
+row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+PERIOD FOR SYSTEM_TIME(row_start, row_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+INSERT INTO t VALUES (1, 1);
+SELECT C.PRTYPE FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS C
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON C.TABLE_ID=t.TABLE_ID
+WHERE t.NAME='test/t' AND C.NAME='b';
+PRTYPE
+50179
+SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
+SET @SAVED_DEBUG_DBUG = @@SESSION.DEBUG_DBUG;
+SET DEBUG_DBUG='+d,ib_commit_inplace_fail_1';
+ALTER TABLE t
+CHANGE b b INT WITHOUT SYSTEM VERSIONING;
+ERROR HY000: Internal error: Injected error!
+SET DEBUG_DBUG = @SAVED_DEBUG_DBUG;
+SELECT C.PRTYPE FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS C
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON C.TABLE_ID=t.TABLE_ID
+WHERE t.NAME='test/t' AND C.NAME='b';
+PRTYPE
+50179
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `row_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START INVISIBLE,
+  `row_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END INVISIBLE,
+  PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
+COUNT(*)
+1
+UPDATE t SET b=11;
+SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
+COUNT(*)
+2
+DROP TABLE t;
diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test
index aab28d1057c..35ba6595440 100644
--- a/mysql-test/suite/versioning/t/trx_id.test
+++ b/mysql-test/suite/versioning/t/trx_id.test
@@ -1,5 +1,4 @@
 -- source include/have_innodb.inc
--- source include/not_embedded.inc
 
 set default_storage_engine= innodb;
 
@@ -411,3 +410,85 @@ DROP TABLE tts;
 DROP TABLE ttx;
 DROP FUNCTION fts;
 DROP FUNCTION ftx;
+
+--echo #
+--echo # MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute
+--echo #
+
+SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
+CREATE TABLE t (
+  a INT,
+  b INT,
+  row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+  row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+  PERIOD FOR SYSTEM_TIME(row_start, row_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+
+INSERT INTO t VALUES (1,1);
+
+--echo # without table rebuild
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+  INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+    ON c.table_id=t.table_id
+  WHERE t.name='test/t' AND c.name='a';
+--enable_info
+ALTER TABLE t
+  CHANGE a a INT WITHOUT SYSTEM VERSIONING;
+--disable_info
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+  INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+    ON c.table_id=t.table_id
+  WHERE t.name='test/t' AND c.name='a';
+
+UPDATE t SET a=11;
+SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
+
+--echo # with table rebuild
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+  INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+    ON c.table_id=t.table_id
+  WHERE t.name='test/t' AND c.name='a';
+--enable_info
+ALTER TABLE t
+  CHANGE a a INT WITH SYSTEM VERSIONING,
+  ADD PRIMARY KEY pk(a);
+--disable_info
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+  INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+    ON c.table_id=t.table_id
+  WHERE t.name='test/t' AND c.name='a';
+
+UPDATE t SET a=1;
+SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
+
+SHOW CREATE TABLE t;
+
+-- echo # handles VIRTUAL columns too
+CREATE OR REPLACE TABLE t (
+  a INT AS (b + 1),
+  b INT,
+  row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+  row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+  PERIOD FOR SYSTEM_TIME(row_start, row_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+
+INSERT INTO t VALUES (DEFAULT, 1);
+
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+  INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+    ON c.table_id=t.table_id
+  WHERE t.name='test/t' AND c.name='b';
+--enable_info
+ALTER TABLE t
+  CHANGE b b INT WITHOUT SYSTEM VERSIONING;
+--disable_info
+SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
+  INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
+    ON c.table_id=t.table_id
+  WHERE t.name='test/t' AND c.name='b';
+
+UPDATE t SET b=11;
+SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
+
+DROP TABLE t;
+SET @@SYSTEM_VERSIONING_ALTER_HISTORY=ERROR;
diff --git a/mysql-test/suite/versioning/t/trx_id_versioning_attribute_persistence.test b/mysql-test/suite/versioning/t/trx_id_versioning_attribute_persistence.test
new file mode 100644
index 00000000000..4e92ee247e4
--- /dev/null
+++ b/mysql-test/suite/versioning/t/trx_id_versioning_attribute_persistence.test
@@ -0,0 +1,87 @@
+-- source include/have_innodb.inc
+-- source include/have_debug.inc
+
+CREATE OR REPLACE TABLE t1 (
+  a INT,
+  b INT,
+  row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+  row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+  PERIOD FOR SYSTEM_TIME(row_start, row_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+
+CREATE OR REPLACE TABLE t2 (
+  a INT WITHOUT SYSTEM VERSIONING,
+  b INT,
+  row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+  row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+  PERIOD FOR SYSTEM_TIME(row_start, row_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t2 VALUES (1,1);
+
+SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
+
+--enable_info
+--echo # without rebuild
+ALTER TABLE t1
+  CHANGE a a INT WITHOUT SYSTEM VERSIONING,
+  ALGORITHM=INSTANT;
+
+--error ER_ALTER_OPERATION_NOT_SUPPORTED
+ALTER TABLE t2
+  CHANGE a a INT WITH SYSTEM VERSIONING,
+  ADD PRIMARY KEY pk (a),
+  ALGORITHM=INSTANT;
+
+--echo # with rebuild
+ALTER TABLE t2
+  CHANGE a a INT WITH SYSTEM VERSIONING,
+  ADD PRIMARY KEY pk (a);
+--disable_info
+
+--source include/restart_mysqld.inc
+
+UPDATE t1 SET a=2;
+SELECT COUNT(*) FROM t1 FOR SYSTEM_TIME ALL;
+
+UPDATE t2 SET a=2;
+SELECT COUNT(*) FROM t2 FOR SYSTEM_TIME ALL;
+
+DROP TABLE t1, t2;
+
+--echo # rollback ALTER TABLE: nothing should change
+CREATE TABLE t (
+  a INT,
+  b INT,
+  row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
+  row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
+  PERIOD FOR SYSTEM_TIME(row_start, row_end)
+) WITH SYSTEM VERSIONING ENGINE=INNODB;
+
+INSERT INTO t VALUES (1, 1);
+
+SELECT C.PRTYPE FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS C
+  JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON C.TABLE_ID=t.TABLE_ID
+  WHERE t.NAME='test/t' AND C.NAME='b';
+
+SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
+
+SET @SAVED_DEBUG_DBUG = @@SESSION.DEBUG_DBUG;
+SET DEBUG_DBUG='+d,ib_commit_inplace_fail_1';
+--error ER_INTERNAL_ERROR
+ALTER TABLE t
+  CHANGE b b INT WITHOUT SYSTEM VERSIONING;
+SET DEBUG_DBUG = @SAVED_DEBUG_DBUG;
+
+SELECT C.PRTYPE FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS C
+  JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON C.TABLE_ID=t.TABLE_ID
+  WHERE t.NAME='test/t' AND C.NAME='b';
+
+SHOW CREATE TABLE t;
+
+SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
+UPDATE t SET b=11;
+SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
+
+DROP TABLE t;