| 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 |