Debian Codegraph @

mysql-test/suite/versioning/r/insert.result

raw
1 create or replace table t1(
2 x int unsigned,
3 y int unsigned,
4 sys_start SYS_DATATYPE as row start invisible,
5 sys_end SYS_DATATYPE as row end invisible,
6 period for system_time (sys_start, sys_end))
7 with system versioning;
8 insert into t1(x, y) values(3, 4);
9 insert into t1(x, y) values(2, 3);
10 insert into t1 values(40, 33);
11 select x, y, sys_end < MAXVAL from t1;
12 x y sys_end < MAXVAL
13 3 4 0
14 2 3 0
15 40 33 0
16 create or replace table t1(
17 id int unsigned auto_increment primary key,
18 x int unsigned,
19 y int unsigned,
20 sys_start SYS_DATATYPE as row start invisible,
21 sys_end SYS_DATATYPE as row end invisible,
22 period for system_time (sys_start, sys_end))
23 with system versioning;
24 insert into t1(x, y) values(33, 44);
25 insert into t1(id, x, y) values(20, 33, 44);
26 insert into t1 values(40, 33, 44);
27 select id, x, y, sys_end < MAXVAL from t1;
28 id x y sys_end < MAXVAL
29 1 33 44 0
30 20 33 44 0
31 40 33 44 0
32 create or replace table t1(
33 x int unsigned,
34 y int unsigned,
35 sys_start SYS_DATATYPE as row start invisible,
36 sys_end SYS_DATATYPE as row end invisible,
37 period for system_time (sys_start, sys_end))
38 with system versioning;
39 create view vt1_1 as select x, y from t1;
40 insert into t1(x, y) values(8001, 9001);
41 insert into vt1_1(x, y) values(1001, 2001);
42 insert into vt1_1 values(1002, 2002);
43 select x, y, sys_end < MAXVAL from t1;
44 x y sys_end < MAXVAL
45 8001 9001 0
46 1001 2001 0
47 1002 2002 0
48 select x, y from vt1_1;
49 x y
50 8001 9001
51 1001 2001
52 1002 2002
53 drop view vt1_1;
54 create or replace table t1( id bigint primary key, a int, b int) with system versioning;
55 insert into t1 values(1, 1, 1);
56 select row_start, row_end from t1 into @sys_start, @sys_end;
57 select id, a, b from t1;
58 id a b
59 1 1 1
60 insert into t1 values(2, 2, 2);
61 select id, a, b, row_start > @sys_start as C, row_end = @sys_end as D from t1 where id = 2;
62 id a b C D
63 2 2 2 1 1
64 drop table t1;
65 create or replace table t1(
66 x int unsigned,
67 y int unsigned,
68 sys_start SYS_DATATYPE as row start invisible,
69 sys_end SYS_DATATYPE as row end invisible,
70 period for system_time (sys_start, sys_end))
71 with system versioning;
72 create or replace table t2 like t1;
73 insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000);
74 delete from t1 where x >= 1;
75 insert into t1(x, y) values (1, 1001), (2, 2001), (3, 3001), (4, 4001), (5, 5001), (6, 6001);
76 insert into t1(x, y, sys_start) values (7, 7001, DEFAULT);
77 insert into t1(x, y, sys_end) values (8, 8001, DEFAULT);
78 insert into t1(x, y, sys_start, sys_end) values (9, 9001, DEFAULT, DEFAULT);
79 insert into t2 select x, y from t1 for system_time all;
80 select x, y from t1;
81 x y
82 1 1001
83 2 2001
84 3 3001
85 4 4001
86 5 5001
87 6 6001
88 7 7001
89 8 8001
90 9 9001
91 select x, y from t2;
92 x y
93 1 1000
94 2 2000
95 3 3000
96 4 4000
97 5 5000
98 6 6000
99 7 7000
100 8 8000
101 9 9000
102 1 1001
103 2 2001
104 3 3001
105 4 4001
106 5 5001
107 6 6001
108 7 7001
109 8 8001
110 9 9001
111 drop table t1;
112 drop table t2;
113 #
114 # MDEV-16546 System versioning setting to allow history modification
115 #
116 set @@session.time_zone='+00:00';
117 create table t1(x int primary key) with system versioning;
118 create table t2(y int primary key,
119 row_start timestamp(6) as row start invisible,
120 row_end timestamp(6) as row end invisible,
121 period for system_time (row_start, row_end))
122 with system versioning;
123 create table t3(z int primary key,
124 row_start timestamp(6) as row start,
125 row_end timestamp(6) as row end,
126 period for system_time (row_start, row_end))
127 with system versioning;
128 insert into t1(x, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
129 ERROR 42S22: Unknown column 'row_start' in 'INSERT INTO'
130 insert into t2(y, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
131 ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
132 set @@system_versioning_insert_history= 1;
133 show create table t1;
134 Table Create Table
135 t1 CREATE TABLE `t1` (
136 `x` int(11) NOT NULL,
137 PRIMARY KEY (`x`)
138 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
139 insert into t1(x, row_start, row_end) values (3, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
140 insert into t2(y, row_start, row_end) values (4, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
141 insert into t3 values (5, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
142 insert into t3 values (5, '1980-01-02 00:00:00', '1980-01-01 00:00:01');
143 ERROR HY000: Incorrect system-version range 'row_start' value: '1980-01-02 00:00:00.000000' and 'row_end' value: '1980-01-01 00:00:01.000000'
144 select x, row_start, row_end from t1 for system_time all;
145 x row_start row_end
146 3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
147 select y, row_start, row_end from t2 for system_time all;
148 y row_start row_end
149 4 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
150 select z, row_start, row_end from t3 for system_time all;
151 z row_start row_end
152 5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
153 insert into t1(x) values (1);
154 insert into t2(y) values (1);
155 update t1 set x= x + 1;
156 update t1 set row_start= '1971-01-01 00:00:00';
157 ERROR 42S22: Unknown column 'row_start' in 'SET'
158 update t2 set row_start= '1971-01-01 00:00:00';
159 ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
160 insert t1 (x) values (2) on duplicate key update x= 3, row_end= '1970-01-01 00:00:00';
161 ERROR 42S22: Unknown column 'row_end' in 'UPDATE'
162 insert t2 (y) values (1) on duplicate key update y= 3, row_end= '1970-01-01 00:00:00';
163 ERROR HY000: The value specified for generated column 'row_end' in table 't2' has been ignored
164 insert t2 (y,row_end) values (1, '1970-01-01 00:00:00') on duplicate key update y= 3;
165 ERROR HY000: The value specified for generated column 'row_end' in table 't2' has been ignored
166 insert into t1 values (4);
167 insert into t1 set x= 5, row_start= '1980-01-01 00:00:00', row_end= '1980-01-01 00:00:01';
168 insert into t1(x, row_start, row_end) values (6, '1980-01-01 00:00:01', '1980-01-01 00:00:00');
169 ERROR HY000: Incorrect system-version range 'row_start' value: '1980-01-01 00:00:01.000000' and 'row_end' value: '1980-01-01 00:00:00.000000'
170 insert into t1(x, row_start, row_end) values (7, '1980-01-01 00:00:11', '1980-01-01 00:00:11');
171 ERROR HY000: Incorrect system-version range 'row_start' value: '1980-01-01 00:00:11.000000' and 'row_end' value: '1980-01-01 00:00:11.000000'
172 insert into t1(x, row_start) values (8, '1980-01-01 00:00:22');
173 insert into t1(x, row_end) values (9, '1980-01-01 00:00:33');
174 ERROR HY000: Incorrect system-version range 'row_start' value: 'now'
175 insert into t1(x, row_end) values (10, MAX_TIMESTAMP);
176 select x, check_row_ts(row_start, row_end) from t1 for system_time all order by x;
177 x check_row_ts(row_start, row_end)
178 1 HISTORICAL ROW
179 2 CURRENT ROW
180 3 HISTORICAL ROW
181 4 CURRENT ROW
182 5 HISTORICAL ROW
183 8 CURRENT ROW
184 10 CURRENT ROW
185 select x, row_start, row_end from t1 for system_time all
186 where x > 1 and row_end < MAX_TIMESTAMP order by x, row_start, row_end;
187 x row_start row_end
188 3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
189 5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
190 # Direct insert is not possible for TRX_ID versioning
191 create or replace table t2(y int primary key,
192 row_start bigint unsigned as row start,
193 row_end bigint unsigned as row end,
194 period for system_time (row_start, row_end))
195 with system versioning engine innodb;
196 insert into t2(y, row_start, row_end) values (0, 1, 2);
197 ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
198 set @@system_versioning_insert_history= 0;
199 ## INSERT..SELECT
200 create or replace table t2 like t1;
201 set @@system_versioning_insert_history= 1;
202 insert into t2 (x, row_start, row_end) select x, row_start, row_end from t1 for system_time all;
203 select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x;
204 x check_row_ts(row_start, row_end)
205 1 HISTORICAL ROW
206 2 CURRENT ROW
207 3 HISTORICAL ROW
208 4 CURRENT ROW
209 5 HISTORICAL ROW
210 8 CURRENT ROW
211 10 CURRENT ROW
212 select x, row_start, row_end from t2 for system_time all
213 where x > 1 and row_end < MAX_TIMESTAMP order by x, row_start, row_end;
214 x row_start row_end
215 3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
216 5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
217 set @@system_versioning_insert_history= 0;
218 # REPLACE / REPLACE .. SELECT
219 create or replace table t2(a int primary key,
220 row_start timestamp(6) as row start invisible,
221 row_end timestamp(6) as row end invisible,
222 period for system_time (row_start, row_end))
223 with system versioning;
224 replace into t2 (a, row_start, row_end) values (1, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
225 ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
226 replace into t2 (a, row_start, row_end) select x, row_start, row_end from t1;
227 ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
228 create or replace table t2 (a int primary key) with system versioning;
229 replace into t2 (a, row_start, row_end) values (1, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
230 ERROR 42S22: Unknown column 'row_start' in 'INSERT INTO'
231 replace into t2 (a, row_start, row_end) select x, row_start, row_end from t1;
232 ERROR 42S22: Unknown column 'row_start' in 'INSERT INTO'
233 set @@system_versioning_insert_history= 1;
234 # REPLACE ignores system_versioning_insert_history
235 replace into t2 (a, row_end) values (0, '1980-01-01 00:00:00');
236 ERROR 42S22: Unknown column 'row_end' in 'INSERT INTO'
237 replace into t3 (z, row_start) values (0, '1980-01-01 00:00:00');
238 ERROR HY000: The value specified for generated column 'row_start' in table 't3' has been ignored
239 replace into t3 values (0, '1980-01-01 00:00:00', '1981-01-01 00:00:00');
240 ERROR HY000: The value specified for generated column 'row_start' in table 't3' has been ignored
241 # LOAD DATA
242 select x, row_start, row_end into outfile 'DATAFILE' from t1 for system_time all;
243 create or replace table t2 like t1;
244 show create table t2;
245 Table Create Table
246 t2 CREATE TABLE `t2` (
247 `x` int(11) NOT NULL,
248 PRIMARY KEY (`x`)
249 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
250 set @@system_versioning_insert_history= 1;
251 show create table t2;
252 Table Create Table
253 t2 CREATE TABLE `t2` (
254 `x` int(11) NOT NULL,
255 PRIMARY KEY (`x`)
256 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
257 load data infile 'DATAFILE' into table t2 (x, row_start, row_end);
258 select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x;
259 x check_row_ts(row_start, row_end)
260 1 HISTORICAL ROW
261 2 CURRENT ROW
262 3 HISTORICAL ROW
263 4 CURRENT ROW
264 5 HISTORICAL ROW
265 8 CURRENT ROW
266 10 CURRENT ROW
267 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;
268 row_start = '1980-01-01 00:00:00' row_end = '1980-01-01 00:00:01'
269 1 1
270 # Honor secure_timestamp option
271 # restart: --secure-timestamp=YES
272 set @@system_versioning_insert_history= 1;
273 insert into t3(z, row_start, row_end) values (8, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
274 ERROR HY000: The MariaDB server is running with the --secure-timestamp=YES option so it cannot execute this statement
275 insert into t3 values (8, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
276 ERROR HY000: The MariaDB server is running with the --secure-timestamp=YES option so it cannot execute this statement
277 # restart: --secure-timestamp=REPLICATION
278 create user nobody;
279 grant all privileges on test.* to nobody;
280 set @@system_versioning_insert_history= 1;
281 insert into test.t3(z, row_start, row_end) values (9, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
282 ERROR 42000: Access denied; you need (at least one of) the BINLOG REPLAY privilege(s) for this operation
283 insert into test.t3 values (9, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
284 ERROR 42000: Access denied; you need (at least one of) the BINLOG REPLAY privilege(s) for this operation
285 # restart: --secure-timestamp=SUPER
286 set @@system_versioning_insert_history= 1;
287 insert into test.t3(z, row_start, row_end) values (10, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
288 set @@system_versioning_insert_history= 1;
289 insert into test.t3(z, row_start, row_end) values (7, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
290 ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG REPLAY privilege(s) for this operation
291 insert into test.t3 values (7, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
292 ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG REPLAY privilege(s) for this operation
293 use test;
294 # restart: --secure-timestamp=NO
295 drop tables t1, t2, t3;
296 #
297 # MDEV-29813 REPLACE/IGNORE does not work with historical records in InnoDB
298 #
299 set sql_mode='STRICT_ALL_TABLES';
300 create or replace table t1 (a int) with system versioning;
301 set system_versioning_insert_history= on;
302 insert into t1 (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
303 select a,row_start,row_end into outfile 'mdev29813.txt' from t1 for system_time all;
304 create or replace table t1 (a int primary key) with system versioning;
305 load data infile 'mdev29813.txt' ignore into table t1 (a,row_start,row_end);
306 Warnings:
307 Warning 1062 Duplicate entry '1' for key 'PRIMARY'
308 select a,row_start,row_end from t1 for system_time all;
309 a row_start row_end
310 1 2022-01-01 00:00:00.000000 2023-01-01 00:00:00.000000
311 create or replace table t1 (a int primary key) with system versioning;
312 insert ignore into t1 (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
313 Warnings:
314 Warning 1062 Duplicate entry '1' for key 'PRIMARY'
315 select a,row_start,row_end from t1 for system_time all;
316 a row_start row_end
317 1 2022-01-01 00:00:00.000000 2023-01-01 00:00:00.000000
318 drop table t1;
319 set sql_mode=default;