Debian Codegraph @

mysql-test/suite/engines/funcs/r/tr_update.result

raw
1 DROP TABLE IF EXISTS t1,t2,t3;
2 CREATE TABLE t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
3 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
4 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 TINYINT, new1 TINYINT, old2 TINYINT, new2 TINYINT);
5 CREATE TABLE t3(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
6 INSERT INTO t3 VALUES(1,0);
7 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
8 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
9 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
10 END//
11 UPDATE t1 SET c2=10 WHERE c1=1;
12 UPDATE t1 SET c2=10 WHERE c1=0;
13 UPDATE t1 SET c2=10 WHERE c1=NULL;
14 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
15 UPDATE t1 SET c2=10 WHERE c2=3;
16 SELECT * FROM t1 ORDER BY c1;
17 c1 c2
18 1 10
19 2 2
20 3 10
21 4 4
22 6 10
23 7 10
24 8 8
25 9 9
26 10 10
27 SELECT * FROM t2 ORDER BY c1;
28 c1 old1 new1 old2 new2
29 1 1 1 1 10
30 2 6 6 6 10
31 3 7 7 7 10
32 4 3 3 3 10
33 SELECT * FROM t3 ORDER BY c1;
34 c1 c2
35 1 40
36 DROP TABLE t1,t2,t3;
37 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
38 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
39 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
40 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
41 INSERT INTO t3 VALUES(1,0);
42 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
43 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
44 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
45 END//
46 UPDATE t1 SET c2=10 WHERE c1=1;
47 UPDATE t1 SET c2=10 WHERE c1=0;
48 UPDATE t1 SET c2=10 WHERE c1=NULL;
49 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
50 UPDATE t1 SET c2=10 WHERE c2=3;
51 SELECT * FROM t1 ORDER BY c1;
52 c1 c2
53 1 10
54 2 2
55 3 10
56 4 4
57 6 10
58 7 10
59 8 8
60 9 9
61 10 10
62 SELECT * FROM t2 ORDER BY c1;
63 c1 old1 new1 old2 new2
64 1 1 1 1 10
65 2 6 6 6 10
66 3 7 7 7 10
67 4 3 3 3 10
68 SELECT * FROM t3 ORDER BY c1;
69 c1 c2
70 1 40
71 DROP TABLE t1,t2,t3;
72 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
73 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
74 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
75 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
76 INSERT INTO t3 VALUES(1,0);
77 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
78 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
79 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
80 END//
81 UPDATE t1 SET c2=10 WHERE c1=1;
82 UPDATE t1 SET c2=10 WHERE c1=0;
83 UPDATE t1 SET c2=10 WHERE c1=NULL;
84 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
85 UPDATE t1 SET c2=10 WHERE c2=3;
86 SELECT * FROM t1 ORDER BY c1;
87 c1 c2
88 1 10
89 2 2
90 3 10
91 4 4
92 6 10
93 7 10
94 8 8
95 9 9
96 10 10
97 SELECT * FROM t2 ORDER BY c1;
98 c1 old1 new1 old2 new2
99 1 1 1 1 10
100 2 6 6 6 10
101 3 7 7 7 10
102 4 3 3 3 10
103 SELECT * FROM t3 ORDER BY c1;
104 c1 c2
105 1 40
106 DROP TABLE t1,t2,t3;
107 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
108 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
109 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
110 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
111 INSERT INTO t3 VALUES(1,0);
112 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
113 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
114 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
115 END//
116 UPDATE t1 SET c2=10 WHERE c1=1;
117 UPDATE t1 SET c2=10 WHERE c1=0;
118 UPDATE t1 SET c2=10 WHERE c1=NULL;
119 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
120 UPDATE t1 SET c2=10 WHERE c2=3;
121 SELECT * FROM t1 ORDER BY c1;
122 c1 c2
123 1 10
124 2 2
125 3 10
126 4 4
127 6 10
128 7 10
129 8 8
130 9 9
131 10 10
132 SELECT * FROM t2 ORDER BY c1;
133 c1 old1 new1 old2 new2
134 1 1 1 1 10
135 2 6 6 6 10
136 3 7 7 7 10
137 4 3 3 3 10
138 SELECT * FROM t3 ORDER BY c1;
139 c1 c2
140 1 40
141 DROP TABLE t1,t2,t3;
142 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
143 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
144 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
145 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
146 INSERT INTO t3 VALUES(1,0);
147 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
148 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
149 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
150 END//
151 UPDATE t1 SET c2=10 WHERE c1=1;
152 UPDATE t1 SET c2=10 WHERE c1=0;
153 UPDATE t1 SET c2=10 WHERE c1=NULL;
154 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
155 UPDATE t1 SET c2=10 WHERE c2=3;
156 SELECT * FROM t1 ORDER BY c1;
157 c1 c2
158 1 10
159 2 2
160 3 10
161 4 4
162 6 10
163 7 10
164 8 8
165 9 9
166 10 10
167 SELECT * FROM t2 ORDER BY c1;
168 c1 old1 new1 old2 new2
169 1 1 1 1 10
170 2 6 6 6 10
171 3 7 7 7 10
172 4 3 3 3 10
173 SELECT * FROM t3 ORDER BY c1;
174 c1 c2
175 1 40
176 DROP TABLE t1,t2,t3;
177 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
178 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
179 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
180 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
181 INSERT INTO t3 VALUES(1,0);
182 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
183 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
184 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
185 END//
186 UPDATE t1 SET c2=10 WHERE c1=1;
187 UPDATE t1 SET c2=10 WHERE c1=0;
188 UPDATE t1 SET c2=10 WHERE c1=NULL;
189 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
190 UPDATE t1 SET c2=10 WHERE c2=3;
191 SELECT * FROM t1 ORDER BY c1;
192 c1 c2
193 1 10
194 2 2
195 3 10
196 4 4
197 6 10
198 7 10
199 8 8
200 9 9
201 10 10
202 SELECT * FROM t2 ORDER BY c1;
203 c1 old1 new1 old2 new2
204 1 1 1 1 10
205 2 6 6 6 10
206 3 7 7 7 10
207 4 3 3 3 10
208 SELECT * FROM t3 ORDER BY c1;
209 c1 c2
210 1 40
211 DROP TABLE t1,t2,t3;
212 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
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 REAL, new1 REAL, old2 REAL, new2 REAL);
215 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
216 INSERT INTO t3 VALUES(1,0);
217 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
218 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
219 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
220 END//
221 UPDATE t1 SET c2=10 WHERE c1=1;
222 UPDATE t1 SET c2=10 WHERE c1=0;
223 UPDATE t1 SET c2=10 WHERE c1=NULL;
224 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
225 UPDATE t1 SET c2=10 WHERE c2=3;
226 SELECT * FROM t1 ORDER BY c1;
227 c1 c2
228 1 10
229 2 2
230 3 10
231 4 4
232 6 10
233 7 10
234 8 8
235 9 9
236 10 10
237 SELECT * FROM t2 ORDER BY c1;
238 c1 old1 new1 old2 new2
239 1 1 1 1 10
240 2 6 6 6 10
241 3 7 7 7 10
242 4 3 3 3 10
243 SELECT * FROM t3 ORDER BY c1;
244 c1 c2
245 1 40
246 DROP TABLE t1,t2,t3;
247 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
248 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
249 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
250 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
251 INSERT INTO t3 VALUES(1,0);
252 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
253 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
254 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
255 END//
256 UPDATE t1 SET c2=10 WHERE c1=1;
257 UPDATE t1 SET c2=10 WHERE c1=0;
258 UPDATE t1 SET c2=10 WHERE c1=NULL;
259 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
260 UPDATE t1 SET c2=10 WHERE c2=3;
261 SELECT * FROM t1 ORDER BY c1;
262 c1 c2
263 1 10
264 2 2
265 3 10
266 4 4
267 6 10
268 7 10
269 8 8
270 9 9
271 10 10
272 SELECT * FROM t2 ORDER BY c1;
273 c1 old1 new1 old2 new2
274 1 1 1 1 10
275 2 6 6 6 10
276 3 7 7 7 10
277 4 3 3 3 10
278 SELECT * FROM t3 ORDER BY c1;
279 c1 c2
280 1 40
281 DROP TABLE t1,t2,t3;
282 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
283 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
284 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
285 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
286 INSERT INTO t3 VALUES(1,0);
287 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
288 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
289 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
290 END//
291 UPDATE t1 SET c2=10 WHERE c1=1;
292 UPDATE t1 SET c2=10 WHERE c1=0;
293 UPDATE t1 SET c2=10 WHERE c1=NULL;
294 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
295 UPDATE t1 SET c2=10 WHERE c2=3;
296 SELECT * FROM t1 ORDER BY c1;
297 c1 c2
298 1 10
299 2 2
300 3 10
301 4 4
302 6 10
303 7 10
304 8 8
305 9 9
306 10 10
307 SELECT * FROM t2 ORDER BY c1;
308 c1 old1 new1 old2 new2
309 1 1 1 1 10
310 2 6 6 6 10
311 3 7 7 7 10
312 4 3 3 3 10
313 SELECT * FROM t3 ORDER BY c1;
314 c1 c2
315 1 40
316 DROP TABLE t1,t2,t3;
317 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
318 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
319 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
320 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
321 INSERT INTO t3 VALUES(1,0);
322 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
323 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
324 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
325 END//
326 UPDATE t1 SET c2=10 WHERE c1=1;
327 UPDATE t1 SET c2=10 WHERE c1=0;
328 UPDATE t1 SET c2=10 WHERE c1=NULL;
329 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
330 UPDATE t1 SET c2=10 WHERE c2=3;
331 SELECT * FROM t1 ORDER BY c1;
332 c1 c2
333 1 10
334 2 2
335 3 10
336 4 4
337 6 10
338 7 10
339 8 8
340 9 9
341 10 10
342 SELECT * FROM t2 ORDER BY c1;
343 c1 old1 new1 old2 new2
344 1 1 1 1 10
345 2 6 6 6 10
346 3 7 7 7 10
347 4 3 3 3 10
348 SELECT * FROM t3 ORDER BY c1;
349 c1 c2
350 1 40
351 DROP TABLE t1,t2,t3;
352 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
353 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
354 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
355 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
356 INSERT INTO t3 VALUES(1,0);
357 CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
358 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
359 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
360 END//
361 UPDATE t1 SET c2=10 WHERE c1=1;
362 UPDATE t1 SET c2=10 WHERE c1=0;
363 UPDATE t1 SET c2=10 WHERE c1=NULL;
364 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
365 UPDATE t1 SET c2=10 WHERE c2=3;
366 SELECT * FROM t1 ORDER BY c1;
367 c1 c2
368 1 10
369 2 2
370 3 10
371 4 4
372 6 10
373 7 10
374 8 8
375 9 9
376 10 10
377 SELECT * FROM t2 ORDER BY c1;
378 c1 old1 new1 old2 new2
379 1 1 1 1 10
380 2 6 6 6 10
381 3 7 7 7 10
382 4 3 3 3 10
383 SELECT * FROM t3 ORDER BY c1;
384 c1 c2
385 1 40
386 DROP TABLE t1,t2,t3;
387 CREATE TABLE t1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
388 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
389 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 TINYINT, new1 TINYINT, old2 TINYINT, new2 TINYINT);
390 CREATE TABLE t3(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT);
391 INSERT INTO t3 VALUES(1,0);
392 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
393 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
394 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
395 END//
396 UPDATE t1 SET c2=10 WHERE c1=1;
397 UPDATE t1 SET c2=10 WHERE c1=0;
398 UPDATE t1 SET c2=10 WHERE c1=NULL;
399 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
400 UPDATE t1 SET c2=10 WHERE c2=3;
401 SELECT * FROM t1 ORDER BY c1;
402 c1 c2
403 1 10
404 2 2
405 3 10
406 4 4
407 6 10
408 7 10
409 8 8
410 9 9
411 10 10
412 SELECT * FROM t2 ORDER BY c1;
413 c1 old1 new1 old2 new2
414 1 1 1 1 10
415 2 6 6 6 10
416 3 7 7 7 10
417 4 3 3 3 10
418 SELECT * FROM t3 ORDER BY c1;
419 c1 c2
420 1 40
421 DROP TABLE t1,t2,t3;
422 CREATE TABLE t1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
423 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
424 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 SMALLINT, new1 SMALLINT, old2 SMALLINT, new2 SMALLINT);
425 CREATE TABLE t3(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT);
426 INSERT INTO t3 VALUES(1,0);
427 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
428 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
429 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
430 END//
431 UPDATE t1 SET c2=10 WHERE c1=1;
432 UPDATE t1 SET c2=10 WHERE c1=0;
433 UPDATE t1 SET c2=10 WHERE c1=NULL;
434 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
435 UPDATE t1 SET c2=10 WHERE c2=3;
436 SELECT * FROM t1 ORDER BY c1;
437 c1 c2
438 1 10
439 2 2
440 3 10
441 4 4
442 6 10
443 7 10
444 8 8
445 9 9
446 10 10
447 SELECT * FROM t2 ORDER BY c1;
448 c1 old1 new1 old2 new2
449 1 1 1 1 10
450 2 6 6 6 10
451 3 7 7 7 10
452 4 3 3 3 10
453 SELECT * FROM t3 ORDER BY c1;
454 c1 c2
455 1 40
456 DROP TABLE t1,t2,t3;
457 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
458 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
459 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 MEDIUMINT, new1 MEDIUMINT, old2 MEDIUMINT, new2 MEDIUMINT);
460 CREATE TABLE t3(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT);
461 INSERT INTO t3 VALUES(1,0);
462 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
463 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
464 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
465 END//
466 UPDATE t1 SET c2=10 WHERE c1=1;
467 UPDATE t1 SET c2=10 WHERE c1=0;
468 UPDATE t1 SET c2=10 WHERE c1=NULL;
469 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
470 UPDATE t1 SET c2=10 WHERE c2=3;
471 SELECT * FROM t1 ORDER BY c1;
472 c1 c2
473 1 10
474 2 2
475 3 10
476 4 4
477 6 10
478 7 10
479 8 8
480 9 9
481 10 10
482 SELECT * FROM t2 ORDER BY c1;
483 c1 old1 new1 old2 new2
484 1 1 1 1 10
485 2 6 6 6 10
486 3 7 7 7 10
487 4 3 3 3 10
488 SELECT * FROM t3 ORDER BY c1;
489 c1 c2
490 1 40
491 DROP TABLE t1,t2,t3;
492 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT);
493 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
494 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INT, new1 INT, old2 INT, new2 INT);
495 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 INT);
496 INSERT INTO t3 VALUES(1,0);
497 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
498 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
499 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
500 END//
501 UPDATE t1 SET c2=10 WHERE c1=1;
502 UPDATE t1 SET c2=10 WHERE c1=0;
503 UPDATE t1 SET c2=10 WHERE c1=NULL;
504 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
505 UPDATE t1 SET c2=10 WHERE c2=3;
506 SELECT * FROM t1 ORDER BY c1;
507 c1 c2
508 1 10
509 2 2
510 3 10
511 4 4
512 6 10
513 7 10
514 8 8
515 9 9
516 10 10
517 SELECT * FROM t2 ORDER BY c1;
518 c1 old1 new1 old2 new2
519 1 1 1 1 10
520 2 6 6 6 10
521 3 7 7 7 10
522 4 3 3 3 10
523 SELECT * FROM t3 ORDER BY c1;
524 c1 c2
525 1 40
526 DROP TABLE t1,t2,t3;
527 CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
528 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
529 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 INTEGER, new1 INTEGER, old2 INTEGER, new2 INTEGER);
530 CREATE TABLE t3(c1 INTEGER NOT NULL PRIMARY KEY, c2 INTEGER);
531 INSERT INTO t3 VALUES(1,0);
532 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
533 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
534 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
535 END//
536 UPDATE t1 SET c2=10 WHERE c1=1;
537 UPDATE t1 SET c2=10 WHERE c1=0;
538 UPDATE t1 SET c2=10 WHERE c1=NULL;
539 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
540 UPDATE t1 SET c2=10 WHERE c2=3;
541 SELECT * FROM t1 ORDER BY c1;
542 c1 c2
543 1 10
544 2 2
545 3 10
546 4 4
547 6 10
548 7 10
549 8 8
550 9 9
551 10 10
552 SELECT * FROM t2 ORDER BY c1;
553 c1 old1 new1 old2 new2
554 1 1 1 1 10
555 2 6 6 6 10
556 3 7 7 7 10
557 4 3 3 3 10
558 SELECT * FROM t3 ORDER BY c1;
559 c1 c2
560 1 40
561 DROP TABLE t1,t2,t3;
562 CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
563 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
564 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 BIGINT, new1 BIGINT, old2 BIGINT, new2 BIGINT);
565 CREATE TABLE t3(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT);
566 INSERT INTO t3 VALUES(1,0);
567 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
568 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
569 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
570 END//
571 UPDATE t1 SET c2=10 WHERE c1=1;
572 UPDATE t1 SET c2=10 WHERE c1=0;
573 UPDATE t1 SET c2=10 WHERE c1=NULL;
574 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
575 UPDATE t1 SET c2=10 WHERE c2=3;
576 SELECT * FROM t1 ORDER BY c1;
577 c1 c2
578 1 10
579 2 2
580 3 10
581 4 4
582 6 10
583 7 10
584 8 8
585 9 9
586 10 10
587 SELECT * FROM t2 ORDER BY c1;
588 c1 old1 new1 old2 new2
589 1 1 1 1 10
590 2 6 6 6 10
591 3 7 7 7 10
592 4 3 3 3 10
593 SELECT * FROM t3 ORDER BY c1;
594 c1 c2
595 1 40
596 DROP TABLE t1,t2,t3;
597 CREATE TABLE t1 (c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
598 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
599 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 REAL, new1 REAL, old2 REAL, new2 REAL);
600 CREATE TABLE t3(c1 REAL NOT NULL PRIMARY KEY, c2 REAL);
601 INSERT INTO t3 VALUES(1,0);
602 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
603 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
604 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
605 END//
606 UPDATE t1 SET c2=10 WHERE c1=1;
607 UPDATE t1 SET c2=10 WHERE c1=0;
608 UPDATE t1 SET c2=10 WHERE c1=NULL;
609 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
610 UPDATE t1 SET c2=10 WHERE c2=3;
611 SELECT * FROM t1 ORDER BY c1;
612 c1 c2
613 1 10
614 2 2
615 3 10
616 4 4
617 6 10
618 7 10
619 8 8
620 9 9
621 10 10
622 SELECT * FROM t2 ORDER BY c1;
623 c1 old1 new1 old2 new2
624 1 1 1 1 10
625 2 6 6 6 10
626 3 7 7 7 10
627 4 3 3 3 10
628 SELECT * FROM t3 ORDER BY c1;
629 c1 c2
630 1 40
631 DROP TABLE t1,t2,t3;
632 CREATE TABLE t1 (c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
633 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
634 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DOUBLE, new1 DOUBLE, old2 DOUBLE, new2 DOUBLE);
635 CREATE TABLE t3(c1 DOUBLE NOT NULL PRIMARY KEY, c2 DOUBLE);
636 INSERT INTO t3 VALUES(1,0);
637 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
638 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
639 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
640 END//
641 UPDATE t1 SET c2=10 WHERE c1=1;
642 UPDATE t1 SET c2=10 WHERE c1=0;
643 UPDATE t1 SET c2=10 WHERE c1=NULL;
644 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
645 UPDATE t1 SET c2=10 WHERE c2=3;
646 SELECT * FROM t1 ORDER BY c1;
647 c1 c2
648 1 10
649 2 2
650 3 10
651 4 4
652 6 10
653 7 10
654 8 8
655 9 9
656 10 10
657 SELECT * FROM t2 ORDER BY c1;
658 c1 old1 new1 old2 new2
659 1 1 1 1 10
660 2 6 6 6 10
661 3 7 7 7 10
662 4 3 3 3 10
663 SELECT * FROM t3 ORDER BY c1;
664 c1 c2
665 1 40
666 DROP TABLE t1,t2,t3;
667 CREATE TABLE t1 (c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
668 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
669 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 FLOAT, new1 FLOAT, old2 FLOAT, new2 FLOAT);
670 CREATE TABLE t3(c1 FLOAT NOT NULL PRIMARY KEY, c2 FLOAT);
671 INSERT INTO t3 VALUES(1,0);
672 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
673 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
674 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
675 END//
676 UPDATE t1 SET c2=10 WHERE c1=1;
677 UPDATE t1 SET c2=10 WHERE c1=0;
678 UPDATE t1 SET c2=10 WHERE c1=NULL;
679 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
680 UPDATE t1 SET c2=10 WHERE c2=3;
681 SELECT * FROM t1 ORDER BY c1;
682 c1 c2
683 1 10
684 2 2
685 3 10
686 4 4
687 6 10
688 7 10
689 8 8
690 9 9
691 10 10
692 SELECT * FROM t2 ORDER BY c1;
693 c1 old1 new1 old2 new2
694 1 1 1 1 10
695 2 6 6 6 10
696 3 7 7 7 10
697 4 3 3 3 10
698 SELECT * FROM t3 ORDER BY c1;
699 c1 c2
700 1 40
701 DROP TABLE t1,t2,t3;
702 CREATE TABLE t1 (c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
703 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
704 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 DECIMAL, new1 DECIMAL, old2 DECIMAL, new2 DECIMAL);
705 CREATE TABLE t3(c1 DECIMAL NOT NULL PRIMARY KEY, c2 DECIMAL);
706 INSERT INTO t3 VALUES(1,0);
707 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
708 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
709 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
710 END//
711 UPDATE t1 SET c2=10 WHERE c1=1;
712 UPDATE t1 SET c2=10 WHERE c1=0;
713 UPDATE t1 SET c2=10 WHERE c1=NULL;
714 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
715 UPDATE t1 SET c2=10 WHERE c2=3;
716 SELECT * FROM t1 ORDER BY c1;
717 c1 c2
718 1 10
719 2 2
720 3 10
721 4 4
722 6 10
723 7 10
724 8 8
725 9 9
726 10 10
727 SELECT * FROM t2 ORDER BY c1;
728 c1 old1 new1 old2 new2
729 1 1 1 1 10
730 2 6 6 6 10
731 3 7 7 7 10
732 4 3 3 3 10
733 SELECT * FROM t3 ORDER BY c1;
734 c1 c2
735 1 40
736 DROP TABLE t1,t2,t3;
737 CREATE TABLE t1 (c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
738 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(6,6),(7,7),(8,8),(9,9),(10,10);
739 CREATE TABLE t2(c1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, old1 NUMERIC, new1 NUMERIC, old2 NUMERIC, new2 NUMERIC);
740 CREATE TABLE t3(c1 NUMERIC NOT NULL PRIMARY KEY, c2 NUMERIC);
741 INSERT INTO t3 VALUES(1,0);
742 CREATE TRIGGER tr1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN
743 INSERT INTO t2 (old1,new1,old2,new2) VALUES(OLD.c1,NEW.c1,OLD.c2,NEW.c2);
744 UPDATE t3 SET c2 = c2 + NEW.c2 WHERE c1 = 1;
745 END//
746 UPDATE t1 SET c2=10 WHERE c1=1;
747 UPDATE t1 SET c2=10 WHERE c1=0;
748 UPDATE t1 SET c2=10 WHERE c1=NULL;
749 UPDATE t1 SET c2=10 WHERE c1=6 OR c1=7;
750 UPDATE t1 SET c2=10 WHERE c2=3;
751 SELECT * FROM t1 ORDER BY c1;
752 c1 c2
753 1 10
754 2 2
755 3 10
756 4 4
757 6 10
758 7 10
759 8 8
760 9 9
761 10 10
762 SELECT * FROM t2 ORDER BY c1;
763 c1 old1 new1 old2 new2
764 1 1 1 1 10
765 2 6 6 6 10
766 3 7 7 7 10
767 4 3 3 3 10
768 SELECT * FROM t3 ORDER BY c1;
769 c1 c2
770 1 40
771 DROP TABLE t1,t2,t3;