diff options
Diffstat (limited to 'mysql-test/suite/versioning/r/trx_id.result')
| -rw-r--r-- | mysql-test/suite/versioning/r/trx_id.result | 507 |
1 files changed, 507 insertions, 0 deletions
diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result new file mode 100644 index 00000000..f09284a6 --- /dev/null +++ b/mysql-test/suite/versioning/r/trx_id.result @@ -0,0 +1,507 @@ +set default_storage_engine= innodb; +create or replace table t1 ( +x int, +sys_trx_start bigint(20) unsigned as row start invisible, +sys_trx_end bigint(20) unsigned as row end invisible, +period for system_time (sys_trx_start, sys_trx_end) +) with system versioning; +# No history inside the transaction +start transaction; +insert into t1 (x) values (1); +update t1 set x= x + 1; +update t1 set x= x + 1; +commit; +select *, sys_trx_start > 1, sys_trx_end from t1 for system_time all; +x sys_trx_start > 1 sys_trx_end +3 1 18446744073709551615 +# ALTER ADD SYSTEM VERSIONING should write to mysql.transaction_registry +set @@system_versioning_alter_history=keep; +create or replace table t1 (x int); +insert into t1 values (1); +alter table t1 +add column s bigint unsigned as row start, +add column e bigint unsigned as row end, +add period for system_time(s, e), +add system versioning, +algorithm=inplace; +select s from t1 into @trx_start; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start; +count(*) = 1 +1 +create or replace table t1 (x int); +select count(*) from mysql.transaction_registry into @tmp; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +alter table t1 +add column s bigint unsigned as row start, +add column e bigint unsigned as row end, +add period for system_time(s, e), +add system versioning, +algorithm=inplace; +select count(*) = @tmp from mysql.transaction_registry; +count(*) = @tmp +1 +create or replace table t1 (x int); +insert into t1 values (1); +alter table t1 +add column s bigint unsigned as row start, +add column e bigint unsigned as row end, +add period for system_time(s, e), +add system versioning, +algorithm=copy; +select s from t1 into @trx_start; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start; +count(*) = 1 +1 +create or replace table t1 (x int); +select count(*) from mysql.transaction_registry into @tmp; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +alter table t1 +add column s bigint unsigned as row start, +add column e bigint unsigned as row end, +add period for system_time(s, e), +add system versioning, +algorithm=copy; +select count(*) = @tmp + 1 from mysql.transaction_registry; +count(*) = @tmp + 1 +1 +# TRX_ID to TIMESTAMP versioning switch +create or replace table t1 ( +x int, +sys_start bigint unsigned as row start invisible, +sys_end bigint unsigned as row end invisible, +period for system_time (sys_start, sys_end) +) with system versioning; +insert into t1 values (1); +alter table t1 drop column sys_start, drop column sys_end; +select row_end = 18446744073709551615 as transaction_based from t1 for system_time all; +transaction_based +1 +# Simple vs SEES algorithms +create or replace table t1 ( +x int, +sys_start bigint(20) unsigned as row start invisible, +sys_end bigint(20) unsigned as row end invisible, +period for system_time (sys_start, sys_end) +) with system versioning; +set transaction isolation level read committed; +start transaction; +insert into t1 values (1); +connect con1,localhost,root,,test; +set transaction isolation level read committed; +start transaction; +insert into t1 values (2); +connect con2,localhost,root,,test; +set transaction isolation level read committed; +start transaction; +insert into t1 values (3); +commit; +disconnect con2; +connection default; +set @ts1= sysdate(6); +connection con1; +commit; +disconnect con1; +connection default; +set @ts2= sysdate(6); +commit; +set @ts3= sysdate(6); +select sys_start from t1 where x = 1 into @trx_id1; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +select sys_start from t1 where x = 2 into @trx_id2; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +select sys_start from t1 where x = 3 into @trx_id3; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +select @trx_id1 < @trx_id2, @trx_id2 < @trx_id3; +@trx_id1 < @trx_id2 @trx_id2 < @trx_id3 +1 1 +select @ts1 < @ts2, @ts2 < @ts3; +@ts1 < @ts2 @ts2 < @ts3 +1 1 +# MVCC is resolved +select * from t1 for system_time as of transaction @trx_id1; +x +1 +2 +3 +select * from t1 for system_time as of timestamp @ts1; +x +3 +select * from t1 for system_time as of transaction @trx_id2; +x +2 +3 +select * from t1 for system_time as of timestamp @ts2; +x +2 +3 +select * from t1 for system_time as of transaction @trx_id3; +x +3 +select * from t1 for system_time as of timestamp @ts3; +x +1 +2 +3 +# +# MDEV-15427 IB: TRX_ID based operations inside transaction generate history +# +create or replace table t1( +x int(10), +row_start bigint(20) unsigned as row start, +row_end bigint(20) unsigned as row end, +period for system_time(row_start, row_end) +) with system versioning; +begin; +insert into t1 (x) values (1); +delete from t1; +commit; +select x from t1 for system_time all; +x +insert into t1 (x) values (2); +begin; +update t1 set x= 3; +update t1 set x= 4; +commit; +select x, row_start < row_end from t1 for system_time all; +x row_start < row_end +4 1 +2 1 +# 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, +row_end bigint unsigned as row end invisible, +period for system_time (row_start, row_end) +) with system versioning engine innodb; +insert into t1 (x) values (1); +delete from t1; +select * from t1 for system_time as of timestamp'1990-1-1 00:00'; +x +select * from t1 for system_time as of NULL; +x +# MDEV-16024 transaction_registry.begin_timestamp is wrong for explicit transactions +create or replace table t1 ( +x 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 with system versioning; +begin; +set @ts1= now(6); +insert into t1 values (1); +commit; +select row_start from t1 into @trx_id; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +select trt_begin_ts(@trx_id) <= @ts1 as BEGIN_TS_GOOD; +BEGIN_TS_GOOD +1 +drop table t1; +# +# MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs +# +CREATE TABLE t1 ( +x INT, +sys_trx_start BIGINT UNSIGNED AS ROW START, +sys_trx_end BIGINT UNSIGNED AS ROW END, +PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; +INSERT INTO t1 (x) VALUES (1); +SET @ts= DATE_ADD(NOW(), INTERVAL 1 YEAR); +EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION @ts; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME AS OF TRANSACTION @`ts` where trt_trx_sees(`test`.`t1`.`sys_trx_end`,@`ts`) and trt_trx_sees_eq(@`ts`,`test`.`t1`.`sys_trx_start`) +EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TIMESTAMP @ts; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts` where trt_trx_sees(`test`.`t1`.`sys_trx_end`,<cache>(trt_trx_id(@`ts`))) and trt_trx_sees_eq(<cache>(trt_trx_id(@`ts`)),`test`.`t1`.`sys_trx_start`) +EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF @ts; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts` where trt_trx_sees(`test`.`t1`.`sys_trx_end`,<cache>(trt_trx_id(@`ts`))) and trt_trx_sees_eq(<cache>(trt_trx_id(@`ts`)),`test`.`t1`.`sys_trx_start`) +DROP TABLE t1; +# +# Testing AS OF with expressions of various kinds and data types +# +CREATE TABLE t1 +( +x INT, +sys_trx_start BIGINT UNSIGNED AS ROW START INVISIBLE, +sys_trx_end BIGINT UNSIGNED AS ROW END INVISIBLE, +PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end) +) WITH SYSTEM VERSIONING; +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 +( +x INT, +sys_trx_start TIMESTAMP(6) AS ROW START INVISIBLE, +sys_trx_end TIMESTAMP(6) AS ROW END INVISIBLE, +PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end) +) WITH SYSTEM VERSIONING; +INSERT INTO t2 VALUES (1); +# +# ROW is not supported +# +SELECT * FROM t1 FOR SYSTEM_TIME AS OF (1,1); +ERROR HY000: Illegal parameter data type row for operation 'FOR SYSTEM_TIME' +SELECT * FROM t2 FOR SYSTEM_TIME AS OF (1,1); +ERROR HY000: Illegal parameter data type row for operation 'FOR SYSTEM_TIME' +# +# DOUBLE is not supported, use explicit CAST +# +SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION RAND(); +ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' +SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION (RAND()); +ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' +SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND()); +ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' +SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION RAND(); +ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' +SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION (RAND()); +ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' +SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND()); +ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' +# +# DECIMAL is not supported, use explicit CAST +# +SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION 10.1; +ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME' +SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1); +ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME' +SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION 10.1; +ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME' +SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1); +ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME' +# +# YEAR is not supported, use explicit CAST +# +BEGIN NOT ATOMIC +DECLARE var YEAR; +SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var; +END; +$$ +ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME' +BEGIN NOT ATOMIC +DECLARE var YEAR; +SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var; +END; +$$ +ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME' +# +# ENUM is not supported, use explicit CAST +# +BEGIN NOT ATOMIC +DECLARE var ENUM('xxx') DEFAULT 'xxx'; +SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var; +END; +$$ +ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME' +BEGIN NOT ATOMIC +DECLARE var ENUM('xxx') DEFAULT 'xxx'; +SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var; +END; +$$ +ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME' +# +# SET is not supported, use explicit CAST +# +BEGIN NOT ATOMIC +DECLARE var SET('xxx') DEFAULT 'xxx'; +SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var; +END; +$$ +ERROR HY000: Illegal parameter data type set for operation 'FOR SYSTEM_TIME' +BEGIN NOT ATOMIC +DECLARE var SET('xxx') DEFAULT 'xxx'; +SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var; +END; +$$ +ERROR HY000: Illegal parameter data type set for operation 'FOR SYSTEM_TIME' +BEGIN NOT ATOMIC +DECLARE var BIT(10); +SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var; +END; +$$ +ERROR HY000: Transaction-precise system versioning for `t2` is not supported +# +# String literals resolve to TIMESTAMP +# +SELECT * FROM t1 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00'; +x +1 +SELECT * FROM t2 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00'; +x +DROP TABLE t1, t2; +# +# MDEV-16094 Crash when using AS OF with a stored function +# +CREATE FUNCTION fts() RETURNS DATETIME RETURN '2001-01-01 10:20:30'; +CREATE FUNCTION ftx() RETURNS BIGINT UNSIGNED RETURN 1; +CREATE TABLE ttx +( +x INT, +start_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW START, +end_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW END, +PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp) +) ENGINE=InnoDB WITH SYSTEM VERSIONING; +CREATE TABLE tts +( +x INT, +start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START, +end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END, +PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp) +) ENGINE=InnoDB WITH SYSTEM VERSIONING; +SELECT * FROM tts FOR SYSTEM_TIME AS OF fts(); +x start_timestamp end_timestamp +SELECT * FROM tts FOR SYSTEM_TIME AS OF TRANSACTION ftx(); +ERROR HY000: Transaction-precise system versioning for `tts` is not supported +SELECT * FROM ttx FOR SYSTEM_TIME AS OF fts(); +x start_timestamp end_timestamp +SELECT * FROM ttx FOR SYSTEM_TIME AS OF TRANSACTION ftx(); +x start_timestamp end_timestamp +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: 1 +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. +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; +SELECT count(*) from mysql.transaction_registry where begin_timestamp>=commit_timestamp; +count(*) +0 +# MDEV-18875 Assertion `thd->transaction.stmt.ha_list == __null || +# trans == &thd->transaction.stmt' failed or bogus ER_DUP_ENTRY upon +# ALTER TABLE with versioning +create or replace table t (x int) engine=innodb; +set autocommit= 0; +alter table t +algorithm=copy, +add column row_start bigint unsigned as row start, +add column row_end bigint unsigned as row end, +add period for system_time(row_start,row_end), +with system versioning; +set autocommit= 1; +# MDEV-18865 Assertion `t->first->versioned_by_id()' +# failed in innodb_prepare_commit_versioned +create or replace table t (x int) engine=innodb; +insert into t values (0); +alter table t add `row_start` bigint unsigned as row start, +add `row_end` bigint unsigned as row end, +add period for system_time(`row_start`,`row_end`), +modify x int after row_start, +with system versioning; +drop table t; +# +# MDEV-20842 Crash using versioning plugin functions after plugin was removed from server +# +uninstall plugin test_versioning; +select trt_begin_ts(0); +ERROR 42000: FUNCTION test.trt_begin_ts does not exist |