Debian Codegraph @

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

raw
1 select * from information_schema.periods;
2 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
3 create table t1 (
4 x1 int unsigned,
5 Sys_start SYS_DATATYPE as row start invisible comment 'start',
6 Sys_end SYS_DATATYPE as row end invisible comment 'end',
7 period for system_time (Sys_start, Sys_end)
8 ) with system versioning;
9 show create table t1;
10 Table Create Table
11 t1 CREATE TABLE `t1` (
12 `x1` int(10) unsigned DEFAULT NULL,
13 `Sys_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START INVISIBLE COMMENT 'start',
14 `Sys_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END INVISIBLE COMMENT 'end',
15 PERIOD FOR SYSTEM_TIME (`Sys_start`, `Sys_end`)
16 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
17 select * from information_schema.periods where table_name = 't1';
18 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
19 def test t1 SYSTEM_TIME Sys_start Sys_end
20 select column_name, is_system_time_period_start, is_system_time_period_end
21 from information_schema.columns where table_name = 't1';
22 column_name is_system_time_period_start is_system_time_period_end
23 x1 NO NO
24 Sys_start YES NO
25 Sys_end NO YES
26 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';
27 table_catalog def
28 table_schema test
29 table_name t1
30 table_type SYSTEM VERSIONED
31 version 10
32 table_rows 0
33 data_free 0
34 auto_increment NULL
35 check_time NULL
36 table_collation utf8mb4_uca1400_ai_ci
37 checksum NULL
38 create_options
39 table_comment
40 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';
41 table_catalog def
42 table_schema test
43 table_name t1
44 column_name x1
45 ordinal_position 1
46 column_default NULL
47 character_maximum_length NULL
48 character_octet_length NULL
49 character_set_name NULL
50 collation_name NULL
51 column_key
52 extra
53 column_comment
54 is_generated NEVER
55 generation_expression NULL
56 table_catalog def
57 table_schema test
58 table_name t1
59 column_name Sys_start
60 ordinal_position 2
61 column_default NULL
62 character_maximum_length NULL
63 character_octet_length NULL
64 character_set_name NULL
65 collation_name NULL
66 column_key
67 extra STORED GENERATED, INVISIBLE
68 column_comment start
69 is_generated ALWAYS
70 generation_expression ROW START
71 table_catalog def
72 table_schema test
73 table_name t1
74 column_name Sys_end
75 ordinal_position 3
76 column_default NULL
77 character_maximum_length NULL
78 character_octet_length NULL
79 character_set_name NULL
80 collation_name NULL
81 column_key
82 extra STORED GENERATED, INVISIBLE
83 column_comment end
84 is_generated ALWAYS
85 generation_expression ROW END
86 # Implicit fields test
87 create or replace table t1 (
88 x2 int unsigned
89 ) with system versioning;
90 show create table t1;
91 Table Create Table
92 t1 CREATE TABLE `t1` (
93 `x2` int(10) unsigned DEFAULT NULL
94 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
95 select * from information_schema.periods where table_name = 't1';
96 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
97 select column_name, is_system_time_period_start, is_system_time_period_end
98 from information_schema.columns where table_name = 't1';
99 column_name is_system_time_period_start is_system_time_period_end
100 x2 NO NO
101 create or replace table t1 (
102 x3 int unsigned,
103 Sys_start timestamp(6) as row start invisible,
104 Sys_end timestamp(6) as row end invisible,
105 period for system_time (x, Sys_end)
106 ) with system versioning;
107 ERROR HY000: PERIOD FOR SYSTEM_TIME must use columns `Sys_start` and `Sys_end`
108 create or replace table t1 (
109 x4 int unsigned,
110 Sys_start timestamp(6) as row start invisible,
111 Sys_end2 timestamp(6) as row end invisible,
112 period for system_time (Sys_start, Sys_end)
113 ) with system versioning;
114 ERROR HY000: PERIOD FOR SYSTEM_TIME must use columns `Sys_start` and `Sys_end2`
115 create or replace table t1 (
116 x5 int unsigned,
117 Sys_start timestamp(6) as row start invisible,
118 Sys_end timestamp(6) as row end invisible,
119 period for system_time (Sys_start, x)
120 ) with system versioning;
121 ERROR HY000: PERIOD FOR SYSTEM_TIME must use columns `Sys_start` and `Sys_end`
122 create or replace table t1 (
123 x6 int unsigned,
124 period for system_time (Sys_start, Sys_end)
125 ) with system versioning;
126 ERROR HY000: Wrong parameters for `t1`: missing 'AS ROW START'
127 create or replace table t1 (
128 x7 int unsigned,
129 Sys_start timestamp(6) as row start invisible,
130 Sys_end timestamp(6) as row end invisible,
131 period for system_time (Sys_start, Sys_end)
132 );
133 ERROR HY000: Wrong parameters for `t1`: missing 'WITH SYSTEM VERSIONING'
134 create or replace table t1 (
135 x8 int unsigned,
136 Sys_start timestamp(6) as row start invisible,
137 Sys_end timestamp(6) as row end invisible,
138 period for system_time (sys_insert, sys_remove)
139 ) with system versioning;
140 ERROR HY000: PERIOD FOR SYSTEM_TIME must use columns `Sys_start` and `Sys_end`
141 create or replace table t1 (
142 x9 int unsigned,
143 Sys_start timestamp(6) as row start invisible,
144 Sys_end timestamp(6) as row end invisible,
145 period for system_time (Sys_start, Sys_end)
146 );
147 ERROR HY000: Wrong parameters for `t1`: missing 'WITH SYSTEM VERSIONING'
148 create or replace table t1 (
149 x10 int unsigned,
150 Sys_start timestamp(6) as row start invisible,
151 Sys_end timestamp(6) as row end invisible,
152 period for system_time (Sys_start, Sys_start)
153 );
154 ERROR HY000: Wrong parameters for `t1`: missing 'WITH SYSTEM VERSIONING'
155 create or replace table t1 (
156 x11 int unsigned,
157 Sys_start bigint unsigned as row start invisible,
158 Sys_end timestamp(6) as row end invisible,
159 period for system_time (Sys_start, Sys_end)
160 ) with system versioning;
161 Got one of the listed errors
162 create or replace table t1 (
163 x12 int unsigned,
164 Sys_start timestamp(6) as row start invisible,
165 Sys_end bigint unsigned as row end invisible,
166 period for system_time (Sys_start, Sys_end)
167 ) with system versioning;
168 Got one of the listed errors
169 create or replace table t1 (
170 x13 int unsigned,
171 Sys_start bigint as row start invisible,
172 Sys_end bigint unsigned as row end invisible,
173 period for system_time (Sys_start, Sys_end)
174 ) with system versioning engine innodb;
175 ERROR HY000: `Sys_start` must be of type BIGINT(20) UNSIGNED for system-versioned table `t1`
176 create or replace table t1 (
177 x14 int unsigned,
178 Sys_start bigint unsigned as row start invisible,
179 Sys_end bigint as row end invisible,
180 period for system_time (Sys_start, Sys_end)
181 ) with system versioning engine innodb;
182 ERROR HY000: `Sys_end` must be of type BIGINT(20) UNSIGNED for system-versioned table `t1`
183 create or replace table t1 (
184 x15 int with system versioning,
185 B int
186 );
187 show create table t1;
188 Table Create Table
189 t1 CREATE TABLE `t1` (
190 `x15` int(11) DEFAULT NULL,
191 `B` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING
192 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
193 create or replace table t1 (
194 x16 int with system versioning,
195 B int
196 ) with system versioning;
197 show create table t1;
198 Table Create Table
199 t1 CREATE TABLE `t1` (
200 `x16` int(11) DEFAULT NULL,
201 `B` int(11) DEFAULT NULL
202 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
203 create or replace table t1 (
204 x17 int,
205 B int without system versioning
206 );
207 create or replace table t1 (
208 x18 int,
209 B int without system versioning
210 ) with system versioning;
211 show create table t1;
212 Table Create Table
213 t1 CREATE TABLE `t1` (
214 `x18` int(11) DEFAULT NULL,
215 `B` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING
216 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
217 create or replace table t1 (
218 x19 int with system versioning,
219 B int without system versioning
220 );
221 show create table t1;
222 Table Create Table
223 t1 CREATE TABLE `t1` (
224 `x19` int(11) DEFAULT NULL,
225 `B` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING
226 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
227 create or replace table t1 (
228 x20 int with system versioning,
229 B int without system versioning
230 ) with system versioning;
231 show create table t1;
232 Table Create Table
233 t1 CREATE TABLE `t1` (
234 `x20` int(11) DEFAULT NULL,
235 `B` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING
236 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
237 create or replace table t1 (
238 x21 int without system versioning
239 );
240 create or replace table t1 (
241 x22 int without system versioning
242 ) with system versioning;
243 ERROR HY000: Table `t1` must have at least one versioned column
244 create or replace table t1 (a int) with system versioning;
245 create table tt1 like t1;
246 show create table tt1;
247 Table Create Table
248 tt1 CREATE TABLE `tt1` (
249 `a` int(11) DEFAULT NULL
250 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
251 drop table tt1;
252 create temporary table tt1 like t1;
253 Warnings:
254 Warning 1105 System versioning is stripped from temporary `test.tt1`
255 # Temporary is stripped from versioning
256 show create table tt1;
257 Table Create Table
258 tt1 CREATE TEMPORARY TABLE `tt1` (
259 `a` int(11) DEFAULT NULL
260 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
261 # CREATE TABLE ... SELECT
262 create or replace table t1 (x23 int) with system versioning;
263 create or replace table t0(
264 y int,
265 st timestamp(6) as row start,
266 en timestamp(6) as row end,
267 period for system_time (st, en)
268 ) with system versioning;
269 ## For non-versioned table:
270 ### 1. invisible fields are not included
271 create or replace table t2 as select * from t1;
272 show create table t2;
273 Table Create Table
274 t2 CREATE TABLE `t2` (
275 `x23` int(11) DEFAULT NULL
276 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
277 ### 2. all visible fields are included
278 create or replace table t3 as select * from t0;
279 select * from t0;
280 y st en
281 show create table t3;
282 Table Create Table
283 t3 CREATE TABLE `t3` (
284 `y` int(11) DEFAULT NULL,
285 `st` timestamp(6) NOT NULL,
286 `en` timestamp(6) NOT NULL
287 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
288 ## For versioned table
289 insert into t1 values (1);
290 select row_start from t1 into @row_start;
291 insert into t0 (y) values (2);
292 select st from t0 into @st;
293 create or replace table t2 with system versioning as select * from t1;
294 show create table t2;
295 Table Create Table
296 t2 CREATE TABLE `t2` (
297 `x23` int(11) DEFAULT NULL
298 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
299 #### invisible fields are not copied
300 select * from t2;
301 x23
302 1
303 select * from t2 where row_start <= @row_start;
304 x23
305 ### 2. source table with visible system fields, target with invisible
306 create or replace table t3 with system versioning as select * from t0;
307 show create table t3;
308 Table Create Table
309 t3 CREATE TABLE `t3` (
310 `y` int(11) DEFAULT NULL,
311 `st` timestamp(6) NOT NULL,
312 `en` timestamp(6) NOT NULL
313 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
314 select * from t3 where y > 2;
315 y st en
316 select y from t3 where st = @st and row_start > @st;
317 y
318 2
319 ### 3. source and target table with visible system fields
320 create or replace table t3 (
321 st timestamp(6) as row start invisible,
322 en timestamp(6) as row end invisible,
323 period for system_time (st, en)
324 ) with system versioning as select * from t0;
325 show create table t3;
326 Table Create Table
327 t3 CREATE TABLE `t3` (
328 `y` int(11) DEFAULT NULL,
329 `st` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
330 `en` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
331 PERIOD FOR SYSTEM_TIME (`st`, `en`)
332 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
333 select y from t3;
334 y
335 2
336 select y from t3 where st = @st;
337 y
338 ### 4. system fields not or wrongly selected
339 create or replace table t3 with system versioning select x23 from t1;
340 show create table t3;
341 Table Create Table
342 t3 CREATE TABLE `t3` (
343 `x23` int(11) DEFAULT NULL
344 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
345 select * from t3;
346 x23
347 1
348 create or replace table t3 with system versioning select x23, row_start from t1;
349 ERROR 42S21: Duplicate column name 'row_start'
350 create or replace table t3 with system versioning select x23, row_end from t1;
351 ERROR 42S21: Duplicate column name 'row_end'
352 # Prepare checking for historical row
353 delete from t1;
354 select row_end from t1 for system_time all into @row_end;
355 delete from t0;
356 select en from t0 for system_time all into @en;
357 ## Combinations of versioned + non-versioned
358 create or replace table t2 (y int);
359 insert into t2 values (3);
360 create or replace table t3 with system versioning select * from t1 for system_time all, t2;
361 show create table t3;
362 Table Create Table
363 t3 CREATE TABLE `t3` (
364 `x23` int(11) DEFAULT NULL,
365 `y` int(11) DEFAULT NULL
366 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
367 select * from t3 for system_time all;
368 x23 y
369 1 3
370 select * from t3 for system_time all where row_start = @row_start and row_end = @row_end;
371 x23 y
372 create or replace table t2 like t0;
373 insert into t2 (y) values (1), (2);
374 delete from t2 where y = 2;
375 create or replace table t3 select * from t2 for system_time all;
376 select st, en from t3 where y = 1 into @st, @en;
377 select y from t2 for system_time all where st = @st and en = @en;
378 y
379 1
380 select st, en from t3 where y = 2 into @st, @en;
381 select y from t2 for system_time all where st = @st and en = @en;
382 y
383 2
384 ## Default engine detection
385 create or replace table t1 (x25 int) with system versioning engine NON_DEFAULT_ENGINE;
386 create or replace table t2
387 as select x25, row_start, row_end from t1 for system_time all;
388 show create table t2;
389 Table Create Table
390 t2 CREATE TABLE `t2` (
391 `x25` int(11) DEFAULT NULL,
392 `row_start` timestamp(6) NOT NULL,
393 `row_end` timestamp(6) NOT NULL
394 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
395 create or replace table t2 with system versioning
396 as select x25, row_start rs, row_end re from t1;
397 show create table t2;
398 Table Create Table
399 t2 CREATE TABLE `t2` (
400 `x25` int(11) DEFAULT NULL,
401 `rs` timestamp(6) NOT NULL,
402 `re` timestamp(6) NOT NULL
403 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
404 create or replace table t1 (
405 x26 int,
406 st bigint unsigned as row start,
407 en bigint unsigned as row end,
408 period for system_time (st, en)
409 ) with system versioning engine innodb;
410 create or replace table t2 with system versioning engine myisam
411 as select * from t1;
412 show create table t2;
413 Table Create Table
414 t2 CREATE TABLE `t2` (
415 `x26` int(11) DEFAULT NULL,
416 `st` bigint(20) unsigned NOT NULL,
417 `en` bigint(20) unsigned NOT NULL
418 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
419 create or replace table t1 (x27 int, id int) with system versioning engine NON_DEFAULT_ENGINE;
420 create or replace table t2 (b int, id int);
421 create or replace table t3 with system versioning
422 as select t2.b, t1.x27, t1.row_start rs, t1.row_end re from t2 inner join t1 on t2.id=t1.id;
423 show create table t3;
424 Table Create Table
425 t3 CREATE TABLE `t3` (
426 `b` int(11) DEFAULT NULL,
427 `x27` int(11) DEFAULT NULL,
428 `rs` timestamp(6) NOT NULL,
429 `re` timestamp(6) NOT NULL
430 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
431 ## Errors
432 create or replace temporary table t (x28 int) with system versioning;
433 ERROR HY000: System-versioned tables do not support CREATE TEMPORARY TABLE
434 create or replace table t1 (
435 x29 int unsigned,
436 Sys_start0 timestamp(6) as row start invisible,
437 Sys_start timestamp(6) as row start invisible,
438 Sys_end timestamp(6) as row end invisible,
439 period for system_time (Sys_start, Sys_end)
440 ) with system versioning;
441 ERROR HY000: Duplicate ROW START column `Sys_start`
442 create or replace table t1 (
443 x29 int unsigned,
444 Sys_end0 timestamp(6) as row end invisible,
445 Sys_start timestamp(6) as row start invisible,
446 Sys_end timestamp(6) as row end invisible,
447 period for system_time (Sys_start, Sys_end)
448 ) with system versioning;
449 ERROR HY000: Duplicate ROW END column `Sys_end`
450 ## System fields detection
451 create or replace table t1 (x30 int) with system versioning;
452 create or replace table t2 (
453 y int,
454 st timestamp(6) as row start invisible,
455 en timestamp(6) as row end invisible,
456 period for system_time (st, en)
457 ) with system versioning;
458 create or replace table t3
459 as select x30, y, row_start, row_end, st, en from t1, t2;
460 show create table t3;
461 Table Create Table
462 t3 CREATE TABLE `t3` (
463 `x30` int(11) DEFAULT NULL,
464 `y` int(11) DEFAULT NULL,
465 `row_start` timestamp(6) NOT NULL,
466 `row_end` timestamp(6) NOT NULL,
467 `st` timestamp(6) NOT NULL,
468 `en` timestamp(6) NOT NULL
469 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
470 create or replace table t3 (
471 y int,
472 st timestamp(6) as row start invisible,
473 en timestamp(6) as row end invisible,
474 period for system_time (st, en)
475 ) with system versioning
476 as select x30, y, row_start, row_end, st, en from t1, t2;
477 show create table t3;
478 Table Create Table
479 t3 CREATE TABLE `t3` (
480 `x30` int(11) DEFAULT NULL,
481 `y` int(11) DEFAULT NULL,
482 `row_start` timestamp(6) NOT NULL,
483 `row_end` timestamp(6) NOT NULL,
484 `st` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
485 `en` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
486 PERIOD FOR SYSTEM_TIME (`st`, `en`)
487 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
488 # MDEV-14828 Server crashes in JOIN::prepare / setup_fields on 2nd execution of PS [#437]
489 create or replace table t1 (x int) with system versioning;
490 prepare bad from 'create or replace table t2 with system versioning as select * from t1';
491 execute bad;
492 execute bad;
493 execute bad;
494 execute bad;
495 execute bad;
496 execute bad;
497 execute bad;
498 execute bad;
499 # bad is good.
500 # MDEV-15413 Unexpected errors upon CREATE TABLE .. WITH SYSTEM VERSIONING AS SELECT ...
501 create or replace table t1 with system versioning as select 1 as i;
502 show create table t1;
503 Table Create Table
504 t1 CREATE TABLE `t1` (
505 `i` int(1) NOT NULL
506 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
507 create or replace table t1 (i int) with system versioning as select 1 as i;
508 show create table t1;
509 Table Create Table
510 t1 CREATE TABLE `t1` (
511 `i` int(11) DEFAULT NULL
512 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
513 create or replace table t1 (
514 a int,
515 row_start bigint as row start,
516 row_end bigint as row end,
517 period for system_time (row_start, row_end)
518 ) engine=innodb with system versioning;
519 ERROR HY000: `row_start` must be of type BIGINT(20) UNSIGNED for system-versioned table `t1`
520 create or replace table t1 (
521 a int,
522 row_start bigint as row start,
523 row_end bigint as row end,
524 period for system_time (row_start, row_end)
525 ) engine=myisam with system versioning;
526 ERROR HY000: `row_start` must be of type BIGINT(20) UNSIGNED for system-versioned table `t1`
527 create table t (
528 a int,
529 row_start datetime(6) generated always as row start,
530 row_end datetime(6) generated always as row end,
531 period for system_time(row_start, row_end)
532 ) with system versioning;
533 ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t`
534 # MDEV-16490 It's possible to make a system versioned table without any versioning field
535 create or replace table t1 (x int without system versioning)
536 with system versioning
537 select 1 as y;
538 create or replace table t1 (x int without system versioning)
539 with system versioning
540 select 1 as x;
541 ERROR HY000: Table `t1` must have at least one versioned column
542 drop tables t0, t1, t2, t3;
543 #
544 # MDEV-23968 CREATE TEMPORARY TABLE .. LIKE (system versioned table) returns error if unique index is defined in the table
545 #
546 create table t1 (id int primary key, index(row_start)) with system versioning;
547 ERROR 42000: Key column 'row_start' doesn't exist in table
548 create table t1 (id int primary key, index(row_end)) with system versioning;
549 ERROR 42000: Key column 'row_end' doesn't exist in table
550 create table t1 (id int, primary key(id, row_end, row_end)) with system versioning;
551 ERROR 42000: Key column 'row_end' doesn't exist in table
552 create table t1 (id int primary key) with system versioning;
553 create temporary table t2 like t1;
554 Warnings:
555 Warning 1105 System versioning is stripped from temporary `test.t2`
556 show create table t1;
557 Table Create Table
558 t1 CREATE TABLE `t1` (
559 `id` int(11) NOT NULL,
560 PRIMARY KEY (`id`)
561 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
562 show create table t2;
563 Table Create Table
564 t2 CREATE TEMPORARY TABLE `t2` (
565 `id` int(11) NOT NULL
566 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
567 drop temporary table t2;
568 create or replace table t1 (
569 a int,
570 row_start timestamp(6) as row start,
571 row_end timestamp(6) as row end,
572 period for system_time (row_start, row_end),
573 index(row_start),
574 index(row_end),
575 primary key(row_end, a, row_start),
576 index(row_end, row_start, a)) with system versioning;
577 create temporary table t2 like t1;
578 Warnings:
579 Warning 1105 System versioning is stripped from temporary `test.t2`
580 show create table t1;
581 Table Create Table
582 t1 CREATE TABLE `t1` (
583 `a` int(11) NOT NULL,
584 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START,
585 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END,
586 PRIMARY KEY (`row_end`,`a`,`row_start`),
587 KEY `row_start` (`row_start`),
588 KEY `row_end` (`row_end`),
589 KEY `row_end_2` (`row_end`,`row_start`,`a`),
590 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
591 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
592 show create table t2;
593 Table Create Table
594 t2 CREATE TEMPORARY TABLE `t2` (
595 `a` int(11) NOT NULL
596 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
597 drop temporary table t2;
598 drop table t1;
599 #
600 # MDEV-16857 system-invisible row_end is displayed in SHOW INDEX
601 #
602 create or replace table t1 (id int primary key, x int) with system versioning;
603 select table_schema, table_name, non_unique, index_schema, index_name, seq_in_index, column_name
604 from information_schema.statistics where table_name = 't1';
605 table_schema table_name non_unique index_schema index_name seq_in_index column_name
606 test t1 0 test PRIMARY 1 id
607 show index from t1;
608 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
609 t1 0 PRIMARY 1 id # # # # # # NO
610 show create table t1;
611 Table Create Table
612 t1 CREATE TABLE `t1` (
613 `id` int(11) NOT NULL,
614 `x` int(11) DEFAULT NULL,
615 PRIMARY KEY (`id`)
616 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
617 drop table t1;
618 #
619 # MDEV-26928 Column-inclusive WITH SYSTEM VERSIONING doesn't work with explicit system fields
620 #
621 create or replace table t1 (x int, y int with system versioning);
622 show create table t1;
623 Table Create Table
624 t1 CREATE TABLE `t1` (
625 `x` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING,
626 `y` int(11) DEFAULT NULL
627 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
628 create or replace table t1 (
629 x int, y int with system versioning,
630 row_start timestamp(6) as row start,
631 row_end timestamp(6) as row end,
632 period for system_time(row_start, row_end));
633 show create table t1;
634 Table Create Table
635 t1 CREATE TABLE `t1` (
636 `x` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING,
637 `y` int(11) DEFAULT NULL,
638 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START,
639 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END,
640 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
641 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
642 drop table t1;
643 #
644 # MDEV-27452 TIMESTAMP(0) system field is allowed for certain creation of system-versioned table
645 #
646 create or replace table t (
647 a int,
648 s timestamp as row start,
649 e timestamp as row end,
650 period for system_time (s, e))
651 with system versioning;
652 ERROR HY000: `s` must be of type TIMESTAMP(6) for system-versioned table `t`
653 create or replace table t (
654 a int with system versioning,
655 s timestamp as row start,
656 e timestamp as row end,
657 period for system_time (s, e));
658 ERROR HY000: `s` must be of type TIMESTAMP(6) for system-versioned table `t`
659 create or replace table t (
660 a int with system versioning,
661 b int with system versioning,
662 s timestamp(6) as row start,
663 e timestamp(6) as row end,
664 period for system_time (s, e));
665 insert into t () values (),();
666 drop table t;