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