diff options
73 files changed, 1178 insertions, 836 deletions
diff --git a/include/my_base.h b/include/my_base.h index 71dca2e831a..2c93165c912 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -414,6 +414,10 @@ enum ha_base_keytype { when only HA_STATUS_VARIABLE but it won't be used. */ #define HA_STATUS_VARIABLE_EXTRA 128U +/* + Treat empty table as empty (ignore HA_STATUS_TIME hack). +*/ +#define HA_STATUS_OPEN 256U /* Errorcodes given by handler functions diff --git a/include/mysql_com.h b/include/mysql_com.h index b8108782104..5a37677ffdd 100644 --- a/include/mysql_com.h +++ b/include/mysql_com.h @@ -198,7 +198,7 @@ enum enum_indicator_type #define VERS_SYS_END_FLAG (1 << 28) /* autogenerated column declared with `generated always as row end` (see II.a SQL Standard).*/ -#define VERS_SYSTEM_FIELD VERS_SYS_START_FLAG | VERS_SYS_END_FLAG +#define VERS_SYSTEM_FIELD (VERS_SYS_START_FLAG | VERS_SYS_END_FLAG) #define VERS_UPDATE_UNVERSIONED_FLAG (1 << 29) /* column that doesn't support system versioning when table itself supports it*/ diff --git a/mysql-test/suite/versioning/common.inc b/mysql-test/suite/versioning/common.inc index 18dbd550135..cbc515241b0 100644 --- a/mysql-test/suite/versioning/common.inc +++ b/mysql-test/suite/versioning/common.inc @@ -57,12 +57,9 @@ create function if not exists current_row(sys_trx_end varbinary(255)) returns int deterministic begin - if default_engine() = 'InnoDB' then - return sys_trx_end = 18446744073709551615; - elseif default_engine() = 'MyISAM' then - return sys_trx_end = timestamp'2038-01-19 03:14:07.999999'; - end if; - return NULL; + declare continue handler for sqlwarning begin end; + return sys_trx_end = timestamp'2038-01-19 03:14:07.999999' + or sys_trx_end = 18446744073709551615; end~~ create function if not exists sys_commit_ts(sys_field varchar(255)) @@ -77,20 +74,16 @@ begin return NULL; end~~ -create procedure if not exists innodb_verify_vtq(recs int) +create procedure if not exists verify_vtq_dummy(recs int) begin declare i int default 1; - if default_engine() = 'InnoDB' then - call verify_vtq; - elseif default_engine() = 'MyISAM' then - create temporary table tmp (No int, A bool, B bool, C bool, D bool); - while i <= recs do - insert into tmp values (i, 1, 1, 1, 1); - set i= i + 1; - end while; - select * from tmp; - drop table tmp; - end if; + create temporary table tmp (No int, A bool, B bool, C bool, D bool); + while i <= recs do + insert into tmp values (i, 1, 1, 1, 1); + set i= i + 1; + end while; + select * from tmp; + drop table tmp; end~~ create procedure concat_exec2(a varchar(255), b varchar(255)) @@ -110,6 +103,25 @@ delimiter ;~~ let $default_engine= `select default_engine()`; let $non_default_engine= `select non_default_engine()`; -let $sys_datatype= `select sys_datatype(default_engine())`; -let $sys_datatype_uc= `select upper(sys_datatype(default_engine()))`; +let $sys_datatype= timestamp(6); +let $sys_datatype_expl= timestamp(6); +let $sys_datatype_uc= TIMESTAMP(6); +let $sys_datatype_expl_uc= TIMESTAMP(6); + +let $non_sys_datatype= `select sys_datatype(non_default_engine())`; +let $non_sys_datatype_uc= `select upper(sys_datatype(non_default_engine()))`; +let $sys_datatype_null= $sys_datatype NULL DEFAULT NULL; +let $sys_datatype_default_null= $sys_datatype DEFAULT NULL; +let $sys_datatype_not_null= $sys_datatype NOT NULL DEFAULT '0000-00-00 00:00:00.000000'; +let $non_sys_datatype_null= $non_sys_datatype NULL; + +if ($MTR_COMBINATION_MYISAM) +{ + --let $MTR_COMBINATION_TIMESTAMP= 1 +} +if ($MTR_COMBINATION_TRX_ID) +{ + let $sys_datatype_expl= bigint(20) unsigned; + let $sys_datatype_expl_uc= BIGINT(20) UNSIGNED; +} --enable_query_log diff --git a/mysql-test/suite/versioning/common_finish.inc b/mysql-test/suite/versioning/common_finish.inc index b34867ee89b..1059bb1251d 100644 --- a/mysql-test/suite/versioning/common_finish.inc +++ b/mysql-test/suite/versioning/common_finish.inc @@ -1,6 +1,6 @@ --disable_query_log drop procedure verify_vtq; -drop procedure innodb_verify_vtq; +drop procedure verify_vtq_dummy; drop function default_engine; drop function non_default_engine; drop function sys_commit_ts; diff --git a/mysql-test/suite/versioning/engines.combinations b/mysql-test/suite/versioning/engines.combinations index 75fb20d9f5e..561c5656929 100644 --- a/mysql-test/suite/versioning/engines.combinations +++ b/mysql-test/suite/versioning/engines.combinations @@ -1,4 +1,7 @@ -[innodb] +[timestamp] +default-storage-engine=innodb + +[trx_id] default-storage-engine=innodb [myisam] diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index 5566966793a..118b6d19891 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -10,7 +10,7 @@ t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t drop system versioning; -ERROR HY000: Table `t` is not versioned +ERROR HY000: Table `t` is not temporal alter table t add system versioning; show create table t; Table Create Table @@ -21,9 +21,9 @@ t CREATE TABLE `t` ( PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING alter table t add column y int; -ERROR HY000: Not allowed for versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. +ERROR HY000: Not allowed for temporal `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t engine innodb; -ERROR HY000: Not allowed for versioned `test`.`t`. Change to/from native versioning engine is prohibited. +ERROR HY000: Not allowed for temporal `test`.`t`. Change to/from native system versioning engine is prohibited. alter table t drop system versioning; show create table t; Table Create Table @@ -36,13 +36,13 @@ add column trx_start bigint(20) unsigned generated always as row start, add column trx_end bigint(20) unsigned generated always as row end, add period for system_time(trx_start, trx_end), add system versioning; -ERROR HY000: `trx_start` must be of type TIMESTAMP(6) for versioned table `t` +ERROR HY000: `trx_start` must be of type TIMESTAMP(6) for temporal table `t` alter table t add column trx_start timestamp generated always as row start, add column trx_end timestamp generated always as row end, add period for system_time(trx_start, trx_end), add system versioning; -ERROR HY000: `trx_start` must be of type TIMESTAMP(6) for versioned table `t` +ERROR HY000: `trx_start` must be of type TIMESTAMP(6) for temporal table `t` alter table t add column trx_start timestamp(6) not null generated always as row start, add column trx_end timestamp(6) not null generated always as row end, @@ -231,9 +231,9 @@ t CREATE TABLE `t` ( `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t modify a int with system versioning; -ERROR HY000: Table `t` is not versioned +ERROR HY000: Table `t` is not temporal alter table t modify a int without system versioning; -ERROR HY000: Table `t` is not versioned +ERROR HY000: Table `t` is not temporal alter table t add system versioning; alter table t modify a int without system versioning; show create table t; @@ -258,21 +258,36 @@ t CREATE TABLE `t` ( create or replace table t( a int ) engine=innodb; -insert into t values(1); -select * from t; -a -1 alter table t -add column trx_start timestamp(6) generated always as row start, -add column trx_end timestamp(6) generated always as row end, +add column trx_start timestamp(6) as row start, +add column trx_end timestamp(6) as row end, add period for system_time(trx_start, trx_end), add system versioning; -ERROR HY000: `trx_start` must be of type BIGINT(20) UNSIGNED for versioned table `t` +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `trx_start` timestamp(6) GENERATED ALWAYS AS ROW START, + `trx_end` timestamp(6) GENERATED ALWAYS AS ROW END, + PERIOD FOR SYSTEM_TIME (`trx_start`, `trx_end`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +# 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; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `trx_start` timestamp(6) GENERATED ALWAYS AS ROW START, + `trx_end` timestamp(6) GENERATED ALWAYS AS ROW END, + PERIOD FOR SYSTEM_TIME (`trx_start`, `trx_end`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t drop system versioning; +insert into t values(1); call verify_vtq; No A B C D alter table t -add column trx_start bigint(20) unsigned generated always as row start, -add column trx_end bigint(20) unsigned generated always as row end, +add column trx_start bigint(20) unsigned as row start, +add column trx_end bigint(20) unsigned as row end, add period for system_time(trx_start, trx_end), add system versioning; call verify_vtq; @@ -285,12 +300,10 @@ t CREATE TABLE `t` ( `trx_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`trx_start`, `trx_end`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING -alter table t drop column trx_start, drop column trx_end; -call verify_vtq; -No A B C D -alter table t drop system versioning, algorithm=copy; -call verify_vtq; -No A B C D +create or replace table t( +a int +) engine=innodb; +insert into t values (1); alter table t add system versioning, algorithm=copy; call verify_vtq; No A B C D @@ -298,8 +311,8 @@ show create table t; Table Create Table t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, - `sys_trx_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START, - `sys_trx_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END, + `sys_trx_start` timestamp(6) GENERATED ALWAYS AS ROW START, + `sys_trx_end` timestamp(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING update t set a= 2; @@ -307,16 +320,13 @@ select * from t for system_time all; a 2 1 -call verify_vtq; -No A B C D -1 1 1 1 1 alter table t add column b int, algorithm=copy; show create table t; Table Create Table t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, - `sys_trx_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START, - `sys_trx_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END, + `sys_trx_start` timestamp(6) GENERATED ALWAYS AS ROW START, + `sys_trx_end` timestamp(6) GENERATED ALWAYS AS ROW END, `b` int(11) DEFAULT NULL, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING @@ -330,8 +340,8 @@ show create table t; Table Create Table t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, - `sys_trx_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START, - `sys_trx_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END, + `sys_trx_start` timestamp(6) GENERATED ALWAYS AS ROW START, + `sys_trx_end` timestamp(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING select * from t for system_time all; @@ -343,53 +353,7 @@ No A B C D alter table t drop system versioning, algorithm=inplace; call verify_vtq; No A B C D -alter table t add system versioning, algorithm=inplace; -call verify_vtq; -No A B C D -show create table t; -Table Create Table -t CREATE TABLE `t` ( - `a` int(11) DEFAULT NULL, - `sys_trx_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START, - `sys_trx_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END, - PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING -update t set a= 1; -select * from t for system_time all; -a -1 -2 -call verify_vtq; -No A B C D -1 1 1 1 1 -alter table t add column b int, algorithm=inplace; -show create table t; -Table Create Table -t CREATE TABLE `t` ( - `a` int(11) DEFAULT NULL, - `sys_trx_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START, - `sys_trx_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END, - `b` int(11) DEFAULT NULL, - PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING -select * from t; -a b -1 NULL -call verify_vtq; -No A B C D -alter table t drop column b, algorithm=inplace; -show create table t; -Table Create Table -t CREATE TABLE `t` ( - `a` int(11) DEFAULT NULL, - `sys_trx_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START, - `sys_trx_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END, - PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING -select * from t for system_time all; -a -1 -2 +alter table t add system versioning; alter table t drop system versioning, algorithm=copy; show create table t; Table Create Table @@ -411,17 +375,11 @@ 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; -call verify_vtq; -No A B C D -1 1 1 1 1 -2 1 1 1 1 alter table t add b int not null unique; -ERROR 23000: Duplicate entry '...' for key 'b' +Got one of the listed errors alter table t add b int auto_increment unique; -ERROR 42000: Table 'test/t' uses an extension that doesn't exist in this MariaDB version +Got one of the listed errors alter table t add b int auto_increment null unique; -call verify_vtq; -No A B C D select * from t; a b 3 1 @@ -436,17 +394,14 @@ a b 1 NULL 2 NULL 3 1 -4 2 -call verify_vtq; -No A B C D -1 1 1 1 1 +4 4 create or replace table t (a int) with system versioning; insert into t values (1), (2), (3); delete from t where a<3; alter table t add b int not null unique; -ERROR 23000: Duplicate entry '...' for key 'b' +Got one of the listed errors alter table t add b int auto_increment unique; -ERROR 42000: Table '#sql-temporary' uses an extension that doesn't exist in this MariaDB version +Got one of the listed errors alter table t add b int auto_increment null unique; select * from t; a b @@ -509,10 +464,10 @@ set system_versioning_alter_history= DROP; ERROR 42000: Variable 'system_versioning_alter_history' can't be set to the value of 'DROP' create or replace table t (a int) with system versioning; alter table t add system versioning; -ERROR HY000: Table `t` is already system-versioned table +ERROR HY000: Table `t` is already temporal alter table t add system versioning, drop system versioning; -ERROR HY000: Table `t` is already system-versioned table -set @@versioning_alter_history=keep; +ERROR HY000: Table `t` is already temporal +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); diff --git a/mysql-test/suite/versioning/r/commit_id.result b/mysql-test/suite/versioning/r/commit_id.result index 3e4bac10790..d6cdb0182ff 100644 --- a/mysql-test/suite/versioning/r/commit_id.result +++ b/mysql-test/suite/versioning/r/commit_id.result @@ -1,5 +1,9 @@ create table t1( -id int auto_increment primary key) +id int auto_increment primary key, +sys_trx_start bigint unsigned generated always as row start, +sys_trx_end bigint unsigned generated always as row end, +period for system_time (sys_trx_start, sys_trx_end) +) with system versioning engine innodb; insert into t1 values (); diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result index 300d70b8afe..b2a9e476e49 100644 --- a/mysql-test/suite/versioning/r/create.result +++ b/mysql-test/suite/versioning/r/create.result @@ -99,90 +99,90 @@ Sys_start bigint generated always as row start, Sys_end bigint unsigned generated always as row end, period for system_time (Sys_start, Sys_end) ) with system versioning engine innodb; -ERROR HY000: `Sys_start` must be of type BIGINT(20) UNSIGNED for versioned table `t1` +ERROR HY000: `Sys_start` must be of type TIMESTAMP(6) for temporal table `t1` create or replace table t1 ( x14 int unsigned, Sys_start bigint unsigned generated always as row start, Sys_end bigint generated always as row end, period for system_time (Sys_start, Sys_end) ) with system versioning engine innodb; -ERROR HY000: `Sys_end` must be of type BIGINT(20) UNSIGNED for versioned table `t1` +ERROR HY000: `Sys_end` must be of type BIGINT(20) UNSIGNED for temporal table `t1` create or replace table t1 ( -A1 int with system versioning, +x15 int with system versioning, B int ); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `A1` int(11) DEFAULT NULL, + `x15` int(11) DEFAULT NULL, `B` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING create or replace table t1 ( -A2 int with system versioning, +x16 int with system versioning, B int ) with system versioning; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `A2` int(11) DEFAULT NULL, + `x16` int(11) DEFAULT NULL, `B` int(11) DEFAULT NULL, `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING create or replace table t1 ( -A3 int, +x17 int, B int without system versioning ); create or replace table t1 ( -A4 int, +x18 int, B int without system versioning ) with system versioning; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `A4` int(11) DEFAULT NULL, + `x18` int(11) DEFAULT NULL, `B` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING create or replace table t1 ( -A5 int with system versioning, +x19 int with system versioning, B int without system versioning ); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `A5` int(11) DEFAULT NULL, + `x19` int(11) DEFAULT NULL, `B` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING create or replace table t1 ( -A6 int with system versioning, +x20 int with system versioning, B int without system versioning ) with system versioning; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `A6` int(11) DEFAULT NULL, + `x20` int(11) DEFAULT NULL, `B` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING create or replace table t1 ( -A7 int without system versioning +x21 int without system versioning ); create or replace table t1 ( -A8 int without system versioning +x22 int without system versioning ) with system versioning; -ERROR HY000: Table `t1` has no versioned columns +ERROR HY000: Table `t1` must have at least 1 temporal column create or replace table t1 (a int) with system versioning; create table tt1 like t1; show create table tt1; @@ -204,7 +204,7 @@ tt1 CREATE TEMPORARY TABLE `tt1` ( `a` int(11) DEFAULT NULL ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 # CREATE TABLE ... SELECT -create or replace table t1 (x int) with system versioning; +create or replace table t1 (x23 int) with system versioning; create or replace table t0( y int, st SYS_DATATYPE generated always as row start, @@ -217,7 +217,7 @@ create or replace table t2 as select * from t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `x` int(11) DEFAULT NULL + `x23` int(11) DEFAULT NULL ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 ### 2. explicit system fields are included create or replace table t3 as select * from t0; @@ -240,14 +240,14 @@ create or replace table t2 with system versioning as select * from t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `x` int(11) DEFAULT NULL, + `x23` int(11) DEFAULT NULL, `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING #### sys_trx_start, sys_trx_end are copied; wildcard not expanded select * from t2 where sys_trx_start = @sys_trx_start; -x +x23 1 ### 2. explicit system fields are included as non-system create or replace table t3 with system versioning as select * from t0; @@ -285,21 +285,21 @@ select y from t3 where st = @st; y 2 ### 4. system fields not or wrongly selected -create or replace table t3 with system versioning select x from t1; +create or replace table t3 with system versioning select x23 from t1; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `x` int(11) DEFAULT NULL, + `x23` int(11) DEFAULT NULL, `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING select * from t3; -x +x23 1 -create or replace table t3 with system versioning select x, sys_trx_start from t1; +create or replace table t3 with system versioning select x23, sys_trx_start from t1; ERROR HY000: Wrong parameters for `t3`: missing 'AS ROW END' -create or replace table t3 with system versioning select x, sys_trx_end from t1; +create or replace table t3 with system versioning select x23, sys_trx_end from t1; ERROR HY000: Wrong parameters for `t3`: missing 'AS ROW START' # Prepare checking for historical row delete from t1; @@ -313,14 +313,14 @@ create or replace table t3 with system versioning select * from t1 for system_ti show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `x` int(11) DEFAULT NULL, + `x23` int(11) DEFAULT NULL, `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, `y` int(11) DEFAULT NULL, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING select * from t3 for system_time all where sys_trx_start = @sys_trx_start and sys_trx_end = @sys_trx_end; -x y +x23 y 1 3 create or replace table t2 like t0; insert into t2 values (1), (2); @@ -335,40 +335,46 @@ select y from t2 for system_time all where st = @st and en = @en; y 2 ## Default engine detection -create or replace table t1 (a int) with system versioning engine NON_DEFAULT_ENGINE; +create or replace table t1 (x25 int) with system versioning engine NON_DEFAULT_ENGINE; create or replace table t2 -as select a, sys_trx_start, sys_trx_end from t1 for system_time all; +as select x25, sys_trx_start, sys_trx_end from t1 for system_time all; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` int(11) DEFAULT NULL, - `sys_trx_start` NON_SYS_DATATYPE DEFAULT NULL, - `sys_trx_end` NON_SYS_DATATYPE DEFAULT NULL + `x25` int(11) DEFAULT NULL, + `sys_trx_start` SYS_DATATYPE, + `sys_trx_end` SYS_DATATYPE ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 create or replace table t2 with system versioning -as select a, sys_trx_start, sys_trx_end from t1; +as select x25, sys_trx_start, sys_trx_end from t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` int(11) DEFAULT NULL, - `sys_trx_start` NON_SYS_DATATYPE GENERATED ALWAYS AS ROW START, - `sys_trx_end` NON_SYS_DATATYPE GENERATED ALWAYS AS ROW END, + `x25` int(11) DEFAULT NULL, + `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, + `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=NON_DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING -create or replace table t2 with system versioning engine DEFAULT_ENGINE -as select a, sys_trx_start, sys_trx_end from t1 for system_time all; -ERROR HY000: `sys_trx_start` must be of type SYS_DATATYPE for versioned table `t2` -create or replace table t1 (a int, id int) with system versioning engine NON_DEFAULT_ENGINE; +create or replace table t1 ( +x26 int, +st bigint unsigned generated always as row start, +en bigint unsigned generated always 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; +ERROR HY000: `st` must be of type TIMESTAMP(6) for temporal table `t2` +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.a, t1.sys_trx_start, t1.sys_trx_end from t2 inner join t1 on t2.id=t1.id; +as select t2.b, t1.x27, t1.sys_trx_start, t1.sys_trx_end from t2 inner join t1 on t2.id=t1.id; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `b` int(11) DEFAULT NULL, - `a` int(11) DEFAULT NULL, - `sys_trx_start` NON_SYS_DATATYPE GENERATED ALWAYS AS ROW START, - `sys_trx_end` NON_SYS_DATATYPE GENERATED ALWAYS AS ROW END, + `x27` int(11) DEFAULT NULL, + `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, + `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=NON_DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING ## Errors @@ -378,10 +384,10 @@ ERROR 42S21: Duplicate column name 'sys_trx_start' create or replace table t (sys_trx_end int); alter table t with system versioning; ERROR 42S21: Duplicate column name 'sys_trx_end' -create or replace temporary table t (x int) with system versioning; +create or replace temporary table t (x28 int) with system versioning; ERROR HY000: Incorrect usage of TEMPORARY and WITH SYSTEM VERSIONING create or replace table t1 ( -x11 int unsigned, +x29 int unsigned, Sys_start0 timestamp(6) generated always as row start, Sys_start timestamp(6) generated always as row start, Sys_end timestamp(6) generated always as row end, @@ -389,7 +395,7 @@ period for system_time (Sys_start, Sys_end) ) with system versioning; ERROR HY000: Duplicate ROW START column `Sys_start` ## System fields detection -create or replace table t1 (x int) with system versioning; +create or replace table t1 (x30 int) with system versioning; create or replace table t2 ( y int, st SYS_DATATYPE generated always as row start, @@ -397,11 +403,11 @@ en SYS_DATATYPE generated always as row end, period for system_time (st, en) ) with system versioning; create or replace table t3 -as select x, y, sys_trx_start, sys_trx_end, st, en from t1, t2; +as select x30, y, sys_trx_start, sys_trx_end, st, en from t1, t2; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `x` int(11) DEFAULT NULL, + `x30` int(11) DEFAULT NULL, `y` int(11) DEFAULT NULL, `sys_trx_start` SYS_DATATYPE, `sys_trx_end` SYS_DATATYPE, @@ -414,11 +420,11 @@ st SYS_DATATYPE generated always as row start, en SYS_DATATYPE generated always as row end, period for system_time (st, en) ) with system versioning -as select x, y, sys_trx_start, sys_trx_end, st, en from t1, t2; +as select x30, y, sys_trx_start, sys_trx_end, st, en from t1, t2; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `x` int(11) DEFAULT NULL, + `x30` int(11) DEFAULT NULL, `y` int(11) DEFAULT NULL, `sys_trx_start` SYS_DATATYPE, `sys_trx_end` SYS_DATATYPE, diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result index c8a3ea14ff8..2310103a07b 100644 --- a/mysql-test/suite/versioning/r/cte.result +++ b/mysql-test/suite/versioning/r/cte.result @@ -25,9 +25,9 @@ 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 vtq_commit_ts(max(sys_trx_start)) into @ts_1 from emp; +select max(sys_trx_start) into @ts_1 from emp; update emp set mgr=30 where name ="john"; -select vtq_commit_ts(sys_trx_start) into @ts_2 from emp where name="john"; +select sys_trx_start into @ts_2 from emp where name="john"; /* All report to 'Bill' */ with recursive ancestors diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index dc0f556ad86..5244fb57946 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -1,3 +1,6 @@ +################# +# Test RESTRICT # +################# create table parent( id int unique key ) engine innodb; @@ -25,6 +28,9 @@ parent_id 1 drop table child; drop table parent; +############################################## +# Test when clustered index is a foreign key # +############################################## create table parent( id int(10) unsigned unique key ) engine innodb; @@ -38,6 +44,9 @@ delete from parent where id = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) drop table child; drop table parent; +################ +# Test CASCADE # +################ create table parent( id int unique key ) engine innodb; @@ -49,6 +58,12 @@ on update cascade ) engine innodb with system versioning; insert into parent values(1); insert into child values(1); +## FIXME: #415 update of foreign constraints is disabled +call mtr.add_suppression("foreign key constraints in timestamp-based temporal table"); +delete from parent where id = 1; +ERROR 42000: Table 'parent' uses an extension that doesn't exist in this MariaDB version +delete from child where parent_id = 1; +## FIXME END delete from parent where id = 1; select * from child; parent_id @@ -102,6 +117,9 @@ on update restrict engine innodb; insert into parent (id) values (3); insert into child (id, parent_id) values (3, 3); +## FIXME: #415 update of foreign constraints is disabled +delete from child; +## FIXME END delete from parent; select * from child; id parent_id @@ -110,6 +128,9 @@ id parent_id 3 3 drop table child; drop table parent; +################# +# Test SET NULL # +################# create table parent( id int unique key ) engine innodb; @@ -123,28 +144,24 @@ insert into parent values(1); insert into child values(1); delete from child; insert into child values(1); +## FIXME: #415 update of foreign constraints is disabled +delete from child where parent_id = 1; +## FIXME END delete from parent where id = 1; select * from child; parent_id -NULL select * from child for system_time from timestamp '1-1-1' to timestamp now(6); parent_id 1 -NULL +1 delete from child; insert into parent values(1); insert into child values(1); -update parent set id=id+1; -select * from child; -parent_id -NULL -select * from child for system_time from timestamp '1-1-1' to timestamp now(6); -parent_id -1 -NULL -NULL drop table child; drop table parent; +########################### +# Parent table is foreign # +########################### create or replace table parent( id int unique key ) engine innodb with system versioning; @@ -170,6 +187,9 @@ update parent set id=2; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) drop table child; drop table parent; +################### +# crash on DELETE # +################### create or replace table a ( cola int(10) primary key, v_cola int(10) as (cola mod 10) virtual diff --git a/mysql-test/suite/versioning/r/insert.result b/mysql-test/suite/versioning/r/insert.result index 8752a5294dd..1891587a406 100644 --- a/mysql-test/suite/versioning/r/insert.result +++ b/mysql-test/suite/versioning/r/insert.result @@ -263,8 +263,6 @@ No A B C D 14 1 1 1 1 15 1 1 1 1 16 1 1 1 1 -17 1 1 1 1 -18 1 1 1 1 create table t1( x int unsigned, sys_start bigint unsigned generated always as row start, @@ -289,15 +287,18 @@ No A B C D set global transaction_registry= off; insert into t2(x) values (1); insert into t1(x) values (1); -ERROR HY000: Some versioned DML requires `transaction_registry` to be set to ON. +ERROR HY000: Temporal operation requires `mysql.transaction_registry` (@@system_versioning_transaction_registry). set global transaction_registry= on; create or replace table t1 ( x int, -y int as (x) virtual +y int as (x) virtual, +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) ) engine=innodb with system versioning; insert into t1 values (1, null); update t1 set x= x + 1; -select *, sys_trx_end = 18446744073709551615 as current from t1 for system_time all; +select x, y, sys_trx_end = 18446744073709551615 as current from t1 for system_time all; x y current 2 2 1 1 1 0 diff --git a/mysql-test/suite/versioning/r/optimized.result b/mysql-test/suite/versioning/r/optimized.result index 8be67793e12..c86a2de0153 100644 --- a/mysql-test/suite/versioning/r/optimized.result +++ b/mysql-test/suite/versioning/r/optimized.result @@ -17,59 +17,59 @@ a b b+0 1 NULL NULL 3 NULL NULL Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select * from t for system_time as of timestamp now(6); a b 1 NULL 3 NULL Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select count(*) from t for system_time as of timestamp now(6) group by b; count(*) 2 Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select * from t for system_time as of timestamp now(6) order by b asc; a b 1 NULL 3 NULL Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select * from t for system_time as of timestamp now(6) order by b desc; a b 1 NULL 3 NULL Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select * from t for system_time as of timestamp now(6) group by a having a=2; a b Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select * from t for system_time as of timestamp now(6) group by b having b=2; a b Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select a from t for system_time as of timestamp now(6) where b=2; a Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select a from t for system_time as of timestamp now(6) where b=NULL; a Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select a from t for system_time as of timestamp now(6) where b is NULL; a 1 3 Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select count(*), b from t for system_time as of timestamp now(6) group by b having b=NULL; count(*) b Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select a, b from t; a b 1 2 @@ -84,12 +84,12 @@ a b 1 NULL 3 NULL Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query select * from t for system_time as of timestamp now(6) where b is NULL; a b 1 NULL 3 NULL Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query -Warning 4112 Attempt to read unversioned field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query +Warning 4112 Attempt to read non-temporal field `b` in historical query drop table t; diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index b1092a5ee9b..bc4cd4035bc 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -1,4 +1,4 @@ -### check System Versioning and conventional partitioning +# Check conventional partitioning on temporal tables create table t1 (x int) with system versioning partition by range columns (x) ( @@ -28,16 +28,17 @@ x select * from t1 partition (p1); x 300 -### Engine change versioned/non-versioned prohibited -create or replace table t1 (i int) engine=MyISAM with system versioning partition by hash(i); -alter table t1 engine=InnoDB; -ERROR HY000: Not allowed for versioned `test`.`t1`. Change to/from native versioning engine is prohibited. -### check server-level partitioning +# Engine change native <-> non-native versioning prohibited +create or replace table t1 (i int) engine=DEFAULT_ENGINE with system versioning partition by hash(i); +alter table t1 engine=NON_DEFAULT_ENGINE; +ERROR HY000: Not allowed for temporal `test`.`t1`. Change to/from native system versioning engine is prohibited. +# Check server-level partitioning +## create errors create or replace table t1 (x int) partition by system_time ( partition p0 history, partition pn current); -ERROR HY000: Engine does not support System Versioning for `t1` +ERROR HY000: Transaction system versioning for `t1` is not supported create or replace table t1 (x int); alter table t1 partition by system_time ( @@ -72,6 +73,7 @@ with system versioning partition by system_time ( partition p0 history, partition pn current); +## alter table alter table t1 add partition ( partition p1 current); ERROR HY000: Wrong partitions consistency for `t1`: must have at least one HISTORY and exactly one last CURRENT @@ -83,14 +85,14 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `x` int(11) DEFAULT NULL, - `sys_trx_start` ${SYS_TRX_TYPE} GENERATED ALWAYS AS ROW START, - `sys_trx_end` ${SYS_TRX_TYPE} GENERATED ALWAYS AS ROW END, + `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, + `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) -) ENGINE=${INNODB_OR_MYISAM} DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME -(PARTITION `p0` HISTORY ENGINE = ${INNODB_OR_MYISAM}, - PARTITION `p1` HISTORY ENGINE = ${INNODB_OR_MYISAM}, - PARTITION `pn` CURRENT ENGINE = ${INNODB_OR_MYISAM}) +(PARTITION `p0` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) insert into t1 values (1), (2); alter table t1 drop partition pn; ERROR HY000: Wrong partitions consistency for `t1`: must have at least one HISTORY and exactly one last CURRENT @@ -101,6 +103,14 @@ select x from t1; x 1 2 +# 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; +## insert, delete, update create or replace table t1 (x int) with system versioning partition by system_time ( @@ -108,21 +118,20 @@ partition p0 history, partition pn current); set @now= now(6); insert into t1 values (1); -set @ts_start= sys_commit_ts('sys_trx_start'); -set @ts_end= sys_commit_ts('sys_trx_end'); -set @str= concat('select x, ', @ts_start, ' < @now as A, ', @ts_end, ' > @now as B from t1 partition (p0)'); +set @str= concat('select x, sys_trx_start < @now as A, sys_trx_end > @now as B from t1 partition (p0)'); prepare select_p0 from @str; -set @str= concat('select x, ', @ts_start, ' > @now as C, ', @ts_end, ' = timestamp\'2038-01-19 03:14:07.999999\' as D from t1 partition (pn)'); +set @str= concat('select x, sys_trx_start > @now as C, sys_trx_end = timestamp\'2038-01-19 03:14:07.999999\' as D from t1 partition (pn)'); prepare select_pn from @str; execute select_p0; x A B execute select_pn; x C D 1 1 1 -explain partitions select * from t1; +## pruning check +explain partitions select * from tN; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pn system NULL NULL NULL NULL 1 -set @str= concat('select ', @ts_start, ' from t1 partition (pn) into @ts0'); +N SIMPLE tN pn system NULL NULL NULL NULL N +set @str= concat('select sys_trx_start from t1 partition (pn) into @ts0'); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -133,7 +142,7 @@ x A B 1 1 1 execute select_pn; x C D -set @str= concat('select ', @ts_start, ' from t1 partition (p0) into @ts1'); +set @str= concat('select sys_trx_start from t1 partition (p0) into @ts1'); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -148,7 +157,7 @@ x A B execute select_pn; x C D 2 1 1 -set @str= concat('select ', @ts_start, ' from t1 partition (pn) into @ts0'); +set @str= concat('select sys_trx_start from t1 partition (pn) into @ts0'); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -163,15 +172,15 @@ x C D 3 1 1 drop prepare select_p0; drop prepare select_pn; -set @str= concat('select ', @ts_start, ' from t1 partition (p0) where x = 2 into @ts1'); +set @str= concat('select sys_trx_start from t1 partition (p0) where x = 2 into @ts1'); prepare stmt from @str; execute stmt; drop prepare stmt; -set @str= concat('select ', @ts_end, ' from t1 partition (p0) where x = 2 into @ts2'); +set @str= concat('select sys_trx_end from t1 partition (p0) where x = 2 into @ts2'); prepare stmt from @str; execute stmt; drop prepare stmt; -set @str= concat('select ', @ts_start, ' from t1 partition (pn) into @ts3'); +set @str= concat('select sys_trx_start from t1 partition (pn) into @ts3'); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -181,6 +190,7 @@ select @ts0 = @ts1; select @ts2 = @ts3; @ts2 = @ts3 1 +## rotation by LIMIT create or replace table t1 (x int) with system versioning partition by system_time limit 0 ( @@ -190,7 +200,7 @@ partition pn current); ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'LIMIT' create or replace table t1 (x int) with system versioning -partition by system_time limit 1 ( +partition by system_time limit 2 ( partition p0 history, partition p1 history, partition pn current); @@ -198,38 +208,44 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `x` int(11) DEFAULT NULL, - `sys_trx_start` ${SYS_TRX_TYPE} GENERATED ALWAYS AS ROW START, - `sys_trx_end` ${SYS_TRX_TYPE} GENERATED ALWAYS AS ROW END, + `sys_trx_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START, + `sys_trx_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) -) ENGINE=${INNODB_OR_MYISAM} DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING - PARTITION BY SYSTEM_TIME LIMIT 1 -(PARTITION `p0` HISTORY ENGINE = ${INNODB_OR_MYISAM}, - PARTITION `p1` HISTORY ENGINE = ${INNODB_OR_MYISAM}, - PARTITION `pn` CURRENT ENGINE = ${INNODB_OR_MYISAM}) +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME LIMIT 2 +(PARTITION `p0` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) alter table t1 drop partition non_existent; ERROR HY000: Error in list of partitions to DROP -insert into t1 values (1), (2); +insert into t1 values (1), (2), (3); select * from t1 partition (pn); x 1 2 +3 +### warn about partition switching delete from t1; Warnings: Note 4116 Versioned table `test`.`t1`: switching from partition `p0` to `p1` select * from t1 partition (p0); x 1 +2 select * from t1 partition (p1); x -2 -insert into t1 values (3); +3 +insert into t1 values (4), (5); +### warn about full partition delete from t1; Warnings: Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions -select * from t1 partition (p1); +select * from t1 partition (p1) order by x; x -2 3 +4 +5 +## rotation by INTERVAL create or replace table t1 (x int) with system versioning partition by system_time interval 0 second ( @@ -262,22 +278,26 @@ Note 4116 Versioned table `test`.`t1`: switching from partition `p0` to `p1` select * from t1 partition (p1); x 4 +## Subpartitions create or replace table t1 (x int) with system versioning -partition by system_time limit 1 +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); +insert into t1 (x) values (1), (2), (3), (4), (5); select * from t1 partition (pnsp0); x 1 3 +5 select * from t1 partition (pnsp1); x 2 +4 +### warn about partition switching and about full partition delete from t1; Warnings: Note 4116 Versioned table `test`.`t1`: switching from partition `p0` to `p1` @@ -285,14 +305,16 @@ Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTO select * from t1 partition (p0sp0); x 1 +3 select * from t1 partition (p0sp1); x select * from t1 partition (p1sp0); x -3 +5 select * from t1 partition (p1sp1); x 2 +4 create or replace table t1 (a bigint) with system versioning partition by range (a) diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result index 16f3d82d243..3d80f19a2ea 100644 --- a/mysql-test/suite/versioning/r/select.result +++ b/mysql-test/suite/versioning/r/select.result @@ -1,6 +1,9 @@ create or replace table t1 ( x int unsigned, -y int unsigned +y int unsigned, +sys_trx_start SYS_DATATYPE generated always as row start, +sys_trx_end SYS_DATATYPE generated always as row end, +period for system_time (sys_trx_start, sys_trx_end) ) with system versioning; insert into t1 (x, y) values (0, 100), @@ -199,7 +202,7 @@ A create or replace table t1 (x int); insert into t1 values (1); select * from t1 for system_time all; -ERROR HY000: System Versioning required: t1 +ERROR HY000: System versioning required: t1 create or replace table t1 (x int) with system versioning; insert into t1 values (1); select * from t1 for system_time all for update; @@ -307,8 +310,13 @@ ERROR HY000: SYSTEM_TIME is not allowed outside historical `t` # TRANSACTION/TIMESTAMP specifier in SYSTEM_TIME [MDEV-14645, Issue #396] create or replace table t1 (x int) with system versioning engine myisam; select * from t1 for system_time as of transaction 1; -ERROR HY000: Engine does not support System Versioning for `t1` -create or replace table t1 (x int) with system versioning engine innodb; +ERROR HY000: Transaction system versioning for `t1` is not supported +create or replace table t1 ( +x int, +sys_trx_start bigint unsigned generated always as row start, +sys_trx_end bigint unsigned generated always as row end, +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; @@ -318,28 +326,29 @@ select @trx_start < unix_timestamp(@ts) - 100 as trx_start_good; trx_start_good 1 ## TIMESTAMP specifier -select * from t1 for system_time as of timestamp @ts; +select x from t1 for system_time as of timestamp @ts; x 1 -select * from t1 for system_time as of timestamp unix_timestamp(@ts); +select x from t1 for system_time as of timestamp unix_timestamp(@ts); x 1 -select * from t1 for system_time as of timestamp @trx_start; +select x from t1 for system_time as of timestamp @trx_start; x +set @ts= timestamp'1-1-1 0:0:0'; ## TRANSACTION specifier -select * from t1 for system_time as of transaction @ts; +select x from t1 for system_time as of transaction @ts; x -select * from t1 for system_time as of transaction unix_timestamp(@ts); +select x from t1 for system_time as of transaction unix_timestamp(@ts); x -select * from t1 for system_time as of transaction @trx_start; +select x from t1 for system_time as of transaction @trx_start; x 1 ## no specifier (auto-detection) -select * from t1 for system_time as of @ts; +select x from t1 for system_time as of @ts; x -select * from t1 for system_time as of unix_timestamp(@ts); +select x from t1 for system_time as of unix_timestamp(@ts); x -select * from t1 for system_time as of @trx_start; +select x from t1 for system_time as of @trx_start; x 1 ### Issue #365, bug 4 (related to #226, optimized fields) @@ -398,7 +407,7 @@ select * from t1 where t = '00:00:00' and i > 0 and sys_trx_end <> '2012-12-12 0 pk i t drop view v1; drop table t1, t2; -call innodb_verify_vtq(34); +call verify_vtq_dummy(34); No A B C D 1 1 1 1 1 2 1 1 1 1 diff --git a/mysql-test/suite/versioning/r/select_sp.result b/mysql-test/suite/versioning/r/select_sp.result index a7b9aa9735c..dc84343847e 100644 --- a/mysql-test/suite/versioning/r/select_sp.result +++ b/mysql-test/suite/versioning/r/select_sp.result @@ -2,7 +2,6 @@ create procedure test_01() begin declare engine varchar(255) default default_engine(); declare sys_type varchar(255) default sys_datatype(default_engine()); -declare fields varchar(255) default sys_commit_ts('sys_start'); set @str= concat(' create table t1( x int unsigned, @@ -56,7 +55,6 @@ create or replace procedure test_02() begin declare engine varchar(255) default default_engine(); declare sys_type varchar(255) default sys_datatype(default_engine()); -declare fields varchar(255) default sys_commit_ts('sys_start'); set @str0= concat('( x int, y int, @@ -219,7 +217,7 @@ A create or replace table t1 (x int); insert into t1 values (1); select * from t1 for system_time all; -ERROR HY000: System Versioning required: t1 +ERROR HY000: System versioning required: t1 create or replace table t1 (x int) with system versioning; insert into t1 values (1); select * from t1 for system_time all for update; @@ -327,34 +325,5 @@ select * from (t1 for system_time all join t2 for system_time all) for system_ti ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 drop view v1; drop table t1, t2; -call innodb_verify_vtq(27); -No A B C D -1 1 1 1 1 -2 1 1 1 1 -3 1 1 1 1 -4 1 1 1 1 -5 1 1 1 1 -6 1 1 1 1 -7 1 1 1 1 -8 1 1 1 1 -9 1 1 1 1 -10 1 1 1 1 -11 1 1 1 1 -12 1 1 1 1 -13 1 1 1 1 -14 1 1 1 1 -15 1 1 1 1 -16 1 1 1 1 -17 1 1 1 1 -18 1 1 1 1 -19 1 1 1 1 -20 1 1 1 1 -21 1 1 1 1 -22 1 1 1 1 -23 1 1 1 1 -24 1 1 1 1 -25 1 1 1 1 -26 1 1 1 1 -27 1 1 1 1 drop procedure test_01; drop procedure test_02; diff --git a/mysql-test/suite/versioning/r/simple.result b/mysql-test/suite/versioning/r/simple.result index 8de6d24802c..3dc395fffda 100644 --- a/mysql-test/suite/versioning/r/simple.result +++ b/mysql-test/suite/versioning/r/simple.result @@ -18,16 +18,16 @@ with system versioning; select now() into @ts_0; insert into dept (dept_id, name) values (10, "accounting"); commit; -select vtq_commit_ts(sys_trx_start) into @ts_1 from dept where dept_id=10; +select sys_trx_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 vtq_commit_ts(sys_trx_start) into @ts_2 from emp where name="bill"; +select sys_trx_start into @ts_2 from emp where name="bill"; select * from emp; emp_id dept_id name salary 1 10 bill 1000 update emp set salary=2000 where name="bill"; commit; -select vtq_commit_ts(sys_trx_start) into @ts_3 from emp where name="bill"; +select sys_trx_start into @ts_3 from emp where name="bill"; select * from emp; emp_id dept_id name salary 1 10 bill 2000 diff --git a/mysql-test/suite/versioning/r/truncate.result b/mysql-test/suite/versioning/r/truncate.result index 23577a83b91..d01b2b2481b 100644 --- a/mysql-test/suite/versioning/r/truncate.result +++ b/mysql-test/suite/versioning/r/truncate.result @@ -1,6 +1,6 @@ create table t (a int); truncate t to system_time now(); -ERROR HY000: System Versioning required: t +ERROR HY000: System versioning required: t create or replace table t (a int) with system versioning; insert into t values (1); update t set a=2; diff --git a/mysql-test/suite/versioning/r/update.result b/mysql-test/suite/versioning/r/update.result index 797b309b887..601906e10ca 100644 --- a/mysql-test/suite/versioning/r/update.result +++ b/mysql-test/suite/versioning/r/update.result @@ -7,9 +7,9 @@ set @str= concat(' create table t1( x int unsigned, y int unsigned, - sys_start ', sys_type, ' generated always as row start, - sys_end ', sys_type, ' generated always as row end, - period for system_time (sys_start, sys_end)) + sys_trx_start ', sys_type, ' generated always as row start, + sys_trx_end ', sys_type, ' generated always as row end, + period for system_time (sys_trx_start, sys_trx_end)) with system versioning engine ', engine); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -40,7 +40,10 @@ set @str= concat(' create table t1 ( id bigint primary key, x int, - y int without system versioning) + y int without system versioning, + sys_trx_start ', sys_type, ' as row start, + sys_trx_end ', sys_type, ' as row end, + period for system_time (sys_trx_start, sys_trx_end)) with system versioning engine ', engine); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -62,8 +65,11 @@ begin set @str= concat(' create table t1 ( x int, - y int) - with system versioning + y 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 ', engine); prepare stmt from @str; execute stmt; drop prepare stmt; insert into t1 (x, y) values (1, 1), (2, 1), (3, 1), (4, 1), (5, 1); @@ -85,7 +91,10 @@ begin set @str= concat(' create table t1 ( id int primary key auto_increment, - x int) + x int, + sys_trx_start ', sys_type, ' as row start, + sys_trx_end ', sys_type, ' as row end, + period for system_time (sys_trx_start, sys_trx_end)) with system versioning engine ', engine); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -109,10 +118,10 @@ begin set @str= concat(' create table t1( x int unsigned, - sys_end ', sys_type, ' generated always as row end, - sys_start ', sys_type, ' generated always as row start, + sys_trx_end ', sys_type, ' generated always as row end, + sys_trx_start ', sys_type, ' generated always as row start, y int unsigned, - period for system_time (sys_start, sys_end), + period for system_time (sys_trx_start, sys_trx_end), primary key(x, y)) with system versioning engine ', engine); @@ -136,9 +145,9 @@ begin set @str= concat('( x int unsigned, y int unsigned, - sys_start ', sys_type, ' generated always as row start, - sys_end ', sys_type, ' generated always as row end, - period for system_time (sys_start, sys_end)) + sys_trx_start ', sys_type, ' generated always as row start, + sys_trx_end ', sys_type, ' generated always as row end, + period for system_time (sys_trx_start, sys_trx_end)) with system versioning engine ', engine); set @str2= concat('create table t1', @str); @@ -179,9 +188,13 @@ engine varchar(255), fields varchar(255)) begin set @str= concat('( - id bigint primary key, - name varchar(128) with system versioning, - salary bigint) + id bigint primary key without system versioning, + name varchar(128), + salary bigint without system versioning, + sys_trx_start ', sys_type, ' as row start, + sys_trx_end ', sys_type, ' as row end, + period for system_time (sys_trx_start, sys_trx_end)) + with system versioning engine ', engine); set @str2= concat('create table t1', @str); prepare stmt from @str2; execute stmt; drop prepare stmt; @@ -202,7 +215,40 @@ select @tmp2 = sys_trx_start as B2, salary from t2; drop table t1; drop table t2; end~~ -call test_01('timestamp(6)', 'myisam', 'sys_end'); +call test_01('timestamp(6)', 'myisam', 'sys_trx_end'); +x y +1 1000 +2 2000 +3 3000 +4 4000 +5 5000 +6 6000 +7 7000 +8 8000 +9 9000 +x y +1 1000 +2 2000 +3 3000 +4 4000 +5 5000 +6 6000 +7 7000 +8 8001 +9 9001 +x y +1 1000 +2 2000 +3 3000 +4 4000 +5 5000 +6 6000 +7 7000 +8 8001 +9 9001 +8 8000 +9 9000 +call test_01('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); x y 1 1000 2 2000 @@ -235,7 +281,7 @@ x y 9 9001 8 8000 9 9000 -call test_01('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_01('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); x y 1 1000 2 2000 @@ -272,12 +318,17 @@ call verify_vtq; No A B C D 1 1 1 1 1 2 1 1 1 1 -call test_02('timestamp(6)', 'myisam', 'sys_end'); +call test_02('timestamp(6)', 'myisam', 'sys_trx_end'); A1 x y 1 11 11 A2 x 1 11 -call test_02('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_02('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); +A1 x y +1 11 11 +A2 x +1 11 +call test_02('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); A1 x y 1 11 11 A2 x @@ -287,8 +338,8 @@ No A B C D 1 1 1 1 1 2 1 1 1 1 # Multiple UPDATE of same rows in single transaction create historical -# rows only once (applicable to InnoDB only). -call test_03('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +# rows only once (applicable to transaction-based only). +call test_03('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); x y current 1 1 1 2 2 1 @@ -303,7 +354,7 @@ call verify_vtq; No A B C D 1 1 1 1 1 2 1 1 1 1 -call test_04('timestamp(6)', 'myisam', 'sys_end'); +call test_04('timestamp(6)', 'myisam', 'sys_trx_end'); x x 1 @@ -311,7 +362,15 @@ x 2 x 3 -call test_04('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_04('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); +x +x +1 +x +2 +x +3 +call test_04('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); x x 1 @@ -324,7 +383,21 @@ No A B C D 1 1 1 1 1 2 1 1 1 1 3 1 1 1 1 -call test_05('timestamp(6)', 'myisam', 'sys_end'); +call test_05('timestamp(6)', 'myisam', 'sys_trx_end'); +x y +1 1000 +3 3000 +3 3001 +4 4000 +4 4444 +5 5000 +x y +1 1000 +3 3001 +4 4000 +4 4444 +5 5000 +call test_05('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); x y 1 1000 3 3000 @@ -338,7 +411,7 @@ x y 4 4000 4 4444 5 5000 -call test_05('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_05('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); x y 1 1000 3 3000 @@ -357,7 +430,56 @@ No A B C D 1 1 1 1 1 2 1 1 1 1 3 1 1 1 1 -call test_06('timestamp(6)', 'myisam', 'sys_end'); +call test_06('timestamp(6)', 'myisam', 'sys_trx_end'); +x y +1 1000 +2 2000 +3 3000 +4 4000 +5 5000 +6 6000 +7 7000 +8 8008 +9 9009 +8 8000 +9 9000 +x y +1 1000 +2 2000 +3 3000 +4 4000 +5 5000 +6 6000 +7 7000 +8 8008 +9 9009 +x y +1 1011 +2 2012 +3 3013 +4 4014 +5 5015 +6 6016 +7 7010 +8 8010 +9 9010 +1 1010 +2 2010 +3 3010 +4 4010 +5 5010 +6 6010 +x y +1 1011 +2 2012 +3 3013 +4 4014 +5 5015 +6 6016 +7 7010 +8 8010 +9 9010 +call test_06('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); x y 1 1000 2 2000 @@ -406,7 +528,7 @@ x y 7 7010 8 8010 9 9010 -call test_06('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_06('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); x y 1 1000 2 2000 @@ -460,7 +582,17 @@ No A B C D 1 1 1 1 1 2 1 1 1 1 3 1 1 1 1 -call test_07('timestamp(6)', 'myisam', 'sys_end'); +# Optimized fields +call test_07('timestamp(6)', 'myisam', 'sys_trx_end'); +A1 name +1 Jerry +A2 name +1 Jerry +B1 salary +1 2500 +B2 salary +1 2500 +call test_07('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); A1 name 1 Jerry A2 name @@ -469,7 +601,7 @@ B1 salary 1 2500 B2 salary 1 2500 -call test_07('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_07('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); A1 name 1 Jerry A2 name diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index b5154c2d86c..5d1b7c0644d 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -135,42 +135,58 @@ 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 -- source suite/versioning/common.inc create or replace table t( a int ) engine=innodb; -insert into t values(1); -select * from t; - ---error ER_VERS_FIELD_WRONG_TYPE alter table t - add column trx_start timestamp(6) generated always as row start, - add column trx_end timestamp(6) generated always as row end, + add column trx_start timestamp(6) as row start, + add column trx_end timestamp(6) as row end, 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_vtq; alter table t - add column trx_start bigint(20) unsigned generated always as row start, - add column trx_end bigint(20) unsigned generated always as row end, + add column trx_start bigint(20) unsigned as row start, + add column trx_end bigint(20) unsigned as row end, add period for system_time(trx_start, trx_end), add system versioning; call verify_vtq; show create table t; -alter table t drop column trx_start, drop column trx_end; +## FIXME: #413 TRT is not updated on ADD SYSTEM VERSIONING +# alter table t drop column trx_start, drop column trx_end; + +# call verify_vtq; +# alter table t drop system versioning, algorithm=copy; +# call verify_vtq; + +create or replace table t( + a int +) engine=innodb; + +insert into t values (1); + +## FIXME END -call verify_vtq; -alter table t drop system versioning, algorithm=copy; -call verify_vtq; alter table t add system versioning, algorithm=copy; call verify_vtq; + show create table t; update t set a= 2; select * from t for system_time all; -call verify_vtq; alter table t add column b int, algorithm=copy; show create table t; @@ -184,6 +200,9 @@ call verify_vtq; alter table t drop system versioning, algorithm=inplace; call verify_vtq; +## FIXME: #414 IB: inplace for VERS_TIMESTAMP versioning +if (0) +{ alter table t add system versioning, algorithm=inplace; call verify_vtq; show create table t; @@ -200,6 +219,9 @@ call verify_vtq; alter table t drop column b, algorithm=inplace; show create table t; select * from t for system_time all; +} +alter table t add system versioning; +## FIXME END alter table t drop system versioning, algorithm=copy; show create table t; @@ -215,28 +237,22 @@ 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; -call verify_vtq; ---replace_regex /'0-[- 0-9.:]+'/'...'/ ---error ER_DUP_ENTRY +--error ER_DUP_ENTRY, ER_DUP_ENTRY alter table t add b int not null unique; ---error ER_UNSUPPORTED_EXTENSION +--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; -call verify_vtq; select * from t; select * from t for system_time all; insert into t values (4, 0); select * from t for system_time all; -call verify_vtq; create or replace table t (a int) with system versioning; insert into t values (1), (2), (3); delete from t where a<3; ---replace_regex /'0-[- 0-9.:]+'/'...'/ ---error ER_DUP_ENTRY +--error ER_DUP_ENTRY, ER_DUP_ENTRY alter table t add b int not null unique; ---replace_regex /#sql-[0-9a-f_]*/#sql-temporary/ ---error ER_UNSUPPORTED_EXTENSION +--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; @@ -349,7 +365,7 @@ alter table t add system versioning; --error ER_VERS_ALREADY_VERSIONED alter table t add system versioning, drop system versioning; -set @@versioning_alter_history=keep; +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); diff --git a/mysql-test/suite/versioning/t/commit_id.test b/mysql-test/suite/versioning/t/commit_id.test index 1782544cffb..6f68630f0d9 100644 --- a/mysql-test/suite/versioning/t/commit_id.test +++ b/mysql-test/suite/versioning/t/commit_id.test @@ -1,7 +1,11 @@ -- source suite/versioning/common.inc create table t1( - id int auto_increment primary key) + id int auto_increment primary key, + sys_trx_start bigint unsigned generated always as row start, + sys_trx_end bigint unsigned generated always as row end, + period for system_time (sys_trx_start, sys_trx_end) +) with system versioning engine innodb; diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test index 0ab6c3cedef..3acb5eaa852 100644 --- a/mysql-test/suite/versioning/t/create.test +++ b/mysql-test/suite/versioning/t/create.test @@ -5,13 +5,6 @@ drop table if exists t1; --enable_warnings -let $non_sys_datatype= `select sys_datatype(non_default_engine())`; -let $non_sys_datatype_uc= `select upper(sys_datatype(non_default_engine()))`; -let $sys_datatype_null= $sys_datatype NULL DEFAULT NULL; -let $sys_datatype_default_null= $sys_datatype DEFAULT NULL; -let $sys_datatype_not_null= $sys_datatype NOT NULL DEFAULT '0000-00-00 00:00:00.000000'; -let $non_sys_datatype_null= $non_sys_datatype NULL; - --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE NULL '' eval create table t1 ( x1 int unsigned, @@ -133,52 +126,52 @@ create or replace table t1 ( # columns with/without system versioning create or replace table t1 ( - A1 int with system versioning, + x15 int with system versioning, B int ); --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE show create table t1; create or replace table t1 ( - A2 int with system versioning, + x16 int with system versioning, B int ) with system versioning; --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE show create table t1; create or replace table t1 ( - A3 int, + x17 int, B int without system versioning ); create or replace table t1 ( - A4 int, + x18 int, B int without system versioning ) with system versioning; --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE show create table t1; create or replace table t1 ( - A5 int with system versioning, + x19 int with system versioning, B int without system versioning ); --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE show create table t1; create or replace table t1 ( - A6 int with system versioning, + x20 int with system versioning, B int without system versioning ) with system versioning; --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE show create table t1; create or replace table t1 ( - A7 int without system versioning + x21 int without system versioning ); ---error ER_VERS_NO_COLS_DEFINED +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS create or replace table t1 ( - A8 int without system versioning + x22 int without system versioning ) with system versioning; # CREATE TABLE ... LIKE @@ -193,7 +186,7 @@ create temporary table tt1 like t1; show create table tt1; --echo # CREATE TABLE ... SELECT -create or replace table t1 (x int) with system versioning; +create or replace table t1 (x23 int) with system versioning; --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE eval create or replace table t0( y int, @@ -247,14 +240,14 @@ show create table 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 x from t1; +create or replace table t3 with system versioning select x23 from t1; --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE $sys_datatype_null SYS_DATATYPE $sys_datatype_not_null SYS_DATATYPE $sys_datatype_default_null SYS_DATATYPE show create table t3; select * from t3; --error ER_MISSING -create or replace table t3 with system versioning select x, sys_trx_start from t1; +create or replace table t3 with system versioning select x23, sys_trx_start from t1; --error ER_MISSING -create or replace table t3 with system versioning select x, sys_trx_end from t1; +create or replace table t3 with system versioning select x23, sys_trx_end from t1; --echo # Prepare checking for historical row delete from t1; @@ -281,29 +274,34 @@ 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 $non_sys_datatype NON_SYS_DATATYPE -eval create or replace table t1 (a int) with system versioning engine $non_default_engine; +--replace_result $non_default_engine NON_DEFAULT_ENGINE $sys_datatype SYS_DATATYPE +eval create or replace table t1 (x25 int) with system versioning engine $non_default_engine; create or replace table t2 -as select a, sys_trx_start, sys_trx_end from t1 for system_time all; ---replace_result $default_engine DEFAULT_ENGINE $non_sys_datatype NON_SYS_DATATYPE $non_sys_datatype_null NON_SYS_DATATYPE +as select x25, sys_trx_start, sys_trx_end from t1 for system_time all; +--replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE $sys_datatype_null SYS_DATATYPE show create table t2; create or replace table t2 with system versioning -as select a, sys_trx_start, sys_trx_end from t1; ---replace_result $non_default_engine NON_DEFAULT_ENGINE $non_sys_datatype NON_SYS_DATATYPE +as select x25, sys_trx_start, sys_trx_end from t1; +--replace_result $non_default_engine NON_DEFAULT_ENGINE $sys_datatype SYS_DATATYPE show create table t2; ---replace_result $default_engine DEFAULT_ENGINE $sys_datatype_uc SYS_DATATYPE +create or replace table t1 ( + x26 int, + st bigint unsigned generated always as row start, + en bigint unsigned generated always as row end, + period for system_time (st, en) +) with system versioning engine innodb; --error ER_VERS_FIELD_WRONG_TYPE -eval create or replace table t2 with system versioning engine $default_engine -as select a, sys_trx_start, sys_trx_end from t1 for system_time all; +create or replace table t2 with system versioning engine myisam +as select * from t1; --replace_result $non_default_engine NON_DEFAULT_ENGINE -eval create or replace table t1 (a int, id int) with system versioning 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.a, t1.sys_trx_start, t1.sys_trx_end from t2 inner join t1 on t2.id=t1.id; ---replace_result $non_default_engine NON_DEFAULT_ENGINE $non_sys_datatype NON_SYS_DATATYPE $non_sys_datatype_null NON_SYS_DATATYPE +as select t2.b, t1.x27, t1.sys_trx_start, t1.sys_trx_end from t2 inner join t1 on t2.id=t1.id; +--replace_result $non_default_engine NON_DEFAULT_ENGINE $sys_datatype SYS_DATATYPE $sys_datatype_null SYS_DATATYPE show create table t3; --echo ## Errors @@ -316,11 +314,11 @@ create or replace table t (sys_trx_end int); alter table t with system versioning; --error ER_WRONG_USAGE -create or replace temporary table t (x int) with system versioning; +create or replace temporary table t (x28 int) with system versioning; --error ER_VERS_DUPLICATE_ROW_START_END create or replace table t1 ( - x11 int unsigned, + x29 int unsigned, Sys_start0 timestamp(6) generated always as row start, Sys_start timestamp(6) generated always as row start, Sys_end timestamp(6) generated always as row end, @@ -328,7 +326,7 @@ create or replace table t1 ( ) with system versioning; --echo ## System fields detection -create or replace table t1 (x int) with system versioning; +create or replace table t1 (x30 int) with system versioning; --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE eval create or replace table t2 ( y int, @@ -338,7 +336,7 @@ eval create or replace table t2 ( ) with system versioning; create or replace table t3 -as select x, y, sys_trx_start, sys_trx_end, st, en from t1, t2; +as select x30, y, sys_trx_start, sys_trx_end, st, en from t1, t2; --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE $sys_datatype_null SYS_DATATYPE $sys_datatype_not_null SYS_DATATYPE $sys_datatype_default_null SYS_DATATYPE show create table t3; @@ -349,7 +347,7 @@ eval create or replace table t3 ( en $sys_datatype generated always as row end, period for system_time (st, en) ) with system versioning -as select x, y, sys_trx_start, sys_trx_end, st, en from t1, t2; +as select x30, y, sys_trx_start, sys_trx_end, st, en from t1, t2; --replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE $sys_datatype_null SYS_DATATYPE $sys_datatype_not_null SYS_DATATYPE $sys_datatype_default_null SYS_DATATYPE show create table t3; diff --git a/mysql-test/suite/versioning/t/cte.opt b/mysql-test/suite/versioning/t/cte.opt index 2757b0f472a..51c33158c7a 100644 --- a/mysql-test/suite/versioning/t/cte.opt +++ b/mysql-test/suite/versioning/t/cte.opt @@ -1,2 +1 @@ ---plugin-load=versioning --system-versioning-hide=implicit diff --git a/mysql-test/suite/versioning/t/cte.test b/mysql-test/suite/versioning/t/cte.test index 992ecbc6e05..b553a9c7114 100644 --- a/mysql-test/suite/versioning/t/cte.test +++ b/mysql-test/suite/versioning/t/cte.test @@ -30,10 +30,10 @@ insert into emp (emp_id, name, salary, dept_id, mgr) values (20, "john", 500, 10, 1), (30, "jane", 750, 10,1 ); -select vtq_commit_ts(max(sys_trx_start)) into @ts_1 from emp; +select max(sys_trx_start) into @ts_1 from emp; update emp set mgr=30 where name ="john"; -select vtq_commit_ts(sys_trx_start) into @ts_2 from emp where name="john"; +select sys_trx_start into @ts_2 from emp where name="john"; /* All report to 'Bill' */ with recursive diff --git a/mysql-test/suite/versioning/t/engines.combinations b/mysql-test/suite/versioning/t/engines.combinations new file mode 100644 index 00000000000..561c5656929 --- /dev/null +++ b/mysql-test/suite/versioning/t/engines.combinations @@ -0,0 +1,8 @@ +[timestamp] +default-storage-engine=innodb + +[trx_id] +default-storage-engine=innodb + +[myisam] +default-storage-engine=myisam diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index 0a4eadd89bb..92aa1deb120 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -1,8 +1,8 @@ -- source include/have_innodb.inc -################# -# Test RESTRICT # -################# +--echo ################# +--echo # Test RESTRICT # +--echo ################# create table parent( id int unique key @@ -34,9 +34,9 @@ select * from child for system_time from timestamp '1-1-1' to timestamp now(6); drop table child; drop table parent; -############################################## -# Test when clustered index is a foreign key # -############################################## +--echo ############################################## +--echo # Test when clustered index is a foreign key # +--echo ############################################## create table parent( id int(10) unsigned unique key @@ -56,9 +56,9 @@ delete from parent where id = 1; drop table child; drop table parent; -################ -# Test CASCADE # -################ +--echo ################ +--echo # Test CASCADE # +--echo ################ create table parent( id int unique key @@ -74,6 +74,12 @@ create table child( insert into parent values(1); insert into child values(1); +--echo ## FIXME: #415 update of foreign constraints is disabled +call mtr.add_suppression("foreign key constraints in timestamp-based temporal table"); +--error ER_UNSUPPORTED_EXTENSION +delete from parent where id = 1; +delete from child where parent_id = 1; +--echo ## FIXME END delete from parent where id = 1; select * from child; select * from child for system_time all; @@ -127,6 +133,9 @@ engine innodb; insert into parent (id) values (3); insert into child (id, parent_id) values (3, 3); +--echo ## FIXME: #415 update of foreign constraints is disabled +delete from child; +--echo ## FIXME END delete from parent; select * from child; select * from child for system_time all; @@ -134,9 +143,9 @@ select * from child for system_time all; drop table child; drop table parent; -################# -# Test SET NULL # -################# +--echo ################# +--echo # Test SET NULL # +--echo ################# create table parent( id int unique key @@ -154,6 +163,9 @@ insert into child values(1); delete from child; insert into child values(1); +--echo ## FIXME: #415 update of foreign constraints is disabled +delete from child where parent_id = 1; +--echo ## FIXME END delete from parent where id = 1; select * from child; select * from child for system_time from timestamp '1-1-1' to timestamp now(6); @@ -161,16 +173,21 @@ delete from child; insert into parent values(1); insert into child values(1); +## FIXME: #415 update of foreign constraints is disabled +if (0) +{ update parent set id=id+1; select * from child; select * from child for system_time from timestamp '1-1-1' to timestamp now(6); +} +## FIXME END drop table child; drop table parent; -########################### -# Parent table is foreign # -########################### +--echo ########################### +--echo # Parent table is foreign # +--echo ########################### create or replace table parent( id int unique key @@ -204,10 +221,10 @@ update parent set id=2; drop table child; drop table parent; +--echo ################### +--echo # crash on DELETE # +--echo ################### -################### -# crash on DELETE # -################### create or replace table a ( cola int(10) primary key, v_cola int(10) as (cola mod 10) virtual diff --git a/mysql-test/suite/versioning/t/insert.test b/mysql-test/suite/versioning/t/insert.test index c10b8a0a668..2945f3714ba 100644 --- a/mysql-test/suite/versioning/t/insert.test +++ b/mysql-test/suite/versioning/t/insert.test @@ -197,11 +197,14 @@ set global transaction_registry= on; # virtual columns create or replace table t1 ( x int, - y int as (x) virtual + y int as (x) virtual, + 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) ) engine=innodb with system versioning; insert into t1 values (1, null); update t1 set x= x + 1; -select *, sys_trx_end = 18446744073709551615 as current from t1 for system_time all; +select x, y, sys_trx_end = 18446744073709551615 as current from t1 for system_time all; create or replace table t1 (i int) with system versioning engine innodb; insert into t1 values (1),(2); diff --git a/mysql-test/suite/versioning/t/partition.combinations b/mysql-test/suite/versioning/t/partition.combinations new file mode 100644 index 00000000000..4d73ef5a5ea --- /dev/null +++ b/mysql-test/suite/versioning/t/partition.combinations @@ -0,0 +1,5 @@ +[timestamp] +default-storage-engine=innodb + +[myisam] +default-storage-engine=myisam diff --git a/mysql-test/suite/versioning/t/partition.opt b/mysql-test/suite/versioning/t/partition.opt index 51c33158c7a..a1e3d3466eb 100644 --- a/mysql-test/suite/versioning/t/partition.opt +++ b/mysql-test/suite/versioning/t/partition.opt @@ -1 +1,3 @@ --system-versioning-hide=implicit +--system-versioning-alter-history=keep + diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index 8e319243c9a..6c42e56b6f3 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -1,7 +1,7 @@ -- source include/have_partition.inc -- source suite/versioning/common.inc ---echo ### check System Versioning and conventional partitioning +--echo # Check conventional partitioning on temporal tables create table t1 (x int) with system versioning @@ -20,14 +20,15 @@ select * from t1 for system_time all; select * from t1 partition (p0); select * from t1 partition (p1); ---echo ### Engine change versioned/non-versioned prohibited +--echo # Engine change native <-> non-native versioning prohibited +--replace_result $default_engine DEFAULT_ENGINE eval create or replace table t1 (i int) 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 ### check server-level partitioning - -# create errors +--echo # Check server-level partitioning +--echo ## create errors --error ER_VERS_ENGINE_UNSUPPORTED create or replace table t1 (x int) partition by system_time ( @@ -74,7 +75,7 @@ partition by system_time ( partition p0 history, partition pn current); -# alter table +--echo ## alter table --error ER_VERS_WRONG_PARTS alter table t1 add partition ( partition p1 current); @@ -82,7 +83,7 @@ alter table t1 add partition ( alter table t1 add partition ( partition p1 history); ---replace_result InnoDB ${INNODB_OR_MYISAM} MyISAM ${INNODB_OR_MYISAM} "bigint(20) unsigned" ${SYS_TRX_TYPE} timestamp(6) ${SYS_TRX_TYPE} +--replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE show create table t1; insert into t1 values (1), (2); @@ -95,7 +96,15 @@ alter table t1 drop partition p0; select x from t1; -# insert, delete, update +--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; + +--echo ## insert, delete, update create or replace table t1 (x int) with system versioning partition by system_time ( @@ -104,21 +113,19 @@ partition by system_time ( set @now= now(6); insert into t1 values (1); -set @ts_start= sys_commit_ts('sys_trx_start'); -set @ts_end= sys_commit_ts('sys_trx_end'); -set @str= concat('select x, ', @ts_start, ' < @now as A, ', @ts_end, ' > @now as B from t1 partition (p0)'); +set @str= concat('select x, sys_trx_start < @now as A, sys_trx_end > @now as B from t1 partition (p0)'); prepare select_p0 from @str; -set @str= concat('select x, ', @ts_start, ' > @now as C, ', @ts_end, ' = timestamp\'2038-01-19 03:14:07.999999\' as D from t1 partition (pn)'); +set @str= concat('select x, sys_trx_start > @now as C, sys_trx_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; -# pruning check ---replace_result ALL system "Using where" "" +--echo ## pruning check +--replace_regex /\d/N/ /ALL/system/ /Using where// explain partitions select * from t1; -set @str= concat('select ', @ts_start, ' from t1 partition (pn) into @ts0'); +set @str= concat('select sys_trx_start from t1 partition (pn) into @ts0'); prepare stmt from @str; execute stmt; drop prepare stmt; set @now= now(6); @@ -126,7 +133,7 @@ delete from t1; execute select_p0; execute select_pn; -set @str= concat('select ', @ts_start, ' from t1 partition (p0) into @ts1'); +set @str= concat('select sys_trx_start from t1 partition (p0) into @ts1'); prepare stmt from @str; execute stmt; drop prepare stmt; select @ts0 = @ts1; @@ -136,7 +143,7 @@ insert into t1 values (2); execute select_p0; execute select_pn; -set @str= concat('select ', @ts_start, ' from t1 partition (pn) into @ts0'); +set @str= concat('select sys_trx_start from t1 partition (pn) into @ts0'); prepare stmt from @str; execute stmt; drop prepare stmt; set @now= now(6); @@ -147,17 +154,17 @@ execute select_pn; drop prepare select_p0; drop prepare select_pn; -set @str= concat('select ', @ts_start, ' from t1 partition (p0) where x = 2 into @ts1'); +set @str= concat('select sys_trx_start from t1 partition (p0) where x = 2 into @ts1'); prepare stmt from @str; execute stmt; drop prepare stmt; -set @str= concat('select ', @ts_end, ' from t1 partition (p0) where x = 2 into @ts2'); +set @str= concat('select sys_trx_end from t1 partition (p0) where x = 2 into @ts2'); prepare stmt from @str; execute stmt; drop prepare stmt; -set @str= concat('select ', @ts_start, ' from t1 partition (pn) into @ts3'); +set @str= concat('select sys_trx_start from t1 partition (pn) into @ts3'); prepare stmt from @str; execute stmt; drop prepare stmt; select @ts0 = @ts1; select @ts2 = @ts3; -# rotation by LIMIT +--echo ## rotation by LIMIT --error ER_PART_WRONG_VALUE create or replace table t1 (x int) with system versioning @@ -168,28 +175,30 @@ partition by system_time limit 0 ( create or replace table t1 (x int) with system versioning -partition by system_time limit 1 ( +partition by system_time limit 2 ( partition p0 history, partition p1 history, partition pn current); ---replace_result InnoDB ${INNODB_OR_MYISAM} MyISAM ${INNODB_OR_MYISAM} "bigint(20) unsigned" ${SYS_TRX_TYPE} timestamp(6) ${SYS_TRX_TYPE} +--replace_result $default_engine DEFAULT_ENGINE $sys_datatype SYS_DATATYPE show create table t1; --error ER_DROP_PARTITION_NON_EXISTENT alter table t1 drop partition non_existent; -insert into t1 values (1), (2); +insert into t1 values (1), (2), (3); select * from t1 partition (pn); +--echo ### warn about partition switching delete from t1; select * from t1 partition (p0); select * from t1 partition (p1); -insert into t1 values (3); +insert into t1 values (4), (5); +--echo ### warn about full partition delete from t1; -select * from t1 partition (p1); +select * from t1 partition (p1) order by x; -# rotation by INTERVAL +--echo ## rotation by INTERVAL --error ER_PART_WRONG_VALUE create or replace table t1 (x int) with system versioning @@ -215,20 +224,21 @@ insert into t1 values (4); delete from t1; select * from t1 partition (p1); -# Subpartitions +--echo ## Subpartitions create or replace table t1 (x int) with system versioning -partition by system_time limit 1 +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); +insert into t1 (x) values (1), (2), (3), (4), (5); select * from t1 partition (pnsp0); select * from t1 partition (pnsp1); +--echo ### warn about partition switching and about full partition delete from t1; select * from t1 partition (p0sp0); select * from t1 partition (p0sp1); @@ -256,7 +266,6 @@ create or replace table t1 (i int) engine=innodb partition by key(i); alter table t1 add system versioning; insert into t1 values(); - drop table t1; -- source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/select.test b/mysql-test/suite/versioning/t/select.test index 2bba0ab472f..ee3ccae98e5 100644 --- a/mysql-test/suite/versioning/t/select.test +++ b/mysql-test/suite/versioning/t/select.test @@ -3,9 +3,13 @@ # test_01 -create or replace table t1 ( +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( x int unsigned, - y int unsigned + y int unsigned, + sys_trx_start $sys_datatype_expl generated always as row start, + sys_trx_end $sys_datatype_expl generated always as row end, + period for system_time (sys_trx_start, sys_trx_end) ) with system versioning; insert into t1 (x, y) values @@ -21,7 +25,7 @@ insert into t1 (x, y) values (9, 109); set @t0= now(6); -if ($default_engine == 'InnoDB') +if ($MTR_COMBINATION_TRX_ID) { --disable_query_log select sys_trx_start from t1 limit 1 into @x0; @@ -33,7 +37,7 @@ 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 ($default_engine == 'InnoDB') +if ($MTR_COMBINATION_TRX_ID) { --disable_query_log set @x1= @t1; @@ -48,13 +52,13 @@ select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' select x as ALL_x, y from t1 for system_time all; --disable_query_log -if ($default_engine == 'InnoDB') +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 ($default_engine != 'InnoDB') +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 '0-0-0 0:0:0' to timestamp @t1; @@ -196,7 +200,13 @@ for system_time all as t; 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; -create or replace table t1 (x int) with system versioning engine innodb; + +create or replace table t1 ( + x int, + sys_trx_start bigint unsigned generated always as row start, + sys_trx_end bigint unsigned generated always as row end, + 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; @@ -206,19 +216,21 @@ select sys_trx_start from t1 for system_time all into @trx_start; select @trx_start < unix_timestamp(@ts) - 100 as trx_start_good; --echo ## TIMESTAMP specifier -select * from t1 for system_time as of timestamp @ts; -select * from t1 for system_time as of timestamp unix_timestamp(@ts); -select * from t1 for system_time as of timestamp @trx_start; +select x from t1 for system_time as of timestamp @ts; +select x from t1 for system_time as of timestamp unix_timestamp(@ts); +select x from t1 for system_time as of timestamp @trx_start; + +set @ts= timestamp'1-1-1 0:0:0'; --echo ## TRANSACTION specifier -select * from t1 for system_time as of transaction @ts; -select * from t1 for system_time as of transaction unix_timestamp(@ts); -select * from t1 for system_time as of transaction @trx_start; +select x from t1 for system_time as of transaction @ts; +select x from t1 for system_time as of transaction unix_timestamp(@ts); +select x from t1 for system_time as of transaction @trx_start; --echo ## no specifier (auto-detection) -select * from t1 for system_time as of @ts; -select * from t1 for system_time as of unix_timestamp(@ts); -select * from t1 for system_time as of @trx_start; +select x from t1 for system_time as of @ts; +select x from t1 for system_time as of unix_timestamp(@ts); +select x from t1 for system_time as of @trx_start; --echo ### Issue #365, bug 4 (related to #226, optimized fields) @@ -275,6 +287,6 @@ select * from t1 where t = '00:00:00' and i > 0 and sys_trx_end <> '2012-12-12 0 drop view v1; drop table t1, t2; -call innodb_verify_vtq(34); +call verify_vtq_dummy(34); -- source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/select_sp.test b/mysql-test/suite/versioning/t/select_sp.test index ac19473f716..328256ded32 100644 --- a/mysql-test/suite/versioning/t/select_sp.test +++ b/mysql-test/suite/versioning/t/select_sp.test @@ -6,7 +6,6 @@ create procedure test_01() begin declare engine varchar(255) default default_engine(); declare sys_type varchar(255) default sys_datatype(default_engine()); - declare fields varchar(255) default sys_commit_ts('sys_start'); set @str= concat(' create table t1( @@ -67,7 +66,6 @@ create or replace procedure test_02() begin declare engine varchar(255) default default_engine(); declare sys_type varchar(255) default sys_datatype(default_engine()); - declare fields varchar(255) default sys_commit_ts('sys_start'); set @str0= concat('( x int, @@ -200,8 +198,6 @@ select * from (t1 for system_time all join t2 for system_time all) for system_ti drop view v1; drop table t1, t2; -call innodb_verify_vtq(27); - drop procedure test_01; drop procedure test_02; diff --git a/mysql-test/suite/versioning/t/simple.opt b/mysql-test/suite/versioning/t/simple.opt deleted file mode 100644 index a0dbb2d9e7a..00000000000 --- a/mysql-test/suite/versioning/t/simple.opt +++ /dev/null @@ -1 +0,0 @@ ---plugin-load=versioning diff --git a/mysql-test/suite/versioning/t/simple.test b/mysql-test/suite/versioning/t/simple.test index 4a288b780ea..516649aa137 100644 --- a/mysql-test/suite/versioning/t/simple.test +++ b/mysql-test/suite/versioning/t/simple.test @@ -23,19 +23,19 @@ select now() into @ts_0; insert into dept (dept_id, name) values (10, "accounting"); commit; -select vtq_commit_ts(sys_trx_start) into @ts_1 from dept where dept_id=10; +select sys_trx_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 vtq_commit_ts(sys_trx_start) into @ts_2 from emp where name="bill"; +select sys_trx_start into @ts_2 from emp where name="bill"; select * from emp; update emp set salary=2000 where name="bill"; commit; -select vtq_commit_ts(sys_trx_start) into @ts_3 from emp where name="bill"; +select sys_trx_start into @ts_3 from emp where name="bill"; select * from emp; select * from emp for system_time as of timestamp @ts_2; diff --git a/mysql-test/suite/versioning/t/update.test b/mysql-test/suite/versioning/t/update.test index 7f72384b6e5..5c53b3fd6fc 100644 --- a/mysql-test/suite/versioning/t/update.test +++ b/mysql-test/suite/versioning/t/update.test @@ -10,9 +10,9 @@ begin create table t1( x int unsigned, y int unsigned, - sys_start ', sys_type, ' generated always as row start, - sys_end ', sys_type, ' generated always as row end, - period for system_time (sys_start, sys_end)) + sys_trx_start ', sys_type, ' generated always as row start, + sys_trx_end ', sys_type, ' generated always as row end, + period for system_time (sys_trx_start, sys_trx_end)) with system versioning engine ', engine); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -44,7 +44,10 @@ begin create table t1 ( id bigint primary key, x int, - y int without system versioning) + y int without system versioning, + sys_trx_start ', sys_type, ' as row start, + sys_trx_end ', sys_type, ' as row end, + period for system_time (sys_trx_start, sys_trx_end)) with system versioning engine ', engine); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -69,8 +72,11 @@ begin set @str= concat(' create table t1 ( x int, - y int) - with system versioning + y 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 ', engine); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -97,7 +103,10 @@ begin set @str= concat(' create table t1 ( id int primary key auto_increment, - x int) + x int, + sys_trx_start ', sys_type, ' as row start, + sys_trx_end ', sys_type, ' as row end, + period for system_time (sys_trx_start, sys_trx_end)) with system versioning engine ', engine); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -125,10 +134,10 @@ begin set @str= concat(' create table t1( x int unsigned, - sys_end ', sys_type, ' generated always as row end, - sys_start ', sys_type, ' generated always as row start, + sys_trx_end ', sys_type, ' generated always as row end, + sys_trx_start ', sys_type, ' generated always as row start, y int unsigned, - period for system_time (sys_start, sys_end), + period for system_time (sys_trx_start, sys_trx_end), primary key(x, y)) with system versioning engine ', engine); @@ -153,9 +162,9 @@ begin set @str= concat('( x int unsigned, y int unsigned, - sys_start ', sys_type, ' generated always as row start, - sys_end ', sys_type, ' generated always as row end, - period for system_time (sys_start, sys_end)) + sys_trx_start ', sys_type, ' generated always as row start, + sys_trx_end ', sys_type, ' generated always as row end, + period for system_time (sys_trx_start, sys_trx_end)) with system versioning engine ', engine); set @str2= concat('create table t1', @str); @@ -197,9 +206,13 @@ create procedure test_07( fields varchar(255)) begin set @str= concat('( - id bigint primary key, - name varchar(128) with system versioning, - salary bigint) + id bigint primary key without system versioning, + name varchar(128), + salary bigint without system versioning, + sys_trx_start ', sys_type, ' as row start, + sys_trx_end ', sys_type, ' as row end, + period for system_time (sys_trx_start, sys_trx_end)) + with system versioning engine ', engine); set @str2= concat('create table t1', @str); @@ -227,34 +240,40 @@ begin end~~ delimiter ;~~ -call test_01('timestamp(6)', 'myisam', 'sys_end'); -call test_01('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_01('timestamp(6)', 'myisam', 'sys_trx_end'); +call test_01('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); +call test_01('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); call verify_vtq; -call test_02('timestamp(6)', 'myisam', 'sys_end'); -call test_02('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_02('timestamp(6)', 'myisam', 'sys_trx_end'); +call test_02('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); +call test_02('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); call verify_vtq; --echo # Multiple UPDATE of same rows in single transaction create historical ---echo # rows only once (applicable to InnoDB only). -call test_03('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +--echo # rows only once (applicable to transaction-based only). +call test_03('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); call verify_vtq; -call test_04('timestamp(6)', 'myisam', 'sys_end'); -call test_04('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_04('timestamp(6)', 'myisam', 'sys_trx_end'); +call test_04('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); +call test_04('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); call verify_vtq; -call test_05('timestamp(6)', 'myisam', 'sys_end'); -call test_05('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_05('timestamp(6)', 'myisam', 'sys_trx_end'); +call test_05('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); +call test_05('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); call verify_vtq; -call test_06('timestamp(6)', 'myisam', 'sys_end'); -call test_06('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); +call test_06('timestamp(6)', 'myisam', 'sys_trx_end'); +call test_06('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); +call test_06('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); call verify_vtq; -call test_07('timestamp(6)', 'myisam', 'sys_end'); -call test_07('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_end)'); - +--echo # Optimized fields +call test_07('timestamp(6)', 'myisam', 'sys_trx_end'); +call test_07('timestamp(6)', 'innodb', 'vtq_commit_ts(sys_trx_end)'); +call test_07('bigint unsigned', 'innodb', 'vtq_commit_ts(sys_trx_end)'); call verify_vtq; --echo ### Issue #365, bug 7 (duplicate of historical row) diff --git a/sql/field.cc b/sql/field.cc index d4120e22a1d..4014fca818f 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -2008,7 +2008,7 @@ bool Field_vers_trx_id::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate, ulonglo DBUG_ASSERT(ltime); if (!table || !table->s) return true; - DBUG_ASSERT(table->versioned_by_engine() || + DBUG_ASSERT(table->versioned(VERS_TRX_ID) || (table->versioned() && table->s->table_category == TABLE_CATEGORY_TEMPORARY)); if (!trx_id) return true; diff --git a/sql/field.h b/sql/field.h index 64294afc7bb..addd1e95f38 100644 --- a/sql/field.h +++ b/sql/field.h @@ -4659,14 +4659,14 @@ bool check_expression(Virtual_column_info *vcol, LEX_CSTRING *name, inline ulonglong TABLE::vers_end_id() const { - DBUG_ASSERT(versioned_by_engine()); + DBUG_ASSERT(versioned(VERS_TRX_ID)); return static_cast<ulonglong>(vers_end_field()->val_int()); } inline ulonglong TABLE::vers_start_id() const { - DBUG_ASSERT(versioned_by_engine()); + DBUG_ASSERT(versioned(VERS_TRX_ID)); return static_cast<ulonglong>(vers_start_field()->val_int()); } diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index faf61c26c66..da274bbdf67 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -2118,7 +2118,7 @@ void ha_partition::update_create_info(HA_CREATE_INFO *create_info) HA_STATUS_AUTO is optimized so it will not always be forwarded to all partitions, but HA_STATUS_VARIABLE will. */ - info(HA_STATUS_VARIABLE); + info(HA_STATUS_VARIABLE | HA_STATUS_OPEN); info(HA_STATUS_AUTO); @@ -3626,7 +3626,7 @@ int ha_partition::open(const char *name, int mode, uint test_if_locked) m_part_info->part_expr->get_monotonicity_info(); else if (m_part_info->list_of_part_fields) m_part_func_monotonicity_info= MONOTONIC_STRICT_INCREASING; - info(HA_STATUS_VARIABLE | HA_STATUS_CONST); + info(HA_STATUS_VARIABLE | HA_STATUS_CONST | HA_STATUS_OPEN); DBUG_RETURN(0); err_handler: diff --git a/sql/ha_partition.h b/sql/ha_partition.h index 0b9f1dc3953..209b9531469 100644 --- a/sql/ha_partition.h +++ b/sql/ha_partition.h @@ -1487,7 +1487,7 @@ public: { handler *file= m_file[part_id]; DBUG_ASSERT(bitmap_is_set(&(m_part_info->read_partitions), part_id)); - file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); + file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK | HA_STATUS_OPEN); part_recs+= file->stats.records; } return part_recs; diff --git a/sql/handler.cc b/sql/handler.cc index 1c237e22015..6d275a4396f 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -4317,6 +4317,9 @@ handler::check_if_supported_inplace_alter(TABLE *altered_table, HA_CREATE_INFO *create_info= ha_alter_info->create_info; + if (altered_table->versioned(VERS_TIMESTAMP)) + DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED); + Alter_inplace_info::HA_ALTER_FLAGS inplace_offline_operations= Alter_inplace_info::ALTER_COLUMN_EQUAL_PACK_LENGTH | Alter_inplace_info::ALTER_COLUMN_NAME | @@ -5777,7 +5780,7 @@ bool ha_show_status(THD *thd, handlerton *db_type, enum ha_stat_type stat) bool handler::check_table_binlog_row_based(bool binlog_row) { - if (table->versioned_by_engine()) + if (table->versioned(VERS_TRX_ID)) return false; if (unlikely((table->in_use->variables.sql_log_bin_off))) return 0; /* Called by partitioning engine */ @@ -6758,7 +6761,7 @@ bool Vers_parse_info::is_end(const Create_field &f) const } static Create_field *vers_init_sys_field(THD *thd, const char *field_name, - int flags, bool integer_fields) + int flags) { Create_field *f= new (thd->mem_root) Create_field(); if (!f) @@ -6769,17 +6772,8 @@ static Create_field *vers_init_sys_field(THD *thd, const char *field_name, f->field_name.length= strlen(field_name); f->charset= system_charset_info; f->flags= flags | VERS_HIDDEN_FLAG; - if (integer_fields) - { - f->set_handler(&type_handler_longlong); - f->flags|= UNSIGNED_FLAG; - f->length= MY_INT64_NUM_DECIMAL_DIGITS - 1; - } - else - { - f->set_handler(&type_handler_timestamp2); - f->length= MAX_DATETIME_PRECISION; - } + f->set_handler(&type_handler_timestamp2); + f->length= MAX_DATETIME_PRECISION; if (f->check(thd)) return NULL; @@ -6788,10 +6782,9 @@ static Create_field *vers_init_sys_field(THD *thd, const char *field_name, } static bool vers_create_sys_field(THD *thd, const char *field_name, - Alter_info *alter_info, int flags, - bool integer_fields) + Alter_info *alter_info, int flags) { - Create_field *f= vers_init_sys_field(thd, field_name, flags, integer_fields); + Create_field *f= vers_init_sys_field(thd, field_name, flags); if (!f) return true; @@ -6803,9 +6796,9 @@ static bool vers_create_sys_field(THD *thd, const char *field_name, static bool vers_change_sys_field(THD *thd, const char *field_name, Alter_info *alter_info, int flags, - bool integer_fields, const char *change) + const char *change) { - Create_field *f= vers_init_sys_field(thd, field_name, flags, integer_fields); + Create_field *f= vers_init_sys_field(thd, field_name, flags); if (!f) return true; @@ -6821,8 +6814,7 @@ static bool vers_change_sys_field(THD *thd, const char *field_name, const LString Vers_parse_info::default_start= "sys_trx_start"; const LString Vers_parse_info::default_end= "sys_trx_end"; -bool Vers_parse_info::fix_implicit(THD *thd, Alter_info *alter_info, - bool integer_fields, int *added) +bool Vers_parse_info::fix_implicit(THD *thd, Alter_info *alter_info, int *added) { // If user specified some of these he must specify the others too. Do nothing. if (*this) @@ -6833,8 +6825,8 @@ bool Vers_parse_info::fix_implicit(THD *thd, Alter_info *alter_info, system_time= start_end_t(default_start, default_end); as_row= system_time; - if (vers_create_sys_field(thd, default_start, alter_info, VERS_SYS_START_FLAG, integer_fields) || - vers_create_sys_field(thd, default_end, alter_info, VERS_SYS_END_FLAG, integer_fields)) + if (vers_create_sys_field(thd, default_start, alter_info, VERS_SYS_START_FLAG) || + vers_create_sys_field(thd, default_end, alter_info, VERS_SYS_END_FLAG)) { return true; } @@ -7008,9 +7000,8 @@ bool Table_scope_and_contents_source_st::vers_fix_system_fields( } // while (Create_field *f= it++) } // if (vers_tables) - bool integer_fields= vers_native(thd); int added= 0; - if (vers_info.fix_implicit(thd, alter_info, integer_fields, &added)) + if (vers_info.fix_implicit(thd, alter_info, &added)) return true; DBUG_ASSERT(added >= 0); @@ -7045,12 +7036,18 @@ bool Table_scope_and_contents_source_st::vers_fix_system_fields( vers_cols == 0 && (plain_cols == 0 || !vers_info)) { - my_error(ER_VERS_NO_COLS_DEFINED, MYF(0), create_table.table_name); + my_error(ER_VERS_TABLE_MUST_HAVE_COLUMNS, MYF(0), create_table.table_name); return true; } - return vers_info.check_with_conditions(create_table.table_name) || - vers_info.check_generated_type(create_table.table_name, alter_info, integer_fields); + if (vers_info.check_with_conditions(create_table.table_name)) + return true; + + bool native= vers_native(thd); + if (vers_info.check_sys_fields(create_table.table_name, alter_info, native)) + return true; + + return false; } static bool add_field_to_drop_list(THD *thd, Alter_info *alter_info, @@ -7092,9 +7089,6 @@ bool Vers_parse_info::fix_alter_info(THD *thd, Alter_info *alter_info, TABLE *table) { TABLE_SHARE *share= table->s; - bool integer_fields= create_info->vers_native(thd); - if (create_info->db_type->db_type == DB_TYPE_PARTITION_DB && table->file->native_versioned()) - integer_fields= true; const char *table_name= share->table_name.str; if (!need_check() && !share->versioned) @@ -7268,9 +7262,7 @@ bool Vers_parse_info::fix_alter_info(THD *thd, Alter_info *alter_info, } if (vers_change_sys_field(thd, name, alter_info, - f->flags & - (VERS_SYS_START_FLAG | VERS_SYS_END_FLAG), - integer_fields, name)) + f->flags & VERS_SYSTEM_FIELD, name)) { return true; } @@ -7285,10 +7277,19 @@ bool Vers_parse_info::fix_alter_info(THD *thd, Alter_info *alter_info, return false; } - return fix_implicit(thd, alter_info, integer_fields) || - (with_system_versioning && - (check_with_conditions(table_name) || - check_generated_type(table_name, alter_info, integer_fields))); + if (fix_implicit(thd, alter_info)) + return true; + + if (with_system_versioning) + { + if (check_with_conditions(table_name)) + return true; + bool native= create_info->vers_native(thd); + if (check_sys_fields(table_name, alter_info, native)) + return true; + } + + return false; } bool @@ -7374,38 +7375,76 @@ bool Vers_parse_info::check_with_conditions(const char *table_name) const return false; } -bool Vers_parse_info::check_generated_type(const char *table_name, - Alter_info *alter_info, - bool integer_fields) const +bool Vers_parse_info::check_sys_fields(const char *table_name, + Alter_info *alter_info, + bool native) const { List_iterator<Create_field> it(alter_info->create_list); + vers_sys_type_t found= VERS_UNDEFINED; + uint found_flag= 0; while (Create_field *f= it++) { - if (is_start(*f) || is_end(*f)) + vers_sys_type_t check_unit= VERS_UNDEFINED; + uint sys_flag= f->flags & VERS_SYSTEM_FIELD; + + if (!sys_flag) + continue; + + if (sys_flag & found_flag) { - if (integer_fields) - { - if (f->type_handler() != &type_handler_longlong || !(f->flags & UNSIGNED_FLAG) || - f->length != (MY_INT64_NUM_DECIMAL_DIGITS - 1)) - { - my_error(ER_VERS_FIELD_WRONG_TYPE, MYF(0), f->field_name.str, - "BIGINT(20) UNSIGNED", table_name); - return true; - } - } - else + my_error(ER_VERS_DUPLICATE_ROW_START_END, MYF(0), + found_flag & VERS_SYS_START_FLAG ? "START" : "END", f->field_name.str); + return true; + } + + sys_flag|= found_flag; + + if ((f->type_handler() == &type_handler_datetime2 || + f->type_handler() == &type_handler_timestamp2) && + f->length == MAX_DATETIME_FULL_WIDTH) + { + check_unit= VERS_TIMESTAMP; + } + else if (native + && f->type_handler() == &type_handler_longlong + && (f->flags & UNSIGNED_FLAG) + && f->length == (MY_INT64_NUM_DECIMAL_DIGITS - 1)) + { + check_unit= VERS_TRX_ID; + } + else + { + if (!found) + found= VERS_TIMESTAMP; + goto error; + } + + if (check_unit) + { + if (found) { - if (!(f->type_handler() == &type_handler_datetime2 || - f->type_handler() == &type_handler_timestamp2) || - f->length != MAX_DATETIME_FULL_WIDTH) + if (found == check_unit) { - my_error(ER_VERS_FIELD_WRONG_TYPE, MYF(0), f->field_name.str, - "TIMESTAMP(6)", table_name); - return true; + if (found == VERS_TRX_ID && !use_transaction_registry) + { + my_error(ER_VERS_TRT_IS_DISABLED, MYF(0)); + return true; + } + return false; } + error: + my_error(ER_VERS_FIELD_WRONG_TYPE, MYF(0), f->field_name.str, + found == VERS_TIMESTAMP ? + "TIMESTAMP(6)" : + "BIGINT(20) UNSIGNED", + table_name); + return true; } + found= check_unit; } } - return false; + my_error(ER_MISSING, MYF(0), table_name, found_flag & VERS_SYS_START_FLAG ? + "ROW END" : found_flag ? "ROW START" : "ROW START/END"); + return true; } diff --git a/sql/handler.h b/sql/handler.h index c29a10c393f..d73661b9a77 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -1757,7 +1757,7 @@ protected: bool is_end(const char *name) const; bool is_start(const Create_field &f) const; bool is_end(const Create_field &f) const; - bool fix_implicit(THD *thd, Alter_info *alter_info, bool integer_fields, int *added= NULL); + bool fix_implicit(THD *thd, Alter_info *alter_info, int *added= NULL); operator bool() const { return as_row.start || as_row.end || system_time.start || system_time.end; @@ -1772,8 +1772,8 @@ protected: *this; } bool check_with_conditions(const char *table_name) const; - bool check_generated_type(const char *table_name, Alter_info *alter_info, - bool integer_fields) const; + bool check_sys_fields(const char *table_name, Alter_info *alter_info, + bool native) const; public: static const LString default_start; diff --git a/sql/log_event.cc b/sql/log_event.cc index 397d1b6bef5..1256da729f0 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -12507,7 +12507,7 @@ Rows_log_event::write_row(rpl_group_info *rgi, // Handle INSERT. // Set vers fields when replicating from not system-versioned table. - if (m_type == WRITE_ROWS_EVENT_V1 && table->versioned_by_sql()) + if (m_type == WRITE_ROWS_EVENT_V1 && table->versioned(VERS_TIMESTAMP)) { ulong sec_part; bitmap_set_bit(table->read_set, table->vers_start_field()->field_index); @@ -13442,7 +13442,7 @@ int Delete_rows_log_event::do_exec_row(rpl_group_info *rgi) if (!error) { m_table->mark_columns_per_binlog_row_image(); - if (m_vers_from_plain && m_table->versioned_by_sql()) + if (m_vers_from_plain && m_table->versioned(VERS_TIMESTAMP)) { Field *end= m_table->vers_end_field(); bitmap_set_bit(m_table->write_set, end->field_index); @@ -13711,7 +13711,7 @@ Update_rows_log_event::do_exec_row(rpl_group_info *rgi) memcpy(m_table->write_set->bitmap, m_cols_ai.bitmap, (m_table->write_set->n_bits + 7) / 8); m_table->mark_columns_per_binlog_row_image(); - if (m_vers_from_plain && m_table->versioned_by_sql()) + if (m_vers_from_plain && m_table->versioned(VERS_TIMESTAMP)) { bitmap_set_bit(m_table->write_set, m_table->vers_start_field()->field_index); @@ -13721,7 +13721,7 @@ Update_rows_log_event::do_exec_row(rpl_group_info *rgi) error= m_table->file->ha_update_row(m_table->record[1], m_table->record[0]); if (error == HA_ERR_RECORD_IS_THE_SAME) error= 0; - if (m_vers_from_plain && m_table->versioned_by_sql()) + if (m_vers_from_plain && m_table->versioned(VERS_TIMESTAMP)) { store_record(m_table, record[2]); error= vers_insert_history_row(m_table); diff --git a/sql/log_event.h b/sql/log_event.h index 2dedd8bbe9a..4d84bc34a47 100644 --- a/sql/log_event.h +++ b/sql/log_event.h @@ -4746,7 +4746,7 @@ public: __attribute__((unused)), const uchar *after_record) { - DBUG_ASSERT(!table->versioned_by_engine()); + DBUG_ASSERT(!table->versioned(VERS_TRX_ID)); return thd->binlog_write_row(table, is_transactional, after_record); } #endif @@ -4828,7 +4828,7 @@ public: const uchar *before_record, const uchar *after_record) { - DBUG_ASSERT(!table->versioned_by_engine()); + DBUG_ASSERT(!table->versioned(VERS_TRX_ID)); return thd->binlog_update_row(table, is_transactional, before_record, after_record); } @@ -4918,7 +4918,7 @@ public: const uchar *after_record __attribute__((unused))) { - DBUG_ASSERT(!table->versioned_by_engine()); + DBUG_ASSERT(!table->versioned(VERS_TRX_ID)); return thd->binlog_delete_row(table, is_transactional, before_record); } diff --git a/sql/mysqld.h b/sql/mysqld.h index aedf2850186..2463f569c94 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -181,22 +181,22 @@ extern const char *log_output_str; extern const char *log_backup_output_str; /* System Versioning begin */ -enum vers_range_type_t +enum vers_system_time_t { - FOR_SYSTEM_TIME_UNSPECIFIED = 0, - FOR_SYSTEM_TIME_ALL, - FOR_SYSTEM_TIME_AS_OF, - FOR_SYSTEM_TIME_FROM_TO, - FOR_SYSTEM_TIME_BETWEEN, - FOR_SYSTEM_TIME_BEFORE + SYSTEM_TIME_UNSPECIFIED = 0, + SYSTEM_TIME_ALL, + SYSTEM_TIME_AS_OF, + SYSTEM_TIME_FROM_TO, + SYSTEM_TIME_BETWEEN, + SYSTEM_TIME_BEFORE }; -struct st_vers_asof_timestamp +struct vers_asof_timestamp_t { ulong type; MYSQL_TIME ltime; - st_vers_asof_timestamp() : - type(FOR_SYSTEM_TIME_UNSPECIFIED) + vers_asof_timestamp_t() : + type(SYSTEM_TIME_UNSPECIFIED) {} }; diff --git a/sql/partition_info.cc b/sql/partition_info.cc index 1f7ba06f875..31af3815cd5 100644 --- a/sql/partition_info.cc +++ b/sql/partition_info.cc @@ -953,7 +953,7 @@ bool partition_info::vers_setup_expression(THD * thd, uint32 alter_add) { DBUG_ASSERT(part_type == VERSIONING_PARTITION); - if (!table->versioned_by_sql()) + if (!table->versioned(VERS_TIMESTAMP)) { my_error(ER_VERS_ENGINE_UNSUPPORTED, MYF(0), table->s->table_name.str); return true; @@ -1084,7 +1084,7 @@ bool partition_info::vers_scan_min_max(THD *thd, partition_element *part) part->partition_name); break; } - if (table->versioned_by_engine()) + if (table->versioned(VERS_TRX_ID)) { uchar buf[8]; Field_timestampf fld(buf, NULL, 0, Field::NONE, &table->vers_end_field()->field_name, NULL, 6); @@ -1261,8 +1261,6 @@ bool partition_info::vers_setup_stats(THD * thd, bool is_create_table_ind) table->s->stat_serial++; table->s->hist_part_id= vers_info->hist_part->id; - if (!is_create_table_ind && (vers_limit_exceed() || vers_interval_exceed())) - vers_part_rotate(thd); } mysql_mutex_lock(&table->s->LOCK_rotation); mysql_cond_broadcast(&table->s->COND_rotation); diff --git a/sql/partition_info.h b/sql/partition_info.h index 359eae6e8cb..1e291a94e3f 100644 --- a/sql/partition_info.h +++ b/sql/partition_info.h @@ -512,7 +512,7 @@ public: bool updated; mysql_rwlock_wrlock(&table->s->LOCK_stat_serial); el->empty= false; - if (table->versioned_by_engine()) + if (table->versioned(VERS_TRX_ID)) { // transaction is not yet pushed to VTQ, so we use now-time my_time_t end_ts= my_time_t(0); diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 3050b56a725..e71f7914a17 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7816,22 +7816,22 @@ ER_INVISIBLE_NOT_NULL_WITHOUT_DEFAULT # MariaDB error numbers related to System Versioning ER_VERSIONING_REQUIRED - eng "System Versioning required: %s" + eng "System versioning required: %s" ER_UPDATE_INFO_WITH_SYSTEM_VERSIONING eng "Rows matched: %ld Changed: %ld Inserted: %ld Warnings: %ld" ER_VERS_FIELD_WRONG_TYPE - eng "%`s must be of type %s for versioned table %`s" + eng "%`s must be of type %s for temporal table %`s" ER_VERS_ENGINE_UNSUPPORTED - eng "Engine does not support System Versioning for %`s" + eng "Transaction system versioning for %`s is not supported" ER_VERS_RANGE_UNITS_MISMATCH eng "Range units mismatch" ER_NON_VERSIONED_FIELD_IN_VERSIONED_QUERY - eng "Attempt to read unversioned field %`s in historical query" + eng "Attempt to read non-temporal field %`s in historical query" ER_PARTITION_WRONG_TYPE eng "Wrong partition type, expected type: %`s" @@ -7852,10 +7852,10 @@ WARN_VERS_PART_NON_HISTORICAL eng "Partition %`s contains non-historical data" ER_VERS_ALTER_NOT_ALLOWED - eng "Not allowed for versioned %`s.%`s. Change @@system_versioning_alter_history to proceed with ALTER." + eng "Not allowed for temporal %`s.%`s. Change @@system_versioning_alter_history to proceed with ALTER." ER_VERS_ALTER_ENGINE_PROHIBITED - eng "Not allowed for versioned %`s.%`s. Change to/from native versioning engine is prohibited." + eng "Not allowed for temporal %`s.%`s. Change to/from native system versioning engine is prohibited." ER_VERS_RANGE_PROHIBITED eng "SYSTEM_TIME range selector is prohibited" @@ -7881,11 +7881,11 @@ ER_VERS_VTMD_ERROR ER_VERS_DIFFERENT_TABLES eng "Wrong parameters for %`s: system fields selected from different tables" -ER_VERS_NO_COLS_DEFINED - eng "Table %`s has no versioned columns" +ER_VERS_TABLE_MUST_HAVE_COLUMNS + eng "Table %`s must have at least 1 temporal column" ER_VERS_NOT_VERSIONED - eng "Table %`s is not versioned" + eng "Table %`s is not temporal" ER_MISSING eng "Wrong parameters for %`s: missing '%s'" @@ -7921,10 +7921,10 @@ ER_VERS_GENERATED_ALWAYS_NOT_EMPTY eng "Can not modify column %`s to GENERATED ALWAYS AS ROW START/END for non-empty table" ER_VERS_TRT_IS_DISABLED - eng "Some versioned DML requires `transaction_registry` to be set to ON." + eng "Temporal operation requires `mysql.transaction_registry` (@@system_versioning_transaction_registry)." ER_VERS_DUPLICATE_ROW_START_END eng "Duplicate ROW %s column %`s" ER_VERS_ALREADY_VERSIONED - eng "Table %`s is already system-versioned table" + eng "Table %`s is already temporal" diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 34bd7513f63..d07e626ec6a 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7640,7 +7640,7 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, TABLE *table= f->field->table; DBUG_ASSERT(table && table->pos_in_table_list); TABLE_LIST *tl= table->pos_in_table_list; - vers_range_type_t vers_type= tl->vers_conditions.type; + vers_system_time_t vers_type= tl->vers_conditions.type; enum_sql_command sql_command= thd->lex->sql_command; unsigned int create_options= thd->lex->create_info.options; @@ -7652,8 +7652,8 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, ((fl & VERS_HIDDEN_FLAG) && ( vers_hide == VERS_HIDE_IMPLICIT || (vers_hide == VERS_HIDE_AUTO && ( - vers_type == FOR_SYSTEM_TIME_UNSPECIFIED || - vers_type == FOR_SYSTEM_TIME_AS_OF))))) : + vers_type == SYSTEM_TIME_UNSPECIFIED || + vers_type == SYSTEM_TIME_AS_OF))))) : (fl & VERS_HIDDEN_FLAG)) { if (sql_command != SQLCOM_CREATE_TABLE || diff --git a/sql/sql_class.h b/sql/sql_class.h index 30e428ab20d..41b8efc4464 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -155,7 +155,6 @@ extern bool volatile shutdown_in_progress; extern "C" LEX_STRING * thd_query_string (MYSQL_THD thd); extern "C" size_t thd_query_safe(MYSQL_THD thd, char *buf, size_t buflen); -void thd_vers_update_trt(THD *thd, bool value); /** @class CSET_STRING @@ -707,7 +706,7 @@ typedef struct system_variables uint column_compression_zlib_level; ulong in_subquery_conversion_threshold; - st_vers_asof_timestamp vers_asof_timestamp; + vers_asof_timestamp_t vers_asof_timestamp; my_bool vers_force; ulong vers_hide; my_bool vers_innodb_algorithm_simple; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 118066e7d93..9c535e2124a 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -253,7 +253,7 @@ static bool record_should_be_deleted(THD *thd, TABLE *table, SQL_SELECT *sel, inline int TABLE::delete_row() { - if (!versioned_by_sql() || !vers_end_field()->is_max()) + if (!versioned(VERS_TIMESTAMP) || !vers_end_field()->is_max()) return file->ha_delete_row(record[0]); store_record(this, record[1]); @@ -328,8 +328,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, DBUG_RETURN(TRUE); // trx_sees() in InnoDB reads sys_trx_start - if (!table->versioned_by_sql()) { - DBUG_ASSERT(table_list->vers_conditions.type == FOR_SYSTEM_TIME_BEFORE); + if (!table->versioned(VERS_TIMESTAMP)) { + DBUG_ASSERT(table_list->vers_conditions.type == SYSTEM_TIME_BEFORE); bitmap_set_bit(table->read_set, table->vers_end_field()->field_index); } } @@ -429,7 +429,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, if (!with_select && !using_limit && const_cond_result && (!thd->is_current_stmt_binlog_format_row() && !has_triggers) - && !table->versioned_by_sql()) + && !table->versioned(VERS_TIMESTAMP)) { /* Update the table->file->stats.records number */ table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); @@ -837,7 +837,7 @@ cleanup: errcode= query_error_code(thd, killed_status == NOT_KILLED); ScopedStatementReplication scoped_stmt_rpl( - table->versioned_by_engine() ? thd : NULL); + table->versioned(VERS_TRX_ID) ? thd : NULL); /* [binlog]: If 'handler::delete_all_rows()' was called and the storage engine does not inject the rows itself, we replicate diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index ab739733c63..b118cac09c2 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1161,7 +1161,7 @@ values_loop_end: errcode= query_error_code(thd, thd->killed == NOT_KILLED); ScopedStatementReplication scoped_stmt_rpl( - table->versioned_by_engine() ? thd : NULL); + table->versioned(VERS_TRX_ID) ? thd : NULL); /* bug#22725: A query which per-row-loop can not be interrupted with @@ -1582,7 +1582,7 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, if (!table) table= table_list->table; - if (table->versioned_by_sql() && duplic == DUP_REPLACE) + if (table->versioned(VERS_TIMESTAMP) && duplic == DUP_REPLACE) { // Additional memory may be required to create historical items. if (table_list->set_insert_values(thd->mem_root)) @@ -1648,7 +1648,7 @@ static int last_uniq_key(TABLE *table,uint keynr) int vers_insert_history_row(TABLE *table) { - DBUG_ASSERT(table->versioned_by_sql()); + DBUG_ASSERT(table->versioned(VERS_TIMESTAMP)); restore_record(table,record[1]); // Set Sys_end to now() @@ -1866,7 +1866,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) info->updated++; if (table->versioned()) { - if (table->versioned_by_sql()) + if (table->versioned(VERS_TIMESTAMP)) { store_record(table, record[2]); if ((error= vers_insert_history_row(table))) @@ -1940,7 +1940,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) !table->file->referenced_by_foreign_key() && (!table->triggers || !table->triggers->has_delete_triggers())) { - if (table->versioned_by_engine()) + if (table->versioned(VERS_TRX_ID)) { bitmap_set_bit(table->write_set, table->vers_start_field()->field_index); table->vers_start_field()->set_notnull(); @@ -1953,7 +1953,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) if (error != HA_ERR_RECORD_IS_THE_SAME) { info->deleted++; - if (table->versioned_by_sql()) + if (table->versioned(VERS_TIMESTAMP)) { store_record(table, record[2]); error= vers_insert_history_row(table); @@ -1978,7 +1978,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) TRG_ACTION_BEFORE, TRUE)) goto before_trg_err; - if (!table->versioned_by_sql()) + if (!table->versioned(VERS_TIMESTAMP)) error= table->file->ha_delete_row(table->record[1]); else { @@ -1996,7 +1996,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) } if (error) goto err; - if (!table->versioned_by_sql()) + if (!table->versioned(VERS_TIMESTAMP)) info->deleted++; else info->updated++; diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index e4662f2d52e..5beec82e063 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -3163,11 +3163,14 @@ int vers_get_partition_id(partition_info *part_info, DBUG_ASSERT(part_info); Field *sys_trx_end= part_info->part_field_array[STAT_TRX_END]; DBUG_ASSERT(sys_trx_end); - DBUG_ASSERT(part_info->table); + TABLE *table= part_info->table; + DBUG_ASSERT(table); Vers_part_info *vers_info= part_info->vers_info; - DBUG_ASSERT(vers_info && vers_info->initialized()); - DBUG_ASSERT(sys_trx_end->table == part_info->table && part_info->table->versioned()); - DBUG_ASSERT(part_info->table->vers_end_field() == sys_trx_end); + DBUG_ASSERT(vers_info); + DBUG_ASSERT(vers_info->initialized()); + DBUG_ASSERT(sys_trx_end->table == table); + DBUG_ASSERT(table->versioned()); + DBUG_ASSERT(table->vers_end_field() == sys_trx_end); // new rows have NULL in sys_trx_end if (sys_trx_end->is_max() || sys_trx_end->is_null()) @@ -3177,7 +3180,6 @@ int vers_get_partition_id(partition_info *part_info, else // row is historical { THD *thd= current_thd; - TABLE *table= part_info->table; switch (thd->lex->sql_command) { @@ -3198,7 +3200,7 @@ int vers_get_partition_id(partition_info *part_info, mysql_mutex_unlock(&table->s->LOCK_rotation); // transaction is not yet pushed to VTQ, so we use now-time ulong sec_part; - my_time_t end_ts= sys_trx_end->table->versioned_by_engine() ? + my_time_t end_ts= sys_trx_end->table->versioned(VERS_TRX_ID) ? my_time_t(0) : sys_trx_end->get_timestamp(&sec_part); if (part_info->vers_limit_exceed() || part_info->vers_interval_exceed(end_ts)) { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 95cae826015..c5d6954171e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -673,13 +673,13 @@ setup_without_group(THD *thd, Ref_ptr_array ref_pointer_array, bool vers_select_conds_t::init_from_sysvar(THD *thd) { - st_vers_asof_timestamp &in= thd->variables.vers_asof_timestamp; - type= (vers_range_type_t) in.type; - unit_start= UNIT_TIMESTAMP; + vers_asof_timestamp_t &in= thd->variables.vers_asof_timestamp; + type= (vers_system_time_t) in.type; + unit_start= VERS_TIMESTAMP; from_query= false; - if (type != FOR_SYSTEM_TIME_UNSPECIFIED && type != FOR_SYSTEM_TIME_ALL) + if (type != SYSTEM_TIME_UNSPECIFIED && type != SYSTEM_TIME_ALL) { - DBUG_ASSERT(type == FOR_SYSTEM_TIME_AS_OF); + DBUG_ASSERT(type == SYSTEM_TIME_AS_OF); start= new (thd->mem_root) Item_datetime_literal(thd, &in.ltime, TIME_SECOND_PART_DIGITS); if (!start) @@ -844,7 +844,7 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr DBUG_RETURN(-1); } else - vers_conditions.init(FOR_SYSTEM_TIME_ALL); + vers_conditions.init(SYSTEM_TIME_ALL); } #endif @@ -886,7 +886,7 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr break; } - if (vers_conditions == FOR_SYSTEM_TIME_ALL) + if (vers_conditions == SYSTEM_TIME_ALL) continue; } // if (vers_conditions) @@ -912,17 +912,17 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr bool tmp_from_ib= table->table->s->table_category == TABLE_CATEGORY_TEMPORARY && table->table->vers_start_field()->type() == MYSQL_TYPE_LONGLONG; - bool timestamps_only= table->table->versioned_by_sql() && !tmp_from_ib; + bool timestamps_only= table->table->versioned(VERS_TIMESTAMP) && !tmp_from_ib; if (vers_conditions) { /* TODO: do resolve fix_length_and_dec(), fix_fields(). This requires storing vers_conditions as Item and make some magic related to - UNIT_TIMESTAMP/UNIT_TRX_ID at stage of fix_fields() + vers_system_time_t/VERS_TRX_ID at stage of fix_fields() (this is large refactoring). */ vers_conditions.resolve_units(timestamps_only); - if (timestamps_only && (vers_conditions.unit_start == UNIT_TRX_ID || - vers_conditions.unit_end == UNIT_TRX_ID)) + if (timestamps_only && (vers_conditions.unit_start == VERS_TRX_ID || + vers_conditions.unit_end == VERS_TRX_ID)) { my_error(ER_VERS_ENGINE_UNSUPPORTED, MYF(0), table->table_name); DBUG_RETURN(-1); @@ -934,54 +934,62 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr // have uint64 type of sys_trx_(start|end) field. // They need special handling. TABLE *t= table->table; - if (tmp_from_ib || t->versioned_by_sql() || + if (tmp_from_ib || t->versioned(VERS_TIMESTAMP) || thd->variables.vers_innodb_algorithm_simple) { if (vers_conditions) { if (vers_conditions.start) { + if (!vers_conditions.unit_start) + vers_conditions.unit_start= t->s->versioned; switch (vers_conditions.unit_start) { - case UNIT_TIMESTAMP: + case VERS_TIMESTAMP: { vers_conditions.start= newx Item_datetime_from_unixtime_typecast( thd, vers_conditions.start, 6); break; } - case UNIT_TRX_ID: + case VERS_TRX_ID: { vers_conditions.start= newx Item_longlong_typecast( thd, vers_conditions.start); break; } - default:; + default: + DBUG_ASSERT(0); + break; } } if (vers_conditions.end) { + if (!vers_conditions.unit_end) + vers_conditions.unit_end= t->s->versioned; switch (vers_conditions.unit_end) { - case UNIT_TIMESTAMP: + case VERS_TIMESTAMP: { vers_conditions.end= newx Item_datetime_from_unixtime_typecast( thd, vers_conditions.end, 6); break; } - case UNIT_TRX_ID: + case VERS_TRX_ID: { vers_conditions.end= newx Item_longlong_typecast( thd, vers_conditions.end); break; } - default:; + default: + DBUG_ASSERT(0); + break; } } } switch (vers_conditions.type) { - case FOR_SYSTEM_TIME_UNSPECIFIED: + case SYSTEM_TIME_UNSPECIFIED: if (t->vers_start_field()->real_type() != MYSQL_TYPE_LONGLONG) { MYSQL_TIME max_time; @@ -997,25 +1005,25 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr cond1= newx Item_func_eq(thd, row_end, curr); } break; - case FOR_SYSTEM_TIME_AS_OF: + case SYSTEM_TIME_AS_OF: cond1= newx Item_func_le(thd, row_start, vers_conditions.start); cond2= newx Item_func_gt(thd, row_end, vers_conditions.start); break; - case FOR_SYSTEM_TIME_FROM_TO: + case SYSTEM_TIME_FROM_TO: cond1= newx Item_func_lt(thd, row_start, vers_conditions.end); cond2= newx Item_func_ge(thd, row_end, vers_conditions.start); break; - case FOR_SYSTEM_TIME_BETWEEN: + case SYSTEM_TIME_BETWEEN: cond1= newx Item_func_le(thd, row_start, vers_conditions.end); cond2= newx Item_func_ge(thd, row_end, vers_conditions.start); break; - case FOR_SYSTEM_TIME_BEFORE: + case SYSTEM_TIME_BEFORE: cond1= newx Item_func_lt(thd, row_end, vers_conditions.start); break; @@ -1031,32 +1039,32 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr switch (vers_conditions.type) { - case FOR_SYSTEM_TIME_UNSPECIFIED: + case SYSTEM_TIME_UNSPECIFIED: curr= newx Item_int(thd, ULONGLONG_MAX); cond1= newx Item_func_eq(thd, row_end, curr); break; - case FOR_SYSTEM_TIME_AS_OF: - trx_id0= vers_conditions.unit_start == UNIT_TIMESTAMP ? + case SYSTEM_TIME_AS_OF: + trx_id0= vers_conditions.unit_start == VERS_TIMESTAMP ? newx Item_func_vtq_id(thd, vers_conditions.start, TR_table::FLD_TRX_ID) : vers_conditions.start; cond1= newx Item_func_vtq_trx_sees_eq(thd, trx_id0, row_start); cond2= newx Item_func_vtq_trx_sees(thd, row_end, trx_id0); break; - case FOR_SYSTEM_TIME_FROM_TO: - case FOR_SYSTEM_TIME_BETWEEN: - trx_id0= vers_conditions.unit_start == UNIT_TIMESTAMP ? + case SYSTEM_TIME_FROM_TO: + case SYSTEM_TIME_BETWEEN: + trx_id0= vers_conditions.unit_start == VERS_TIMESTAMP ? newx Item_func_vtq_id(thd, vers_conditions.start, TR_table::FLD_TRX_ID, true) : vers_conditions.start; - trx_id1= vers_conditions.unit_end == UNIT_TIMESTAMP ? + trx_id1= vers_conditions.unit_end == VERS_TIMESTAMP ? newx Item_func_vtq_id(thd, vers_conditions.end, TR_table::FLD_TRX_ID, false) : vers_conditions.end; - cond1= vers_conditions.type == FOR_SYSTEM_TIME_FROM_TO ? + cond1= vers_conditions.type == SYSTEM_TIME_FROM_TO ? newx Item_func_vtq_trx_sees(thd, trx_id1, row_start) : newx Item_func_vtq_trx_sees_eq(thd, trx_id1, row_start); cond2= newx Item_func_vtq_trx_sees_eq(thd, row_end, trx_id0); break; - case FOR_SYSTEM_TIME_BEFORE: - trx_id0= vers_conditions.unit_start == UNIT_TIMESTAMP ? + case SYSTEM_TIME_BEFORE: + trx_id0= vers_conditions.unit_start == VERS_TIMESTAMP ? newx Item_func_vtq_id(thd, vers_conditions.start, TR_table::FLD_TRX_ID) : vers_conditions.start; cond1= newx Item_func_lt(thd, row_end, trx_id0); @@ -1094,7 +1102,7 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr } if (outer_table) - outer_table->vers_conditions.type= FOR_SYSTEM_TIME_ALL; + outer_table->vers_conditions.type= SYSTEM_TIME_ALL; } DBUG_RETURN(0); @@ -17457,6 +17465,8 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, Field **tmp_from_field=from_field; Field *sys_trx_start= NULL; Field *sys_trx_end= NULL; + vers_sys_type_t versioned= VERS_UNDEFINED; + while ((item=li++)) { Item::Type type= item->type(); @@ -17594,9 +17604,15 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, if (s->versioned) { if (field->flags & VERS_SYS_START_FLAG) + { sys_trx_start= new_field; + versioned= s->versioned; + } else if (field->flags & VERS_SYS_END_FLAG) + { sys_trx_end= new_field; + versioned= s->versioned; + } } } } @@ -17604,9 +17620,16 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, { Item_type_holder *ith= (Item_type_holder*)item; if (ith->field_flags() & VERS_SYS_START_FLAG) + { sys_trx_start= new_field; + goto set_versioned; + } else if (ith->field_flags() & VERS_SYS_END_FLAG) + { sys_trx_end= new_field; + set_versioned: + versioned= ith->vers_trx_id() ? VERS_TRX_ID : VERS_TIMESTAMP; + } } if (type == Item::SUM_FUNC_ITEM) { @@ -17691,9 +17714,10 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, if (sys_trx_start && sys_trx_end) { + DBUG_ASSERT(versioned); sys_trx_start->flags|= VERS_SYS_START_FLAG | VERS_HIDDEN_FLAG; sys_trx_end->flags|= VERS_SYS_END_FLAG | VERS_HIDDEN_FLAG; - share->versioned= true; + share->versioned= versioned; share->field= table->field; share->row_start_field= sys_trx_start->field_index; share->row_end_field= sys_trx_end->field_index; diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 32f5f546dcc..1296ac3ae2f 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -1372,7 +1372,7 @@ mysqld_show_create(THD *thd, TABLE_LIST *table_list) bool versioned= table_list->vers_conditions; if (versioned) { - DBUG_ASSERT(table_list->vers_conditions == FOR_SYSTEM_TIME_AS_OF); + DBUG_ASSERT(table_list->vers_conditions == SYSTEM_TIME_AS_OF); VTMD_table vtmd(*table_list); if (vtmd.setup_select(thd)) goto exit; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index a6686eec2a4..e7c43d08ea3 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -9048,7 +9048,7 @@ bool mysql_alter_table(THD *thd, const char *new_db, const char *new_name, DBUG_RETURN(true); } - if (table_list->table->versioned_by_engine() && + if (table_list->table->versioned(VERS_TRX_ID) && alter_info->requested_algorithm == Alter_info::ALTER_TABLE_ALGORITHM_DEFAULT && !table_list->table->s->partition_info_str) @@ -9184,8 +9184,7 @@ bool mysql_alter_table(THD *thd, const char *new_db, const char *new_name, if (check_engine(thd, alter_ctx.new_db, alter_ctx.new_name, create_info)) DBUG_RETURN(true); - if (create_info->vers_info.fix_alter_info(thd, alter_info, create_info, - table)) + if (create_info->vers_info.fix_alter_info(thd, alter_info, create_info, table)) { DBUG_RETURN(true); } @@ -9799,7 +9798,7 @@ bool mysql_alter_table(THD *thd, const char *new_db, const char *new_name, alter_info->keys_onoff, &alter_ctx)) { - if (vers_survival_mod && new_versioned && table->versioned_by_sql()) + if (vers_survival_mod && new_versioned && table->versioned(VERS_TIMESTAMP)) { // Failure of this function may result in corruption of an original table. vers_reset_alter_copy(thd, table); @@ -10384,7 +10383,7 @@ copy_data_between_tables(THD *thd, TABLE *from, TABLE *to, error= 1; break; } - if (keep_versioned && to->versioned_by_engine() && + if (keep_versioned && to->versioned(VERS_TRX_ID) && thd->variables.vers_alter_history != VERS_ALTER_HISTORY_SURVIVE) { to->vers_write= false; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index d14d50267fd..509cb984f84 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -930,7 +930,7 @@ update_begin: if (has_vers_fields && table->versioned()) { - if (table->versioned_by_sql()) + if (table->versioned(VERS_TIMESTAMP)) { store_record(table, record[2]); if ((error = vers_insert_history_row(table))) @@ -1119,7 +1119,7 @@ update_end: errcode= query_error_code(thd, killed_status == NOT_KILLED); ScopedStatementReplication scoped_stmt_rpl( - table->versioned_by_engine() ? thd : NULL); + table->versioned(VERS_TRX_ID) ? thd : NULL); if (thd->binlog_query(THD::ROW_QUERY_TYPE, thd->query(), thd->query_length(), @@ -1145,7 +1145,7 @@ update_end: if (error < 0 && !thd->lex->analyze_stmt) { char buff[MYSQL_ERRMSG_SIZE]; - if (!table->versioned_by_sql()) + if (!table->versioned(VERS_TIMESTAMP)) my_snprintf(buff, sizeof(buff), ER_THD(thd, ER_UPDATE_INFO), (ulong) found, (ulong) updated, (ulong) thd->get_stmt_da()->current_statement_warn_count()); @@ -2330,7 +2330,7 @@ int multi_update::send_data(List<Item> ¬_used_values) } else if (has_vers_fields && table->versioned()) { - if (table->versioned_by_sql()) + if (table->versioned(VERS_TIMESTAMP)) { store_record(table, record[2]); if (vers_insert_history_row(table)) @@ -2655,7 +2655,7 @@ int multi_update::do_updates() if (has_vers_fields && table->versioned()) { - if (table->versioned_by_sql()) + if (table->versioned(VERS_TIMESTAMP)) { store_record(table, record[2]); if ((local_error= vers_insert_history_row(table))) @@ -2788,7 +2788,7 @@ bool multi_update::send_eof() bool force_stmt= false; for (TABLE *table= all_tables->table; table; table= table->next) { - if (table->versioned_by_engine()) + if (table->versioned(VERS_TRX_ID)) { force_stmt= true; break; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 1b9b72a5898..9f2059bafc1 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -740,8 +740,8 @@ bool LEX::set_bincmp(CHARSET_INFO *cs, bool bin) } while(0) -void vers_select_conds_t::init(vers_range_type_t t, vers_range_unit_t u_start, - Item *s, vers_range_unit_t u_end, Item *e) +void vers_select_conds_t::init(vers_system_time_t t, vers_sys_type_t u_start, + Item *s, vers_sys_type_t u_end, Item *e) { type= t; unit_start= u_start; @@ -880,7 +880,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr) enum Window_frame::Frame_exclusion frame_exclusion; enum trigger_order_type trigger_action_order_type; DDL_options_st object_ddl_options; - enum vers_range_unit_t vers_range_unit; + enum vers_sys_type_t vers_range_unit; enum Column_definition::enum_column_versioning vers_column_versioning; } @@ -9171,15 +9171,15 @@ select_options: opt_trans_or_timestamp: /* empty */ { - $$ = UNIT_AUTO; + $$ = VERS_UNDEFINED; } | TRANSACTION_SYM { - $$ = UNIT_TRX_ID; + $$ = VERS_TRX_ID; } | TIMESTAMP { - $$ = UNIT_TIMESTAMP; + $$ = VERS_TIMESTAMP; } ; @@ -9197,21 +9197,21 @@ opt_for_system_time_clause: system_time_expr: AS OF_SYM opt_trans_or_timestamp simple_expr { - Lex->vers_conditions.init(FOR_SYSTEM_TIME_AS_OF, $3, $4); + Lex->vers_conditions.init(SYSTEM_TIME_AS_OF, $3, $4); } | ALL { - Lex->vers_conditions.init(FOR_SYSTEM_TIME_ALL); + Lex->vers_conditions.init(SYSTEM_TIME_ALL); } | FROM opt_trans_or_timestamp simple_expr TO_SYM opt_trans_or_timestamp simple_expr { - Lex->vers_conditions.init(FOR_SYSTEM_TIME_FROM_TO, $2, $3, $5, $6); + Lex->vers_conditions.init(SYSTEM_TIME_FROM_TO, $2, $3, $5, $6); } | BETWEEN_SYM opt_trans_or_timestamp simple_expr AND_SYM opt_trans_or_timestamp simple_expr { - Lex->vers_conditions.init(FOR_SYSTEM_TIME_BETWEEN, $2, $3, $5, $6); + Lex->vers_conditions.init(SYSTEM_TIME_BETWEEN, $2, $3, $5, $6); } ; @@ -13504,7 +13504,7 @@ truncate_end: opt_lock_wait_timeout | TO_SYM SYSTEM_TIME_SYM opt_trans_or_timestamp simple_expr { - Lex->vers_conditions.init(FOR_SYSTEM_TIME_BEFORE, $3, $4); + Lex->vers_conditions.init(SYSTEM_TIME_BEFORE, $3, $4); Lex->last_table()->vers_conditions= Lex->vers_conditions; } ; @@ -13824,8 +13824,8 @@ show_param: MYSQL_YYABORT; lex->create_info.storage_media= HA_SM_DEFAULT; - if (lex->vers_conditions.type != FOR_SYSTEM_TIME_UNSPECIFIED && - lex->vers_conditions.type != FOR_SYSTEM_TIME_AS_OF) + if (lex->vers_conditions.type != SYSTEM_TIME_UNSPECIFIED && + lex->vers_conditions.type != SYSTEM_TIME_AS_OF) { my_yyabort_error((ER_VERS_RANGE_PROHIBITED, MYF(0))); } diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index d4971c0d8d5..02237921c11 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -391,7 +391,7 @@ const char *Sys_var_vers_asof::asof_keywords[]= {"DEFAULT", NULL}; static Sys_var_vers_asof Sys_vers_asof_timestamp( "system_versioning_asof", "Default value for the FOR SYSTEM_TIME AS OF clause", SESSION_VAR(vers_asof_timestamp.type), NO_CMD_LINE, - Sys_var_vers_asof::asof_keywords, DEFAULT(FOR_SYSTEM_TIME_UNSPECIFIED)); + Sys_var_vers_asof::asof_keywords, DEFAULT(SYSTEM_TIME_UNSPECIFIED)); static Sys_var_mybool Sys_vers_force( "system_versioning_force", "Force system versioning for all created tables", diff --git a/sql/sys_vars.ic b/sql/sys_vars.ic index a2ea75d0dc2..200669a5bf5 100644 --- a/sql/sys_vars.ic +++ b/sql/sys_vars.ic @@ -2628,17 +2628,17 @@ public: bool res= var->value->get_date(<ime, 0); if (!res) { - var->save_result.ulonglong_value= FOR_SYSTEM_TIME_AS_OF; + var->save_result.ulonglong_value= SYSTEM_TIME_AS_OF; } return res; } private: - bool update(set_var *var, st_vers_asof_timestamp &out) + bool update(set_var *var, vers_asof_timestamp_t &out) { bool res= false; out.type= static_cast<enum_var_type>(var->save_result.ulonglong_value); - if (out.type == FOR_SYSTEM_TIME_AS_OF) + if (out.type == SYSTEM_TIME_AS_OF) { res= var->value->get_date(&out.ltime, 0); } @@ -2648,27 +2648,28 @@ private: public: virtual bool global_update(THD *thd, set_var *var) { - return update(var, global_var(st_vers_asof_timestamp)); + return update(var, global_var(vers_asof_timestamp_t)); } virtual bool session_update(THD *thd, set_var *var) { - return update(var, session_var(thd, st_vers_asof_timestamp)); + return update(var, session_var(thd, vers_asof_timestamp_t)); } private: - uchar *value_ptr(THD *thd, st_vers_asof_timestamp &val) + uchar *value_ptr(THD *thd, vers_asof_timestamp_t &val) { switch (val.type) { - case FOR_SYSTEM_TIME_UNSPECIFIED: - case FOR_SYSTEM_TIME_ALL: + case SYSTEM_TIME_UNSPECIFIED: + case SYSTEM_TIME_ALL: return (uchar*) thd->strdup(asof_keywords[val.type]); - case FOR_SYSTEM_TIME_AS_OF: + case SYSTEM_TIME_AS_OF: { uchar *buf= (uchar*) thd->alloc(MAX_DATE_STRING_REP_LENGTH); if (buf &&!my_datetime_to_str(&val.ltime, (char*) buf, 6)) { - my_error(ER_WRONG_VALUE_FOR_VAR, MYF(0), "versioning_asof_timestamp", "NULL (wrong datetime)"); + // TODO: figure out variable name + my_error(ER_WRONG_VALUE_FOR_VAR, MYF(0), "system_versioning_asof_timestamp", "NULL (wrong datetime)"); return (uchar*) thd->strdup("Error: wrong datetime"); } return buf; @@ -2676,13 +2677,13 @@ private: default: break; } - my_error(ER_WRONG_VALUE_FOR_VAR, MYF(0), "versioning_asof_timestamp", "NULL (wrong range type)"); + my_error(ER_WRONG_VALUE_FOR_VAR, MYF(0), "system_versioning_asof_timestamp", "NULL (wrong range type)"); return (uchar*) thd->strdup("Error: wrong range type"); } public: virtual uchar *session_value_ptr(THD *thd, const LEX_CSTRING *base) - { return value_ptr(thd, session_var(thd, st_vers_asof_timestamp)); } + { return value_ptr(thd, session_var(thd, vers_asof_timestamp_t)); } virtual uchar *global_value_ptr(THD *thd, const LEX_CSTRING *base) - { return value_ptr(thd, global_var(st_vers_asof_timestamp)); } + { return value_ptr(thd, global_var(vers_asof_timestamp_t)); } }; diff --git a/sql/table.cc b/sql/table.cc index 23ecec81c0d..2601480c6f5 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -1198,6 +1198,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, const uchar *system_period= 0; bool vtmd_used= false; share->vtmd= false; + bool vers_can_native= false; const uchar *extra2_field_flags= 0; size_t extra2_field_flags_length= 0; @@ -1770,9 +1771,9 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, /* Set system versioning information. */ if (system_period == NULL) { - versioned= false; - row_start_field = 0; - row_end_field = 0; + versioned= VERS_UNDEFINED; + row_start_field= 0; + row_end_field= 0; } else { @@ -1782,7 +1783,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, if (row_start >= share->fields || row_end >= share->fields) goto err; DBUG_PRINT("info", ("Columns with system versioning: [%d, %d]", row_start, row_end)); - versioned= true; + versioned= VERS_TIMESTAMP; + vers_can_native= plugin_hton(se_plugin)->flags & HTON_NATIVE_SYS_VERSIONING; vers_init(); row_start_field= row_start; row_end_field= row_end; @@ -2018,6 +2020,27 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, flags|= VERS_SYS_START_FLAG; else if (i == row_end_field) flags|= VERS_SYS_END_FLAG; + + if (flags & VERS_SYSTEM_FIELD) + { + switch (field_type) + { + case MYSQL_TYPE_TIMESTAMP2: + case MYSQL_TYPE_DATETIME2: + break; + case MYSQL_TYPE_LONGLONG: + if (vers_can_native) + { + versioned= VERS_TRX_ID; + break; + } + default: + my_error(ER_VERS_FIELD_WRONG_TYPE, MYF(0), fieldnames.type_names[i], + versioned == VERS_TIMESTAMP ? "TIMESTAMP(6)" : "BIGINT(20) UNSIGNED", + table_name.str); + goto err; + } + } } /* Convert pre-10.2.2 timestamps to use Field::default_value */ @@ -7753,7 +7776,7 @@ void TABLE::vers_update_fields() bitmap_set_bit(write_set, vers_start_field()->field_index); bitmap_set_bit(write_set, vers_end_field()->field_index); - if (versioned_by_sql()) + if (versioned(VERS_TIMESTAMP)) { if (!vers_write) return; @@ -8883,25 +8906,25 @@ bool TR_table::check() void vers_select_conds_t::resolve_units(bool timestamps_only) { - DBUG_ASSERT(type != FOR_SYSTEM_TIME_UNSPECIFIED); + DBUG_ASSERT(type != SYSTEM_TIME_UNSPECIFIED); DBUG_ASSERT(start); - if (unit_start == UNIT_AUTO) + if (unit_start == VERS_UNDEFINED) { if (start->type() == Item::FIELD_ITEM) - unit_start= UNIT_TIMESTAMP; + unit_start= VERS_TIMESTAMP; else unit_start= (!timestamps_only && (start->result_type() == INT_RESULT || start->result_type() == REAL_RESULT)) ? - UNIT_TRX_ID : UNIT_TIMESTAMP; + VERS_TRX_ID : VERS_TIMESTAMP; } - if (end && unit_end == UNIT_AUTO) + if (end && unit_end == VERS_UNDEFINED) { if (start->type() == Item::FIELD_ITEM) - unit_start= UNIT_TIMESTAMP; + unit_start= VERS_TIMESTAMP; else unit_end= (!timestamps_only && (end->result_type() == INT_RESULT || end->result_type() == REAL_RESULT)) ? - UNIT_TRX_ID : UNIT_TIMESTAMP; + VERS_TRX_ID : VERS_TIMESTAMP; } } diff --git a/sql/table.h b/sql/table.h index 4fffae7e763..83bc9d4597f 100644 --- a/sql/table.h +++ b/sql/table.h @@ -574,6 +574,13 @@ struct TABLE_STATISTICS_CB class Vers_min_max_stats; +enum vers_sys_type_t +{ + VERS_UNDEFINED= 0, + VERS_TIMESTAMP, + VERS_TRX_ID +}; + #ifndef UINT32_MAX #define UINT32_MAX (4294967295U) #endif @@ -757,7 +764,7 @@ struct TABLE_SHARE System versioning support. */ - bool versioned; + vers_sys_type_t versioned; bool vtmd; uint16 row_start_field; uint16 row_end_field; @@ -1517,29 +1524,16 @@ public: */ bool vers_write; - bool versioned() const + bool versioned(vers_sys_type_t type= VERS_UNDEFINED) const { DBUG_ASSERT(s); - return s->versioned; + return type ? s->versioned == type : s->versioned; } - bool versioned_write() const + bool versioned_write(vers_sys_type_t type= VERS_UNDEFINED) const { DBUG_ASSERT(versioned() || !vers_write); - return vers_write; - } - - /* Versioned by SQL layer */ - bool versioned_by_sql() const - { - DBUG_ASSERT(s && file); - return s->versioned && !file->native_versioned(); - } - - bool versioned_by_engine() const - { - DBUG_ASSERT(s && file); - return s->versioned && file->native_versioned(); + return versioned(type) ? vers_write : false; } bool vers_vtmd() const @@ -1861,53 +1855,46 @@ class Item_in_subselect; 4) jtbm semi-join (jtbm_subselect != NULL) */ -enum vers_range_unit_t -{ - UNIT_AUTO = 0, - UNIT_TIMESTAMP, - UNIT_TRX_ID -}; - /** last_leaf_for_name_resolutioning support. */ struct vers_select_conds_t { - vers_range_type_t type; - vers_range_unit_t unit_start, unit_end; + vers_system_time_t type; + vers_sys_type_t unit_start, unit_end; bool from_query:1; Item *start, *end; void empty() { - type= FOR_SYSTEM_TIME_UNSPECIFIED; - unit_start= unit_end= UNIT_AUTO; + type= SYSTEM_TIME_UNSPECIFIED; + unit_start= unit_end= VERS_UNDEFINED; from_query= false; start= end= NULL; } Item *fix_dec(Item *item); - void init(vers_range_type_t t, vers_range_unit_t u_start= UNIT_AUTO, - Item * s= NULL, vers_range_unit_t u_end= UNIT_AUTO, + void init(vers_system_time_t t, vers_sys_type_t u_start= VERS_UNDEFINED, + Item * s= NULL, vers_sys_type_t u_end= VERS_UNDEFINED, Item * e= NULL); bool init_from_sysvar(THD *thd); - bool operator== (vers_range_type_t b) + bool operator== (vers_system_time_t b) { return type == b; } - bool operator!= (vers_range_type_t b) + bool operator!= (vers_system_time_t b) { return type != b; } operator bool() const { - return type != FOR_SYSTEM_TIME_UNSPECIFIED; + return type != SYSTEM_TIME_UNSPECIFIED; } void resolve_units(bool timestamps_only); bool user_defined() const { - return !from_query && type != FOR_SYSTEM_TIME_UNSPECIFIED; + return !from_query && type != SYSTEM_TIME_UNSPECIFIED; } }; diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 5e1f22f3f59..f1b5bafb371 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -3636,7 +3636,7 @@ static ulonglong innodb_prepare_commit_versioned(THD* thd, ulonglong *trx_id) for (trx_mod_tables_t::const_iterator t = trx->mod_tables.begin(); t != trx->mod_tables.end(); t++) { - if (t->second.is_versioned()) { + if (t->second.is_trx_versioned()) { DBUG_ASSERT(t->first->versioned()); DBUG_ASSERT(trx->undo_no); DBUG_ASSERT(trx->rsegs.m_redo.rseg); @@ -6561,7 +6561,7 @@ no_such_table: ut_ad(table->versioned() == m_prebuilt->table->versioned()); } - info(HA_STATUS_NO_LOCK | HA_STATUS_VARIABLE | HA_STATUS_CONST); + info(HA_STATUS_NO_LOCK | HA_STATUS_VARIABLE | HA_STATUS_CONST | HA_STATUS_OPEN); DBUG_RETURN(0); } @@ -7782,7 +7782,7 @@ ha_innobase::build_template( index = whole_row ? clust_index : m_prebuilt->index; - m_prebuilt->versioned_write = table->versioned_write(); + m_prebuilt->versioned_write = table->versioned_write(VERS_TRX_ID); m_prebuilt->need_to_access_clustered = (index == clust_index); /* Either m_prebuilt->index should be a secondary index, or it @@ -8406,8 +8406,8 @@ no_commit: innobase_srv_conc_enter_innodb(m_prebuilt); - vers_set_fields = - table->versioned_write() ? ROW_INS_VERSIONED : ROW_INS_NORMAL; + vers_set_fields = table->versioned_write(VERS_TRX_ID) ? + ROW_INS_VERSIONED : ROW_INS_NORMAL; /* Step-5: Execute insert graph that will result in actual insert. */ error = row_insert_for_mysql((byte*) record, m_prebuilt, vers_set_fields); @@ -9196,8 +9196,10 @@ ha_innobase::update_row( || thd_sql_command(m_user_thd) != SQLCOM_ALTER_TABLE); /* This is not a delete */ - m_prebuilt->upd_node->is_delete - = vers_set_fields && !vers_ins_row + m_prebuilt->upd_node->is_delete = + (vers_set_fields && !vers_ins_row) || + (thd_sql_command(m_user_thd) == SQLCOM_DELETE && + table->versioned(VERS_TIMESTAMP)) ? VERSIONED_DELETE : NO_DELETE; @@ -9323,8 +9325,8 @@ ha_innobase::delete_row( } /* This is a delete */ - m_prebuilt->upd_node->is_delete - = table->versioned_write() && table->vers_end_field()->is_max() + m_prebuilt->upd_node->is_delete = table->versioned_write(VERS_TRX_ID) + && table->vers_end_field()->is_max() ? VERSIONED_DELETE : PLAIN_DELETE; @@ -14453,7 +14455,7 @@ ha_innobase::info_low( set. That way SHOW TABLE STATUS will show the best estimate, while the optimizer never sees the table empty. */ - if (n_rows == 0 && !(flag & HA_STATUS_TIME)) { + if (n_rows == 0 && !(flag & (HA_STATUS_TIME | HA_STATUS_OPEN))) { n_rows++; } diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index 0b322663141..8944f27a041 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -694,6 +694,10 @@ ha_innobase::check_if_supported_inplace_alter( { DBUG_ENTER("check_if_supported_inplace_alter"); + if (altered_table->versioned(VERS_TIMESTAMP)) { + DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED); + } + /* Before 10.2.2 information about virtual columns was not stored in system tables. We need to do a full alter to rebuild proper 10.2.2+ metadata with the information about virtual columns */ @@ -4978,7 +4982,7 @@ new_clustered_failed: field_type |= DATA_UNSIGNED; } - if (altered_table->versioned()) { + if (altered_table->versioned(VERS_TRX_ID)) { if (i == altered_table->s->row_start_field) { field_type |= DATA_VERS_START; } else if (i == diff --git a/storage/innobase/include/data0data.h b/storage/innobase/include/data0data.h index 0dde23a7820..d3361ad8b3b 100644 --- a/storage/innobase/include/data0data.h +++ b/storage/innobase/include/data0data.h @@ -592,17 +592,20 @@ struct dfield_t{ @return the cloned object */ dfield_t* clone(mem_heap_t* heap) const; - /** @return whether this column is the end of the system - version history and points to the past, that is, this record - does not exist in the current time */ - bool is_version_historical_end() const + /** @return system field indicates history row */ + bool vers_history_row() const { - if (!type.is_version_end()) { - return false; + ut_ad(type.vers_sys_end()); + if (type.mtype == DATA_FIXBINARY) { + ut_ad(len == sizeof timestamp_max_bytes); + return 0 != memcmp(data, timestamp_max_bytes, len); + } else { + ut_ad(type.mtype == DATA_INT); + ut_ad(len == sizeof trx_id_max_bytes); + return 0 != memcmp(data, trx_id_max_bytes, len); } - - ut_ad(len == sizeof trx_id_max_bytes); - return memcmp(data, trx_id_max_bytes, sizeof trx_id_max_bytes); + ut_ad(0); + return false; } }; diff --git a/storage/innobase/include/data0type.h b/storage/innobase/include/data0type.h index a6a0c79f363..8dab756c7a5 100644 --- a/storage/innobase/include/data0type.h +++ b/storage/innobase/include/data0type.h @@ -561,17 +561,17 @@ struct dtype_t{ mbminlen=DATA_MBMINLEN(mbminmaxlen); mbmaxlen=DATA_MBMINLEN(mbminmaxlen) */ - /** @return whether this is any system versioned field */ - bool is_any_versioned() const { return prtype & DATA_VERSIONED; } + /** @return whether this is system field */ + bool vers_sys_field() const { return prtype & DATA_VERSIONED; } /** @return whether this is system versioned user field */ bool is_versioned() const { return !(~prtype & DATA_VERSIONED); } - /** @return whether this is the system version start */ - bool is_version_start() const + /** @return whether this is the system field start */ + bool vers_sys_start() const { return (prtype & DATA_VERSIONED) == DATA_VERS_START; } - /** @return whether this is the system version end */ - bool is_version_end() const + /** @return whether this is the system field end */ + bool vers_sys_end() const { return (prtype & DATA_VERSIONED) == DATA_VERS_END; } diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h index 700017b9331..8c5a33312de 100644 --- a/storage/innobase/include/dict0mem.h +++ b/storage/innobase/include/dict0mem.h @@ -653,17 +653,17 @@ struct dict_col_t{ /** @return whether NULL is an allowed value for this column */ bool is_nullable() const { return !(prtype & DATA_NOT_NULL); } - /** @return whether this is any system versioned field */ - bool is_any_versioned() const { return prtype & DATA_VERSIONED; } + /** @return whether this is system field */ + bool vers_sys_field() const { return prtype & DATA_VERSIONED; } /** @return whether this is system versioned */ bool is_versioned() const { return !(~prtype & DATA_VERSIONED); } /** @return whether this is the system version start */ - bool is_version_start() const + bool vers_sys_start() const { return (prtype & DATA_VERSIONED) == DATA_VERS_START; } /** @return whether this is the system version end */ - bool is_version_end() const + bool vers_sys_end() const { return (prtype & DATA_VERSIONED) == DATA_VERS_END; } @@ -1133,6 +1133,20 @@ struct dict_index_t{ n_core_fields = n_fields; n_core_null_bytes = UT_BITS_IN_BYTES(n_nullable); } + + /** Check if record in clustered index is historical row. + @param[in] rec clustered row + @param[in] offsets offsets + @return true if row is historical */ + bool + vers_history_row(const rec_t* rec, const ulint* offsets); + + /** Check if record in secondary index is historical row. + @param[in] rec record in a secondary index + @param[out] history_row true if row is historical + @return true on error */ + bool + vers_history_row(const rec_t* rec, bool &history_row); }; /** The status of online index creation */ diff --git a/storage/innobase/include/dict0types.h b/storage/innobase/include/dict0types.h index 171fbc3efd9..c509f9a1845 100644 --- a/storage/innobase/include/dict0types.h +++ b/storage/innobase/include/dict0types.h @@ -57,6 +57,7 @@ typedef ib_id_t index_id_t; /** The bit pattern corresponding to TRX_ID_MAX */ extern const char trx_id_max_bytes[8]; +extern const char timestamp_max_bytes[7]; /** Error to ignore when we load table dictionary into memory. However, the table and index will be marked as "corrupted", and caller will diff --git a/storage/innobase/include/row0upd.h b/storage/innobase/include/row0upd.h index fead3dee5b1..877b44a5b07 100644 --- a/storage/innobase/include/row0upd.h +++ b/storage/innobase/include/row0upd.h @@ -489,7 +489,7 @@ struct upd_t{ bool affects_versioned() const { for (ulint i = 0; i < n_fields; i++) { - if (fields[i].new_val.type.is_any_versioned()) { + if (fields[i].new_val.type.vers_sys_field()) { return true; } } diff --git a/storage/innobase/include/trx0trx.h b/storage/innobase/include/trx0trx.h index a263dc07290..9f9369b5f67 100644 --- a/storage/innobase/include/trx0trx.h +++ b/storage/innobase/include/trx0trx.h @@ -786,6 +786,7 @@ class trx_mod_table_time_t undo_no_t first; /** First modification of a system versioned column */ undo_no_t first_versioned; + bool vers_by_trx; /** Magic value signifying that a system versioned column of a table was never modified in a transaction. */ @@ -795,7 +796,8 @@ public: /** Constructor @param[in] rows number of modified rows so far */ trx_mod_table_time_t(undo_no_t rows) - : first(rows), first_versioned(UNVERSIONED) {} + : first(rows), first_versioned(UNVERSIONED), + vers_by_trx(false) {} #ifdef UNIV_DEBUG /** Validation @@ -808,13 +810,18 @@ public: #endif /* UNIV_DEBUG */ /** @return if versioned columns were modified */ bool is_versioned() const { return first_versioned != UNVERSIONED; } + bool is_trx_versioned() const + { + return is_versioned() && vers_by_trx; + } /** After writing an undo log record, set is_versioned() if needed @param[in] rows number of modified rows so far */ - void set_versioned(undo_no_t rows) + void set_versioned(undo_no_t rows, bool by_trx_id) { ut_ad(!is_versioned()); first_versioned = rows; + vers_by_trx = by_trx_id; ut_ad(valid()); } diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc index 8210c989816..0a76bca9e48 100644 --- a/storage/innobase/row/row0ins.cc +++ b/storage/innobase/row/row0ins.cc @@ -1568,41 +1568,46 @@ private: ulint& counter; }; -/** Reads sys_trx_end field from clustered index row. +/** Check if record in clustered index is historical row. @param[in] rec clustered row @param[in] offsets offsets -@param[in] index clustered index -@return trx_id_t */ -static -trx_id_t -row_ins_get_sys_trx_end( +@return true if row is historical */ +bool +dict_index_t::vers_history_row( const rec_t* rec, - const ulint* offsets, - const dict_index_t* index) + const ulint* offsets) { - ut_a(index->is_clust()); + ut_a(is_clust()); ulint len; - ulint nfield = dict_col_get_clust_pos( - &index->table->cols[index->table->vers_end], index); - const byte *field = rec_get_nth_field(rec, offsets, nfield, &len); - ut_a(len == 8); - return(mach_read_from_8(field)); + dict_col_t& col= table->cols[table->vers_end]; + ut_ad(col.vers_sys_end()); + ulint nfield = dict_col_get_clust_pos(&col, this); + const byte *data = rec_get_nth_field(rec, offsets, nfield, &len); + if (col.mtype == DATA_FIXBINARY) { + ut_ad(len == sizeof timestamp_max_bytes); + return 0 != memcmp(data, timestamp_max_bytes, len); + } else { + ut_ad(col.mtype == DATA_INT); + ut_ad(len == sizeof trx_id_max_bytes); + return 0 != memcmp(data, trx_id_max_bytes, len); + } + ut_ad(0); + return false; } -/** Performs search at clustered index and returns sys_trx_end if row was found. -@param[in] index secondary index of record +/** Check if record in secondary index is historical row. @param[in] rec record in a secondary index -@return sys_trx_end on success or 0 at failure */ -static -trx_id_t -row_ins_search_sys_trx_end( - dict_index_t* index, - const rec_t* rec) +@param[out] history_row true if row is historical +@return true on error */ +bool +dict_index_t::vers_history_row( + const rec_t* rec, + bool &history_row) { - ut_ad(!index->is_clust()); + ut_ad(!is_clust()); - trx_id_t result = 0; + bool error = false; mem_heap_t* heap = NULL; dict_index_t* clust_index = NULL; ulint offsets_[REC_OFFS_NORMAL_SIZE]; @@ -1613,23 +1618,23 @@ row_ins_search_sys_trx_end( mtr.start(); rec_t* clust_rec = - row_get_clust_rec(BTR_SEARCH_LEAF, rec, index, &clust_index, &mtr); + row_get_clust_rec(BTR_SEARCH_LEAF, rec, this, &clust_index, &mtr); if (clust_rec) { offsets = rec_get_offsets(clust_rec, clust_index, offsets, true, ULINT_UNDEFINED, &heap); - result = - row_ins_get_sys_trx_end(clust_rec, offsets, clust_index); + history_row = clust_index->vers_history_row(clust_rec, offsets); } else { ib::error() << "foreign constraints: secondary index is out of " "sync"; ut_ad(!"secondary index is out of sync"); + error = true; } mtr.commit(); if (heap) { mem_heap_free(heap); } - return(result); + return(error); } /***************************************************************//** @@ -1696,7 +1701,7 @@ row_ins_check_foreign_constraint( } /* System Versioning: if sys_trx_end != Inf, we suppress the foreign key check */ - if (field->is_version_historical_end()) { + if (field->type.vers_sys_end() && field->vers_history_row()) { goto exit_func; } } @@ -1828,19 +1833,18 @@ row_ins_check_foreign_constraint( if (cmp == 0) { if (check_table->versioned()) { - trx_id_t end_trx_id = 0; + bool history_row = false; if (check_index->is_clust()) { - end_trx_id = - row_ins_get_sys_trx_end( - rec, offsets, check_index); - } else if (!(end_trx_id = - row_ins_search_sys_trx_end( - check_index, rec))) { + history_row = check_index-> + vers_history_row(rec, offsets); + } else if (check_index-> + vers_history_row(rec, history_row)) + { break; } - if (end_trx_id != TRX_ID_MAX) { + if (history_row) { continue; } } diff --git a/storage/innobase/row/row0merge.cc b/storage/innobase/row/row0merge.cc index 64f2d9a7a66..23018f68536 100644 --- a/storage/innobase/row/row0merge.cc +++ b/storage/innobase/row/row0merge.cc @@ -2246,12 +2246,11 @@ end_of_index: ut_ad(add_autoinc < dict_table_get_n_user_cols(new_table)); - bool historical_row = false; + bool history_row = false; if (new_table->versioned()) { const dfield_t* dfield = dtuple_get_nth_field( row, new_table->vers_end); - historical_row - = dfield->is_version_historical_end(); + history_row = dfield->vers_history_row(); } dfield_t* dfield; @@ -2259,7 +2258,7 @@ end_of_index: dfield = dtuple_get_nth_field(row, add_autoinc); if (new_table->versioned()) { - if (historical_row) { + if (history_row) { if (dfield_get_type(dfield)->prtype & DATA_NOT_NULL) { err = DB_UNSUPPORTED; my_error(ER_UNSUPPORTED_EXTENSION, MYF(0), @@ -2320,19 +2319,9 @@ end_of_index: } if (old_table->versioned()) { - if (!new_table->versioned() || drop_historical) { - const dict_col_t* col = - &old_table->cols[old_table->vers_end]; - const ulint nfield = - dict_col_get_clust_pos(col, clust_index); - ulint len = 0; - const rec_t* sys_trx_end = rec_get_nth_field( - rec, offsets, nfield, &len); - ut_ad(len == 8); - if (mach_read_from_8(sys_trx_end) - != TRX_ID_MAX) { - continue; - } + if ((!new_table->versioned() || drop_historical) + && clust_index->vers_history_row(rec, offsets)) { + continue; } } else if (new_table->versioned()) { dfield_t* start = diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc index 0b743db6cd5..f3274b498ec 100644 --- a/storage/innobase/row/row0mysql.cc +++ b/storage/innobase/row/row0mysql.cc @@ -833,6 +833,12 @@ handle_new_error: << FK_MAX_CASCADE_DEL << ". Please drop excessive" " foreign constraints and try again"; break; + case DB_UNSUPPORTED: + ib::error() << "Cannot delete/update rows with cascading" + " foreign key constraints in timestamp-based temporal" + " table. Please drop excessive" + " foreign constraints and try again"; + break; default: ib::fatal() << "Unknown error code " << err << ": " << ut_strerr(err); @@ -2042,7 +2048,7 @@ run_again: err = trx->error_state; if (err != DB_SUCCESS) { - +handle_error: que_thr_stop_for_mysql(thr); if (err == DB_RECORD_NOT_FOUND) { @@ -2124,6 +2130,14 @@ run_again: && (node->is_delete == PLAIN_DELETE || node->update->affects_versioned()); + if (vers_set_fields && !prebuilt->versioned_write) + { + // FIXME: timestamp-based update of sys_trx_end in run_again + err = DB_UNSUPPORTED; + trx->error_state = err; + + goto handle_error; + } goto run_again; } diff --git a/storage/innobase/trx/trx0rec.cc b/storage/innobase/trx/trx0rec.cc index be93f5d8f45..1d03b55913a 100644 --- a/storage/innobase/trx/trx0rec.cc +++ b/storage/innobase/trx/trx0rec.cc @@ -2028,9 +2028,11 @@ trx_undo_report_row_operation( && index->table->versioned() && (!rec /* INSERT */ || !update /* DELETE */ - || update->affects_versioned())) { - - time.set_versioned(limit); + || update->affects_versioned())) + { + dict_col_t &col = index->table->cols[index->table->vers_start]; + bool by_trx_id = col.mtype == DATA_INT; + time.set_versioned(limit, by_trx_id); } } diff --git a/storage/innobase/trx/trx0trx.cc b/storage/innobase/trx/trx0trx.cc index e8a5623524b..a5432777ff4 100644 --- a/storage/innobase/trx/trx0trx.cc +++ b/storage/innobase/trx/trx0trx.cc @@ -64,9 +64,15 @@ int thd_deadlock_victim_preference(const MYSQL_THD thd1, const MYSQL_THD thd2); /** The bit pattern corresponding to TRX_ID_MAX */ const char trx_id_max_bytes[8] = { - '\377', '\377', '\377', '\377', '\377', '\377', '\377', '\377' + 0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff }; +/** The bit pattern corresponding to max timestamp */ +const char timestamp_max_bytes[7] = { + 0x7f, 0xff, 0xff, 0xff, 0x0f, 0x42, 0x3f +}; + + static const ulint MAX_DETAILED_ERROR_LEN = 256; /** Set of table_id */ |