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.

2 files changed, 182 insertions, 0 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;