Debian Codegraph @

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

raw
1 set default_storage_engine=innodb;
2 create or replace table dept (
3 dept_id int(10) primary key,
4 name varchar(100)
5 )
6 with system versioning;
7 create or replace table emp (
8 emp_id int(10) primary key,
9 dept_id int(10),
10 name varchar(100),
11 salary int(10),
12 constraint `dept-emp-fk`
13 foreign key (dept_id) references dept (dept_id)
14 on delete restrict
15 on update restrict
16 )
17 with system versioning;
18 select now() into @ts_0;
19 insert into dept (dept_id, name) values (10, "accounting");
20 commit;
21 select row_start into @ts_1 from dept where dept_id=10;
22 insert into emp (emp_id, name, salary, dept_id) values (1, "bill", 1000, 10);
23 commit;
24 select row_start into @ts_2 from emp where name="bill";
25 select * from emp;
26 emp_id dept_id name salary
27 1 10 bill 1000
28 update emp set salary=2000 where name="bill";
29 commit;
30 select row_start into @ts_3 from emp where name="bill";
31 select * from emp;
32 emp_id dept_id name salary
33 1 10 bill 2000
34 select * from emp for system_time as of timestamp @ts_2;
35 emp_id dept_id name salary
36 1 10 bill 1000
37 select * from emp for system_time as of timestamp @ts_3;
38 emp_id dept_id name salary
39 1 10 bill 2000
40 select * from emp e, dept d
41 where d.dept_id = 10
42 and d.dept_id = e.dept_id;
43 emp_id dept_id name salary dept_id name
44 1 10 bill 2000 10 accounting
45 select * from
46 emp for system_time from timestamp @ts_1 to timestamp @ts_2 e,
47 dept for system_time from timestamp @ts_1 to timestamp @ts_2 d
48 where d.dept_id = 10
49 and d.dept_id = e.dept_id;
50 emp_id dept_id name salary dept_id name
51 set statement system_versioning_asof=@ts_0 for
52 select * from emp e, dept d
53 where d.dept_id = 10
54 and d.dept_id = e.dept_id;
55 emp_id dept_id name salary dept_id name
56 set statement system_versioning_asof=@ts_1 for
57 select * from emp e, dept d
58 where d.dept_id = 10
59 and d.dept_id = e.dept_id;
60 emp_id dept_id name salary dept_id name
61 set statement system_versioning_asof=@ts_2 for
62 select * from emp e, dept d
63 where d.dept_id = 10
64 and d.dept_id = e.dept_id;
65 emp_id dept_id name salary dept_id name
66 1 10 bill 1000 10 accounting
67 set statement system_versioning_asof=@ts_3 for
68 select * from emp e, dept d
69 where d.dept_id = 10
70 and d.dept_id = e.dept_id;
71 emp_id dept_id name salary dept_id name
72 1 10 bill 2000 10 accounting
73 drop table emp, dept;
74 create table t1 (
75 a timestamp(6),
76 b timestamp(6) generated always as (a + interval 1 day),
77 c timestamp(6) generated always as (a + interval 1 month) stored,
78 d timestamp(6) generated always as row start,
79 e timestamp(6) generated always as row end,
80 period for system_time(d,e)
81 ) with system versioning;
82 show columns from t1;
83 Field Type Null Key Default Extra
84 a timestamp(6) YES NULL
85 b timestamp(6) YES NULL VIRTUAL GENERATED
86 c timestamp(6) YES NULL STORED GENERATED
87 d timestamp(6) NO NULL STORED GENERATED
88 e timestamp(6) NO NULL STORED GENERATED
89 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';
90 table_catalog def
91 table_schema test
92 table_name t1
93 column_name a
94 ordinal_position 1
95 column_default NULL
96 is_nullable YES
97 data_type timestamp
98 character_maximum_length NULL
99 character_octet_length NULL
100 numeric_precision NULL
101 numeric_scale NULL
102 datetime_precision 6
103 character_set_name NULL
104 collation_name NULL
105 column_type timestamp(6)
106 column_key
107 extra
108 column_comment
109 is_generated NEVER
110 generation_expression NULL
111 --- ---
112 table_catalog def
113 table_schema test
114 table_name t1
115 column_name b
116 ordinal_position 2
117 column_default NULL
118 is_nullable YES
119 data_type timestamp
120 character_maximum_length NULL
121 character_octet_length NULL
122 numeric_precision NULL
123 numeric_scale NULL
124 datetime_precision 6
125 character_set_name NULL
126 collation_name NULL
127 column_type timestamp(6)
128 column_key
129 extra VIRTUAL GENERATED
130 column_comment
131 is_generated ALWAYS
132 generation_expression `a` + interval 1 day
133 --- ---
134 table_catalog def
135 table_schema test
136 table_name t1
137 column_name c
138 ordinal_position 3
139 column_default NULL
140 is_nullable YES
141 data_type timestamp
142 character_maximum_length NULL
143 character_octet_length NULL
144 numeric_precision NULL
145 numeric_scale NULL
146 datetime_precision 6
147 character_set_name NULL
148 collation_name NULL
149 column_type timestamp(6)
150 column_key
151 extra STORED GENERATED
152 column_comment
153 is_generated ALWAYS
154 generation_expression `a` + interval 1 month
155 --- ---
156 table_catalog def
157 table_schema test
158 table_name t1
159 column_name d
160 ordinal_position 4
161 column_default NULL
162 is_nullable NO
163 data_type timestamp
164 character_maximum_length NULL
165 character_octet_length NULL
166 numeric_precision NULL
167 numeric_scale NULL
168 datetime_precision 6
169 character_set_name NULL
170 collation_name NULL
171 column_type timestamp(6)
172 column_key
173 extra STORED GENERATED
174 column_comment
175 is_generated ALWAYS
176 generation_expression ROW START
177 --- ---
178 table_catalog def
179 table_schema test
180 table_name t1
181 column_name e
182 ordinal_position 5
183 column_default NULL
184 is_nullable NO
185 data_type timestamp
186 character_maximum_length NULL
187 character_octet_length NULL
188 numeric_precision NULL
189 numeric_scale NULL
190 datetime_precision 6
191 character_set_name NULL
192 collation_name NULL
193 column_type timestamp(6)
194 column_key
195 extra STORED GENERATED
196 column_comment
197 is_generated ALWAYS
198 generation_expression ROW END
199 --- ---
200 drop table t1;