Debian Codegraph @

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

raw
1 --source suite/versioning/common.inc
2 --source include/have_partition.inc
3 --source suite/versioning/engines.inc
4 --source include/word_size.inc
5
6 call mtr.add_suppression("need more HISTORY partitions");
7
8 create table t (a int);
9 --error ER_VERS_NOT_VERSIONED
10 delete history from t before system_time now();
11
12 # TRUNCATE is not DELETE and trigger must not be called.
13 --replace_result $sys_datatype_expl SYS_TYPE
14 eval create or replace table t (
15 a int,
16 row_start $sys_datatype_expl as row start invisible,
17 row_end $sys_datatype_expl as row end invisible,
18 period for system_time (row_start, row_end))
19 with system versioning;
20 insert into t values (1);
21 update t set a=2;
22 set @test = 'correct';
23 create trigger trg_before before delete on t for each row set @test = 'incorrect';
24 create trigger trg_after after delete on t for each row set @test = 'incorrect';
25 delete history from t;
26 select @test from t;
27 drop table t;
28
29 --replace_result $sys_datatype_expl SYS_TYPE
30 eval create or replace table t (
31 a int,
32 row_start $sys_datatype_expl as row start invisible,
33 row_end $sys_datatype_expl as row end invisible,
34 period for system_time (row_start, row_end))
35 with system versioning;
36 insert into t values (1), (2);
37 update t set a=11 where a=1;
38 --real_sleep 0.01
39 set @ts1=now(6);
40 --real_sleep 0.01
41 update t set a=22 where a=2;
42 select * from t for system_time all;
43 delete history from t before system_time timestamp @ts1;
44 select * from t for system_time all;
45 prepare stmt from 'delete history from t';
46 execute stmt; drop prepare stmt;
47 select * from t for system_time all;
48 delete from t;
49
50 delimiter ~~;
51 create or replace procedure truncate_sp()
52 begin
53 delete history from t before system_time timestamp now(6);
54 end~~
55 delimiter ;~~
56 call truncate_sp;
57 select * from t for system_time all;
58
59 drop procedure truncate_sp;
60
61 --echo # Truncate partitioned
62 create or replace table t (a int) with system versioning
63 partition by system_time limit 1 partitions 3;
64 insert into t values (1);
65 update t set a= 2;
66 update t set a= 3;
67 --echo # You see warning above ^
68 delete history from t;
69 select * from t for system_time all;
70
71 --echo # VIEW
72 --replace_result $sys_datatype_expl SYS_TYPE
73 eval create or replace table t (
74 i int,
75 row_start $sys_datatype_expl as row start invisible,
76 row_end $sys_datatype_expl as row end invisible,
77 period for system_time (row_start, row_end))
78 with system versioning;
79 delete history from t;
80 create or replace view v as select * from t;
81 --error ER_IT_IS_A_VIEW
82 delete history from v;
83
84 create or replace table t (i int);
85 --error ER_VERS_NOT_VERSIONED
86 delete history from t;
87 create or replace view v as select * from t;
88 --error ER_IT_IS_A_VIEW
89 delete history from v;
90 --error ER_VERS_NOT_VERSIONED
91 prepare stmt from 'delete history from t';
92
93 drop table t;
94 drop view v;
95
96 #
97 # MDEV-15402 Assertion `table' failed in mysql_delete on attempt to delete history from view
98 #
99 create or replace table t (i int);
100 create or replace view v as select * from t;
101 --error ER_IT_IS_A_VIEW
102 drop table v;
103 lock table v write;
104 --error ER_IT_IS_A_VIEW
105 delete history from v before system_time now(6);
106 unlock tables;
107 drop view v;
108 drop table t;
109
110 #
111 # MDEV-16783 Assertion `!conds' failed in mysql_delete upon 2nd execution of SP with DELETE HISTORY
112 #
113 create table t1 (i int) with system versioning;
114 create procedure pr() delete history from t1 before system_time now();
115 call pr;
116 call pr;
117 drop procedure pr;
118 drop table t1;
119
120 --echo # MDEV-15966 Behavior for TRUNCATE versioned table is not documented and not covered by tests
121 create or replace table t1 (id int);
122 create or replace table t2 (id int) with system versioning;
123
124 -- echo # force cleaning table shares
125 flush tables t1, t2;
126
127 truncate table t1;
128 --error ER_VERS_NOT_SUPPORTED
129 truncate table t2;
130
131 -- echo # fetch table shares
132 describe t1;
133 describe t2;
134
135 truncate table t1;
136 --error ER_VERS_NOT_SUPPORTED
137 truncate table t2;
138
139 --echo # enter locked tables mode
140 lock tables t1 WRITE, t2 WRITE;
141
142 truncate t1;
143 --error ER_VERS_NOT_SUPPORTED
144 truncate t2;
145
146 unlock tables;
147 drop table t2;
148
149 --echo #
150 --echo # MDEV-19814 Assertion `update->n_fields < ulint(table->n_cols + table->n_v_cols)' on DELETE HISTORY
151 --echo #
152 --replace_result $sys_datatype_expl SYS_TYPE
153 eval create or replace table t1 (
154 f varchar(1),
155 row_start $sys_datatype_expl as row start,
156 row_end $sys_datatype_expl as row end,
157 period for system_time (row_start, row_end))
158 with system versioning;
159 insert into t1 (f) values ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h');
160 delete from t1;
161 delete history from t1;
162 drop table t1;
163
164 --echo #
165 --echo # MDEV-20186 Wrong result or Assertion on INSERT after DELETE HISTORY
166 --echo #
167 create or replace table t1 (a int check (a > 0)) with system versioning;
168 delete history from t1;
169 insert into t1 values (1);
170 select * from t1;
171 drop table t1;
172
173 --echo #
174 --echo # MDEV-25468 DELETE HISTORY may delete current data on system-versioned table
175 --echo #
176 create or replace table t1 (x int) with system versioning;
177 insert into t1 values (1);
178 delete history from t1 before system_time '2039-01-01 23:00';
179 select * from t1;
180 explain extended delete history from t1 before system_time '2039-01-01 23:00';
181 create or replace procedure p() delete history from t1 before system_time '2039-01-01 23:00';
182 call p;
183 select * from t1;
184 call p;
185 select * from t1;
186 drop procedure p;
187 prepare stmt from "delete history from t1 before system_time '2039-01-01 23:00'";
188 execute stmt;
189 select * from t1;
190 execute stmt;
191 select * from t1;
192 drop prepare stmt;
193 drop table t1;
194
195 --echo #
196 --echo # MDEV-25004 Missing row in FTS_DOC_ID_INDEX during DELETE HISTORY
197 --echo #
198 create table t1 (a integer, c0 varchar(255), fulltext key (c0))
199 with system versioning engine innodb stats_persistent=0;
200 set system_versioning_alter_history= keep;
201 alter table t1 drop system versioning;
202 alter table t1 add system versioning;
203 insert into t1 values (1, 'politician');
204 update t1 set c0= 'criminal';
205 --source suite/innodb/include/wait_all_purged.inc
206 delete history from t1;
207 drop table t1;
208
209 create table t1 (id int primary key, ftx varchar(255))
210 with system versioning engine innodb stats_persistent=0;
211 insert into t1 values (1, 'c');
212 delete from t1;
213 alter table t1 add fulltext key(ftx);
214 drop table t1;
215
216 --echo #
217 --echo # MDEV-28201 Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
218 --echo #
219 CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
220 CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
221 SET optimizer_trace= 'enabled=on';
222 --error ER_NON_UPDATABLE_TABLE
223 DELETE HISTORY FROM v1 BEFORE SYSTEM_TIME '2021-01-01';
224 --error ER_NON_UPDATABLE_TABLE
225 DELETE HISTORY FROM v1;
226 DROP VIEW v1;
227 DROP TABLE t1;
228
229 --echo # End of 10.4 tests
230
231 --echo #
232 --echo # MDEV-33470 Unique hash index is broken on DML for system-versioned table
233 --echo #
234 create or replace table t (
235 c int, unique (c) using hash)
236 with system versioning;
237 insert into t values (0);
238 delete from t;
239 delete history from t;
240 drop table t;
241
242 --echo # End of 10.5 tests
243
244 --echo #
245 --echo # MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT
246 --echo #
247 --echo # Don't auto-create new partition on DELETE HISTORY:
248 set timestamp= unix_timestamp('2000-01-01 00:00:00');
249 create or replace table t (a int) with system versioning
250 partition by system_time interval 1 hour auto;
251 set timestamp= unix_timestamp('2000-01-01 10:00:00');
252 delete history from t;
253 set timestamp= default;
254 --replace_result $default_engine DEFAULT_ENGINE
255 show create table t;
256 drop table t;
257
258 --echo # End of 10.9 tests
259
260 --echo #
261 --echo # MDEV-34046 Parameterized PS converts error to warning, causes replication problems
262 --echo #
263 create table t (a int) with system versioning;
264 set timestamp= unix_timestamp('2000-01-01 00:00:00');
265 insert into t values (1), (100);
266
267 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
268 delete history from t before system_time @@timestamp;
269 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
270 execute immediate "delete history from t before system_time @@timestamp";
271 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
272 execute immediate "delete history from t before system_time ?" using @@timestamp;
273
274 set @ts1= '2000-01-01 00:00:01'; set timestamp= unix_timestamp(@ts1);
275 update t set a= a + 1 where a < 100;
276 set @ts2= '2000-01-01 00:00:02'; set timestamp= unix_timestamp(@ts2);
277 update t set a= a + 1 where a < 100;
278 set @ts3= '2000-01-01 00:00:03'; set timestamp= unix_timestamp(@ts3);
279 select *, row_start, row_end from t for system_time all order by a;
280 select *, row_start, row_end from t for system_time as of @ts1;
281 execute immediate "select *, row_start, row_end from t for system_time as of ?" using @ts1;
282 prepare stmt from 'select *, row_start, row_end from t for system_time as of ?';
283 execute stmt using @ts1;
284 Execute stmt using @ts1;
285 select *, row_start, row_end from t for system_time as of @ts2;
286 execute immediate "select *, row_start, row_end from t for system_time as of ?" using @ts2;
287 execute stmt using @ts2;
288 Execute stmt using @ts2;
289 drop prepare stmt;
290
291 execute immediate "delete history from t before system_time @ts1";
292 select *, row_start, row_end from t for system_time all order by a;
293 execute immediate "delete history from t before system_time @ts2";
294 select *, row_start, row_end from t for system_time all order by a;
295 execute immediate "delete history from t before system_time ?" using @ts3;
296 select *, row_start, row_end from t for system_time all order by a;
297 execute immediate "delete history from t before system_time ?" using @ts3;
298 select *, row_start, row_end from t for system_time all order by a;
299 update t set a= a + 1 where a < 100;
300 set @ts4= '2000-01-01 00:00:04'; set timestamp= unix_timestamp(@ts4);
301 update t set a= a + 1 where a < 100;
302 select *, row_start, row_end from t for system_time all order by a;
303 execute immediate "delete history from t before system_time ?" using '2000-01-01 00:00:04';
304 select *, row_start, row_end from t for system_time all order by a;
305 set @ts5= '2000-01-01 00:00:05'; set timestamp= unix_timestamp(@ts5);
306 update t set a= a + 1 where a < 100;
307 set @ts6= '2000-01-01 00:00:06'; set timestamp= unix_timestamp(@ts6);
308 update t set a= a + 1 where a < 100;
309 set @ts7= '2000-01-01 00:00:07'; set timestamp= unix_timestamp(@ts7);
310 update t set a= a + 1 where a < 100;
311 select *, row_start, row_end from t for system_time all order by a;
312 prepare stmt from 'delete history from t before system_time ?';
313 execute stmt using @ts4;
314 select *, row_start, row_end from t for system_time all order by a;
315 execute stmt using @ts5;
316 select *, row_start, row_end from t for system_time all order by a;
317 execute stmt using @ts6;
318 select *, row_start, row_end from t for system_time all order by a;
319 execute stmt using '2000-01-01 00:00:06';
320 select *, row_start, row_end from t for system_time all order by a;
321 execute stmt using '2000-01-01 00:00:06.000001';
322 select *, row_start, row_end from t for system_time all order by a;
323 set @ts8= '2000-01-01 00:00:08'; set timestamp= unix_timestamp(@ts8);
324 delete from t;
325 select *, row_start, row_end from t for system_time all order by a;
326 execute immediate "delete history from t before system_time from_unixtime(?)" using @@timestamp;
327 select *, row_start, row_end from t for system_time all order by a;
328 execute stmt using '2020-01-01';
329 select *, row_start, row_end from t for system_time all order by a;
330 drop prepare stmt;
331
332 # Check expression
333 set timestamp= unix_timestamp('2000-01-01 00:00:00');
334 insert into t values (1), (100);
335 set @ts1= '2000-01-01 00:00:01'; set timestamp= unix_timestamp(@ts1);
336 update t set a= a + 1 where a < 100;
337 set timestamp= @@timestamp + 1;
338 set @ts2= @@timestamp;
339 update t set a= a + 1 where a < 100;
340 set timestamp= @@timestamp + 1;
341 update t set a= a + 1 where a < 100;
342
343 prepare stmt from 'delete history from t before system_time from_unixtime(? + ?)';
344 select *, row_start, row_end from t for system_time all order by a;
345 execute stmt using @ts1, 0;
346 select *, row_start, row_end from t for system_time all order by a;
347 execute stmt using @ts2, 0;
348 select *, row_start, row_end from t for system_time all order by a;
349 execute stmt using @@timestamp, NULL;
350 select *, row_start, row_end from t for system_time all order by a;
351 execute stmt using NULL, NULL;
352 select *, row_start, row_end from t for system_time all order by a;
353 execute stmt using @ts2, 1;
354 select *, row_start, row_end from t for system_time all order by a;
355 execute stmt using @ts2, @ts2;
356 select *, row_start, row_end from t for system_time all order by a;
357 delete from t;
358 select *, row_start, row_end from t for system_time all order by a;
359 execute stmt using @ts2, @ts2;
360 select *, row_start, row_end from t for system_time all order by a;
361
362 drop prepare stmt;
363
364 drop table t;
365 set timestamp= default;
366
367 --echo #
368 --echo # MDEV-37164 Assertion `vers_conditions.delete_history' failed upon PREPARE
369 --echo #
370 create table t (a int) with system versioning;
371 insert into t values (1),(2);
372 prepare stmt from 'select * from t for system_time as of timestamp ?';
373 drop table t;
374
375 --echo # End of 10.11 tests
376
377 --source suite/versioning/common_finish.inc