diff options
Diffstat (limited to 'mysql-test/suite/versioning/t/create.test')
| -rw-r--r-- | mysql-test/suite/versioning/t/create.test | 498 |
1 files changed, 498 insertions, 0 deletions
diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test new file mode 100644 index 00000000..46ed8fad --- /dev/null +++ b/mysql-test/suite/versioning/t/create.test @@ -0,0 +1,498 @@ +--source suite/versioning/engines.inc +--source suite/versioning/common.inc + +--enable_prepare_warnings + +--replace_result $default_engine DEFAULT_ENGINE $sys_datatype_expl SYS_DATATYPE NULL '' +eval create table t1 ( + x1 int unsigned, + Sys_start $sys_datatype_expl as row start invisible comment 'start', + Sys_end $sys_datatype_expl as row end invisible comment 'end', + period for system_time (Sys_start, Sys_end) +) with system versioning; +--replace_result $default_engine DEFAULT_ENGINE $sys_datatype_expl SYS_DATATYPE +show create table t1; + +--query_vertical 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' +--query_vertical 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' + +--echo # Implicit fields test +create or replace table t1 ( + x2 int unsigned +) with system versioning; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +--replace_result $default_engine DEFAULT_ENGINE +--error ER_VERS_PERIOD_COLUMNS +eval create or replace table t1 ( + x3 int unsigned, + Sys_start timestamp(6) as row start invisible, + Sys_end timestamp(6) as row end invisible, + period for system_time (x, Sys_end) +) with system versioning; + +--replace_result $default_engine DEFAULT_ENGINE +--error ER_VERS_PERIOD_COLUMNS +eval create or replace table t1 ( + x4 int unsigned, + Sys_start timestamp(6) as row start invisible, + Sys_end2 timestamp(6) as row end invisible, + period for system_time (Sys_start, Sys_end) +) with system versioning; + +--replace_result $default_engine DEFAULT_ENGINE +--error ER_VERS_PERIOD_COLUMNS +eval create or replace table t1 ( + x5 int unsigned, + Sys_start timestamp(6) as row start invisible, + Sys_end timestamp(6) as row end invisible, + period for system_time (Sys_start, x) +) with system versioning; + +--error ER_MISSING +create or replace table t1 ( + x6 int unsigned, + period for system_time (Sys_start, Sys_end) +) with system versioning; + +--replace_result $default_engine DEFAULT_ENGINE +--error ER_MISSING +eval create or replace table t1 ( + x7 int unsigned, + Sys_start timestamp(6) as row start invisible, + Sys_end timestamp(6) as row end invisible, + period for system_time (Sys_start, Sys_end) +); + +--replace_result $default_engine DEFAULT_ENGINE +--error ER_VERS_PERIOD_COLUMNS +eval create or replace table t1 ( + x8 int unsigned, + Sys_start timestamp(6) as row start invisible, + Sys_end timestamp(6) as row end invisible, + period for system_time (sys_insert, sys_remove) +) with system versioning; + +--replace_result $default_engine DEFAULT_ENGINE +--error ER_MISSING +eval create or replace table t1 ( + x9 int unsigned, + Sys_start timestamp(6) as row start invisible, + Sys_end timestamp(6) as row end invisible, + period for system_time (Sys_start, Sys_end) +); + +--replace_result $default_engine DEFAULT_ENGINE +--error ER_MISSING +eval create or replace table t1 ( + x10 int unsigned, + Sys_start timestamp(6) as row start invisible, + Sys_end timestamp(6) as row end invisible, + period for system_time (Sys_start, Sys_start) +); + +--error ER_VERS_FIELD_WRONG_TYPE, ER_VERS_FIELD_WRONG_TYPE +create or replace table t1 ( + x11 int unsigned, + Sys_start bigint unsigned as row start invisible, + Sys_end timestamp(6) as row end invisible, + period for system_time (Sys_start, Sys_end) +) with system versioning; + +--error ER_VERS_FIELD_WRONG_TYPE, ER_VERS_FIELD_WRONG_TYPE +create or replace table t1 ( + x12 int unsigned, + Sys_start timestamp(6) as row start invisible, + Sys_end bigint unsigned as row end invisible, + period for system_time (Sys_start, Sys_end) +) with system versioning; + +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t1 ( + x13 int unsigned, + Sys_start bigint as row start invisible, + Sys_end bigint unsigned as row end invisible, + period for system_time (Sys_start, Sys_end) +) with system versioning engine innodb; + +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t1 ( + x14 int unsigned, + Sys_start bigint unsigned as row start invisible, + Sys_end bigint as row end invisible, + period for system_time (Sys_start, Sys_end) +) with system versioning engine innodb; + +# columns with/without system versioning + +create or replace table t1 ( + x15 int with system versioning, + B int +); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +create or replace table t1 ( + x16 int with system versioning, + B int +) with system versioning; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +create or replace table t1 ( + x17 int, + B int without system versioning +); + +create or replace table t1 ( + x18 int, + B int without system versioning +) with system versioning; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +create or replace table t1 ( + x19 int with system versioning, + B int without system versioning +); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +create or replace table t1 ( + x20 int with system versioning, + B int without system versioning +) with system versioning; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +create or replace table t1 ( + x21 int without system versioning +); + +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +create or replace table t1 ( + x22 int without system versioning +) with system versioning; + +# CREATE TABLE ... LIKE +create or replace table t1 (a int) with system versioning; +create table tt1 like t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table tt1; +drop table tt1; +create temporary table tt1 like t1; +--echo # Temporary is stripped from versioning +--replace_result $default_engine DEFAULT_ENGINE +show create table tt1; + +--echo # CREATE TABLE ... SELECT +create or replace table t1 (x23 int) with system versioning; +--replace_result $default_engine DEFAULT_ENGINE +eval create or replace table t0( + y int, + st timestamp(6) as row start, + en timestamp(6) as row end, + period for system_time (st, en) +) with system versioning; + +--echo ## For non-versioned table: +--echo ### 1. invisible fields are not included +create or replace table t2 as select * from t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t2; + +--echo ### 2. all visible fields are included +create or replace table t3 as select * from t0; +select * from t0; +--replace_result $default_engine DEFAULT_ENGINE +show create table t3; + +--echo ## For versioned table +insert into t1 values (1); +select row_start from t1 into @row_start; +insert into t0 (y) values (2); +select st from t0 into @st; + +create or replace table t2 with system versioning as select * from t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t2; +--echo #### invisible fields are not copied +select * from t2; +select * from t2 where row_start <= @row_start; + +--echo ### 2. source table with visible system fields, target with invisible +create or replace table t3 with system versioning as select * from t0; +--replace_result $default_engine DEFAULT_ENGINE +show create table t3; +select * from t3 where y > 2; +select y from t3 where st = @st and row_start > @st; + +--echo ### 3. source and target table with visible system fields +--replace_result $default_engine DEFAULT_ENGINE +eval create or replace table t3 ( + st timestamp(6) as row start invisible, + en timestamp(6) as row end invisible, + period for system_time (st, en) +) with system versioning as select * from t0; +--replace_result $default_engine DEFAULT_ENGINE +show create table t3; +select y from t3; +select y from t3 where st = @st; + +--echo ### 4. system fields not or wrongly selected +create or replace table t3 with system versioning select x23 from t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t3; +select * from t3; +--error ER_DUP_FIELDNAME +create or replace table t3 with system versioning select x23, row_start from t1; +--error ER_DUP_FIELDNAME +create or replace table t3 with system versioning select x23, row_end from t1; + +--echo # Prepare checking for historical row +delete from t1; +select row_end from t1 for system_time all into @row_end; +delete from t0; +select en from t0 for system_time all into @en; + +--echo ## Combinations of versioned + non-versioned +create or replace table t2 (y int); +insert into t2 values (3); +create or replace table t3 with system versioning select * from t1 for system_time all, t2; +--replace_result $default_engine DEFAULT_ENGINE +show create table t3; +select * from t3 for system_time all; +select * from t3 for system_time all where row_start = @row_start and row_end = @row_end; + +create or replace table t2 like t0; +insert into t2 (y) values (1), (2); +delete from t2 where y = 2; + +create or replace table t3 select * from t2 for system_time all; +select st, en from t3 where y = 1 into @st, @en; +select y from t2 for system_time all where st = @st and en = @en; +select st, en from t3 where y = 2 into @st, @en; +select y from t2 for system_time all where st = @st and en = @en; + +--echo ## Default engine detection +--replace_result $non_default_engine NON_DEFAULT_ENGINE +eval create or replace table t1 (x25 int) with system versioning engine $non_default_engine; +create or replace table t2 +as select x25, row_start, row_end from t1 for system_time all; +--replace_result $default_engine DEFAULT_ENGINE +show create table t2; + +create or replace table t2 with system versioning +as select x25, row_start rs, row_end re from t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t2; + +create or replace table t1 ( + x26 int, + st bigint unsigned as row start, + en bigint unsigned as row end, + period for system_time (st, en) +) with system versioning engine innodb; +create or replace table t2 with system versioning engine myisam +as select * from t1; +show create table t2; + +--replace_result $non_default_engine NON_DEFAULT_ENGINE +eval create or replace table t1 (x27 int, id int) with system versioning engine $non_default_engine; +create or replace table t2 (b int, id int); +create or replace table t3 with system versioning +as select t2.b, t1.x27, t1.row_start rs, t1.row_end re from t2 inner join t1 on t2.id=t1.id; +--replace_result $default_engine DEFAULT_ENGINE +show create table t3; + +--echo ## Errors + +--error ER_VERS_NOT_SUPPORTED +create or replace temporary table t (x28 int) with system versioning; + +--error ER_VERS_DUPLICATE_ROW_START_END +create or replace table t1 ( + x29 int unsigned, + Sys_start0 timestamp(6) as row start invisible, + Sys_start timestamp(6) as row start invisible, + Sys_end timestamp(6) as row end invisible, + period for system_time (Sys_start, Sys_end) +) with system versioning; + +--error ER_VERS_DUPLICATE_ROW_START_END +create or replace table t1 ( + x29 int unsigned, + Sys_end0 timestamp(6) as row end invisible, + Sys_start timestamp(6) as row start invisible, + Sys_end timestamp(6) as row end invisible, + period for system_time (Sys_start, Sys_end) +) with system versioning; + +--echo ## System fields detection +create or replace table t1 (x30 int) with system versioning; +--replace_result $default_engine DEFAULT_ENGINE +eval create or replace table t2 ( + y int, + st timestamp(6) as row start invisible, + en timestamp(6) as row end invisible, + period for system_time (st, en) +) with system versioning; + +create or replace table t3 +as select x30, y, row_start, row_end, st, en from t1, t2; +--replace_result $default_engine DEFAULT_ENGINE +show create table t3; + +--replace_result $default_engine DEFAULT_ENGINE +eval create or replace table t3 ( + y int, + st timestamp(6) as row start invisible, + en timestamp(6) as row end invisible, + period for system_time (st, en) +) with system versioning +as select x30, y, row_start, row_end, st, en from t1, t2; +--replace_result $default_engine DEFAULT_ENGINE +show create table t3; + +--echo # MDEV-14828 Server crashes in JOIN::prepare / setup_fields on 2nd execution of PS [#437] +create or replace table t1 (x int) with system versioning; +prepare bad from 'create or replace table t2 with system versioning as select * from t1'; +execute bad; execute bad; execute bad; execute bad; execute bad; execute bad; execute bad; execute bad; +--echo # bad is good. + +--echo # MDEV-15413 Unexpected errors upon CREATE TABLE .. WITH SYSTEM VERSIONING AS SELECT ... +create or replace table t1 with system versioning as select 1 as i; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +create or replace table t1 (i int) with system versioning as select 1 as i; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t1 ( + a int, + row_start bigint as row start, + row_end bigint as row end, + period for system_time (row_start, row_end) +) engine=innodb with system versioning; + +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t1 ( + a int, + row_start bigint as row start, + row_end bigint as row end, + period for system_time (row_start, row_end) +) engine=myisam with system versioning; + +--error ER_VERS_FIELD_WRONG_TYPE +create table t ( + a int, + row_start datetime(6) generated always as row start, + row_end datetime(6) generated always as row end, + period for system_time(row_start, row_end) +) with system versioning; + +--source suite/versioning/common_finish.inc +--echo # MDEV-16490 It's possible to make a system versioned table without any versioning field +create or replace table t1 (x int without system versioning) +with system versioning +select 1 as y; +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +create or replace table t1 (x int without system versioning) +with system versioning +select 1 as x; + +drop tables t0, t1, t2, t3; + +--echo # +--echo # MDEV-23968 CREATE TEMPORARY TABLE .. LIKE (system versioned table) returns error if unique index is defined in the table +--echo # +--error ER_KEY_COLUMN_DOES_NOT_EXIST +create table t1 (id int primary key, index(row_start)) with system versioning; +--error ER_KEY_COLUMN_DOES_NOT_EXIST +create table t1 (id int primary key, index(row_end)) with system versioning; +--error ER_KEY_COLUMN_DOES_NOT_EXIST +create table t1 (id int, primary key(id, row_end, row_end)) with system versioning; +create table t1 (id int primary key) with system versioning; +create temporary table t2 like t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t2; +drop temporary table t2; +create or replace table t1 ( + a int, + row_start timestamp(6) as row start, + row_end timestamp(6) as row end, + period for system_time (row_start, row_end), + index(row_start), + index(row_end), + primary key(row_end, a, row_start), + index(row_end, row_start, a)) with system versioning; +create temporary table t2 like t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t2; +drop temporary table t2; +drop table t1; +--disable_prepare_warnings + +--echo # +--echo # MDEV-16857 system-invisible row_end is displayed in SHOW INDEX +--echo # +create or replace table t1 (id int primary key, x int) with system versioning; +select table_schema, table_name, non_unique, index_schema, index_name, seq_in_index, column_name +from information_schema.statistics where table_name = 't1'; +--replace_column 6 # 7 # 8 # 9 # 10 # 11 # +show index from t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +--echo # +--echo # MDEV-26928 Column-inclusive WITH SYSTEM VERSIONING doesn't work with explicit system fields +--echo # +create or replace table t1 (x int, y int with system versioning); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +create or replace table t1 ( + x int, y int with system versioning, + row_start timestamp(6) as row start, + row_end timestamp(6) as row end, + period for system_time(row_start, row_end)); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +drop table t1; + +--echo # +--echo # MDEV-27452 TIMESTAMP(0) system field is allowed for certain creation of system-versioned table +--echo # +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t ( + a int, + s timestamp as row start, + e timestamp as row end, + period for system_time (s, e)) +with system versioning; + +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t ( + a int with system versioning, + s timestamp as row start, + e timestamp as row end, + period for system_time (s, e)); + +create or replace table t ( + a int with system versioning, + b int with system versioning, + s timestamp(6) as row start, + e timestamp(6) as row end, + period for system_time (s, e)); +insert into t () values (),(); + +# cleanup +drop table t; |