Debian Codegraph @

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

raw
1 set default_storage_engine= innodb;
2 create or replace table t1 (
3 x int,
4 sys_trx_start bigint(20) unsigned as row start invisible,
5 sys_trx_end bigint(20) unsigned as row end invisible,
6 period for system_time (sys_trx_start, sys_trx_end)
7 ) with system versioning;
8 # No history inside the transaction
9 start transaction;
10 insert into t1 (x) values (1);
11 update t1 set x= x + 1;
12 update t1 set x= x + 1;
13 commit;
14 select *, sys_trx_start > 1, sys_trx_end from t1 for system_time all;
15 x sys_trx_start > 1 sys_trx_end
16 3 1 18446744073709551615
17 # ALTER ADD SYSTEM VERSIONING should write to mysql.transaction_registry
18 set @@system_versioning_alter_history=keep;
19 create or replace table t1 (x int);
20 insert into t1 values (1);
21 alter table t1
22 add column s bigint unsigned as row start,
23 add column e bigint unsigned as row end,
24 add period for system_time(s, e),
25 add system versioning,
26 algorithm=inplace;
27 select s from t1 into @trx_start;
28 select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start;
29 count(*) = 1
30 1
31 create or replace table t1 (x int);
32 select count(*) from mysql.transaction_registry into @tmp;
33 alter table t1
34 add column s bigint unsigned as row start,
35 add column e bigint unsigned as row end,
36 add period for system_time(s, e),
37 add system versioning,
38 algorithm=inplace;
39 select count(*) = @tmp from mysql.transaction_registry;
40 count(*) = @tmp
41 1
42 create or replace table t1 (x int);
43 insert into t1 values (1);
44 alter table t1
45 add column s bigint unsigned as row start,
46 add column e bigint unsigned as row end,
47 add period for system_time(s, e),
48 add system versioning,
49 algorithm=copy;
50 select s from t1 into @trx_start;
51 select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start;
52 count(*) = 1
53 1
54 create or replace table t1 (x int);
55 select count(*) from mysql.transaction_registry into @tmp;
56 alter table t1
57 add column s bigint unsigned as row start,
58 add column e bigint unsigned as row end,
59 add period for system_time(s, e),
60 add system versioning,
61 algorithm=copy;
62 select count(*) = @tmp + 1 from mysql.transaction_registry;
63 count(*) = @tmp + 1
64 1
65 # TRX_ID to TIMESTAMP versioning switch
66 create or replace table t1 (
67 x int,
68 sys_start bigint unsigned as row start invisible,
69 sys_end bigint unsigned as row end invisible,
70 period for system_time (sys_start, sys_end)
71 ) with system versioning;
72 insert into t1 values (1);
73 alter table t1 drop column sys_start, drop column sys_end;
74 select row_end = 18446744073709551615 as transaction_based from t1 for system_time all;
75 transaction_based
76 1
77 # Simple vs SEES algorithms
78 create or replace table t1 (
79 x int,
80 sys_start bigint(20) unsigned as row start invisible,
81 sys_end bigint(20) unsigned as row end invisible,
82 period for system_time (sys_start, sys_end)
83 ) with system versioning;
84 INSERT INTO t1 VALUES(100);
85 set transaction isolation level read committed;
86 start transaction;
87 insert into t1 values (1);
88 connect con1,localhost,root,,test;
89 set transaction isolation level read committed;
90 start transaction;
91 insert into t1 values (2);
92 connect con2,localhost,root,,test;
93 set transaction isolation level read committed;
94 start transaction;
95 insert into t1 values (3);
96 commit;
97 disconnect con2;
98 connection default;
99 set @ts1= sysdate(6);
100 connection con1;
101 commit;
102 disconnect con1;
103 connection default;
104 set @ts2= sysdate(6);
105 commit;
106 set @ts3= sysdate(6);
107 select sys_start from t1 where x = 1 into @trx_id1;
108 select sys_start from t1 where x = 2 into @trx_id2;
109 select sys_start from t1 where x = 3 into @trx_id3;
110 select @trx_id1 < @trx_id2, @trx_id2 < @trx_id3;
111 @trx_id1 < @trx_id2 @trx_id2 < @trx_id3
112 1 1
113 select @ts1 < @ts2, @ts2 < @ts3;
114 @ts1 < @ts2 @ts2 < @ts3
115 1 1
116 # MVCC is resolved
117 select * from t1 for system_time as of transaction @trx_id1;
118 x
119 100
120 1
121 2
122 3
123 select * from t1 for system_time as of timestamp @ts1;
124 x
125 100
126 3
127 select * from t1 for system_time as of transaction @trx_id2;
128 x
129 100
130 2
131 3
132 select * from t1 for system_time as of timestamp @ts2;
133 x
134 100
135 2
136 3
137 select * from t1 for system_time as of transaction @trx_id3;
138 x
139 100
140 3
141 select * from t1 for system_time as of timestamp @ts3;
142 x
143 100
144 1
145 2
146 3
147 #
148 # MDEV-15427 IB: TRX_ID based operations inside transaction generate history
149 #
150 create or replace table t1(
151 x int(10),
152 row_start bigint(20) unsigned as row start,
153 row_end bigint(20) unsigned as row end,
154 period for system_time(row_start, row_end)
155 ) with system versioning;
156 begin;
157 insert into t1 (x) values (1);
158 delete from t1;
159 commit;
160 select x from t1 for system_time all;
161 x
162 insert into t1 (x) values (2);
163 begin;
164 update t1 set x= 3;
165 update t1 set x= 4;
166 commit;
167 select x, row_start < row_end from t1 for system_time all;
168 x row_start < row_end
169 4 1
170 2 1
171 #
172 # MDEV-15951 system versioning by trx id doesn't work with partitioning
173 # currently trx_id does not support partitioning by system_time
174 #
175 create or replace table t1(
176 i int,
177 row_start bigint unsigned generated always as row start,
178 row_end bigint unsigned generated always as row end,
179 period for system_time(row_start, row_end)
180 ) engine=InnoDB with system versioning partition by system_time (
181 partition p0 history,
182 partition pn current
183 );
184 ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1`
185 create or replace table t1(
186 i int,
187 row_start bigint unsigned generated always as row start,
188 row_end bigint unsigned generated always as row end,
189 period for system_time(row_start, row_end)
190 ) engine=InnoDB with system versioning;
191 alter table t1 partition by system_time (
192 partition p0 history,
193 partition pn current
194 );
195 ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1`
196 drop table t1;
197 create or replace table t (
198 a int primary key,
199 row_start bigint unsigned as row start invisible,
200 row_end bigint unsigned as row end invisible,
201 period for system_time(row_start, row_end)
202 ) engine=innodb with system versioning
203 partition by key() (
204 partition p1,
205 partition p2
206 );
207 ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
208 create or replace table t (
209 a int primary key,
210 row_start bigint unsigned as row start invisible,
211 row_end bigint unsigned as row end invisible,
212 period for system_time(row_start, row_end)
213 ) engine=innodb with system versioning
214 partition by key(a, row_start) (
215 partition p1,
216 partition p2
217 );
218 ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
219 create or replace table t (
220 a int primary key,
221 row_start bigint unsigned as row start invisible,
222 row_end bigint unsigned as row end invisible,
223 period for system_time(row_start, row_end)
224 ) engine=innodb with system versioning
225 partition by hash(a + row_end * 2) (
226 partition p1,
227 partition p2
228 );
229 ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
230 create or replace table t (
231 a int primary key,
232 row_start bigint unsigned as row start invisible,
233 row_end bigint unsigned as row end invisible,
234 period for system_time(row_start, row_end)
235 ) engine=innodb with system versioning
236 partition by range columns (a, row_start) (
237 partition p1 values less than (100, 100)
238 );
239 ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
240 #
241 # MDEV-16010 Too many rows with AS OF point_in_the_past_or_NULL
242 #
243 create or replace table t1 (
244 x int,
245 row_start bigint unsigned as row start invisible,
246 row_end bigint unsigned as row end invisible,
247 period for system_time (row_start, row_end)
248 ) with system versioning engine innodb;
249 insert into t1 (x) values (1);
250 delete from t1;
251 select * from t1 for system_time as of timestamp'1990-1-1 00:00';
252 x
253 select * from t1 for system_time as of NULL;
254 x
255 # MDEV-16024 transaction_registry.begin_timestamp is wrong for explicit transactions
256 create or replace table t1 (
257 x int(11) default null,
258 row_start bigint(20) unsigned generated always as row start invisible,
259 row_end bigint(20) unsigned generated always as row end invisible,
260 period for system_time (row_start, row_end)
261 ) engine=innodb with system versioning;
262 begin;
263 set @ts1= now(6);
264 insert into t1 values (1);
265 commit;
266 select row_start from t1 into @trx_id;
267 select trt_begin_ts(@trx_id) <= @ts1 as BEGIN_TS_GOOD;
268 BEGIN_TS_GOOD
269 1
270 drop table t1;
271 #
272 # MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs
273 #
274 CREATE TABLE t1 (
275 x INT,
276 sys_trx_start BIGINT UNSIGNED AS ROW START,
277 sys_trx_end BIGINT UNSIGNED AS ROW END,
278 PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
279 ) WITH SYSTEM VERSIONING ENGINE=INNODB;
280 INSERT INTO t1 (x) VALUES (1);
281 SET @ts= DATE_ADD(NOW(), INTERVAL 1 YEAR);
282 EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION @ts;
283 id select_type table type possible_keys key key_len ref rows filtered Extra
284 1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where
285 Warnings:
286 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`)
287 EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TIMESTAMP @ts;
288 id select_type table type possible_keys key key_len ref rows filtered Extra
289 1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where
290 Warnings:
291 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`)
292 EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF @ts;
293 id select_type table type possible_keys key key_len ref rows filtered Extra
294 1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where
295 Warnings:
296 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`)
297 DROP TABLE t1;
298 #
299 # Testing AS OF with expressions of various kinds and data types
300 #
301 CREATE TABLE t1
302 (
303 x INT,
304 sys_trx_start BIGINT UNSIGNED AS ROW START INVISIBLE,
305 sys_trx_end BIGINT UNSIGNED AS ROW END INVISIBLE,
306 PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
307 ) WITH SYSTEM VERSIONING;
308 INSERT INTO t1 VALUES (1);
309 CREATE TABLE t2
310 (
311 x INT,
312 sys_trx_start TIMESTAMP(6) AS ROW START INVISIBLE,
313 sys_trx_end TIMESTAMP(6) AS ROW END INVISIBLE,
314 PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
315 ) WITH SYSTEM VERSIONING;
316 INSERT INTO t2 VALUES (1);
317 #
318 # ROW is not supported
319 #
320 SELECT * FROM t1 FOR SYSTEM_TIME AS OF (1,1);
321 ERROR HY000: Illegal parameter data type row for operation 'FOR SYSTEM_TIME'
322 SELECT * FROM t2 FOR SYSTEM_TIME AS OF (1,1);
323 ERROR HY000: Illegal parameter data type row for operation 'FOR SYSTEM_TIME'
324 #
325 # DOUBLE is not supported, use explicit CAST
326 #
327 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION RAND();
328 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
329 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION (RAND());
330 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
331 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND());
332 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
333 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION RAND();
334 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
335 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION (RAND());
336 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
337 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND());
338 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
339 #
340 # DECIMAL is not supported, use explicit CAST
341 #
342 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION 10.1;
343 ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
344 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1);
345 ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
346 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION 10.1;
347 ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
348 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1);
349 ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
350 #
351 # YEAR is not supported, use explicit CAST
352 #
353 BEGIN NOT ATOMIC
354 DECLARE var YEAR;
355 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
356 END;
357 $$
358 ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME'
359 BEGIN NOT ATOMIC
360 DECLARE var YEAR;
361 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
362 END;
363 $$
364 ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME'
365 #
366 # ENUM is not supported, use explicit CAST
367 #
368 BEGIN NOT ATOMIC
369 DECLARE var ENUM('xxx') DEFAULT 'xxx';
370 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
371 END;
372 $$
373 ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME'
374 BEGIN NOT ATOMIC
375 DECLARE var ENUM('xxx') DEFAULT 'xxx';
376 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
377 END;
378 $$
379 ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME'
380 #
381 # SET is not supported, use explicit CAST
382 #
383 BEGIN NOT ATOMIC
384 DECLARE var SET('xxx') DEFAULT 'xxx';
385 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
386 END;
387 $$
388 ERROR HY000: Illegal parameter data type set for operation 'FOR SYSTEM_TIME'
389 BEGIN NOT ATOMIC
390 DECLARE var SET('xxx') DEFAULT 'xxx';
391 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
392 END;
393 $$
394 ERROR HY000: Illegal parameter data type set for operation 'FOR SYSTEM_TIME'
395 BEGIN NOT ATOMIC
396 DECLARE var BIT(10);
397 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
398 END;
399 $$
400 ERROR HY000: Transaction-precise system versioning for `t2` is not supported
401 #
402 # String literals resolve to TIMESTAMP
403 #
404 SELECT * FROM t1 FOR SYSTEM_TIME AS OF '2107-12-30 00:00:00';
405 x
406 1
407 SELECT * FROM t2 FOR SYSTEM_TIME AS OF '2107-12-30 00:00:00';
408 x
409 DROP TABLE t1, t2;
410 #
411 # MDEV-16094 Crash when using AS OF with a stored function
412 #
413 CREATE FUNCTION fts() RETURNS DATETIME RETURN '2001-01-01 10:20:30';
414 CREATE FUNCTION ftx() RETURNS BIGINT UNSIGNED RETURN 1;
415 CREATE TABLE ttx
416 (
417 x INT,
418 start_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
419 end_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
420 PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
421 ) ENGINE=InnoDB WITH SYSTEM VERSIONING;
422 CREATE TABLE tts
423 (
424 x INT,
425 start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
426 end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
427 PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
428 ) ENGINE=InnoDB WITH SYSTEM VERSIONING;
429 SELECT * FROM tts FOR SYSTEM_TIME AS OF fts();
430 x start_timestamp end_timestamp
431 SELECT * FROM tts FOR SYSTEM_TIME AS OF TRANSACTION ftx();
432 ERROR HY000: Transaction-precise system versioning for `tts` is not supported
433 SELECT * FROM ttx FOR SYSTEM_TIME AS OF fts();
434 x start_timestamp end_timestamp
435 SELECT * FROM ttx FOR SYSTEM_TIME AS OF TRANSACTION ftx();
436 x start_timestamp end_timestamp
437 DROP TABLE tts;
438 DROP TABLE ttx;
439 DROP FUNCTION fts;
440 DROP FUNCTION ftx;
441 #
442 # MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute
443 #
444 SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
445 CREATE TABLE t (
446 a INT,
447 b INT,
448 row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
449 row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
450 PERIOD FOR SYSTEM_TIME(row_start, row_end)
451 ) WITH SYSTEM VERSIONING ENGINE=INNODB;
452 INSERT INTO t VALUES (1,1);
453 # without table rebuild
454 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
455 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
456 ON c.table_id=t.table_id
457 WHERE t.name='test/t' AND c.name='a';
458 prtype
459 50179
460 ALTER TABLE t
461 CHANGE a a INT WITHOUT SYSTEM VERSIONING;
462 affected rows: 0
463 info: Records: 0 Duplicates: 0 Warnings: 0
464 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
465 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
466 ON c.table_id=t.table_id
467 WHERE t.name='test/t' AND c.name='a';
468 prtype
469 1027
470 UPDATE t SET a=11;
471 SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
472 COUNT(*)
473 1
474 # with table rebuild
475 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
476 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
477 ON c.table_id=t.table_id
478 WHERE t.name='test/t' AND c.name='a';
479 prtype
480 1027
481 ALTER TABLE t
482 CHANGE a a INT WITH SYSTEM VERSIONING,
483 ADD PRIMARY KEY pk(a);
484 affected rows: 0
485 info: Records: 0 Duplicates: 0 Warnings: 1
486 Warnings:
487 Warning 1280 Name 'pk' ignored for PRIMARY key.
488 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
489 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
490 ON c.table_id=t.table_id
491 WHERE t.name='test/t' AND c.name='a';
492 prtype
493 50435
494 UPDATE t SET a=1;
495 SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
496 COUNT(*)
497 2
498 SHOW CREATE TABLE t;
499 Table Create Table
500 t CREATE TABLE `t` (
501 `a` int(11) NOT NULL,
502 `b` int(11) DEFAULT NULL,
503 `row_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START INVISIBLE,
504 `row_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END INVISIBLE,
505 PRIMARY KEY (`a`,`row_end`),
506 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
508 # handles VIRTUAL columns too
509 CREATE OR REPLACE TABLE t (
510 a INT AS (b + 1),
511 b INT,
512 row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
513 row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
514 PERIOD FOR SYSTEM_TIME(row_start, row_end)
515 ) WITH SYSTEM VERSIONING ENGINE=INNODB;
516 INSERT INTO t VALUES (DEFAULT, 1);
517 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
518 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
519 ON c.table_id=t.table_id
520 WHERE t.name='test/t' AND c.name='b';
521 prtype
522 50179
523 ALTER TABLE t
524 CHANGE b b INT WITHOUT SYSTEM VERSIONING;
525 affected rows: 0
526 info: Records: 0 Duplicates: 0 Warnings: 0
527 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
528 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
529 ON c.table_id=t.table_id
530 WHERE t.name='test/t' AND c.name='b';
531 prtype
532 1027
533 UPDATE t SET b=11;
534 SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
535 COUNT(*)
536 1
537 DROP TABLE t;
538 SET @@SYSTEM_VERSIONING_ALTER_HISTORY=ERROR;
539 SELECT count(*) from mysql.transaction_registry where begin_timestamp>=commit_timestamp;
540 count(*)
541 0
542 # MDEV-18875 Assertion `thd->transaction.stmt.ha_list == __null ||
543 # trans == &thd->transaction.stmt' failed or bogus ER_DUP_ENTRY upon
544 # ALTER TABLE with versioning
545 create or replace table t (x int) engine=innodb;
546 set autocommit= 0;
547 alter table t
548 algorithm=copy,
549 add column row_start bigint unsigned as row start,
550 add column row_end bigint unsigned as row end,
551 add period for system_time(row_start,row_end),
552 with system versioning;
553 set autocommit= 1;
554 # MDEV-18865 Assertion `t->first->versioned_by_id()'
555 # failed in innodb_prepare_commit_versioned
556 create or replace table t (x int) engine=innodb;
557 insert into t values (0);
558 alter table t add `row_start` bigint unsigned as row start,
559 add `row_end` bigint unsigned as row end,
560 add period for system_time(`row_start`,`row_end`),
561 modify x int after row_start,
562 with system versioning;
563 drop table t;
564 #
565 # MDEV-20842 Crash using versioning plugin functions after plugin was removed from server
566 #
567 uninstall plugin test_versioning;
568 select trt_begin_ts(0);
569 ERROR 42000: FUNCTION test.trt_begin_ts does not exist
570 #
571 # MDEV-21650 Non-empty statement transaction on global rollback after TRT update error
572 #
573 create table t1 (s date, e date, period for app(s,e)) engine=innodb;
574 alter table t1
575 add row_start bigint unsigned as row start,
576 add row_end bigint unsigned as row end,
577 add period for system_time(row_start,row_end),
578 with system versioning,
579 add period if not exists for app(x,y);
580 Warnings:
581 Note 1060 Duplicate column name 'app'
582 set transaction isolation level serializable;
583 start transaction;
584 insert into t1 (s,e) values ('2021-07-04','2024-08-18');
585 connect con1,localhost,root,,test;
586 start transaction;
587 insert into t1 (s,e) values ('2018-06-01','2021-09-15');
588 connection default;
589 select * from t1 for system_time as of now();
590 ERROR HY000: TRX_ID ... not found in `mysql.transaction_registry`
591 connection con1;
592 set innodb_lock_wait_timeout= 1, lock_wait_timeout= 1;
593 alter table xx;
594 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
595 disconnect con1;
596 connection default;
597 drop table t1;
598 # End of 10.4 tests
599 #
600 # MDEV-37005 Unexpected ER_TABLE_EXISTS_ERROR on primary or replica upon CREATE OR REPLACE for partitioned table
601 #
602 create table t1 (
603 a int,
604 row_start bigint unsigned generated always as row start,
605 row_end bigint unsigned generated always as row end,
606 period for system_time (row_start, row_end)
607 ) engine=innodb with system versioning
608 partition by hash (a);
609 create or replace table t1 (b int) engine=innodb with system versioning partition by system_time;
610 show create table t1;
611 Table Create Table
612 t1 CREATE TABLE `t1` (
613 `b` int(11) DEFAULT NULL
614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
615 PARTITION BY SYSTEM_TIME
616 PARTITIONS 2
617 drop table t1;
618 # End of 11.8 tests