Debian Codegraph @

mysql-test/suite/versioning/r/delete.result

raw
1 # Basic + delete from view
2 create or replace table t1(
3 XNo int unsigned,
4 sys_start SYS_DATATYPE as row start invisible,
5 sys_end SYS_DATATYPE as row end invisible,
6 period for system_time (sys_start, sys_end))
7 with system versioning;
8 insert into t1(XNo) values(0);
9 insert into t1(XNo) values(1);
10 insert into t1(XNo) values(2);
11 insert into t1(XNo) values(3);
12 insert into t1(XNo) values(4);
13 insert into t1(XNo) values(5);
14 insert into t1(XNo) values(6);
15 insert into t1(XNo) values(7);
16 insert into t1(XNo) values(8);
17 insert into t1(XNo) values(9);
18 select XNo, sys_end < MAXVAL from t1 for system_time all;
19 XNo sys_end < MAXVAL
20 0 0
21 1 0
22 2 0
23 3 0
24 4 0
25 5 0
26 6 0
27 7 0
28 8 0
29 9 0
30 delete from t1 where XNo = 0;
31 delete from t1 where XNo = 1;
32 delete from t1 where XNo > 5;
33 create view vt1 as select XNo from t1;
34 select XNo as XNo_vt1 from vt1;
35 XNo_vt1
36 2
37 3
38 4
39 5
40 delete from vt1 where XNo = 3;
41 select XNo as XNo_vt1 from vt1;
42 XNo_vt1
43 2
44 4
45 5
46 drop view vt1;
47 drop table t1;
48 # Check sys_start, sys_end
49 create or replace table t1(
50 x int,
51 sys_start SYS_DATATYPE as row start invisible,
52 sys_end SYS_DATATYPE as row end invisible,
53 period for system_time (sys_start, sys_end))
54 with system versioning;
55 insert into t1(x) values (1);
56 select sys_start into @sys_start from t1;
57 delete from t1;
58 select * from t1;
59 x
60 select x = 1 as A, sys_start = @sys_start as B, sys_end > sys_start as C from t1 for system_time all;
61 A B C
62 1 1 1
63 drop table t1;
64 # Multi-delete
65 create or replace table t1(
66 x int,
67 y int,
68 sys_start SYS_DATATYPE as row start invisible,
69 sys_end SYS_DATATYPE as row end invisible,
70 period for system_time (sys_start, sys_end))
71 with system versioning;
72 create or replace table t2 like t1;
73 insert into t1(x, y) values (1, 1), (2, 2), (3, 3), (14, 4);
74 insert into t2(x, y) values (11, 1), (12, 2), (13, 32), (14, 4);
75 delete t1, t2 from t1 join t2 where t1.y = 3 and t2.y = 32;
76 select x as t1_x from t1;
77 t1_x
78 1
79 2
80 14
81 select x as t2_x from t2;
82 t2_x
83 11
84 12
85 14
86 delete t1, t2 from t1 join t2 where t1.x = t2.x;
87 select x as t1_x from t1;
88 t1_x
89 1
90 2
91 select x as t2_x from t2;
92 t2_x
93 11
94 12
95 select x as t1_x_all from t1 for system_time all;
96 t1_x_all
97 1
98 2
99 3
100 14
101 select x as t2_x_all from t2 for system_time all;
102 t2_x_all
103 11
104 12
105 13
106 14
107 drop table t1;
108 drop table t2;
109 # Update + delete
110 create or replace table t1 (x int) with system versioning;
111 insert into t1 values (1);
112 update t1 set x= 2;
113 delete from t1;
114 select x from t1 for system_time all;
115 x
116 2
117 1
118 drop table t1;
119 #
120 # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED
121 #
122 create or replace table t1 (a int) with system versioning;
123 replace into t1 values (1), (2);
124 create or replace trigger tr before delete on t1 for each row delete from xx;
125 create or replace procedure pr() delete from t1;
126 call pr;
127 ERROR 42S02: Table 'test.xx' doesn't exist
128 call pr;
129 ERROR 42S02: Table 'test.xx' doesn't exist
130 drop procedure pr;
131 drop trigger tr;
132 drop table t1;
133 #
134 # MDEV-21138 Assertion `col->ord_part' or `f.col->ord_part' failed in row_build_index_entry_low
135 #
136 # Check DELETE and multi-DELETE with foreign key
137 create table t1 (
138 f1 int, f2 text, f3 int, fulltext (f2), key(f1), key(f3),
139 foreign key r (f3) references t1 (f1) on delete set null,
140 row_start SYS_TYPE as row start invisible,
141 row_end SYS_TYPE as row end invisible,
142 period for system_time (row_start, row_end))
143 with system versioning engine innodb;
144 insert into t1 values (1, repeat('a', 8193), 1), (1, repeat('b', 8193), 1);
145 insert into t1 select 2, f2, 2 from t1;
146 select f1, f3, check_row(row_start, row_end) from t1;
147 f1 f3 check_row(row_start, row_end)
148 1 1 CURRENT ROW
149 1 1 CURRENT ROW
150 2 2 CURRENT ROW
151 2 2 CURRENT ROW
152 delete from t1 where f1 = 1;
153 select f1, f3, check_row(row_start, row_end) from t1 for system_time all order by f1, row_end;
154 f1 f3 check_row(row_start, row_end)
155 1 1 HISTORICAL ROW
156 1 1 HISTORICAL ROW
157 2 2 CURRENT ROW
158 2 2 CURRENT ROW
159 create table t2 (f1 int);
160 insert into t2 values (2);
161 # Multi-delelte
162 delete t1, t2 from t1 join t2 where t1.f1 = t2.f1;
163 select f1, f3, check_row(row_start, row_end) from t1 for system_time all order by f1, row_end;
164 f1 f3 check_row(row_start, row_end)
165 1 1 HISTORICAL ROW
166 1 1 HISTORICAL ROW
167 2 2 HISTORICAL ROW
168 2 2 HISTORICAL ROW
169 # Cleanup
170 drop tables t1, t2;