Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 set @@time_zone='+00:00';
3 --source suite/versioning/sys_time.inc
4
5 # TRT test
6
7 create table t1(
8 x int unsigned,
9 sys_start bigint unsigned as row start invisible,
10 sys_end bigint unsigned as row end invisible,
11 period for system_time (sys_start, sys_end))
12 with system versioning engine=innodb;
13
14 create table t2(x int unsigned) engine=innodb;
15
16 start transaction;
17 insert into t1(x) values(1);
18 commit;
19
20 start transaction;
21 insert into t2(x) values(1);
22 savepoint a;
23 insert into t1(x) values(1);
24 rollback to a;
25 commit;
26
27 insert into t2(x) values (1);
28
29 # virtual columns
30 create or replace table t1 (
31 x int,
32 y int as (x) virtual,
33 sys_trx_start bigint unsigned as row start invisible,
34 sys_trx_end bigint unsigned as row end invisible,
35 period for system_time (sys_trx_start, sys_trx_end)
36 ) engine=innodb with system versioning;
37 insert into t1 values (1, null);
38 update t1 set x= x + 1;
39 select x, y, sys_trx_end = 18446744073709551615 as current from t1 for system_time all;
40
41 create or replace table t1 (
42 x int,
43 row_start timestamp(6) as row start invisible,
44 row_end timestamp(6) as row end invisible,
45 period for system_time (row_start, row_end)
46 ) with system versioning;
47 insert into t1 values (1), (2);
48 --error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
49 insert into t1 (row_start) select row_end from t1;
50 set sql_mode='';
51 insert into t1 (row_start, row_end) values (DEFAULT, 1);
52 set sql_mode=default;
53 --disable_cursor_protocol
54 select @@sql_mode into @saved_mode;
55 --enable_cursor_protocol
56 set sql_mode= '';
57 insert into t1 (x, row_start, row_end) values (3, 4, 5);
58 set sql_mode= @saved_mode;
59 insert into t1 (row_start, row_end) values (DEFAULT, DEFAULT);
60 select * from t1;
61
62 --echo # MDEV-14792 INSERT without column list into table with explicit versioning columns produces bad data
63 create or replace table t1 (
64 i int,
65 s timestamp(6) as row start,
66 e timestamp(6) as row end,
67 c varchar(8),
68 period for system_time(s, e))
69 with system versioning;
70 insert into t1 values (1, null, null, 'foo');
71 --replace_result $sys_time_max SYS_TIME_MAX
72 --eval select i, c, e>LEFT($sys_time_max, 10) AS current_row from t1;
73
74 drop table t1;
75 drop table t2;
76
77 --echo #
78 --echo # MDEV-14871 Server crashes in fill_record / fill_record_n_invoke_before_triggers upon inserting into versioned table with trigger
79 --echo #
80 create or replace table t1 (pk int primary key) with system versioning;
81 create trigger tr before insert on t1 for each row select 1 into @a;
82 insert into t1 values (1),(2);
83 drop table t1;
84
85 #
86 # MDEV-14794 Limitations which the row end as a part of PK imposes due to CURRENT_TIMESTAMP behavior and otherwise
87 #
88 create table t1 (pk int primary key, i int) with system versioning;
89 replace into t1 values (1,10),(1,100),(1,1000);
90 select pk,i,row_end > '2038-01-01' from t1 for system_time all;
91 drop table t1;
92
93 # Cleanup
94 set @@time_zone=default;