Debian Codegraph @

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

raw
1 --source include/default_charset.inc
2 --source suite/versioning/common.inc
3
4 select @@system_versioning_alter_history;
5
6 create table t(
7 a int
8 );
9 show create table t;
10 --error ER_VERS_NOT_VERSIONED
11 alter table t drop system versioning;
12
13 alter table t add system versioning;
14 show create table t;
15
16 --error ER_VERS_ALTER_NOT_ALLOWED
17 alter table t add column y int;
18 --error ER_VERS_ALTER_NOT_ALLOWED
19 alter table t add primary key (a);
20 --error ER_VERS_ALTER_NOT_ALLOWED
21 alter table t add unique key (a);
22 --error ER_VERS_ALTER_ENGINE_PROHIBITED
23 alter table t engine innodb;
24
25 alter table t drop system versioning;
26 show create table t;
27
28 set system_versioning_alter_history= keep;
29
30 alter table t add system versioning;
31 --error ER_CANT_DROP_FIELD_OR_KEY
32 alter table t drop system versioning, drop column row_start;
33 alter table t drop system versioning;
34
35 --error ER_VERS_FIELD_WRONG_TYPE
36 alter table t
37 add column trx_start bigint(20) unsigned as row start invisible,
38 add column trx_end bigint(20) unsigned as row end invisible,
39 add period for system_time(trx_start, trx_end),
40 add system versioning;
41
42 --error ER_VERS_FIELD_WRONG_TYPE
43 alter table t
44 add column trx_start timestamp as row start invisible,
45 add column trx_end timestamp as row end invisible,
46 add period for system_time(trx_start, trx_end),
47 add system versioning;
48
49 --error ER_PARSE_ERROR
50 alter table t
51 add column trx_start timestamp(6) not null as row start invisible,
52 add column trx_end timestamp(6) not null as row end invisible,
53 add period for system_time(trx_start, trx_end),
54 add system versioning;
55
56 alter table t
57 add column trx_start timestamp(6) as row start invisible,
58 add column trx_end timestamp(6) as row end invisible,
59 add period for system_time(trx_start, trx_end),
60 add system versioning;
61 show create table t;
62
63 --error ER_MISSING
64 alter table t drop system versioning;
65
66 alter table t drop column trx_start, drop column trx_end;
67 select row_start from t;
68 alter table t drop system versioning;
69 show create table t;
70
71 --error ER_VERS_NOT_VERSIONED
72 alter table t add column trx_start timestamp(6) as row start;
73
74 alter table t add system versioning;
75 show create table t;
76
77 --error ER_MISSING
78 alter table t add column trx_start timestamp(6) as row start;
79 --error ER_WRONG_FIELD_SPEC
80 alter table t modify a int as row start;
81
82 alter table t add column b int;
83 show create table t;
84
85 alter table t add column c int;
86 show create table t;
87
88 alter table t add column d int first;
89 show create table t;
90
91 alter table t add column e int after d;
92 show create table t;
93
94 alter table t drop column a;
95 show create table t;
96
97 create or replace table t (
98 a int,
99 row_start timestamp(6) as row start invisible,
100 row_end timestamp(6) as row end invisible,
101 period for system_time(row_start, row_end))
102 with system versioning;
103
104 select * from t for system_time all;
105 --error ER_MISSING
106 alter table t drop column row_start;
107 --error ER_MISSING
108 alter table t drop column row_end;
109 alter table t drop column row_start, drop column row_end;
110 select * from t for system_time all;
111 show create table t;
112
113 --error ER_CANT_DROP_FIELD_OR_KEY
114 alter table t drop column row_start;
115 --error ER_CANT_DROP_FIELD_OR_KEY
116 alter table t drop column row_end;
117
118 create or replace table t (
119 a int,
120 row_start timestamp(6) as row start invisible,
121 row_end timestamp(6) as row end invisible,
122 period for system_time(row_start, row_end))
123 with system versioning;
124
125 select * from t for system_time all;
126 alter table t drop column row_start, drop column row_end;
127 select * from t for system_time all;
128
129 create or replace table t(
130 a int
131 );
132 insert into t values(1);
133 alter table t add system versioning;
134 show create table t;
135 insert into t values(2);
136 select * from t for system_time all;
137 select * from t;
138
139 update t set a=3 where a=1;
140 select * from t;
141 select * from t for system_time all;
142 --disable_cursor_protocol
143 --enable_prepare_warnings
144 select row_start from t where a=3 into @tm;
145 --disable_prepare_warnings
146 --enable_cursor_protocol
147 alter table t add column b int;
148 select @tm=row_start from t where a=3;
149 show create table t;
150 select * from t;
151 select * from t for system_time all;
152
153 alter table t drop system versioning;
154 select * from t;
155 show create table t;
156
157 --error ER_VERS_NOT_VERSIONED
158 alter table t modify a int with system versioning;
159 --error ER_VERS_NOT_VERSIONED
160 alter table t modify a int without system versioning;
161
162 alter table t add system versioning;
163
164 alter table t modify a int without system versioning;
165 show create table t;
166
167 alter table t modify a int with system versioning;
168 show create table t;
169
170 # TODO: move TRX_ID cases to separate test
171 create or replace table t(
172 a int
173 ) engine=innodb;
174
175 alter table t
176 add column trx_start timestamp(6) as row start invisible,
177 add column trx_end timestamp(6) as row end invisible,
178 add period for system_time(trx_start, trx_end),
179 add system versioning;
180
181 show create table t;
182 --echo # Issue #211: drop of system columns required before drop system versioning
183 alter table t drop column trx_start, drop column trx_end;
184 show create table t;
185
186 alter table t drop system versioning;
187
188 insert into t values(1);
189
190 call verify_trt;
191 alter table t
192 add column trx_start bigint(20) unsigned as row start invisible,
193 add column trx_end bigint(20) unsigned as row end invisible,
194 add period for system_time(trx_start, trx_end),
195 add system versioning;
196 call verify_trt;
197
198 show create table t;
199 alter table t drop column trx_start, drop column trx_end;
200
201 call verify_trt;
202 alter table t drop system versioning, algorithm=copy;
203 call verify_trt;
204
205 alter table t add system versioning, algorithm=copy;
206 call verify_trt;
207
208 show create table t;
209
210 update t set a= 2;
211 select * from t for system_time all;
212
213 alter table t add column b int, algorithm=copy;
214 show create table t;
215 select * from t;
216 call verify_trt;
217
218 alter table t drop column b, algorithm=copy;
219 show create table t;
220 select * from t for system_time all;
221 call verify_trt;
222
223 ## FIXME: #414 IB: inplace for VERS_TIMESTAMP versioning
224 if (0)
225 {
226 alter table t drop system versioning, algorithm=inplace;
227 call verify_trt;
228
229 alter table t add system versioning, algorithm=inplace;
230 call verify_trt;
231 show create table t;
232
233 update t set a= 1;
234 select * from t for system_time all;
235 call verify_trt;
236
237 alter table t add column b int, algorithm=inplace;
238 show create table t;
239 select * from t;
240 call verify_trt;
241
242 alter table t drop column b, algorithm=inplace;
243 show create table t;
244 select * from t for system_time all;
245 }
246 ## FIXME END
247
248 alter table t drop system versioning, algorithm=copy;
249 show create table t;
250 call verify_trt;
251
252 # nullable autoinc test w/o versioning
253 create or replace table t (a int);
254 insert t values (1),(2),(3),(4);
255 alter table t add b int auto_increment null unique;
256 select * from t;
257 drop table t;
258
259 create or replace table t (a int) with system versioning engine=innodb;
260 insert into t values (1), (2), (3);
261 delete from t where a<3;
262 --error ER_DUP_ENTRY, ER_DUP_ENTRY
263 alter table t add b int not null unique;
264 --error ER_UNSUPPORTED_EXTENSION, ER_UNSUPPORTED_EXTENSION
265 alter table t add b int auto_increment unique;
266 alter table t add b int auto_increment null unique;
267 select * from t;
268 select * from t for system_time all;
269 insert into t values (4, 0);
270 select * from t for system_time all;
271 alter table t add c int, drop system versioning;
272 select * from t;
273
274 create or replace table t (a int) with system versioning;
275 insert into t values (1), (2), (3);
276 delete from t where a<3;
277 --error ER_DUP_ENTRY, ER_DUP_ENTRY
278 alter table t add b int not null unique;
279 --error ER_UNSUPPORTED_EXTENSION, ER_UNSUPPORTED_EXTENSION
280 alter table t add b int auto_increment unique;
281 alter table t add b int auto_increment null unique;
282 select * from t;
283 select * from t for system_time all;
284 insert into t values (4, 0);
285 select * from t for system_time all;
286
287 create or replace table t (a int, b int primary key, c int unique) with system versioning;
288 insert t values (1,2,3),(1,3,4),(1,4,5);
289 alter table t drop system versioning;
290 show create table t;
291 select * from t;
292
293 create or replace table t (
294 a int,
295 row_start timestamp(6) as row start invisible,
296 row_end timestamp(6) as row end invisible,
297 period for system_time(row_start, row_end)
298 ) with system versioning;
299 --error ER_VERS_ALTER_SYSTEM_FIELD
300 alter table t change column row_start asdf timestamp(6);
301 insert into t values (1);
302 --error ER_VERS_ALTER_SYSTEM_FIELD
303 alter table t modify column row_start bigint unsigned;
304
305 ## These experimental options are now disabled
306
307 --error ER_WRONG_VALUE_FOR_VAR
308 set system_versioning_alter_history= SURVIVE;
309
310 --error ER_WRONG_VALUE_FOR_VAR
311 set system_versioning_alter_history= 'DROP';
312
313 if (0)
314 {
315 create or replace table t (a int) with system versioning engine innodb;
316 insert into t values (1);
317 update t set a = 2;
318 select * from t for system_time all;
319 alter table t add column b int;
320 select * from t for system_time all;
321
322 create or replace table t (a int) with system versioning engine myisam;
323 insert into t values (1);
324 update t set a = 2;
325 select * from t for system_time all;
326 alter table t add column b int;
327 select * from t for system_time all;
328
329 create or replace table non_empty (
330 a int,
331 row_start bigint(20) unsigned,
332 row_end bigint(20) unsigned
333 ) engine innodb;
334 insert into non_empty values (1, 100, 200);
335
336 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
337 alter table non_empty
338 change column row_start row_start bigint(20) unsigned as row start invisible;
339 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
340 alter table non_empty
341 change column row_end row_end bigint(20) unsigned as row end invisible;
342 drop table non_empty;
343
344 create or replace table t (a int primary key) with system versioning;
345 insert into t values (1);
346 update t set a=2;
347 alter table t drop primary key, add primary key (a), drop system versioning;
348 select * from t;
349 --replace_result InnoDB INNODB_OR_MYISAM MyISAM INNODB_OR_MYISAM
350 show create table t;
351
352 create or replace table t (a int primary key) with system versioning;
353 insert into t values (1);
354 update t set a=2;
355 alter table t drop system versioning;
356 select * from t;
357 --replace_result InnoDB INNODB_OR_MYISAM MyISAM INNODB_OR_MYISAM
358 show create table t;
359
360
361 call verify_trt;
362 }
363
364 create or replace table t (a int) with system versioning;
365 --error ER_VERS_ALREADY_VERSIONED
366 alter table t add system versioning;
367 --error ER_VERS_ALREADY_VERSIONED
368 alter table t add system versioning, drop system versioning;
369
370 set @@system_versioning_alter_history=keep;
371 create or replace table t(x int, y int) with system versioning engine=innodb;
372 alter table t modify y int without system versioning;
373 insert into t values(1, 1);
374 update t set y=2;
375
376 --echo # MDEV-14681 Bogus ER_UNSUPPORTED_EXTENSION
377 create or replace table t1 (pk int auto_increment unique) with system versioning;
378 insert into t1 values (1);
379 delete from t1;
380 alter table t1 engine=myisam;
381
382 --echo # MDEV-14692 crash in MDL_context::upgrade_shared_lock()
383 create or replace temporary table t (a int);
384 --error ER_VERS_NOT_SUPPORTED
385 alter table t change column if exists b c bigint unsigned generated always as row start;
386 --error ER_VERS_NOT_SUPPORTED
387 alter table t change column if exists b c bigint unsigned generated always as row end;
388 --error ER_VERS_NOT_SUPPORTED
389 alter table t add system versioning;
390 drop table t;
391
392 --echo # MDEV-14744 trx_id-based and transaction-based mixup in assertion
393 create or replace table t (c text) engine=innodb with system versioning;
394 show create table t;
395 alter table t add fulltext key (c);
396
397 create or replace table t (a int) with system versioning;
398 --error ER_VERS_TABLE_MUST_HAVE_COLUMNS
399 alter table t drop column a;
400 --error ER_CANT_DROP_FIELD_OR_KEY
401 alter table t drop column a, drop column a;
402
403 create or replace table t1 (row_start int);
404 --error ER_DUP_FIELDNAME
405 alter table t1 with system versioning;
406
407 create or replace table t1 (row_end int);
408 --error ER_DUP_FIELDNAME
409 alter table t1 with system versioning;
410
411 --error ER_DUP_FIELDNAME
412 create or replace table t1 (a int, row_start int) with system versioning;
413
414 create or replace table t1 (a int) with system versioning;
415
416 --error ER_DUP_FIELDNAME
417 set statement system_versioning_alter_history=keep for
418 alter table t1 add column row_start int;
419
420 --error ER_DUP_FIELDNAME
421 set statement system_versioning_alter_history=keep for
422 alter table t1 add column row_start timestamp(6);
423
424 --echo # MDEV-14798 Add, drop system versioning semantic and syntax
425 create or replace table t (
426 a int,
427 row_start timestamp(6) generated always as row start,
428 row_end timestamp(6) generated always as row end,
429 period for system_time(row_start, row_end)
430 ) with system versioning;
431 show create table t;
432
433 alter table t
434 drop column row_start,
435 drop column row_end,
436 drop period for system_time,
437 drop system versioning;
438 show create table t;
439
440 --error ER_VERS_NOT_VERSIONED
441 alter table t drop period for system_time;
442
443 create or replace table t (
444 a int,
445 row_start timestamp(6) generated always as row start,
446 row_end timestamp(6) generated always as row end,
447 period for system_time(row_start, row_end)
448 ) with system versioning;
449 --error ER_MISSING
450 alter table t drop period for system_time;
451 --error ER_MISSING
452 alter table t drop column sys_trx_start, drop period for system_time;
453 --error ER_MISSING
454 alter table t drop column sys_trx_end, drop period for system_time;
455
456 --error ER_VERS_ALREADY_VERSIONED
457 alter table t add period for system_time(sys_trx_start, sys_trx_end);
458
459 --echo #
460 --echo # MDEV-14790 System versioning for system tables does not work as expected
461 --echo #
462 use mysql;
463 --error ER_VERS_DB_NOT_SUPPORTED
464 create or replace table t (x int) with system versioning;
465 --error ER_VERS_DB_NOT_SUPPORTED
466 alter table db add system versioning;
467 use test;
468
469 --echo # MDEV-15956 Strange ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN upon ALTER on versioning column
470 create or replace table t1 (i int, j int as (i), s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning;
471 --error ER_VERS_DUPLICATE_ROW_START_END
472 alter table t1 modify s timestamp(6) as row start;
473
474 --echo # ignore CHECK for historical rows
475 create or replace table t (a int) with system versioning;
476 insert into t values (0), (1);
477 delete from t where a = 0;
478 --error ER_CONSTRAINT_FAILED
479 alter table t add check (a > 1);
480 alter table t add check (a > 0);
481 --error ER_CONSTRAINT_FAILED
482 insert into t values (0);
483 insert into t values (2);
484 drop table t;
485
486 --echo #
487 --echo # MDEV-18869 Assertion `!((field)->vcol_info && (field)->stored_in_db())' failed in innodb_col_no upon altering table with system versioning
488 --echo #
489 set system_versioning_alter_history= keep;
490 create or replace table t1 (a int, b int generated always as (0) stored) engine=innodb with system versioning;
491 insert into t1 (a) values (1);
492 --enable_info
493 alter table t1 modify a int without system versioning, algorithm=copy;
494 alter table t1 modify a int with system versioning, algorithm=copy;
495 alter table t1 modify a int without system versioning;
496 alter table t1 modify a int with system versioning;
497 --disable_info
498 show create table t1;
499 select * from t1;
500 --enable_info
501 --error ER_PARSE_ERROR
502 alter table t1 modify b int generated always as (0) stored without system versioning;
503 --error ER_PARSE_ERROR
504 alter table t1 modify b int generated always as (0) stored with system versioning;
505 alter table t1 modify b int without system versioning;
506 --disable_info
507 show create table t1;
508 select * from t1;
509
510 create or replace table t1 (a int, b int generated always as (0) virtual) engine=innodb with system versioning;
511 insert into t1 (a) values (1);
512 --enable_info
513 alter table t1 modify a int without system versioning, algorithm=copy;
514 alter table t1 modify a int with system versioning, algorithm=copy;
515 alter table t1 modify a int without system versioning;
516 alter table t1 modify a int with system versioning;
517 select * from t1;
518 --disable_info
519
520 --echo #
521 --echo # MDEV-19304 Segfault in ALTER TABLE after UPDATE for SIMULTANEOUS_ASSIGNMENT
522 --echo #
523 create or replace table t1 (a int, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) engine=myisam with system versioning;
524 insert into t1 values (null, null, null);
525 insert into t1 values (null, null, null);
526 set sql_mode= 'simultaneous_assignment';
527 update t1 set e= 1;
528 alter table t1 force;
529 set sql_mode= default;
530
531 --echo #
532 --echo # MDEV-18862 Unfortunate error message upon attempt to drop system versioning
533 --echo #
534 set system_versioning_alter_history= keep;
535 create or replace table t1 (x int) with system versioning;
536 --error ER_CANT_DROP_FIELD_OR_KEY
537 alter table t1 drop column `row_start`, drop column `row_end`, drop period for system_time, drop system versioning;
538 --error ER_CANT_DROP_FIELD_OR_KEY
539 alter table t1 drop period for system_time;
540 --error ER_CANT_DROP_FIELD_OR_KEY
541 alter table t1 drop column `row_start`, drop column `row_end`, drop system versioning;
542 --error ER_CANT_DROP_FIELD_OR_KEY
543 alter table t1 drop column `row_end`;
544
545 --echo #
546 --echo # MDEV-19127 Assertion `row_start_field' failed in vers_prepare_keys upon ALTER TABLE
547 --echo #
548 set system_versioning_alter_history=keep;
549 create or replace table t1 (f1 int) with system versioning;
550 alter table t1 add f2 int with system versioning, drop system versioning;
551 create or replace table t1 (f1 int) with system versioning;
552 --error ER_VERS_NOT_VERSIONED
553 alter table t1 drop system versioning, add f2 int with system versioning;
554
555 drop table t1;
556 --echo # MDEV-16490 It's possible to make a system versioned table without any versioning field
557
558 set @@system_versioning_alter_history=keep;
559 create or replace table t (a int) with system versioning engine=innodb;
560 --error ER_VERS_TABLE_MUST_HAVE_COLUMNS
561 alter table t change column a a int without system versioning;
562
563 alter table t
564 change column a a int without system versioning,
565 add column b int with system versioning;
566 show create table t;
567
568 alter table t
569 change column a new_a int,
570 drop system versioning;
571 show create table t;
572
573 alter table t add system versioning;
574 alter table t change column new_a a int without system versioning;
575 show create table t;
576
577 --error ER_VERS_TABLE_MUST_HAVE_COLUMNS
578 alter table t
579 add column c int,
580 change column c c int without system versioning,
581 change column b b int without system versioning;
582
583 alter table t
584 add column c int without system versioning,
585 change column c c int,
586 change column b b int without system versioning;
587
588 drop table t;
589
590 --echo #
591 --echo # MDEV-21688 Assertion or ER_WARN_DATA_OUT_OF_RANGE upon ALTER on previously versioned table
592 --echo #
593 create or replace table t1 (a int) with system versioning;
594 insert into t1 values (128);
595 delete from t1;
596 set statement system_versioning_alter_history=keep for
597 alter table t1 drop system versioning, modify column a tinyint;
598
599 # cleanup
600 drop table t1;
601
602 --echo #
603 --echo # MDEV-24690 Dropping primary key column from versioned table always fails with 1072
604 --echo #
605 create table t1 (a int, b int primary key) with system versioning;
606 alter table t1 drop column b;
607
608 create or replace table t1 (
609 a int, b int primary key,
610 row_start timestamp(6) as row start,
611 row_end timestamp(6) as row end,
612 period for system_time(row_start, row_end)
613 ) with system versioning;
614 show create table t1;
615 --error ER_KEY_COLUMN_DOES_NOT_EXIST
616 alter table t1 drop column b;
617
618 create or replace table t1 (
619 a int, b int primary key,
620 row_start timestamp(6) as row start invisible,
621 row_end timestamp(6) as row end invisible,
622 period for system_time(row_start, row_end)
623 ) with system versioning;
624 show create table t1;
625 --error ER_KEY_COLUMN_DOES_NOT_EXIST
626 alter table t1 drop column b;
627
628 # cleanup
629 drop table t1;
630
631 --echo #
632 --echo # MDEV-25172 Wrong error message for ADD COLUMN .. AS ROW START
633 --echo #
634 create or replace table t1 (x int);
635 --error ER_VERS_NOT_VERSIONED
636 alter table t1 add column y timestamp(6) as row start;
637 # cleanup
638 drop table t1;
639
640
641 --echo #
642 --echo # MDEV-25327 Unexpected ER_DUP_ENTRY upon dropping PK column from system-versioned table
643 --echo #
644 create table t1 (pk int, a int, primary key (pk), key (a))
645 with system versioning;
646 insert into t1 values (1, 1), (2, 2);
647 delete from t1;
648 set system_versioning_alter_history= keep;
649 alter table t1 drop pk;
650 # cleanup
651 drop table t1;
652
653 create table t1 (pk int, a int, primary key (pk), key (a))
654 with system versioning;
655 insert into t1 values (1, 2), (2, 8), (3, 4), (4, 4), (5, 0);
656 delete from t1;
657 set system_versioning_alter_history= keep;
658 alter ignore table t1 drop pk;
659 # cleanup
660 drop table t1;
661
662
663 --echo #
664 --echo # MDEV-22660 SIGSEGV on adding system versioning and modifying system column
665 --echo #
666 create or replace table t1 (a int);
667 --error ER_VERS_PERIOD_COLUMNS
668 alter table t1
669 add row_start timestamp(6) as row start,
670 add row_end timestamp(6) as row end,
671 add period for system_time(row_start, row_end),
672 with system versioning,
673 modify row_end varchar(8);
674 --error ER_VERS_PERIOD_COLUMNS
675 alter table t1
676 add row_start timestamp(6) as row start,
677 add row_end timestamp(6) as row end,
678 add period for system_time(row_start, row_end),
679 with system versioning,
680 modify row_start varchar(8);
681 # cleanup
682 drop table t1;
683
684 --echo #
685 --echo # MDEV-20545 Assertion (col.vers_sys_end()) upon inplace ALTER with virtual columns
686 --echo #
687 create table t1 (
688 a int,
689 va int as (a) virtual,
690 b int,
691 vb int as (b) virtual,
692 c int,
693 vc int as (c) virtual,
694 d int,
695 e int,
696 index(va)
697 ) engine=innodb with system versioning;
698 replace into t1 () values (),();
699 set statement system_versioning_alter_history=keep for alter table t1 drop e;
700 alter table t1 algorithm=inplace, drop system versioning;
701 # cleanup
702 drop table t1;
703
704 --echo #
705 --echo # MDEV-20765 Assertion (type.vers_sys_end()) upon inplace ALTER with virtual columns
706 --echo #
707 create table t1 (
708 a int,
709 b int as (a) virtual,
710 c int,
711 d int as (c) virtual,
712 e int,
713 f int as (e) virtual,
714 g int,
715 h int,
716 i int,
717 index(d),
718 key(h),
719 foreign key (g) references t1 (h)
720 ) engine=innodb with system versioning;
721 set system_versioning_alter_history= keep;
722 alter table t1 drop column i;
723 insert into t1 (g,h) values (1,1);
724 # cleanup
725 drop table t1;
726
727 --echo #
728 --echo # MDEV-29034 Assertion (o->ind == vers_start) upon inplace ALTER with virtual columns
729 --echo #
730 create table b (
731 pk integer auto_increment,
732 col_int_key integer,
733 col_varchar_key varchar(1),
734 o bit, n bit,
735 h float as ( n + 2 ) virtual,
736 v bit,
737 primary key (pk),
738 key (col_varchar_key, col_int_key)
739 ) engine = innodb;
740 set `system_versioning_alter_history`= keep;
741 alter table `b` add system versioning;
742 alter table `b` add column if not exists ( w bit, v serial );
743 alter table `b` add column if not exists ( p bit );
744 drop table `b`;
745
746 --echo #
747 --echo # MDEV-30528 Assertion !mbmaxlen || ... failed
748 --echo # in dtype_get_at_most_n_mbchars()
749 --echo #
750
751 CREATE TABLE t(f TEXT) WITH SYSTEM VERSIONING CHARACTER SET utf8 ENGINE=InnoDB;
752 INSERT INTO t VALUES ('foo');
753 DELETE FROM t;
754 --enable_info
755 ALTER TABLE t ADD FULLTEXT (f);
756 --disable_info
757 DROP TABLE t;
758
759 --echo # End of 10.4 tests
760
761 --echo #
762 --echo # MDEV-21941 RENAME doesn't work for system time or period fields
763 --echo #
764 create or replace table t1 (a int) with system versioning;
765 --error ER_BAD_FIELD_ERROR
766 alter table t1 rename column row_start to x;
767
768 create or replace table t1 (
769 a int,
770 row_start timestamp(6) as row start invisible,
771 row_end timestamp(6) as row end invisible,
772 period for system_time (row_start, row_end)
773 ) with system versioning;
774
775 alter table t1 rename column row_start to x;
776 alter table t1 rename column row_end to y;
777
778 show create table t1;
779 # cleanup
780 drop table t1;
781
782 --echo # End of 10.5 tests
783
784 --echo #
785 --echo # MDEV-27293 Allow converting a versioned table from implicit
786 --echo # to explicit row_start/row_end columns
787 --echo #
788 create table t1 (x int) with system versioning;
789 insert t1 values (0), (1);
790 update t1 set x= x + 10;
791 set @old_system_versioning_alter_history= @@system_versioning_alter_history;
792 set @@system_versioning_alter_history= keep;
793 alter table t1 add column y int;
794 show create table t1;
795 --error ER_MISSING
796 alter table t1 add column rs timestamp(6) as row start;
797 --error ER_MISSING
798 alter table t1 add column re timestamp(6) as row end, add period for system_time (row_start, re);
799 --error ER_MISSING
800 alter table t1 add column rs timestamp(6) as row start, add column re timestamp(6) as row end;
801 --error ER_BAD_FIELD_ERROR
802 alter table t1 rename column row_start to re;
803 --error ER_WRONG_FIELD_SPEC
804 alter table t1 add column rs timestamp(5) as row start, add column re timestamp(6) as row end, add period for system_time (rs,re);
805 alter table t1 add column rs timestamp(6) as row start, add column re timestamp(6) as row end, add period for system_time (rs,re);
806 show create table t1;
807 select x, check_row_ts(rs, re) from t1 for system_time all order by x;
808
809 create or replace table t1 (x int) engine innodb with system versioning;
810 alter table t1 add column rs timestamp(6) as row start, add column re timestamp(6) as row end, add period for system_time (rs,re);
811 show create table t1;
812
813 set @@system_versioning_alter_history= @old_system_versioning_alter_history;
814 drop table t1;
815
816 --echo # End of 11.6 tests
817 --source suite/versioning/common_finish.inc