diff options
Diffstat (limited to 'mysql-test/suite/versioning/r/trx_id.result')
| -rw-r--r-- | mysql-test/suite/versioning/r/trx_id.result | 96 |
1 files changed, 96 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; |