Debian Codegraph @

mysql-test/suite/engines/funcs/t/tr_delete.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2,t3;
3 --enable_warnings
4 CREATE TABLE t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
5 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
6 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 TINYINT, new1 TINYINT, old2 TINYINT, new2 TINYINT);
7 CREATE TABLE t3(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
8 INSERT INTO t3 VALUES(1,0);
9 delimiter //;
10 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
11 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
12 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
13 END//
14 DELIMITER ;//
15 REPLACE INTO t1(c1,c2) VALUES(12,12);
16 REPLACE INTO t1(c1,c2) VALUES(1,11);
17 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
18 SELECT * FROM t1 ORDER BY c1;
19 SELECT * FROM t2 ORDER BY c1;
20 SELECT * FROM t3 ORDER BY c1;
21 DELETE FROM t1 WHERE c1=1;
22 DELETE FROM t1 WHERE c1=0;
23 DELETE FROM t1 WHERE c1=NULL;
24 DELETE FROM t1 WHERE c1=6 OR c1=7;
25 DELETE FROM t1 WHERE c2=3;
26 SELECT * FROM t1 ORDER BY c1;
27 SELECT * FROM t2 ORDER BY c1;
28 SELECT * FROM t3 ORDER BY c1;
29 DROP TABLE t1,t2,t3;
30 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
31 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
32 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
33 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
34 INSERT INTO t3 VALUES(1,0);
35 delimiter //;
36 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
37 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
38 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
39 END//
40 DELIMITER ;//
41 REPLACE INTO t1(c1,c2) VALUES(12,12);
42 REPLACE INTO t1(c1,c2) VALUES(1,11);
43 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
44 SELECT * FROM t1 ORDER BY c1;
45 SELECT * FROM t2 ORDER BY c1;
46 SELECT * FROM t3 ORDER BY c1;
47 DELETE FROM t1 WHERE c1=1;
48 DELETE FROM t1 WHERE c1=0;
49 DELETE FROM t1 WHERE c1=NULL;
50 DELETE FROM t1 WHERE c1=6 OR c1=7;
51 DELETE FROM t1 WHERE c2=3;
52 SELECT * FROM t1 ORDER BY c1;
53 SELECT * FROM t2 ORDER BY c1;
54 SELECT * FROM t3 ORDER BY c1;
55 DROP TABLE t1,t2,t3;
56 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
57 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
58 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
59 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
60 INSERT INTO t3 VALUES(1,0);
61 delimiter //;
62 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
63 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
64 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
65 END//
66 DELIMITER ;//
67 REPLACE INTO t1(c1,c2) VALUES(12,12);
68 REPLACE INTO t1(c1,c2) VALUES(1,11);
69 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
70 SELECT * FROM t1 ORDER BY c1;
71 SELECT * FROM t2 ORDER BY c1;
72 SELECT * FROM t3 ORDER BY c1;
73 DELETE FROM t1 WHERE c1=1;
74 DELETE FROM t1 WHERE c1=0;
75 DELETE FROM t1 WHERE c1=NULL;
76 DELETE FROM t1 WHERE c1=6 OR c1=7;
77 DELETE FROM t1 WHERE c2=3;
78 SELECT * FROM t1 ORDER BY c1;
79 SELECT * FROM t2 ORDER BY c1;
80 SELECT * FROM t3 ORDER BY c1;
81 DROP TABLE t1,t2,t3;
82 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
83 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
84 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
85 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
86 INSERT INTO t3 VALUES(1,0);
87 delimiter //;
88 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
89 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
90 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
91 END//
92 DELIMITER ;//
93 REPLACE INTO t1(c1,c2) VALUES(12,12);
94 REPLACE INTO t1(c1,c2) VALUES(1,11);
95 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
96 SELECT * FROM t1 ORDER BY c1;
97 SELECT * FROM t2 ORDER BY c1;
98 SELECT * FROM t3 ORDER BY c1;
99 DELETE FROM t1 WHERE c1=1;
100 DELETE FROM t1 WHERE c1=0;
101 DELETE FROM t1 WHERE c1=NULL;
102 DELETE FROM t1 WHERE c1=6 OR c1=7;
103 DELETE FROM t1 WHERE c2=3;
104 SELECT * FROM t1 ORDER BY c1;
105 SELECT * FROM t2 ORDER BY c1;
106 SELECT * FROM t3 ORDER BY c1;
107 DROP TABLE t1,t2,t3;
108 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
109 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
110 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
111 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
112 INSERT INTO t3 VALUES(1,0);
113 delimiter //;
114 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
115 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
116 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
117 END//
118 DELIMITER ;//
119 REPLACE INTO t1(c1,c2) VALUES(12,12);
120 REPLACE INTO t1(c1,c2) VALUES(1,11);
121 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
122 SELECT * FROM t1 ORDER BY c1;
123 SELECT * FROM t2 ORDER BY c1;
124 SELECT * FROM t3 ORDER BY c1;
125 DELETE FROM t1 WHERE c1=1;
126 DELETE FROM t1 WHERE c1=0;
127 DELETE FROM t1 WHERE c1=NULL;
128 DELETE FROM t1 WHERE c1=6 OR c1=7;
129 DELETE FROM t1 WHERE c2=3;
130 SELECT * FROM t1 ORDER BY c1;
131 SELECT * FROM t2 ORDER BY c1;
132 SELECT * FROM t3 ORDER BY c1;
133 DROP TABLE t1,t2,t3;
134 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
135 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
136 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
137 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
138 INSERT INTO t3 VALUES(1,0);
139 delimiter //;
140 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
141 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
142 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
143 END//
144 DELIMITER ;//
145 REPLACE INTO t1(c1,c2) VALUES(12,12);
146 REPLACE INTO t1(c1,c2) VALUES(1,11);
147 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
148 SELECT * FROM t1 ORDER BY c1;
149 SELECT * FROM t2 ORDER BY c1;
150 SELECT * FROM t3 ORDER BY c1;
151 DELETE FROM t1 WHERE c1=1;
152 DELETE FROM t1 WHERE c1=0;
153 DELETE FROM t1 WHERE c1=NULL;
154 DELETE FROM t1 WHERE c1=6 OR c1=7;
155 DELETE FROM t1 WHERE c2=3;
156 SELECT * FROM t1 ORDER BY c1;
157 SELECT * FROM t2 ORDER BY c1;
158 SELECT * FROM t3 ORDER BY c1;
159 DROP TABLE t1,t2,t3;
160 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
161 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
162 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL);
163 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
164 INSERT INTO t3 VALUES(1,0);
165 delimiter //;
166 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
167 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
168 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
169 END//
170 DELIMITER ;//
171 REPLACE INTO t1(c1,c2) VALUES(12,12);
172 REPLACE INTO t1(c1,c2) VALUES(1,11);
173 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
174 SELECT * FROM t1 ORDER BY c1;
175 SELECT * FROM t2 ORDER BY c1;
176 SELECT * FROM t3 ORDER BY c1;
177 DELETE FROM t1 WHERE c1=1;
178 DELETE FROM t1 WHERE c1=0;
179 DELETE FROM t1 WHERE c1=NULL;
180 DELETE FROM t1 WHERE c1=6 OR c1=7;
181 DELETE FROM t1 WHERE c2=3;
182 SELECT * FROM t1 ORDER BY c1;
183 SELECT * FROM t2 ORDER BY c1;
184 SELECT * FROM t3 ORDER BY c1;
185 DROP TABLE t1,t2,t3;
186 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
187 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
188 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
189 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
190 INSERT INTO t3 VALUES(1,0);
191 delimiter //;
192 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
193 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
194 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
195 END//
196 DELIMITER ;//
197 REPLACE INTO t1(c1,c2) VALUES(12,12);
198 REPLACE INTO t1(c1,c2) VALUES(1,11);
199 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
200 SELECT * FROM t1 ORDER BY c1;
201 SELECT * FROM t2 ORDER BY c1;
202 SELECT * FROM t3 ORDER BY c1;
203 DELETE FROM t1 WHERE c1=1;
204 DELETE FROM t1 WHERE c1=0;
205 DELETE FROM t1 WHERE c1=NULL;
206 DELETE FROM t1 WHERE c1=6 OR c1=7;
207 DELETE FROM t1 WHERE c2=3;
208 SELECT * FROM t1 ORDER BY c1;
209 SELECT * FROM t2 ORDER BY c1;
210 SELECT * FROM t3 ORDER BY c1;
211 DROP TABLE t1,t2,t3;
212 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
213 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
214 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
215 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
216 INSERT INTO t3 VALUES(1,0);
217 delimiter //;
218 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
219 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
220 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
221 END//
222 DELIMITER ;//
223 REPLACE INTO t1(c1,c2) VALUES(12,12);
224 REPLACE INTO t1(c1,c2) VALUES(1,11);
225 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
226 SELECT * FROM t1 ORDER BY c1;
227 SELECT * FROM t2 ORDER BY c1;
228 SELECT * FROM t3 ORDER BY c1;
229 DELETE FROM t1 WHERE c1=1;
230 DELETE FROM t1 WHERE c1=0;
231 DELETE FROM t1 WHERE c1=NULL;
232 DELETE FROM t1 WHERE c1=6 OR c1=7;
233 DELETE FROM t1 WHERE c2=3;
234 SELECT * FROM t1 ORDER BY c1;
235 SELECT * FROM t2 ORDER BY c1;
236 SELECT * FROM t3 ORDER BY c1;
237 DROP TABLE t1,t2,t3;
238 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
239 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
240 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
241 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
242 INSERT INTO t3 VALUES(1,0);
243 delimiter //;
244 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
245 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
246 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
247 END//
248 DELIMITER ;//
249 REPLACE INTO t1(c1,c2) VALUES(12,12);
250 REPLACE INTO t1(c1,c2) VALUES(1,11);
251 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
252 SELECT * FROM t1 ORDER BY c1;
253 SELECT * FROM t2 ORDER BY c1;
254 SELECT * FROM t3 ORDER BY c1;
255 DELETE FROM t1 WHERE c1=1;
256 DELETE FROM t1 WHERE c1=0;
257 DELETE FROM t1 WHERE c1=NULL;
258 DELETE FROM t1 WHERE c1=6 OR c1=7;
259 DELETE FROM t1 WHERE c2=3;
260 SELECT * FROM t1 ORDER BY c1;
261 SELECT * FROM t2 ORDER BY c1;
262 SELECT * FROM t3 ORDER BY c1;
263 DROP TABLE t1,t2,t3;
264 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
265 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
266 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
267 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
268 INSERT INTO t3 VALUES(1,0);
269 delimiter //;
270 CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN
271 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
272 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
273 END//
274 DELIMITER ;//
275 REPLACE INTO t1(c1,c2) VALUES(12,12);
276 REPLACE INTO t1(c1,c2) VALUES(1,11);
277 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
278 SELECT * FROM t1 ORDER BY c1;
279 SELECT * FROM t2 ORDER BY c1;
280 SELECT * FROM t3 ORDER BY c1;
281 DELETE FROM t1 WHERE c1=1;
282 DELETE FROM t1 WHERE c1=0;
283 DELETE FROM t1 WHERE c1=NULL;
284 DELETE FROM t1 WHERE c1=6 OR c1=7;
285 DELETE FROM t1 WHERE c2=3;
286 SELECT * FROM t1 ORDER BY c1;
287 SELECT * FROM t2 ORDER BY c1;
288 SELECT * FROM t3 ORDER BY c1;
289 DROP TABLE t1,t2,t3;
290 CREATE TABLE t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
291 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
292 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 TINYINT, new1 TINYINT, old2 TINYINT, new2 TINYINT);
293 CREATE TABLE t3(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
294 INSERT INTO t3 VALUES(1,0);
295 delimiter //;
296 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
297 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
298 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
299 END//
300 DELIMITER ;//
301 REPLACE INTO t1(c1,c2) VALUES(12,12);
302 REPLACE INTO t1(c1,c2) VALUES(1,11);
303 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
304 SELECT * FROM t1 ORDER BY c1;
305 SELECT * FROM t2 ORDER BY c1;
306 SELECT * FROM t3 ORDER BY c1;
307 DELETE FROM t1 WHERE c1=1;
308 DELETE FROM t1 WHERE c1=0;
309 DELETE FROM t1 WHERE c1=NULL;
310 DELETE FROM t1 WHERE c1=6 OR c1=7;
311 DELETE FROM t1 WHERE c2=3;
312 SELECT * FROM t1 ORDER BY c1;
313 SELECT * FROM t2 ORDER BY c1;
314 SELECT * FROM t3 ORDER BY c1;
315 DROP TABLE t1,t2,t3;
316 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
317 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
318 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
319 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
320 INSERT INTO t3 VALUES(1,0);
321 delimiter //;
322 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
323 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
324 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
325 END//
326 DELIMITER ;//
327 REPLACE INTO t1(c1,c2) VALUES(12,12);
328 REPLACE INTO t1(c1,c2) VALUES(1,11);
329 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
330 SELECT * FROM t1 ORDER BY c1;
331 SELECT * FROM t2 ORDER BY c1;
332 SELECT * FROM t3 ORDER BY c1;
333 DELETE FROM t1 WHERE c1=1;
334 DELETE FROM t1 WHERE c1=0;
335 DELETE FROM t1 WHERE c1=NULL;
336 DELETE FROM t1 WHERE c1=6 OR c1=7;
337 DELETE FROM t1 WHERE c2=3;
338 SELECT * FROM t1 ORDER BY c1;
339 SELECT * FROM t2 ORDER BY c1;
340 SELECT * FROM t3 ORDER BY c1;
341 DROP TABLE t1,t2,t3;
342 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
343 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
344 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
345 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
346 INSERT INTO t3 VALUES(1,0);
347 delimiter //;
348 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
349 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
350 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
351 END//
352 DELIMITER ;//
353 REPLACE INTO t1(c1,c2) VALUES(12,12);
354 REPLACE INTO t1(c1,c2) VALUES(1,11);
355 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
356 SELECT * FROM t1 ORDER BY c1;
357 SELECT * FROM t2 ORDER BY c1;
358 SELECT * FROM t3 ORDER BY c1;
359 DELETE FROM t1 WHERE c1=1;
360 DELETE FROM t1 WHERE c1=0;
361 DELETE FROM t1 WHERE c1=NULL;
362 DELETE FROM t1 WHERE c1=6 OR c1=7;
363 DELETE FROM t1 WHERE c2=3;
364 SELECT * FROM t1 ORDER BY c1;
365 SELECT * FROM t2 ORDER BY c1;
366 SELECT * FROM t3 ORDER BY c1;
367 DROP TABLE t1,t2,t3;
368 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
369 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
370 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
371 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
372 INSERT INTO t3 VALUES(1,0);
373 delimiter //;
374 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
375 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
376 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
377 END//
378 DELIMITER ;//
379 REPLACE INTO t1(c1,c2) VALUES(12,12);
380 REPLACE INTO t1(c1,c2) VALUES(1,11);
381 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
382 SELECT * FROM t1 ORDER BY c1;
383 SELECT * FROM t2 ORDER BY c1;
384 SELECT * FROM t3 ORDER BY c1;
385 DELETE FROM t1 WHERE c1=1;
386 DELETE FROM t1 WHERE c1=0;
387 DELETE FROM t1 WHERE c1=NULL;
388 DELETE FROM t1 WHERE c1=6 OR c1=7;
389 DELETE FROM t1 WHERE c2=3;
390 SELECT * FROM t1 ORDER BY c1;
391 SELECT * FROM t2 ORDER BY c1;
392 SELECT * FROM t3 ORDER BY c1;
393 DROP TABLE t1,t2,t3;
394 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
395 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
396 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
397 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
398 INSERT INTO t3 VALUES(1,0);
399 delimiter //;
400 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
401 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
402 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
403 END//
404 DELIMITER ;//
405 REPLACE INTO t1(c1,c2) VALUES(12,12);
406 REPLACE INTO t1(c1,c2) VALUES(1,11);
407 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
408 SELECT * FROM t1 ORDER BY c1;
409 SELECT * FROM t2 ORDER BY c1;
410 SELECT * FROM t3 ORDER BY c1;
411 DELETE FROM t1 WHERE c1=1;
412 DELETE FROM t1 WHERE c1=0;
413 DELETE FROM t1 WHERE c1=NULL;
414 DELETE FROM t1 WHERE c1=6 OR c1=7;
415 DELETE FROM t1 WHERE c2=3;
416 SELECT * FROM t1 ORDER BY c1;
417 SELECT * FROM t2 ORDER BY c1;
418 SELECT * FROM t3 ORDER BY c1;
419 DROP TABLE t1,t2,t3;
420 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
421 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
422 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
423 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
424 INSERT INTO t3 VALUES(1,0);
425 delimiter //;
426 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
427 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
428 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
429 END//
430 DELIMITER ;//
431 REPLACE INTO t1(c1,c2) VALUES(12,12);
432 REPLACE INTO t1(c1,c2) VALUES(1,11);
433 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
434 SELECT * FROM t1 ORDER BY c1;
435 SELECT * FROM t2 ORDER BY c1;
436 SELECT * FROM t3 ORDER BY c1;
437 DELETE FROM t1 WHERE c1=1;
438 DELETE FROM t1 WHERE c1=0;
439 DELETE FROM t1 WHERE c1=NULL;
440 DELETE FROM t1 WHERE c1=6 OR c1=7;
441 DELETE FROM t1 WHERE c2=3;
442 SELECT * FROM t1 ORDER BY c1;
443 SELECT * FROM t2 ORDER BY c1;
444 SELECT * FROM t3 ORDER BY c1;
445 DROP TABLE t1,t2,t3;
446 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
447 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
448 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL);
449 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
450 INSERT INTO t3 VALUES(1,0);
451 delimiter //;
452 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
453 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
454 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
455 END//
456 DELIMITER ;//
457 REPLACE INTO t1(c1,c2) VALUES(12,12);
458 REPLACE INTO t1(c1,c2) VALUES(1,11);
459 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
460 SELECT * FROM t1 ORDER BY c1;
461 SELECT * FROM t2 ORDER BY c1;
462 SELECT * FROM t3 ORDER BY c1;
463 DELETE FROM t1 WHERE c1=1;
464 DELETE FROM t1 WHERE c1=0;
465 DELETE FROM t1 WHERE c1=NULL;
466 DELETE FROM t1 WHERE c1=6 OR c1=7;
467 DELETE FROM t1 WHERE c2=3;
468 SELECT * FROM t1 ORDER BY c1;
469 SELECT * FROM t2 ORDER BY c1;
470 SELECT * FROM t3 ORDER BY c1;
471 DROP TABLE t1,t2,t3;
472 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
473 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
474 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
475 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
476 INSERT INTO t3 VALUES(1,0);
477 delimiter //;
478 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
479 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
480 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
481 END//
482 DELIMITER ;//
483 REPLACE INTO t1(c1,c2) VALUES(12,12);
484 REPLACE INTO t1(c1,c2) VALUES(1,11);
485 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
486 SELECT * FROM t1 ORDER BY c1;
487 SELECT * FROM t2 ORDER BY c1;
488 SELECT * FROM t3 ORDER BY c1;
489 DELETE FROM t1 WHERE c1=1;
490 DELETE FROM t1 WHERE c1=0;
491 DELETE FROM t1 WHERE c1=NULL;
492 DELETE FROM t1 WHERE c1=6 OR c1=7;
493 DELETE FROM t1 WHERE c2=3;
494 SELECT * FROM t1 ORDER BY c1;
495 SELECT * FROM t2 ORDER BY c1;
496 SELECT * FROM t3 ORDER BY c1;
497 DROP TABLE t1,t2,t3;
498 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
499 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
500 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
501 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
502 INSERT INTO t3 VALUES(1,0);
503 delimiter //;
504 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
505 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
506 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
507 END//
508 DELIMITER ;//
509 REPLACE INTO t1(c1,c2) VALUES(12,12);
510 REPLACE INTO t1(c1,c2) VALUES(1,11);
511 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
512 SELECT * FROM t1 ORDER BY c1;
513 SELECT * FROM t2 ORDER BY c1;
514 SELECT * FROM t3 ORDER BY c1;
515 DELETE FROM t1 WHERE c1=1;
516 DELETE FROM t1 WHERE c1=0;
517 DELETE FROM t1 WHERE c1=NULL;
518 DELETE FROM t1 WHERE c1=6 OR c1=7;
519 DELETE FROM t1 WHERE c2=3;
520 SELECT * FROM t1 ORDER BY c1;
521 SELECT * FROM t2 ORDER BY c1;
522 SELECT * FROM t3 ORDER BY c1;
523 DROP TABLE t1,t2,t3;
524 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
525 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
526 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
527 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
528 INSERT INTO t3 VALUES(1,0);
529 delimiter //;
530 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
531 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
532 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
533 END//
534 DELIMITER ;//
535 REPLACE INTO t1(c1,c2) VALUES(12,12);
536 REPLACE INTO t1(c1,c2) VALUES(1,11);
537 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
538 SELECT * FROM t1 ORDER BY c1;
539 SELECT * FROM t2 ORDER BY c1;
540 SELECT * FROM t3 ORDER BY c1;
541 DELETE FROM t1 WHERE c1=1;
542 DELETE FROM t1 WHERE c1=0;
543 DELETE FROM t1 WHERE c1=NULL;
544 DELETE FROM t1 WHERE c1=6 OR c1=7;
545 DELETE FROM t1 WHERE c2=3;
546 SELECT * FROM t1 ORDER BY c1;
547 SELECT * FROM t2 ORDER BY c1;
548 SELECT * FROM t3 ORDER BY c1;
549 DROP TABLE t1,t2,t3;
550 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
551 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
552 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
553 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
554 INSERT INTO t3 VALUES(1,0);
555 delimiter //;
556 CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN
557 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,0,OLD.c2,0);
558 UPDATE t3 SET c2 = c2 + OLD.c2 WHERE c1 = 1;
559 END//
560 DELIMITER ;//
561 REPLACE INTO t1(c1,c2) VALUES(12,12);
562 REPLACE INTO t1(c1,c2) VALUES(1,11);
563 REPLACE INTO t1(c1,c2) VALUES(3,11),(4,11);
564 SELECT * FROM t1 ORDER BY c1;
565 SELECT * FROM t2 ORDER BY c1;
566 SELECT * FROM t3 ORDER BY c1;
567 DELETE FROM t1 WHERE c1=1;
568 DELETE FROM t1 WHERE c1=0;
569 DELETE FROM t1 WHERE c1=NULL;
570 DELETE FROM t1 WHERE c1=6 OR c1=7;
571 DELETE FROM t1 WHERE c2=3;
572 SELECT * FROM t1 ORDER BY c1;
573 SELECT * FROM t2 ORDER BY c1;
574 SELECT * FROM t3 ORDER BY c1;
575 DROP TABLE t1,t2,t3;
576