jzahxqMf

1--source suite/versioning/engines.inc
2--source suite/versioning/common.inc
3
4--disable_warnings
5drop table if exists t1;
6--enable_warnings
7
8--replace_result $default_engine DEFAULT_ENGINE $sys_datatype_expl SYS_DATATYPE NULL ''
9eval create table t1 (
10  x1 int unsigned,
11  Sys_start $sys_datatype_expl as row start invisible comment 'start',
12  Sys_end $sys_datatype_expl as row end invisible comment 'end',
13  period for system_time (Sys_start, Sys_end)
14) with system versioning;
15--replace_result $default_engine DEFAULT_ENGINE $sys_datatype_expl SYS_DATATYPE
16show create table t1;
17
18--query_vertical select table_catalog,table_schema,table_name,table_type,version,table_rows,avg_row_length,data_free,auto_increment,check_time,table_collation,checksum,create_options,table_comment from information_schema.tables where table_name='t1'
19--query_vertical select table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,character_maximum_length,character_octet_length,character_set_name,collation_name,column_key,extra,column_comment,is_generated,generation_expression from information_schema.columns where table_name='t1'
20
21--echo # Implicit fields test
22create or replace table t1 (
23  x2 int unsigned
24) with system versioning;
25--replace_result $default_engine DEFAULT_ENGINE
26show create table t1;
27
28--replace_result $default_engine DEFAULT_ENGINE
29--error ER_VERS_PERIOD_COLUMNS
30eval create or replace table t1 (
31  x3 int unsigned,
32  Sys_start timestamp(6) as row start invisible,
33  Sys_end timestamp(6) as row end invisible,
34  period for system_time (x, Sys_end)
35) with system versioning;
36
37--replace_result $default_engine DEFAULT_ENGINE
38--error ER_VERS_PERIOD_COLUMNS
39eval create or replace table t1 (
40  x4 int unsigned,
41  Sys_start timestamp(6) as row start invisible,
42  Sys_end2 timestamp(6) as row end invisible,
43  period for system_time (Sys_start, Sys_end)
44) with system versioning;
45
46--replace_result $default_engine DEFAULT_ENGINE
47--error ER_VERS_PERIOD_COLUMNS
48eval create or replace table t1 (
49  x5 int unsigned,
50  Sys_start timestamp(6) as row start invisible,
51  Sys_end timestamp(6) as row end invisible,
52  period for system_time (Sys_start, x)
53) with system versioning;
54
55--error ER_MISSING
56create or replace table t1 (
57  x6 int unsigned,
58  period for system_time (Sys_start, Sys_end)
59) with system versioning;
60
61--replace_result $default_engine DEFAULT_ENGINE
62--error ER_MISSING
63eval create or replace table t1 (
64  x7 int unsigned,
65  Sys_start timestamp(6) as row start invisible,
66  Sys_end timestamp(6) as row end invisible,
67  period for system_time (Sys_start, Sys_end)
68);
69
70--replace_result $default_engine DEFAULT_ENGINE
71--error ER_VERS_PERIOD_COLUMNS
72eval create or replace table t1 (
73  x8 int unsigned,
74  Sys_start timestamp(6) as row start invisible,
75  Sys_end timestamp(6) as row end invisible,
76  period for system_time (sys_insert, sys_remove)
77) with system versioning;
78
79--replace_result $default_engine DEFAULT_ENGINE
80--error ER_MISSING
81eval create or replace table t1 (
82  x9 int unsigned,
83  Sys_start timestamp(6) as row start invisible,
84  Sys_end timestamp(6) as row end invisible,
85  period for system_time (Sys_start, Sys_end)
86);
87
88--replace_result $default_engine DEFAULT_ENGINE
89--error ER_MISSING
90eval create or replace table t1 (
91  x10 int unsigned,
92  Sys_start timestamp(6) as row start invisible,
93  Sys_end timestamp(6) as row end invisible,
94  period for system_time (Sys_start, Sys_start)
95);
96
97--error ER_VERS_FIELD_WRONG_TYPE, ER_VERS_FIELD_WRONG_TYPE
98create or replace table t1 (
99  x11 int unsigned,
100  Sys_start bigint unsigned as row start invisible,
101  Sys_end timestamp(6) as row end invisible,
102  period for system_time (Sys_start, Sys_end)
103) with system versioning;
104
105--error ER_VERS_FIELD_WRONG_TYPE, ER_VERS_FIELD_WRONG_TYPE
106create or replace table t1 (
107  x12 int unsigned,
108  Sys_start timestamp(6) as row start invisible,
109  Sys_end bigint unsigned as row end invisible,
110  period for system_time (Sys_start, Sys_end)
111) with system versioning;
112
113--error ER_VERS_FIELD_WRONG_TYPE
114create or replace table t1 (
115  x13 int unsigned,
116  Sys_start bigint as row start invisible,
117  Sys_end bigint unsigned as row end invisible,
118  period for system_time (Sys_start, Sys_end)
119) with system versioning engine innodb;
120
121--error ER_VERS_FIELD_WRONG_TYPE
122create or replace table t1 (
123  x14 int unsigned,
124  Sys_start bigint unsigned as row start invisible,
125  Sys_end bigint as row end invisible,
126  period for system_time (Sys_start, Sys_end)
127) with system versioning engine innodb;
128
129# columns with/without system versioning
130
131create or replace table t1 (
132  x15 int with system versioning,
133  B int
134);
135--replace_result $default_engine DEFAULT_ENGINE
136show create table t1;
137
138create or replace table t1 (
139  x16 int with system versioning,
140  B int
141) with system versioning;
142--replace_result $default_engine DEFAULT_ENGINE
143show create table t1;
144
145create or replace table t1 (
146  x17 int,
147  B int without system versioning
148);
149
150create or replace table t1 (
151  x18 int,
152  B int without system versioning
153) with system versioning;
154--replace_result $default_engine DEFAULT_ENGINE
155show create table t1;
156
157create or replace table t1 (
158  x19 int with system versioning,
159  B int without system versioning
160);
161--replace_result $default_engine DEFAULT_ENGINE
162show create table t1;
163
164create or replace table t1 (
165  x20 int with system versioning,
166  B int without system versioning
167) with system versioning;
168--replace_result $default_engine DEFAULT_ENGINE
169show create table t1;
170
171create or replace table t1 (
172  x21 int without system versioning
173);
174
175--error ER_VERS_TABLE_MUST_HAVE_COLUMNS
176create or replace table t1 (
177  x22 int without system versioning
178) with system versioning;
179
180# CREATE TABLE ... LIKE
181create or replace table t1 (a int) with system versioning;
182create table tt1 like t1;
183--replace_result $default_engine DEFAULT_ENGINE
184show create table tt1;
185drop table tt1;
186create temporary table tt1 like t1;
187--echo # Temporary is stripped from versioning
188--replace_result $default_engine DEFAULT_ENGINE
189show create table tt1;
190
191--echo # CREATE TABLE ... SELECT
192create or replace table t1 (x23 int) with system versioning;
193--replace_result $default_engine DEFAULT_ENGINE
194eval create or replace table t0(
195  y int,
196  st timestamp(6) as row start,
197  en timestamp(6) as row end,
198  period for system_time (st, en)
199) with system versioning;
200
201--echo ## For non-versioned table:
202--echo ### 1. invisible fields are not included
203create or replace table t2 as select * from t1;
204--replace_result $default_engine DEFAULT_ENGINE
205show create table t2;
206
207--echo ### 2. all visible fields are included
208create or replace table t3 as select * from t0;
209select * from t0;
210--replace_result $default_engine DEFAULT_ENGINE
211show create table t3;
212
213--echo ## For versioned table
214insert into t1 values (1);
215select row_start from t1 into @row_start;
216insert into t0 (y) values (2);
217select st from t0 into @st;
218
219create or replace table t2 with system versioning as select * from t1;
220--replace_result $default_engine DEFAULT_ENGINE
221show create table t2;
222--echo #### invisible fields are not copied
223select * from t2;
224select * from t2 where row_start <= @row_start;
225
226--echo ### 2. source table with visible system fields, target with invisible
227create or replace table t3 with system versioning as select * from t0;
228--replace_result $default_engine DEFAULT_ENGINE
229show create table t3;
230select * from t3 where y > 2;
231select y from t3 where st = @st and row_start > @st;
232
233--echo ### 3. source and target table with visible system fields
234--replace_result $default_engine DEFAULT_ENGINE
235eval create or replace table t3 (
236  st timestamp(6) as row start invisible,
237  en timestamp(6) as row end invisible,
238  period for system_time (st, en)
239) with system versioning as select * from t0;
240--replace_result $default_engine DEFAULT_ENGINE
241show create table t3;
242select y from t3;
243select y from t3 where st = @st;
244
245--echo ### 4. system fields not or wrongly selected
246create or replace table t3 with system versioning select x23 from t1;
247--replace_result $default_engine DEFAULT_ENGINE
248show create table t3;
249select * from t3;
250--error ER_DUP_FIELDNAME
251create or replace table t3 with system versioning select x23, row_start from t1;
252--error ER_DUP_FIELDNAME
253create or replace table t3 with system versioning select x23, row_end from t1;
254
255--echo # Prepare checking for historical row
256delete from t1;
257select row_end from t1 for system_time all into @row_end;
258delete from t0;
259select en from t0 for system_time all into @en;
260
261--echo ## Combinations of versioned + non-versioned
262create or replace table t2 (y int);
263insert into t2 values (3);
264create or replace table t3 with system versioning select * from t1 for system_time all, t2;
265--replace_result $default_engine DEFAULT_ENGINE
266show create table t3;
267select * from t3 for system_time all;
268select * from t3 for system_time all where row_start = @row_start and row_end = @row_end;
269
270create or replace table t2 like t0;
271insert into t2 (y) values (1), (2);
272delete from t2 where y = 2;
273
274create or replace table t3 select * from t2 for system_time all;
275select st, en from t3 where y = 1 into @st, @en;
276select y from t2 for system_time all where st = @st and en = @en;
277select st, en from t3 where y = 2 into @st, @en;
278select y from t2 for system_time all where st = @st and en = @en;
279
280--echo ## Default engine detection
281--replace_result $non_default_engine NON_DEFAULT_ENGINE
282eval create or replace table t1 (x25 int) with system versioning engine $non_default_engine;
283create or replace table t2
284as select x25, row_start, row_end from t1 for system_time all;
285--replace_result $default_engine DEFAULT_ENGINE
286show create table t2;
287
288create or replace table t2 with system versioning
289as select x25, row_start rs, row_end re from t1;
290--replace_result $default_engine DEFAULT_ENGINE
291show create table t2;
292
293create or replace table t1 (
294  x26 int,
295  st bigint unsigned as row start,
296  en bigint unsigned as row end,
297  period for system_time (st, en)
298) with system versioning engine innodb;
299create or replace table t2 with system versioning engine myisam
300as select * from t1;
301show create table t2;
302
303--replace_result $non_default_engine NON_DEFAULT_ENGINE
304eval create or replace table t1 (x27 int, id int) with system versioning engine $non_default_engine;
305create or replace table t2 (b int, id int);
306create or replace table t3 with system versioning
307as select t2.b, t1.x27, t1.row_start rs, t1.row_end re from t2 inner join t1 on t2.id=t1.id;
308--replace_result $default_engine DEFAULT_ENGINE
309show create table t3;
310
311--echo ## Errors
312
313--error ER_VERS_TEMPORARY
314create or replace temporary table t (x28 int) with system versioning;
315
316--error ER_VERS_DUPLICATE_ROW_START_END
317create or replace table t1 (
318  x29 int unsigned,
319  Sys_start0 timestamp(6) as row start invisible,
320  Sys_start timestamp(6) as row start invisible,
321  Sys_end timestamp(6) as row end invisible,
322  period for system_time (Sys_start, Sys_end)
323) with system versioning;
324
325--error ER_VERS_DUPLICATE_ROW_START_END
326create or replace table t1 (
327  x29 int unsigned,
328  Sys_end0 timestamp(6) as row end invisible,
329  Sys_start timestamp(6) as row start invisible,
330  Sys_end timestamp(6) as row end invisible,
331  period for system_time (Sys_start, Sys_end)
332) with system versioning;
333
334--echo ## System fields detection
335create or replace table t1 (x30 int) with system versioning;
336--replace_result $default_engine DEFAULT_ENGINE
337eval create or replace table t2 (
338    y int,
339    st timestamp(6) as row start invisible,
340    en timestamp(6) as row end invisible,
341    period for system_time (st, en)
342) with system versioning;
343
344create or replace table t3
345as select x30, y, row_start, row_end, st, en from t1, t2;
346--replace_result $default_engine DEFAULT_ENGINE
347show create table t3;
348
349--replace_result $default_engine DEFAULT_ENGINE
350eval create or replace table t3 (
351    y int,
352    st timestamp(6) as row start invisible,
353    en timestamp(6) as row end invisible,
354    period for system_time (st, en)
355) with system versioning
356as select x30, y, row_start, row_end, st, en from t1, t2;
357--replace_result $default_engine DEFAULT_ENGINE
358show create table t3;
359
360--echo # MDEV-14828 Server crashes in JOIN::prepare / setup_fields on 2nd execution of PS [#437]
361create or replace table t1 (x int) with system versioning;
362prepare bad from 'create or replace table t2 with system versioning as select * from t1';
363execute bad; execute bad; execute bad; execute bad; execute bad; execute bad; execute bad; execute bad;
364--echo # bad is good.
365
366--echo # MDEV-15413 Unexpected errors upon CREATE TABLE .. WITH SYSTEM VERSIONING AS SELECT ...
367create or replace table t1 with system versioning as select 1 as i;
368--replace_result $default_engine DEFAULT_ENGINE
369show create table t1;
370create or replace table t1 (i int) with system versioning as select 1 as i;
371--replace_result $default_engine DEFAULT_ENGINE
372show create table t1;
373
374drop database test;
375create database test;