Debian Codegraph @

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

raw
1 --source suite/versioning/engines.inc
2 --source suite/versioning/common.inc
3
4 --enable_prepare_warnings
5
6 select * from information_schema.periods;
7
8 --replace_result $default_engine DEFAULT_ENGINE $sys_datatype_expl SYS_DATATYPE NULL ''
9 eval 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
16 show create table t1;
17
18 select * from information_schema.periods where table_name = 't1';
19 select column_name, is_system_time_period_start, is_system_time_period_end
20 from information_schema.columns where table_name = 't1';
21
22 --query_vertical select table_catalog,table_schema,table_name,table_type,version,table_rows,data_free,auto_increment,check_time,table_collation,checksum,create_options,table_comment from information_schema.tables where table_name='t1'
23 --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'
24
25 --echo # Implicit fields test
26 create or replace table t1 (
27 x2 int unsigned
28 ) with system versioning;
29 --replace_result $default_engine DEFAULT_ENGINE
30 show create table t1;
31
32 select * from information_schema.periods where table_name = 't1';
33 select column_name, is_system_time_period_start, is_system_time_period_end
34 from information_schema.columns where table_name = 't1';
35
36 --replace_result $default_engine DEFAULT_ENGINE
37 --error ER_VERS_PERIOD_COLUMNS
38 eval create or replace table t1 (
39 x3 int unsigned,
40 Sys_start timestamp(6) as row start invisible,
41 Sys_end timestamp(6) as row end invisible,
42 period for system_time (x, Sys_end)
43 ) with system versioning;
44
45 --replace_result $default_engine DEFAULT_ENGINE
46 --error ER_VERS_PERIOD_COLUMNS
47 eval create or replace table t1 (
48 x4 int unsigned,
49 Sys_start timestamp(6) as row start invisible,
50 Sys_end2 timestamp(6) as row end invisible,
51 period for system_time (Sys_start, Sys_end)
52 ) with system versioning;
53
54 --replace_result $default_engine DEFAULT_ENGINE
55 --error ER_VERS_PERIOD_COLUMNS
56 eval create or replace table t1 (
57 x5 int unsigned,
58 Sys_start timestamp(6) as row start invisible,
59 Sys_end timestamp(6) as row end invisible,
60 period for system_time (Sys_start, x)
61 ) with system versioning;
62
63 --error ER_MISSING
64 create or replace table t1 (
65 x6 int unsigned,
66 period for system_time (Sys_start, Sys_end)
67 ) with system versioning;
68
69 --replace_result $default_engine DEFAULT_ENGINE
70 --error ER_MISSING
71 eval create or replace table t1 (
72 x7 int unsigned,
73 Sys_start timestamp(6) as row start invisible,
74 Sys_end timestamp(6) as row end invisible,
75 period for system_time (Sys_start, Sys_end)
76 );
77
78 --replace_result $default_engine DEFAULT_ENGINE
79 --error ER_VERS_PERIOD_COLUMNS
80 eval create or replace table t1 (
81 x8 int unsigned,
82 Sys_start timestamp(6) as row start invisible,
83 Sys_end timestamp(6) as row end invisible,
84 period for system_time (sys_insert, sys_remove)
85 ) with system versioning;
86
87 --replace_result $default_engine DEFAULT_ENGINE
88 --error ER_MISSING
89 eval create or replace table t1 (
90 x9 int unsigned,
91 Sys_start timestamp(6) as row start invisible,
92 Sys_end timestamp(6) as row end invisible,
93 period for system_time (Sys_start, Sys_end)
94 );
95
96 --replace_result $default_engine DEFAULT_ENGINE
97 --error ER_MISSING
98 eval create or replace table t1 (
99 x10 int unsigned,
100 Sys_start timestamp(6) as row start invisible,
101 Sys_end timestamp(6) as row end invisible,
102 period for system_time (Sys_start, Sys_start)
103 );
104
105 --error ER_VERS_FIELD_WRONG_TYPE, ER_VERS_FIELD_WRONG_TYPE
106 create or replace table t1 (
107 x11 int unsigned,
108 Sys_start bigint unsigned as row start invisible,
109 Sys_end timestamp(6) 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, ER_VERS_FIELD_WRONG_TYPE
114 create or replace table t1 (
115 x12 int unsigned,
116 Sys_start timestamp(6) 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;
120
121 --error ER_VERS_FIELD_WRONG_TYPE
122 create or replace table t1 (
123 x13 int unsigned,
124 Sys_start bigint as row start invisible,
125 Sys_end bigint unsigned as row end invisible,
126 period for system_time (Sys_start, Sys_end)
127 ) with system versioning engine innodb;
128
129 --error ER_VERS_FIELD_WRONG_TYPE
130 create or replace table t1 (
131 x14 int unsigned,
132 Sys_start bigint unsigned as row start invisible,
133 Sys_end bigint as row end invisible,
134 period for system_time (Sys_start, Sys_end)
135 ) with system versioning engine innodb;
136
137 # columns with/without system versioning
138
139 create or replace table t1 (
140 x15 int with system versioning,
141 B int
142 );
143 --replace_result $default_engine DEFAULT_ENGINE
144 show create table t1;
145
146 create or replace table t1 (
147 x16 int with system versioning,
148 B int
149 ) with system versioning;
150 --replace_result $default_engine DEFAULT_ENGINE
151 show create table t1;
152
153 create or replace table t1 (
154 x17 int,
155 B int without system versioning
156 );
157
158 create or replace table t1 (
159 x18 int,
160 B int without system versioning
161 ) with system versioning;
162 --replace_result $default_engine DEFAULT_ENGINE
163 show create table t1;
164
165 create or replace table t1 (
166 x19 int with system versioning,
167 B int without system versioning
168 );
169 --replace_result $default_engine DEFAULT_ENGINE
170 show create table t1;
171
172 create or replace table t1 (
173 x20 int with system versioning,
174 B int without system versioning
175 ) with system versioning;
176 --replace_result $default_engine DEFAULT_ENGINE
177 show create table t1;
178
179 create or replace table t1 (
180 x21 int without system versioning
181 );
182
183 --error ER_VERS_TABLE_MUST_HAVE_COLUMNS
184 create or replace table t1 (
185 x22 int without system versioning
186 ) with system versioning;
187
188 # CREATE TABLE ... LIKE
189 create or replace table t1 (a int) with system versioning;
190 create table tt1 like t1;
191 --replace_result $default_engine DEFAULT_ENGINE
192 show create table tt1;
193 drop table tt1;
194 create temporary table tt1 like t1;
195 --echo # Temporary is stripped from versioning
196 --replace_result $default_engine DEFAULT_ENGINE
197 show create table tt1;
198
199 --echo # CREATE TABLE ... SELECT
200 create or replace table t1 (x23 int) with system versioning;
201 --replace_result $default_engine DEFAULT_ENGINE
202 eval create or replace table t0(
203 y int,
204 st timestamp(6) as row start,
205 en timestamp(6) as row end,
206 period for system_time (st, en)
207 ) with system versioning;
208
209 --echo ## For non-versioned table:
210 --echo ### 1. invisible fields are not included
211 create or replace table t2 as select * from t1;
212 --replace_result $default_engine DEFAULT_ENGINE
213 show create table t2;
214
215 --echo ### 2. all visible fields are included
216 create or replace table t3 as select * from t0;
217 select * from t0;
218 --replace_result $default_engine DEFAULT_ENGINE
219 show create table t3;
220
221 --echo ## For versioned table
222 insert into t1 values (1);
223 --disable_cursor_protocol
224 select row_start from t1 into @row_start;
225 insert into t0 (y) values (2);
226 select st from t0 into @st;
227 --enable_cursor_protocol
228
229 create or replace table t2 with system versioning as select * from t1;
230 --replace_result $default_engine DEFAULT_ENGINE
231 show create table t2;
232 --echo #### invisible fields are not copied
233 select * from t2;
234 select * from t2 where row_start <= @row_start;
235
236 --echo ### 2. source table with visible system fields, target with invisible
237 create or replace table t3 with system versioning as select * from t0;
238 --replace_result $default_engine DEFAULT_ENGINE
239 show create table t3;
240 select * from t3 where y > 2;
241 select y from t3 where st = @st and row_start > @st;
242
243 --echo ### 3. source and target table with visible system fields
244 --replace_result $default_engine DEFAULT_ENGINE
245 eval create or replace table t3 (
246 st timestamp(6) as row start invisible,
247 en timestamp(6) as row end invisible,
248 period for system_time (st, en)
249 ) with system versioning as select * from t0;
250 --replace_result $default_engine DEFAULT_ENGINE
251 show create table t3;
252 select y from t3;
253 select y from t3 where st = @st;
254
255 --echo ### 4. system fields not or wrongly selected
256 create or replace table t3 with system versioning select x23 from t1;
257 --replace_result $default_engine DEFAULT_ENGINE
258 show create table t3;
259 select * from t3;
260 --error ER_DUP_FIELDNAME
261 create or replace table t3 with system versioning select x23, row_start from t1;
262 --error ER_DUP_FIELDNAME
263 create or replace table t3 with system versioning select x23, row_end from t1;
264
265 --echo # Prepare checking for historical row
266 --disable_cursor_protocol
267 delete from t1;
268 select row_end from t1 for system_time all into @row_end;
269 delete from t0;
270 select en from t0 for system_time all into @en;
271 --enable_cursor_protocol
272
273 --echo ## Combinations of versioned + non-versioned
274 create or replace table t2 (y int);
275 insert into t2 values (3);
276 create or replace table t3 with system versioning select * from t1 for system_time all, t2;
277 --replace_result $default_engine DEFAULT_ENGINE
278 show create table t3;
279 select * from t3 for system_time all;
280 select * from t3 for system_time all where row_start = @row_start and row_end = @row_end;
281
282 create or replace table t2 like t0;
283 insert into t2 (y) values (1), (2);
284 delete from t2 where y = 2;
285
286 create or replace table t3 select * from t2 for system_time all;
287 --disable_cursor_protocol
288 select st, en from t3 where y = 1 into @st, @en;
289 --enable_cursor_protocol
290 select y from t2 for system_time all where st = @st and en = @en;
291 --disable_cursor_protocol
292 select st, en from t3 where y = 2 into @st, @en;
293 --enable_cursor_protocol
294 select y from t2 for system_time all where st = @st and en = @en;
295
296 --echo ## Default engine detection
297 --replace_result $non_default_engine NON_DEFAULT_ENGINE
298 eval create or replace table t1 (x25 int) with system versioning engine $non_default_engine;
299 create or replace table t2
300 as select x25, row_start, row_end from t1 for system_time all;
301 --replace_result $default_engine DEFAULT_ENGINE
302 show create table t2;
303
304 create or replace table t2 with system versioning
305 as select x25, row_start rs, row_end re from t1;
306 --replace_result $default_engine DEFAULT_ENGINE
307 show create table t2;
308
309 create or replace table t1 (
310 x26 int,
311 st bigint unsigned as row start,
312 en bigint unsigned as row end,
313 period for system_time (st, en)
314 ) with system versioning engine innodb;
315 create or replace table t2 with system versioning engine myisam
316 as select * from t1;
317 show create table t2;
318
319 --replace_result $non_default_engine NON_DEFAULT_ENGINE
320 eval create or replace table t1 (x27 int, id int) with system versioning engine $non_default_engine;
321 create or replace table t2 (b int, id int);
322 create or replace table t3 with system versioning
323 as select t2.b, t1.x27, t1.row_start rs, t1.row_end re from t2 inner join t1 on t2.id=t1.id;
324 --replace_result $default_engine DEFAULT_ENGINE
325 show create table t3;
326
327 --echo ## Errors
328
329 --error ER_VERS_NOT_SUPPORTED
330 create or replace temporary table t (x28 int) with system versioning;
331
332 --error ER_VERS_DUPLICATE_ROW_START_END
333 create or replace table t1 (
334 x29 int unsigned,
335 Sys_start0 timestamp(6) as row start invisible,
336 Sys_start timestamp(6) as row start invisible,
337 Sys_end timestamp(6) as row end invisible,
338 period for system_time (Sys_start, Sys_end)
339 ) with system versioning;
340
341 --error ER_VERS_DUPLICATE_ROW_START_END
342 create or replace table t1 (
343 x29 int unsigned,
344 Sys_end0 timestamp(6) as row end invisible,
345 Sys_start timestamp(6) as row start invisible,
346 Sys_end timestamp(6) as row end invisible,
347 period for system_time (Sys_start, Sys_end)
348 ) with system versioning;
349
350 --echo ## System fields detection
351 create or replace table t1 (x30 int) with system versioning;
352 --replace_result $default_engine DEFAULT_ENGINE
353 eval create or replace table t2 (
354 y int,
355 st timestamp(6) as row start invisible,
356 en timestamp(6) as row end invisible,
357 period for system_time (st, en)
358 ) with system versioning;
359
360 create or replace table t3
361 as select x30, y, row_start, row_end, st, en from t1, t2;
362 --replace_result $default_engine DEFAULT_ENGINE
363 show create table t3;
364
365 --replace_result $default_engine DEFAULT_ENGINE
366 eval create or replace table t3 (
367 y int,
368 st timestamp(6) as row start invisible,
369 en timestamp(6) as row end invisible,
370 period for system_time (st, en)
371 ) with system versioning
372 as select x30, y, row_start, row_end, st, en from t1, t2;
373 --replace_result $default_engine DEFAULT_ENGINE
374 show create table t3;
375
376 --echo # MDEV-14828 Server crashes in JOIN::prepare / setup_fields on 2nd execution of PS [#437]
377 create or replace table t1 (x int) with system versioning;
378 prepare bad from 'create or replace table t2 with system versioning as select * from t1';
379 execute bad; execute bad; execute bad; execute bad; execute bad; execute bad; execute bad; execute bad;
380 --echo # bad is good.
381
382 --echo # MDEV-15413 Unexpected errors upon CREATE TABLE .. WITH SYSTEM VERSIONING AS SELECT ...
383 create or replace table t1 with system versioning as select 1 as i;
384 --replace_result $default_engine DEFAULT_ENGINE
385 show create table t1;
386 create or replace table t1 (i int) with system versioning as select 1 as i;
387 --replace_result $default_engine DEFAULT_ENGINE
388 show create table t1;
389
390 --error ER_VERS_FIELD_WRONG_TYPE
391 create or replace table t1 (
392 a int,
393 row_start bigint as row start,
394 row_end bigint as row end,
395 period for system_time (row_start, row_end)
396 ) engine=innodb with system versioning;
397
398 --error ER_VERS_FIELD_WRONG_TYPE
399 create or replace table t1 (
400 a int,
401 row_start bigint as row start,
402 row_end bigint as row end,
403 period for system_time (row_start, row_end)
404 ) engine=myisam with system versioning;
405
406 --error ER_VERS_FIELD_WRONG_TYPE
407 create table t (
408 a int,
409 row_start datetime(6) generated always as row start,
410 row_end datetime(6) generated always as row end,
411 period for system_time(row_start, row_end)
412 ) with system versioning;
413
414 --source suite/versioning/common_finish.inc
415 --echo # MDEV-16490 It's possible to make a system versioned table without any versioning field
416 create or replace table t1 (x int without system versioning)
417 with system versioning
418 select 1 as y;
419 --error ER_VERS_TABLE_MUST_HAVE_COLUMNS
420 create or replace table t1 (x int without system versioning)
421 with system versioning
422 select 1 as x;
423
424 drop tables t0, t1, t2, t3;
425
426 --echo #
427 --echo # MDEV-23968 CREATE TEMPORARY TABLE .. LIKE (system versioned table) returns error if unique index is defined in the table
428 --echo #
429 --error ER_KEY_COLUMN_DOES_NOT_EXIST
430 create table t1 (id int primary key, index(row_start)) with system versioning;
431 --error ER_KEY_COLUMN_DOES_NOT_EXIST
432 create table t1 (id int primary key, index(row_end)) with system versioning;
433 --error ER_KEY_COLUMN_DOES_NOT_EXIST
434 create table t1 (id int, primary key(id, row_end, row_end)) with system versioning;
435 create table t1 (id int primary key) with system versioning;
436 create temporary table t2 like t1;
437 --replace_result $default_engine DEFAULT_ENGINE
438 show create table t1;
439 --replace_result $default_engine DEFAULT_ENGINE
440 show create table t2;
441 drop temporary table t2;
442 create or replace table t1 (
443 a int,
444 row_start timestamp(6) as row start,
445 row_end timestamp(6) as row end,
446 period for system_time (row_start, row_end),
447 index(row_start),
448 index(row_end),
449 primary key(row_end, a, row_start),
450 index(row_end, row_start, a)) with system versioning;
451 create temporary table t2 like t1;
452 --replace_result $default_engine DEFAULT_ENGINE
453 show create table t1;
454 --replace_result $default_engine DEFAULT_ENGINE
455 show create table t2;
456 drop temporary table t2;
457 drop table t1;
458 --disable_prepare_warnings
459
460 --echo #
461 --echo # MDEV-16857 system-invisible row_end is displayed in SHOW INDEX
462 --echo #
463 create or replace table t1 (id int primary key, x int) with system versioning;
464 select table_schema, table_name, non_unique, index_schema, index_name, seq_in_index, column_name
465 from information_schema.statistics where table_name = 't1';
466 --replace_column 6 # 7 # 8 # 9 # 10 # 11 #
467 show index from t1;
468 --replace_result $default_engine DEFAULT_ENGINE
469 show create table t1;
470 drop table t1;
471
472 --echo #
473 --echo # MDEV-26928 Column-inclusive WITH SYSTEM VERSIONING doesn't work with explicit system fields
474 --echo #
475 create or replace table t1 (x int, y int with system versioning);
476 --replace_result $default_engine DEFAULT_ENGINE
477 show create table t1;
478
479 create or replace table t1 (
480 x int, y int with system versioning,
481 row_start timestamp(6) as row start,
482 row_end timestamp(6) as row end,
483 period for system_time(row_start, row_end));
484 --replace_result $default_engine DEFAULT_ENGINE
485 show create table t1;
486
487 drop table t1;
488
489 --echo #
490 --echo # MDEV-27452 TIMESTAMP(0) system field is allowed for certain creation of system-versioned table
491 --echo #
492 --error ER_VERS_FIELD_WRONG_TYPE
493 create or replace table t (
494 a int,
495 s timestamp as row start,
496 e timestamp as row end,
497 period for system_time (s, e))
498 with system versioning;
499
500 --error ER_VERS_FIELD_WRONG_TYPE
501 create or replace table t (
502 a int with system versioning,
503 s timestamp as row start,
504 e timestamp as row end,
505 period for system_time (s, e));
506
507 create or replace table t (
508 a int with system versioning,
509 b int with system versioning,
510 s timestamp(6) as row start,
511 e timestamp(6) as row end,
512 period for system_time (s, e));
513 insert into t () values (),();
514
515 # cleanup
516 drop table t;