Debian Codegraph @

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

raw
1 set @@time_zone='+00:00';
2 create table t1(
3 x int unsigned,
4 sys_start bigint unsigned as row start invisible,
5 sys_end bigint unsigned as row end invisible,
6 period for system_time (sys_start, sys_end))
7 with system versioning engine=innodb;
8 create table t2(x int unsigned) engine=innodb;
9 start transaction;
10 insert into t1(x) values(1);
11 commit;
12 start transaction;
13 insert into t2(x) values(1);
14 savepoint a;
15 insert into t1(x) values(1);
16 rollback to a;
17 commit;
18 insert into t2(x) values (1);
19 create or replace table t1 (
20 x int,
21 y int as (x) virtual,
22 sys_trx_start bigint unsigned as row start invisible,
23 sys_trx_end bigint unsigned as row end invisible,
24 period for system_time (sys_trx_start, sys_trx_end)
25 ) engine=innodb with system versioning;
26 insert into t1 values (1, null);
27 update t1 set x= x + 1;
28 select x, y, sys_trx_end = 18446744073709551615 as current from t1 for system_time all;
29 x y current
30 2 2 1
31 1 1 0
32 create or replace table t1 (
33 x int,
34 row_start timestamp(6) as row start invisible,
35 row_end timestamp(6) as row end invisible,
36 period for system_time (row_start, row_end)
37 ) with system versioning;
38 insert into t1 values (1), (2);
39 insert into t1 (row_start) select row_end from t1;
40 ERROR HY000: The value specified for generated column 'row_start' in table 't1' has been ignored
41 set sql_mode='';
42 insert into t1 (row_start, row_end) values (DEFAULT, 1);
43 Warnings:
44 Warning 1906 The value specified for generated column 'row_end' in table 't1' has been ignored
45 set sql_mode=default;
46 select @@sql_mode into @saved_mode;
47 set sql_mode= '';
48 insert into t1 (x, row_start, row_end) values (3, 4, 5);
49 Warnings:
50 Warning 1906 The value specified for generated column 'row_start' in table 't1' has been ignored
51 Warning 1906 The value specified for generated column 'row_end' in table 't1' has been ignored
52 set sql_mode= @saved_mode;
53 insert into t1 (row_start, row_end) values (DEFAULT, DEFAULT);
54 select * from t1;
55 x
56 1
57 2
58 NULL
59 3
60 NULL
61 # MDEV-14792 INSERT without column list into table with explicit versioning columns produces bad data
62 create or replace table t1 (
63 i int,
64 s timestamp(6) as row start,
65 e timestamp(6) as row end,
66 c varchar(8),
67 period for system_time(s, e))
68 with system versioning;
69 insert into t1 values (1, null, null, 'foo');
70 select i, c, e>LEFT(SYS_TIME_MAX, 10) AS current_row from t1;;
71 i c current_row
72 1 foo 1
73 drop table t1;
74 drop table t2;
75 #
76 # MDEV-14871 Server crashes in fill_record / fill_record_n_invoke_before_triggers upon inserting into versioned table with trigger
77 #
78 create or replace table t1 (pk int primary key) with system versioning;
79 create trigger tr before insert on t1 for each row select 1 into @a;
80 insert into t1 values (1),(2);
81 drop table t1;
82 create table t1 (pk int primary key, i int) with system versioning;
83 replace into t1 values (1,10),(1,100),(1,1000);
84 select pk,i,row_end > '2038-01-01' from t1 for system_time all;
85 pk i row_end > '2038-01-01'
86 1 1000 1
87 drop table t1;
88 set @@time_zone=default;