Debian Codegraph @

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

raw
1 -- source include/have_innodb.inc
2 set default_storage_engine=innodb;
3 create or replace table dept (
4 dept_id int(10) primary key,
5 name varchar(100)
6 )
7 with system versioning;
8
9 create or replace table emp (
10 emp_id int(10) primary key,
11 dept_id int(10),
12 name varchar(100),
13 salary int(10),
14 constraint `dept-emp-fk`
15 foreign key (dept_id) references dept (dept_id)
16 on delete restrict
17 on update restrict
18 )
19 with system versioning;
20
21 --disable_cursor_protocol
22 select now() into @ts_0;
23
24 insert into dept (dept_id, name) values (10, "accounting");
25 commit;
26
27 select row_start into @ts_1 from dept where dept_id=10;
28
29 insert into emp (emp_id, name, salary, dept_id) values (1, "bill", 1000, 10);
30 commit;
31
32 select row_start into @ts_2 from emp where name="bill";
33 --enable_cursor_protocol
34
35 select * from emp;
36
37 update emp set salary=2000 where name="bill";
38 commit;
39
40 --disable_cursor_protocol
41 select row_start into @ts_3 from emp where name="bill";
42 --enable_cursor_protocol
43
44 select * from emp;
45 select * from emp for system_time as of timestamp @ts_2;
46 select * from emp for system_time as of timestamp @ts_3;
47 select * from emp e, dept d
48 where d.dept_id = 10
49 and d.dept_id = e.dept_id;
50
51 select * from
52 emp for system_time from timestamp @ts_1 to timestamp @ts_2 e,
53 dept for system_time from timestamp @ts_1 to timestamp @ts_2 d
54 where d.dept_id = 10
55 and d.dept_id = e.dept_id;
56
57 set statement system_versioning_asof=@ts_0 for
58 select * from emp e, dept d
59 where d.dept_id = 10
60 and d.dept_id = e.dept_id;
61
62 set statement system_versioning_asof=@ts_1 for
63 select * from emp e, dept d
64 where d.dept_id = 10
65 and d.dept_id = e.dept_id;
66
67 set statement system_versioning_asof=@ts_2 for
68 select * from emp e, dept d
69 where d.dept_id = 10
70 and d.dept_id = e.dept_id;
71
72 set statement system_versioning_asof=@ts_3 for
73 select * from emp e, dept d
74 where d.dept_id = 10
75 and d.dept_id = e.dept_id;
76
77 drop table emp, dept;
78
79 #
80 # MDEV-16804 SYSTEM VERSIONING columns not showing as GENERATED
81 #
82 create table t1 (
83 a timestamp(6),
84 b timestamp(6) generated always as (a + interval 1 day),
85 c timestamp(6) generated always as (a + interval 1 month) stored,
86 d timestamp(6) generated always as row start,
87 e timestamp(6) generated always as row end,
88 period for system_time(d,e)
89 ) with system versioning;
90 show columns from t1;
91 query_vertical select table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,character_octet_length,numeric_precision,numeric_scale,datetime_precision,character_set_name,collation_name,column_type,column_key,extra,column_comment,is_generated,generation_expression,'---' from information_schema.columns where table_name='t1';
92 drop table t1;
93