Debian Codegraph @

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

raw
1 create table t (id int, s date, e date, period for apptime(s,e));
2 insert into t values(1, '1999-01-01', '2018-12-12');
3 insert into t values(1, '1999-01-01', '2017-01-01');
4 insert into t values(1, '2017-01-01', '2019-01-01');
5 insert into t values(2, '1998-01-01', '2018-12-12');
6 insert into t values(3, '1997-01-01', '2015-01-01');
7 insert into t values(4, '2016-01-01', '2020-01-01');
8 insert into t values(5, '2010-01-01', '2015-01-01');
9 create or replace table t1 (id int, s date, e date, period for apptime(s,e));
10 insert t1 select * from t;
11 create or replace table t2 (id int, s date, e date, period for apptime(s,e));
12 insert t2 select * from t;
13 create or replace table t3 (id int, s date, e date, period for apptime(s,e));
14 insert t3 select * from t;
15 create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
16 create or replace procedure log(s text)
17 insert into log_tbl(log) values(s);
18 create trigger tr1upd_t1 before update on t1
19 for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
20 create trigger tr2upd_t1 after update on t1
21 for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
22 create trigger tr1del_t1 before delete on t1
23 for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e));
24 create trigger tr2del_t1 after delete on t1
25 for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e));
26 create trigger tr1ins_t1 before insert on t1
27 for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e));
28 create trigger tr2ins_t1 after insert on t1
29 for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e));
30 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
31 delete from t1 for portion of APPTIME from '2000-01-01' to '2018-01-01';
32 select * from t;
33 id s e
34 1 1999-01-01 2000-01-01
35 1 1999-01-01 2000-01-01
36 1 2018-01-01 2018-12-12
37 1 2018-01-01 2019-01-01
38 2 1998-01-01 2000-01-01
39 2 2018-01-01 2018-12-12
40 3 1997-01-01 2000-01-01
41 4 2018-01-01 2020-01-01
42 select * from t1;
43 id s e
44 1 1999-01-01 2000-01-01
45 1 1999-01-01 2000-01-01
46 1 2018-01-01 2018-12-12
47 1 2018-01-01 2019-01-01
48 2 1998-01-01 2000-01-01
49 2 2018-01-01 2018-12-12
50 3 1997-01-01 2000-01-01
51 4 2018-01-01 2020-01-01
52 select * from log_tbl order by id;
53 id log
54 1 >DEL: 1, 1999-01-01, 2018-12-12
55 2 >INS: 1, 1999-01-01, 2000-01-01
56 3 <INS: 1, 1999-01-01, 2000-01-01
57 4 >INS: 1, 2018-01-01, 2018-12-12
58 5 <INS: 1, 2018-01-01, 2018-12-12
59 6 <DEL: 1, 1999-01-01, 2018-12-12
60 7 >DEL: 1, 1999-01-01, 2017-01-01
61 8 >INS: 1, 1999-01-01, 2000-01-01
62 9 <INS: 1, 1999-01-01, 2000-01-01
63 10 <DEL: 1, 1999-01-01, 2017-01-01
64 11 >DEL: 1, 2017-01-01, 2019-01-01
65 12 >INS: 1, 2018-01-01, 2019-01-01
66 13 <INS: 1, 2018-01-01, 2019-01-01
67 14 <DEL: 1, 2017-01-01, 2019-01-01
68 15 >DEL: 2, 1998-01-01, 2018-12-12
69 16 >INS: 2, 1998-01-01, 2000-01-01
70 17 <INS: 2, 1998-01-01, 2000-01-01
71 18 >INS: 2, 2018-01-01, 2018-12-12
72 19 <INS: 2, 2018-01-01, 2018-12-12
73 20 <DEL: 2, 1998-01-01, 2018-12-12
74 21 >DEL: 3, 1997-01-01, 2015-01-01
75 22 >INS: 3, 1997-01-01, 2000-01-01
76 23 <INS: 3, 1997-01-01, 2000-01-01
77 24 <DEL: 3, 1997-01-01, 2015-01-01
78 25 >DEL: 4, 2016-01-01, 2020-01-01
79 26 >INS: 4, 2018-01-01, 2020-01-01
80 27 <INS: 4, 2018-01-01, 2020-01-01
81 28 <DEL: 4, 2016-01-01, 2020-01-01
82 29 >DEL: 5, 2010-01-01, 2015-01-01
83 30 <DEL: 5, 2010-01-01, 2015-01-01
84 # INSERT trigger only also works
85 create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
86 create or replace procedure log(s text)
87 insert into log_tbl(log) values(s);
88 create trigger tr1upd_t2 before update on t2
89 for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
90 create trigger tr2upd_t2 after update on t2
91 for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
92 create trigger tr1del_t2 before delete on t2
93 for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e));
94 create trigger tr2del_t2 after delete on t2
95 for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e));
96 create trigger tr1ins_t2 before insert on t2
97 for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e));
98 create trigger tr2ins_t2 after insert on t2
99 for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e));
100 drop trigger tr1del_t2;
101 drop trigger tr2del_t2;
102 delete from t2 for portion of APPTIME from '2000-01-01' to '2018-01-01';
103 select * from log_tbl order by id;
104 id log
105 1 >INS: 1, 1999-01-01, 2000-01-01
106 2 <INS: 1, 1999-01-01, 2000-01-01
107 3 >INS: 1, 2018-01-01, 2018-12-12
108 4 <INS: 1, 2018-01-01, 2018-12-12
109 5 >INS: 1, 1999-01-01, 2000-01-01
110 6 <INS: 1, 1999-01-01, 2000-01-01
111 7 >INS: 1, 2018-01-01, 2019-01-01
112 8 <INS: 1, 2018-01-01, 2019-01-01
113 9 >INS: 2, 1998-01-01, 2000-01-01
114 10 <INS: 2, 1998-01-01, 2000-01-01
115 11 >INS: 2, 2018-01-01, 2018-12-12
116 12 <INS: 2, 2018-01-01, 2018-12-12
117 13 >INS: 3, 1997-01-01, 2000-01-01
118 14 <INS: 3, 1997-01-01, 2000-01-01
119 15 >INS: 4, 2018-01-01, 2020-01-01
120 16 <INS: 4, 2018-01-01, 2020-01-01
121 # removing BEFORE INSERT trigger enables internal substitution
122 # DELETE+INSERT -> UPDATE, but without any side effects.
123 # The optimization is disabled for non-transactional engines
124 create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
125 create or replace procedure log(s text)
126 insert into log_tbl(log) values(s);
127 create trigger tr1upd_t3 before update on t3
128 for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
129 create trigger tr2upd_t3 after update on t3
130 for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
131 create trigger tr1del_t3 before delete on t3
132 for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
133 create trigger tr2del_t3 after delete on t3
134 for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
135 create trigger tr1ins_t3 before insert on t3
136 for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
137 create trigger tr2ins_t3 after insert on t3
138 for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
139 drop trigger tr1ins_t3;
140 delete from t3 for portion of APPTIME from '2000-01-01' to '2018-01-01';
141 select * from log_tbl order by id;
142 id log
143 1 >DEL: 1999-01-01, 2018-12-12
144 2 <INS: 1999-01-01, 2000-01-01
145 3 <INS: 2018-01-01, 2018-12-12
146 4 <DEL: 1999-01-01, 2018-12-12
147 5 >DEL: 1999-01-01, 2017-01-01
148 6 <INS: 1999-01-01, 2000-01-01
149 7 <DEL: 1999-01-01, 2017-01-01
150 8 >DEL: 2017-01-01, 2019-01-01
151 9 <INS: 2018-01-01, 2019-01-01
152 10 <DEL: 2017-01-01, 2019-01-01
153 11 >DEL: 1998-01-01, 2018-12-12
154 12 <INS: 1998-01-01, 2000-01-01
155 13 <INS: 2018-01-01, 2018-12-12
156 14 <DEL: 1998-01-01, 2018-12-12
157 15 >DEL: 1997-01-01, 2015-01-01
158 16 <INS: 1997-01-01, 2000-01-01
159 17 <DEL: 1997-01-01, 2015-01-01
160 18 >DEL: 2016-01-01, 2020-01-01
161 19 <INS: 2018-01-01, 2020-01-01
162 20 <DEL: 2016-01-01, 2020-01-01
163 21 >DEL: 2010-01-01, 2015-01-01
164 22 <DEL: 2010-01-01, 2015-01-01
165 # multi-table DELETE is not possible
166 delete t, t1 from t1, t for portion of apptime from '2000-01-01' to '2018-01-01';
167 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'for portion of apptime from '2000-01-01' to '2018-01-01'' at line 1
168 delete t for portion of apptime from '2000-01-01' to '2018-01-01', t1 from t, t1;
169 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'for portion of apptime from '2000-01-01' to '2018-01-01', t1 from t, t1' at line 1
170 # Here another check fails before parsing ends
171 delete t, t1 from t for portion of apptime from '2000-01-01' to '2018-01-01', t1;
172 ERROR 42S02: Unknown table 't1' in MULTI DELETE
173 delete history from t2 for portion of apptime from '2000-01-01' to '2018-01-01';
174 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'for portion of apptime from '2000-01-01' to '2018-01-01'' at line 1
175 delete from t for portion of othertime from '2000-01-01' to '2018-01-01';
176 ERROR HY000: Period `othertime` is not found in table
177 delete from t for portion of system_time from '2000-01-01' to '2018-01-01';
178 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'of system_time from '2000-01-01' to '2018-01-01'' at line 1
179 # MDEV-22596 DELETE FOR PORTION does not obey
180 # "Expression in FOR PORTION OF must be constant" limitation, data can be easily lost
181 delete from t for portion of apptime from s to e;
182 ERROR HY000: Expression in FOR PORTION OF must be constant
183 create or replace table t (id int, str text, s date, e date,
184 period for apptime(s,e));
185 insert into t values(1, 'data', '1999-01-01', '2018-12-12');
186 insert into t values(1, 'other data', '1999-01-01', '2018-12-12');
187 insert into t values(1, 'deleted', '2000-01-01', '2018-01-01');
188 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
189 show warnings;
190 Level Code Message
191 select * from t;
192 id str s e
193 1 data 1999-01-01 2000-01-01
194 1 data 2018-01-01 2018-12-12
195 1 other data 1999-01-01 2000-01-01
196 1 other data 2018-01-01 2018-12-12
197 drop table t1;
198 # SQL16, Part 2, 15.7 <Effect of deleting rows from base tables>,
199 # General rules, 8)b)i)
200 # If the column descriptor that corresponds to the i-th field of BR
201 # describes an identity column, a generated column, a system-time period
202 # start column, or a system-time period end column, then let V i be
203 # DEFAULT.
204 # auto_increment field is updated
205 create or replace table t (id int primary key auto_increment, s date, e date,
206 period for apptime(s, e));
207 insert into t values (default, '1999-01-01', '2018-12-12');
208 select * from t;
209 id s e
210 1 1999-01-01 2018-12-12
211 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
212 select * from t;
213 id s e
214 2 1999-01-01 2000-01-01
215 3 2018-01-01 2018-12-12
216 truncate t;
217 # same for trigger case
218 insert into t values (default, '1999-01-01', '2018-12-12');
219 create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
220 create or replace procedure log(s text)
221 insert into log_tbl(log) values(s);
222 create trigger tr1upd_t before update on t
223 for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
224 create trigger tr2upd_t after update on t
225 for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
226 create trigger tr1del_t before delete on t
227 for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
228 create trigger tr2del_t after delete on t
229 for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
230 create trigger tr1ins_t before insert on t
231 for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
232 create trigger tr2ins_t after insert on t
233 for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
234 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
235 select * from t;
236 id s e
237 2 1999-01-01 2000-01-01
238 3 2018-01-01 2018-12-12
239 select * from log_tbl order by id;
240 id log
241 1 >DEL: 1999-01-01, 2018-12-12
242 2 >INS: 1999-01-01, 2000-01-01
243 3 <INS: 1999-01-01, 2000-01-01
244 4 >INS: 2018-01-01, 2018-12-12
245 5 <INS: 2018-01-01, 2018-12-12
246 6 <DEL: 1999-01-01, 2018-12-12
247 # generated columns are updated
248 create or replace table t (s date, e date,
249 xs date as (s) stored, xe date as (e) stored,
250 period for apptime(s, e));
251 insert into t values('1999-01-01', '2018-12-12', default, default);
252 select * from t;
253 s e xs xe
254 1999-01-01 2018-12-12 1999-01-01 2018-12-12
255 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
256 select * from t;
257 s e xs xe
258 1999-01-01 2000-01-01 1999-01-01 2000-01-01
259 2018-01-01 2018-12-12 2018-01-01 2018-12-12
260 truncate t;
261 # same for trigger case
262 insert into t values('1999-01-01', '2018-12-12', default, default);
263 create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
264 create or replace procedure log(s text)
265 insert into log_tbl(log) values(s);
266 create trigger tr1upd_t before update on t
267 for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
268 create trigger tr2upd_t after update on t
269 for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
270 create trigger tr1del_t before delete on t
271 for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
272 create trigger tr2del_t after delete on t
273 for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
274 create trigger tr1ins_t before insert on t
275 for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
276 create trigger tr2ins_t after insert on t
277 for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
278 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
279 select * from t;
280 s e xs xe
281 1999-01-01 2000-01-01 1999-01-01 2000-01-01
282 2018-01-01 2018-12-12 2018-01-01 2018-12-12
283 select * from log_tbl order by id;
284 id log
285 1 >DEL: 1999-01-01, 2018-12-12
286 2 >INS: 1999-01-01, 2000-01-01
287 3 <INS: 1999-01-01, 2000-01-01
288 4 >INS: 2018-01-01, 2018-12-12
289 5 <INS: 2018-01-01, 2018-12-12
290 6 <DEL: 1999-01-01, 2018-12-12
291 # View can't be used
292 create or replace view v as select * from t;
293 delete from v for portion of p from '2000-01-01' to '2018-01-01';
294 ERROR 42S02: 'v' is a view
295 # View can't be used
296 create or replace view v as select t.* from t, t as t1;
297 delete from v for portion of p from '2000-01-01' to '2018-01-01';
298 ERROR HY000: Can not delete from join view 'test.v'
299 # auto_increment field overflow
300 create or replace table t (id tinyint auto_increment primary key,
301 s date, e date, period for apptime(s,e));
302 insert into t values(127, '1999-01-01', '2018-12-12');
303 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
304 ERROR 22003: Out of range value for column 'id' at row 1
305 select * from t;
306 id s e
307 127 1999-01-01 2018-12-12
308 # same for trigger case
309 create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
310 create or replace procedure log(s text)
311 insert into log_tbl(log) values(s);
312 create trigger tr1upd_t before update on t
313 for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
314 create trigger tr2upd_t after update on t
315 for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
316 create trigger tr1del_t before delete on t
317 for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
318 create trigger tr2del_t after delete on t
319 for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
320 create trigger tr1ins_t before insert on t
321 for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
322 create trigger tr2ins_t after insert on t
323 for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
324 # negotiate side effects of non-transactional MyISAM engine
325 replace into t values(127, '1999-01-01', '2018-12-12');
326 select * from t;
327 id s e
328 127 1999-01-01 2018-12-12
329 truncate table log_tbl;
330 delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
331 ERROR 22003: Out of range value for column 'id' at row 1
332 select * from t;
333 id s e
334 127 1999-01-01 2018-12-12
335 select * from log_tbl order by id;
336 id log
337 1 >DEL: 1999-01-01, 2018-12-12
338 # custom constraint for period fields
339 create or replace table t(id int, s date, e date, period for apptime(s,e),
340 constraint dist2days check (datediff(e, s) >= 2));
341 insert into t values(1, '1999-01-01', '2018-12-12'),
342 (2, '1999-01-01', '1999-12-12');
343 delete from t for portion of apptime from '1999-01-02' to '2018-12-12';
344 ERROR 23000: CONSTRAINT `dist2days` failed for `test`.`t`
345 # negotiate side effects of non-transactional MyISAM engine
346 truncate t;
347 insert into t values(1, '1999-01-01', '2018-12-12'),
348 (2, '1999-01-01', '1999-12-12');
349 delete from t for portion of apptime from '1999-01-01' to '2018-12-11';
350 ERROR 23000: CONSTRAINT `dist2days` failed for `test`.`t`
351 truncate t;
352 insert into t values(1, '1999-01-01', '2018-12-12'),
353 (2, '1999-01-01', '1999-12-12');
354 delete from t for portion of apptime from '1999-01-03' to '2018-12-10';
355 select *, datediff(e, s) from t;
356 id s e datediff(e, s)
357 1 1999-01-01 1999-01-03 2
358 1 2018-12-10 2018-12-12 2
359 2 1999-01-01 1999-01-03 2
360 #
361 # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED
362 #
363 create or replace table t1 (id int, s date, e date, period for apptime(s,e));
364 create or replace procedure sp()
365 delete from t1 for portion of othertime from '2000-01-01' to '2018-01-01';
366 call sp;
367 ERROR HY000: Period `othertime` is not found in table
368 call sp;
369 ERROR HY000: Period `othertime` is not found in table
370 drop table t1;
371 drop procedure sp;
372 drop table t,t2,t3,log_tbl;
373 drop view v;
374 drop procedure log;
375 # MDEV-19130 Assertion
376 # `next_insert_id >= auto_inc_interval_for_cur_row.minimum()'
377 # failed in handler::update_auto_increment after error 167
378 create or replace table t (f tinyint auto_increment null,
379 s timestamp, e timestamp,
380 period for app(s,e), key(f, s));
381 insert into t (s,e) values
382 ('2021-08-22 10:28:43', '2023-09-17 00:00:00'),
383 ('2019-05-09 21:45:24', '2020-04-22 14:38:49');
384 insert into t (s,e) select s,e from t;
385 insert into t (s,e) select s,e from t;
386 insert into t (s,e) select s,e from t;
387 insert into t (s,e) values ('2015-07-07 00:00:00','2020-03-11 08:48:52');
388 insert into t (s,e) select s,e from t;
389 insert into t (s,e) select s,e from t;
390 insert into t select * from t;
391 ERROR 22003: Out of range value for column 'f' at row ROW
392 delete ignore from t
393 for portion of app from '2015-07-07 00:00:00' to '2020-03-11 08:48:52';
394 drop table t;
395 create table t1 (pk int, s date, e date, period for se (s,e), primary key (pk,se without overlaps));
396 insert into t1 values (1,'2020-01-01','2020-02-20');
397 delete from t1 for portion of se from '2020-01-30' to '2020-01-31';
398 drop table t1;
399 # End of 10.5 tests
400 #
401 # MDEV-19190 Assertion `part_share->auto_inc_initialized` failed in
402 # ha_partition::get_auto_increment
403 #
404 create table t1 (id int, s date, e date, period for app(s,e))
405 partition by key(id);
406 insert into t1 (s,e) values ('2023-07-21','2024-06-07');
407 alter table t1 modify id int auto_increment key;
408 delete from t1 for portion of app from '2023-07-20' to '2024-05-23';
409 select * from t1;
410 id s e
411 2 2024-05-23 2024-06-07
412 drop table t1;
413 create table t1 (id int, s date, e date, period for app(s,e))
414 partition by key(id);
415 insert into t1 (s,e) values ('2023-07-21','2024-06-07');
416 alter table t1 modify id int auto_increment key;
417 delete from t1 for portion of app from '2023-07-20' to '2024-05-23';
418 select * from t1;
419 id s e
420 2 2024-05-23 2024-06-07
421 drop table t1;
422 drop table log_tbl;
423 drop procedure log;