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