summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/versioning/r/insert.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/versioning/r/insert.result')
-rw-r--r--mysql-test/suite/versioning/r/insert.result321
1 files changed, 321 insertions, 0 deletions
diff --git a/mysql-test/suite/versioning/r/insert.result b/mysql-test/suite/versioning/r/insert.result
new file mode 100644
index 00000000..442d71c6
--- /dev/null
+++ b/mysql-test/suite/versioning/r/insert.result
@@ -0,0 +1,321 @@
+create or replace table t1(
+x int unsigned,
+y int unsigned,
+sys_start SYS_DATATYPE as row start invisible,
+sys_end SYS_DATATYPE as row end invisible,
+period for system_time (sys_start, sys_end))
+with system versioning;
+insert into t1(x, y) values(3, 4);
+insert into t1(x, y) values(2, 3);
+insert into t1 values(40, 33);
+select x, y, sys_end < MAXVAL from t1;
+x y sys_end < MAXVAL
+3 4 0
+2 3 0
+40 33 0
+create or replace table t1(
+id int unsigned auto_increment primary key,
+x int unsigned,
+y int unsigned,
+sys_start SYS_DATATYPE as row start invisible,
+sys_end SYS_DATATYPE as row end invisible,
+period for system_time (sys_start, sys_end))
+with system versioning;
+insert into t1(x, y) values(33, 44);
+insert into t1(id, x, y) values(20, 33, 44);
+insert into t1 values(40, 33, 44);
+select id, x, y, sys_end < MAXVAL from t1;
+id x y sys_end < MAXVAL
+1 33 44 0
+20 33 44 0
+40 33 44 0
+create or replace table t1(
+x int unsigned,
+y int unsigned,
+sys_start SYS_DATATYPE as row start invisible,
+sys_end SYS_DATATYPE as row end invisible,
+period for system_time (sys_start, sys_end))
+with system versioning;
+create view vt1_1 as select x, y from t1;
+insert into t1(x, y) values(8001, 9001);
+insert into vt1_1(x, y) values(1001, 2001);
+insert into vt1_1 values(1002, 2002);
+select x, y, sys_end < MAXVAL from t1;
+x y sys_end < MAXVAL
+8001 9001 0
+1001 2001 0
+1002 2002 0
+select x, y from vt1_1;
+x y
+8001 9001
+1001 2001
+1002 2002
+drop view vt1_1;
+create or replace table t1( id bigint primary key, a int, b int) with system versioning;
+insert into t1 values(1, 1, 1);
+select row_start, row_end from t1 into @sys_start, @sys_end;
+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 id, a, b from t1;
+id a b
+1 1 1
+insert into t1 values(2, 2, 2);
+select id, a, b, row_start > @sys_start as C, row_end = @sys_end as D from t1 where id = 2;
+id a b C D
+2 2 2 1 1
+drop table t1;
+create or replace table t1(
+x int unsigned,
+y int unsigned,
+sys_start SYS_DATATYPE as row start invisible,
+sys_end SYS_DATATYPE as row end invisible,
+period for system_time (sys_start, sys_end))
+with system versioning;
+create or replace table t2 like t1;
+insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000);
+delete from t1 where x >= 1;
+insert into t1(x, y) values (1, 1001), (2, 2001), (3, 3001), (4, 4001), (5, 5001), (6, 6001);
+insert into t1(x, y, sys_start) values (7, 7001, DEFAULT);
+insert into t1(x, y, sys_end) values (8, 8001, DEFAULT);
+insert into t1(x, y, sys_start, sys_end) values (9, 9001, DEFAULT, DEFAULT);
+insert into t2 select x, y from t1 for system_time all;
+select x, y from t1;
+x y
+1 1001
+2 2001
+3 3001
+4 4001
+5 5001
+6 6001
+7 7001
+8 8001
+9 9001
+select x, y from t2;
+x y
+1 1000
+2 2000
+3 3000
+4 4000
+5 5000
+6 6000
+7 7000
+8 8000
+9 9000
+1 1001
+2 2001
+3 3001
+4 4001
+5 5001
+6 6001
+7 7001
+8 8001
+9 9001
+drop table t1;
+drop table t2;
+#
+# MDEV-16546 System versioning setting to allow history modification
+#
+set @@session.time_zone='+00:00';
+create table t1(x int primary key) with system versioning;
+create table t2(y int primary key,
+row_start timestamp(6) as row start invisible,
+row_end timestamp(6) as row end invisible,
+period for system_time (row_start, row_end))
+with system versioning;
+create table t3(z int primary key,
+row_start timestamp(6) as row start,
+row_end timestamp(6) as row end,
+period for system_time (row_start, row_end))
+with system versioning;
+insert into t1(x, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+ERROR 42S22: Unknown column 'row_start' in 'field list'
+insert into t2(y, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
+set @@system_versioning_insert_history= 1;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `x` int(11) NOT NULL,
+ PRIMARY KEY (`x`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
+insert into t1(x, row_start, row_end) values (3, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+insert into t2(y, row_start, row_end) values (4, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+insert into t3 values (5, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+insert into t3 values (5, '1980-01-02 00:00:00', '1980-01-01 00:00:01');
+ERROR HY000: Incorrect row_start value: '1980-01-02 00:00:00.000000'
+select x, row_start, row_end from t1 for system_time all;
+x row_start row_end
+3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
+select y, row_start, row_end from t2 for system_time all;
+y row_start row_end
+4 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
+select z, row_start, row_end from t3 for system_time all;
+z row_start row_end
+5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
+insert into t1(x) values (1);
+insert into t2(y) values (1);
+update t1 set x= x + 1;
+update t1 set row_start= '1971-01-01 00:00:00';
+ERROR 42S22: Unknown column 'row_start' in 'field list'
+update t2 set row_start= '1971-01-01 00:00:00';
+ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
+insert t1 (x) values (2) on duplicate key update x= 3, row_end= '1970-01-01 00:00:00';
+ERROR 42S22: Unknown column 'row_end' in 'field list'
+insert t2 (y) values (1) on duplicate key update y= 3, row_end= '1970-01-01 00:00:00';
+ERROR HY000: The value specified for generated column 'row_end' in table 't2' has been ignored
+insert t2 (y,row_end) values (1, '1970-01-01 00:00:00') on duplicate key update y= 3;
+ERROR HY000: The value specified for generated column 'row_end' in table 't2' has been ignored
+insert into t1 values (4);
+insert into t1 set x= 5, row_start= '1980-01-01 00:00:00', row_end= '1980-01-01 00:00:01';
+insert into t1(x, row_start, row_end) values (6, '1980-01-01 00:00:01', '1980-01-01 00:00:00');
+ERROR HY000: Incorrect row_start value: '1980-01-01 00:00:01.000000'
+insert into t1(x, row_start, row_end) values (7, '1980-01-01 00:00:11', '1980-01-01 00:00:11');
+ERROR HY000: Incorrect row_start value: '1980-01-01 00:00:11.000000'
+insert into t1(x, row_start) values (8, '1980-01-01 00:00:22');
+insert into t1(x, row_end) values (9, '1980-01-01 00:00:33');
+ERROR HY000: Incorrect row_start value: 'now'
+insert into t1(x, row_end) values (10, TIMESTAMP'2038-01-19 03:14:07.999999');
+select x, check_row_ts(row_start, row_end) from t1 for system_time all order by x;
+x check_row_ts(row_start, row_end)
+1 HISTORICAL ROW
+2 CURRENT ROW
+3 HISTORICAL ROW
+4 CURRENT ROW
+5 HISTORICAL ROW
+8 CURRENT ROW
+10 CURRENT ROW
+select x, row_start, row_end from t1 for system_time all
+where x > 1 and row_end < TIMESTAMP'2038-01-19 03:14:07.999999' order by x, row_start, row_end;
+x row_start row_end
+3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
+5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
+# Direct insert is not possible for TRX_ID versioning
+create or replace table t2(y int primary key,
+row_start bigint unsigned as row start,
+row_end bigint unsigned as row end,
+period for system_time (row_start, row_end))
+with system versioning engine innodb;
+insert into t2(y, row_start, row_end) values (0, 1, 2);
+ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
+set @@system_versioning_insert_history= 0;
+## INSERT..SELECT
+create or replace table t2 like t1;
+set @@system_versioning_insert_history= 1;
+insert into t2 (x, row_start, row_end) select x, row_start, row_end from t1 for system_time all;
+select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x;
+x check_row_ts(row_start, row_end)
+1 HISTORICAL ROW
+2 CURRENT ROW
+3 HISTORICAL ROW
+4 CURRENT ROW
+5 HISTORICAL ROW
+8 CURRENT ROW
+10 CURRENT ROW
+select x, row_start, row_end from t2 for system_time all
+where x > 1 and row_end < TIMESTAMP'2038-01-19 03:14:07.999999' order by x, row_start, row_end;
+x row_start row_end
+3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
+5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
+set @@system_versioning_insert_history= 0;
+# REPLACE / REPLACE .. SELECT
+create or replace table t2(a int primary key,
+row_start timestamp(6) as row start invisible,
+row_end timestamp(6) as row end invisible,
+period for system_time (row_start, row_end))
+with system versioning;
+replace into t2 (a, row_start, row_end) values (1, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
+replace into t2 (a, row_start, row_end) select x, row_start, row_end from t1;
+ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
+create or replace table t2 (a int primary key) with system versioning;
+replace into t2 (a, row_start, row_end) values (1, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+ERROR 42S22: Unknown column 'row_start' in 'field list'
+replace into t2 (a, row_start, row_end) select x, row_start, row_end from t1;
+ERROR 42S22: Unknown column 'row_start' in 'field list'
+set @@system_versioning_insert_history= 1;
+# REPLACE ignores system_versioning_insert_history
+replace into t2 (a, row_end) values (0, '1980-01-01 00:00:00');
+ERROR 42S22: Unknown column 'row_end' in 'field list'
+replace into t3 (z, row_start) values (0, '1980-01-01 00:00:00');
+ERROR HY000: The value specified for generated column 'row_start' in table 't3' has been ignored
+replace into t3 values (0, '1980-01-01 00:00:00', '1981-01-01 00:00:00');
+ERROR HY000: The value specified for generated column 'row_start' in table 't3' has been ignored
+# LOAD DATA
+select x, row_start, row_end into outfile 'DATAFILE' from t1 for system_time all;
+create or replace table t2 like t1;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `x` int(11) NOT NULL,
+ PRIMARY KEY (`x`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
+set @@system_versioning_insert_history= 1;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `x` int(11) NOT NULL,
+ PRIMARY KEY (`x`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
+load data infile 'DATAFILE' into table t2 (x, row_start, row_end);
+select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x;
+x check_row_ts(row_start, row_end)
+1 HISTORICAL ROW
+2 CURRENT ROW
+3 HISTORICAL ROW
+4 CURRENT ROW
+5 HISTORICAL ROW
+8 CURRENT ROW
+10 CURRENT ROW
+select row_start = '1980-01-01 00:00:00', row_end = '1980-01-01 00:00:01' from t2 for system_time all where x = 3;
+row_start = '1980-01-01 00:00:00' row_end = '1980-01-01 00:00:01'
+1 1
+# Honor secure_timestamp option
+# restart: --secure-timestamp=YES
+set @@system_versioning_insert_history= 1;
+insert into t3(z, row_start, row_end) values (8, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+ERROR HY000: The MariaDB server is running with the --secure-timestamp=YES option so it cannot execute this statement
+insert into t3 values (8, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+ERROR HY000: The MariaDB server is running with the --secure-timestamp=YES option so it cannot execute this statement
+# restart: --secure-timestamp=REPLICATION
+create user nobody;
+grant all privileges on test.* to nobody;
+set @@system_versioning_insert_history= 1;
+insert into test.t3(z, row_start, row_end) values (9, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+ERROR 42000: Access denied; you need (at least one of) the BINLOG REPLAY privilege(s) for this operation
+insert into test.t3 values (9, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+ERROR 42000: Access denied; you need (at least one of) the BINLOG REPLAY privilege(s) for this operation
+# restart: --secure-timestamp=SUPER
+set @@system_versioning_insert_history= 1;
+insert into test.t3(z, row_start, row_end) values (10, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+set @@system_versioning_insert_history= 1;
+insert into test.t3(z, row_start, row_end) values (7, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG REPLAY privilege(s) for this operation
+insert into test.t3 values (7, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
+ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG REPLAY privilege(s) for this operation
+use test;
+# restart: --secure-timestamp=NO
+drop tables t1, t2, t3;
+#
+# MDEV-29813 REPLACE/IGNORE does not work with historical records in InnoDB
+#
+set sql_mode='STRICT_ALL_TABLES';
+create or replace table t1 (a int) with system versioning;
+set system_versioning_insert_history= on;
+insert into t1 (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
+select a,row_start,row_end into outfile 'mdev29813.txt' from t1 for system_time all;
+create or replace table t1 (a int primary key) with system versioning;
+load data infile 'mdev29813.txt' ignore into table t1 (a,row_start,row_end);
+Warnings:
+Warning 1062 Duplicate entry '1' for key 'PRIMARY'
+select a,row_start,row_end from t1 for system_time all;
+a row_start row_end
+1 2022-01-01 00:00:00.000000 2023-01-01 00:00:00.000000
+create or replace table t1 (a int primary key) with system versioning;
+insert ignore into t1 (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
+Warnings:
+Warning 1062 Duplicate entry '1' for key 'PRIMARY'
+select a,row_start,row_end from t1 for system_time all;
+a row_start row_end
+1 2022-01-01 00:00:00.000000 2023-01-01 00:00:00.000000
+drop table t1;
+set sql_mode=default;
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-01 08:45:55 +0000