1source suite/versioning/engines.inc; 2source suite/versioning/common.inc; 3 4--echo # Basic + delete from view 5replace_result $sys_datatype_expl SYS_DATATYPE; 6eval create or replace table t1( 7 XNo int unsigned, 8 sys_start $sys_datatype_expl as row start invisible, 9 sys_end $sys_datatype_expl as row end invisible, 10 period for system_time (sys_start, sys_end)) 11 with system versioning; 12 13insert into t1(XNo) values(0); 14insert into t1(XNo) values(1); 15insert into t1(XNo) values(2); 16insert into t1(XNo) values(3); 17insert into t1(XNo) values(4); 18insert into t1(XNo) values(5); 19insert into t1(XNo) values(6); 20insert into t1(XNo) values(7); 21insert into t1(XNo) values(8); 22insert into t1(XNo) values(9); 23replace_result $sys_datatype_max MAXVAL; 24eval select XNo, sys_end < $sys_datatype_max from t1 for system_time all; 25delete from t1 where XNo = 0; 26delete from t1 where XNo = 1; 27delete from t1 where XNo > 5; 28create view vt1 as select XNo from t1; 29select XNo as XNo_vt1 from vt1; 30delete from vt1 where XNo = 3; 31select XNo as XNo_vt1 from vt1; 32drop view vt1; 33drop table t1; 34 35--echo # Check sys_start, sys_end 36replace_result $sys_datatype_expl SYS_DATATYPE; 37eval create or replace table t1( 38 x int, 39 sys_start $sys_datatype_expl as row start invisible, 40 sys_end $sys_datatype_expl as row end invisible, 41 period for system_time (sys_start, sys_end)) 42 with system versioning; 43 44insert into t1(x) values (1); 45select sys_start into @sys_start from t1; 46delete from t1; 47select * from t1; 48select x = 1 as A, sys_start = @sys_start as B, sys_end > sys_start as C from t1 for system_time all; 49drop table t1; 50 51--echo # Multi-delete 52replace_result $sys_datatype_expl SYS_DATATYPE; 53eval create or replace table t1( 54 x int, 55 y int, 56 sys_start $sys_datatype_expl as row start invisible, 57 sys_end $sys_datatype_expl as row end invisible, 58 period for system_time (sys_start, sys_end)) 59 with system versioning; 60create or replace table t2 like t1; 61insert into t1(x, y) values (1, 1), (2, 2), (3, 3), (14, 4); 62insert into t2(x, y) values (11, 1), (12, 2), (13, 32), (14, 4); 63delete t1, t2 from t1 join t2 where t1.y = 3 and t2.y = 32; 64select x as t1_x from t1; 65select x as t2_x from t2; 66delete t1, t2 from t1 join t2 where t1.x = t2.x; 67select x as t1_x from t1; 68select x as t2_x from t2; 69select x as t1_x_all from t1 for system_time all; 70select x as t2_x_all from t2 for system_time all; 71drop table t1; 72drop table t2; 73 74--echo # Update + delete 75create or replace table t1 (x int) with system versioning; 76insert into t1 values (1); 77update t1 set x= 2; 78delete from t1; 79select x from t1 for system_time all; 80drop table t1; 81 82--echo # 83--echo # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED 84--echo # 85create or replace table t1 (a int) with system versioning; 86replace into t1 values (1), (2); 87create or replace trigger tr before delete on t1 for each row delete from xx; 88create or replace procedure pr() delete from t1; 89--error ER_NO_SUCH_TABLE 90call pr; 91--error ER_NO_SUCH_TABLE 92call pr; 93drop procedure pr; 94drop trigger tr; 95drop table t1; 96 97--echo # 98--echo # MDEV-21138 Assertion `col->ord_part' or `f.col->ord_part' failed in row_build_index_entry_low 99--echo # 100create table t1 ( 101 f1 int, f2 text, f3 int, fulltext (f2), key(f1), key(f3), 102 foreign key r (f3) references t1 (f1) on delete set null) 103with system versioning engine innodb; 104insert into t1 values (1, repeat('a', 8193), 1), (1, repeat('b', 8193), 1); 105select f1, f3, check_row_ts(row_start, row_end) from t1; 106delete from t1; 107select f1, f3, check_row_ts(row_start, row_end) from t1 for system_time all; 108 109# cleanup 110drop table t1; 111 112--source suite/versioning/common_finish.inc 113