path: root/mysql-test/suite/versioning/r/trx_id.result

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;
insert into t1 (x) values (1);
# 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 ALL 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 ALL 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 ALL 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