diff options
Diffstat (limited to 'mysql-test/suite/versioning/t')
33 files changed, 5644 insertions, 0 deletions
diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test new file mode 100644 index 00000000000..9d8f0a1bb7b --- /dev/null +++ b/mysql-test/suite/versioning/t/alter.test @@ -0,0 +1,594 @@ +--source include/default_charset.inc +--source suite/versioning/common.inc + +select @@system_versioning_alter_history; + +create table t( + a int +); +show create table t; +--error ER_VERS_NOT_VERSIONED +alter table t drop system versioning; + +alter table t add system versioning; +show create table t; + +--error ER_VERS_ALTER_NOT_ALLOWED +alter table t add column y int; +--error ER_VERS_ALTER_NOT_ALLOWED +alter table t add primary key (a); +--error ER_VERS_ALTER_NOT_ALLOWED +alter table t add unique key (a); +--error ER_VERS_ALTER_ENGINE_PROHIBITED +alter table t engine innodb; + +alter table t drop system versioning; +show create table t; + +set system_versioning_alter_history= keep; + +alter table t add system versioning; +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t drop system versioning, drop column row_start; +alter table t drop system versioning; + +--error ER_VERS_FIELD_WRONG_TYPE +alter table t + add column trx_start bigint(20) unsigned as row start invisible, + add column trx_end bigint(20) unsigned as row end invisible, + add period for system_time(trx_start, trx_end), + add system versioning; + +--error ER_VERS_FIELD_WRONG_TYPE +alter table t + add column trx_start timestamp as row start invisible, + add column trx_end timestamp as row end invisible, + add period for system_time(trx_start, trx_end), + add system versioning; + +--error ER_PARSE_ERROR +alter table t + add column trx_start timestamp(6) not null as row start invisible, + add column trx_end timestamp(6) not null as row end invisible, + add period for system_time(trx_start, trx_end), + add system versioning; + +alter table t + add column trx_start timestamp(6) as row start invisible, + add column trx_end timestamp(6) as row end invisible, + add period for system_time(trx_start, trx_end), + add system versioning; +show create table t; + +--error ER_MISSING +alter table t drop system versioning; + +alter table t drop column trx_start, drop column trx_end; +select row_start from t; +alter table t drop system versioning; +show create table t; + +--error ER_VERS_DUPLICATE_ROW_START_END +alter table t add column trx_start timestamp(6) as row start; + +alter table t add system versioning; +show create table t; + +--error ER_VERS_DUPLICATE_ROW_START_END +alter table t add column trx_start timestamp(6) as row start; +--error ER_VERS_DUPLICATE_ROW_START_END +alter table t modify a int as row start; + +alter table t add column b int; +show create table t; + +alter table t add column c int; +show create table t; + +alter table t add column d int first; +show create table t; + +alter table t add column e int after d; +show create table t; + +alter table t drop column a; +show create table t; + +create or replace table t ( + a int, + row_start timestamp(6) as row start invisible, + row_end timestamp(6) as row end invisible, + period for system_time(row_start, row_end)) +with system versioning; + +select * from t for system_time all; +--error ER_MISSING +alter table t drop column row_start; +--error ER_MISSING +alter table t drop column row_end; +alter table t drop column row_start, drop column row_end; +select * from t for system_time all; +show create table t; + +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t drop column row_start; +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t drop column row_end; + +create or replace table t ( + a int, + row_start timestamp(6) as row start invisible, + row_end timestamp(6) as row end invisible, + period for system_time(row_start, row_end)) +with system versioning; + +select * from t for system_time all; +alter table t drop column row_start, drop column row_end; +select * from t for system_time all; + +create or replace table t( + a int +); +insert into t values(1); +alter table t add system versioning; +show create table t; +insert into t values(2); +select * from t for system_time all; +select * from t; + +update t set a=3 where a=1; +select * from t; +select * from t for system_time all; +select row_start from t where a=3 into @tm; +alter table t add column b int; +select @tm=row_start from t where a=3; +show create table t; +select * from t; +select * from t for system_time all; + +alter table t drop system versioning; +select * from t; +show create table t; + +--error ER_VERS_NOT_VERSIONED +alter table t modify a int with system versioning; +--error ER_VERS_NOT_VERSIONED +alter table t modify a int without system versioning; + +alter table t add system versioning; + +alter table t modify a int without system versioning; +show create table t; + +alter table t modify a int with system versioning; +show create table t; + +# TODO: move TRX_ID cases to separate test +create or replace table t( + a int +) engine=innodb; + +alter table t + add column trx_start timestamp(6) as row start invisible, + add column trx_end timestamp(6) as row end invisible, + add period for system_time(trx_start, trx_end), + add system versioning; + +show create table t; +--echo # Issue #211: drop of system columns required before drop system versioning +alter table t drop column trx_start, drop column trx_end; +show create table t; + +alter table t drop system versioning; + +insert into t values(1); + +call verify_trt; +alter table t + add column trx_start bigint(20) unsigned as row start invisible, + add column trx_end bigint(20) unsigned as row end invisible, + add period for system_time(trx_start, trx_end), + add system versioning; +call verify_trt; + +show create table t; +alter table t drop column trx_start, drop column trx_end; + +call verify_trt; +alter table t drop system versioning, algorithm=copy; +call verify_trt; + +alter table t add system versioning, algorithm=copy; +call verify_trt; + +show create table t; + +update t set a= 2; +select * from t for system_time all; + +alter table t add column b int, algorithm=copy; +show create table t; +select * from t; +call verify_trt; + +alter table t drop column b, algorithm=copy; +show create table t; +select * from t for system_time all; +call verify_trt; + +## FIXME: #414 IB: inplace for VERS_TIMESTAMP versioning +if (0) +{ +alter table t drop system versioning, algorithm=inplace; +call verify_trt; + +alter table t add system versioning, algorithm=inplace; +call verify_trt; +show create table t; + +update t set a= 1; +select * from t for system_time all; +call verify_trt; + +alter table t add column b int, algorithm=inplace; +show create table t; +select * from t; +call verify_trt; + +alter table t drop column b, algorithm=inplace; +show create table t; +select * from t for system_time all; +} +## FIXME END + +alter table t drop system versioning, algorithm=copy; +show create table t; +call verify_trt; + +# nullable autoinc test w/o versioning +create or replace table t (a int); +insert t values (1),(2),(3),(4); +alter table t add b int auto_increment null unique; +select * from t; +drop table t; + +create or replace table t (a int) with system versioning engine=innodb; +insert into t values (1), (2), (3); +delete from t where a<3; +--error ER_DUP_ENTRY, ER_DUP_ENTRY +alter table t add b int not null unique; +--error ER_UNSUPPORTED_EXTENSION, ER_UNSUPPORTED_EXTENSION +alter table t add b int auto_increment unique; +alter table t add b int auto_increment null unique; +select * from t; +select * from t for system_time all; +insert into t values (4, 0); +select * from t for system_time all; + +create or replace table t (a int) with system versioning; +insert into t values (1), (2), (3); +delete from t where a<3; +--error ER_DUP_ENTRY, ER_DUP_ENTRY +alter table t add b int not null unique; +--error ER_UNSUPPORTED_EXTENSION, ER_UNSUPPORTED_EXTENSION +alter table t add b int auto_increment unique; +alter table t add b int auto_increment null unique; +select * from t; +select * from t for system_time all; +insert into t values (4, 0); +select * from t for system_time all; + +create or replace table t (a int, b int primary key, c int unique) with system versioning; +insert t values (1,2,3),(1,3,4),(1,4,5); +alter table t drop system versioning; +show create table t; +select * from t; + +create or replace table t ( + a int, + row_start timestamp(6) as row start invisible, + row_end timestamp(6) as row end invisible, + period for system_time(row_start, row_end) +) with system versioning; +--error ER_VERS_ALTER_SYSTEM_FIELD +alter table t change column row_start asdf timestamp(6); +insert into t values (1); +--error ER_VERS_ALTER_SYSTEM_FIELD +alter table t modify column row_start bigint unsigned; + +## These experimental options are now disabled + +--error ER_WRONG_VALUE_FOR_VAR +set system_versioning_alter_history= SURVIVE; + +--error ER_WRONG_VALUE_FOR_VAR +set system_versioning_alter_history= 'DROP'; + +if (0) +{ +create or replace table t (a int) with system versioning engine innodb; +insert into t values (1); +update t set a = 2; +select * from t for system_time all; +alter table t add column b int; +select * from t for system_time all; + +create or replace table t (a int) with system versioning engine myisam; +insert into t values (1); +update t set a = 2; +select * from t for system_time all; +alter table t add column b int; +select * from t for system_time all; + +create or replace table non_empty ( + a int, + row_start bigint(20) unsigned, + row_end bigint(20) unsigned +) engine innodb; +insert into non_empty values (1, 100, 200); + +--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN +alter table non_empty + change column row_start row_start bigint(20) unsigned as row start invisible; +--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN +alter table non_empty + change column row_end row_end bigint(20) unsigned as row end invisible; +drop table non_empty; + +create or replace table t (a int primary key) with system versioning; +insert into t values (1); +update t set a=2; +alter table t drop primary key, add primary key (a), drop system versioning; +select * from t; +--replace_result InnoDB INNODB_OR_MYISAM MyISAM INNODB_OR_MYISAM +show create table t; + +create or replace table t (a int primary key) with system versioning; +insert into t values (1); +update t set a=2; +alter table t drop system versioning; +select * from t; +--replace_result InnoDB INNODB_OR_MYISAM MyISAM INNODB_OR_MYISAM +show create table t; + + +call verify_trt; +} + +create or replace table t (a int) with system versioning; +--error ER_VERS_ALREADY_VERSIONED +alter table t add system versioning; +--error ER_VERS_ALREADY_VERSIONED +alter table t add system versioning, drop system versioning; + +set @@system_versioning_alter_history=keep; +create or replace table t(x int, y int) with system versioning engine=innodb; +alter table t modify y int without system versioning; +insert into t values(1, 1); +update t set y=2; + +--echo # MDEV-14681 Bogus ER_UNSUPPORTED_EXTENSION +create or replace table t1 (pk int auto_increment unique) with system versioning; +insert into t1 values (1); +delete from t1; +alter table t1 engine=myisam; + +--echo # MDEV-14692 crash in MDL_context::upgrade_shared_lock() +create or replace temporary table t (a int); +--error ER_VERS_TEMPORARY +alter table t change column if exists b c bigint unsigned generated always as row start; +--error ER_VERS_TEMPORARY +alter table t change column if exists b c bigint unsigned generated always as row end; +--error ER_VERS_TEMPORARY +alter table t add system versioning; +drop table t; + +--echo # MDEV-14744 trx_id-based and transaction-based mixup in assertion +create or replace table t (c text) engine=innodb with system versioning; +show create table t; +alter table t add fulltext key (c); + +create or replace table t (a int) with system versioning; +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +alter table t drop column a; +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t drop column a, drop column a; + +create or replace table t1 (row_start int); +--error ER_DUP_FIELDNAME +alter table t1 with system versioning; + +create or replace table t1 (row_end int); +--error ER_DUP_FIELDNAME +alter table t1 with system versioning; + +--error ER_DUP_FIELDNAME +create or replace table t1 (a int, row_start int) with system versioning; + +create or replace table t1 (a int) with system versioning; + +--error ER_DUP_FIELDNAME +set statement system_versioning_alter_history=keep for +alter table t1 add column row_start int; + +--error ER_DUP_FIELDNAME +set statement system_versioning_alter_history=keep for +alter table t1 add column row_start timestamp(6); + +--echo # MDEV-14798 Add, drop system versioning semantic and syntax +create or replace table t ( + a int, + row_start timestamp(6) generated always as row start, + row_end timestamp(6) generated always as row end, + period for system_time(row_start, row_end) +) with system versioning; +show create table t; + +alter table t + drop column row_start, + drop column row_end, + drop period for system_time, + drop system versioning; +show create table t; + +--error ER_VERS_NOT_VERSIONED +alter table t drop period for system_time; + +create or replace table t ( + a int, + row_start timestamp(6) generated always as row start, + row_end timestamp(6) generated always as row end, + period for system_time(row_start, row_end) +) with system versioning; +--error ER_MISSING +alter table t drop period for system_time; +--error ER_MISSING +alter table t drop column sys_trx_start, drop period for system_time; +--error ER_MISSING +alter table t drop column sys_trx_end, drop period for system_time; + +--error ER_VERS_ALREADY_VERSIONED +alter table t add period for system_time(sys_trx_start, sys_trx_end); + +--echo # +--echo # MDEV-14790 System versioning for system tables does not work as expected +--echo # +use mysql; +--error ER_VERS_DB_NOT_SUPPORTED +create or replace table t (x int) with system versioning; +--error ER_VERS_DB_NOT_SUPPORTED +alter table user add system versioning; +use test; + +--echo # MDEV-15956 Strange ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN upon ALTER on versioning column +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; +--error ER_VERS_DUPLICATE_ROW_START_END +alter table t1 modify s timestamp(6) as row start; + +--echo # ignore CHECK for historical rows +create or replace table t (a int) with system versioning; +insert into t values (0), (1); +delete from t where a = 0; +--error ER_CONSTRAINT_FAILED +alter table t add check (a > 1); +alter table t add check (a > 0); +--error ER_CONSTRAINT_FAILED +insert into t values (0); +insert into t values (2); + +--echo # +--echo # MDEV-18869 Assertion `!((field)->vcol_info && (field)->stored_in_db())' failed in innodb_col_no upon altering table with system versioning +--echo # +set system_versioning_alter_history= keep; +create or replace table t1 (a int, b int generated always as (0) stored) engine=innodb with system versioning; +insert into t1 (a) values (1); +--enable_info +alter table t1 modify a int without system versioning, algorithm=copy; +alter table t1 modify a int with system versioning, algorithm=copy; +alter table t1 modify a int without system versioning; +alter table t1 modify a int with system versioning; +--disable_info +show create table t1; +select * from t1; +--enable_info +--error ER_PARSE_ERROR +alter table t1 modify b int generated always as (0) stored without system versioning; +--error ER_PARSE_ERROR +alter table t1 modify b int generated always as (0) stored with system versioning; +alter table t1 modify b int without system versioning; +--disable_info +show create table t1; +select * from t1; + +create or replace table t1 (a int, b int generated always as (0) virtual) engine=innodb with system versioning; +insert into t1 (a) values (1); +--enable_info +alter table t1 modify a int without system versioning, algorithm=copy; +alter table t1 modify a int with system versioning, algorithm=copy; +alter table t1 modify a int without system versioning; +alter table t1 modify a int with system versioning; +select * from t1; +--disable_info + +--echo # +--echo # MDEV-19304 Segfault in ALTER TABLE after UPDATE for SIMULTANEOUS_ASSIGNMENT +--echo # +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; +insert into t1 values (null, null, null); +insert into t1 values (null, null, null); +set sql_mode= 'simultaneous_assignment'; +update t1 set e= 1; +alter table t1 force; +set sql_mode= default; + +--echo # +--echo # MDEV-18862 Unfortunate error message upon attempt to drop system versioning +--echo # +set system_versioning_alter_history= keep; +create or replace table t1 (x int) with system versioning; +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t1 drop column `row_start`, drop column `row_end`, drop period for system_time, drop system versioning; +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t1 drop period for system_time; +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t1 drop column `row_start`, drop column `row_end`, drop system versioning; +--error ER_CANT_DROP_FIELD_OR_KEY +alter table t1 drop column `row_end`; + +--echo # +--echo # MDEV-19127 Assertion `row_start_field' failed in vers_prepare_keys upon ALTER TABLE +--echo # +set system_versioning_alter_history=keep; +create or replace table t1 (f1 int) with system versioning; +alter table t1 add f2 int with system versioning, drop system versioning; +create or replace table t1 (f1 int) with system versioning; +--error ER_VERS_NOT_VERSIONED +alter table t1 drop system versioning, add f2 int with system versioning; + +drop table t1; +--source suite/versioning/common_finish.inc +--echo # MDEV-16490 It's possible to make a system versioned table without any versioning field + +set @@system_versioning_alter_history=keep; +create or replace table t (a int) with system versioning engine=innodb; +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +alter table t change column a a int without system versioning; + +alter table t + change column a a int without system versioning, + add column b int with system versioning; +show create table t; + +alter table t + change column a new_a int, + drop system versioning; +show create table t; + +alter table t add system versioning; +alter table t change column new_a a int without system versioning; +show create table t; + +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +alter table t + add column c int, + change column c c int without system versioning, + change column b b int without system versioning; + +alter table t + add column c int without system versioning, + change column c c int, + change column b b int without system versioning; + +drop table t; + +--echo # +--echo # MDEV-21688 Assertion or ER_WARN_DATA_OUT_OF_RANGE upon ALTER on previously versioned table +--echo # +create or replace table t1 (a int) with system versioning; +insert into t1 values (128); +delete from t1; +set statement system_versioning_alter_history=keep for +alter table t1 drop system versioning, modify column a tinyint; + +# cleanup +drop table t1; diff --git a/mysql-test/suite/versioning/t/auto_increment.test b/mysql-test/suite/versioning/t/auto_increment.test new file mode 100644 index 00000000000..804c0424179 --- /dev/null +++ b/mysql-test/suite/versioning/t/auto_increment.test @@ -0,0 +1,50 @@ +-- source suite/versioning/engines.inc +-- source suite/versioning/common.inc + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create table t1( + id int unsigned auto_increment primary key, + x int unsigned, + y int unsigned, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time (sys_start, sys_end)) + with system versioning; + +eval create table t2( + id int unsigned auto_increment primary key, + x int unsigned, + y int unsigned); + +insert into t1(x, y) values(1, 11); +insert into t2(x, y) values(1, 11); +insert into t1(x, y) values(2, 12); +insert into t2(x, y) values(2, 12); +insert into t1(x, y) values(3, 13); +insert into t2(x, y) values(3, 13); +insert into t1(x, y) values(4, 14); +insert into t2(x, y) values(4, 14); +insert into t1(x, y) values(5, 15); +insert into t2(x, y) values(5, 15); +insert into t1(x, y) values(6, 16); +insert into t2(x, y) values(6, 16); +insert into t1(x, y) values(7, 17); +insert into t2(x, y) values(7, 17); +insert into t1(x, y) values(8, 18); +insert into t2(x, y) values(8, 18); +insert into t1(x, y) values(9, 19); +insert into t2(x, y) values(9, 19); + +select t1.x = t2.x and t1.y = t2.y as A, t1.x, t1.y, t2.x, t2.y from t1 inner join t2 on t1.id = t2.id; +delete from t1 where x = 2; +delete from t2 where x = 2; + +select t1.x = t2.x and t1.y = t2.y as A, t1.x, t1.y, t2.x, t2.y from t1 inner join t2 on t1.id = t2.id; +delete from t1 where x > 7; +delete from t2 where x > 7; + +select t1.x = t2.x and t1.y = t2.y as A, t1.x, t1.y, t2.x, t2.y from t1 inner join t2 on t1.id = t2.id; +drop table t1; +drop table t2; + +-- source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/commit_id.test b/mysql-test/suite/versioning/t/commit_id.test new file mode 100644 index 00000000000..0f9cf1eb391 --- /dev/null +++ b/mysql-test/suite/versioning/t/commit_id.test @@ -0,0 +1,94 @@ +-- source suite/versioning/common.inc + +create table t1( + id int auto_increment primary key, + sys_trx_start bigint unsigned as row start invisible, + sys_trx_end bigint unsigned as row end invisible, + period for system_time (sys_trx_start, sys_trx_end) +) +with system versioning +engine innodb; + + +# TRT_TRX_ID, TRT_COMMIT_ID, TRT_TRX_SEES # + +insert into t1 values (); + +--real_sleep 0.01 +set @ts0= now(6); +insert into t1 values (); +select sys_trx_start from t1 where id = last_insert_id() into @tx0; +select transaction_id = @tx0 from mysql.transaction_registry +order by transaction_id desc limit 1; + +set @ts1= now(6); +insert into t1 values (); +select sys_trx_start from t1 where id = last_insert_id() into @tx1; +select transaction_id = @tx1 from mysql.transaction_registry +order by transaction_id desc limit 1; + +set @ts2= now(6); +insert into t1 values (); +select sys_trx_start from t1 where id = last_insert_id() into @tx2; +select transaction_id = @tx2 from mysql.transaction_registry +order by transaction_id desc limit 1; + +set @ts3= now(6); + +select + trt_trx_id(@ts0) < @tx0 as A, + trt_trx_id(@ts0, true) = @tx0 as B, + trt_trx_id(@ts1) = @tx0 as C, + trt_trx_id(@ts1, true) = @tx1 as D, + trt_trx_id(@ts2) = @tx1 as E, + trt_trx_id(@ts2, true) = @tx2 as F, + trt_trx_id(@ts3) = @tx2 as G, + trt_trx_id(@ts3, true) is null as H; + +select + trt_commit_id(@ts0) < @tx0 as A, + trt_commit_id(@ts0, true) = trt_commit_id(null, @tx0) as B, + trt_commit_id(@ts1) = trt_commit_id(null, @tx0) as C, + trt_commit_id(@ts1, true) = trt_commit_id(null, @tx1) as D, + trt_commit_id(@ts2) = trt_commit_id(null, @tx1) as E, + trt_commit_id(@ts2, true) = trt_commit_id(null, @tx2) as F, + trt_commit_id(@ts3) = trt_commit_id(null, @tx2) as G, + trt_commit_id(@ts3, true) is null as H; + +select + trt_trx_sees(@tx1, @tx0) as A, + not trt_trx_sees(@tx0, @tx1) as B, + trt_trx_sees_eq(@tx1, @tx1) as C, + not trt_trx_sees(@tx1, @tx1) as D, + trt_trx_sees(@tx2, 0) as E, + trt_trx_sees(-1, @tx2) as F; + +select trt_trx_sees(0, @tx2); + +# TRT_ISO_LEVEL # + +set transaction isolation level read uncommitted; +insert into t1 values (); +select sys_trx_start from t1 where id = last_insert_id() into @tx3; +select isolation_level = 'READ-UNCOMMITTED' from mysql.transaction_registry where transaction_id = @tx3; + +set transaction isolation level read committed; +insert into t1 values (); +select sys_trx_start from t1 where id = last_insert_id() into @tx4; +select isolation_level = 'READ-COMMITTED' from mysql.transaction_registry where transaction_id = @tx4; + +set transaction isolation level serializable; +insert into t1 values (); +select sys_trx_start from t1 where id = last_insert_id() into @tx5; +select isolation_level = 'SERIALIZABLE' from mysql.transaction_registry where transaction_id = @tx5; + +set transaction isolation level repeatable read; +insert into t1 values (); +select sys_trx_start from t1 where id = last_insert_id() into @tx6; +select isolation_level = 'REPEATABLE-READ' from mysql.transaction_registry where transaction_id = @tx6; + + +drop table t1; +call verify_trt; + +-- source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test new file mode 100644 index 00000000000..fc4120ef2aa --- /dev/null +++ b/mysql-test/suite/versioning/t/create.test @@ -0,0 +1,408 @@ +--source suite/versioning/engines.inc +--source suite/versioning/common.inc + +--disable_warnings +drop table if exists t1; +--enable_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_TEMPORARY +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; diff --git a/mysql-test/suite/versioning/t/cte.test b/mysql-test/suite/versioning/t/cte.test new file mode 100644 index 00000000000..5a8fb1f8211 --- /dev/null +++ b/mysql-test/suite/versioning/t/cte.test @@ -0,0 +1,228 @@ +--source include/have_innodb.inc +--source include/default_optimizer_switch.inc + +set time_zone="+00:00"; +set default_storage_engine=innodb; +create or replace table dept ( + dept_id int(10) primary key, + name varchar(100) +) with system versioning; + +create or replace table emp ( + emp_id int(10) primary key, + dept_id int(10) not null, + name varchar(100) not null, + mgr int(10), + salary int(10) not null, + constraint `dept-emp-fk` + foreign key (dept_id) references dept (dept_id) + on delete cascade + on update restrict, + constraint `mgr-fk` + foreign key (mgr) references emp (emp_id) + on delete restrict + on update restrict +) with system versioning; + +insert into dept (dept_id, name) values (10, "accounting"); + +insert into emp (emp_id, name, salary, dept_id, mgr) values +(1, "bill", 1000, 10, null), +(20, "john", 500, 10, 1), +(30, "jane", 750, 10,1 ); + +select row_start into @ts_1 from emp where name="jane"; + +update emp set mgr=30 where name ="john"; + +explain extended +with ancestors as ( + select e.emp_id, e.name, e.mgr, e.salary from emp as e where name = 'bill' + union + select e.emp_id, e.name, e.mgr, e.salary from emp as e +) select * from ancestors for system_time as of @ts_1; + +select row_start into @ts_2 from emp where name="john"; + +let $q= +/* All report to 'Bill' */ +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr, e.salary + from emp for system_time as of timestamp @ts_1 as e + where name = 'bill' + union + select e.emp_id, e.name, e.mgr, e.salary + from emp for system_time as of timestamp @ts_1 as e, + ancestors as a + where e.mgr = a.emp_id +) +select * from ancestors; + +eval explain extended $q; +eval $q; + +let $q=with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr, e.salary + from emp as e + where name = 'bill' + union + select e.emp_id, e.name, e.mgr, e.salary + from emp as e, + ancestors as a + where e.mgr = a.emp_id +) +select * from ancestors for system_time as of timestamp @ts_1; + +eval explain extended $q; +eval $q; + +let $q=with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr, e.salary + from emp as e + where name = 'bill' + union + select e.emp_id, e.name, e.mgr, e.salary + from emp as e, + ancestors as a + where e.mgr = a.emp_id +) +select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1); + +eval explain extended $q; +eval $q; + +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr, e.salary + from emp as e + where name = 'bill' + union + select e.emp_id, e.name, e.mgr, e.salary + from emp as e, + ancestors as a + where e.mgr = a.emp_id +) +select * from ancestors for system_time as of @ts_2, + ancestors for system_time as of @ts_2 a2; + +--error ER_CONFLICTING_FOR_SYSTEM_TIME +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr, e.salary + from emp as e + where name = 'bill' + union + select e.emp_id, e.name, e.mgr, e.salary + from emp as e, + ancestors as a + where e.mgr = a.emp_id +) +select * from ancestors for system_time as of @ts_2, + ancestors for system_time as of now() a2; + +--error ER_CONFLICTING_FOR_SYSTEM_TIME +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr, e.salary + from emp as e + where name = 'bill' + union + select e.emp_id, e.name, e.mgr, e.salary + from emp as e, + ancestors as a + where e.mgr = a.emp_id +) +select * from ancestors, + ancestors for system_time as of @ts_2 a2; + +--error ER_CONFLICTING_FOR_SYSTEM_TIME +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr, e.salary + from emp as e + where name = 'bill' + union + select e.emp_id, e.name, e.mgr, e.salary + from emp as e, + ancestors as a + where e.mgr = a.emp_id +) +select * from ancestors for system_time as of @ts_2, + ancestors a2; + +--error ER_CONFLICTING_FOR_SYSTEM_TIME +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr, e.salary + from emp as e + where name = 'bill' + union + select e.emp_id, e.name, e.mgr, e.salary + from emp as e, + ancestors as a + where e.mgr = a.emp_id +) +select * from ancestors for system_time as of @ts_2 + where emp_id in (select * from ancestors); + +--echo # SYSTEM_TIME to internal recursive instance is prohibited +--error ER_VERS_NOT_VERSIONED +with recursive cte as +( + select * from emp + union all + select * from cte for system_time as of @ts_1 +) +select * from cte; + +create or replace table emp ( emp_id int, name varchar(127), mgr int) with system versioning; +create or replace table addr ( emp_id int, address varchar(100)) with system versioning; +insert emp values (1, 'bill', 0), (2, 'bill', 1), (3, 'kate', 1); +insert addr values (1, 'Moscow'), (2, 'New York'), (3, 'London'); +set @ts=now(6); +delete from emp; +delete from addr; + +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp for system_time as of timestamp @ts as e + where name = 'bill' + union + select ee.emp_id, ee.name, ee.mgr + from emp for system_time as of timestamp @ts as ee, ancestors as a + where ee.mgr = a.emp_id +) +select * from ancestors; + +insert emp values (4, 'john', 1); +insert addr values (4, 'Paris'); +with ancestors as (select * from emp natural join addr) select * from ancestors; +with ancestors as (select * from emp natural join addr) select * from ancestors for system_time all; +with ancestors as (select * from (select * from emp natural join addr) for system_time all as t) select * from ancestors; +select * from (select * from emp natural join addr) for system_time all as t; + +drop table emp; +drop table dept; +drop table addr; diff --git a/mysql-test/suite/versioning/t/debug.test b/mysql-test/suite/versioning/t/debug.test new file mode 100644 index 00000000000..c6d5bd60861 --- /dev/null +++ b/mysql-test/suite/versioning/t/debug.test @@ -0,0 +1,35 @@ +--source include/have_debug.inc + +create table t1 (a int); +show create table t1; + +--error ER_VERS_TEMPORARY +create temporary table tt1 (a int) with system versioning; + +set @old_dbug=@@global.debug_dbug; +set global debug_dbug='+d,sysvers_force'; + +create table t2 (a int); +show create table t2; + +create temporary table tt2 (a int) with system versioning; +show create table tt2; + +--connect con1, localhost, root + +create table t3 (a int); +show create table t3; + +create temporary table tt3 (a int) with system versioning; +show create table tt3; +--disconnect con1 +--connection default + +set debug_dbug='+d,sysvers_show'; + +show create table t3; +create table t4 (a int); +show create table t4; + +set global debug_dbug=@old_dbug; +drop table t1, t2, t3, t4; diff --git a/mysql-test/suite/versioning/t/delete.test b/mysql-test/suite/versioning/t/delete.test new file mode 100644 index 00000000000..492463f9395 --- /dev/null +++ b/mysql-test/suite/versioning/t/delete.test @@ -0,0 +1,97 @@ +source suite/versioning/engines.inc; +source suite/versioning/common.inc; + +--echo # Basic + delete from view +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create or replace table t1( + XNo int unsigned, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time (sys_start, sys_end)) + with system versioning; + +insert into t1(XNo) values(0); +insert into t1(XNo) values(1); +insert into t1(XNo) values(2); +insert into t1(XNo) values(3); +insert into t1(XNo) values(4); +insert into t1(XNo) values(5); +insert into t1(XNo) values(6); +insert into t1(XNo) values(7); +insert into t1(XNo) values(8); +insert into t1(XNo) values(9); +replace_result $sys_datatype_max MAXVAL; +eval select XNo, sys_end < $sys_datatype_max from t1 for system_time all; +delete from t1 where XNo = 0; +delete from t1 where XNo = 1; +delete from t1 where XNo > 5; +create view vt1 as select XNo from t1; +select XNo as XNo_vt1 from vt1; +delete from vt1 where XNo = 3; +select XNo as XNo_vt1 from vt1; +drop view vt1; +drop table t1; + +--echo # Check sys_start, sys_end +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create or replace table t1( + x int, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time (sys_start, sys_end)) + with system versioning; + +insert into t1(x) values (1); +select sys_start into @sys_start from t1; +delete from t1; +select * from t1; +select x = 1 as A, sys_start = @sys_start as B, sys_end > sys_start as C from t1 for system_time all; +drop table t1; + +--echo # Multi-delete +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create or replace table t1( + x int, + y int, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time (sys_start, sys_end)) + with system versioning; +create or replace table t2 like t1; +insert into t1(x, y) values (1, 1), (2, 2), (3, 3), (14, 4); +insert into t2(x, y) values (11, 1), (12, 2), (13, 32), (14, 4); +delete t1, t2 from t1 join t2 where t1.y = 3 and t2.y = 32; +select x as t1_x from t1; +select x as t2_x from t2; +delete t1, t2 from t1 join t2 where t1.x = t2.x; +select x as t1_x from t1; +select x as t2_x from t2; +select x as t1_x_all from t1 for system_time all; +select x as t2_x_all from t2 for system_time all; +drop table t1; +drop table t2; + +--echo # Update + delete +create or replace table t1 (x int) with system versioning; +insert into t1 values (1); +update t1 set x= 2; +delete from t1; +select x from t1 for system_time all; +drop table t1; + +--echo # +--echo # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED +--echo # +create or replace table t1 (a int) with system versioning; +replace into t1 values (1), (2); +create or replace trigger tr before delete on t1 for each row delete from xx; +create or replace procedure pr() delete from t1; +--error ER_NO_SUCH_TABLE +call pr; +--error ER_NO_SUCH_TABLE +call pr; +drop procedure pr; +drop trigger tr; +drop table t1; + +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/delete_history.test b/mysql-test/suite/versioning/t/delete_history.test new file mode 100644 index 00000000000..8a7f8e84a76 --- /dev/null +++ b/mysql-test/suite/versioning/t/delete_history.test @@ -0,0 +1,144 @@ +--source suite/versioning/common.inc +--source include/have_partition.inc +--source suite/versioning/engines.inc + +create table t (a int); +--error ER_VERS_NOT_VERSIONED +delete history from t before system_time now(); + +# TRUNCATE is not DELETE and trigger must not be called. +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t ( + a int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end)) +with system versioning; +insert into t values (1); +update t set a=2; +set @test = 'correct'; +create trigger trg_before before delete on t for each row set @test = 'incorrect'; +create trigger trg_after after delete on t for each row set @test = 'incorrect'; +delete history from t; +select @test from t; +drop table t; + +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t ( + a int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end)) +with system versioning; +insert into t values (1), (2); +update t set a=11 where a=1; +--real_sleep 0.01 +set @ts1=now(6); +--real_sleep 0.01 +update t set a=22 where a=2; +select * from t for system_time all; +delete history from t before system_time timestamp @ts1; +select * from t for system_time all; +prepare stmt from 'delete history from t'; +execute stmt; drop prepare stmt; +select * from t for system_time all; +delete from t; + +delimiter ~~; +create or replace procedure truncate_sp() +begin + delete history from t before system_time timestamp now(6); +end~~ +delimiter ;~~ +call truncate_sp; +select * from t for system_time all; + +drop procedure truncate_sp; + +--echo # Truncate partitioned +create or replace table t (a int) +with system versioning +partition by system_time limit 1 ( + partition p0 history, + partition p1 history, + partition pn current); +insert into t values (1); +update t set a= 2; +update t set a= 3; +delete history from t; +select * from t for system_time all; + +--echo # VIEW +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t ( + i int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end)) +with system versioning; +delete history from t; +create or replace view v as select * from t; +--error ER_IT_IS_A_VIEW +delete history from v; + +create or replace table t (i int); +--error ER_VERS_NOT_VERSIONED +delete history from t; +create or replace view v as select * from t; +--error ER_IT_IS_A_VIEW +delete history from v; +--error ER_VERS_NOT_VERSIONED +prepare stmt from 'delete history from t'; + +drop table t; +drop view v; + +# +# MDEV-15402 Assertion `table' failed in mysql_delete on attempt to delete history from view +# +create or replace table t (i int); +create or replace view v as select * from t; +--error ER_IT_IS_A_VIEW +drop table v; +lock table v write; +--error ER_IT_IS_A_VIEW +delete history from v before system_time now(6); +unlock tables; +drop view v; +drop table t; + +# +# MDEV-16783 Assertion `!conds' failed in mysql_delete upon 2nd execution of SP with DELETE HISTORY +# +create table t1 (i int) with system versioning; +create procedure pr() delete history from t1 before system_time now(); +call pr; +call pr; +drop procedure pr; +drop table t1; + +--echo # +--echo # MDEV-19814 Assertion `update->n_fields < ulint(table->n_cols + table->n_v_cols)' on DELETE HISTORY +--echo # +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t1 ( + f varchar(1), + row_start $sys_datatype_expl as row start, + row_end $sys_datatype_expl as row end, + period for system_time (row_start, row_end)) +with system versioning; +insert into t1 (f) values ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'); +delete from t1; +delete history from t1; +drop table t1; + +--echo # +--echo # MDEV-20186 Wrong result or Assertion on INSERT after DELETE HISTORY +--echo # +create or replace table t1 (a int check (a > 0)) with system versioning; +delete history from t1; +insert into t1 values (1); +select * from t1; +drop table t1; + +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/derived.test b/mysql-test/suite/versioning/t/derived.test new file mode 100644 index 00000000000..9d96856f01f --- /dev/null +++ b/mysql-test/suite/versioning/t/derived.test @@ -0,0 +1,238 @@ +--source include/default_optimizer_switch.inc + +create table emp +( + emp_id int, + name varchar(127), + mgr int +) with system versioning; + +insert into emp values (1, 'bill', 0), + (2, 'bill', 1), + (3, 'kate', 1); +set @ts=now(6); +delete from emp; +insert into emp values (4, 'john', 1); + +with ancestors as (select * from emp) select * from ancestors; +set @tmp= "with ancestors as (select * from emp) select * from ancestors"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +with ancestors as (select * from emp for system_time all) select * from ancestors; +set @tmp= "with ancestors as (select * from emp for system_time all) select * from ancestors"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +with recursive ancestors as (select * from emp) select * from ancestors; +set @tmp= "with recursive ancestors as (select * from emp) select * from ancestors"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +select emp_id from (select emp_id from emp where row_end>'2031-1-1') as tmp; +set @tmp= "select emp_id from (select emp_id from emp where row_end>'2031-1-1') as tmp"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp as e + where name = 'john' + union + select ee.emp_id, ee.name, ee.mgr + from emp as ee, ancestors as a + where ee.mgr = a.emp_id +) +select * from ancestors; +set @tmp= " +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp as e + where name = 'john' + union + select ee.emp_id, ee.name, ee.mgr + from emp as ee, ancestors as a + where ee.mgr = a.emp_id +) +select * from ancestors"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +#385 +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp for system_time as of timestamp @ts as e + where name = 'bill' + union + select ee.emp_id, ee.name, ee.mgr + from emp for system_time as of timestamp @ts as ee, + ancestors as a + where ee.mgr = a.emp_id +) +select * from ancestors; +set @tmp= " +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp for system_time as of timestamp @ts as e + where name = 'bill' + union + select ee.emp_id, ee.name, ee.mgr + from emp for system_time as of timestamp @ts as ee, + ancestors as a + where ee.mgr = a.emp_id +) +select * from ancestors"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +drop table emp; + +create or replace table t1 (x int) with system versioning; +create or replace table t2 (y int) with system versioning; +insert into t1 values (1); +set @t0= now(6); +delete from t1; +insert into t1 values (2); +insert into t2 values (10); + +--replace_column 2 # 3 # +select * from (select *, t1.row_end, t1.row_end as endo from t1) as s0; +--replace_column 3 # 4 # +select * from (select *, t1.row_end, t2.row_start from t1, t2) as s0; + +--echo # SYSTEM_TIME propagation from inner to outer +select * from (select * from t1 for system_time as of timestamp @t0, t2) as s0; +with s1 as (select * from t1 for system_time as of timestamp @t0, t2) select * from s1; +--echo # leading table selection +--replace_column 3 # +select * from (select *, t1.row_end from t2, t1 for system_time as of timestamp @t0) as s2; +--replace_column 3 # +with s3 as (select *, t1.row_end from t2, t1 for system_time as of timestamp @t0) select * from s3; + +--echo ### VIEW instead of t1 +set @q= concat("create view vt1 as select * from t1 for system_time as of timestamp '", @t0, "'"); +prepare q from @q; execute q; drop prepare q; +create view vt2 as select * from t1; + +--echo # SYSTEM_TIME propagation from view +select * from vt1; +--echo # SYSTEM_TIME propagation from inner to outer +select * from (select * from vt1, t2) as s0; + +--echo ### SYSTEM_TIME clash +--error ER_VERS_NOT_VERSIONED +select * from (select * from t1 for system_time all) for system_time all as dt0; +--error ER_VERS_NOT_VERSIONED +select * from vt1 for system_time all; +--error ER_VERS_NOT_VERSIONED +with dt1 as (select * from t1 for system_time all) +select * from dt1 for system_time all; + +--echo ### UNION +set @t1= now(6); +delete from t2; +insert into t2 values (3); +--echo # SYSTEM_TIME is not propagated +select x from t1 union +select y from t2; +select x from t1 for system_time as of @t0 union +select y from t2; +select x from t1 union +select y from t2 for system_time as of @t1; +select x from t1 for system_time as of @t0 union +select y from t2 for system_time as of @t1; + +--echo # LEFT/RIGHT JOIN +create or replace table t1 (x int, y int) with system versioning; +create or replace table t2 (x int, y int) with system versioning; + +insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5); +insert into t2 values (1, 2), (2, 1), (3, 1); + +--echo ## Outer or inner SYSTEM_TIME produces same expression + +--disable_warnings +--disable_query_log +explain extended +select * from ( + select t1.x, t1.y as y1, t2.x as x2, t2.y as y2 + from t1 join t2 on t1.x = t2.x) for system_time as of now() as t; + +let $a=`show warnings`; +--echo Query A: +echo $a; + +explain extended +select * from ( + select t1.x, t1.y as y1, t2.x as x2, t2.y as y2 + from t1 for system_time as of now() + join t2 for system_time as of now() on t1.x = t2.x) as t; + +let $b=`show warnings`; +--echo Query B: +echo $b; + +if ($a == $b) +{ + --echo Fine result: queries A and B are equal. +} +--enable_query_log +--enable_warnings + +--echo ## LEFT JOIN: t1, t2 versioned +select * from ( + select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 + from t1 left join t2 on t1.x = t2.x) +as derived; + +alter table t2 drop system versioning; + +--echo ## LEFT JOIN: t1 versioned +select * from ( + select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 + from t1 left join t2 on t1.x = t2.x) +as derived; + +alter table t1 drop system versioning; +alter table t2 add system versioning; + +--echo ## LEFT JOIN: t2 versioned +select * from ( + select t1.x as LJ3_x1, t1.y as y1, t2.x as x2, t2.y as y2 + from t1 left join t2 on t1.x = t2.x) +as derived; + +alter table t1 add system versioning; + +--echo ## RIGHT JOIN: t1, t2 versioned +select * from ( + select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 + from t1 right join t2 on t1.x = t2.x) +as derived; + +alter table t2 drop system versioning; + +--echo ## RIGHT JOIN: t1 versioned +select * from ( + select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 + from t1 right join t2 on t1.x = t2.x) +as derived; + +alter table t1 drop system versioning; +alter table t2 add system versioning; + +--echo ## RIGHT JOIN: t2 versioned +select * from ( + select t1.x as RJ3_x1, t1.y as y1, t2.x as x2, t2.y as y2 + from t1 right join t2 on t1.x = t2.x) +as derived; + +drop table t1, t2; +drop view vt1, vt2; + diff --git a/mysql-test/suite/versioning/t/foreign.combinations b/mysql-test/suite/versioning/t/foreign.combinations new file mode 100644 index 00000000000..1a0812cfafe --- /dev/null +++ b/mysql-test/suite/versioning/t/foreign.combinations @@ -0,0 +1,5 @@ +[timestamp] +default-storage-engine=innodb + +[trx_id] +default-storage-engine=innodb diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test new file mode 100644 index 00000000000..7493f99cba7 --- /dev/null +++ b/mysql-test/suite/versioning/t/foreign.test @@ -0,0 +1,461 @@ +--source suite/versioning/key_type.inc +--source suite/versioning/common.inc + +--echo ################# +--echo # Test RESTRICT # +--echo ################# + +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int, + $KEY_TYPE (id) +) engine innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create table child( + parent_id int, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end), + foreign key(parent_id) references parent(id) + on delete restrict + on update restrict +) engine innodb with system versioning; + +insert into parent values(1); +insert into child values(1); + +-- error ER_ROW_IS_REFERENCED_2 +delete from parent where id = 1; +delete from child where parent_id = 1; +delete from parent where id = 1; + +insert into parent values(1); +insert into child values(1); +-- error ER_ROW_IS_REFERENCED_2 +update parent set id=id+1; +delete from child; +update parent set id=id+1; +select * from child for system_time all; + +drop table child; +drop table parent; + +--echo ############################################## +--echo # Test when clustered index is a foreign key # +--echo ############################################## + +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int(10) unsigned, + $KEY_TYPE (id) +) engine innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create table child( + parent_id int(10) unsigned primary key, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end), + foreign key(parent_id) references parent(id) +) engine innodb with system versioning; + +insert into parent values(1); +insert into child values(1); + +-- error ER_ROW_IS_REFERENCED_2 +delete from parent where id = 1; + +drop table child; +drop table parent; + +--echo ################ +--echo # Test CASCADE # +--echo ################ + +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int, + $KEY_TYPE (id) +) engine innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create table child( + parent_id int, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end), + foreign key(parent_id) references parent(id) + on delete cascade + on update cascade +) engine innodb with system versioning; + +insert into parent values(1); +insert into child values(1); + +delete from parent where id = 1; +select * from child; +select * from child for system_time all; + +insert into parent values(1); +insert into child values(1); +update parent set id = id + 1; +select * from child; +select * from child for system_time all; + +drop table child; +drop table parent; + +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE +eval create or replace table parent ( + id int, + $KEY_TYPE(id), + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end) +) with system versioning +engine innodb; + +create or replace table child ( + x int, + parent_id int not null, + constraint `parent-fk` + foreign key (parent_id) references parent (id) + on delete cascade + on update restrict +) +engine innodb; + +insert into parent (id) values (2); +insert into child (x, parent_id) values (2, 2); +delete from parent; +select * from child; + +drop table child; +drop table parent; + +--replace_result "$KEY_TYPE" KEY_TYPE +eval create or replace table parent ( + id int, + $KEY_TYPE(id) +) +engine innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table child ( + id int primary key, + parent_id int not null, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time(row_start, row_end), + constraint `parent-fk` + foreign key (parent_id) references parent (id) + on delete cascade + on update restrict +) with system versioning +engine innodb; + +insert into parent (id) values (3); +insert into child (id, parent_id) values (3, 3); +delete from parent; +select * from child; +select *, check_row(row_start, row_end) from child for system_time all; + +drop table child; +drop table parent; + +--echo ################# +--echo # Test SET NULL # +--echo ################# + +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int, + $KEY_TYPE (id) +) engine innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table child( + parent_id int, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end), + foreign key(parent_id) references parent(id) + on delete set null + on update set null +) engine innodb with system versioning; + +insert into parent values(1); +insert into child values(1); +delete from child; +insert into child values(1); + +delete from parent where id = 1; +select * from child; +select *, current_row(sys_end) as current_row from child for system_time all order by sys_end; +delete from child; + +insert into parent values(1); +insert into child values(1); +update parent set id= id + 1; +select * from child; +select *, current_row(sys_end) as current_row from child for system_time all order by sys_end; + +drop table child; +drop table parent; + +--echo ########################### +--echo # Parent table is foreign # +--echo ########################### + +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE +eval create or replace table parent( + id int, + $KEY_TYPE (id), + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end) +) engine innodb with system versioning; + +create or replace table child( + parent_id int, + foreign key(parent_id) references parent(id) +) engine innodb; + +insert into parent values(1); +insert into child values(1); +-- error ER_ROW_IS_REFERENCED_2 +delete from parent; +-- error ER_ROW_IS_REFERENCED_2 +update parent set id=2; + +delete from child; +delete from parent; + +-- error ER_NO_REFERENCED_ROW_2 +insert into child values(1); + +insert into parent values(1); +insert into child values(1); +-- error ER_ROW_IS_REFERENCED_2 +delete from parent; +-- error ER_ROW_IS_REFERENCED_2 +update parent set id=2; + +drop table child; +drop table parent; + +--echo ################### +--echo # crash on DELETE # +--echo ################### + +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE +eval create or replace table a ( + cola int(10), + $KEY_TYPE (cola), + v_cola int(10) as (cola mod 10) virtual, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end) +) engine=innodb with system versioning; + +create index v_cola on a (v_cola); + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table b( + cola int(10), + v_cola int(10), + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end) +) engine=innodb with system versioning; + +alter table b add constraint `v_cola_fk` +foreign key (v_cola) references a (v_cola); + +insert into a(cola) values (12); +insert into b(cola, v_cola) values (10,2); +--error ER_ROW_IS_REFERENCED_2 +delete from a; + +drop table b, a; + +--echo ############################################### +--echo # CASCADE UPDATE foreign not system versioned # +--echo ############################################### +create or replace table parent ( + id smallint unsigned not null auto_increment, + value int unsigned not null, + primary key (id, value) +) engine = innodb; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table child ( + id mediumint unsigned not null auto_increment primary key, + parent_id smallint unsigned not null, + parent_value int unsigned not null, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time(sys_start, sys_end), + constraint `fk_child_parent` + foreign key (parent_id, parent_value) references parent (id, value) + on delete cascade + on update cascade +) engine = innodb with system versioning; + +create or replace table subchild ( + id int not null auto_increment primary key, + parent_id smallint unsigned not null, + parent_value int unsigned not null, + constraint `fk_subchild_child_parent` + foreign key (parent_id, parent_value) references child (parent_id, parent_value) + on delete cascade + on update cascade +) engine=innodb; + +insert into parent (value) values (23); +select id, value from parent into @id, @value; +insert into child values (default, @id, @value); +insert into subchild values (default, @id, @value); + +select parent_id from subchild; +update parent set id = 11, value = value + 1; +select parent_id from subchild; +select * from child; + +delete from parent; +select count(*) from child; +select * from child for system_time all; +select count(*) from subchild; + +drop table subchild, child, parent; + +--echo # +--echo # MDEV-18057 Assertion `(node->state == 5) || (node->state == 6)' failed in row_upd_sec_step upon DELETE after UPDATE failed due to FK violation +--echo # +create or replace table t1 (f1 int, key(f1)) engine=innodb; +create or replace table t2 (f2 int, foreign key (f2) references t1 (f1)) engine=innodb with system versioning; + +set foreign_key_checks= off; +insert ignore into t2 values (1); + +set foreign_key_checks= on; +--error ER_NO_REFERENCED_ROW_2 +update t2 set f2= 2; +delete from t2; + +drop table t2, t1; + +--echo # +--echo # MDEV-18879 Corrupted record inserted by FOREIGN KEY operation +--echo # +SET timestamp = 1; +SET time_zone='+02:00'; +SELECT now(); +CREATE TABLE t1 ( + pk INT UNSIGNED PRIMARY KEY, + f1 varchar(255) CHARACTER SET ucs2, + f2 longtext CHARACTER SET ucs2, + f3 varchar(255), + f4 char(255), + f5 longtext CHARACTER SET ucs2, + f6 INT UNSIGNED, + f7 INT UNSIGNED, + f8 INT UNSIGNED, + f9 INT UNSIGNED, + f10 INT UNSIGNED, + f11 INT UNSIGNED, + f12 varchar(255) CHARACTER SET ucs2, + f13 char(255) CHARACTER SET ucs2, + f14 char(255) CHARACTER SET ucs2, + f15 varchar(255), + f16 longtext, + f17 char(255) +) ENGINE=InnoDB WITH SYSTEM VERSIONING; + +INSERT INTO t1 VALUES +(1, 'a', 'e', 'f', 'a', 'generate', 1, 2, 3, 4, 5, 6, 'main', 'against', 'b', 'u', 'explode', 'tomorrow'), +(2, REPEAT('a',127), 'f', 'k', 'game', 'g', 2, 3, 4, 5, 6, 7, REPEAT('o',222), 'oven', 'flower', REPEAT('r',120), 'l', 'g'), +(3, 'weekly', 'x', 'v', 'r', 'c', 3, 4, 5, 6, 7, 8, 'validity', 'y', 'h', 'oxygen', 'venture', 'uncertainty'), +(4, 'r', 't', REPEAT('b',153), 'modern', 'h', 4, 5, 6, 7, 8, 9, REPEAT('g',128), 'a', 'w', 'f', 'b', 'b'), +(5, 'h', 'y', REPEAT('v',107), 'knife', 'profession', 5, 6, 7, 8, 9, 0, 'infection', 'u', 'likelihood', REPEAT('n',149), 'folk', 'd'), +(6, 'g', 'violent', REPEAT('o',28), 'capital', 'p', 6, 7, 8, 9, 0, 1, 'w', 'patron', 'd', 'y', 'originally', 'k'), +(7, 'k', 'uncomfortable', REPEAT('v',248), 'y', 'link', 7, 8, 9, 0, 1, 2, REPEAT('j',204), 'j', 'statute', 'emphasis', 'u', 'water'), +(8, 'preparation', 'water', 'suck', 'silver', 'a', 8, 9, 0, 1, 2, 3, 'h', 'q', 'o', 't', 'k', 'y'), +(9, 'y', 'f', 'e', 'a', 'dawn', 9, 0, 1, 2, 3, 4, 'peak', 'parking', 'b', 't', 'timber', 'c'), +(10, REPEAT('h',78), 'apologize', 'direct', 'u', 'frankly', 0, 1, 2, 3, 4, 5, 'h', 'exhibit', 'f', 'd', 'effective', 'c'), +(11, 'i', 'h', 'a', 'y', 'u', 1, 2, 3, 4, 5, 6, 'l', 'b', 'm', 'respond', 'ideological', 'credibility'); + +CREATE TABLE t2 ( + pk int primary key, + f char(255) CHARACTER SET ucs2, + key(f) +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES (1,'against'),(2,'q'); + +SET SQL_MODE= ''; +SET timestamp = 2; +SELECT * FROM t1 INTO OUTFILE 't1.data'; +SET timestamp = 3; +UPDATE t1 SET f13 = 'q'; +SET timestamp = 4; +LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1; +SELECT * FROM t1 INTO OUTFILE 't1.data.2' ; +SET timestamp = 5; +LOAD DATA INFILE 't1.data.2' REPLACE INTO TABLE t1; +SELECT * FROM t2 INTO OUTFILE 't2.data'; +SET timestamp = 6; +LOAD DATA INFILE 't2.data' REPLACE INTO TABLE t2; +SET FOREIGN_KEY_CHECKS = OFF; +ALTER TABLE t1 ADD FOREIGN KEY (f13) REFERENCES t2 (f) ON DELETE SET NULL; +SET timestamp = 7; +LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1; +SET FOREIGN_KEY_CHECKS = ON; + +SET SESSION SQL_MODE= 'NO_BACKSLASH_ESCAPES'; +SET timestamp = 8; +LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1; +SET timestamp = 9; +REPLACE INTO t2 SELECT * FROM t2; + +# Cleanup +DROP TABLE t1, t2; +--let $datadir= `select @@datadir` +--remove_file $datadir/test/t1.data +--remove_file $datadir/test/t1.data.2 +--remove_file $datadir/test/t2.data + +--echo # +--echo # MDEV-16210 FK constraints on versioned tables use historical rows, which may cause constraint violation +--echo # +create or replace table t1 (a int, key(a)) engine innodb with system versioning; +create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb; +insert into t1 values (1),(2); +insert into t2 values (1); +--echo # DELETE from referenced table is not allowed +--error ER_ROW_IS_REFERENCED_2 +delete from t1 where a = 1; +drop tables t2, t1; + +--echo # +--echo # MDEV-20812 Unexpected ER_ROW_IS_REFERENCED_2 or server crash in row_ins_foreign_report_err upon DELETE from versioned table with FK +--echo # +create or replace table t1 (x int primary key) engine innodb; +create or replace table t2 (x int, foreign key (x) references t1(x)) engine innodb with system versioning; +set foreign_key_checks= off; +insert into t2 values (1), (1); +set foreign_key_checks= on; +--echo # DELETE from foreign table is allowed +delete from t2; +drop tables t2, t1; + +create or replace table t1 (a int, key(a)) engine innodb; +insert into t1 values (1); +create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb with system versioning; +insert into t2 values (1), (1); +--echo # DELETE from foreign table is allowed +delete from t2; +drop tables t2, t1; + +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/insert.test b/mysql-test/suite/versioning/t/insert.test new file mode 100644 index 00000000000..0324df64d0c --- /dev/null +++ b/mysql-test/suite/versioning/t/insert.test @@ -0,0 +1,82 @@ +source suite/versioning/engines.inc; +source suite/versioning/common.inc; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create or replace table t1( + x int unsigned, + y int unsigned, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time (sys_start, sys_end)) +with system versioning; + +insert into t1(x, y) values(3, 4); +insert into t1(x, y) values(2, 3); +insert into t1 values(40, 33); +replace_result $sys_datatype_max MAXVAL; +eval select x, y, sys_end < $sys_datatype_max from t1; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create or replace table t1( + id int unsigned auto_increment primary key, + x int unsigned, + y int unsigned, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time (sys_start, sys_end)) +with system versioning; + +insert into t1(x, y) values(33, 44); +insert into t1(id, x, y) values(20, 33, 44); +insert into t1 values(40, 33, 44); +replace_result $sys_datatype_max MAXVAL; +eval select id, x, y, sys_end < $sys_datatype_max from t1; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create or replace table t1( + x int unsigned, + y int unsigned, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time (sys_start, sys_end)) +with system versioning; +create view vt1_1 as select x, y from t1; +insert into t1(x, y) values(8001, 9001); +insert into vt1_1(x, y) values(1001, 2001); +insert into vt1_1 values(1002, 2002); +replace_result $sys_datatype_max MAXVAL; +eval select x, y, sys_end < $sys_datatype_max from t1; +select x, y from vt1_1; +drop view vt1_1; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create or replace table t1( id bigint primary key, a int, b int) with system versioning; +insert into t1 values(1, 1, 1); +select row_start, row_end from t1 into @sys_start, @sys_end; +select id, a, b from t1; +insert into t1 values(2, 2, 2); +select id, a, b, row_start > @sys_start as C, row_end = @sys_end as D from t1 where id = 2; +drop table t1; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create or replace table t1( + x int unsigned, + y int unsigned, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time (sys_start, sys_end)) +with system versioning; +create or replace table t2 like t1; +insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000); +delete from t1 where x >= 1; +insert into t1(x, y) values (1, 1001), (2, 2001), (3, 3001), (4, 4001), (5, 5001), (6, 6001); +insert into t1(x, y, sys_start) values (7, 7001, DEFAULT); +insert into t1(x, y, sys_end) values (8, 8001, DEFAULT); +insert into t1(x, y, sys_start, sys_end) values (9, 9001, DEFAULT, DEFAULT); +insert into t2 select x, y from t1 for system_time all; +select x, y from t1; +select x, y from t2; +drop table t1; +drop table t2; + +-- source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/insert2.test b/mysql-test/suite/versioning/t/insert2.test new file mode 100644 index 00000000000..1e7d2166064 --- /dev/null +++ b/mysql-test/suite/versioning/t/insert2.test @@ -0,0 +1,86 @@ +--source include/have_innodb.inc + +# TRT test + +create table t1( + x int unsigned, + sys_start bigint unsigned 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; + +create table t2(x int unsigned) engine=innodb; + +start transaction; +insert into t1(x) values(1); +commit; + +start transaction; +insert into t2(x) values(1); +savepoint a; +insert into t1(x) values(1); +rollback to a; +commit; + +insert into t2(x) values (1); + +# virtual columns +create or replace table t1 ( + x int, + y int as (x) virtual, + sys_trx_start bigint unsigned as row start invisible, + sys_trx_end bigint unsigned as row end invisible, + period for system_time (sys_trx_start, sys_trx_end) +) engine=innodb with system versioning; +insert into t1 values (1, null); +update t1 set x= x + 1; +select x, y, sys_trx_end = 18446744073709551615 as current from t1 for system_time all; + +create or replace table t1 ( + x int, + row_start timestamp(6) as row start invisible, + row_end timestamp(6) as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; +insert into t1 values (1), (2); +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN +insert into t1 (row_start) select row_end from t1; +set sql_mode=''; +insert into t1 (row_start, row_end) values (DEFAULT, 1); +set sql_mode=default; +select @@sql_mode into @saved_mode; +set sql_mode= ''; +insert into t1 (x, row_start, row_end) values (3, 4, 5); +set sql_mode= @saved_mode; +insert into t1 (row_start, row_end) values (DEFAULT, DEFAULT); +select * from t1; + +--echo # MDEV-14792 INSERT without column list into table with explicit versioning columns produces bad data +create or replace table t1 ( + i int, + s timestamp(6) as row start, + e timestamp(6) as row end, + c varchar(8), + period for system_time(s, e)) +with system versioning; +insert into t1 values (1, null, null, 'foo'); +select i, c, e>TIMESTAMP'2038-01-01 00:00:00' AS current_row from t1; + +drop table t1; +drop table t2; + +--echo # +--echo # MDEV-14871 Server crashes in fill_record / fill_record_n_invoke_before_triggers upon inserting into versioned table with trigger +--echo # +create or replace table t1 (pk int primary key) with system versioning; +create trigger tr before insert on t1 for each row select 1 into @a; +insert into t1 values (1),(2); +drop table t1; + +# +# MDEV-14794 Limitations which the row end as a part of PK imposes due to CURRENT_TIMESTAMP behavior and otherwise +# +create table t1 (pk int primary key, i int) with system versioning; +replace into t1 values (1,10),(1,100),(1,1000); +select pk,i,row_end > '2038-01-01' from t1 for system_time all; +drop table t1; diff --git a/mysql-test/suite/versioning/t/load_data.test b/mysql-test/suite/versioning/t/load_data.test new file mode 100644 index 00000000000..4db6eee6c4d --- /dev/null +++ b/mysql-test/suite/versioning/t/load_data.test @@ -0,0 +1,12 @@ +# +# MDEV-15330 Server crash or assertion `table->insert_values' failure in write_record upon LOAD DATA +# +CREATE TABLE t1 (a INT, b INT, c INT, vc INT AS (c), UNIQUE(a), UNIQUE(b)) WITH SYSTEM VERSIONING; +INSERT IGNORE INTO t1 (a,b,c) VALUES (1,2,3); + +SELECT a, b, c FROM t1 INTO OUTFILE '15330.data'; +LOAD DATA INFILE '15330.data' IGNORE INTO TABLE t1 (a,b,c); +LOAD DATA INFILE '15330.data' REPLACE INTO TABLE t1 (a,b,c); + +# Cleanup +DROP TABLE t1; diff --git a/mysql-test/suite/versioning/t/online.test b/mysql-test/suite/versioning/t/online.test new file mode 100644 index 00000000000..cff3193407b --- /dev/null +++ b/mysql-test/suite/versioning/t/online.test @@ -0,0 +1,139 @@ +--source suite/versioning/innodb.inc +--source suite/versioning/common.inc +--source include/maybe_debug.inc + +set system_versioning_alter_history=keep; + +create or replace table t (a int); +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t add system versioning, lock=none; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t add system versioning, algorithm=inplace; +alter table t add system versioning, lock=shared; + +alter table t add column b int, change column a a int without system versioning, lock=none; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t drop system versioning, lock=none; +alter table t drop system versioning, algorithm=inplace; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t ( + a int, b int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; +insert into t values (1, 0); +insert into t values (2, 0); +delete from t where a = 2; +alter table t drop column b, lock=none; +alter table t add index idx(a), lock=none; + +select a, check_row(row_start, row_end) from t for system_time all order by a; + +--echo # MDEV-17038 ALTER TABLE CHANGE COLUMN c1 c1 bigint NOT NULL - +--echo # generates error if table uses SYSTEM VERSIONING [tempesta-tech/mariadb#540] +create or replace table t1 (a int, key(a)) with system versioning; +create or replace table t2 like t; +alter table t2 add foreign key(a) references t1(a); +alter table t2 modify column a int not null, lock=none; + +drop table t2; +drop table t1; + +--echo # MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + a int, + b int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time(row_start, row_end) +) with system versioning; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t2 ( + a int without system versioning, + b int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time(row_start, row_end) +) with system versioning; + +insert into t1 values (1,1); +insert into t2 values (1,1); + +set @@system_versioning_alter_history=keep; + +--enable_info +--echo # without rebuild +alter table t1 + change a a int without system versioning, + algorithm=instant; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t2 + change a a int with system versioning, + add primary key pk (a), + algorithm=instant; + +--echo # with rebuild +alter table t2 + change a a int with system versioning, + add primary key pk (a); +--disable_info + +--source include/restart_mysqld.inc + +update t1 set a=2; +select count(*) from t1 for system_time all; + +update t2 set a=2; +select count(*) from t2 for system_time all; + +drop table t1, t2; + +--echo # rollback ALTER TABLE: nothing should change +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t ( + a int, + b int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time(row_start, row_end) +) with system versioning; + +insert into t values (1, 1); + +select c.prtype from information_schema.innodb_sys_columns as c + join information_schema.innodb_sys_tables as t on c.table_id=t.table_id + where t.name='test/t' and c.name='b'; + +set @@system_versioning_alter_history=keep; + +if ($have_debug) { +--disable_query_log +--disable_result_log +set debug_dbug='+d,ib_commit_inplace_fail_1'; +--error ER_INTERNAL_ERROR +alter table t + change b b int without system versioning; +set debug_dbug= default; +--enable_query_log +--enable_result_log +} + +select c.prtype from information_schema.innodb_sys_columns as c + join information_schema.innodb_sys_tables as t on c.table_id=t.table_id + where t.name='test/t' and c.name='b'; + +--replace_result $sys_datatype_expl SYS_DATATYPE +show create table t; + +select count(*) from t for system_time all; +update t set b=11; +select count(*) from t for system_time all; + +drop table t; + +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/optimized.test b/mysql-test/suite/versioning/t/optimized.test new file mode 100644 index 00000000000..054c1d32559 --- /dev/null +++ b/mysql-test/suite/versioning/t/optimized.test @@ -0,0 +1,40 @@ +create table t ( + a int, + b int without system versioning +) with system versioning; + +insert into t values(1, 2); +insert into t values(3, 4); +select * from t; +select a from t for system_time as of timestamp now(6); +select a, b, b+0 from t for system_time as of timestamp now(6); +select * from t for system_time as of timestamp now(6); +select count(*) from t for system_time as of timestamp now(6) group by b; +select * from t for system_time as of timestamp now(6) order by b asc; +select * from t for system_time as of timestamp now(6) order by b desc; +select * from t for system_time as of timestamp now(6) group by a having a=2; +select * from t for system_time as of timestamp now(6) group by b having b=2; +select a from t for system_time as of timestamp now(6) where b=2; +select a from t for system_time as of timestamp now(6) where b=NULL; +select a from t for system_time as of timestamp now(6) where b is NULL; +select count(*), b from t for system_time as of timestamp now(6) group by b having b=NULL; +select a, b from t; + +create or replace table t ( + a int, + b int not null without system versioning +) with system versioning; + +insert into t values (1, 2), (3, 4); + +select * from t for system_time as of timestamp now(6); +select * from t for system_time as of timestamp now(6) where b is NULL; + +# +# MDEV-15062 Information Schema COLUMNS Table does not show system versioning information +# +create or replace table t (x int with system versioning, y int); +select column_name, extra from information_schema.columns where table_name='t'; +show create table t; + +drop table t; diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test new file mode 100644 index 00000000000..957fddc730d --- /dev/null +++ b/mysql-test/suite/versioning/t/partition.test @@ -0,0 +1,657 @@ +-- source include/have_partition.inc +-- source suite/versioning/common.inc +-- source suite/versioning/engines.inc + +set system_versioning_alter_history=keep; +--echo # Check conventional partitioning on temporal tables + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + x int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time(row_start, row_end)) +with system versioning +partition by range columns (x) ( + partition p0 values less than (100), + partition p1 values less than (1000)); + +insert into t1 values (3), (300); +select * from t1; +select * from t1 partition (p0); +select * from t1 partition (p1); + +delete from t1; +select * from t1; +select * from t1 partition (p0); +select * from t1 partition (p1); +select * from t1 for system_time all; +select * from t1 partition (p0) for system_time all; +select * from t1 partition (p1) for system_time all; + +--echo # Engine change native <-> non-native versioning prohibited +--replace_result $sys_datatype_expl SYS_DATATYPE $default_engine DEFAULT_ENGINE +eval create or replace table t1 ( + i int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time(row_start, row_end)) +engine=$default_engine +with system versioning partition by hash(i); +--replace_result $non_default_engine NON_DEFAULT_ENGINE +--error ER_VERS_ALTER_ENGINE_PROHIBITED +eval alter table t1 engine=$non_default_engine; + + +--echo ## CREATE TABLE + +--error ER_VERS_NOT_VERSIONED +create or replace table t1 (x int) +partition by system_time ( + partition p0 history, + partition pn current); + +create or replace table t1 (x int); +--error ER_VERS_NOT_VERSIONED +alter table t1 +partition by system_time ( + partition p0 history, + partition pn current); + +--error ER_VERS_WRONG_PARTS +create or replace table t1 (x int) +with system versioning +partition by system_time ( + partition p0 current); + +--error ER_VERS_WRONG_PARTS +create or replace table t1 (x int) +with system versioning +partition by system_time ( + partition p0 current, + partition p1 current); + +--error ER_VERS_WRONG_PARTS +create or replace table t1 (x int) +with system versioning +partition by system_time ( + partition p0 history, + partition p1 history); + +--error ER_VERS_WRONG_PARTS +create or replace table t1 (x int) +with system versioning +partition by system_time ( + partition pn current, + partition p0 history); + +--error ER_VERS_WRONG_PARTS +create or replace table t1 (x int) +with system versioning +partition by system_time ( + partition p0, + partition pn current); + +create or replace table t1 (x int) +with system versioning +partition by system_time ( + partition p0 history, + partition pn current); + + +--echo ## ALTER TABLE + +--error ER_VERS_WRONG_PARTS +alter table t1 add partition ( + partition p1 current); + +alter table t1 add partition ( + partition p1 history); + +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +insert into t1 values (1), (2); + +--error ER_VERS_WRONG_PARTS +alter table t1 drop partition pn; +alter table t1 drop partition p1; +--error ER_VERS_WRONG_PARTS +alter table t1 drop partition p0; + +select x from t1; + +--echo # Bug #260: incorrect IB partitioning warning +create or replace table t1 (x int) +with system versioning +partition by system_time limit 1 ( + partition p0 history, + partition pn current); +alter table t1 change x big int; + +create or replace table t1 (i int) engine myisam partition by hash(i) partitions 2; +--error ER_PARTITION_WRONG_TYPE +alter table t1 add partition (partition px history); + + +--echo ## INSERT, UPDATE, DELETE +create or replace table t1 (x int) +with system versioning +partition by system_time ( + partition p0 history, + partition pn current); + +set @now= now(6); +insert into t1 values (1); +set @str= concat('select x, row_start < @now as A, row_end > @now as B from t1 partition (p0)'); +prepare select_p0 from @str; +set @str= concat('select x, row_start > @now as C, row_end = timestamp\'2038-01-19 03:14:07.999999\' as D from t1 partition (pn)'); +prepare select_pn from @str; + +execute select_p0; +execute select_pn; + +set @str= concat('select row_start from t1 partition (pn) into @ts0'); +prepare stmt from @str; execute stmt; drop prepare stmt; + +--source suite/versioning/wait_system_clock.inc + +set @now= now(6); +delete from t1; +execute select_p0; +execute select_pn; + +set @str= concat('select row_start from t1 partition (p0) into @ts1'); +prepare stmt from @str; execute stmt; drop prepare stmt; + +select @ts0 = @ts1; + +set @now= now(6); +insert into t1 values (2); + +--source suite/versioning/wait_system_clock.inc + +execute select_p0; +execute select_pn; + +set @str= concat('select row_start from t1 partition (pn) into @ts0'); +prepare stmt from @str; execute stmt; drop prepare stmt; + +set @now= now(6); +update t1 set x = x + 1; + +--source suite/versioning/wait_system_clock.inc + +execute select_p0; +execute select_pn; + +drop prepare select_p0; +drop prepare select_pn; + +set @str= concat('select row_start from t1 partition (p0) where x = 2 into @ts1'); +prepare stmt from @str; execute stmt; drop prepare stmt; +set @str= concat('select row_end from t1 partition (p0) where x = 2 into @ts2'); +prepare stmt from @str; execute stmt; drop prepare stmt; +set @str= concat('select row_start from t1 partition (pn) into @ts3'); +prepare stmt from @str; execute stmt; drop prepare stmt; + +select @ts0 = @ts1; +select @ts2 = @ts3; + +--echo ## rotation by LIMIT +--error ER_PART_WRONG_VALUE +create or replace table t1 (x int) +with system versioning +partition by system_time limit 0 ( + partition p0 history, + partition p1 history, + partition pn current); + +create or replace table t1 (x int) +with system versioning +partition by system_time limit 2 ( + partition p0 history, + partition p1 history, + partition pn current); + +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +--error ER_DROP_PARTITION_NON_EXISTENT +alter table t1 drop partition non_existent; + +insert into t1 values (1), (2), (3), (4), (5), (6); +select * from t1 partition (pn); +delete from t1 where x < 4; +delete from t1; +select * from t1 partition (p0); +select * from t1 partition (p1); + +insert into t1 values (7), (8); +--echo ### warn about full partition +delete from t1; +select * from t1 partition (p1) order by x; + +--echo ### Assertion in ALTER on warning from partitioning LIMIT [#446] +create or replace table t1 (x int) with system versioning; +insert into t1 values (1), (2); +delete from t1; +alter table t1 partition by system_time limit 1 ( + partition p1 history, + partition pn current); + +--echo ## rotation by INTERVAL +--error ER_PART_WRONG_VALUE +create or replace table t1 (x int) +with system versioning +partition by system_time interval 0 second ( + partition p0 history, + partition p1 history, + partition pn current); + +--error ER_PARSE_ERROR +create or replace table t1 (x int) +with system versioning +partition by system_time interval 1 second starts 12345 ( + partition p0 history, + partition p1 history, + partition pn current); + +--error ER_PARSE_ERROR +create table t1 (i int) with system versioning + partition by system_time interval 6 day limit 98 + (partition p0 history, partition ver_pn current); + +--echo ## Subpartitions +create or replace table t1 (x int) +with system versioning +partition by system_time limit 2 +subpartition by key (x) +subpartitions 2 ( + partition p0 history, + partition p1 history, + partition pn current); + +insert into t1 (x) values (1), (2), (3), (4), (5); +select * from t1 partition (pnsp0); +select * from t1 partition (pnsp1); + +--echo ### warn about full partition +delete from t1 where x < 3; +delete from t1; +delete from t1; +select * from t1 partition (p0sp0); +select * from t1 partition (p0sp1); +select * from t1 partition (p1sp0); +select * from t1 partition (p1sp1); + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + a bigint, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time(row_start, row_end)) +with system versioning +partition by range (a) +(partition p0 values less than (20) engine innodb, + partition p1 values less than maxvalue engine innodb); +insert into t1 values (1); + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + f_int1 integer default 0, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time(row_start, row_end) +) with system versioning +partition by range(f_int1) +subpartition by hash(f_int1) +( partition part1 values less than (1000) +(subpartition subpart11 storage engine = 'innodb', +subpartition subpart12 storage engine = 'innodb')); +insert into t1 values (1); + +create or replace table t1 (i int) engine=innodb partition by key(i); +--replace_result $sys_datatype_expl SYS_DATATYPE +eval alter table t1 + add column row_start $sys_datatype_expl as row start invisible, + add column row_end $sys_datatype_expl as row end invisible, + add period for system_time(row_start, row_end), + add system versioning; +insert into t1 values(); + +--echo # MDEV-14722 Assertion in ha_commit_trans for sub-statement +create or replace table t1 (i int) with system versioning +partition by system_time interval 1 day ( + partition p1 history, + partition pc current); +create or replace table t2 (f int); +create or replace trigger tr before insert on t2 +for each row select table_rows from information_schema.tables +where table_name = 't1' into @a; +insert into t2 values (1); + +--echo # MDEV-14740 Locking assertion for system_time partitioning +create or replace table t1 (i int) with system versioning +partition by system_time interval 1 week ( + partition p1 history, + partition pn current); +create or replace table t2 (f int); +create or replace trigger tr before insert on t2 +for each row select count(*) from t1 into @a; +insert into t2 values (1); + +--echo # MDEV-14741 Assertion `(trx)->start_file == 0' failed in row_truncate_table_for_mysql() +create or replace table t1 (i int) with system versioning +partition by system_time interval 1 hour ( + partition p1 history, + partition pn current); +set autocommit= off; +truncate table t1; +set autocommit= on; + +--echo # MDEV-14747 ALTER PARTITION BY SYSTEM_TIME after LOCK TABLES +create or replace table t1 (x int) with system versioning; +lock table t1 write; +alter table t1 partition by system_time interval 1 week ( + partition p1 history, + partition pn current); +unlock tables; + +--echo # MDEV-14748 Assertion in ha_myisammrg::attach_children() +create or replace table t1 (x int) engine=myisam with system versioning + partition by system_time interval 1 month (partition p1 history, partition pn current); +create or replace table t2 (x int) engine=myisam; +create or replace table t3 (x int) engine=merge union=(t2); +create or replace table t4 (x int) engine=myisam; +create or replace trigger tr after insert on t4 for each row insert into t2 + ( select x from t3 ) union ( select x from t1 ); +insert into t4 values (1); + +--echo # MDEV-14821 Assertion failure +create or replace table t1 (x int) with system versioning; +insert into t1 values (0), (1); +update t1 set x= x + 1; +alter table t1 partition by system_time limit 1 ( + partition p1 history, + partition p2 history, + partition pn current); +delete from t1 where x = 1; +delete from t1 where x = 2; + +--echo # MDEV-14923 Assertion upon INSERT into locked versioned partitioned table +create or replace table t1 (x int) with system versioning +partition by system_time (partition p1 history, partition pn current); +lock table t1 write; +--error ER_SAME_NAME_PARTITION +alter table t1 add partition (partition p1 history); +insert into t1 values (1); +unlock tables; + +--error ER_DATA_OUT_OF_RANGE +create or replace table t1 (pk int) with system versioning +partition by system_time interval 10 year ( + partition p1 history, + partition p2 history, + partition pn current +); + +# INTERVAL and ALTER TABLE +create or replace table t1 (i int) with system versioning +partition by system_time interval 1 hour ( + partition p0 history, partition pn current); + +set @ts=(select partition_description from information_schema.partitions + where table_schema='test' and table_name='t1' and partition_name='p0'); + +alter table t1 add column b int; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +alter table t1 add partition (partition p1 history, partition p2 history); +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +alter table t1 drop partition p0; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +--error ER_VERS_DROP_PARTITION_INTERVAL +alter table t1 drop partition p2; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; + +--echo # +--echo # MDEV-15103 Assertion in ha_partition::part_records() for updating VIEW +--echo # +create or replace table t1 (pk int primary key, f int) with system versioning +partition by system_time limit 100 (partition p1 history, partition pn current); +insert into t1 values (1,10), (2,20); +create or replace view v1 as select * from t1; +update v1 set f= 30; + +--echo # +--echo # MDEV-15168 Unexpected ER_VERS_ENGINE_UNSUPPORTED upon dropping versioning on a partitioned table +--echo # +create or replace table t (a int) with system versioning + partition by system_time (partition p1 history, partition pn current); +--error ER_DROP_VERSIONING_SYSTEM_TIME_PARTITION +alter table t drop system versioning; + +--echo # MDEV-15191 Assertion `bit < (map)->n_bits' failed in bitmap_is_set upon INSERT +create or replace table t1 (i int) with system versioning; +insert into t1 values (1), (2); +update t1 set i= 3; +alter table t1 partition by system_time interval 1 month (partition p1 history, partition pn current); +lock table t1 write; +alter table t1 add partition (partition p2 history); +insert into t1 values (4); +unlock tables; + +--echo # MDEV-15036 Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' in Diagnostics_area::set_ok_status or unexpected ER_RANGE_NOT_INCREASING_ERROR +create or replace table t1 (a int) with system versioning +partition by system_time limit 2 ( + partition p1 history, partition p2 history, + partition p3 history, partition pn current); +insert into t1 values (1),(2),(3); +update t1 set a = 4; +delete from t1; +delete from t1 where a is not null; + +--echo # MDEV-14823 Wrong error message upon selecting from a system_time partition +create or replace table t1 (i int) with system versioning partition by system_time limit 10 (partition p0 history, partition pn current); +--error ER_VERS_QUERY_IN_PARTITION +select * from t1 partition (p0) for system_time all; +--echo # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED +create or replace procedure sp() +select * from t1 partition (p0) for system_time all; +--error ER_VERS_QUERY_IN_PARTITION +call sp; +--error ER_VERS_QUERY_IN_PARTITION +call sp; +drop procedure sp; + +--echo # MDEV-15380 Index for versioned table gets corrupt after partitioning and DELETE +create or replace table t1 (pk int primary key) + engine=myisam + with system versioning + partition by key() partitions 3; +set timestamp=1523466002.799571; +insert into t1 values (11),(12); +set timestamp=1523466004.169435; +delete from t1 where pk in (11, 12); +--echo Same test but for Aria storage engine +create or replace table t1 (pk int primary key) + engine=aria + with system versioning + partition by key() partitions 3; +set timestamp=1523466002.799571; +insert into t1 values (11),(12); +set timestamp=1523466004.169435; +delete from t1 where pk in (11, 12); + +--echo # +--echo # MDEV-18136 Server crashes in Item_func_dyncol_create::prepare_arguments +--echo # +create or replace table t1 (pk int) with system versioning +partition by system_time interval 7 second ( + partition ver_p1 history, + partition ver_pn current); +alter table t1 +partition by system_time interval column_get(column_create(7,7), 7 as int) second ( + partition ver_p1 history, + partition ver_pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +--echo # +--echo # MDEV-19175 Server crashes in ha_partition::vers_can_native upon INSERT DELAYED into versioned partitioned table +--echo # +create or replace table t1 (f int) with system versioning partition by hash(f); +# delayed works differently in embedded server +--error 0,ER_DELAYED_NOT_SUPPORTED +insert delayed into t1 values (1); + +--echo # +--echo # MDEV-20068 History partition rotation is not done under LOCK TABLES +--echo # +create or replace table t1 (x int) with system versioning partition by system_time limit 1 +(partition p1 history, partition pn current); +lock tables t1 write; +insert into t1 values (0), (1), (2), (3); +delete from t1 where x < 3; +delete from t1; +unlock tables; + +--echo # +--echo # MDEV-20336 Assertion bitmap_is_set(read_partitions) upon SELECT FOR UPDATE from versioned table +--echo # +create or replace table t1 (pk int primary key) with system versioning partition by system_time limit 100 (partition p1 history, partition pn current); +execute immediate 'select * from t1 for update'; + +drop view v1; +drop tables t, t1, t2, t3, t4; + +--echo # +--echo # MDEV-18957 UPDATE with LIMIT clause is wrong for versioned partitioned tables +--echo # +create or replace table t1 ( + x int, + a varchar(255) +) with system versioning partition by system_time (partition p1 history, partition pn current); + +insert into t1 (x) values (1), (2), (3), (4); +update t1 set a= 'foo' limit 3; +update t1 set a= 'bar' limit 4; +select * from t1; +drop table t1; + +--echo # +--echo # MDEV-21011 Table corruption reported for versioned partitioned table after DELETE: "Found a misplaced row" +--echo # +create table t1 (a int) with system versioning +partition by system_time limit 3 +(partition p1 history, partition p2 history, partition pn current); +insert into t1 values (1),(2),(3),(4); +delete from t1; +delete from t1; +check table t1; + +# cleanup +drop table t1; + +--echo # +--echo # MDEV-21233 Assertion `m_extra_cache' failed in ha_partition::late_extra_cache +--echo # +create table t1 (id int, a varchar(8)) with system versioning partition by key (id) partitions 2; +insert into t1 values (1,'foo'),(2,'bar'); + +create table t2 (b int); +insert into t2 values (1),(2); + +update t1, t2 set a = 1; + +# cleanup +drop table t1, t2; + +--echo # +--echo # MDEV-20515 multi-update tries to position updated table by null reference +--echo # +create or replace table t1 (a int); +insert into t1 values (0), (1); + +create or replace table t2 (b int) with system versioning +partition by system_time +(partition p1 history, partition pn current); + +insert into t2 values (0), (2); +update t1 left join t2 on a > b set b= 2 order by b; + +# cleanup +drop table t1, t2; + +--echo # +--echo # MDEV-17091 Assertion `old_part_id == m_last_part' failed in +--echo # ha_partition::update_row or `part_id == m_last_part' in +--echo # ha_partition::delete_row upon UPDATE/DELETE after dropping versioning +--echo # +create or replace table t1 (pk int primary key, f int) engine=innodb + with system versioning + partition by key() partitions 2; +insert into t1 values (1,10),(2,20); +--echo # expected to hit same partition +select * from t1 partition (p0); +alter table t1 drop system versioning; + +--echo # 1 and 2 are expected to be in different partitions +select * from t1 partition(p0); +select * from t1 partition(p1); + +update t1 set f=pk; +delete from t1; +drop table t1; + +--echo # +--echo # MDEV-22413 Server hangs upon UPDATE/DELETE on a view reading from versioned partitioned table +--echo # +create or replace table t1 (f char(6)) engine innodb with system versioning; + +insert into t1 values (null); +update t1 set f= 'foo'; +update t1 set f= 'bar'; + +create or replace view v1 as select * from t1 for system_time all; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +update v1 set f = ''; + +create or replace table t1 (f char(6)) engine innodb with system versioning +partition by system_time limit 1 +(partition p1 history, partition p2 history, partition pn current); + +insert into t1 values (null); +update t1 set f= 'foo'; +update t1 set f= 'bar'; + +create or replace view v1 as select * from t1 for system_time all; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +update v1 set f= ''; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +delete from v1; + +# cleanup +drop view v1; +drop table t1; + +--echo # +--echo # MDEV-22112 Assertion `tab_part_info->part_type == RANGE_PARTITION || tab_part_info->part_type == LIST_PARTITION' failed in prep_alter_part_table +--echo # + +### TMP: Please remove this error check in 10.5 (MDEV-19903) +--error ER_PARTITIONS_MUST_BE_DEFINED_ERROR +create table t1 (a int) with system versioning partition by system_time; +### TMP end + +create table t1 (a int) with system versioning partition by system_time +(partition p1 history, partition pn current); +--error ER_PARTITION_WRONG_TYPE +alter table t1 add partition (partition p2); + +# Cleanup +drop table t1; + +--echo # End of 10.3 tests + +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/partition_innodb.test b/mysql-test/suite/versioning/t/partition_innodb.test new file mode 100644 index 00000000000..29ec58af13c --- /dev/null +++ b/mysql-test/suite/versioning/t/partition_innodb.test @@ -0,0 +1,94 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--source suite/versioning/common.inc + +--echo # MDEV-15951 system versioning by trx id doesn't work with partitioning +--echo # currently trx_id does not support partitioning by system_time +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t1( + i int, + row_start bigint unsigned generated always as row start, + row_end bigint unsigned generated always as row end, + period for system_time(row_start, row_end) +) engine=InnoDB with system versioning partition by system_time ( + partition p0 history, + partition pn current +); + +create or replace table t1( + i int, + row_start bigint unsigned generated always as row start, + row_end bigint unsigned generated always as row end, + period for system_time(row_start, row_end) +) engine=InnoDB with system versioning; + +--replace_regex /#sql-[0-9a-f_]*/#sql-temporary/ +--error ER_VERS_FIELD_WRONG_TYPE +alter table t1 partition by system_time ( + partition p0 history, + partition pn current +); + +drop table t1; + +--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED +create or replace table t ( + a int primary key, + row_start bigint unsigned as row start invisible, + row_end bigint unsigned as row end invisible, + period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by key() ( + partition p1, + partition p2 +); + +--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED +create or replace table t ( + a int primary key, + row_start bigint unsigned as row start invisible, + row_end bigint unsigned as row end invisible, + period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by key(a, row_start) ( + partition p1, + partition p2 +); + +--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED +create or replace table t ( + a int primary key, + row_start bigint unsigned as row start invisible, + row_end bigint unsigned as row end invisible, + period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by hash(a + row_end * 2) ( + partition p1, + partition p2 +); + +--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED +create or replace table t ( + a int primary key, + row_start bigint unsigned as row start invisible, + row_end bigint unsigned as row end invisible, + period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by range columns (a, row_start) ( + partition p1 values less than (100, 100) +); + +--echo # +--echo # MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table +--echo # +create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i)) +engine=innodb with system versioning partition by key() partitions 2; +insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b'); +alter table t1 drop system versioning; +replace into t1 select * from t1; +select * from t1 where i > 0 or pk = 1000 limit 1; +drop table t1; + +--echo # End of 10.3 tests + +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/partition_rotation.test b/mysql-test/suite/versioning/t/partition_rotation.test new file mode 100644 index 00000000000..4937a2a069b --- /dev/null +++ b/mysql-test/suite/versioning/t/partition_rotation.test @@ -0,0 +1,40 @@ +--source include/have_partition.inc + +# +# partition rotation +# +set timestamp=unix_timestamp('2001-02-03 10:20:30'); +create or replace table t1 (i int) with system versioning +partition by system_time interval 1 day + subpartition by key (i) subpartitions 2 + (partition p1 history, partition pn current); +set timestamp=unix_timestamp('2001-02-03 10:20:40'); +insert t1 values (1); delete from t1; +set timestamp=unix_timestamp('2001-02-04 10:20:50'); +insert t1 values (2); delete from t1; + +select subpartition_name,partition_description,table_rows from information_schema.partitions where table_schema='test' and table_name='t1'; + +set timestamp=unix_timestamp('2001-02-04 10:20:55'); +alter table t1 add partition (partition p0 history, partition p2 history); +set timestamp=unix_timestamp('2001-02-04 10:30:00'); +insert t1 values (4),(5); +set timestamp=unix_timestamp('2001-02-04 10:30:10'); +update t1 set i=6 where i=5; + +select subpartition_name,partition_description,table_rows from information_schema.partitions where table_schema='test' and table_name='t1'; + +--echo ## pruning check +set @ts=(select partition_description from information_schema.partitions + where table_schema='test' and table_name='t1' and partition_name='p0' limit 1); +--sorted_result +select * from t1; +explain partitions select * from t1; +--replace_column 10 # +explain partitions select * from t1 for system_time as of '2001-02-04 10:20:30'; +set @ts=(select row_end from t1 for system_time all where i=1); +select * from t1 for system_time all where row_end = @ts; +--replace_column 5 # 10 # 11 # +explain partitions select * from t1 for system_time all where row_end = @ts; + +drop table t1; diff --git a/mysql-test/suite/versioning/t/replace.test b/mysql-test/suite/versioning/t/replace.test new file mode 100644 index 00000000000..392c0ffcf35 --- /dev/null +++ b/mysql-test/suite/versioning/t/replace.test @@ -0,0 +1,62 @@ +--source suite/versioning/key_type.inc +if ($MTR_COMBINATION_SEC) +{ + --skip pk or unique only +} +--source suite/versioning/common.inc +--source suite/versioning/engines.inc + +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE +eval create or replace table t( + id int, + $KEY_TYPE(id), + x int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time(row_start, row_end) +) with system versioning; + +insert t values (1, 2); +replace t values (1, 3); +select *, current_row(row_end) as current from t for system_time all order by x; +drop table t; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create table t ( + id int unique, + x int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; + +insert t values (1, 2); +replace t values (1, 3); +select *, current_row(row_end) as current from t for system_time all order by x; +drop table t; + +--echo # MDEV-15645 Assertion `table->insert_values' failed in write_record upon REPLACE into a view with underlying versioned table +create or replace table t1 (a int, b int, primary key (a), unique(b)) with system versioning; +insert into t1 values (1,1); +create or replace table t2 (c int); +create or replace view v as select t1.* from t1 join t2; +replace into v (a, b) select a, b from t1; +drop view v; +drop tables t1, t2; + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval CREATE TABLE t1 ( + pk INT AUTO_INCREMENT, + f INT, + row_start $sys_datatype_expl AS ROW START INVISIBLE, + row_end $sys_datatype_expl AS ROW END INVISIBLE, + PRIMARY KEY(pk), + UNIQUE(f), + PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING; +INSERT INTO t1 () VALUES (),(),(),(),(),(); +UPDATE IGNORE t1 SET f = 1; +REPLACE t1 SELECT * FROM t1; +DROP TABLE t1; + +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/rpl.test b/mysql-test/suite/versioning/t/rpl.test new file mode 100644 index 00000000000..b5be68feece --- /dev/null +++ b/mysql-test/suite/versioning/t/rpl.test @@ -0,0 +1,136 @@ +--source suite/versioning/engines.inc +--source include/have_partition.inc +--source include/master-slave.inc + +#BUG#12662190 - COM_COMMIT IS NOT INCREMENTED FROM THE BINARY LOGS ON SLAVE, COM_BEGIN IS +#Testing command counters -BEFORE. +#Storing the before counts of Slave +connection slave; +let $slave_com_commit_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_commit', Value, 1); +let $slave_com_insert_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_insert', Value, 1); +let $slave_com_delete_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_delete', Value, 1); +let $slave_com_update_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_update', Value, 1); + +connection master; +CREATE TABLE t1 (x int) with system versioning; +insert into t1 values (1); +select * from t1 order by x; +delete from t1; +select * from t1 order by x; +select * from t1 for system_time all order by row_end, x; +sync_slave_with_master; +select * from t1 order by x; +select * from t1 for system_time all order by row_end, x; + +connection master; +insert into t1 values (2); +sync_slave_with_master; +select * from t1 order by x; + +connection master; +update t1 set x = 3; +sync_slave_with_master; +select * from t1 order by x; +select * from t1 for system_time all order by row_end, x; + +--echo # check unversioned -> versioned replication +connection master; +create or replace table t1 (x int primary key); +sync_slave_with_master; +alter table t1 with system versioning; + +connection master; +insert into t1 values (1); +sync_slave_with_master; +select * from t1 order by x; +select * from t1 for system_time all order by row_end, x; + +connection master; +update t1 set x= 2 where x = 1; +sync_slave_with_master; +select * from t1 order by x; +select * from t1 for system_time all order by row_end, x; + +connection master; +delete from t1; +sync_slave_with_master; +select * from t1 order by x; +select * from t1 for system_time all order by row_end, x; + +--echo # same thing (UPDATE, DELETE), but without PK +connection master; +create or replace table t1 (x int); +sync_slave_with_master; +alter table t1 with system versioning; + +connection master; +insert into t1 values (1); +update t1 set x= 2 where x = 1; +sync_slave_with_master; +select * from t1 order by x; +select * from t1 for system_time all order by row_end, x; + +connection master; +delete from t1; +sync_slave_with_master; +select * from t1 order by x; +select * from t1 for system_time all order by row_end, x; + +--echo # multi-update +connection master; +create or replace table t1 (x int) with system versioning; +create or replace table t2 (x int) with system versioning; +insert into t1 values (1); +insert into t2 values (2); +update t1, t2 set t1.x=11, t2.x=22; +sync_slave_with_master; +select * from t1 order by x; +select * from t2 order by x; +select * from t1 for system_time all order by row_end, x; +select * from t2 for system_time all order by row_end, x; + +--echo # MDEV-14767 system_versioning_alter_history breaks ALTER replication +--echo ## Case 1: KEEP on the master, ALTER will work on the slave +connection master; +create or replace table t1 (a int) with system versioning; +set system_versioning_alter_history= KEEP; +alter table t1 add column b int; +sync_slave_with_master; +--replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE +show create table t1; + +--echo ## Case 2: ERROR on the master, it'll fail on the master, the slave won't see it +connection master; +set system_versioning_alter_history= ERROR; +--error ER_VERS_ALTER_NOT_ALLOWED +alter table t1 drop column b; +sync_slave_with_master; +--replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE +show create table t1; + +--echo ## Case 3: table is not versioned on the master, ALTER will work on the slave +connection master; +create or replace table t1 (a int); +sync_slave_with_master; +create or replace table t1 (a int) with system versioning; +connection master; +alter table t1 add column b int; +sync_slave_with_master; +--replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE +show create table t1; + +connection master; +drop table t1, t2; + +# +# MDEV-15395 Wrong result or Assertion `old_part_id == m_last_part' failed in ha_partition::update_row on slave +# +create table t1 (i int) with system versioning partition by system_time limit 8 ( partition p1 history, partition p2 history, partition pn current ); +insert into t1 values (1); +update t1 set i = 1; +update t1 set i = 0; +sync_slave_with_master; +connection master; +drop table t1; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/versioning/t/rpl_mix.test b/mysql-test/suite/versioning/t/rpl_mix.test new file mode 100644 index 00000000000..64025c74625 --- /dev/null +++ b/mysql-test/suite/versioning/t/rpl_mix.test @@ -0,0 +1,19 @@ +--source include/have_binlog_format_mixed.inc +--source include/master-slave.inc + +# +# MDEV-15405 Mixed replication fails with "Could not execute Delete_rows_v1 event" upon DELETE HISTORY +# + +CREATE TABLE t1 (pk INT PRIMARY KEY, i INT) WITH SYSTEM VERSIONING; +INSERT INTO t1 VALUES (1,10),(2,20); +UPDATE t1 SET i = 100; +# This is to imitiate any real-life situation which causes a switch to row format +SET BINLOG_FORMAT= ROW; +DELETE HISTORY FROM t1; +--sync_slave_with_master + +connection master; +drop table t1; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/versioning/t/rpl_row.test b/mysql-test/suite/versioning/t/rpl_row.test new file mode 100644 index 00000000000..17ce2dfdcf8 --- /dev/null +++ b/mysql-test/suite/versioning/t/rpl_row.test @@ -0,0 +1,18 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--echo # MDEV-16252: MINIMAL binlog_row_image does not work for versioned tables +set @old_row_image= @@binlog_row_image; +set binlog_row_image= minimal; + +create or replace table t1 (pk int, i int, primary key(pk)) +with system versioning; +insert into t1 values (1,10),(2,20); +update t1 set i = 0; + +--sync_slave_with_master +--connection master +drop table t1; +set binlog_row_image= @old_row_image; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/versioning/t/select.test b/mysql-test/suite/versioning/t/select.test new file mode 100644 index 00000000000..c70f2714fff --- /dev/null +++ b/mysql-test/suite/versioning/t/select.test @@ -0,0 +1,427 @@ +--source suite/versioning/engines.inc +--source suite/versioning/common.inc +--source include/default_optimizer_switch.inc + +# test_01 + +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + x int unsigned, + y int unsigned, + sys_trx_start $sys_datatype_expl as row start invisible, + sys_trx_end $sys_datatype_expl as row end invisible, + period for system_time (sys_trx_start, sys_trx_end) +) with system versioning; + +insert into t1 (x, y) values + (0, 100), + (1, 101), + (2, 102), + (3, 103), + (4, 104), + (5, 105), + (6, 106), + (7, 107), + (8, 108), + (9, 109); + +set @t0= now(6); +if ($MTR_COMBINATION_TRX_ID) +{ +--disable_query_log + select sys_trx_start from t1 limit 1 into @x0; +--enable_query_log +} + +delete from t1 where x = 3; +delete from t1 where x > 7; + +insert into t1(x, y) values(3, 33); +select sys_trx_start from t1 where x = 3 and y = 33 into @t1; +if ($MTR_COMBINATION_TRX_ID) +{ +--disable_query_log + set @x1= @t1; + select trt_commit_ts(@x1) into @t1; +--enable_query_log +} + +select x, y from t1; +select x as ASOF_x, y from t1 for system_time as of timestamp @t0; +select x as FROMTO_x, y from t1 for system_time from timestamp '1970-01-01 00:00:00' to timestamp @t1; +select x as BETWAND_x, y from t1 for system_time between timestamp '1970-01-01 00:00:00' and timestamp @t1; +select x as ALL_x, y from t1 for system_time all; + +--disable_query_log +if ($MTR_COMBINATION_TRX_ID) +{ + select x as ASOF2_x, y from t1 for system_time as of @x0; + select x as FROMTO2_x, y from t1 for system_time from @x0 to @x1; + select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1; +} +if ($MTR_COMBINATION_TIMESTAMP) +{ + select x as ASOF2_x, y from t1 for system_time as of @t0; + select x as FROMTO2_x, y from t1 for system_time from timestamp '1970-01-01 00:00:00' to timestamp @t1; + select x as BETWAND2_x, y from t1 for system_time between timestamp '1970-01-01 00:00:00' and timestamp @t1; +} +--enable_query_log + +# test_02 + +create or replace table t1 ( + x int unsigned, + y int unsigned +) with system versioning; +create or replace table t2 ( + x int unsigned, + y int unsigned +) with system versioning; + +insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5); +insert into t2 values (1, 2), (2, 1), (3, 1); +set @t0= now(6); + +select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x; +select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x; +select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x; + +delete from t1; +delete from t2; + +#384 +explain extended select * from (select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x) +for system_time as of timestamp @t0 as t; +explain extended select * from (select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) +for system_time as of timestamp @t0 as t; +#383 +explain extended select * from (select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x) +for system_time as of timestamp @t0 as t; + +select * from (select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x) +for system_time as of timestamp @t0 as t; +select * from (select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) +for system_time as of timestamp @t0 as t; +select * from (select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x) +for system_time as of timestamp @t0 as t; + +drop table t1; +drop table t2; + +# Wildcard expansion on hidden fields + +create table t1( + A int +) with system versioning; +insert into t1 values(1); +select * from t1; + +create or replace table t1 (x int); +insert into t1 values (1); +--error ER_VERS_NOT_VERSIONED +select * from t1 for system_time all; + +create or replace table t1 (x int) with system versioning; +insert into t1 values (1); +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +select * from t1 for system_time all for update; + +create or replace table t1 (a int not null auto_increment primary key) with system versioning; +select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; + +create or replace table t1 (a int) with system versioning; +create or replace table t2 (a int) with system versioning; +insert into t1 values(1); +insert into t2 values(1); +create view v1 as select * from t2 inner join t1 using (a); +select * from v1; +drop view v1; + +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +create view vt1 as select a from t1; +select * from t1 natural join vt1; +drop view vt1; + +create or replace table t1(x int) with system versioning; +select * from (t1 as r left join t1 as u using (x)), t1; + +# @end should be max +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +create trigger read_end after update on t1 + for each row set @end = old.row_end; +update t1 set a=2; +--replace_result 18446744073709551615 MAX_RESULT "2038-01-19 03:14:07.999999" MAX_RESULT +select @end; + +create or replace table t1 (a int) with system versioning; +create or replace table t2 (b int) with system versioning; +insert into t1 values (1); +insert into t2 values (2); +select * from (select * from t1 cross join t2) as tmp; +select * from (select * from (select * from t1 cross join t2) as tmp1) as tmp2; +select * from (select * from t1 cross join t2 for system_time as of timestamp ('1970-01-01 00:00:00')) as tmp; + +create or replace table t1(a1 int) with system versioning; +create or replace table t2(a2 int) with system versioning; +insert into t1 values(1),(2); +insert into t2 values(1),(2); +select * from t1 for system_time all natural left join t2 for system_time all; + +# natural join of a view and table +create or replace table t1(a1 int) with system versioning; +create or replace table t2(a2 int) with system versioning; +insert into t1 values(1),(2); +insert into t2 values(1),(2); +create or replace view v1 as select a1 from t1; + +select * from v1 natural join t2; +select * from v1 natural left join t2; +select * from v1 natural right join t2; + +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +insert into t1 values (2); +insert into t1 values (3); +explain extended +select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; +select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; + +create or replace table t1 (x int) with system versioning; +create or replace table t2 (y int) with system versioning; +insert into t1 values (1), (2), (3); +delete from t1 where x = 3; +insert into t2 values (1); +select * from t1, t2 for system_time all; + +--error ER_VERS_NOT_VERSIONED +select * from (select * from t1 for system_time all, t2 for system_time all) +for system_time all as t; + +--echo # TRANSACTION/TIMESTAMP specifier in SYSTEM_TIME [MDEV-14645, Issue #396] +create or replace table t1 (x int) with system versioning engine myisam; +--error ER_VERS_ENGINE_UNSUPPORTED +select * from t1 for system_time as of transaction 1; +--echo # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED +create or replace procedure sp() +select * from t1 for system_time as of transaction 1; +--error ER_VERS_ENGINE_UNSUPPORTED +call sp; +--error ER_VERS_ENGINE_UNSUPPORTED +call sp; +create or replace table t1 (a int); +create or replace procedure sp() +select * from t1 for system_time all; +--error ER_VERS_NOT_VERSIONED +call sp; +--error ER_VERS_NOT_VERSIONED +call sp; +drop procedure sp; + +create or replace table t1 ( + x int, + sys_trx_start bigint unsigned as row start invisible, + sys_trx_end bigint unsigned as row end invisible, + period for system_time (sys_trx_start, sys_trx_end) +) with system versioning engine innodb; +insert into t1 values (1); +set @ts= now(6); +delete from t1; +select sys_trx_start from t1 for system_time all into @trx_start; + +--echo ## ensure @trx_start is much lower than unix timestamp +select @trx_start < unix_timestamp(@ts) - 100 as trx_start_good; + +--echo ## TIMESTAMP specifier +select x from t1 for system_time as of timestamp @ts; + +set @ts= timestamp'1-1-1 0:0:0'; + +select x from t1 for system_time as of timestamp @ts; + +--echo ## TRANSACTION specifier +select x from t1 for system_time as of transaction @trx_start; + +--echo ## no specifier (auto-detection) +select x from t1 for system_time as of @ts; +select x from t1 for system_time as of @trx_start; + +--echo ### Issue #365, bug 4 (related to #226, optimized fields) +create or replace table t1 (i int, b int) with system versioning; +insert into t1 values (0, 0), (0, 0); +select min(i) over (partition by b) as f +from (select i + 0 as i, b from t1) as tt +order by i; + +--echo ### Issue #365, bug 5 (dangling AND) +create or replace table t1 (a int); +create or replace table t2 (b int) with system versioning; +select * from t1 +where exists (select 1 from t2 where t2.b = t1.a and t2.b = t1.a); + +--echo ### Issue #365, bug 9 (not a derived subquery) +create or replace table t1 (x int) with system versioning; +select t1.x in (select x from t1) a from t1, (select x from t1) b; + +--echo ### Issue #365, bug 10 (WHERE cond freed prematurely for PS) +create or replace table t1 (x int) with system versioning; +insert into t1 values (1); +create or replace view v1 as select x from t1 where x = 1; +prepare stmt from " +select x from t1 where x in (select x from v1);"; +execute stmt; +execute stmt; + +--echo ### Issue #365, bug 11 (WHERE cond not top level item) +create or replace table t1 (a int, b int, key idx(a)) with system versioning; +insert into t1 values (1, 1), (2, 2); +select * from t1 where (a, 2) in ((1, 1), (2, 2)) and b = 1; + +--echo ### Issue #398, NOW is now non-magic +create or replace table t1 (x int) with system versioning; +select * from t1 for system_time as of current_timestamp; +--error ER_BAD_FIELD_ERROR +select * from t1 for system_time as of now; + +--echo ### Issue #405, NATURAL JOIN failure +create or replace table t1 (a int) with system versioning; +create or replace table t2 (b int); +create or replace view v1 as select a, row_start, row_end from t1 where a > round(rand()*1000); +select * from v1 natural join t2; + +--echo # +--echo # Issue #406, MDEV-14633 Assertion on TRT read +--echo # +create or replace table t1 (pk int primary key, i int, t time, key (i)) with system versioning; +insert into t1 values (1, 10, '15:01:53'), (2, 20, '00:00:00'); +delete from t1; +--disable_warnings +select * from t1 where t = '00:00:00' and i > 0 and row_end <> '2012-12-12 00:00:00'; +--enable_warnings + +--echo # +--echo # MDEV-14816 Assertion `join->best_read < double(1.797...e+308L)' failed in bool greedy_search +--echo # +create or replace table t1 (f1 int) with system versioning; +create or replace table t2 (f2 int) with system versioning; +create or replace table t3 (f3 int); +create or replace table t4 (f4 int); +insert into t1 values (1), (2), (3), (4); +insert into t2 values (1), (2), (3); +insert into t3 values (1), (2); +insert into t4 values (1); +select * from + t1 as t1a + left join t2 as t2a left join (t3 as t3a inner join t1) on t2a.f2 = t3a.f3 on t1a.f1 = t2a.f2 + left join (t2 join t3 inner join t4) on t2a.f2 = t1a.f1; + +--echo # +--echo # MDEV-15004 parser greedily parses AS OF TIMESTAMP +--echo # +--error ER_WRONG_VALUE +select timestamp'2016-02-30 08:07:06'; +--error ER_WRONG_VALUE +select * from t1 for system_time as of timestamp'2016-02-30 08:07:06'; +select timestamp('2003-12-31 12:00:00','12:00:00'); +select * from t1 for system_time as of timestamp('2003-12-31 12:00:00','12:00:00'); + + +--echo # +--echo # MDEV-15391 Server crashes in JOIN::fix_all_splittings_in_plan or Assertion `join->best_read < double(1.79...e+308L)' failed [tempesta-tech#475] +--echo # +create or replace table t1 (f1 int) with system versioning; +insert t1 values (1),(2); +create or replace table t2 (f2 int); +create or replace table t3 (f3 int); +create or replace table t4 (f4 int) with system versioning; +select f1 from t1 join t2 left join t3 left join t4 on f3 = f4 on f3 = f2; +insert t2 values (1),(2); +insert t3 values (1),(2); +insert t4 values (1),(2); +explain extended +select f1 from t1 join t2 left join t3 left join t4 on f3 = f4 on f3 = f2; + +drop view v1; +drop table t1, t2, t3, t4; + +--echo # +--echo # MDEV-15980 FOR SYSTEM_TIME BETWEEN and FROM .. TO work with negative intervals +--echo # +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + a int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; +insert into t1 values (1); +delete from t1; +select row_start from t1 for system_time all into @t1; +select row_end from t1 for system_time all into @t2; +select * from t1 for system_time between @t1 and @t2; +select * from t1 for system_time between @t2 and @t1; +select * from t1 for system_time from @t1 to @t2; +select * from t1 for system_time from @t2 to @t1; +drop table t1; + +--echo # +--echo # MDEV-15991 Server crashes in setup_on_expr upon calling SP or function executing DML on versioned tables +--echo # +create or replace table t1 (i int); +insert into t1 values (1); +--delimiter $ +create or replace procedure p(n int) +begin + select * from t1; +end $ +--delimiter ; +call p(1); +alter table t1 add system versioning; +call p(2); +call p(3); + +--echo # +--echo # MDEV-15947 ASAN heap-use-after-free in Item_ident::print or in my_strcasecmp_utf8 or unexpected ER_BAD_FIELD_ERROR upon call of stored procedure reading from versioned table +--echo # +create or replace table t1 (i int) with system versioning; +create or replace procedure p() select * from t1; +call p; +flush tables; +call p; +call p; +drop procedure p; +drop table t1; + +--echo # +--echo # MDEV-21234 Server crashes in in setup_on_expr upon 3rd execution of SP +--echo # +create table t1 (a varchar(8)); +insert into t1 values ('foo'),('bar'); +create table t2 (b date); + +create procedure pr() insert into t2 select * from t1; +--error ER_TRUNCATED_WRONG_VALUE +call pr; +prepare stmt from 'insert into t2 select * from t1'; +--error ER_TRUNCATED_WRONG_VALUE +execute stmt; +alter table t1 add system versioning; +--error ER_TRUNCATED_WRONG_VALUE +call pr; +--error ER_TRUNCATED_WRONG_VALUE +call pr; +--error ER_TRUNCATED_WRONG_VALUE +execute stmt; +--error ER_TRUNCATED_WRONG_VALUE +execute stmt; +drop prepare stmt; + +# cleanup +drop procedure pr; +drop table t1, t2; + + +call verify_trt_dummy(34); + +-- source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/select2.test b/mysql-test/suite/versioning/t/select2.test new file mode 100644 index 00000000000..7ede218cabf --- /dev/null +++ b/mysql-test/suite/versioning/t/select2.test @@ -0,0 +1,217 @@ +--source suite/versioning/engines.inc +--source suite/versioning/common.inc + +replace_result $default_engine ENGINE $sys_datatype_expl SYS_TYPE; +eval create table t1( + x int unsigned, + y int unsigned, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time (sys_start, sys_end)) +with system versioning engine=$default_engine; + +insert into t1 (x, y) values + (0, 100), + (1, 101), + (2, 102), + (3, 103), + (4, 104), + (5, 105), + (6, 106), + (7, 107), + (8, 108), + (9, 109); +set @t0= now(6); +select sys_start from t1 limit 1 into @x0; + +delete from t1 where x = 3; +delete from t1 where x > 7; + +insert into t1(x, y) values(3, 33); +select sys_start from t1 where x = 3 and y = 33 into @t1; +if($MTR_COMBINATION_TRX_ID) { + set @x1= @t1; + select trt_commit_ts(@x1) into @t1; +} + +select x, y from t1; +select x as ASOF_x, y from t1 for system_time as of timestamp @t0; +select x as FROMTO_x, y from t1 for system_time from '1970-01-01 00:00' to timestamp @t1; +select x as BETWAND_x, y from t1 for system_time between '1970-01-01 00:00' and timestamp @t1; +select x as ALL_x, y from t1 for system_time all; + +if($MTR_COMBINATION_TRX_ID) { + select x as ASOF2_x, y from t1 for system_time as of @x0; + select x as FROMTO2_x, y from t1 for system_time from @x0 to @x1; + select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1; +} +if(!$MTR_COMBINATION_TRX_ID) { + select x as ASOF2_x, y from t1 for system_time as of @t0; + select x as FROMTO2_x, y from t1 for system_time from '1970-01-01 00:00' to @t1; + select x as BETWAND2_x, y from t1 for system_time between timestamp '1970-01-01 00:00' and timestamp @t1; +} + +drop table t1; + +replace_result $default_engine ENGINE $sys_datatype_expl SYS_TYPE; +eval create table t1( + x int, + y int, + sys_start $sys_datatype_expl as row start invisible, + sys_end $sys_datatype_expl as row end invisible, + period for system_time (sys_start, sys_end)) +with system versioning engine=$default_engine; + +create table t2 like t1; + +insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5); +insert into t2 values (1, 2), (2, 1), (3, 1); +set @t0= now(6); + +select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x; +select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x; +select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x; + +delete from t1; +delete from t2; + +select IJ2_x1,y1,x2,y2 from (select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x) +for system_time as of timestamp @t0 as t; +select LJ2_x1,y1,x2,y2 from (select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) +for system_time as of timestamp @t0 as t; +select RJ2_x1,y1,x2,y2 from (select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x) +for system_time as of timestamp @t0 as t; + +drop table t1; +drop table t2; + +--echo # MDEV-14686 Server crashes in Item_field::used_tables on 2nd call of SP [#422] +create or replace table t1 (called int, bad int) with system versioning; +create or replace procedure bad() select * from t1 where bad in (select called from t1); +--disable_query_log +call bad; call bad; call bad; call bad; call bad; call bad; call bad; call bad; +drop procedure bad; +--enable_query_log +--echo # bad() is good. + +--echo # MDEV-14751 Server crashes in TABLE::versioned on 2nd execution of SP [#431] +create or replace table t1 (called_bad int); +create or replace table t2 (b int); +create or replace procedure bad() select * from t1 where ( 5, 6 ) in ( select b, b from t2 ) and called_bad in ( select max(b) from t2 ); +--disable_query_log +call bad; call bad; call bad; call bad; call bad; call bad; call bad; call bad; +drop procedure bad; +--enable_query_log +--echo # bad() is good. + +--echo # MDEV-14786 Server crashes in Item_cond::transform on 2nd execution of SP querying from a view [#436] +create or replace table t1 (called_bad int) with system versioning; +create or replace view v1 as select called_bad from t1 where called_bad < 5; +create or replace procedure bad() select called_bad from v1; +--disable_query_log +call bad; call bad; call bad; call bad; call bad; call bad; call bad; call bad; +drop procedure bad; +--enable_query_log +--echo # bad() is good. + +--echo # wildcard expansion on hidden fields. +create or replace table t1( + A int +) with system versioning; +insert into t1 values(1); +select * from t1; + +create or replace table t1 (x int); +insert into t1 values (1); +--error ER_VERS_NOT_VERSIONED +select * from t1 for system_time all; + +create or replace table t1 (x int) with system versioning; +insert into t1 values (1); +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +select * from t1 for system_time as of now() for update; + +create or replace table t1 (a int not null auto_increment primary key) with system versioning; +select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; + +create or replace table t1 (a int) with system versioning; +create or replace table t2 (a int) with system versioning; +insert into t1 values(1); +insert into t2 values(1); +create or replace view v1 as select * from t2 inner join t1 using (a); +select * from v1; +drop view v1; + +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +create view vt1 as select a from t1; +select * from t1 natural join vt1; +drop view vt1; + +create or replace table t1(x int) with system versioning; +select * from (t1 as r left join t1 as u using (x)), t1; + +# @end should be max +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +create trigger read_end after update on t1 + for each row set @end = old.row_end; +update t1 set a=2; +--replace_result 18446744073709551615 MAX_RESULT "2038-01-19 03:14:07.999999" MAX_RESULT +select @end; + +create or replace table t1 (a int) with system versioning; +create or replace table t2 (b int) with system versioning; +insert into t1 values (1); +insert into t2 values (2); +select * from (select * from t1 cross join t2) as tmp; +select * from (select * from (select * from t1 cross join t2) as tmp1) as tmp2; +select * from (select * from t1 cross join t2 for system_time as of timestamp ('1970-01-01 00:00')) as tmp; + +create or replace table t1(a1 int) with system versioning; +create or replace table t2(a2 int) with system versioning; +insert into t1 values(1),(2); +insert into t2 values(1),(2); +select * from t1 for system_time all natural left join t2 for system_time all; + +# natural join of a view and table +create or replace table t1(a1 int) with system versioning; +create or replace table t2(a2 int) with system versioning; +insert into t1 values(1),(2); +insert into t2 values(1),(2); +create or replace view v1 as select a1 from t1; + +select * from v1 natural join t2; +select * from v1 natural left join t2; +select * from v1 natural right join t2; + +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +insert into t1 values (2); +insert into t1 values (3); +select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; + +create or replace table t1 (x int) with system versioning; +create or replace table t2 (y int) with system versioning; +insert into t1 values (1), (2), (3); +delete from t1 where x = 3; +insert into t2 values (1); +select * from t1, t2 for system_time all; + +--error ER_VERS_NOT_VERSIONED +select * from (select * from t1 for system_time all, t2 for system_time all) for system_time all as t; +--error ER_PARSE_ERROR +select * from (t1 for system_time all join t2 for system_time all) for system_time all; + +--echo # MDEV-16043 Assertion thd->Item_change_list::is_empty() failed in mysql_parse upon SELECT from a view reading from a versioned table +create or replace table t1 (a int) with system versioning; +create or replace view v1 as select * from t1; +prepare stmt from "select * from t1 where exp( '20010609211642053929' )"; +--error ER_DATA_OUT_OF_RANGE +execute stmt; +select a from v1; + +drop view v1; +drop table t1, t2; + +-- source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/simple.test b/mysql-test/suite/versioning/t/simple.test new file mode 100644 index 00000000000..b550b3967d9 --- /dev/null +++ b/mysql-test/suite/versioning/t/simple.test @@ -0,0 +1,89 @@ +-- source include/have_innodb.inc +set default_storage_engine=innodb; +create or replace table dept ( + dept_id int(10) primary key, + name varchar(100) +) +with system versioning; + +create or replace table emp ( + emp_id int(10) primary key, + dept_id int(10), + name varchar(100), + salary int(10), + constraint `dept-emp-fk` + foreign key (dept_id) references dept (dept_id) + on delete restrict + on update restrict +) +with system versioning; + +select now() into @ts_0; + +insert into dept (dept_id, name) values (10, "accounting"); +commit; + +select row_start into @ts_1 from dept where dept_id=10; + +insert into emp (emp_id, name, salary, dept_id) values (1, "bill", 1000, 10); +commit; + +select row_start into @ts_2 from emp where name="bill"; + +select * from emp; + +update emp set salary=2000 where name="bill"; +commit; + +select row_start into @ts_3 from emp where name="bill"; + +select * from emp; +select * from emp for system_time as of timestamp @ts_2; +select * from emp for system_time as of timestamp @ts_3; +select * from emp e, dept d +where d.dept_id = 10 + and d.dept_id = e.dept_id; + +select * from + emp for system_time from timestamp @ts_1 to timestamp @ts_2 e, + dept for system_time from timestamp @ts_1 to timestamp @ts_2 d +where d.dept_id = 10 + and d.dept_id = e.dept_id; + +set statement system_versioning_asof=@ts_0 for +select * from emp e, dept d +where d.dept_id = 10 + and d.dept_id = e.dept_id; + +set statement system_versioning_asof=@ts_1 for +select * from emp e, dept d +where d.dept_id = 10 + and d.dept_id = e.dept_id; + +set statement system_versioning_asof=@ts_2 for +select * from emp e, dept d +where d.dept_id = 10 + and d.dept_id = e.dept_id; + +set statement system_versioning_asof=@ts_3 for +select * from emp e, dept d +where d.dept_id = 10 + and d.dept_id = e.dept_id; + +drop table emp, dept; + +# +# MDEV-16804 SYSTEM VERSIONING columns not showing as GENERATED +# +create table t1 ( + a timestamp(6), + b timestamp(6) generated always as (a + interval 1 day), + c timestamp(6) generated always as (a + interval 1 month) stored, + d timestamp(6) generated always as row start, + e timestamp(6) generated always as row end, + period for system_time(d,e) +) with system versioning; +show columns from t1; +query_vertical select table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,character_octet_length,numeric_precision,numeric_scale,datetime_precision,character_set_name,collation_name,column_type,column_key,extra,column_comment,is_generated,generation_expression,'---' from information_schema.columns where table_name='t1'; +drop table t1; + diff --git a/mysql-test/suite/versioning/t/sysvars.test b/mysql-test/suite/versioning/t/sysvars.test new file mode 100644 index 00000000000..1999ddd13fc --- /dev/null +++ b/mysql-test/suite/versioning/t/sysvars.test @@ -0,0 +1,105 @@ +create table t (a int) with system versioning; +insert into t values (1); +update t set a= 2; + +show global variables like 'system_versioning_asof'; +show variables like 'system_versioning_asof'; +select * from t; + +set system_versioning_asof= '2031-1-1 0:0:0'; +show variables like 'system_versioning_asof'; +select * from t; + +set system_versioning_asof= '2011-1-1 0:0:0'; +show variables like 'system_versioning_asof'; +select * from t; + +# global +--error ER_WRONG_VALUE_FOR_VAR +set global system_versioning_asof= 'alley'; +--error ER_WRONG_VALUE_FOR_VAR +set global system_versioning_asof= null; +--error ER_WRONG_TYPE_FOR_VAR +set global system_versioning_asof= 1; +--error ER_WRONG_TYPE_FOR_VAR +set global system_versioning_asof= 1.1; +--error ER_WRONG_VALUE_FOR_VAR +set global system_versioning_asof= '2011-02-29 00:00'; +--error ER_WRONG_VALUE_FOR_VAR +set global system_versioning_asof= '2011-02-28 24:00'; +--error ER_WRONG_VALUE_FOR_VAR +set global system_versioning_asof= '2011-00-28 00:00'; +--error ER_WRONG_VALUE_FOR_VAR +set global system_versioning_asof= '0000-00-00 00:00'; + +# session +--error ER_WRONG_VALUE_FOR_VAR +set system_versioning_asof= 'alley'; +--error ER_WRONG_VALUE_FOR_VAR +set system_versioning_asof= null; +--error ER_WRONG_TYPE_FOR_VAR +set system_versioning_asof= 1; +--error ER_WRONG_TYPE_FOR_VAR +set system_versioning_asof= 1.1; +--error ER_WRONG_VALUE_FOR_VAR +set system_versioning_asof= '2011-02-29 00:00'; +--error ER_WRONG_VALUE_FOR_VAR +set system_versioning_asof= '2011-02-28 24:00'; +--error ER_WRONG_VALUE_FOR_VAR +set system_versioning_asof= '2011-00-28 00:00'; +--error ER_WRONG_VALUE_FOR_VAR +set system_versioning_asof= '0000-00-00 00:00'; + +--echo # GLOBAL @@system_versioning_asof +set global system_versioning_asof= '1911-11-11 11:11:11.1111119'; +show global variables like 'system_versioning_asof'; + +set global system_versioning_asof= '1900-01-01 00:00:00'; +show global variables like 'system_versioning_asof'; + +set global system_versioning_asof= timestamp'1911-11-11 11:11:11.1111119'; +show global variables like 'system_versioning_asof'; + +set @ts= timestamp'1900-01-01 00:00:00'; +set global system_versioning_asof= @ts; +show global variables like 'system_versioning_asof'; + +set global system_versioning_asof= default; +select @@global.system_versioning_asof; + +--echo # SESSION @@system_versioning_asof +set system_versioning_asof= '1911-11-11 11:11:11.1111119'; +show variables like 'system_versioning_asof'; + +set system_versioning_asof= '1900-01-01 00:00:00'; +show variables like 'system_versioning_asof'; + +set system_versioning_asof= timestamp'1911-11-11 11:11:11.1111119'; +show variables like 'system_versioning_asof'; + +set @ts= timestamp'1900-01-01 00:00:00'; +set system_versioning_asof= @ts; +show variables like 'system_versioning_asof'; + +--echo # DEFAULT: value is copied from GLOBAL to SESSION +set global system_versioning_asof= timestamp'1911-11-11 11:11:11.111111'; +set system_versioning_asof= '1900-01-01 00:00:00'; +select @@global.system_versioning_asof != @@system_versioning_asof as different; +set system_versioning_asof= default; +select @@global.system_versioning_asof = @@system_versioning_asof as equal; + +set global system_versioning_asof= DEFAULT; +set system_versioning_asof= DEFAULT; +select @@global.system_versioning_asof, @@system_versioning_asof; + +select * from t for system_time all; + +select * from t; +select * from t for system_time as of timestamp current_timestamp(6); +select * from t for system_time all; +select * from t for system_time from '1970-01-01 00:00' to current_timestamp(6); +select * from t for system_time between '1970-01-01 00:00' and current_timestamp(6); + +show status like "Feature_system_versioning"; + +drop table t; diff --git a/mysql-test/suite/versioning/t/truncate_privilege.test b/mysql-test/suite/versioning/t/truncate_privilege.test new file mode 100644 index 00000000000..dcdad59039a --- /dev/null +++ b/mysql-test/suite/versioning/t/truncate_privilege.test @@ -0,0 +1,41 @@ +# Can't test with embedded server +-- source include/not_embedded.inc + +--source include/have_innodb.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +connect (root,localhost,root,,test); +connection root; + +--disable_warnings +create database mysqltest; +--enable_warnings + +create user mysqltest_1@localhost; +connect (user1,localhost,mysqltest_1,,test); +connection user1; + +connection root; +create table mysqltest.t (a int) with system versioning; + +connection user1; +show grants; +--error ER_TABLEACCESS_DENIED_ERROR +delete history from mysqltest.t before system_time now(); + +connection root; +grant delete history on mysqltest.* to mysqltest_1@localhost; +grant delete history on mysqltest.t to mysqltest_1@localhost; + +connection user1; +show grants; +delete history from mysqltest.t before system_time now(); + +connection root; +grant all on *.* to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; + +drop user mysqltest_1@localhost; +drop database mysqltest; diff --git a/mysql-test/suite/versioning/t/trx_id.opt b/mysql-test/suite/versioning/t/trx_id.opt new file mode 100644 index 00000000000..b55a187cb13 --- /dev/null +++ b/mysql-test/suite/versioning/t/trx_id.opt @@ -0,0 +1 @@ +--plugin-load-add=$TEST_VERSIONING_SO diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test new file mode 100644 index 00000000000..38724a47fd1 --- /dev/null +++ b/mysql-test/suite/versioning/t/trx_id.test @@ -0,0 +1,527 @@ +if (!$TEST_VERSIONING_SO) +{ + --skip needs test_versioning plugin +} +--source include/have_innodb.inc +--source include/default_charset.inc + +set default_storage_engine= innodb; + +create or replace table t1 ( + x int, + sys_trx_start bigint(20) unsigned as row start invisible, + sys_trx_end bigint(20) unsigned as row end invisible, + period for system_time (sys_trx_start, sys_trx_end) +) with system versioning; + +insert into t1 (x) values (1); + +--echo # ALTER ADD SYSTEM VERSIONING should write to mysql.transaction_registry +set @@system_versioning_alter_history=keep; + +create or replace table t1 (x int); +insert into t1 values (1); +alter table t1 + add column s bigint unsigned as row start, + add column e bigint unsigned as row end, + add period for system_time(s, e), + add system versioning, + algorithm=inplace; +select s from t1 into @trx_start; +select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start; + +create or replace table t1 (x int); +select count(*) from mysql.transaction_registry into @tmp; +alter table t1 + add column s bigint unsigned as row start, + add column e bigint unsigned as row end, + add period for system_time(s, e), + add system versioning, + algorithm=inplace; +select count(*) = @tmp from mysql.transaction_registry; + +create or replace table t1 (x int); +insert into t1 values (1); +alter table t1 + add column s bigint unsigned as row start, + add column e bigint unsigned as row end, + add period for system_time(s, e), + add system versioning, + algorithm=copy; +select s from t1 into @trx_start; +select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start; + +create or replace table t1 (x int); +select count(*) from mysql.transaction_registry into @tmp; +alter table t1 + add column s bigint unsigned as row start, + add column e bigint unsigned as row end, + add period for system_time(s, e), + add system versioning, + algorithm=copy; +# With MDEV-14511 the transaction will be registered even for empty tables. +select count(*) = @tmp + 1 from mysql.transaction_registry; + +--echo # TRX_ID to TIMESTAMP versioning switch +create or replace table t1 ( + x int, + sys_start bigint unsigned as row start invisible, + sys_end bigint unsigned as row end invisible, + period for system_time (sys_start, sys_end) +) with system versioning; +insert into t1 values (1); +alter table t1 drop column sys_start, drop column sys_end; +select row_end = 18446744073709551615 as transaction_based from t1 for system_time all; + +--echo # Simple vs SEES algorithms +create or replace table t1 ( + x int, + sys_start bigint(20) unsigned as row start invisible, + sys_end bigint(20) unsigned as row end invisible, + period for system_time (sys_start, sys_end) +) with system versioning; + +set transaction isolation level read committed; +start transaction; +insert into t1 values (1); +--connect (con1,localhost,root,,test) +set transaction isolation level read committed; +start transaction; +insert into t1 values (2); +--connect (con2,localhost,root,,test) +set transaction isolation level read committed; +start transaction; +insert into t1 values (3); +commit; +--disconnect con2 +--connection default +--sleep 0.01 +set @ts1= sysdate(6); +--connection con1 +commit; +--disconnect con1 +--connection default +--sleep 0.01 +set @ts2= sysdate(6); +commit; +--sleep 0.01 +set @ts3= sysdate(6); + +select sys_start from t1 where x = 1 into @trx_id1; +select sys_start from t1 where x = 2 into @trx_id2; +select sys_start from t1 where x = 3 into @trx_id3; + +select @trx_id1 < @trx_id2, @trx_id2 < @trx_id3; +select @ts1 < @ts2, @ts2 < @ts3; + +--echo # MVCC is resolved +select * from t1 for system_time as of transaction @trx_id1; +select * from t1 for system_time as of timestamp @ts1; +select * from t1 for system_time as of transaction @trx_id2; +select * from t1 for system_time as of timestamp @ts2; +select * from t1 for system_time as of transaction @trx_id3; +select * from t1 for system_time as of timestamp @ts3; + +--echo # +--echo # MDEV-15427 IB: TRX_ID based operations inside transaction generate history +--echo # +create or replace table t1( + x int(10), + row_start bigint(20) unsigned as row start, + row_end bigint(20) unsigned as row end, + period for system_time(row_start, row_end) +) with system versioning; + +begin; +insert into t1 (x) values (1); +delete from t1; +commit; +select x from t1 for system_time all; + +insert into t1 (x) values (2); +begin; +update t1 set x= 3; +update t1 set x= 4; +commit; +select x, row_start < row_end from t1 for system_time all; + +--echo # MDEV-16010 Too many rows with AS OF point_in_the_past_or_NULL +create or replace table t1 ( + x int, + row_start bigint unsigned as row start invisible, + row_end bigint unsigned as row end invisible, + period for system_time (row_start, row_end) +) with system versioning engine innodb; +insert into t1 (x) values (1); +delete from t1; +select * from t1 for system_time as of timestamp'1990-1-1 00:00'; +select * from t1 for system_time as of NULL; + +--echo # MDEV-16024 transaction_registry.begin_timestamp is wrong for explicit transactions +create or replace table t1 ( + x int(11) default null, + row_start bigint(20) unsigned generated always as row start invisible, + row_end bigint(20) unsigned generated always as row end invisible, + period for system_time (row_start, row_end) +) engine=innodb with system versioning; +begin; +set @ts1= now(6); +--sleep 0.01 +insert into t1 values (1); +commit; + +select row_start from t1 into @trx_id; +select trt_begin_ts(@trx_id) <= @ts1 as BEGIN_TS_GOOD; + +drop table t1; + +--echo # +--echo # MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs +--echo # + +CREATE TABLE t1 ( + x INT, + sys_trx_start BIGINT UNSIGNED AS ROW START, + sys_trx_end BIGINT UNSIGNED AS ROW END, + PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; +INSERT INTO t1 (x) VALUES (1); +SET @ts= DATE_ADD(NOW(), INTERVAL 1 YEAR); +EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION @ts; +EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TIMESTAMP @ts; +EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF @ts; +DROP TABLE t1; + + +--echo # +--echo # Testing AS OF with expressions of various kinds and data types +--echo # + +CREATE TABLE t1 +( + x INT, + sys_trx_start BIGINT UNSIGNED AS ROW START INVISIBLE, + sys_trx_end BIGINT UNSIGNED AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end) +) WITH SYSTEM VERSIONING; +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 +( + x INT, + sys_trx_start TIMESTAMP(6) AS ROW START INVISIBLE, + sys_trx_end TIMESTAMP(6) AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end) +) WITH SYSTEM VERSIONING; +INSERT INTO t2 VALUES (1); + +--echo # +--echo # ROW is not supported +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t1 FOR SYSTEM_TIME AS OF (1,1); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t2 FOR SYSTEM_TIME AS OF (1,1); + + +--echo # +--echo # DOUBLE is not supported, use explicit CAST +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t1 FOR SYSTEM_TIME AS OF RAND(); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t1 FOR SYSTEM_TIME AS OF (RAND()); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t1 FOR SYSTEM_TIME AS OF COALESCE(RAND()); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t2 FOR SYSTEM_TIME AS OF RAND(); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t2 FOR SYSTEM_TIME AS OF (RAND()); +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(RAND()); + + +--echo # +--echo # DECIMAL is not supported, use explicit CAST +--echo # + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t1 FOR SYSTEM_TIME AS OF 10.1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t1 FOR SYSTEM_TIME AS OF COALESCE(10.1); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t2 FOR SYSTEM_TIME AS OF 10.1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(10.1); + + +--echo # +--echo # YEAR is not supported, use explicit CAST +--echo # + +DELIMITER $$; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +BEGIN NOT ATOMIC + DECLARE var YEAR; + SELECT * FROM t1 FOR SYSTEM_TIME AS OF var; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +BEGIN NOT ATOMIC + DECLARE var YEAR; + SELECT * FROM t2 FOR SYSTEM_TIME AS OF var; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # ENUM is not supported, use explicit CAST +--echo # + +DELIMITER $$; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +BEGIN NOT ATOMIC + DECLARE var ENUM('xxx') DEFAULT 'xxx'; + SELECT * FROM t1 FOR SYSTEM_TIME AS OF var; +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +BEGIN NOT ATOMIC + DECLARE var ENUM('xxx') DEFAULT 'xxx'; + SELECT * FROM t2 FOR SYSTEM_TIME AS OF var; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # SET is not supported, use explicit CAST +--echo # + +DELIMITER $$; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +BEGIN NOT ATOMIC + DECLARE var SET('xxx') DEFAULT 'xxx'; + SELECT * FROM t1 FOR SYSTEM_TIME AS OF var; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +BEGIN NOT ATOMIC + DECLARE var SET('xxx') DEFAULT 'xxx'; + SELECT * FROM t2 FOR SYSTEM_TIME AS OF var; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # BIT is resolved to TRANSACTION +--echo # + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE var BIT(10); + SELECT * FROM t1 FOR SYSTEM_TIME AS OF var; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_VERS_ENGINE_UNSUPPORTED +BEGIN NOT ATOMIC + DECLARE var BIT(10); + SELECT * FROM t2 FOR SYSTEM_TIME AS OF var; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # String literals resolve to TIMESTAMP +--echo # + +SELECT * FROM t1 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00'; +SELECT * FROM t2 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00'; + + +--echo # +--echo # HEX hybrids resolve to TRANSACTION +--echo # + +--error ER_VERS_NO_TRX_ID +SELECT * FROM t1 FOR SYSTEM_TIME AS OF (0xFFFFFFFF); +--error ER_VERS_ENGINE_UNSUPPORTED +SELECT * FROM t2 FOR SYSTEM_TIME AS OF (0xFFFFFFFF); + + +--echo # +--echo # BIT literals resolve to TRANSACTION +--echo # + +--error ER_VERS_NO_TRX_ID +SELECT * FROM t1 FOR SYSTEM_TIME AS OF (b'11111111111111111111111111111111'); +--error ER_VERS_ENGINE_UNSUPPORTED +SELECT * FROM t2 FOR SYSTEM_TIME AS OF (b'11111111111111111111111111111111'); + +DROP TABLE t1, t2; + + +--echo # +--echo # MDEV-16094 Crash when using AS OF with a stored function +--echo # + +CREATE FUNCTION fts() RETURNS DATETIME RETURN '2001-01-01 10:20:30'; +CREATE FUNCTION ftx() RETURNS BIGINT UNSIGNED RETURN 1; + +CREATE TABLE ttx +( + x INT, + start_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW START, + end_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW END, + PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp) +) ENGINE=InnoDB WITH SYSTEM VERSIONING; + +CREATE TABLE tts +( + x INT, + start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START, + end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END, + PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp) +) ENGINE=InnoDB WITH SYSTEM VERSIONING; + +SELECT * FROM tts FOR SYSTEM_TIME AS OF fts(); +--error ER_VERS_ENGINE_UNSUPPORTED +SELECT * FROM tts FOR SYSTEM_TIME AS OF ftx(); +SELECT * FROM ttx FOR SYSTEM_TIME AS OF fts(); +SELECT * FROM ttx FOR SYSTEM_TIME AS OF ftx(); + +DROP TABLE tts; +DROP TABLE ttx; +DROP FUNCTION fts; +DROP FUNCTION ftx; + +--echo # +--echo # MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute +--echo # + +SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP; +CREATE TABLE t ( + a INT, + b INT, + row_start BIGINT UNSIGNED AS ROW START INVISIBLE, + row_end BIGINT UNSIGNED AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; + +INSERT INTO t VALUES (1,1); + +--echo # without table rebuild +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='a'; +--enable_info +ALTER TABLE t + CHANGE a a INT WITHOUT SYSTEM VERSIONING; +--disable_info +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='a'; + +UPDATE t SET a=11; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; + +--echo # with table rebuild +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='a'; +--enable_info +ALTER TABLE t + CHANGE a a INT WITH SYSTEM VERSIONING, + ADD PRIMARY KEY pk(a); +--disable_info +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='a'; + +UPDATE t SET a=1; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; + +SHOW CREATE TABLE t; + +-- echo # handles VIRTUAL columns too +CREATE OR REPLACE TABLE t ( + a INT AS (b + 1), + b INT, + row_start BIGINT UNSIGNED AS ROW START INVISIBLE, + row_end BIGINT UNSIGNED AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; + +INSERT INTO t VALUES (DEFAULT, 1); + +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='b'; +--enable_info +ALTER TABLE t + CHANGE b b INT WITHOUT SYSTEM VERSIONING; +--disable_info +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='b'; + +UPDATE t SET b=11; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; + +DROP TABLE t; +SET @@SYSTEM_VERSIONING_ALTER_HISTORY=ERROR; + +SELECT count(*) from mysql.transaction_registry where begin_timestamp>=commit_timestamp; + +--echo # MDEV-18875 Assertion `thd->transaction.stmt.ha_list == __null || +--echo # trans == &thd->transaction.stmt' failed or bogus ER_DUP_ENTRY upon +--echo # ALTER TABLE with versioning +create or replace table t (x int) engine=innodb; +set autocommit= 0; +alter table t + algorithm=copy, + add column row_start bigint unsigned as row start, + add column row_end bigint unsigned as row end, + add period for system_time(row_start,row_end), + with system versioning; +set autocommit= 1; + +--echo # MDEV-18865 Assertion `t->first->versioned_by_id()' +--echo # failed in innodb_prepare_commit_versioned + +create or replace table t (x int) engine=innodb; +insert into t values (0); +alter table t add `row_start` bigint unsigned as row start, + add `row_end` bigint unsigned as row end, + add period for system_time(`row_start`,`row_end`), + modify x int after row_start, + with system versioning; + + +create or replace database test; diff --git a/mysql-test/suite/versioning/t/update-big.test b/mysql-test/suite/versioning/t/update-big.test new file mode 100644 index 00000000000..175bfc79a48 --- /dev/null +++ b/mysql-test/suite/versioning/t/update-big.test @@ -0,0 +1,34 @@ +source include/big_test.inc; +source suite/versioning/engines.inc; +source suite/versioning/common.inc; + +--echo # +--echo # MDEV-15458 Segfault in heap_scan() upon UPDATE after ADD SYSTEM VERSIONING +--echo # +create or replace table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; + +--connect (con1,localhost,root,,test) +alter table t1 add system versioning; + +--connection default +update t1 set a= 7 where a = 3; +update t1 set a= 2 where a = 7; +update t1 set a= 5 where a = 2; +update t1 set a= 1 where a = 5; +update t1 set a= 8 where a = 1; +update t1 set a= 4 where a = 8; +update t1 set a= 6; + +drop table t1; + +source suite/versioning/common_finish.inc; diff --git a/mysql-test/suite/versioning/t/update.test b/mysql-test/suite/versioning/t/update.test new file mode 100644 index 00000000000..5b0a9eb5c42 --- /dev/null +++ b/mysql-test/suite/versioning/t/update.test @@ -0,0 +1,248 @@ +source suite/versioning/engines.inc; +source suite/versioning/common.inc; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create table t1( + x int unsigned, + y int unsigned, + sys_trx_start $sys_datatype_expl as row start invisible, + sys_trx_end $sys_datatype_expl as row end invisible, + period for system_time (sys_trx_start, sys_trx_end)) + with system versioning; + +insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000); +select x, y from t1 order by x, y; +update t1 set y = y + 1 where x > 7; +select x, y from t1 order by x, y; +select x, y from t1 for system_time all order by sys_trx_end, x, y; +drop table t1; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create table t1 ( + id bigint primary key, + x int, + y int without system versioning, + sys_trx_start $sys_datatype_expl as row start invisible, + sys_trx_end $sys_datatype_expl as row end invisible, + period for system_time (sys_trx_start, sys_trx_end)) +with system versioning; +insert into t1 values(1, 1, 1); +set @ins_t= now(6); +select sys_trx_start into @tmp1 from t1; +update t1 set x= 11, y= 11 where id = 1; +select @tmp1 < sys_trx_start as A1, x, y from t1; +select sys_trx_start into @tmp1 from t1; +update t1 set y= 1 where id = 1; +select @tmp1 = sys_trx_start as A2, x from t1; +drop table t1; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create table t1 ( + x int, + y int, + sys_trx_start $sys_datatype_expl as row start invisible, + sys_trx_end $sys_datatype_expl as row end invisible, + period for system_time (sys_trx_start, sys_trx_end) +) with system versioning; + +insert into t1 (x, y) values (1, 1), (2, 1), (3, 1), (4, 1), (5, 1); + +start transaction; +update t1 set y= y + 1 where x = 3; +update t1 set y= y + 1 where x = 2; +update t1 set y= y + 1 where x = 3; +update t1 set y= y + 1 where x > 3; +update t1 set y= y + 1 where x > 4; +commit; + +replace_result $sys_datatype_max MAXVAL; +eval select x, y, sys_trx_end = $sys_datatype_max as current from t1 for system_time all order by sys_trx_end, x, y; + +drop table t1; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create table t1 ( + id int primary key auto_increment, + x int, + sys_trx_start $sys_datatype_expl as row start invisible, + sys_trx_end $sys_datatype_expl as row end invisible, + period for system_time (sys_trx_start, sys_trx_end)) +with system versioning; + +set @t0= now(6); +insert into t1 (x) values (1); +set @t1= now(6); +update t1 set x= 2 where id = 1; +set @t2= now(6); +update t1 set x= 3 where id = 1; + +select x from t1 for system_time as of timestamp @t0; +select x from t1 for system_time as of timestamp @t1; +select x from t1 for system_time as of timestamp @t2; +select x from t1 for system_time as of timestamp now(6); + +drop table t1; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create table t1( + x int unsigned, + sys_trx_end $sys_datatype_expl as row end invisible, + sys_trx_start $sys_datatype_expl as row start invisible, + y int unsigned, + period for system_time (sys_trx_start, sys_trx_end), + primary key(x, y)) +with system versioning; +insert into t1(x, y) values (1, 1000), (3, 3000), (4, 4000), (5, 5000); +insert into t1(x, y) values(3, 3000) on duplicate key update y = y+1; +insert into t1(x, y) values(4, 4444) on duplicate key update y = y+1; +select x, y from t1 for system_time all order by sys_trx_end, x, y; +select x, y from t1 order by x, y; +drop table t1; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create table t1 ( + x int unsigned, + y int unsigned, + sys_trx_start $sys_datatype_expl as row start invisible, + sys_trx_end $sys_datatype_expl as row end invisible, + period for system_time (sys_trx_start, sys_trx_end)) + with system versioning; +create table t2 like t1; +insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000); +insert into t2(x, y) values (1, 1010), (2, 2010), (3, 3010), (4, 4010), (5, 5010), (6, 6010), (7, 7010), (8, 8010), (9, 9010); +update t1, t2 set t1.y = t1.x + t1.y, t2.y = t2.x + t2.y where t1.x > 7 and t2.x < 7; +select x, y from t1 for system_time all order by sys_trx_end, x, y; +select x, y from t1 order by x, y; +select x, y from t2 for system_time all order by sys_trx_end, x, y; +select x, y from t2 order by x, y; +drop table t1; +drop table t2; + +replace_result $sys_datatype_expl SYS_DATATYPE; +eval create table t1 ( + id bigint primary key without system versioning, + name varchar(128), + salary bigint without system versioning, + sys_trx_start $sys_datatype_expl as row start invisible, + sys_trx_end $sys_datatype_expl as row end invisible, + period for system_time (sys_trx_start, sys_trx_end)) + with system versioning; +create table t2 like t1; + +insert into t1 values (1, "Jeremy", 3000); +insert into t2 values (1, "Jeremy", 4000); + +select sys_trx_start into @tmp1 from t1; +select sys_trx_start into @tmp2 from t2; +update t1, t2 set t1.name= "Jerry", t2.name= "Jerry" where t1.id = t2.id and t1.name = "Jeremy"; +select @tmp1 < sys_trx_start as A1, name from t1; +select @tmp2 < sys_trx_start as A2, name from t2; + +select sys_trx_start into @tmp1 from t1; +select sys_trx_start into @tmp2 from t2; +update t1, t2 set t1.salary= 2500, t2.salary= 2500 where t1.id = t2.id and t1.name = "Jerry"; +select @tmp1 = sys_trx_start as B1, salary from t1; +select @tmp2 = sys_trx_start as B2, salary from t2; + +drop table t1; +drop table t2; + +--echo ### Issue tempesta-tech/mariadb#365, bug 7 (duplicate of historical row) +create or replace table t1 (a int primary key, b int) +with system versioning engine myisam; +insert into t1 (a) values (1); + +replace t1 values (1,2),(1,3),(2,4); + +--echo # +--echo # MDEV-14829 Assertion `0' failed in Protocol::end_statement upon concurrent UPDATE +--echo # +set @old_lock_wait_timeout= @@innodb_lock_wait_timeout; +set @@innodb_lock_wait_timeout= 1073741824; +create or replace table t1 (pk int, a char(3), b char(3), primary key(pk)) + engine=innodb with system versioning; + +insert into t1 (pk) values (1); +connect (con1,localhost,root,,test); +start transaction; +select * from t1 for update; +connection default; +send update t1 set b = 'foo'; +connection con1; +let $wait_condition= select count(*) from information_schema.innodb_lock_waits; +source include/wait_condition.inc; +error ER_LOCK_DEADLOCK; +update t1 set a = 'bar'; +disconnect con1; +connection default; +reap; +drop table t1; +set @@innodb_lock_wait_timeout= @old_lock_wait_timeout; + +--echo # +--echo # MDEV-19406 Assertion on updating view of join with versioned table +--echo # +--disable_warnings +create or replace table t1 (pk int primary key, a date, b int, index(b)) engine=innodb with system versioning; +create or replace table t2 (c int); +create or replace view v as select * from t1 join t2; + +insert into t1 (pk) values (1); +update t1 set a= '2012-12-12'; +update v set a= '2000-01-01' order by b limit 1; # point of failure +drop view v; +drop table t1, t2; +--enable_warnings + +--echo # +--echo # MDEV-20441 ER_CRASHED_ON_USAGE upon update on versioned Aria table +--echo # +create or replace table t1 (a varchar(8)) +engine=aria row_format=fixed +with system versioning; + +insert into t1 (a) values ('foo'); +update t1 set a = 'bar'; +drop table t1; + +--echo # +--echo # MDEV-21147 Assertion `marked_for_read()' upon UPDATE on versioned table via view +--echo # +create or replace table t1 ( + pk int, a char(8), b char(8), + primary key (pk) +) with system versioning; + +create or replace view v1 as select * from t1; +insert into t1 values (1, null, 'd') , (2, null, 'i') ; +update v1 set a= null where b = ''; + +create or replace table t1 (id int, k int, primary key (id)) engine=innodb with system versioning; +insert into t1 values (1,1),(2,2); +create or replace view v1 as select * from t1; +update v1 set id= 2 where k = 0; + +create or replace table t1 (a int) with system versioning; +create or replace view v1 as select * from t1; +create or replace procedure sp() update v1 set xx = 1; +--error ER_BAD_FIELD_ERROR +call sp; +--error ER_BAD_FIELD_ERROR +call sp; + +# cleanup +drop procedure sp; +drop view v1; +drop table t1; + +--echo # +--echo # MDEV-21342 Assertion in set_ok_status() upon spatial field error on system-versioned table +--echo # +create or replace table t1 (f point, key(f)) with system versioning engine=myisam; +--error ER_CANT_CREATE_GEOMETRY_OBJECT +update t1 set f = null where f = 'foo'; + +# cleanup +drop table t1; + +source suite/versioning/common_finish.inc; diff --git a/mysql-test/suite/versioning/t/view.test b/mysql-test/suite/versioning/t/view.test new file mode 100644 index 00000000000..37676a7fb50 --- /dev/null +++ b/mysql-test/suite/versioning/t/view.test @@ -0,0 +1,216 @@ +--source suite/versioning/engines.inc +--source suite/versioning/common.inc + +create or replace table t1 (x int) with system versioning; +insert into t1 values (1); + +select now(6) into @t1; +update t1 set x= 2; + +select now(6) into @t2; +delete from t1; + +set @vt1= concat("create or replace view vt1 as select * from t1 for system_time as of timestamp '", @t1, "'"); +prepare stmt from @vt1; execute stmt; drop prepare stmt; + +set @vt2= concat("create or replace view vt2 as select *, row_end from t1 for system_time as of timestamp '", @t2, "'"); +prepare stmt from @vt2; execute stmt; drop prepare stmt; + +select * from t1; + +create or replace view vt1 as select * from t1; +--replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT +show create view vt1; + +drop view vt1; +drop view vt2; + +create or replace view vt1 as select * from t1 for system_time all; +select * from vt1; +prepare stmt from 'select * from vt1'; execute stmt; drop prepare stmt; + +set @str= concat('create or replace view vt1 as +select * from t1 for system_time as of timestamp "', @t1, '"'); +prepare stmt from @str; execute stmt; drop prepare stmt; +select * from t1 for system_time as of timestamp @t1; +select * from vt1; + +insert into vt1 values (3); +select * from t1; +select * from vt1; + +create or replace table t1 (x int) with system versioning; +insert into t1 values (1), (2); +set @t1=now(6); +delete from t1 where x=2; +set @t2=now(6); +delete from t1 where x=1; +set @t3=now(6); + +set @tmp= concat("create or replace view vt1 as select * from t1 for system_time as of timestamp '", @t1, "'"); +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +select * from vt1; + +--echo # VIEW with parameters [tempesta-tech/mariadb#151] +create or replace table t1 (x int) with system versioning; +create or replace view vt1(c) as select x from t1; +--replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT +show create view vt1; + +--echo # VIEW over JOIN of versioned tables [tempesta-tech/mariadb#153] +create or replace table t1 (a int) with system versioning; +create or replace table t2 (b int) with system versioning; +insert into t1 values (1); +insert into t2 values (2); +create or replace view vt12 as select * from t1 cross join t2; +select * from vt12; +create or replace view vt12 as select * from t1 for system_time as of timestamp ('1970-01-01 00:00') cross join t2; +select * from vt12; + +--echo # VIEW improvements [tempesta-tech/mariadb#183] +create or replace table t3 (x int); +create or replace view vt1 as select * from t1, t2, t3; +--replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT +show create view vt1; +create or replace view vt1 as select * from t3, t2, t1; +--replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT +show create view vt1; +create or replace view vt1 as select a, t2.row_end as endo from t3, t1, t2; +--replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT +show create view vt1; + +--echo # VIEW over UNION [tempesta-tech/mariadb#269] +create or replace view vt1 as select * from t1 union select * from t1; +select * from vt1; + +--echo # VIEW over UNION with non-versioned [tempesta-tech/mariadb#393] +create or replace table t2 (a int); +create or replace view vt1 as select * from t1 union select * from t2; +select * from vt1; + +--echo # +--echo # MDEV-14689 crash on second PS execute +--echo # +create or replace table t1 (a int); +create or replace view v1 as select * from t1; +create or replace table t2 (b int) with system versioning; +prepare stmt from 'select a from v1 inner join t2 group by a order by a'; +execute stmt; +execute stmt; +drop view v1; +drop tables t1, t2; + +--echo # +--echo # MDEV-15146 SQLError[4122]: View is not system versioned +--echo # +create or replace table t1 (a int) with system versioning; +insert t1 values (1),(2); +set @a=now(6); +create or replace view v1 as select * from t1; +delete from t1; +select * from v1; +select * from v1 for system_time as of @a; +show create view v1; + +--echo # +--echo # MDEV-15378 Valid query causes invalid view definition due to syntax limitation in FOR SYSTEM_TIME +--echo # +create or replace table t1 (i int) with system versioning; +select * from t1 for system_time as of now() - interval 6 second; +create or replace view v1 as select * from t1 for system_time as of date_sub(now(), interval 6 second); +show create view v1; + +drop view v1, vt1, vt12; +drop tables t1, t3; + +--echo # +--echo # MDEV-18727 improve DML operation of System Versioning +--echo # +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + x int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; +insert into t1 values (1), (2); +create or replace view v1 as select * from t1 where x > 1; +--echo # update, delete +update v1 set x= x + 1; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +insert v1 values (4); +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +delete from v1 where x < 4; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +--echo # multi-update +create or replace table t2 like t1; +insert into t2 values (1), (2); +create or replace view v2 as select * from t2 where x > 1; +update v1, v2 set v1.x= v1.x + 1, v2.x= v2.x + 1 where v1.x = v2.x + 2; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +select *, check_row(row_start, row_end) from t2 for system_time all order by x; +--echo # multi-delete +delete v1, v2 from v1 join v2 where v1.x = v2.x + 2; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +select *, check_row(row_start, row_end) from t2 for system_time all order by x; +--echo # replace +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + x int primary key, y int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; +insert into t1 values (1, 0), (2, 0); +create or replace view v1 as select * from t1 where x > 1; +replace v1 values (1, 1); +replace v1 values (2, 1); +replace v1 values (3, 1); +--echo # REPLACE ignores VIEW condition because itself doesn't use WHERE +select *, check_row(row_start, row_end) from t1 for system_time all order by x, row_end; +--echo # insert-select, on duplicate key +insert v1 select * from t1 where x = 1 on duplicate key update x = v1.x - 1; +select *, check_row(row_start, row_end) from t1 for system_time all order by x, row_end; +drop view v1, v2; +drop tables t1, t2; + +--echo # +--echo # MDEV-21146 Assertion `m_lock_type == 2' in handler::ha_drop_table upon LOAD DATA +--echo # +create table t1 (a int); +create view v1 as select * from t1; +create or replace table t1 (b int) with system versioning; +--error ER_VIEW_INVALID +load data infile 'xx' into table v1; + +# cleanup +drop view v1; +drop table t1; + +--echo # +--echo # MDEV-21155 Assertion with versioned table upon DELETE from view of view after replacing first view +--echo # +create table t1 (a int); +insert into t1 values (1); +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create table t2 ( + b int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; +insert into t2 values (2); +create view v1 as select * from t1; +create view v2 as select * from v1; +create or replace view v1 as select * from t2; +delete from v2; +select * from t1; +select *, check_row(row_start, row_end) from t2 for system_time all; + +# cleanup +drop view v2; +drop view v1; +drop table t1, t2; + +--source suite/versioning/common_finish.inc |