Debian Codegraph @

mysql-test/suite/versioning/t/delete.test

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