Debian Codegraph @

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

raw
1 select @@system_versioning_alter_history;
2 @@system_versioning_alter_history
3 ERROR
4 create table t(
5 a int
6 );
7 show create table t;
8 Table Create Table
9 t CREATE TABLE `t` (
10 `a` int(11) DEFAULT NULL
11 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
12 alter table t drop system versioning;
13 ERROR HY000: Table `t` is not system-versioned
14 alter table t add system versioning;
15 show create table t;
16 Table Create Table
17 t CREATE TABLE `t` (
18 `a` int(11) DEFAULT NULL
19 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
20 alter table t add column y int;
21 ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER.
22 alter table t add primary key (a);
23 ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER.
24 alter table t add unique key (a);
25 ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER.
26 alter table t engine innodb;
27 ERROR HY000: Not allowed for system-versioned `test`.`t`. Change to/from native system versioning engine is not supported.
28 alter table t drop system versioning;
29 show create table t;
30 Table Create Table
31 t CREATE TABLE `t` (
32 `a` int(11) DEFAULT NULL
33 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
34 set system_versioning_alter_history= keep;
35 alter table t add system versioning;
36 alter table t drop system versioning, drop column row_start;
37 ERROR 42000: Can't DROP COLUMN `row_start`; check that it exists
38 alter table t drop system versioning;
39 alter table t
40 add column trx_start bigint(20) unsigned as row start invisible,
41 add column trx_end bigint(20) unsigned as row end invisible,
42 add period for system_time(trx_start, trx_end),
43 add system versioning;
44 ERROR HY000: `trx_start` must be of type TIMESTAMP(6) for system-versioned table `t`
45 alter table t
46 add column trx_start timestamp as row start invisible,
47 add column trx_end timestamp as row end invisible,
48 add period for system_time(trx_start, trx_end),
49 add system versioning;
50 ERROR HY000: `trx_start` must be of type TIMESTAMP(6) for system-versioned table `t`
51 alter table t
52 add column trx_start timestamp(6) not null as row start invisible,
53 add column trx_end timestamp(6) not null as row end invisible,
54 add period for system_time(trx_start, trx_end),
55 add system versioning;
56 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as row start invisible,
57 add column trx_end timestamp(6) not null as row end i...' at line 2
58 alter table t
59 add column trx_start timestamp(6) as row start invisible,
60 add column trx_end timestamp(6) as row end invisible,
61 add period for system_time(trx_start, trx_end),
62 add system versioning;
63 show create table t;
64 Table Create Table
65 t CREATE TABLE `t` (
66 `a` int(11) DEFAULT NULL,
67 `trx_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
68 `trx_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
69 PERIOD FOR SYSTEM_TIME (`trx_start`, `trx_end`)
70 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
71 alter table t drop system versioning;
72 ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `trx_start`'
73 alter table t drop column trx_start, drop column trx_end;
74 select row_start from t;
75 row_start
76 alter table t drop system versioning;
77 show create table t;
78 Table Create Table
79 t CREATE TABLE `t` (
80 `a` int(11) DEFAULT NULL
81 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
82 alter table t add column trx_start timestamp(6) as row start;
83 ERROR HY000: Table `t` is not system-versioned
84 alter table t add system versioning;
85 show create table t;
86 Table Create Table
87 t CREATE TABLE `t` (
88 `a` int(11) DEFAULT NULL
89 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
90 alter table t add column trx_start timestamp(6) as row start;
91 ERROR HY000: Wrong parameters for `t`: missing 'AS ROW END'
92 alter table t modify a int as row start;
93 ERROR 42000: Incorrect column specifier for column 'a'
94 alter table t add column b int;
95 show create table t;
96 Table Create Table
97 t CREATE TABLE `t` (
98 `a` int(11) DEFAULT NULL,
99 `b` int(11) DEFAULT NULL
100 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
101 alter table t add column c int;
102 show create table t;
103 Table Create Table
104 t CREATE TABLE `t` (
105 `a` int(11) DEFAULT NULL,
106 `b` int(11) DEFAULT NULL,
107 `c` int(11) DEFAULT NULL
108 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
109 alter table t add column d int first;
110 show create table t;
111 Table Create Table
112 t CREATE TABLE `t` (
113 `d` int(11) DEFAULT NULL,
114 `a` int(11) DEFAULT NULL,
115 `b` int(11) DEFAULT NULL,
116 `c` int(11) DEFAULT NULL
117 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
118 alter table t add column e int after d;
119 show create table t;
120 Table Create Table
121 t CREATE TABLE `t` (
122 `d` int(11) DEFAULT NULL,
123 `e` int(11) DEFAULT NULL,
124 `a` int(11) DEFAULT NULL,
125 `b` int(11) DEFAULT NULL,
126 `c` int(11) DEFAULT NULL
127 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
128 alter table t drop column a;
129 show create table t;
130 Table Create Table
131 t CREATE TABLE `t` (
132 `d` int(11) DEFAULT NULL,
133 `e` int(11) DEFAULT NULL,
134 `b` int(11) DEFAULT NULL,
135 `c` int(11) DEFAULT NULL
136 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
137 create or replace table t (
138 a int,
139 row_start timestamp(6) as row start invisible,
140 row_end timestamp(6) as row end invisible,
141 period for system_time(row_start, row_end))
142 with system versioning;
143 select * from t for system_time all;
144 a
145 alter table t drop column row_start;
146 ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `row_end`'
147 alter table t drop column row_end;
148 ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `row_start`'
149 alter table t drop column row_start, drop column row_end;
150 select * from t for system_time all;
151 a
152 show create table t;
153 Table Create Table
154 t CREATE TABLE `t` (
155 `a` int(11) DEFAULT NULL
156 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
157 alter table t drop column row_start;
158 ERROR 42000: Can't DROP COLUMN `row_start`; check that it exists
159 alter table t drop column row_end;
160 ERROR 42000: Can't DROP COLUMN `row_end`; check that it exists
161 create or replace table t (
162 a int,
163 row_start timestamp(6) as row start invisible,
164 row_end timestamp(6) as row end invisible,
165 period for system_time(row_start, row_end))
166 with system versioning;
167 select * from t for system_time all;
168 a
169 alter table t drop column row_start, drop column row_end;
170 select * from t for system_time all;
171 a
172 create or replace table t(
173 a int
174 );
175 insert into t values(1);
176 alter table t add system versioning;
177 show create table t;
178 Table Create Table
179 t CREATE TABLE `t` (
180 `a` int(11) DEFAULT NULL
181 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
182 insert into t values(2);
183 select * from t for system_time all;
184 a
185 1
186 2
187 select * from t;
188 a
189 1
190 2
191 update t set a=3 where a=1;
192 select * from t;
193 a
194 3
195 2
196 select * from t for system_time all;
197 a
198 3
199 2
200 1
201 select row_start from t where a=3 into @tm;
202 alter table t add column b int;
203 select @tm=row_start from t where a=3;
204 @tm=row_start
205 1
206 show create table t;
207 Table Create Table
208 t CREATE TABLE `t` (
209 `a` int(11) DEFAULT NULL,
210 `b` int(11) DEFAULT NULL
211 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
212 select * from t;
213 a b
214 3 NULL
215 2 NULL
216 select * from t for system_time all;
217 a b
218 3 NULL
219 2 NULL
220 1 NULL
221 alter table t drop system versioning;
222 select * from t;
223 a b
224 3 NULL
225 2 NULL
226 show create table t;
227 Table Create Table
228 t CREATE TABLE `t` (
229 `a` int(11) DEFAULT NULL,
230 `b` int(11) DEFAULT NULL
231 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
232 alter table t modify a int with system versioning;
233 ERROR HY000: Table `t` is not system-versioned
234 alter table t modify a int without system versioning;
235 ERROR HY000: Table `t` is not system-versioned
236 alter table t add system versioning;
237 alter table t modify a int without system versioning;
238 show create table t;
239 Table Create Table
240 t CREATE TABLE `t` (
241 `a` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING,
242 `b` int(11) DEFAULT NULL
243 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
244 alter table t modify a int with system versioning;
245 show create table t;
246 Table Create Table
247 t CREATE TABLE `t` (
248 `a` int(11) DEFAULT NULL,
249 `b` int(11) DEFAULT NULL
250 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
251 create or replace table t(
252 a int
253 ) engine=innodb;
254 alter table t
255 add column trx_start timestamp(6) as row start invisible,
256 add column trx_end timestamp(6) as row end invisible,
257 add period for system_time(trx_start, trx_end),
258 add system versioning;
259 show create table t;
260 Table Create Table
261 t CREATE TABLE `t` (
262 `a` int(11) DEFAULT NULL,
263 `trx_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
264 `trx_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
265 PERIOD FOR SYSTEM_TIME (`trx_start`, `trx_end`)
266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
267 # Issue #211: drop of system columns required before drop system versioning
268 alter table t drop column trx_start, drop column trx_end;
269 show create table t;
270 Table Create Table
271 t CREATE TABLE `t` (
272 `a` int(11) DEFAULT NULL
273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
274 alter table t drop system versioning;
275 insert into t values(1);
276 call verify_trt;
277 No A B C D
278 alter table t
279 add column trx_start bigint(20) unsigned as row start invisible,
280 add column trx_end bigint(20) unsigned as row end invisible,
281 add period for system_time(trx_start, trx_end),
282 add system versioning;
283 call verify_trt;
284 No A B C D
285 1 1 1 1 1
286 show create table t;
287 Table Create Table
288 t CREATE TABLE `t` (
289 `a` int(11) DEFAULT NULL,
290 `trx_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START INVISIBLE,
291 `trx_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END INVISIBLE,
292 PERIOD FOR SYSTEM_TIME (`trx_start`, `trx_end`)
293 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
294 alter table t drop column trx_start, drop column trx_end;
295 call verify_trt;
296 No A B C D
297 alter table t drop system versioning, algorithm=copy;
298 call verify_trt;
299 No A B C D
300 alter table t add system versioning, algorithm=copy;
301 call verify_trt;
302 No A B C D
303 show create table t;
304 Table Create Table
305 t CREATE TABLE `t` (
306 `a` int(11) DEFAULT NULL
307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
308 update t set a= 2;
309 select * from t for system_time all;
310 a
311 2
312 1
313 alter table t add column b int, algorithm=copy;
314 show create table t;
315 Table Create Table
316 t CREATE TABLE `t` (
317 `a` int(11) DEFAULT NULL,
318 `b` int(11) DEFAULT NULL
319 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
320 select * from t;
321 a b
322 2 NULL
323 call verify_trt;
324 No A B C D
325 alter table t drop column b, algorithm=copy;
326 show create table t;
327 Table Create Table
328 t CREATE TABLE `t` (
329 `a` int(11) DEFAULT NULL
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
331 select * from t for system_time all;
332 a
333 2
334 1
335 call verify_trt;
336 No A B C D
337 alter table t drop system versioning, algorithm=copy;
338 show create table t;
339 Table Create Table
340 t CREATE TABLE `t` (
341 `a` int(11) DEFAULT NULL
342 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
343 call verify_trt;
344 No A B C D
345 create or replace table t (a int);
346 insert t values (1),(2),(3),(4);
347 alter table t add b int auto_increment null unique;
348 select * from t;
349 a b
350 1 1
351 2 2
352 3 3
353 4 4
354 drop table t;
355 create or replace table t (a int) with system versioning engine=innodb;
356 insert into t values (1), (2), (3);
357 delete from t where a<3;
358 alter table t add b int not null unique;
359 Got one of the listed errors
360 alter table t add b int auto_increment unique;
361 Got one of the listed errors
362 alter table t add b int auto_increment null unique;
363 select * from t;
364 a b
365 3 1
366 select * from t for system_time all;
367 a b
368 1 NULL
369 2 NULL
370 3 1
371 insert into t values (4, 0);
372 select * from t for system_time all;
373 a b
374 1 NULL
375 2 NULL
376 3 1
377 4 2
378 alter table t add c int, drop system versioning;
379 select * from t;
380 a b c
381 3 1 NULL
382 4 2 NULL
383 create or replace table t (a int) with system versioning;
384 insert into t values (1), (2), (3);
385 delete from t where a<3;
386 alter table t add b int not null unique;
387 Got one of the listed errors
388 alter table t add b int auto_increment unique;
389 Got one of the listed errors
390 alter table t add b int auto_increment null unique;
391 select * from t;
392 a b
393 3 1
394 select * from t for system_time all;
395 a b
396 1 NULL
397 2 NULL
398 3 1
399 insert into t values (4, 0);
400 select * from t for system_time all;
401 a b
402 1 NULL
403 2 NULL
404 3 1
405 4 2
406 create or replace table t (a int, b int primary key, c int unique) with system versioning;
407 insert t values (1,2,3),(1,3,4),(1,4,5);
408 alter table t drop system versioning;
409 show create table t;
410 Table Create Table
411 t CREATE TABLE `t` (
412 `a` int(11) DEFAULT NULL,
413 `b` int(11) NOT NULL,
414 `c` int(11) DEFAULT NULL,
415 PRIMARY KEY (`b`),
416 UNIQUE KEY `c` (`c`)
417 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
418 select * from t;
419 a b c
420 1 2 3
421 1 3 4
422 1 4 5
423 create or replace table t (
424 a int,
425 row_start timestamp(6) as row start invisible,
426 row_end timestamp(6) as row end invisible,
427 period for system_time(row_start, row_end)
428 ) with system versioning;
429 alter table t change column row_start asdf timestamp(6);
430 ERROR HY000: Can not change system versioning field `row_start`
431 insert into t values (1);
432 alter table t modify column row_start bigint unsigned;
433 ERROR HY000: Can not change system versioning field `row_start`
434 set system_versioning_alter_history= SURVIVE;
435 ERROR 42000: Variable 'system_versioning_alter_history' can't be set to the value of 'SURVIVE'
436 set system_versioning_alter_history= 'DROP';
437 ERROR 42000: Variable 'system_versioning_alter_history' can't be set to the value of 'DROP'
438 create or replace table t (a int) with system versioning;
439 alter table t add system versioning;
440 ERROR HY000: Table `t` is already system-versioned
441 alter table t add system versioning, drop system versioning;
442 ERROR HY000: Table `t` is already system-versioned
443 set @@system_versioning_alter_history=keep;
444 create or replace table t(x int, y int) with system versioning engine=innodb;
445 alter table t modify y int without system versioning;
446 insert into t values(1, 1);
447 update t set y=2;
448 # MDEV-14681 Bogus ER_UNSUPPORTED_EXTENSION
449 create or replace table t1 (pk int auto_increment unique) with system versioning;
450 insert into t1 values (1);
451 delete from t1;
452 alter table t1 engine=myisam;
453 # MDEV-14692 crash in MDL_context::upgrade_shared_lock()
454 create or replace temporary table t (a int);
455 alter table t change column if exists b c bigint unsigned generated always as row start;
456 ERROR HY000: System-versioned tables do not support CREATE TEMPORARY TABLE
457 alter table t change column if exists b c bigint unsigned generated always as row end;
458 ERROR HY000: System-versioned tables do not support CREATE TEMPORARY TABLE
459 alter table t add system versioning;
460 ERROR HY000: System-versioned tables do not support CREATE TEMPORARY TABLE
461 drop table t;
462 # MDEV-14744 trx_id-based and transaction-based mixup in assertion
463 create or replace table t (c text) engine=innodb with system versioning;
464 show create table t;
465 Table Create Table
466 t CREATE TABLE `t` (
467 `c` text DEFAULT NULL
468 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
469 alter table t add fulltext key (c);
470 create or replace table t (a int) with system versioning;
471 alter table t drop column a;
472 ERROR HY000: Table `t` must have at least one versioned column
473 alter table t drop column a, drop column a;
474 ERROR 42000: Can't DROP COLUMN `a`; check that it exists
475 create or replace table t1 (row_start int);
476 alter table t1 with system versioning;
477 ERROR 42S21: Duplicate column name 'row_start'
478 create or replace table t1 (row_end int);
479 alter table t1 with system versioning;
480 ERROR 42S21: Duplicate column name 'row_end'
481 create or replace table t1 (a int, row_start int) with system versioning;
482 ERROR 42S21: Duplicate column name 'row_start'
483 create or replace table t1 (a int) with system versioning;
484 set statement system_versioning_alter_history=keep for
485 alter table t1 add column row_start int;
486 ERROR 42S21: Duplicate column name 'row_start'
487 set statement system_versioning_alter_history=keep for
488 alter table t1 add column row_start timestamp(6);
489 ERROR 42S21: Duplicate column name 'row_start'
490 # MDEV-14798 Add, drop system versioning semantic and syntax
491 create or replace table t (
492 a int,
493 row_start timestamp(6) generated always as row start,
494 row_end timestamp(6) generated always as row end,
495 period for system_time(row_start, row_end)
496 ) with system versioning;
497 show create table t;
498 Table Create Table
499 t CREATE TABLE `t` (
500 `a` int(11) DEFAULT NULL,
501 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START,
502 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END,
503 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
504 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
505 alter table t
506 drop column row_start,
507 drop column row_end,
508 drop period for system_time,
509 drop system versioning;
510 show create table t;
511 Table Create Table
512 t CREATE TABLE `t` (
513 `a` int(11) DEFAULT NULL
514 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
515 alter table t drop period for system_time;
516 ERROR HY000: Table `t` is not system-versioned
517 create or replace table t (
518 a int,
519 row_start timestamp(6) generated always as row start,
520 row_end timestamp(6) generated always as row end,
521 period for system_time(row_start, row_end)
522 ) with system versioning;
523 alter table t drop period for system_time;
524 ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `row_start`, DROP COLUMN `row_end`'
525 alter table t drop column sys_trx_start, drop period for system_time;
526 ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `row_start`, DROP COLUMN `row_end`'
527 alter table t drop column sys_trx_end, drop period for system_time;
528 ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `row_start`, DROP COLUMN `row_end`'
529 alter table t add period for system_time(sys_trx_start, sys_trx_end);
530 ERROR HY000: Table `t` is already system-versioned
531 #
532 # MDEV-14790 System versioning for system tables does not work as expected
533 #
534 use mysql;
535 create or replace table t (x int) with system versioning;
536 ERROR HY000: System-versioned tables in the `mysql` database are not supported
537 alter table db add system versioning;
538 ERROR HY000: System-versioned tables in the `mysql` database are not supported
539 use test;
540 # MDEV-15956 Strange ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN upon ALTER on versioning column
541 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;
542 alter table t1 modify s timestamp(6) as row start;
543 ERROR HY000: Duplicate ROW START column `s`
544 # ignore CHECK for historical rows
545 create or replace table t (a int) with system versioning;
546 insert into t values (0), (1);
547 delete from t where a = 0;
548 alter table t add check (a > 1);
549 ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t`
550 alter table t add check (a > 0);
551 insert into t values (0);
552 ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t`
553 insert into t values (2);
554 drop table t;
555 #
556 # MDEV-18869 Assertion `!((field)->vcol_info && (field)->stored_in_db())' failed in innodb_col_no upon altering table with system versioning
557 #
558 set system_versioning_alter_history= keep;
559 create or replace table t1 (a int, b int generated always as (0) stored) engine=innodb with system versioning;
560 insert into t1 (a) values (1);
561 alter table t1 modify a int without system versioning, algorithm=copy;
562 affected rows: 1
563 info: Records: 1 Duplicates: 0 Warnings: 0
564 alter table t1 modify a int with system versioning, algorithm=copy;
565 affected rows: 1
566 info: Records: 1 Duplicates: 0 Warnings: 0
567 alter table t1 modify a int without system versioning;
568 affected rows: 0
569 info: Records: 0 Duplicates: 0 Warnings: 0
570 alter table t1 modify a int with system versioning;
571 affected rows: 0
572 info: Records: 0 Duplicates: 0 Warnings: 0
573 show create table t1;
574 Table Create Table
575 t1 CREATE TABLE `t1` (
576 `a` int(11) DEFAULT NULL,
577 `b` int(11) GENERATED ALWAYS AS (0) STORED
578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
579 select * from t1;
580 a b
581 1 0
582 alter table t1 modify b int generated always as (0) stored without system versioning;
583 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'without system versioning' at line 1
584 alter table t1 modify b int generated always as (0) stored with system versioning;
585 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'system versioning' at line 1
586 alter table t1 modify b int without system versioning;
587 affected rows: 1
588 info: Records: 1 Duplicates: 0 Warnings: 0
589 show create table t1;
590 Table Create Table
591 t1 CREATE TABLE `t1` (
592 `a` int(11) DEFAULT NULL,
593 `b` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING
594 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
595 select * from t1;
596 a b
597 1 0
598 create or replace table t1 (a int, b int generated always as (0) virtual) engine=innodb with system versioning;
599 insert into t1 (a) values (1);
600 alter table t1 modify a int without system versioning, algorithm=copy;
601 affected rows: 1
602 info: Records: 1 Duplicates: 0 Warnings: 0
603 alter table t1 modify a int with system versioning, algorithm=copy;
604 affected rows: 1
605 info: Records: 1 Duplicates: 0 Warnings: 0
606 alter table t1 modify a int without system versioning;
607 affected rows: 0
608 info: Records: 0 Duplicates: 0 Warnings: 0
609 alter table t1 modify a int with system versioning;
610 affected rows: 0
611 info: Records: 0 Duplicates: 0 Warnings: 0
612 select * from t1;
613 a b
614 1 0
615 affected rows: 1
616 #
617 # MDEV-19304 Segfault in ALTER TABLE after UPDATE for SIMULTANEOUS_ASSIGNMENT
618 #
619 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;
620 insert into t1 values (null, null, null);
621 insert into t1 values (null, null, null);
622 set sql_mode= 'simultaneous_assignment';
623 update t1 set e= 1;
624 Warnings:
625 Warning 1906 The value specified for generated column 'e' in table 't1' has been ignored
626 Warning 1906 The value specified for generated column 'e' in table 't1' has been ignored
627 alter table t1 force;
628 set sql_mode= default;
629 #
630 # MDEV-18862 Unfortunate error message upon attempt to drop system versioning
631 #
632 set system_versioning_alter_history= keep;
633 create or replace table t1 (x int) with system versioning;
634 alter table t1 drop column `row_start`, drop column `row_end`, drop period for system_time, drop system versioning;
635 ERROR 42000: Can't DROP PERIOD FOR SYSTEM_TIME on `t1`; check that it exists
636 alter table t1 drop period for system_time;
637 ERROR 42000: Can't DROP PERIOD FOR SYSTEM_TIME on `t1`; check that it exists
638 alter table t1 drop column `row_start`, drop column `row_end`, drop system versioning;
639 ERROR 42000: Can't DROP COLUMN `row_start`; check that it exists
640 alter table t1 drop column `row_end`;
641 ERROR 42000: Can't DROP COLUMN `row_end`; check that it exists
642 #
643 # MDEV-19127 Assertion `row_start_field' failed in vers_prepare_keys upon ALTER TABLE
644 #
645 set system_versioning_alter_history=keep;
646 create or replace table t1 (f1 int) with system versioning;
647 alter table t1 add f2 int with system versioning, drop system versioning;
648 create or replace table t1 (f1 int) with system versioning;
649 alter table t1 drop system versioning, add f2 int with system versioning;
650 ERROR HY000: Table `t1` is not system-versioned
651 drop table t1;
652 # MDEV-16490 It's possible to make a system versioned table without any versioning field
653 set @@system_versioning_alter_history=keep;
654 create or replace table t (a int) with system versioning engine=innodb;
655 alter table t change column a a int without system versioning;
656 ERROR HY000: Table `t` must have at least one versioned column
657 alter table t
658 change column a a int without system versioning,
659 add column b int with system versioning;
660 show create table t;
661 Table Create Table
662 t CREATE TABLE `t` (
663 `a` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING,
664 `b` int(11) DEFAULT NULL
665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
666 alter table t
667 change column a new_a int,
668 drop system versioning;
669 show create table t;
670 Table Create Table
671 t CREATE TABLE `t` (
672 `new_a` int(11) DEFAULT NULL,
673 `b` int(11) DEFAULT NULL
674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
675 alter table t add system versioning;
676 alter table t change column new_a a int without system versioning;
677 show create table t;
678 Table Create Table
679 t CREATE TABLE `t` (
680 `a` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING,
681 `b` int(11) DEFAULT NULL
682 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
683 alter table t
684 add column c int,
685 change column c c int without system versioning,
686 change column b b int without system versioning;
687 ERROR HY000: Table `t` must have at least one versioned column
688 alter table t
689 add column c int without system versioning,
690 change column c c int,
691 change column b b int without system versioning;
692 drop table t;
693 #
694 # MDEV-21688 Assertion or ER_WARN_DATA_OUT_OF_RANGE upon ALTER on previously versioned table
695 #
696 create or replace table t1 (a int) with system versioning;
697 insert into t1 values (128);
698 delete from t1;
699 set statement system_versioning_alter_history=keep for
700 alter table t1 drop system versioning, modify column a tinyint;
701 drop table t1;
702 #
703 # MDEV-24690 Dropping primary key column from versioned table always fails with 1072
704 #
705 create table t1 (a int, b int primary key) with system versioning;
706 alter table t1 drop column b;
707 create or replace table t1 (
708 a int, b int primary key,
709 row_start timestamp(6) as row start,
710 row_end timestamp(6) as row end,
711 period for system_time(row_start, row_end)
712 ) with system versioning;
713 show create table t1;
714 Table Create Table
715 t1 CREATE TABLE `t1` (
716 `a` int(11) DEFAULT NULL,
717 `b` int(11) NOT NULL,
718 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START,
719 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END,
720 PRIMARY KEY (`b`,`row_end`),
721 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
722 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
723 alter table t1 drop column b;
724 ERROR 42000: Key column 'b' doesn't exist in table
725 create or replace table t1 (
726 a int, b int primary key,
727 row_start timestamp(6) as row start invisible,
728 row_end timestamp(6) as row end invisible,
729 period for system_time(row_start, row_end)
730 ) with system versioning;
731 show create table t1;
732 Table Create Table
733 t1 CREATE TABLE `t1` (
734 `a` int(11) DEFAULT NULL,
735 `b` int(11) NOT NULL,
736 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
737 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
738 PRIMARY KEY (`b`,`row_end`),
739 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
740 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
741 alter table t1 drop column b;
742 ERROR 42000: Key column 'b' doesn't exist in table
743 drop table t1;
744 #
745 # MDEV-25172 Wrong error message for ADD COLUMN .. AS ROW START
746 #
747 create or replace table t1 (x int);
748 alter table t1 add column y timestamp(6) as row start;
749 ERROR HY000: Table `t1` is not system-versioned
750 drop table t1;
751 #
752 # MDEV-25327 Unexpected ER_DUP_ENTRY upon dropping PK column from system-versioned table
753 #
754 create table t1 (pk int, a int, primary key (pk), key (a))
755 with system versioning;
756 insert into t1 values (1, 1), (2, 2);
757 delete from t1;
758 set system_versioning_alter_history= keep;
759 alter table t1 drop pk;
760 drop table t1;
761 create table t1 (pk int, a int, primary key (pk), key (a))
762 with system versioning;
763 insert into t1 values (1, 2), (2, 8), (3, 4), (4, 4), (5, 0);
764 delete from t1;
765 set system_versioning_alter_history= keep;
766 alter ignore table t1 drop pk;
767 drop table t1;
768 #
769 # MDEV-22660 SIGSEGV on adding system versioning and modifying system column
770 #
771 create or replace table t1 (a int);
772 alter table t1
773 add row_start timestamp(6) as row start,
774 add row_end timestamp(6) as row end,
775 add period for system_time(row_start, row_end),
776 with system versioning,
777 modify row_end varchar(8);
778 ERROR HY000: PERIOD FOR SYSTEM_TIME must use columns `row_start` and `row_end`
779 alter table t1
780 add row_start timestamp(6) as row start,
781 add row_end timestamp(6) as row end,
782 add period for system_time(row_start, row_end),
783 with system versioning,
784 modify row_start varchar(8);
785 ERROR HY000: PERIOD FOR SYSTEM_TIME must use columns `row_start` and `row_end`
786 drop table t1;
787 #
788 # MDEV-20545 Assertion (col.vers_sys_end()) upon inplace ALTER with virtual columns
789 #
790 create table t1 (
791 a int,
792 va int as (a) virtual,
793 b int,
794 vb int as (b) virtual,
795 c int,
796 vc int as (c) virtual,
797 d int,
798 e int,
799 index(va)
800 ) engine=innodb with system versioning;
801 replace into t1 () values (),();
802 set statement system_versioning_alter_history=keep for alter table t1 drop e;
803 alter table t1 algorithm=inplace, drop system versioning;
804 drop table t1;
805 #
806 # MDEV-20765 Assertion (type.vers_sys_end()) upon inplace ALTER with virtual columns
807 #
808 create table t1 (
809 a int,
810 b int as (a) virtual,
811 c int,
812 d int as (c) virtual,
813 e int,
814 f int as (e) virtual,
815 g int,
816 h int,
817 i int,
818 index(d),
819 key(h),
820 foreign key (g) references t1 (h)
821 ) engine=innodb with system versioning;
822 set system_versioning_alter_history= keep;
823 alter table t1 drop column i;
824 insert into t1 (g,h) values (1,1);
825 drop table t1;
826 #
827 # MDEV-29034 Assertion (o->ind == vers_start) upon inplace ALTER with virtual columns
828 #
829 create table b (
830 pk integer auto_increment,
831 col_int_key integer,
832 col_varchar_key varchar(1),
833 o bit, n bit,
834 h float as ( n + 2 ) virtual,
835 v bit,
836 primary key (pk),
837 key (col_varchar_key, col_int_key)
838 ) engine = innodb;
839 set `system_versioning_alter_history`= keep;
840 alter table `b` add system versioning;
841 alter table `b` add column if not exists ( w bit, v serial );
842 Warnings:
843 Note 1060 Duplicate column name 'v'
844 alter table `b` add column if not exists ( p bit );
845 drop table `b`;
846 #
847 # MDEV-30528 Assertion !mbmaxlen || ... failed
848 # in dtype_get_at_most_n_mbchars()
849 #
850 CREATE TABLE t(f TEXT) WITH SYSTEM VERSIONING CHARACTER SET utf8 ENGINE=InnoDB;
851 INSERT INTO t VALUES ('foo');
852 DELETE FROM t;
853 ALTER TABLE t ADD FULLTEXT (f);
854 affected rows: 1
855 info: Records: 1 Duplicates: 0 Warnings: 0
856 DROP TABLE t;
857 # End of 10.4 tests
858 #
859 # MDEV-21941 RENAME doesn't work for system time or period fields
860 #
861 create or replace table t1 (a int) with system versioning;
862 alter table t1 rename column row_start to x;
863 ERROR 42S22: Unknown column 'row_start' in 't1'
864 create or replace table t1 (
865 a int,
866 row_start timestamp(6) as row start invisible,
867 row_end timestamp(6) as row end invisible,
868 period for system_time (row_start, row_end)
869 ) with system versioning;
870 alter table t1 rename column row_start to x;
871 alter table t1 rename column row_end to y;
872 show create table t1;
873 Table Create Table
874 t1 CREATE TABLE `t1` (
875 `a` int(11) DEFAULT NULL,
876 `x` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
877 `y` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
878 PERIOD FOR SYSTEM_TIME (`x`, `y`)
879 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
880 drop table t1;
881 # End of 10.5 tests
882 #
883 # MDEV-27293 Allow converting a versioned table from implicit
884 # to explicit row_start/row_end columns
885 #
886 create table t1 (x int) with system versioning;
887 insert t1 values (0), (1);
888 update t1 set x= x + 10;
889 set @old_system_versioning_alter_history= @@system_versioning_alter_history;
890 set @@system_versioning_alter_history= keep;
891 alter table t1 add column y int;
892 show create table t1;
893 Table Create Table
894 t1 CREATE TABLE `t1` (
895 `x` int(11) DEFAULT NULL,
896 `y` int(11) DEFAULT NULL
897 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
898 alter table t1 add column rs timestamp(6) as row start;
899 ERROR HY000: Wrong parameters for `t1`: missing 'AS ROW END'
900 alter table t1 add column re timestamp(6) as row end, add period for system_time (row_start, re);
901 ERROR HY000: Wrong parameters for `t1`: missing 'AS ROW START'
902 alter table t1 add column rs timestamp(6) as row start, add column re timestamp(6) as row end;
903 ERROR HY000: Wrong parameters for `t1`: missing 'PERIOD FOR SYSTEM_TIME'
904 alter table t1 rename column row_start to re;
905 ERROR 42S22: Unknown column 'row_start' in 't1'
906 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);
907 ERROR 42000: Incorrect column specifier for column 'rs'
908 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);
909 show create table t1;
910 Table Create Table
911 t1 CREATE TABLE `t1` (
912 `x` int(11) DEFAULT NULL,
913 `y` int(11) DEFAULT NULL,
914 `rs` timestamp(6) GENERATED ALWAYS AS ROW START,
915 `re` timestamp(6) GENERATED ALWAYS AS ROW END,
916 PERIOD FOR SYSTEM_TIME (`rs`, `re`)
917 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
918 select x, check_row_ts(rs, re) from t1 for system_time all order by x;
919 x check_row_ts(rs, re)
920 0 HISTORICAL ROW
921 1 HISTORICAL ROW
922 10 CURRENT ROW
923 11 CURRENT ROW
924 create or replace table t1 (x int) engine innodb with system versioning;
925 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);
926 show create table t1;
927 Table Create Table
928 t1 CREATE TABLE `t1` (
929 `x` int(11) DEFAULT NULL,
930 `rs` timestamp(6) GENERATED ALWAYS AS ROW START,
931 `re` timestamp(6) GENERATED ALWAYS AS ROW END,
932 PERIOD FOR SYSTEM_TIME (`rs`, `re`)
933 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
934 set @@system_versioning_alter_history= @old_system_versioning_alter_history;
935 drop table t1;
936 # End of 11.6 tests