Debian Codegraph @

mysql-test/suite/versioning/t/update.test

raw
1 source suite/versioning/engines.inc;
2 source suite/versioning/common.inc;
3
4 --disable_query_log
5 call mtr.add_suppression("InnoDB: Transaction was aborted due to ");
6 --enable_query_log
7
8 replace_result $sys_datatype_expl SYS_DATATYPE;
9 eval create table t1(
10 x int unsigned,
11 y int unsigned,
12 sys_trx_start $sys_datatype_expl as row start invisible,
13 sys_trx_end $sys_datatype_expl as row end invisible,
14 period for system_time (sys_trx_start, sys_trx_end))
15 with system versioning;
16
17 insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000);
18 select x, y from t1 order by x, y;
19 update t1 set y = y + 1 where x > 7;
20 select x, y from t1 order by x, y;
21 select x, y from t1 for system_time all order by sys_trx_end, x, y;
22 drop table t1;
23
24 replace_result $sys_datatype_expl SYS_DATATYPE;
25 eval create table t1 (
26 id bigint primary key,
27 x int,
28 y int without system versioning,
29 sys_trx_start $sys_datatype_expl as row start invisible,
30 sys_trx_end $sys_datatype_expl as row end invisible,
31 period for system_time (sys_trx_start, sys_trx_end))
32 with system versioning;
33 set timestamp= unix_timestamp('2000-01-01 00:00:00');
34 insert into t1 values(1, 1, 1);
35 --disable_cursor_protocol
36 select sys_trx_start into @tmp1 from t1;
37 --enable_cursor_protocol
38 set timestamp= unix_timestamp('2000-01-01 01:00:00');
39 update t1 set x= 11, y= 11 where id = 1;
40 select @tmp1 < sys_trx_start as A1, x, y from t1;
41 --disable_cursor_protocol
42 select sys_trx_start into @tmp1 from t1;
43 --enable_cursor_protocol
44 set timestamp= unix_timestamp('2000-01-01 02:00:00');
45 update t1 set y= 1 where id = 1;
46 select @tmp1 = sys_trx_start as A2, x from t1;
47 drop table t1;
48 set timestamp= default;
49
50 replace_result $sys_datatype_expl SYS_DATATYPE;
51 eval create table t1 (
52 x int,
53 y int,
54 sys_trx_start $sys_datatype_expl as row start invisible,
55 sys_trx_end $sys_datatype_expl as row end invisible,
56 period for system_time (sys_trx_start, sys_trx_end)
57 ) with system versioning;
58
59 insert into t1 (x, y) values (1, 1), (2, 1), (3, 1), (4, 1), (5, 1);
60
61 start transaction;
62 update t1 set y= y + 1 where x = 3;
63 update t1 set y= y + 1 where x = 2;
64 update t1 set y= y + 1 where x = 3;
65 update t1 set y= y + 1 where x > 3;
66 update t1 set y= y + 1 where x > 4;
67 commit;
68
69 replace_result $sys_datatype_max MAXVAL;
70 eval select x, y, sys_trx_end = $sys_datatype_max as current from t1 for system_time all order by sys_trx_end, x, y;
71
72 drop table t1;
73
74 replace_result $sys_datatype_expl SYS_DATATYPE;
75 eval create table t1 (
76 id int primary key auto_increment,
77 x int,
78 sys_trx_start $sys_datatype_expl as row start invisible,
79 sys_trx_end $sys_datatype_expl as row end invisible,
80 period for system_time (sys_trx_start, sys_trx_end))
81 with system versioning;
82
83 set @t0= now(6);
84 insert into t1 (x) values (1);
85 set @t1= now(6);
86 update t1 set x= 2 where id = 1;
87 set @t2= now(6);
88 update t1 set x= 3 where id = 1;
89
90 select x from t1 for system_time as of timestamp @t0;
91 select x from t1 for system_time as of timestamp @t1;
92 select x from t1 for system_time as of timestamp @t2;
93 select x from t1 for system_time as of timestamp now(6);
94
95 drop table t1;
96
97 replace_result $sys_datatype_expl SYS_DATATYPE;
98 eval create table t1(
99 x int unsigned,
100 sys_trx_end $sys_datatype_expl as row end invisible,
101 sys_trx_start $sys_datatype_expl as row start invisible,
102 y int unsigned,
103 period for system_time (sys_trx_start, sys_trx_end),
104 primary key(x, y))
105 with system versioning;
106 insert into t1(x, y) values (1, 1000), (3, 3000), (4, 4000), (5, 5000);
107 insert into t1(x, y) values(3, 3000) on duplicate key update y = y+1;
108 insert into t1(x, y) values(4, 4444) on duplicate key update y = y+1;
109 select x, y from t1 for system_time all order by sys_trx_end, x, y;
110 select x, y from t1 order by x, y;
111 drop table t1;
112
113 replace_result $sys_datatype_expl SYS_DATATYPE;
114 eval create table t1 (
115 x int unsigned,
116 y int unsigned,
117 sys_trx_start $sys_datatype_expl as row start invisible,
118 sys_trx_end $sys_datatype_expl as row end invisible,
119 period for system_time (sys_trx_start, sys_trx_end))
120 with system versioning;
121 create table t2 like t1;
122 insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000);
123 insert into t2(x, y) values (1, 1010), (2, 2010), (3, 3010), (4, 4010), (5, 5010), (6, 6010), (7, 7010), (8, 8010), (9, 9010);
124 update t1, t2 set t1.y = t1.x + t1.y, t2.y = t2.x + t2.y where t1.x > 7 and t2.x < 7;
125 select x, y from t1 for system_time all order by sys_trx_end, x, y;
126 select x, y from t1 order by x, y;
127 select x, y from t2 for system_time all order by sys_trx_end, x, y;
128 select x, y from t2 order by x, y;
129 drop table t1;
130 drop table t2;
131
132 replace_result $sys_datatype_expl SYS_DATATYPE;
133 eval create table t1 (
134 id bigint primary key without system versioning,
135 name varchar(128),
136 salary bigint without system versioning,
137 sys_trx_start $sys_datatype_expl as row start invisible,
138 sys_trx_end $sys_datatype_expl as row end invisible,
139 period for system_time (sys_trx_start, sys_trx_end))
140 with system versioning;
141 create table t2 like t1;
142
143 insert into t1 values (1, "Jeremy", 3000);
144 insert into t2 values (1, "Jeremy", 4000);
145
146 --disable_cursor_protocol
147 select sys_trx_start into @tmp1 from t1;
148 select sys_trx_start into @tmp2 from t2;
149 --enable_cursor_protocol
150 update t1, t2 set t1.name= "Jerry", t2.name= "Jerry" where t1.id = t2.id and t1.name = "Jeremy";
151 select @tmp1 < sys_trx_start as A1, name from t1;
152 select @tmp2 < sys_trx_start as A2, name from t2;
153
154 --disable_cursor_protocol
155 select sys_trx_start into @tmp1 from t1;
156 select sys_trx_start into @tmp2 from t2;
157 --enable_cursor_protocol
158 update t1, t2 set t1.salary= 2500, t2.salary= 2500 where t1.id = t2.id and t1.name = "Jerry";
159 select @tmp1 = sys_trx_start as B1, salary from t1;
160 select @tmp2 = sys_trx_start as B2, salary from t2;
161
162 drop table t1;
163 drop table t2;
164
165 --echo # Ensure FTS retains correct history
166 replace_result $sys_datatype_expl SYS_DATATYPE;
167 eval create table t1 (
168 x int, y text, fulltext (y),
169 row_start $sys_datatype_expl as row start invisible,
170 row_end $sys_datatype_expl as row end invisible,
171 period for system_time (row_start, row_end))
172 with system versioning engine innodb;
173 insert into t1 values (1, repeat('LONG', 2048));
174 update t1 set x= x + 1;
175 select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y;
176 update t1 set y= 'SHORT';
177 select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y;
178 drop tables t1;
179
180 --echo ### Issue tempesta-tech/mariadb#365, bug 7 (duplicate of historical row)
181 create or replace table t1 (a int primary key, b int)
182 with system versioning engine myisam;
183 insert into t1 (a) values (1);
184
185 replace t1 values (1,2),(1,3),(2,4);
186
187 --echo #
188 --echo # MDEV-14829 Assertion `0' failed in Protocol::end_statement upon concurrent UPDATE
189 --echo #
190 set @old_lock_wait_timeout= @@innodb_lock_wait_timeout;
191 set @@innodb_lock_wait_timeout= 100000000;
192 create or replace table t1 (pk int, a char(3), b char(3), primary key(pk))
193 engine=innodb with system versioning;
194
195 insert into t1 (pk) values (1);
196 connect (con1,localhost,root,,test);
197 start transaction;
198 select * from t1 for update;
199 connection default;
200 send update t1 set b = 'foo';
201 connection con1;
202 let $wait_condition= select count(*) from information_schema.innodb_lock_waits;
203 source include/wait_condition.inc;
204 error ER_LOCK_DEADLOCK;
205 update t1 set a = 'bar';
206 disconnect con1;
207 connection default;
208 reap;
209 drop table t1;
210 set @@innodb_lock_wait_timeout= @old_lock_wait_timeout;
211
212 --echo #
213 --echo # MDEV-19406 Assertion on updating view of join with versioned table
214 --echo #
215 --disable_warnings
216 create or replace table t1 (pk int primary key, a date, b int, index(b)) engine=innodb with system versioning;
217 create or replace table t2 (c int);
218 create or replace view v as select * from t1 join t2;
219
220 insert into t1 (pk) values (1);
221 update t1 set a= '2012-12-12';
222 update v set a= '2000-01-01' order by b limit 1; # point of failure
223 drop view v;
224 drop table t1, t2;
225 --enable_warnings
226
227 --echo #
228 --echo # MDEV-20441 ER_CRASHED_ON_USAGE upon update on versioned Aria table
229 --echo #
230 create or replace table t1 (a varchar(8))
231 engine=aria row_format=fixed
232 with system versioning;
233
234 insert into t1 (a) values ('foo');
235 update t1 set a = 'bar';
236 drop table t1;
237
238 --echo #
239 --echo # MDEV-21147 Assertion `marked_for_read()' upon UPDATE on versioned table via view
240 --echo #
241 create or replace table t1 (
242 pk int, a char(8), b char(8),
243 primary key (pk)
244 ) with system versioning;
245
246 create or replace view v1 as select * from t1;
247 insert into t1 values (1, null, 'd') , (2, null, 'i') ;
248 update v1 set a= null where b = '';
249
250 create or replace table t1 (id int, k int, primary key (id)) engine=innodb with system versioning;
251 insert into t1 values (1,1),(2,2);
252 create or replace view v1 as select * from t1;
253 update v1 set id= 2 where k = 0;
254
255 create or replace table t1 (a int) with system versioning;
256 create or replace view v1 as select * from t1;
257 create or replace procedure sp() update v1 set xx = 1;
258 --error ER_BAD_FIELD_ERROR
259 call sp;
260 --error ER_BAD_FIELD_ERROR
261 call sp;
262
263 # cleanup
264 drop procedure sp;
265 drop view v1;
266 drop table t1;
267
268 --echo #
269 --echo # MDEV-21342 Assertion in set_ok_status() upon spatial field error on system-versioned table
270 --echo #
271 create or replace table t1 (f point, key(f)) with system versioning engine=myisam;
272 insert t1 values (null);
273 --error ER_CANT_CREATE_GEOMETRY_OBJECT
274 update t1 set f = null where f = 'foo';
275
276 # cleanup
277 drop table t1;
278
279 --echo #
280 --echo # MDEV-22061 InnoDB: Assertion of missing row in sec index row_start upon REPLACE on a system-versioned table
281 --echo #
282 create or replace table t1 (
283 a int,
284 b int,
285 row_start bigint(20) unsigned generated always as row start,
286 row_end bigint(20) unsigned generated always as row end,
287 unique key (b,row_end),
288 key (row_start),
289 period for system_time (row_start,row_end)
290 ) engine=innodb with system versioning;
291
292 insert into t1 (a, b) values (1, 2);
293 replace into t1 (a, b) values (3, 2);
294 replace into t1 (a, b) values (4, 2);
295
296 # cleanup
297 drop table t1;
298
299 --echo #
300 --echo # MDEV-20661 Virtual fields are not recalculated on system fields value assignment
301 --echo #
302
303 replace_result $sys_datatype_expl SYS_DATATYPE;
304 eval create table t1 (
305 a int,
306 row_start $sys_datatype_expl as row start invisible,
307 row_end $sys_datatype_expl as row end invisible,
308 period for system_time (row_start, row_end),
309 v1 bigint unsigned as (a ^ row_start) unique,
310 v2 bigint unsigned as (a ^ row_end) unique
311 ) engine=innodb with system versioning;
312
313 insert into t1 (a) values (1), (2);
314 update ignore t1 set a= 3;
315 delete history from t1;
316
317 # cleanup
318 drop table t1;
319
320 --echo #
321 --echo # MDEV-23446 UPDATE does not insert history row if the row is not changed
322 --echo #
323 replace_result $sys_datatype_expl SYS_DATATYPE;
324 eval create table t1 (
325 a int,
326 row_start $sys_datatype_expl as row start invisible,
327 row_end $sys_datatype_expl as row end invisible,
328 period for system_time (row_start, row_end)) with system versioning;
329 insert into t1 values (1);
330 update t1 set a= 1;
331 select *, check_row(row_start, row_end) from t1 for system_time all order by row_end;
332
333 --echo # multi-update
334 create or replace table t2 like t1;
335 create or replace table t3 like t1;
336 insert into t2 values (1);
337 insert into t3 values (1);
338 update t2, t3 set t2.a= 1, t3.a= 1 where t2.a = t3.a;
339 select *, check_row(row_start, row_end) from t2 for system_time all order by row_end;
340 select *, check_row(row_start, row_end) from t2 for system_time all order by row_end;
341
342 # cleanup
343 drop tables t1, t2, t3;
344
345 --echo #
346 --echo # MDEV-26778 row_start is not updated in current row for InnoDB
347 --echo #
348 create or replace table t1 (x int) with system versioning;
349 insert t1 values (1);
350 update t1 set x= 1;
351 --disable_cursor_protocol
352 select row_start into @r from t1;
353 --enable_cursor_protocol
354 select check_row_ts(row_start, row_end) from t1 for system_time all where row_start = @r;
355 drop table t1;
356
357 --echo #
358 --echo # MDEV-22973 Assertion in compare_record upon multi-update involving versioned table via view
359 --echo #
360 create or replace table t1 (a int, primary key (a)) engine=myisam;
361 insert into t1 values (0);
362 create or replace table t2 (pk int, b int, primary key (pk), key(b)) engine=innodb with system versioning;
363 insert into t2 values (1, 0), (2, 0);
364 create or replace view v as select a, b from t1, t2;
365 update v set b= null where a = 0 order by b;
366 # cleanup
367 drop view v;
368 drop table t1, t2;
369
370 --echo #
371 --echo # MDEV-24522 Assertion `inited==NONE' fails upon UPDATE on versioned table with unique blob
372 --echo #
373 create table t1 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning;
374 insert into t1 values (1, 1, 'foo'), (2, 11, 'bar');
375
376 update t1 set a = 3 where b <= 9;
377 update t1 set a = 3 where b <= 10;
378
379 # cleanup
380 drop table t1;
381
382 create table t1 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning;
383 create table t2 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning;
384 insert into t1 values (1, 1, 'foo'), (2, 11, 'bar');
385 insert into t2 values (1, 1, 'foo'), (2, 11, 'bar');
386
387 update t1 set a = 3 where b <= 9;
388 update t2 set a = 3 where b <= 9;
389 update t1, t2 set t1.a = 3, t2.a = 3 where t1.b <= 10 and t2.b <= 10 and t1.b = t2.b;
390
391 # cleanup
392 drop tables t1, t2;
393
394 --echo #
395 --echo # MDEV-23100 ODKU of non-versioning column inserts history row
396 --echo #
397 create table t1 (
398 x int unique,
399 y int without system versioning
400 ) with system versioning;
401
402 insert into t1 (x, y) values ('1', '1');
403 insert into t1 (x, y) values ('1', '2')
404 on duplicate key update y = 3;
405
406 select x, y, check_row_ts(row_start, row_end) from t1 for system_time all order by row_end;
407
408 drop table t1;
409
410 --echo #
411 --echo # MDEV-25644 UPDATE not working properly on transaction precise system versioned table
412 --echo #
413 create or replace table t1 (nid int primary key, nstate int, ntype int) engine innodb;
414 --replace_result $sys_datatype_expl SYS_DATATYPE
415 eval alter table t1 add
416 row_start $sys_datatype_expl generated always as row start invisible,
417 add row_end $sys_datatype_expl generated always as row end invisible,
418 add period for system_time(row_start, row_end),
419 add system versioning;
420 insert into t1 values (1, 1, 1);
421 select nid, nstate, check_row(row_start, row_end) from t1 for system_time all order by row_start, row_end;
422 start transaction;
423 update t1 set nstate= nstate where nid = 1;
424 select nid, nstate, check_row(row_start, row_end) from t1 for system_time all order by row_start, row_end;
425 --echo # Bug: ERROR 1761 (23000): Foreign key constraint for table 'xxx', record '1-18446744073709551615' would lead to a duplicate entry in table 'xxx', key 'PRIMARY'
426 update t1 set nstate= 3 where nid= 1;
427 # Under one transaction trx_id generates only one history row, that differs from timestamp
428 select nid, nstate, check_row(row_start, row_end) from t1 for system_time all order by row_start, row_end;
429 commit;
430 drop tables t1;
431
432 --echo # End of 10.4 tests
433
434 source suite/versioning/common_finish.inc;