diff options
26 files changed, 1122 insertions, 271 deletions
diff --git a/mysql-test/suite/versioning/common.inc b/mysql-test/suite/versioning/common.inc index 288c188c43d..fcd767b140e 100644 --- a/mysql-test/suite/versioning/common.inc +++ b/mysql-test/suite/versioning/common.inc @@ -67,3 +67,6 @@ begin end if; end~~ delimiter ;~~ + +let $default_engine= `select default_engine()`; +let sys_datatype= `select sys_datatype()`; diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result index 161255aeaa7..e3b6ba0876e 100644 --- a/mysql-test/suite/versioning/r/cte.result +++ b/mysql-test/suite/versioning/r/cte.result @@ -24,7 +24,7 @@ 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 vtq_commit_ts(max(sys_trx_start + 0)) 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"; /* All report to 'Bill' */ diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result index 8a22b26fad1..8599cb92b52 100644 --- a/mysql-test/suite/versioning/r/select.result +++ b/mysql-test/suite/versioning/r/select.result @@ -60,21 +60,10 @@ select * from tmp; drop table tmp; end if; end~~ -create procedure test_01() -begin -declare engine varchar(255) default default_engine(); -declare sys_type varchar(255) default sys_datatype(); -declare fields varchar(255) default sys_commit_ts('sys_start'); -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)) - with system versioning - engine ', engine); -prepare stmt from @str; execute stmt; drop prepare stmt; +create or replace table t1 ( +x int unsigned, +y int unsigned +) with system versioning; insert into t1 (x, y) values (0, 100), (1, 101), @@ -87,74 +76,11 @@ insert into t1 (x, y) values (8, 108), (9, 109); set @t0= now(6); -if engine = 'innodb' then -select sys_start from t1 limit 1 into @x0; -end if; delete from t1 where x = 3; delete from t1 where x > 7; insert into t1(x, y) values(3, 33); -select sys_start from t1 where x = 3 and y = 33 into @t1; -if engine = 'innodb' then -set @x1= @t1; -select vtq_commit_ts(@x1) into @t1; -end if; +select sys_trx_start from t1 where x = 3 and y = 33 into @t1; select x, y from t1; -select x as ASOF_x, y from t1 for system_time as of timestamp @t0; -select x as FROMTO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; -select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; -select x as FROMTO_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; -select x as BETWAND_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; -select x as ALL_x, y from t1 for system_time all; -if engine = 'innodb' then -select x as ASOF2_x, y from t1 for system_time as of transaction @x0; -select x as FROMTO2_x, y from t1 for system_time from transaction 0 to transaction @x1; -select x as BETWAND2_x, y from t1 for system_time between transaction 0 and transaction @x1; -select x as FROMTO2_ext_x, y from t1 for system_time transaction from 0 to @x1; -select x as BETWAND2_ext_x, y from t1 for system_time transaction between 0 and @x1; -else -select x as ASOF2_x, y from t1 for system_time as of timestamp @t0; -select x as FROMTO2_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; -select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; -select x as FROMTO2_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; -select x as BETWAND2_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; -end if; -drop table t1; -end~~ -create or replace procedure test_02() -begin -declare engine varchar(255) default default_engine(); -declare sys_type varchar(255) default sys_datatype(); -declare fields varchar(255) default sys_commit_ts('sys_start'); -set @str0= concat('( - x int, - y int, - 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)) - with system versioning - engine ', engine); -set @str= concat('create or replace table t1', @str0); -prepare stmt from @str; execute stmt; drop prepare stmt; -set @str= concat('create or replace table t2', @str0); -prepare stmt from @str; execute stmt; drop prepare stmt; -insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5); -insert into t2 values (1, 2), (2, 1), (3, 1); -set @t0= now(6); -select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x; -select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x; -select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x; -delete from t1; -delete from t2; -select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x -system_time as of timestamp @t0; -select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x -system_time as of timestamp @t0; -select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x -system_time as of timestamp @t0; -drop table t1; -drop table t2; -end~~ -call test_01(); x y 0 100 1 101 @@ -164,6 +90,7 @@ x y 6 106 7 107 3 33 +select x as ASOF_x, y from t1 for system_time as of timestamp @t0; ASOF_x y 0 100 1 101 @@ -175,6 +102,7 @@ ASOF_x y 7 107 8 108 9 109 +select x as FROMTO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; FROMTO_x y 0 100 1 101 @@ -186,6 +114,7 @@ FROMTO_x y 7 107 8 108 9 109 +select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; BETWAND_x y 0 100 1 101 @@ -198,6 +127,7 @@ BETWAND_x y 8 108 9 109 3 33 +select x as FROMTO_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; FROMTO_ext_x y 0 100 1 101 @@ -209,6 +139,7 @@ FROMTO_ext_x y 7 107 8 108 9 109 +select x as BETWAND_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; BETWAND_ext_x y 0 100 1 101 @@ -221,6 +152,7 @@ BETWAND_ext_x y 8 108 9 109 3 33 +select x as ALL_x, y from t1 for system_time all; ALL_x y 0 100 1 101 @@ -290,39 +222,62 @@ BETWAND2_ext_x y 8 108 9 109 3 33 -call test_02(); +create or replace table t1 ( +x int unsigned, +y int unsigned +) with system versioning; +create or replace table t2 ( +x int unsigned, +y int unsigned +) with system versioning; +insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5); +insert into t2 values (1, 2), (2, 1), (3, 1); +set @t0= now(6); +select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x; IJ1_x1 y1 x2 y2 1 1 1 2 1 2 1 2 1 3 1 2 +select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x; LJ1_x1 y1 x2 y2 1 1 1 2 1 2 1 2 1 3 1 2 4 4 NULL NULL 5 5 NULL NULL +select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x; RJ1_x1 y1 x2 y2 1 1 1 2 1 2 1 2 1 3 1 2 NULL NULL 2 1 NULL NULL 3 1 +delete from t1; +delete from t2; +select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x +system_time as of timestamp @t0; IJ2_x1 y1 x2 y2 1 1 1 2 1 2 1 2 1 3 1 2 +select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x +system_time as of timestamp @t0; LJ2_x1 y1 x2 y2 1 1 1 2 1 2 1 2 1 3 1 2 4 4 NULL NULL 5 5 NULL NULL +select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x +system_time as of timestamp @t0; RJ2_x1 y1 x2 y2 1 1 1 2 1 2 1 2 1 3 1 2 NULL NULL 2 1 NULL NULL 3 1 +drop table t1; +drop table t2; create table t1( A int ) with system versioning; @@ -469,8 +424,6 @@ No A B C D 25 1 1 1 1 26 1 1 1 1 27 1 1 1 1 -drop procedure test_01; -drop procedure test_02; drop procedure verify_vtq; drop procedure innodb_verify_vtq; drop function default_engine; diff --git a/mysql-test/suite/versioning/r/select_sp.result b/mysql-test/suite/versioning/r/select_sp.result new file mode 100644 index 00000000000..9ced07f03e1 --- /dev/null +++ b/mysql-test/suite/versioning/r/select_sp.result @@ -0,0 +1,478 @@ +set @@session.time_zone='+00:00'; +select ifnull(max(transaction_id), 0) into @start_trx_id from information_schema.innodb_vtq; +create procedure if not exists verify_vtq() +begin +set @i= 0; +select +@i:= @i + 1 as No, +transaction_id > 0 as A, +commit_id > transaction_id as B, +begin_timestamp > '1-1-1 0:0:0' as C, +commit_timestamp >= begin_timestamp as D +from information_schema.innodb_vtq +where transaction_id > @start_trx_id; +select ifnull(max(transaction_id), 0) +into @start_trx_id +from information_schema.innodb_vtq; +end~~ +create function if not exists default_engine() +returns varchar(255) +deterministic +begin +declare e varchar(255); +select lower(engine) from information_schema.engines where support='DEFAULT' into e; +return e; +end~~ +create function if not exists sys_datatype() +returns varchar(255) +deterministic +begin +if default_engine() = 'innodb' then +return 'bigint unsigned'; +elseif default_engine() = 'myisam' then +return 'timestamp(6)'; +end if; +return NULL; +end~~ +create function if not exists sys_commit_ts(sys_field varchar(255)) +returns varchar(255) +deterministic +begin +if default_engine() = 'innodb' then +return concat('vtq_commit_ts(', sys_field, ')'); +elseif default_engine() = 'myisam' then +return sys_field; +end if; +return NULL; +end~~ +create procedure if not exists innodb_verify_vtq(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; +end~~ +create procedure test_01() +begin +declare engine varchar(255) default default_engine(); +declare sys_type varchar(255) default sys_datatype(); +declare fields varchar(255) default sys_commit_ts('sys_start'); +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)) + with system versioning + engine ', engine); +prepare stmt from @str; execute stmt; drop prepare stmt; +insert into t1 (x, y) values +(0, 100), +(1, 101), +(2, 102), +(3, 103), +(4, 104), +(5, 105), +(6, 106), +(7, 107), +(8, 108), +(9, 109); +set @t0= now(6); +if engine = 'innodb' then +select sys_start from t1 limit 1 into @x0; +end if; +delete from t1 where x = 3; +delete from t1 where x > 7; +insert into t1(x, y) values(3, 33); +select sys_start from t1 where x = 3 and y = 33 into @t1; +if engine = 'innodb' then +set @x1= @t1; +select vtq_commit_ts(@x1) into @t1; +end if; +select x, y from t1; +select x as ASOF_x, y from t1 for system_time as of timestamp @t0; +select x as FROMTO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; +select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; +select x as FROMTO_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; +select x as BETWAND_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; +select x as ALL_x, y from t1 for system_time all; +if engine = 'innodb' then +select x as ASOF2_x, y from t1 for system_time as of transaction @x0; +select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1; +select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1; +select x as FROMTO2_ext_x, y from t1 for system_time transaction from @x0 to @x1; +select x as BETWAND2_ext_x, y from t1 for system_time transaction between @x0 and @x1; +else +select x as ASOF2_x, y from t1 for system_time as of timestamp @t0; +select x as FROMTO2_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; +select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; +select x as FROMTO2_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; +select x as BETWAND2_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; +end if; +drop table t1; +end~~ +create or replace procedure test_02() +begin +declare engine varchar(255) default default_engine(); +declare sys_type varchar(255) default sys_datatype(); +declare fields varchar(255) default sys_commit_ts('sys_start'); +set @str0= concat('( + x int, + y int, + 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)) + with system versioning + engine ', engine); +set @str= concat('create or replace table t1', @str0); +prepare stmt from @str; execute stmt; drop prepare stmt; +set @str= concat('create or replace table t2', @str0); +prepare stmt from @str; execute stmt; drop prepare stmt; +insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5); +insert into t2 values (1, 2), (2, 1), (3, 1); +set @t0= now(6); +select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x; +select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x; +select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x; +delete from t1; +delete from t2; +select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x +system_time as of timestamp @t0; +select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x +system_time as of timestamp @t0; +select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x +system_time as of timestamp @t0; +drop table t1; +drop table t2; +end~~ +call test_01(); +x y +0 100 +1 101 +2 102 +4 104 +5 105 +6 106 +7 107 +3 33 +ASOF_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +FROMTO_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +BETWAND_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +3 33 +FROMTO_ext_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +BETWAND_ext_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +3 33 +ALL_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +3 33 +ASOF2_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +FROMTO2_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +BETWAND2_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +3 33 +FROMTO2_ext_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +BETWAND2_ext_x y +0 100 +1 101 +2 102 +3 103 +4 104 +5 105 +6 106 +7 107 +8 108 +9 109 +3 33 +call test_02(); +IJ1_x1 y1 x2 y2 +1 1 1 2 +1 2 1 2 +1 3 1 2 +LJ1_x1 y1 x2 y2 +1 1 1 2 +1 2 1 2 +1 3 1 2 +4 4 NULL NULL +5 5 NULL NULL +RJ1_x1 y1 x2 y2 +1 1 1 2 +1 2 1 2 +1 3 1 2 +NULL NULL 2 1 +NULL NULL 3 1 +IJ2_x1 y1 x2 y2 +1 1 1 2 +1 2 1 2 +1 3 1 2 +LJ2_x1 y1 x2 y2 +1 1 1 2 +1 2 1 2 +1 3 1 2 +4 4 NULL NULL +5 5 NULL NULL +RJ2_x1 y1 x2 y2 +1 1 1 2 +1 2 1 2 +1 3 1 2 +NULL NULL 2 1 +NULL NULL 3 1 +create table t1( +A int +) with system versioning; +insert into t1 values(1); +select * from t1; +A +1 +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 +create or replace table t1 (x int) with system versioning; +insert into t1 values (1); +select * from t1 for system_time all for update; +ERROR HY000: Versioned SELECT write-locking of history rows +create or replace table t1 (a int not null auto_increment primary key) with system versioning; +select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; +a +create or replace table t1 (a int) with system versioning; +create or replace table t2 (a int) with system versioning; +insert into t1 values(1); +insert into t2 values(1); +create view v1 as select * from t2 inner join t1 using (a); +select * from v1; +a +1 +drop view v1; +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +create view vt1 as select a from t1; +select * from t1 natural join vt1; +a +1 +drop view vt1; +create or replace table t1(x int) with system versioning; +select * from (t1 as r left join t1 as u using (x)), t1; +x x +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +create trigger read_end after update on t1 +for each row set @end = old.sys_trx_end; +update t1 set a=2; +select @end; +@end +MAX_RESULT +create or replace table t1 (a int) with system versioning; +create or replace table t2 (b int) with system versioning; +insert into t1 values (1); +insert into t2 values (2); +select * from (select * from t1 cross join t2) as tmp; +a b +1 2 +select * from (select * from (select * from t1 cross join t2) as tmp1) as tmp2; +a b +1 2 +select * from (select * from t1 cross join t2 for system_time as of timestamp '0-0-0') as tmp; +a b +create or replace table t1(a1 int) with system versioning; +create or replace table t2(a2 int) with system versioning; +insert into t1 values(1),(2); +insert into t2 values(1),(2); +select * from t1 for system_time all natural left join t2 for system_time all; +a1 a2 +1 1 +2 1 +1 2 +2 2 +create or replace table t1(a1 int) with system versioning; +create or replace table t2(a2 int) with system versioning; +insert into t1 values(1),(2); +insert into t2 values(1),(2); +create or replace view v1 as select a1 from t1; +select * from v1 natural join t2; +a1 a2 +1 1 +2 1 +1 2 +2 2 +select * from v1 natural left join t2; +a1 a2 +1 1 +2 1 +1 2 +2 2 +select * from v1 natural right join t2; +a2 a1 +1 1 +2 1 +1 2 +2 2 +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +insert into t1 values (2); +insert into t1 values (3); +select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; +a a +2 1 +3 1 +2 2 +3 2 +2 3 +3 3 +1 NULL +create or replace table t1 (x int) with system versioning; +create or replace table t2 (y int) with system versioning; +insert into t1 values (1), (2), (3); +delete from t1 where x = 3; +insert into t2 values (1); +select * from t1, t2 system_time all; +x y +1 1 +2 1 +3 1 +select * from t1 for system_time all, t2 for system_time all system_time all; +ERROR HY000: Unused clause: 'SYSTEM_TIME' +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; +drop procedure verify_vtq; +drop procedure innodb_verify_vtq; +drop function default_engine; +drop function sys_commit_ts; +drop function sys_datatype; diff --git a/mysql-test/suite/versioning/r/truncate.result b/mysql-test/suite/versioning/r/truncate.result index 9d8b92d6254..5651cffb179 100644 --- a/mysql-test/suite/versioning/r/truncate.result +++ b/mysql-test/suite/versioning/r/truncate.result @@ -151,7 +151,7 @@ select * from t for system_time before timestamp @ts1; a 1 insert into t values (33); -select max(sys_trx_start) from t into @tx; +select max(sys_trx_start + 0) from t into @tx; select * from t for system_time before transaction @tx; a 1 diff --git a/mysql-test/suite/versioning/r/view.result b/mysql-test/suite/versioning/r/view.result index 08c67cfa693..96ff29bd73b 100644 --- a/mysql-test/suite/versioning/r/view.result +++ b/mysql-test/suite/versioning/r/view.result @@ -1,3 +1,65 @@ +set @@session.time_zone='+00:00'; +select ifnull(max(transaction_id), 0) into @start_trx_id from information_schema.innodb_vtq; +create procedure if not exists verify_vtq() +begin +set @i= 0; +select +@i:= @i + 1 as No, +transaction_id > 0 as A, +commit_id > transaction_id as B, +begin_timestamp > '1-1-1 0:0:0' as C, +commit_timestamp >= begin_timestamp as D +from information_schema.innodb_vtq +where transaction_id > @start_trx_id; +select ifnull(max(transaction_id), 0) +into @start_trx_id +from information_schema.innodb_vtq; +end~~ +create function if not exists default_engine() +returns varchar(255) +deterministic +begin +declare e varchar(255); +select lower(engine) from information_schema.engines where support='DEFAULT' into e; +return e; +end~~ +create function if not exists sys_datatype() +returns varchar(255) +deterministic +begin +if default_engine() = 'innodb' then +return 'bigint unsigned'; +elseif default_engine() = 'myisam' then +return 'timestamp(6)'; +end if; +return NULL; +end~~ +create function if not exists sys_commit_ts(sys_field varchar(255)) +returns varchar(255) +deterministic +begin +if default_engine() = 'innodb' then +return concat('vtq_commit_ts(', sys_field, ')'); +elseif default_engine() = 'myisam' then +return sys_field; +end if; +return NULL; +end~~ +create procedure if not exists innodb_verify_vtq(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; +end~~ create table t1 (x int) with system versioning engine innodb; insert into t1 values (1); select now(6) into @t1; @@ -23,7 +85,7 @@ x create or replace view vt1 as select * from t1; show create view vt1; View Create View character_set_client collation_connection -vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `x`,`t1`.`sys_trx_start` AS `sys_trx_start`,`t1`.`sys_trx_end` AS `sys_trx_end` from `t1` FOR SYSTEM_TIME ALL where `t1`.`sys_trx_end` = 18446744073709551615 latin1 latin1_swedish_ci +vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `x`,`t1`.`sys_trx_start` AS `sys_trx_start`,`t1`.`sys_trx_end` AS `sys_trx_end` from `t1` FOR SYSTEM_TIME ALL where `t1`.`sys_trx_end` = TIMESTAMP'2038-01-19 03:14:07' latin1 latin1_swedish_ci drop view vt1; drop view vt2; create view vt1 as select * from t1 for system_time all; @@ -116,16 +178,21 @@ create or replace table t3 (x int); create or replace view vt1 as select * from t1, t2, t3; show create view vt1; View Create View character_set_client collation_connection -vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t3`.`x` AS `x`,`t1`.`sys_trx_start` AS `sys_trx_start`,`t1`.`sys_trx_end` AS `sys_trx_end` from ((`t1` FOR SYSTEM_TIME ALL join `t2` FOR SYSTEM_TIME ALL) join `t3`) where `t1`.`sys_trx_end` = 18446744073709551615 and `t2`.`sys_trx_end` = 18446744073709551615 latin1 latin1_swedish_ci +vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t3`.`x` AS `x`,`t1`.`sys_trx_start` AS `sys_trx_start`,`t1`.`sys_trx_end` AS `sys_trx_end` from ((`t1` FOR SYSTEM_TIME ALL join `t2` FOR SYSTEM_TIME ALL) join `t3`) where `t1`.`sys_trx_end` = TIMESTAMP'2038-01-19 03:14:07' and `t2`.`sys_trx_end` = TIMESTAMP'2038-01-19 03:14:07' latin1 latin1_swedish_ci create or replace view vt1 as select * from t3, t2, t1; show create view vt1; View Create View character_set_client collation_connection -vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t3`.`x` AS `x`,`t2`.`b` AS `b`,`t1`.`a` AS `a`,`t2`.`sys_trx_start` AS `sys_trx_start`,`t2`.`sys_trx_end` AS `sys_trx_end` from ((`t3` join `t2` FOR SYSTEM_TIME ALL) join `t1` FOR SYSTEM_TIME ALL) where `t2`.`sys_trx_end` = 18446744073709551615 and `t1`.`sys_trx_end` = 18446744073709551615 latin1 latin1_swedish_ci +vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t3`.`x` AS `x`,`t2`.`b` AS `b`,`t1`.`a` AS `a`,`t2`.`sys_trx_start` AS `sys_trx_start`,`t2`.`sys_trx_end` AS `sys_trx_end` from ((`t3` join `t2` FOR SYSTEM_TIME ALL) join `t1` FOR SYSTEM_TIME ALL) where `t2`.`sys_trx_end` = TIMESTAMP'2038-01-19 03:14:07' and `t1`.`sys_trx_end` = TIMESTAMP'2038-01-19 03:14:07' latin1 latin1_swedish_ci create or replace view vt1 as select a, t2.sys_trx_end as endo from t3, t1, t2; show create view vt1; View Create View character_set_client collation_connection -vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`sys_trx_end` AS `endo`,`t2`.`sys_trx_start` AS `sys_trx_start` from ((`t3` join `t1` FOR SYSTEM_TIME ALL) join `t2` FOR SYSTEM_TIME ALL) where `t1`.`sys_trx_end` = 18446744073709551615 and `t2`.`sys_trx_end` = 18446744073709551615 latin1 latin1_swedish_ci +vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`sys_trx_end` AS `endo`,`t2`.`sys_trx_start` AS `sys_trx_start` from ((`t3` join `t1` FOR SYSTEM_TIME ALL) join `t2` FOR SYSTEM_TIME ALL) where `t1`.`sys_trx_end` = TIMESTAMP'2038-01-19 03:14:07' and `t2`.`sys_trx_end` = TIMESTAMP'2038-01-19 03:14:07' latin1 latin1_swedish_ci create or replace view vvt1 as select * from t1, t2, vt1; ERROR HY000: Creating VIEW `vvt1` is prohibited: versioned VIEW `vt1` in query! drop view vt1, vt12; drop table t1, t2, t3; +drop procedure verify_vtq; +drop procedure innodb_verify_vtq; +drop function default_engine; +drop function sys_commit_ts; +drop function sys_datatype; diff --git a/mysql-test/suite/versioning/r/vtmd.result b/mysql-test/suite/versioning/r/vtmd.result index d2cb726ac6f..264795c668e 100644 --- a/mysql-test/suite/versioning/r/vtmd.result +++ b/mysql-test/suite/versioning/r/vtmd.result @@ -37,7 +37,7 @@ set @start= null; select start from tmp_vtmd for system_time all order by start limit 1 into @start; select @start > 0 and @start < @inf; select -start = @start as A_start, +start + 0 = @start as A_start, (@start:= end) and end = @inf as B_end, name, substr(archive_name, 1, instr(archive_name, '_')) as C_archive_name diff --git a/mysql-test/suite/versioning/t/cte.test b/mysql-test/suite/versioning/t/cte.test index 7a35352f7c8..50680f2f375 100644 --- a/mysql-test/suite/versioning/t/cte.test +++ b/mysql-test/suite/versioning/t/cte.test @@ -28,7 +28,7 @@ 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 vtq_commit_ts(max(sys_trx_start + 0)) 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"; diff --git a/mysql-test/suite/versioning/t/select.test b/mysql-test/suite/versioning/t/select.test index ef8dcb85664..5dc5f603d54 100644 --- a/mysql-test/suite/versioning/t/select.test +++ b/mysql-test/suite/versioning/t/select.test @@ -1,119 +1,106 @@ -- source suite/versioning/common.inc -delimiter ~~; -create procedure test_01() -begin - declare engine varchar(255) default default_engine(); - declare sys_type varchar(255) default sys_datatype(); - declare fields varchar(255) default sys_commit_ts('sys_start'); - - 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)) - with system versioning - engine ', engine); - prepare stmt from @str; execute stmt; drop prepare stmt; - insert into t1 (x, y) values - (0, 100), - (1, 101), - (2, 102), - (3, 103), - (4, 104), - (5, 105), - (6, 106), - (7, 107), - (8, 108), - (9, 109); - set @t0= now(6); - if engine = 'innodb' then - select sys_start from t1 limit 1 into @x0; - end if; - - delete from t1 where x = 3; - delete from t1 where x > 7; - - insert into t1(x, y) values(3, 33); - select sys_start from t1 where x = 3 and y = 33 into @t1; - if engine = 'innodb' then - set @x1= @t1; - select vtq_commit_ts(@x1) into @t1; - end if; - - select x, y from t1; - select x as ASOF_x, y from t1 for system_time as of timestamp @t0; - select x as FROMTO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; - select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; - select x as FROMTO_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; - select x as BETWAND_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; - select x as ALL_x, y from t1 for system_time all; - - if engine = 'innodb' then - select x as ASOF2_x, y from t1 for system_time as of transaction @x0; - select x as FROMTO2_x, y from t1 for system_time from transaction 0 to transaction @x1; - select x as BETWAND2_x, y from t1 for system_time between transaction 0 and transaction @x1; - select x as FROMTO2_ext_x, y from t1 for system_time transaction from 0 to @x1; - select x as BETWAND2_ext_x, y from t1 for system_time transaction between 0 and @x1; - else - select x as ASOF2_x, y from t1 for system_time as of timestamp @t0; - select x as FROMTO2_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; - select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; - select x as FROMTO2_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; - select x as BETWAND2_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; - end if; - - drop table t1; -end~~ - -create or replace procedure test_02() -begin - declare engine varchar(255) default default_engine(); - declare sys_type varchar(255) default sys_datatype(); - declare fields varchar(255) default sys_commit_ts('sys_start'); - - set @str0= concat('( - x int, - y int, - 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)) - with system versioning - engine ', engine); - set @str= concat('create or replace table t1', @str0); - prepare stmt from @str; execute stmt; drop prepare stmt; - set @str= concat('create or replace table t2', @str0); - prepare stmt from @str; execute stmt; drop prepare stmt; - - insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5); - insert into t2 values (1, 2), (2, 1), (3, 1); - set @t0= now(6); - - select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x; - select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x; - select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x; - - delete from t1; - delete from t2; - - select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x - system_time as of timestamp @t0; - select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x - system_time as of timestamp @t0; - select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x - system_time as of timestamp @t0; - - drop table t1; - drop table t2; -end~~ -delimiter ;~~ - -call test_01(); -call test_02(); - -# wildcard expansion on hidden fields. +# test_01 + +create or replace table t1 ( + x int unsigned, + y int unsigned +) with system versioning; + +insert into t1 (x, y) values + (0, 100), + (1, 101), + (2, 102), + (3, 103), + (4, 104), + (5, 105), + (6, 106), + (7, 107), + (8, 108), + (9, 109); + +set @t0= now(6); +if ($default_engine == 'innodb') +{ +--disable_query_log + select sys_trx_start from t1 limit 1 into @x0; +--enable_query_log +} + +delete from t1 where x = 3; +delete from t1 where x > 7; + +insert into t1(x, y) values(3, 33); +select sys_trx_start from t1 where x = 3 and y = 33 into @t1; +if ($default_engine == 'innodb') +{ +--disable_query_log + set @x1= @t1; + select vtq_commit_ts(@x1) into @t1; +--enable_query_log +} + +select x, y from t1; +select x as ASOF_x, y from t1 for system_time as of timestamp @t0; +select x as FROMTO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; +select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; +select x as FROMTO_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; +select x as BETWAND_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; +select x as ALL_x, y from t1 for system_time all; + +--disable_query_log +if ($default_engine == 'innodb') +{ + select x as ASOF2_x, y from t1 for system_time as of transaction @x0; + select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1; + select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1; + select x as FROMTO2_ext_x, y from t1 for system_time transaction from @x0 to @x1; + select x as BETWAND2_ext_x, y from t1 for system_time transaction between @x0 and @x1; +} +if ($default_engine != 'innodb') +{ + select x as ASOF2_x, y from t1 for system_time as of timestamp @t0; + select x as FROMTO2_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; + select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; + select x as FROMTO2_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; + select x as BETWAND2_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; +} +--enable_query_log + +# test_02 + +create or replace table t1 ( + x int unsigned, + y int unsigned +) with system versioning; +create or replace table t2 ( + x int unsigned, + y int unsigned +) with system versioning; + +insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5); +insert into t2 values (1, 2), (2, 1), (3, 1); +set @t0= now(6); + +select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x; +select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x; +select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x; + +delete from t1; +delete from t2; + +select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x +system_time as of timestamp @t0; +select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x +system_time as of timestamp @t0; +select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x +system_time as of timestamp @t0; + +drop table t1; +drop table t2; + +# Wildcard expansion on hidden fields + create table t1( A int ) with system versioning; @@ -205,7 +192,4 @@ drop table t1, t2; call innodb_verify_vtq(27); -drop procedure test_01; -drop procedure test_02; - -- source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/select_sp.combinations b/mysql-test/suite/versioning/t/select_sp.combinations new file mode 100644 index 00000000000..75fb20d9f5e --- /dev/null +++ b/mysql-test/suite/versioning/t/select_sp.combinations @@ -0,0 +1,5 @@ +[innodb] +default-storage-engine=innodb + +[myisam] +default-storage-engine=myisam diff --git a/mysql-test/suite/versioning/t/select_sp.test b/mysql-test/suite/versioning/t/select_sp.test new file mode 100644 index 00000000000..23921e9580d --- /dev/null +++ b/mysql-test/suite/versioning/t/select_sp.test @@ -0,0 +1,211 @@ +-- source suite/versioning/common.inc + +delimiter ~~; +create procedure test_01() +begin + declare engine varchar(255) default default_engine(); + declare sys_type varchar(255) default sys_datatype(); + declare fields varchar(255) default sys_commit_ts('sys_start'); + + 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)) + with system versioning + engine ', engine); + prepare stmt from @str; execute stmt; drop prepare stmt; + insert into t1 (x, y) values + (0, 100), + (1, 101), + (2, 102), + (3, 103), + (4, 104), + (5, 105), + (6, 106), + (7, 107), + (8, 108), + (9, 109); + set @t0= now(6); + if engine = 'innodb' then + select sys_start from t1 limit 1 into @x0; + end if; + + delete from t1 where x = 3; + delete from t1 where x > 7; + + insert into t1(x, y) values(3, 33); + select sys_start from t1 where x = 3 and y = 33 into @t1; + if engine = 'innodb' then + set @x1= @t1; + select vtq_commit_ts(@x1) into @t1; + end if; + + select x, y from t1; + select x as ASOF_x, y from t1 for system_time as of timestamp @t0; + select x as FROMTO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; + select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; + select x as FROMTO_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; + select x as BETWAND_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; + select x as ALL_x, y from t1 for system_time all; + + if engine = 'innodb' then + select x as ASOF2_x, y from t1 for system_time as of transaction @x0; + select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1; + select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1; + select x as FROMTO2_ext_x, y from t1 for system_time transaction from @x0 to @x1; + select x as BETWAND2_ext_x, y from t1 for system_time transaction between @x0 and @x1; + else + select x as ASOF2_x, y from t1 for system_time as of timestamp @t0; + select x as FROMTO2_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; + select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; + select x as FROMTO2_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1; + select x as BETWAND2_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1; + end if; + + drop table t1; +end~~ + +create or replace procedure test_02() +begin + declare engine varchar(255) default default_engine(); + declare sys_type varchar(255) default sys_datatype(); + declare fields varchar(255) default sys_commit_ts('sys_start'); + + set @str0= concat('( + x int, + y int, + 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)) + with system versioning + engine ', engine); + set @str= concat('create or replace table t1', @str0); + prepare stmt from @str; execute stmt; drop prepare stmt; + set @str= concat('create or replace table t2', @str0); + prepare stmt from @str; execute stmt; drop prepare stmt; + + insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5); + insert into t2 values (1, 2), (2, 1), (3, 1); + set @t0= now(6); + + select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x; + select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x; + select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x; + + delete from t1; + delete from t2; + + select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x + system_time as of timestamp @t0; + select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x + system_time as of timestamp @t0; + select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x + system_time as of timestamp @t0; + + drop table t1; + drop table t2; +end~~ +delimiter ;~~ + +call test_01(); +call test_02(); + +# wildcard expansion on hidden fields. +create table t1( + A int +) with system versioning; +insert into t1 values(1); +select * from t1; + +create or replace table t1 (x int); +insert into t1 values (1); +--error ER_VERSIONING_REQUIRED +select * from t1 for system_time all; + +create or replace table t1 (x int) with system versioning; +insert into t1 values (1); +--error ER_VERS_HISTORY_LOCK +select * from t1 for system_time all for update; + +create or replace table t1 (a int not null auto_increment primary key) with system versioning; +select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; + +create or replace table t1 (a int) with system versioning; +create or replace table t2 (a int) with system versioning; +insert into t1 values(1); +insert into t2 values(1); +create view v1 as select * from t2 inner join t1 using (a); +select * from v1; +drop view v1; + +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +create view vt1 as select a from t1; +select * from t1 natural join vt1; +drop view vt1; + +create or replace table t1(x int) with system versioning; +select * from (t1 as r left join t1 as u using (x)), t1; + +# @end should be max +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +create trigger read_end after update on t1 + for each row set @end = old.sys_trx_end; +update t1 set a=2; +--replace_result 18446744073709551615 MAX_RESULT "2038-01-19 03:14:07.000000" MAX_RESULT +select @end; + +create or replace table t1 (a int) with system versioning; +create or replace table t2 (b int) with system versioning; +insert into t1 values (1); +insert into t2 values (2); +select * from (select * from t1 cross join t2) as tmp; +select * from (select * from (select * from t1 cross join t2) as tmp1) as tmp2; +select * from (select * from t1 cross join t2 for system_time as of timestamp '0-0-0') as tmp; + +create or replace table t1(a1 int) with system versioning; +create or replace table t2(a2 int) with system versioning; +insert into t1 values(1),(2); +insert into t2 values(1),(2); +select * from t1 for system_time all natural left join t2 for system_time all; + +# natural join of a view and table +create or replace table t1(a1 int) with system versioning; +create or replace table t2(a2 int) with system versioning; +insert into t1 values(1),(2); +insert into t2 values(1),(2); +create or replace view v1 as select a1 from t1; + +select * from v1 natural join t2; +select * from v1 natural left join t2; +select * from v1 natural right join t2; + +create or replace table t1 (a int) with system versioning; +insert into t1 values (1); +insert into t1 values (2); +insert into t1 values (3); +select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; + +create or replace table t1 (x int) with system versioning; +create or replace table t2 (y int) with system versioning; +insert into t1 values (1), (2), (3); +delete from t1 where x = 3; +insert into t2 values (1); +select * from t1, t2 system_time all; + +--error ER_VERS_UNUSED_CLAUSE +select * from t1 for system_time all, t2 for system_time all system_time all; + +drop view v1; +drop table t1, t2; + +call innodb_verify_vtq(27); + +drop procedure test_01; +drop procedure test_02; + +-- source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/truncate.test b/mysql-test/suite/versioning/t/truncate.test index b51e2ad9311..97362c7423b 100644 --- a/mysql-test/suite/versioning/t/truncate.test +++ b/mysql-test/suite/versioning/t/truncate.test @@ -110,7 +110,7 @@ update t set a=22 where a=2; select * from t for system_time all; select * from t for system_time before timestamp @ts1; insert into t values (33); -select max(sys_trx_start) from t into @tx; +select max(sys_trx_start + 0) from t into @tx; select * from t for system_time before transaction @tx; truncate t for system_time before timestamp @ts1; select * from t for system_time all; diff --git a/mysql-test/suite/versioning/t/view.test b/mysql-test/suite/versioning/t/view.test index 64be35f70c0..5b7b267ff38 100644 --- a/mysql-test/suite/versioning/t/view.test +++ b/mysql-test/suite/versioning/t/view.test @@ -1,4 +1,5 @@ -- source include/have_innodb.inc +-- source suite/versioning/common.inc create table t1 (x int) with system versioning engine innodb; insert into t1 values (1); @@ -89,3 +90,5 @@ create or replace view vvt1 as select * from t1, t2, vt1; drop view vt1, vt12; drop table t1, t2, t3; + +-- source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/vtmd.test b/mysql-test/suite/versioning/t/vtmd.test index b81674f675e..5d6b09a56e0 100644 --- a/mysql-test/suite/versioning/t/vtmd.test +++ b/mysql-test/suite/versioning/t/vtmd.test @@ -46,7 +46,7 @@ begin select start from tmp_vtmd for system_time all order by start limit 1 into @start; select @start > 0 and @start < @inf; select - start = @start as A_start, + start + 0 = @start as A_start, (@start:= end) and end = @inf as B_end, name, substr(archive_name, 1, instr(archive_name, '_')) as C_archive_name diff --git a/sql/field.cc b/sql/field.cc index ac0e59773be..cd83e61ba43 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -1999,6 +1999,40 @@ bool Field_num::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate) } +bool Field_vers_system::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate, ulonglong trx_id) +{ + ASSERT_COLUMN_MARKED_FOR_READ; + DBUG_ASSERT(ltime); + if (!table || !table->s) + return true; + DBUG_ASSERT(table->versioned_by_engine() || + (table->versioned() && table->s->table_category == TABLE_CATEGORY_TEMPORARY)); + if (!trx_id) + return true; + if (trx_id == ULONGLONG_MAX) + { + get_thd()->variables.time_zone->gmt_sec_to_TIME(ltime, TIMESTAMP_MAX_VALUE); + return false; + } + if (cached == trx_id) + { + *ltime= cache; + return false; + } + handlerton *hton= table->file->partition_ht(); + DBUG_ASSERT(hton); + DBUG_ASSERT(hton->vers_query_trx_id); + bool found= hton->vers_query_trx_id(get_thd(), &cache, trx_id, VTQ_COMMIT_TS); + if (found) + { + *ltime= cache; + cached= trx_id; + return false; + } + return true; +} + + Field_str::Field_str(uchar *ptr_arg,uint32 len_arg, uchar *null_ptr_arg, uchar null_bit_arg, utype unireg_check_arg, const char *field_name_arg, CHARSET_INFO *charset_arg) @@ -10373,7 +10407,8 @@ Field *make_field(TABLE_SHARE *share, Field::geometry_type geom_type, uint srid, Field::utype unireg_check, TYPELIB *interval, - const char *field_name) + const char *field_name, + uint32 flags) { uchar *UNINIT_VAR(bit_ptr); uchar UNINIT_VAR(bit_offset); @@ -10527,11 +10562,22 @@ Field *make_field(TABLE_SHARE *share, f_is_zerofill(pack_flag) != 0, f_is_dec(pack_flag) == 0); case MYSQL_TYPE_LONGLONG: - return new (mem_root) - Field_longlong(ptr,field_length,null_pos,null_bit, - unireg_check, field_name, - f_is_zerofill(pack_flag) != 0, - f_is_dec(pack_flag) == 0); + if (flags & (VERS_SYS_START_FLAG|VERS_SYS_END_FLAG)) + { + return new (mem_root) + Field_vers_system(ptr, field_length, null_pos, null_bit, + unireg_check, field_name, + f_is_zerofill(pack_flag) != 0, + f_is_dec(pack_flag) == 0); + } + else + { + return new (mem_root) + Field_longlong(ptr,field_length,null_pos,null_bit, + unireg_check, field_name, + f_is_zerofill(pack_flag) != 0, + f_is_dec(pack_flag) == 0); + } case MYSQL_TYPE_TIMESTAMP: { uint dec= field_length > MAX_DATETIME_WIDTH ? diff --git a/sql/field.h b/sql/field.h index 28d47b337a7..efd3fe18e6f 100644 --- a/sql/field.h +++ b/sql/field.h @@ -1402,7 +1402,7 @@ public: FIELD_FLAGS_COLUMN_FORMAT; } - bool vers_sys_field() + bool vers_sys_field() const { return flags & (VERS_SYS_START_FLAG | VERS_SYS_END_FLAG); } @@ -2130,6 +2130,29 @@ public: }; +class Field_vers_system :public Field_longlong { + MYSQL_TIME cache; + ulonglong cached; +public: + Field_vers_system(uchar *ptr_arg, uint32 len_arg, uchar *null_ptr_arg, + uchar null_bit_arg, + enum utype unireg_check_arg, const char *field_name_arg, + bool zero_arg, bool unsigned_arg) + :Field_longlong(ptr_arg, len_arg, null_ptr_arg, null_bit_arg, + unireg_check_arg, field_name_arg, zero_arg,unsigned_arg), + cached(0) + {} + enum_field_types real_type() const { return MYSQL_TYPE_LONGLONG; } + enum_field_types type() const { return MYSQL_TYPE_DATETIME;} + uint size_of() const { return sizeof(*this); } + bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate, ulonglong trx_id); + bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate) + { + return get_date(ltime, fuzzydate, (ulonglong) val_int()); + } +}; + + class Field_float :public Field_real { public: Field_float(uchar *ptr_arg, uint32 len_arg, uchar *null_ptr_arg, @@ -3778,7 +3801,8 @@ Field *make_field(TABLE_SHARE *share, MEM_ROOT *mem_root, CHARSET_INFO *cs, Field::geometry_type geom_type, uint srid, Field::utype unireg_check, - TYPELIB *interval, const char *field_name); + TYPELIB *interval, const char *field_name, + uint32 flags); /* Create field class for CREATE TABLE @@ -3965,7 +3989,7 @@ public: (uint32)length, null_pos, null_bit, pack_flag, sql_type, charset, geom_type, srid, unireg_check, interval, - field_name_arg); + field_name_arg, flags); } Field *make_field(TABLE_SHARE *share, MEM_ROOT *mem_root, const char *field_name_arg) diff --git a/sql/item.cc b/sql/item.cc index 0df22234f88..117662d9c79 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6325,9 +6325,18 @@ Field *Item::tmp_table_field_from_field_type(TABLE *table, break; #ifdef HAVE_LONG_LONG case MYSQL_TYPE_LONGLONG: - field= new (mem_root) - Field_longlong((uchar*) 0, max_length, null_ptr, 0, Field::NONE, - name, 0, unsigned_flag); + if (field_flags() & (VERS_SYS_START_FLAG|VERS_SYS_END_FLAG)) + { + field= new (mem_root) + Field_vers_system((uchar*) 0, max_length, null_ptr, 0, Field::NONE, + name, 0, unsigned_flag); + } + else + { + field= new (mem_root) + Field_longlong((uchar*) 0, max_length, null_ptr, 0, Field::NONE, + name, 0, unsigned_flag); + } break; #endif case MYSQL_TYPE_FLOAT: diff --git a/sql/item.h b/sql/item.h index ab2c9dd65a9..130ad688593 100644 --- a/sql/item.h +++ b/sql/item.h @@ -747,6 +747,10 @@ public: virtual bool send(Protocol *protocol, String *str); virtual bool eq(const Item *, bool binary_cmp) const; virtual enum_field_types field_type() const= 0; + virtual uint field_flags() const + { + return 0; + } virtual const Type_handler *type_handler() const { return Type_handler::get_handler_by_field_type(field_type()); @@ -2669,6 +2673,10 @@ public: { return field->type(); } + uint32 field_flags() const + { + return field->flags; + } const Type_handler *real_type_handler() const; enum_monotonicity_info get_monotonicity_info() const { @@ -5898,6 +5906,10 @@ public: Item* get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; } uint flags; + uint32 field_flags() const + { + return flags; + } }; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index f5ba6f206c2..147bb270e37 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -5218,6 +5218,58 @@ bool fix_escape_item(THD *thd, Item *escape_item, String *tmp_str, return FALSE; } +bool Item_bool_func2::fix_fields(THD* thd, Item** ref) +{ + if (Item_bool_func::fix_fields(thd, ref)) + return true; + + // System Versioning: convert TRX_ID to DATETIME + Item *trx_id= NULL; + int arg_idx= -1; + Field_vers_system *sys_field= NULL; + DBUG_ASSERT(arg_count == 2); + // find trx_id and sys_field + for (int i= 0; i < 2; ++i) + { + Item *arg= args[i]; + if (arg->result_type() != INT_RESULT) + continue; + DBUG_ASSERT(arg); + if (arg->type() == Item::FIELD_ITEM) + { + Field *f= static_cast<Item_field *>(arg)->field; + DBUG_ASSERT(f); + if (f->vers_sys_field() && f->real_type() == MYSQL_TYPE_LONGLONG) + { + if (sys_field) + return false; + sys_field= static_cast<Field_vers_system *>(f); + continue; + } + } + if (trx_id) + return false; + trx_id= arg; + arg_idx= i; + } + if (!trx_id || !sys_field) + return false; + MYSQL_TIME ltime; + ulonglong trx_id_val= (ulonglong) trx_id->val_int(); + if (sys_field->get_date(<ime, false, trx_id_val)) + { + my_error(ER_VERS_NO_TRX_ID, MYF(0), trx_id_val); + return true; + } + Query_arena_stmt on_stmt_arena(thd); + Item *datetime= new (thd->mem_root) Item_datetime_literal(thd, <ime, 6); + if (!datetime) + return true; + DBUG_ASSERT(arg_idx > -1); + args[arg_idx]= datetime; + return false; +} + bool Item_func_like::fix_fields(THD *thd, Item **ref) { diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index a94105b352d..6eb1b34e568 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -410,6 +410,7 @@ public: ftree= Item_func::get_mm_tree(param, cond_ptr); DBUG_RETURN(ftree); } + bool fix_fields(THD *thd, Item **ref); }; diff --git a/sql/item_vers.cc b/sql/item_vers.cc index 3562cc7fbc9..47e62d9ebf0 100644 --- a/sql/item_vers.cc +++ b/sql/item_vers.cc @@ -54,6 +54,14 @@ Item_func_vtq_ts::get_date(MYSQL_TIME *res, ulonglong fuzzy_date) { THD *thd= current_thd; // can it differ from constructor's? DBUG_ASSERT(thd); + DBUG_ASSERT(args[0]); + if (args[0]->result_type() != INT_RESULT) + { + my_error(ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION, MYF(0), + args[0]->type_handler()->name().ptr(), + func_name()); + return true; + } ulonglong trx_id= args[0]->val_uint(); if (trx_id == ULONGLONG_MAX) { @@ -62,13 +70,14 @@ Item_func_vtq_ts::get_date(MYSQL_TIME *res, ulonglong fuzzy_date) return false; } - if (!hton) - return true; - - DBUG_ASSERT(hton->vers_query_trx_id); + DBUG_ASSERT(hton && hton->vers_query_trx_id); null_value= !hton->vers_query_trx_id(thd, res, trx_id, vtq_field); + if (null_value) + { + my_error(ER_VERS_NO_TRX_ID, MYF(0), trx_id); + } - return false; + return null_value; } diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 1a386c4f16c..bae32e4d247 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7589,6 +7589,9 @@ ER_VERS_WRONG_PARTS ER_VERS_HISTORY_LOCK eng "Versioned SELECT write-locking of history rows" +ER_VERS_NO_TRX_ID + eng "TRX_ID %lu not found in VTQ" + ER_WRONG_TABLESPACE_NAME 42000 eng "Incorrect tablespace name `%-.192s`" diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 238fa3a0fa4..0a7ad7aef55 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5704,6 +5704,7 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, if (field_to_set) { TABLE *table= field_to_set->table; + DBUG_ASSERT(table); if (thd->mark_used_columns == MARK_COLUMNS_READ) bitmap_set_bit(table->read_set, field_to_set->field_index); else diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a1fc9f16b21..8575755c267 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -877,24 +877,6 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr, DBUG_RETURN(-1); } } - else if (thd->variables.vers_innodb_algorithm_simple && - vers_conditions.unit == UNIT_TIMESTAMP && - vers_conditions.type != FOR_SYSTEM_TIME_UNSPECIFIED) - { - DBUG_ASSERT(table->table->s && table->table->s->db_plugin); - handlerton *hton= plugin_hton(table->table->s->db_plugin); - DBUG_ASSERT(hton); - row_start= newx Item_func_vtq_ts( - thd, - hton, - row_start, - VTQ_COMMIT_TS); - row_end= newx Item_func_vtq_ts( - thd, - hton, - row_end, - VTQ_COMMIT_TS); - } Item *cond1= 0, *cond2= 0, *curr= 0; // Temporary tables of can be created from INNODB tables and thus will @@ -907,7 +889,7 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr, switch (vers_conditions.type) { case FOR_SYSTEM_TIME_UNSPECIFIED: - if (table->table->versioned_by_sql() && !tmp_from_ib) + if (!tmp_from_ib) { MYSQL_TIME max_time; thd->variables.time_zone->gmt_sec_to_TIME(&max_time, TIMESTAMP_MAX_VALUE); diff --git a/sql/table.cc b/sql/table.cc index b2a511d89f4..2dcec49d46d 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -1769,6 +1769,27 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, strpos, vcol_screen_pos); } + /* Set system versioning information. */ + if (system_period == NULL) + { + versioned= false; + row_start_field = 0; + row_end_field = 0; + } + else + { + DBUG_PRINT("info", ("Setting system versioning informations")); + uint16 row_start= uint2korr(system_period); + uint16 row_end= uint2korr(system_period + sizeof(uint16)); + 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; + vers_init(); + row_start_field= row_start; + row_end_field= row_end; + } // if (system_period == NULL) + for (i=0 ; i < share->fields; i++, strpos+=field_pack_length, field_ptr++) { uint pack_flag, interval_nr, unireg_type, recpos, field_length; @@ -1781,6 +1802,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, Virtual_column_info *vcol_info= 0; uint gis_length, gis_decimals, srid= 0; Field::utype unireg_check; + uint32 flags= 0; if (new_frm_ver >= 3) { @@ -1988,6 +2010,14 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, swap_variables(uint, null_bit_pos, mysql57_vcol_null_bit_pos); } + if (versioned) + { + if (i == row_start_field) + flags|= VERS_SYS_START_FLAG; + else if (i == row_end_field) + flags|= VERS_SYS_END_FLAG; + } + /* Convert pre-10.2.2 timestamps to use Field::default_value */ unireg_check= (Field::utype) MTYP_TYPENR(unireg_type); *field_ptr= reg_field= @@ -1995,7 +2025,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, null_pos, null_bit_pos, pack_flag, field_type, charset, geom_type, srid, unireg_check, (interval_nr ? share->intervals+interval_nr-1 : NULL), - share->fieldnames.type_names[i]); + share->fieldnames.type_names[i], flags); if (!reg_field) // Not supported field type goto err; @@ -2010,6 +2040,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, reg_field->field_index= i; reg_field->comment=comment; reg_field->vcol_info= vcol_info; + reg_field->flags|= flags; if (extra2_field_flags) { uchar flags= *extra2_field_flags++; @@ -2555,30 +2586,6 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, #endif share->db_plugin= se_plugin; - - /* Set system versioning information. */ - if (system_period == NULL) - { - versioned= false; - row_start_field = 0; - row_end_field = 0; - } - else - { - DBUG_PRINT("info", ("Setting system versioning informations")); - uint16 row_start= uint2korr(system_period); - uint16 row_end= uint2korr(system_period + sizeof(uint16)); - 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; - vers_init(); - row_start_field= row_start; - row_end_field= row_end; - vers_start_field()->flags|= VERS_SYS_START_FLAG; - vers_end_field()->flags|= VERS_SYS_END_FLAG; - } // if (system_period == NULL) - delete handler_file; share->error= OPEN_FRM_OK; diff --git a/sql/unireg.cc b/sql/unireg.cc index b647a284b14..c4b4a784ccc 100644 --- a/sql/unireg.cc +++ b/sql/unireg.cc @@ -1050,7 +1050,8 @@ static bool make_empty_rec(THD *thd, uchar *buff, uint table_options, field->unireg_check, field->save_interval ? field->save_interval : field->interval, - field->field_name); + field->field_name, + field->flags); if (!regfield) { error= 1; |