Debian Codegraph @

mysql-test/suite/engines/iuds/r/update_delete_number.result

raw
1 DROP TABLE IF EXISTS t1,t2;
2 CREATE TABLE t1(c1 INT UNSIGNED NOT NULL, c2 INT SIGNED NULL, c3 INT, INDEX idx2(c2));
3 INSERT INTO t1 VALUES(0,-128,0),(1,1,1),(2,2,2),(0,NULL,3),(101,-101,4),(102,-102,5),(103,-103,6),(104,-104,7),(105,-105,8);
4 SELECT * FROM t1;
5 c1 c2 c3
6 0 -128 0
7 0 NULL 3
8 1 1 1
9 101 -101 4
10 102 -102 5
11 103 -103 6
12 104 -104 7
13 105 -105 8
14 2 2 2
15 UPDATE t1 SET c1=110 WHERE c2 >-128 ORDER BY c2 LIMIT 2;
16 SELECT * FROM t1 WHERE c1=110;
17 c1 c2 c3
18 110 -104 7
19 110 -105 8
20 UPDATE t1 SET c1=c1+1,c2=NULL WHERE c1=101;
21 SELECT * FROM t1 WHERE c1=102 AND c2 IS NULL;
22 c1 c2 c3
23 102 NULL 4
24 UPDATE t1 SET c1=120 WHERE c2 IS NULL;
25 SELECT c1,c2 FROM t1 WHERE c1=120;
26 c1 c2
27 120 NULL
28 120 NULL
29 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
30 UPDATE t1 SET c1=-120 WHERE c2=-102;
31 Warnings:
32 Warning 1264 Out of range value for column 'c1' at row 1
33 SELECT c1,c2 FROM t1 WHERE c2=-102;
34 c1 c2
35 0 -102
36 UPDATE t1 SET c1=0,c2=-2147483648 WHERE c1=103 AND c2=-103;
37 SELECT * FROM t1 WHERE c1=0 AND c2=-2147483648;
38 c1 c2 c3
39 0 -2147483648 6
40 UPDATE t1 SET c1=4294967295,c2=2147483647 WHERE c1=104 OR c2=105;
41 SELECT * FROM t1 WHERE c1=4294967295 AND c2=2147483647;
42 c1 c2 c3
43 UPDATE t1 SET c2=0 WHERE c1 IN (1,2);
44 SELECT * FROM t1 WHERE c2=0;
45 c1 c2 c3
46 1 0 1
47 2 0 2
48 INSERT INTO t1 VALUES(106,-106,9),(107,-107,10),(108,-108,11),(109,-109,12),(255,127,13);
49 UPDATE t1 SET c1=4294967296,c2=2147483648 WHERE c2 BETWEEN -108 AND -106;
50 Warnings:
51 Warning 1264 Out of range value for column 'c1' at row 1
52 Warning 1264 Out of range value for column 'c2' at row 1
53 Warning 1264 Out of range value for column 'c1' at row 2
54 Warning 1264 Out of range value for column 'c2' at row 2
55 Warning 1264 Out of range value for column 'c1' at row 3
56 Warning 1264 Out of range value for column 'c2' at row 3
57 SELECT COUNT(*) FROM t1 WHERE c1=4294967296 AND c2=2147483648 /* no rows */;
58 COUNT(*)
59 0
60 SELECT * FROM t1 WHERE c1=4294967295 AND c2=2147483647;
61 c1 c2 c3
62 4294967295 2147483647 10
63 4294967295 2147483647 11
64 4294967295 2147483647 9
65 UPDATE t1 SET c2=-2147483649 WHERE c1=109 ORDER BY c1;
66 Warnings:
67 Warning 1264 Out of range value for column 'c2' at row 13
68 SET SQL_MODE=DEFAULT;
69 SELECT c1,c2 FROM t1 WHERE c1=109;
70 c1 c2
71 109 -2147483648
72 INSERT INTO t1 VALUES(110,-110,14),(111,-111,15);
73 SET SQL_MODE=STRICT_ALL_TABLES;
74 Warnings:
75 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
76 UPDATE t1 SET c1=NULL WHERE c2=-110;
77 ERROR 23000: Column 'c1' cannot be null
78 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-110 /* updates to default value 0 */;
79 Warnings:
80 Warning 1048 Column 'c1' cannot be null
81 SELECT c1,c2 FROM t1 WHERE c2=-110;
82 c1 c2
83 0 -110
84 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
85 UPDATE t1 SET c1=NULL WHERE c2=-111 /* updates to default value 0 */;
86 Warnings:
87 Warning 1048 Column 'c1' cannot be null
88 SET SQL_MODE=DEFAULT;
89 SELECT * FROM t1 WHERE c1=0;
90 c1 c2 c3
91 0 -102 5
92 0 -110 14
93 0 -111 15
94 0 -128 0
95 0 -2147483648 6
96 SELECT * FROM t1 WHERE c2>=-101 AND c1<=101 ORDER BY c2;
97 c1 c2 c3
98 1 0 1
99 2 0 2
100 UPDATE t1 SET c1=c1+1,c2=c2+1 WHERE c2>=-101 AND c1<=101 ORDER BY c2 LIMIT 2;
101 SELECT * FROM t1;
102 c1 c2 c3
103 0 -102 5
104 0 -110 14
105 0 -111 15
106 0 -128 0
107 0 -2147483648 6
108 109 -2147483648 12
109 110 -104 7
110 110 -105 8
111 120 NULL 3
112 120 NULL 4
113 2 1 1
114 255 127 13
115 3 1 2
116 4294967295 2147483647 10
117 4294967295 2147483647 11
118 4294967295 2147483647 9
119 CREATE TABLE t2(c1 INT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT UNSIGNED NULL, c3 INT);
120 INSERT INTO t2(c1) VALUES(1),(2),(3),(4),(5);
121 UPDATE t2 SET c1=1 WHERE c1=3;
122 ERROR 23000: Duplicate entry '1' for key 't2.PRIMARY'
123 UPDATE IGNORE t2 SET c1=1 WHERE c1>=3;
124 Warnings:
125 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
126 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
127 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
128 SELECT c1 FROM t2;
129 c1
130 1
131 2
132 3
133 4
134 5
135 TRUNCATE TABLE t1;
136 ALTER TABLE t1 CHANGE c1 c1 INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(c1);
137 INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
138 TRUNCATE TABLE t2;
139 INSERT INTO t2 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
140 INSERT INTO t1(c2,c3) VALUES(13,14);
141 SELECT c1 FROM t1 WHERE c2=13;
142 c1
143 11
144 INSERT INTO t2(c2,c3) VALUES(13,14);
145 SELECT c1 FROM t2 WHERE c2=13;
146 c1
147 11
148 UPDATE t1 SET c1=4294967295,c2=2147483647 WHERE c2=13;
149 SELECT c1,c2 FROM t1 ORDER BY c1;
150 c1 c2
151 1 2
152 4 5
153 7 8
154 10 11
155 4294967295 2147483647
156 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
157 UPDATE t2 SET c1=0,c2=-2147483648 WHERE c2=2;
158 Warnings:
159 Warning 1264 Out of range value for column 'c2' at row 1
160 SELECT c1,c2 FROM t1 ORDER BY c1;
161 c1 c2
162 1 2
163 4 5
164 7 8
165 10 11
166 4294967295 2147483647
167 UPDATE t2 SET c2=0 WHERE c2=5;
168 SELECT c1,c2 FROM t1 ORDER BY c1;
169 c1 c2
170 1 2
171 4 5
172 7 8
173 10 11
174 4294967295 2147483647
175 UPDATE t2 SET c1=2147483647,c2=4294967295 WHERE c2=13;
176 SELECT c1,c2 FROM t2 ORDER BY c1;
177 c1 c2
178 0 0
179 4 0
180 7 8
181 10 11
182 2147483647 4294967295
183 UPDATE t2 SET c1=-2147483648,c2=0 WHERE c2=2;
184 SELECT c1,c2 FROM t2 ORDER BY c1;
185 c1 c2
186 0 0
187 4 0
188 7 8
189 10 11
190 2147483647 4294967295
191 UPDATE t2 SET c1=0,c2=0 WHERE c2=5;
192 SELECT c1,c2 FROM t2 ORDER BY c1;
193 c1 c2
194 0 0
195 4 0
196 7 8
197 10 11
198 2147483647 4294967295
199 SET SQL_MODE=DEFAULT;
200 CREATE TABLE mt1 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
201 CREATE TABLE mt2 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
202 CREATE TABLE mt3 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
203 INSERT INTO mt1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
204 INSERT INTO mt2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
205 INSERT INTO mt3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
206 UPDATE IGNORE mt1, mt2 ,mt3 SET mt1.c2 = 30, mt2.c2 = 40, mt3.c2=50 WHERE mt1.c1=mt2.c2 AND mt2.c1=mt3.c2;
207 SELECT * FROM mt1;
208 c1 c2
209 1 30
210 2 30
211 3 3
212 4 4
213 5 5
214 SELECT * FROM mt2;
215 c1 c2
216 11 40
217 12 1
218 13 40
219 14 40
220 15 6
221 SELECT * FROM mt3;
222 c1 c2
223 21 50
224 22 50
225 23 50
226 24 50
227 25 15
228 DROP TABLE mt1,mt2,mt3;
229 TRUNCATE TABLE t1;
230 TRUNCATE TABLE t2;
231 ALTER TABLE t2 CHANGE c2 c2 INT UNSIGNED NULL, ADD KEY(c2);
232 INSERT INTO t1 VALUES(1,-1,1),(2,-2,2),(3,-3,3),(4,-4,4),(5,-5,5),(6,-6,6),(7,-7,7),(8,-8,8),(9,-9,9),(10,-10,10),(11,NULL,11);
233 INSERT INTO t2 VALUES(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(-7,7,7),(-8,8,8),(-9,9,9),(10,10,10),(-11,NULL,11),(-12,12,12);
234 UPDATE t1,t2 SET t1.c1=50,t1.c2=50,t2.c1=50,t2.c2=50 WHERE t2.c1=t1.c1;
235 SELECT * FROM t1,t2 WHERE t1.c1=50 AND t1.c2=50 AND t2.c1=50 AND t2.c2=50;
236 c1 c2 c3 c1 c2 c3
237 50 50 10 50 50 10
238 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
239 c1 c2 c3 c1 c2 c3
240 50 50 10 50 50 10
241 ALTER TABLE t2 CHANGE c1 c1 INT SIGNED NOT NULL, DROP PRIMARY KEY;
242 ALTER TABLE t2 CHANGE c1 c1 INT SIGNED NOT NULL, ADD KEY(c1);
243 INSERT INTO t2 VALUES(-2147483648,0,13),(2147483647,4294967295,14),(0,2147483648,15),(2147483647,2147483647,16);
244 UPDATE t2 SET c1=-2147483648 WHERE c2 <> 0 ORDER BY c2 LIMIT 2;
245 SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c2;
246 c1 c2 c3
247 -2147483648 1 1
248 -2147483648 2 2
249 -3 3 3
250 -4 4 4
251 -5 5 5
252 -6 6 6
253 -7 7 7
254 -8 8 8
255 -9 9 9
256 -12 12 12
257 50 50 10
258 2147483647 2147483647 16
259 0 2147483648 15
260 2147483647 4294967295 14
261 UPDATE t2 SET c1=-2147483648 WHERE c2 >= 0 ORDER BY c2 DESC LIMIT 2;
262 SELECT * FROM t2 WHERE c2 >= 0 ORDER BY c2 DESC;
263 c1 c2 c3
264 -2147483648 4294967295 14
265 -2147483648 2147483648 15
266 2147483647 2147483647 16
267 50 50 10
268 -12 12 12
269 -9 9 9
270 -8 8 8
271 -7 7 7
272 -6 6 6
273 -5 5 5
274 -4 4 4
275 -3 3 3
276 -2147483648 2 2
277 -2147483648 1 1
278 -2147483648 0 13
279 UPDATE t2 SET c1=-2147483648 WHERE c2 <= 3 ORDER BY c2 LIMIT 2;
280 SELECT * FROM t2 WHERE c2 <= 3 ORDER BY c2;
281 c1 c2 c3
282 -2147483648 0 13
283 -2147483648 1 1
284 -2147483648 2 2
285 -3 3 3
286 UPDATE t2 SET c1=-2147483648 WHERE c2 <=> 4 ORDER BY c2 DESC LIMIT 2;
287 SELECT * FROM t2 WHERE c2 <=> 4 ORDER BY c2;
288 c1 c2 c3
289 -2147483648 4 4
290 UPDATE t2 SET c1=-2147483648 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2 LIMIT 2;
291 SELECT * FROM t2 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2;
292 c1 c2 c3
293 -2147483648 4 4
294 -2147483648 5 5
295 -6 6 6
296 -7 7 7
297 UPDATE t2 SET c1=-2147483648 WHERE c2 IN(8,9) ORDER BY c2 DESC LIMIT 2;
298 SELECT * FROM t2 WHERE c2 IN(8,9) ORDER BY c2 DESC;
299 c1 c2 c3
300 -2147483648 9 9
301 -2147483648 8 8
302 UPDATE t2 SET c1=-2147483648 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
303 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
304 c1 c2 c3
305 -2147483648 NULL 11
306 UPDATE t2 SET c1=-2147483648 WHERE c2>= 6 AND c2 < 9 ORDER BY c2 LIMIT 2;
307 SELECT * FROM t2 WHERE c2>= 6 AND c2 < 9 ORDER BY c2;
308 c1 c2 c3
309 -2147483648 6 6
310 -2147483648 7 7
311 -2147483648 8 8
312 UPDATE t2 SET c1=-2147483648 WHERE c1=-12 OR c2=1;
313 SELECT * FROM t2 WHERE c1=-2147483648;
314 c1 c2 c3
315 -2147483648 0 13
316 -2147483648 1 1
317 -2147483648 12 12
318 -2147483648 2 2
319 -2147483648 2147483648 15
320 -2147483648 4 4
321 -2147483648 4294967295 14
322 -2147483648 5 5
323 -2147483648 6 6
324 -2147483648 7 7
325 -2147483648 8 8
326 -2147483648 9 9
327 -2147483648 NULL 11
328 SELECT * FROM t2;
329 c1 c2 c3
330 -2147483648 0 13
331 -2147483648 1 1
332 -2147483648 12 12
333 -2147483648 2 2
334 -2147483648 2147483648 15
335 -2147483648 4 4
336 -2147483648 4294967295 14
337 -2147483648 5 5
338 -2147483648 6 6
339 -2147483648 7 7
340 -2147483648 8 8
341 -2147483648 9 9
342 -2147483648 NULL 11
343 -3 3 3
344 2147483647 2147483647 16
345 50 50 10
346 SELECT c1 FROM t2;
347 c1
348 -2147483648
349 -2147483648
350 -2147483648
351 -2147483648
352 -2147483648
353 -2147483648
354 -2147483648
355 -2147483648
356 -2147483648
357 -2147483648
358 -2147483648
359 -2147483648
360 -2147483648
361 -3
362 2147483647
363 50
364 SELECT c1 FROM t2 ORDER BY c1 DESC;
365 c1
366 2147483647
367 50
368 -3
369 -2147483648
370 -2147483648
371 -2147483648
372 -2147483648
373 -2147483648
374 -2147483648
375 -2147483648
376 -2147483648
377 -2147483648
378 -2147483648
379 -2147483648
380 -2147483648
381 -2147483648
382 SELECT * FROM t2 WHERE c1=-2147483648;
383 c1 c2 c3
384 -2147483648 0 13
385 -2147483648 1 1
386 -2147483648 12 12
387 -2147483648 2 2
388 -2147483648 2147483648 15
389 -2147483648 4 4
390 -2147483648 4294967295 14
391 -2147483648 5 5
392 -2147483648 6 6
393 -2147483648 7 7
394 -2147483648 8 8
395 -2147483648 9 9
396 -2147483648 NULL 11
397 SELECT * FROM t2 WHERE c1 <> -2147483648 ORDER BY c1,c2;
398 c1 c2 c3
399 -3 3 3
400 50 50 10
401 2147483647 2147483647 16
402 SELECT * FROM t2 WHERE c1 <> -2147483648 ORDER BY c1,c2 DESC LIMIT 2;
403 c1 c2 c3
404 -3 3 3
405 50 50 10
406 SELECT * FROM t2 WHERE c1 > -2147483648 ORDER BY c1,c2 DESC;
407 c1 c2 c3
408 -3 3 3
409 50 50 10
410 2147483647 2147483647 16
411 SELECT * FROM t2 WHERE c1 >= -2147483648 ORDER BY c1,c2 LIMIT 2;
412 c1 c2 c3
413 -2147483648 NULL 11
414 -2147483648 0 13
415 SELECT * FROM t2 WHERE c1 < -2147483648 ORDER BY c1,c2;
416 c1 c2 c3
417 SELECT * FROM t2 WHERE c1 <= -2147483648 ORDER BY c1,c2 DESC LIMIT 2;
418 c1 c2 c3
419 -2147483648 4294967295 14
420 -2147483648 2147483648 15
421 SELECT * FROM t2 WHERE c1 <=> -2147483648 ORDER BY c1,c2 DESC;
422 c1 c2 c3
423 -2147483648 4294967295 14
424 -2147483648 2147483648 15
425 -2147483648 12 12
426 -2147483648 9 9
427 -2147483648 8 8
428 -2147483648 7 7
429 -2147483648 6 6
430 -2147483648 5 5
431 -2147483648 4 4
432 -2147483648 2 2
433 -2147483648 1 1
434 -2147483648 0 13
435 -2147483648 NULL 11
436 SELECT * FROM t2 WHERE c1 <=> -2147483648 ORDER BY c1,c2 LIMIT 2;
437 c1 c2 c3
438 -2147483648 NULL 11
439 -2147483648 0 13
440 SELECT * FROM t2 WHERE c1 BETWEEN -2147483648 AND 2147483647 ORDER BY c1,c2;
441 c1 c2 c3
442 -2147483648 NULL 11
443 -2147483648 0 13
444 -2147483648 1 1
445 -2147483648 2 2
446 -2147483648 4 4
447 -2147483648 5 5
448 -2147483648 6 6
449 -2147483648 7 7
450 -2147483648 8 8
451 -2147483648 9 9
452 -2147483648 12 12
453 -2147483648 2147483648 15
454 -2147483648 4294967295 14
455 -3 3 3
456 50 50 10
457 2147483647 2147483647 16
458 SELECT * FROM t2 WHERE c1 BETWEEN -2147483648 AND 2147483647 ORDER BY c1,c2 DESC LIMIT 2;
459 c1 c2 c3
460 -2147483648 4294967295 14
461 -2147483648 2147483648 15
462 SELECT * FROM t2 WHERE c1 IN(-2147483648,2147483647) ORDER BY c1,c2 DESC;
463 c1 c2 c3
464 -2147483648 4294967295 14
465 -2147483648 2147483648 15
466 -2147483648 12 12
467 -2147483648 9 9
468 -2147483648 8 8
469 -2147483648 7 7
470 -2147483648 6 6
471 -2147483648 5 5
472 -2147483648 4 4
473 -2147483648 2 2
474 -2147483648 1 1
475 -2147483648 0 13
476 -2147483648 NULL 11
477 2147483647 2147483647 16
478 SELECT * FROM t2 WHERE c1 IN(-2147483648,2147483647) ORDER BY c1,c2 LIMIT 2;
479 c1 c2 c3
480 -2147483648 NULL 11
481 -2147483648 0 13
482 SELECT * FROM t2 WHERE c1>=-2147483648 AND c1 <= 2147483647 ORDER BY c1,c2 DESC;
483 c1 c2 c3
484 -2147483648 4294967295 14
485 -2147483648 2147483648 15
486 -2147483648 12 12
487 -2147483648 9 9
488 -2147483648 8 8
489 -2147483648 7 7
490 -2147483648 6 6
491 -2147483648 5 5
492 -2147483648 4 4
493 -2147483648 2 2
494 -2147483648 1 1
495 -2147483648 0 13
496 -2147483648 NULL 11
497 -3 3 3
498 50 50 10
499 2147483647 2147483647 16
500 SELECT * FROM t2 WHERE c1>=-2147483648 AND c1 <= 2147483647 ORDER BY c1,c2 LIMIT 2;
501 c1 c2 c3
502 -2147483648 NULL 11
503 -2147483648 0 13
504 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1;
505 c1 c2 c3
506 -2147483648 NULL 11
507 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
508 c1 c2 c3
509 -2147483648 NULL 11
510 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
511 c1 c2 c3
512 -2147483648 0 13
513 -2147483648 1 1
514 -2147483648 2 2
515 -3 3 3
516 -2147483648 4 4
517 -2147483648 5 5
518 -2147483648 6 6
519 -2147483648 7 7
520 -2147483648 8 8
521 -2147483648 9 9
522 -2147483648 12 12
523 50 50 10
524 2147483647 2147483647 16
525 -2147483648 2147483648 15
526 -2147483648 4294967295 14
527 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 LIMIT 2;
528 c1 c2 c3
529 -2147483648 0 13
530 -2147483648 1 1
531 CREATE TABLE mt1 (a int not null primary key, b int not null, key (b));
532 CREATE TABLE mt2 (a int not null primary key, b int not null, key (b));
533 INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
534 INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
535 update mt1,mt2 set mt1.a=mt1.a+100;
536 select * from mt1;
537 a b
538 101 1
539 102 2
540 103 3
541 104 4
542 105 5
543 106 6
544 107 7
545 108 8
546 109 9
547 update mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
548 select * from mt1;
549 a b
550 102 2
551 103 3
552 104 4
553 105 5
554 106 6
555 107 7
556 108 8
557 109 9
558 201 1
559 update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
560 select * from mt1;
561 a b
562 102 12
563 103 3
564 104 4
565 105 5
566 106 6
567 107 7
568 108 8
569 109 9
570 201 1
571 update mt1 straight_join mt2 set mt1.b=mt1.b+2,mt2.b=mt1.b+10 where mt1.b between 3 and 5 and mt2.a=mt1.a-100;
572 select * from mt1;
573 a b
574 102 12
575 103 5
576 104 6
577 105 7
578 106 6
579 107 7
580 108 8
581 109 9
582 201 1
583 update mt1,mt2 set mt1.b=mt2.b, mt1.a=mt2.a where mt1.a=mt2.a and not exists (select * from mt2 where mt2.a > 10);
584 drop table mt1,mt2;
585 TRUNCATE TABLE t2;
586 INSERT INTO t2 VALUES(-128,0,1),(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(0,0,7),(1,1,8),(127,255,9);
587 SELECT * FROM t2 WHERE c1=127 OR c2=3;
588 c1 c2 c3
589 -3 3 3
590 127 255 9
591 DELETE FROM t2 WHERE c1=127 OR c2=3;
592 SELECT * FROM t2 WHERE c1=127 OR c2=3;
593 c1 c2 c3
594 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
595 c1 c2 c3
596 -128 0 1
597 -6 6 6
598 -5 5 5
599 -4 4 4
600 -2 2 2
601 -1 1 1
602 0 0 7
603 1 1 8
604 DELETE FROM t2 WHERE c1 >= -128 ORDER BY c1 LIMIT 2;
605 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
606 c1 c2 c3
607 -5 5 5
608 -4 4 4
609 -2 2 2
610 -1 1 1
611 0 0 7
612 1 1 8
613 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
614 c1 c2 c3
615 1 1 8
616 0 0 7
617 -1 1 1
618 -2 2 2
619 -4 4 4
620 -5 5 5
621 DELETE FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC LIMIT 2;
622 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
623 c1 c2 c3
624 -1 1 1
625 -2 2 2
626 -4 4 4
627 -5 5 5
628 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
629 c1 c2 c3
630 -5 5 5
631 -4 4 4
632 -2 2 2
633 -1 1 1
634 DELETE FROM t2 WHERE c1 < 0 ORDER BY c1 LIMIT 2;
635 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
636 c1 c2 c3
637 -2 2 2
638 -1 1 1
639 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
640 c1 c2 c3
641 -1 1 1
642 -2 2 2
643 DELETE FROM t2 WHERE c1 > -3 ORDER BY c1 DESC LIMIT 2;
644 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
645 c1 c2 c3
646 INSERT INTO t2 VALUES(-128,0,1),(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(0,0,7),(1,1,8),(2,NULL,9),(3,NULL,10),(127,255,11);
647 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
648 c1 c2 c3
649 -128 0 1
650 -6 6 6
651 -5 5 5
652 -4 4 4
653 -3 3 3
654 -2 2 2
655 -1 1 1
656 0 0 7
657 1 1 8
658 2 NULL 9
659 3 NULL 10
660 127 255 11
661 DELETE FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1 LIMIT 2;
662 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
663 c1 c2 c3
664 -5 5 5
665 -4 4 4
666 -3 3 3
667 -2 2 2
668 -1 1 1
669 0 0 7
670 1 1 8
671 2 NULL 9
672 3 NULL 10
673 127 255 11
674 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
675 c1 c2 c3
676 0 0 7
677 -2 2 2
678 DELETE FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
679 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
680 c1 c2 c3
681 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
682 c1 c2 c3
683 -1 1 1
684 1 1 8
685 DELETE FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1 LIMIT 2;
686 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
687 c1 c2 c3
688 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
689 c1 c2 c3
690 -5 5 5
691 -4 4 4
692 -3 3 3
693 127 255 11
694 DELETE FROM t2 WHERE c2 IS NOT NULL ORDER BY c1 LIMIT 2;
695 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
696 c1 c2 c3
697 -3 3 3
698 127 255 11
699 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
700 c1 c2 c3
701 3 NULL 10
702 2 NULL 9
703 DELETE FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
704 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
705 c1 c2 c3
706 TRUNCATE TABLE t1;
707 TRUNCATE TABLE t2;
708 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
709 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
710 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
711 c1 c2 c3 c1 c2 c3
712 1 127 3 127 1 2
713 1 127 3 127 255 1
714 2 -128 4 -128 0 3
715 2 -128 4 -128 2 4
716 254 127 1 127 1 2
717 254 127 1 127 255 1
718 255 -128 2 -128 0 3
719 255 -128 2 -128 2 4
720 255 -128 2 127 255 1
721 3 NULL 5 -1 NULL 5
722 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
723 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
724 c1 c2 c3 c1 c2 c3
725 TRUNCATE TABLE t1;
726 TRUNCATE TABLE t2;
727 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
728 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
729 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
730 c1 c2 c3 c1 c2 c3
731 1 127 3 127 1 2
732 1 127 3 127 255 1
733 2 -128 4 -128 0 3
734 2 -128 4 -128 2 4
735 254 127 1 127 1 2
736 254 127 1 127 255 1
737 255 -128 2 -128 0 3
738 255 -128 2 -128 2 4
739 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
740 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
741 c1 c2 c3 c1 c2 c3
742 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
743 ERROR HY000: You can't specify target table 't1' for update in FROM clause
744 CREATE TABLE t3(c1 INT UNSIGNED NOT NULL PRIMARY KEY, c2 INT SIGNED NULL, c3 INT);
745 CREATE TABLE t4(c1 INT UNSIGNED, c2 INT);
746 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
747 INSERT INTO t4 VALUES(200,1),(150,3);
748 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
749 c1 c2 c3 c1 c2
750 200 126 1 200 1
751 DELETE t3.*,t4.* FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
752 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
753 c1 c2 c3 c1 c2
754 DROP TABLE t1,t2,t3,t4;
755 create table mt1 (a int not null, b int not null, key (a));
756 insert into mt1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
757 SET @tmp=0;
758 update mt1 set b=(@tmp:=@tmp+1) order by a;
759 Warnings:
760 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
761 update mt1 set b=99 where a=1 order by b asc limit 1;
762 select * from mt1 order by a,b;
763 a b
764 1 2
765 1 3
766 1 99
767 2 4
768 2 5
769 2 6
770 3 7
771 3 8
772 3 9
773 3 10
774 3 11
775 3 12
776 update mt1 set b=100 where a=1 order by b desc limit 2;
777 update mt1 set a=a+10+b where a=1 order by b;
778 select * from mt1 order by a,b;
779 a b
780 2 4
781 2 5
782 2 6
783 3 7
784 3 8
785 3 9
786 3 10
787 3 11
788 3 12
789 13 2
790 111 100
791 111 100
792 create table mt2 (a int not null, b int not null);
793 insert into mt2 values (1,1),(1,2),(1,3);
794 update mt1 set b=(select distinct 1 from (select * from mt2) a);
795 drop table mt1,mt2;
796 create table mt1 (f1 int);
797 create table mt2 (f2 int);
798 insert into mt1 values(1),(2);
799 insert into mt2 values(1),(1);
800 update mt1,mt2 set f1=3,f2=3 where f1=f2 and f1=1;
801 affected rows: 3
802 info: Rows matched: 3 Changed: 3 Warnings: 0
803 update mt2 set f2=1;
804 update mt1 set f1=1 where f1=3;
805 update mt2,mt1 set f1=3,f2=3 where f1=f2 and f1=1;
806 affected rows: 3
807 info: Rows matched: 3 Changed: 3 Warnings: 0
808 SELECT * FROM mt1;
809 f1
810 2
811 3
812 SELECT * FROM mt2;
813 f2
814 3
815 3
816 drop table mt1,mt2;
817 create table mt1(f1 int, f2 int);
818 create table mt2(f3 int, f4 int);
819 create index idx on mt2(f3);
820 insert into mt1 values(1,0),(2,0);
821 insert into mt2 values(1,1),(2,2);
822 UPDATE mt1 SET mt1.f2=(SELECT MAX(mt2.f4) FROM mt2 WHERE mt2.f3=mt1.f1);
823 select * from mt1;
824 f1 f2
825 1 1
826 2 2
827 drop table mt1,mt2;
828 create table mt1 (a int, b char(255), key(a, b(20)));
829 insert into mt1 values (0, '1');
830 update mt1 set b = b + 1 where a = 0;
831 select * from mt1;
832 a b
833 0 2
834 drop table mt1;
835 create table mt1(f1 int, f2 int);
836 create table mt2(f3 int, f4 int);
837 create index idx on mt2(f3);
838 insert into mt1 values(1,0),(2,0);
839 insert into mt2 values(1,1),(2,2);
840 UPDATE mt1 SET mt1.f2=(SELECT MAX(mt2.f4) FROM mt2 WHERE mt2.f3=mt1.f1);
841 select * from mt1;
842 f1 f2
843 1 1
844 2 2
845 drop table mt1,mt2;
846 create table mt1 (id int not null auto_increment primary key, id_str varchar(32));
847 insert into mt1 (id_str) values ("test");
848 update mt1 set id_str = concat(id_str, id) where id = last_insert_id();
849 select * from mt1;
850 id id_str
851 1 test1
852 drop table mt1;
853 create table mt1 (n int(10) not null primary key, d int(10));
854 Warnings:
855 Warning 1681 Integer display width is deprecated and will be removed in a future release.
856 Warning 1681 Integer display width is deprecated and will be removed in a future release.
857 create table mt2 (n int(10) not null primary key, d int(10));
858 Warnings:
859 Warning 1681 Integer display width is deprecated and will be removed in a future release.
860 Warning 1681 Integer display width is deprecated and will be removed in a future release.
861 insert into mt1 values(1,1), (3,3);
862 insert into mt2 values(1,10),(2,20);
863 UPDATE mt2 left outer join mt1 on mt1.n=mt2.n SET mt1.d=mt2.d;
864 select * from mt1;
865 n d
866 1 10
867 3 3
868 select * from mt2;
869 n d
870 1 10
871 2 20
872 drop table mt1,mt2;
873 create table mt1 (n int(10), d int(10));
874 Warnings:
875 Warning 1681 Integer display width is deprecated and will be removed in a future release.
876 Warning 1681 Integer display width is deprecated and will be removed in a future release.
877 create table mt2 (n int(10), d int(10));
878 Warnings:
879 Warning 1681 Integer display width is deprecated and will be removed in a future release.
880 Warning 1681 Integer display width is deprecated and will be removed in a future release.
881 insert into mt1 values(1,1),(1,2);
882 insert into mt2 values(1,10),(2,20);
883 UPDATE mt1 straight_join mt2 SET mt1.d=mt2.d,mt2.d=30 WHERE mt1.n=mt2.n;
884 select * from mt1;
885 n d
886 1 10
887 1 10
888 select * from mt2;
889 n d
890 1 30
891 2 20
892 drop table mt1,mt2;
893 create table mt1 (n int(10), d int(10));
894 Warnings:
895 Warning 1681 Integer display width is deprecated and will be removed in a future release.
896 Warning 1681 Integer display width is deprecated and will be removed in a future release.
897 create table mt2 (n int(10), d int(10));
898 Warnings:
899 Warning 1681 Integer display width is deprecated and will be removed in a future release.
900 Warning 1681 Integer display width is deprecated and will be removed in a future release.
901 insert into mt1 values(1,1),(3,2);
902 insert into mt2 values(1,10),(1,20);
903 UPDATE mt1 straight_join mt2 SET mt1.d=mt2.d,mt2.d=30 WHERE mt1.n=mt2.n;
904 select * from mt1;
905 n d
906 1 10
907 3 2
908 select * from mt2;
909 n d
910 1 30
911 1 30
912 UPDATE mt1 a straight_join mt2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
913 select * from mt1;
914 n d
915 1 30
916 3 2
917 select * from mt2;
918 n d
919 1 30
920 1 30
921 DELETE a, b FROM mt1 a,mt2 b where a.n=b.n;
922 select * from mt1;
923 n d
924 3 2
925 select * from mt2;
926 n d
927 drop table mt1,mt2;
928 CREATE TABLE mt1 (`colA` int(10) unsigned NOT NULL auto_increment, `colB` int(11) NOT NULL default '0', PRIMARY KEY (`colA`));
929 Warnings:
930 Warning 1681 Integer display width is deprecated and will be removed in a future release.
931 Warning 1681 Integer display width is deprecated and will be removed in a future release.
932 INSERT INTO mt1 VALUES (4433,5424);
933 CREATE TABLE mt2 (`colC` int(10) unsigned NOT NULL default '0', `colA` int(10) unsigned NOT NULL default '0', `colD` int(10) unsigned NOT NULL default '0', `colE` int(10) unsigned NOT NULL default '0', `colF` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`colC`,`colA`,`colD`,`colE`));
934 Warnings:
935 Warning 1681 Integer display width is deprecated and will be removed in a future release.
936 Warning 1681 Integer display width is deprecated and will be removed in a future release.
937 Warning 1681 Integer display width is deprecated and will be removed in a future release.
938 Warning 1681 Integer display width is deprecated and will be removed in a future release.
939 Warning 1681 Integer display width is deprecated and will be removed in a future release.
940 INSERT INTO mt2 VALUES (3,4433,10005,495,500);
941 INSERT INTO mt2 VALUES (3,4433,10005,496,500);
942 INSERT INTO mt2 VALUES (3,4433,10009,494,500);
943 INSERT INTO mt2 VALUES (3,4433,10011,494,500);
944 INSERT INTO mt2 VALUES (3,4433,10005,497,500);
945 INSERT INTO mt2 VALUES (3,4433,10013,489,500);
946 INSERT INTO mt2 VALUES (3,4433,10005,494,500);
947 INSERT INTO mt2 VALUES (3,4433,10005,493,500);
948 INSERT INTO mt2 VALUES (3,4433,10005,492,500);
949 UPDATE IGNORE mt2,mt1 set mt2.colE = mt2.colE + 1,colF=0 WHERE mt1.colA = mt2.colA AND (mt1.colB & 4096) > 0 AND (colE + 1) < colF;
950 Warnings:
951 Warning 1062 Duplicate entry '3-4433-10005-493' for key 'mt2.PRIMARY'
952 Warning 1062 Duplicate entry '3-4433-10005-494' for key 'mt2.PRIMARY'
953 Warning 1062 Duplicate entry '3-4433-10005-495' for key 'mt2.PRIMARY'
954 Warning 1062 Duplicate entry '3-4433-10005-496' for key 'mt2.PRIMARY'
955 Warning 1062 Duplicate entry '3-4433-10005-497' for key 'mt2.PRIMARY'
956 SELECT * FROM mt2;
957 colC colA colD colE colF
958 3 4433 10005 492 500
959 3 4433 10005 493 500
960 3 4433 10005 494 500
961 3 4433 10005 495 500
962 3 4433 10005 496 500
963 3 4433 10005 498 0
964 3 4433 10009 495 0
965 3 4433 10011 495 0
966 3 4433 10013 490 0
967 DROP TABLE mt1,mt2;
968 create table mt1(id1 smallint(5), field char(5));
969 Warnings:
970 Warning 1681 Integer display width is deprecated and will be removed in a future release.
971 create table mt2(id2 smallint(5), field char(5));
972 Warnings:
973 Warning 1681 Integer display width is deprecated and will be removed in a future release.
974 insert into mt1 values (1, 'a'), (2, 'aa');
975 insert into mt2 values (1, 'b'), (2, 'bb');
976 select * from mt1;
977 id1 field
978 1 a
979 2 aa
980 select * from mt2;
981 id2 field
982 1 b
983 2 bb
984 update mt2 inner join mt1 on mt1.id1=mt2.id2 set mt2.field=mt1.field where 0=1;
985 update mt2, mt1 set mt2.field=mt1.field where mt1.id1=mt2.id2 and 0=1;
986 delete mt1, mt2 from mt2 inner join mt1 on mt1.id1=mt2.id2 where 0=1;
987 delete mt1, mt2 from mt2,mt1 where mt1.id1=mt2.id2 and 0=1;
988 drop table mt1,mt2;
989 create table mt1 (n numeric(10));
990 create table mt2 (n numeric(10));
991 insert into mt2 values (1),(2),(4),(8),(16),(32);
992 select * from mt2 left outer join mt1 using (n);
993 n
994 1
995 16
996 2
997 32
998 4
999 8
1000 delete mt1,mt2 from mt2 left outer join mt1 using (n);
1001 select * from mt2 left outer join mt1 using (n);
1002 n
1003 drop table mt1,mt2 ;
1004 create table mt1(id1 int not null primary key, t varchar(100)) pack_keys = 1;
1005 create table mt2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1;
1006 delete mt1 from mt1,mt2 where mt1.id1 = mt2.id2 and mt1.id1 > 500;
1007 drop table mt1,mt2;
1008 create table mt1 (aclid bigint not null primary key, status tinyint(1) not null);
1009 create table mt2 (refid bigint not null primary key, aclid bigint, index idx_acl(aclid));
1010 insert into mt2 values(1,null);
1011 delete mt2, mt1 from mt2 left join mt1 on (mt2.aclid=mt1.aclid) where mt2.refid='1';
1012 drop table mt1, mt2;
1013 create table mt1 (a int, primary key (a));
1014 create table mt2 (a int, primary key (a));
1015 create table mt3 (a int, primary key (a));
1016 delete mt1,mt3 from mt1,mt2 where mt1.a=mt2.a and mt2.a=(select mt3.a from mt3 where mt1.a=mt3.a);
1017 ERROR 42S02: Unknown table 'mt3' in MULTI DELETE
1018 drop table mt1, mt2, mt3;
1019 create table mt1 (col1 int);
1020 create table mt2 (col1 int);
1021 update mt1,mt2 set mt1.col1 = (select max(col1) from mt1) where mt1.col1 = mt2.col1;
1022 ERROR HY000: You can't specify target table 'mt1' for update in FROM clause
1023 delete mt1 from mt1,mt2 where mt1.col1 < (select max(col1) from mt1) and mt1.col1 = mt2.col1;
1024 ERROR HY000: You can't specify target table 'mt1' for update in FROM clause
1025 drop table mt1,mt2;
1026 CREATE TABLE IF NOT EXISTS `mt1` (`id` int(11) NOT NULL auto_increment, `tst` text, `tsmt1` text, PRIMARY KEY (`id`));
1027 Warnings:
1028 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1029 CREATE TABLE IF NOT EXISTS `mt2` (`ID` int(11) NOT NULL auto_increment, `ParId` int(11) default NULL, `tst` text, `tsmt1` text, PRIMARY KEY (`ID`), KEY `IX_ParId_mt2` (`ParId`), FOREIGN KEY (`ParId`) REFERENCES `mt1` (`id`));
1030 Warnings:
1031 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1032 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1033 INSERT INTO mt1(tst,tsmt1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
1034 INSERT INTO mt2(ParId) VALUES(1), (2), (3);
1035 select * from mt2;
1036 ID ParId tst tsmt1
1037 1 1 NULL NULL
1038 2 2 NULL NULL
1039 3 3 NULL NULL
1040 UPDATE mt2, mt1 SET mt2.tst = mt1.tst, mt2.tsmt1 = mt1.tsmt1 WHERE mt2.ParId = mt1.Id;
1041 select * from mt2;
1042 ID ParId tst tsmt1
1043 1 1 MySQL MySQL AB
1044 2 2 MSSQL Microsoft
1045 3 3 ORACLE ORACLE
1046 drop table mt2,mt1;
1047 CREATE TABLE mt1 ( a int );
1048 CREATE TABLE mt2 ( a int );
1049 DELETE mt1 FROM mt1, mt2 AS t3;
1050 DELETE t4 FROM mt1, mt1 AS t4;
1051 DELETE t3 FROM mt1 AS t3, mt1 AS t4;
1052 DELETE mt1 FROM mt1 AS t3, mt2 AS t4;
1053 ERROR 42S02: Unknown table 'mt1' in MULTI DELETE
1054 INSERT INTO mt1 values (1),(2);
1055 INSERT INTO mt2 values (1),(2);
1056 DELETE mt1 FROM mt1 AS mt2, mt2 AS mt1 where mt1.a=mt2.a and mt1.a=1;
1057 SELECT * from mt1;
1058 a
1059 1
1060 2
1061 SELECT * from mt2;
1062 a
1063 2
1064 DELETE mt2 FROM mt1 AS mt2, mt2 AS mt1 where mt1.a=mt2.a and mt1.a=2;
1065 SELECT * from mt1;
1066 a
1067 1
1068 SELECT * from mt2;
1069 a
1070 2
1071 DROP TABLE mt1,mt2;
1072 create table mt1(id1 int not null auto_increment primary key, t char(12));
1073 create table mt2(id2 int not null, t char(12));
1074 create table mt3(id3 int not null, t char(12), index(id3));
1075 select count(*) from mt1 where id1 > 95;
1076 count(*)
1077 5
1078 select count(*) from mt2 where id2 > 95;
1079 count(*)
1080 25
1081 select count(*) from mt3 where id3 > 95;
1082 count(*)
1083 250
1084 update mt1,mt2,mt3 set mt1.t="aaa", mt2.t="bbb", mt3.t="cc" where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3 and mt1.id1 > 90;
1085 select count(*) from mt1 where t = "aaa";
1086 count(*)
1087 10
1088 select count(*) from mt1 where id1 > 90;
1089 count(*)
1090 10
1091 select count(*) from mt2 where t = "bbb";
1092 count(*)
1093 50
1094 select count(*) from mt2 where id2 > 90;
1095 count(*)
1096 50
1097 select count(*) from mt3 where t = "cc";
1098 count(*)
1099 500
1100 select count(*) from mt3 where id3 > 90;
1101 count(*)
1102 500
1103 delete mt1.*, mt2.*, mt3.* from mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3 and mt1.id1 > 95;
1104 check table mt1, mt2, mt3;
1105 Table Op Msg_type Msg_text
1106 test.mt1 check status OK
1107 test.mt2 check status OK
1108 test.mt3 check status OK
1109 select count(*) from mt1 where id1 > 95;
1110 count(*)
1111 0
1112 select count(*) from mt2 where id2 > 95;
1113 count(*)
1114 0
1115 select count(*) from mt3 where id3 > 95;
1116 count(*)
1117 0
1118 delete mt1, mt2, mt3 from mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3 and mt1.id1 > 5;
1119 select count(*) from mt1 where id1 > 5;
1120 count(*)
1121 0
1122 select count(*) from mt2 where id2 > 5;
1123 count(*)
1124 0
1125 select count(*) from mt3 where id3 > 5;
1126 count(*)
1127 0
1128 delete from mt1, mt2, mt3 using mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3 and mt1.id1 > 0;
1129 select count(*) from mt1 where id1;
1130 count(*)
1131 0
1132 select count(*) from mt2 where id2;
1133 count(*)
1134 0
1135 select count(*) from mt3 where id3;
1136 count(*)
1137 0
1138 drop table mt1,mt2,mt3;
1139 CREATE TABLE t1(c1 TINYINT UNSIGNED NOT NULL, c2 TINYINT SIGNED NULL, c3 INT, INDEX idx2(c2));
1140 INSERT INTO t1 VALUES(0,-128,0),(1,1,1),(2,2,2),(0,NULL,3),(101,-101,4),(102,-102,5),(103,-103,6),(104,-104,7),(105,-105,8);
1141 SELECT * FROM t1;
1142 c1 c2 c3
1143 0 -128 0
1144 0 NULL 3
1145 1 1 1
1146 101 -101 4
1147 102 -102 5
1148 103 -103 6
1149 104 -104 7
1150 105 -105 8
1151 2 2 2
1152 UPDATE t1 SET c1=110 WHERE c2 >-128 ORDER BY c2 LIMIT 2;
1153 SELECT * FROM t1 WHERE c1=110;
1154 c1 c2 c3
1155 110 -104 7
1156 110 -105 8
1157 UPDATE t1 SET c1=c1+1,c2=NULL WHERE c1=101;
1158 SELECT * FROM t1 WHERE c1=102 AND c2 IS NULL;
1159 c1 c2 c3
1160 102 NULL 4
1161 UPDATE t1 SET c1=120 WHERE c2 IS NULL;
1162 SELECT c1,c2 FROM t1 WHERE c1=120;
1163 c1 c2
1164 120 NULL
1165 120 NULL
1166 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
1167 UPDATE t1 SET c1=-120 WHERE c2=-102;
1168 Warnings:
1169 Warning 1264 Out of range value for column 'c1' at row 1
1170 SELECT c1,c2 FROM t1 WHERE c2=-102;
1171 c1 c2
1172 0 -102
1173 UPDATE t1 SET c1=0,c2=-128 WHERE c1=103 AND c2=-103;
1174 SELECT * FROM t1 WHERE c1=0 AND c2=-128;
1175 c1 c2 c3
1176 0 -128 0
1177 0 -128 6
1178 UPDATE t1 SET c1=255,c2=127 WHERE c1=104 OR c2=105;
1179 SELECT * FROM t1 WHERE c1=255 AND c2=127;
1180 c1 c2 c3
1181 UPDATE t1 SET c2=0 WHERE c1 IN (1,2);
1182 SELECT * FROM t1 WHERE c2=0;
1183 c1 c2 c3
1184 1 0 1
1185 2 0 2
1186 INSERT INTO t1 VALUES(106,-106,9),(107,-107,10),(108,-108,11),(109,-109,12),(255,127,13);
1187 UPDATE t1 SET c1=256,c2=128 WHERE c2 BETWEEN -108 AND -106;
1188 Warnings:
1189 Warning 1264 Out of range value for column 'c1' at row 1
1190 Warning 1264 Out of range value for column 'c2' at row 1
1191 Warning 1264 Out of range value for column 'c1' at row 2
1192 Warning 1264 Out of range value for column 'c2' at row 2
1193 Warning 1264 Out of range value for column 'c1' at row 3
1194 Warning 1264 Out of range value for column 'c2' at row 3
1195 SELECT COUNT(*) FROM t1 WHERE c1=256 AND c2=128 /* no rows */;
1196 COUNT(*)
1197 0
1198 SELECT * FROM t1 WHERE c1=255 AND c2=127;
1199 c1 c2 c3
1200 255 127 10
1201 255 127 11
1202 255 127 13
1203 255 127 9
1204 UPDATE t1 SET c2=-129 WHERE c1=109 ORDER BY c1;
1205 Warnings:
1206 Warning 1264 Out of range value for column 'c2' at row 13
1207 SELECT c1,c2 FROM t1 WHERE c1=109;
1208 c1 c2
1209 109 -128
1210 SET SQL_MODE=DEFAULT;
1211 INSERT INTO t1 VALUES(110,-110,14),(111,-111,15);
1212 SET SQL_MODE=STRICT_ALL_TABLES;
1213 Warnings:
1214 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
1215 UPDATE t1 SET c1=NULL WHERE c2=-110;
1216 ERROR 23000: Column 'c1' cannot be null
1217 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-110 /* updates to default value 0 */;
1218 Warnings:
1219 Warning 1048 Column 'c1' cannot be null
1220 SELECT c1,c2 FROM t1 WHERE c2=-110;
1221 c1 c2
1222 0 -110
1223 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
1224 UPDATE t1 SET c1=NULL WHERE c2=-111 /* updates to default value 0 */;
1225 Warnings:
1226 Warning 1048 Column 'c1' cannot be null
1227 SET SQL_MODE=DEFAULT;
1228 SELECT * FROM t1 WHERE c1=0;
1229 c1 c2 c3
1230 0 -102 5
1231 0 -110 14
1232 0 -111 15
1233 0 -128 0
1234 0 -128 6
1235 SELECT * FROM t1 WHERE c2>=-101 AND c1<=101 ORDER BY c2;
1236 c1 c2 c3
1237 1 0 1
1238 2 0 2
1239 UPDATE t1 SET c1=c1+1,c2=c2+1 WHERE c2>=-101 AND c1<=101 ORDER BY c2 LIMIT 2;
1240 SELECT * FROM t1;
1241 c1 c2 c3
1242 0 -102 5
1243 0 -110 14
1244 0 -111 15
1245 0 -128 0
1246 0 -128 6
1247 109 -128 12
1248 110 -104 7
1249 110 -105 8
1250 120 NULL 3
1251 120 NULL 4
1252 2 1 1
1253 255 127 10
1254 255 127 11
1255 255 127 13
1256 255 127 9
1257 3 1 2
1258 CREATE TABLE t2(c1 TINYINT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 TINYINT UNSIGNED NULL, c3 INT);
1259 INSERT INTO t2(c1) VALUES(1),(2),(3),(4),(5);
1260 UPDATE t2 SET c1=1 WHERE c1=3;
1261 ERROR 23000: Duplicate entry '1' for key 't2.PRIMARY'
1262 UPDATE IGNORE t2 SET c1=1 WHERE c1>=3;
1263 Warnings:
1264 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
1265 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
1266 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
1267 SELECT c1 FROM t2;
1268 c1
1269 1
1270 2
1271 3
1272 4
1273 5
1274 TRUNCATE TABLE t1;
1275 ALTER TABLE t1 CHANGE c1 c1 TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(c1);
1276 INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
1277 TRUNCATE TABLE t2;
1278 INSERT INTO t2 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
1279 INSERT INTO t1(c2,c3) VALUES(13,14);
1280 SELECT c1 FROM t1 WHERE c2=13;
1281 c1
1282 11
1283 INSERT INTO t2(c2,c3) VALUES(13,14);
1284 SELECT c1 FROM t2 WHERE c2=13;
1285 c1
1286 11
1287 UPDATE t1 SET c1=255,c2=127 WHERE c2=13;
1288 SELECT c1,c2 FROM t1 ORDER BY c1;
1289 c1 c2
1290 1 2
1291 4 5
1292 7 8
1293 10 11
1294 255 127
1295 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
1296 UPDATE t2 SET c1=0,c2=-128 WHERE c2=2;
1297 Warnings:
1298 Warning 1264 Out of range value for column 'c2' at row 1
1299 SET SQL_MODE=DEFAULT;
1300 SELECT c1,c2 FROM t1 ORDER BY c1;
1301 c1 c2
1302 1 2
1303 4 5
1304 7 8
1305 10 11
1306 255 127
1307 UPDATE t2 SET c2=0 WHERE c2=5;
1308 SELECT c1,c2 FROM t1 ORDER BY c1;
1309 c1 c2
1310 1 2
1311 4 5
1312 7 8
1313 10 11
1314 255 127
1315 UPDATE t2 SET c1=127,c2=255 WHERE c2=13;
1316 SELECT c1,c2 FROM t2 ORDER BY c1;
1317 c1 c2
1318 0 0
1319 4 0
1320 7 8
1321 10 11
1322 127 255
1323 UPDATE t2 SET c1=-128,c2=0 WHERE c2=2;
1324 SELECT c1,c2 FROM t2 ORDER BY c1;
1325 c1 c2
1326 0 0
1327 4 0
1328 7 8
1329 10 11
1330 127 255
1331 UPDATE t2 SET c1=0,c2=0 WHERE c2=5;
1332 SELECT c1,c2 FROM t2 ORDER BY c1;
1333 c1 c2
1334 0 0
1335 4 0
1336 7 8
1337 10 11
1338 127 255
1339 CREATE TABLE mt1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
1340 CREATE TABLE mt2 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
1341 CREATE TABLE mt3 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
1342 INSERT INTO mt1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
1343 INSERT INTO mt2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
1344 INSERT INTO mt3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
1345 UPDATE IGNORE mt1, mt2 ,mt3 SET mt1.c2 = 30, mt2.c2 = 40, mt3.c2=50 WHERE mt1.c1=mt2.c2 AND mt2.c1=mt3.c2;
1346 SELECT * FROM mt1;
1347 c1 c2
1348 1 30
1349 2 30
1350 3 3
1351 4 4
1352 5 5
1353 SELECT * FROM mt2;
1354 c1 c2
1355 11 40
1356 12 1
1357 13 40
1358 14 40
1359 15 6
1360 SELECT * FROM mt3;
1361 c1 c2
1362 21 50
1363 22 50
1364 23 50
1365 24 50
1366 25 15
1367 DROP TABLE mt1,mt2,mt3;
1368 TRUNCATE TABLE t1;
1369 TRUNCATE TABLE t2;
1370 ALTER TABLE t2 CHANGE c2 c2 TINYINT UNSIGNED NULL, ADD KEY(c2);
1371 INSERT INTO t1 VALUES(1,-1,1),(2,-2,2),(3,-3,3),(4,-4,4),(5,-5,5),(6,-6,6),(7,-7,7),(8,-8,8),(9,-9,9),(10,-10,10),(11,NULL,11);
1372 INSERT INTO t2 VALUES(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(-7,7,7),(-8,8,8),(-9,9,9),(10,10,10),(-11,NULL,11),(-12,12,12);
1373 UPDATE t1,t2 SET t1.c1=50,t1.c2=50,t2.c1=50,t2.c2=50 WHERE t2.c1=t1.c1;
1374 SELECT * FROM t1,t2 WHERE t1.c1=50 AND t1.c2=50 AND t2.c1=50 AND t2.c2=50;
1375 c1 c2 c3 c1 c2 c3
1376 50 50 10 50 50 10
1377 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
1378 c1 c2 c3 c1 c2 c3
1379 50 50 10 50 50 10
1380 ALTER TABLE t2 CHANGE c1 c1 TINYINT SIGNED NOT NULL, DROP PRIMARY KEY;
1381 ALTER TABLE t2 CHANGE c1 c1 TINYINT SIGNED NOT NULL, ADD KEY(c1);
1382 INSERT INTO t2 VALUES(-128,0,13),(127,255,14),(0,128,15),(127,127,16);
1383 UPDATE t2 SET c1=-128 WHERE c2 <> 0 ORDER BY c2 LIMIT 2;
1384 SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c2;
1385 c1 c2 c3
1386 -128 1 1
1387 -128 2 2
1388 -3 3 3
1389 -4 4 4
1390 -5 5 5
1391 -6 6 6
1392 -7 7 7
1393 -8 8 8
1394 -9 9 9
1395 -12 12 12
1396 50 50 10
1397 127 127 16
1398 0 128 15
1399 127 255 14
1400 UPDATE t2 SET c1=-128 WHERE c2 >= 0 ORDER BY c2 DESC LIMIT 2;
1401 SELECT * FROM t2 WHERE c2 >= 0 ORDER BY c2 DESC;
1402 c1 c2 c3
1403 -128 255 14
1404 -128 128 15
1405 127 127 16
1406 50 50 10
1407 -12 12 12
1408 -9 9 9
1409 -8 8 8
1410 -7 7 7
1411 -6 6 6
1412 -5 5 5
1413 -4 4 4
1414 -3 3 3
1415 -128 2 2
1416 -128 1 1
1417 -128 0 13
1418 UPDATE t2 SET c1=-128 WHERE c2 <= 3 ORDER BY c2 LIMIT 2;
1419 SELECT * FROM t2 WHERE c2 <= 3 ORDER BY c2;
1420 c1 c2 c3
1421 -128 0 13
1422 -128 1 1
1423 -128 2 2
1424 -3 3 3
1425 UPDATE t2 SET c1=-128 WHERE c2 <=> 4 ORDER BY c2 DESC LIMIT 2;
1426 SELECT * FROM t2 WHERE c2 <=> 4 ORDER BY c2;
1427 c1 c2 c3
1428 -128 4 4
1429 UPDATE t2 SET c1=-128 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2 LIMIT 2;
1430 SELECT * FROM t2 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2;
1431 c1 c2 c3
1432 -128 4 4
1433 -128 5 5
1434 -6 6 6
1435 -7 7 7
1436 UPDATE t2 SET c1=-128 WHERE c2 IN(8,9) ORDER BY c2 DESC LIMIT 2;
1437 SELECT * FROM t2 WHERE c2 IN(8,9) ORDER BY c2 DESC;
1438 c1 c2 c3
1439 -128 9 9
1440 -128 8 8
1441 UPDATE t2 SET c1=-128 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
1442 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
1443 c1 c2 c3
1444 -128 NULL 11
1445 UPDATE t2 SET c1=-128 WHERE c2>= 6 AND c2 < 9 ORDER BY c2 LIMIT 2;
1446 SELECT * FROM t2 WHERE c2>= 6 AND c2 < 9 ORDER BY c2;
1447 c1 c2 c3
1448 -128 6 6
1449 -128 7 7
1450 -128 8 8
1451 UPDATE t2 SET c1=-128 WHERE c1=-12 OR c2=1;
1452 SELECT * FROM t2 WHERE c1=-128;
1453 c1 c2 c3
1454 -128 0 13
1455 -128 1 1
1456 -128 12 12
1457 -128 128 15
1458 -128 2 2
1459 -128 255 14
1460 -128 4 4
1461 -128 5 5
1462 -128 6 6
1463 -128 7 7
1464 -128 8 8
1465 -128 9 9
1466 -128 NULL 11
1467 SELECT * FROM t2;
1468 c1 c2 c3
1469 -128 0 13
1470 -128 1 1
1471 -128 12 12
1472 -128 128 15
1473 -128 2 2
1474 -128 255 14
1475 -128 4 4
1476 -128 5 5
1477 -128 6 6
1478 -128 7 7
1479 -128 8 8
1480 -128 9 9
1481 -128 NULL 11
1482 -3 3 3
1483 127 127 16
1484 50 50 10
1485 SELECT c1 FROM t2;
1486 c1
1487 -128
1488 -128
1489 -128
1490 -128
1491 -128
1492 -128
1493 -128
1494 -128
1495 -128
1496 -128
1497 -128
1498 -128
1499 -128
1500 -3
1501 127
1502 50
1503 SELECT c1 FROM t2 ORDER BY c1 DESC;
1504 c1
1505 127
1506 50
1507 -3
1508 -128
1509 -128
1510 -128
1511 -128
1512 -128
1513 -128
1514 -128
1515 -128
1516 -128
1517 -128
1518 -128
1519 -128
1520 -128
1521 SELECT * FROM t2 WHERE c1=-128;
1522 c1 c2 c3
1523 -128 0 13
1524 -128 1 1
1525 -128 12 12
1526 -128 128 15
1527 -128 2 2
1528 -128 255 14
1529 -128 4 4
1530 -128 5 5
1531 -128 6 6
1532 -128 7 7
1533 -128 8 8
1534 -128 9 9
1535 -128 NULL 11
1536 SELECT * FROM t2 WHERE c1 <> -128 ORDER BY c1,c2;
1537 c1 c2 c3
1538 -3 3 3
1539 50 50 10
1540 127 127 16
1541 SELECT * FROM t2 WHERE c1 <> -128 ORDER BY c1,c2 DESC LIMIT 2;
1542 c1 c2 c3
1543 -3 3 3
1544 50 50 10
1545 SELECT * FROM t2 WHERE c1 > -128 ORDER BY c1,c2 DESC;
1546 c1 c2 c3
1547 -3 3 3
1548 50 50 10
1549 127 127 16
1550 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1,c2 LIMIT 2;
1551 c1 c2 c3
1552 -128 NULL 11
1553 -128 0 13
1554 SELECT * FROM t2 WHERE c1 < -128 ORDER BY c1,c2;
1555 c1 c2 c3
1556 SELECT * FROM t2 WHERE c1 <= -128 ORDER BY c1,c2 DESC LIMIT 2;
1557 c1 c2 c3
1558 -128 255 14
1559 -128 128 15
1560 SELECT * FROM t2 WHERE c1 <=> -128 ORDER BY c1,c2 DESC;
1561 c1 c2 c3
1562 -128 255 14
1563 -128 128 15
1564 -128 12 12
1565 -128 9 9
1566 -128 8 8
1567 -128 7 7
1568 -128 6 6
1569 -128 5 5
1570 -128 4 4
1571 -128 2 2
1572 -128 1 1
1573 -128 0 13
1574 -128 NULL 11
1575 SELECT * FROM t2 WHERE c1 <=> -128 ORDER BY c1,c2 LIMIT 2;
1576 c1 c2 c3
1577 -128 NULL 11
1578 -128 0 13
1579 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1,c2;
1580 c1 c2 c3
1581 -128 NULL 11
1582 -128 0 13
1583 -128 1 1
1584 -128 2 2
1585 -128 4 4
1586 -128 5 5
1587 -128 6 6
1588 -128 7 7
1589 -128 8 8
1590 -128 9 9
1591 -128 12 12
1592 -128 128 15
1593 -128 255 14
1594 -3 3 3
1595 50 50 10
1596 127 127 16
1597 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1,c2 DESC LIMIT 2;
1598 c1 c2 c3
1599 -128 255 14
1600 -128 128 15
1601 SELECT * FROM t2 WHERE c1 IN(-128,127) ORDER BY c1,c2 DESC;
1602 c1 c2 c3
1603 -128 255 14
1604 -128 128 15
1605 -128 12 12
1606 -128 9 9
1607 -128 8 8
1608 -128 7 7
1609 -128 6 6
1610 -128 5 5
1611 -128 4 4
1612 -128 2 2
1613 -128 1 1
1614 -128 0 13
1615 -128 NULL 11
1616 127 127 16
1617 SELECT * FROM t2 WHERE c1 IN(-128,127) ORDER BY c1,c2 LIMIT 2;
1618 c1 c2 c3
1619 -128 NULL 11
1620 -128 0 13
1621 SELECT * FROM t2 WHERE c1>=-128 AND c1 <= 127 ORDER BY c1,c2 DESC;
1622 c1 c2 c3
1623 -128 255 14
1624 -128 128 15
1625 -128 12 12
1626 -128 9 9
1627 -128 8 8
1628 -128 7 7
1629 -128 6 6
1630 -128 5 5
1631 -128 4 4
1632 -128 2 2
1633 -128 1 1
1634 -128 0 13
1635 -128 NULL 11
1636 -3 3 3
1637 50 50 10
1638 127 127 16
1639 SELECT * FROM t2 WHERE c1>=-128 AND c1 <= 127 ORDER BY c1,c2 LIMIT 2;
1640 c1 c2 c3
1641 -128 NULL 11
1642 -128 0 13
1643 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1;
1644 c1 c2 c3
1645 -128 NULL 11
1646 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
1647 c1 c2 c3
1648 -128 NULL 11
1649 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1650 c1 c2 c3
1651 -128 0 13
1652 -128 1 1
1653 -128 2 2
1654 -3 3 3
1655 -128 4 4
1656 -128 5 5
1657 -128 6 6
1658 -128 7 7
1659 -128 8 8
1660 -128 9 9
1661 -128 12 12
1662 50 50 10
1663 127 127 16
1664 -128 128 15
1665 -128 255 14
1666 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 LIMIT 2;
1667 c1 c2 c3
1668 -128 0 13
1669 -128 1 1
1670 SELECT * FROM t2 WHERE c1=-128 OR c2=3;
1671 c1 c2 c3
1672 -128 0 13
1673 -128 1 1
1674 -128 12 12
1675 -128 128 15
1676 -128 2 2
1677 -128 255 14
1678 -128 4 4
1679 -128 5 5
1680 -128 6 6
1681 -128 7 7
1682 -128 8 8
1683 -128 9 9
1684 -128 NULL 11
1685 -3 3 3
1686 CREATE TABLE mt1 (a tinyint not null primary key, b tinyint not null, key (b));
1687 CREATE TABLE mt2 (a tinyint not null primary key, b tinyint not null, key (b));
1688 INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1689 INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1690 update mt1,mt2 set mt1.a=mt1.a+100;
1691 select * from mt1;
1692 a b
1693 101 1
1694 102 2
1695 103 3
1696 104 4
1697 105 5
1698 106 6
1699 107 7
1700 108 8
1701 109 9
1702 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
1703 update mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
1704 Warnings:
1705 Warning 1264 Out of range value for column 'a' at row 1
1706 SET SQL_MODE=DEFAULT;
1707 select * from mt1;
1708 a b
1709 102 2
1710 103 3
1711 104 4
1712 105 5
1713 106 6
1714 107 7
1715 108 8
1716 109 9
1717 127 1
1718 update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
1719 select * from mt1;
1720 a b
1721 102 12
1722 103 3
1723 104 4
1724 105 5
1725 106 6
1726 107 7
1727 108 8
1728 109 9
1729 127 1
1730 update mt1 straight_join mt2 set mt1.b=mt1.b+2,mt2.b=mt1.b+10 where mt1.b between 3 and 5 and mt2.a=mt1.a-100;
1731 select * from mt1;
1732 a b
1733 102 12
1734 103 5
1735 104 6
1736 105 7
1737 106 6
1738 107 7
1739 108 8
1740 109 9
1741 127 1
1742 update mt1,mt2 set mt1.b=mt2.b, mt1.a=mt2.a where mt1.a=mt2.a and not exists (select * from mt2 where mt2.a > 10);
1743 drop table mt1,mt2;
1744 TRUNCATE TABLE t2;
1745 INSERT INTO t2 VALUES(-128,0,1),(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(0,0,7),(1,1,8),(127,255,9);
1746 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
1747 c1 c2 c3
1748 -3 3 3
1749 127 255 9
1750 DELETE FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1 LIMIT 2;
1751 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
1752 c1 c2 c3
1753 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
1754 c1 c2 c3
1755 -128 0 1
1756 -6 6 6
1757 -5 5 5
1758 -4 4 4
1759 -2 2 2
1760 -1 1 1
1761 0 0 7
1762 1 1 8
1763 DELETE FROM t2 WHERE c1 >= -128 ORDER BY c1 LIMIT 2;
1764 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
1765 c1 c2 c3
1766 -5 5 5
1767 -4 4 4
1768 -2 2 2
1769 -1 1 1
1770 0 0 7
1771 1 1 8
1772 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
1773 c1 c2 c3
1774 1 1 8
1775 0 0 7
1776 -1 1 1
1777 -2 2 2
1778 -4 4 4
1779 -5 5 5
1780 DELETE FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC LIMIT 2;
1781 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
1782 c1 c2 c3
1783 -1 1 1
1784 -2 2 2
1785 -4 4 4
1786 -5 5 5
1787 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
1788 c1 c2 c3
1789 -5 5 5
1790 -4 4 4
1791 -2 2 2
1792 -1 1 1
1793 DELETE FROM t2 WHERE c1 < 0 ORDER BY c1 LIMIT 2;
1794 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
1795 c1 c2 c3
1796 -2 2 2
1797 -1 1 1
1798 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
1799 c1 c2 c3
1800 -1 1 1
1801 -2 2 2
1802 DELETE FROM t2 WHERE c1 > -3 ORDER BY c1 DESC LIMIT 2;
1803 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
1804 c1 c2 c3
1805 INSERT INTO t2 VALUES(-128,0,1),(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(0,0,7),(1,1,8),(2,NULL,9),(3,NULL,10),(127,255,11);
1806 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
1807 c1 c2 c3
1808 -128 0 1
1809 -6 6 6
1810 -5 5 5
1811 -4 4 4
1812 -3 3 3
1813 -2 2 2
1814 -1 1 1
1815 0 0 7
1816 1 1 8
1817 2 NULL 9
1818 3 NULL 10
1819 127 255 11
1820 DELETE FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1 LIMIT 2;
1821 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
1822 c1 c2 c3
1823 -5 5 5
1824 -4 4 4
1825 -3 3 3
1826 -2 2 2
1827 -1 1 1
1828 0 0 7
1829 1 1 8
1830 2 NULL 9
1831 3 NULL 10
1832 127 255 11
1833 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1834 c1 c2 c3
1835 0 0 7
1836 -2 2 2
1837 DELETE FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1838 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1839 c1 c2 c3
1840 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
1841 c1 c2 c3
1842 -1 1 1
1843 1 1 8
1844 DELETE FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1 LIMIT 2;
1845 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
1846 c1 c2 c3
1847 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
1848 c1 c2 c3
1849 -5 5 5
1850 -4 4 4
1851 -3 3 3
1852 127 255 11
1853 DELETE FROM t2 WHERE c2 IS NOT NULL ORDER BY c1 LIMIT 2;
1854 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
1855 c1 c2 c3
1856 -3 3 3
1857 127 255 11
1858 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1859 c1 c2 c3
1860 3 NULL 10
1861 2 NULL 9
1862 DELETE FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
1863 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1864 c1 c2 c3
1865 TRUNCATE TABLE t1;
1866 TRUNCATE TABLE t2;
1867 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
1868 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
1869 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1870 c1 c2 c3 c1 c2 c3
1871 1 127 3 127 1 2
1872 1 127 3 127 255 1
1873 2 -128 4 -128 0 3
1874 2 -128 4 -128 2 4
1875 254 127 1 127 1 2
1876 254 127 1 127 255 1
1877 255 -128 2 -128 0 3
1878 255 -128 2 -128 2 4
1879 255 -128 2 127 255 1
1880 3 NULL 5 -1 NULL 5
1881 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1882 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1883 c1 c2 c3 c1 c2 c3
1884 TRUNCATE TABLE t1;
1885 TRUNCATE TABLE t2;
1886 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
1887 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
1888 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1889 c1 c2 c3 c1 c2 c3
1890 1 127 3 127 1 2
1891 1 127 3 127 255 1
1892 2 -128 4 -128 0 3
1893 2 -128 4 -128 2 4
1894 254 127 1 127 1 2
1895 254 127 1 127 255 1
1896 255 -128 2 -128 0 3
1897 255 -128 2 -128 2 4
1898 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
1899 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1900 c1 c2 c3 c1 c2 c3
1901 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
1902 ERROR HY000: You can't specify target table 't1' for update in FROM clause
1903 CREATE TABLE t3(c1 TINYINT UNSIGNED NOT NULL PRIMARY KEY, c2 TINYINT SIGNED NULL, c3 INT);
1904 CREATE TABLE t4(c1 TINYINT UNSIGNED, c2 INT);
1905 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
1906 INSERT INTO t4 VALUES(200,1),(150,3);
1907 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1908 c1 c2 c3 c1 c2
1909 200 126 1 200 1
1910 DELETE t3.*,t4.* FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1911 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1912 c1 c2 c3 c1 c2
1913 DROP TABLE t1,t2,t3,t4;
1914 CREATE TABLE t1(c1 SMALLINT UNSIGNED NOT NULL, c2 SMALLINT SIGNED NULL, c3 INT, INDEX idx2(c2));
1915 INSERT INTO t1 VALUES(0,-128,0),(1,1,1),(2,2,2),(0,NULL,3),(101,-101,4),(102,-102,5),(103,-103,6),(104,-104,7),(105,-105,8);
1916 SELECT * FROM t1;
1917 c1 c2 c3
1918 0 -128 0
1919 0 NULL 3
1920 1 1 1
1921 101 -101 4
1922 102 -102 5
1923 103 -103 6
1924 104 -104 7
1925 105 -105 8
1926 2 2 2
1927 UPDATE t1 SET c1=110 WHERE c2 >-128 ORDER BY c2 LIMIT 2;
1928 SELECT * FROM t1 WHERE c1=110;
1929 c1 c2 c3
1930 110 -104 7
1931 110 -105 8
1932 UPDATE t1 SET c1=c1+1,c2=NULL WHERE c1=101;
1933 SELECT * FROM t1 WHERE c1=102 AND c2 IS NULL;
1934 c1 c2 c3
1935 102 NULL 4
1936 UPDATE t1 SET c1=120 WHERE c2 IS NULL;
1937 SELECT c1,c2 FROM t1 WHERE c1=120;
1938 c1 c2
1939 120 NULL
1940 120 NULL
1941 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
1942 UPDATE t1 SET c1=-120 WHERE c2=-102;
1943 Warnings:
1944 Warning 1264 Out of range value for column 'c1' at row 1
1945 SET SQL_MODE=DEFAULT;
1946 SELECT c1,c2 FROM t1 WHERE c2=-102;
1947 c1 c2
1948 0 -102
1949 UPDATE t1 SET c1=0,c2=-32768 WHERE c1=103 AND c2=-103;
1950 SELECT * FROM t1 WHERE c1=0 AND c2=-32768;
1951 c1 c2 c3
1952 0 -32768 6
1953 UPDATE t1 SET c1=65535,c2=32767 WHERE c1=104 OR c2=105;
1954 SELECT * FROM t1 WHERE c1=65535 AND c2=32767;
1955 c1 c2 c3
1956 UPDATE t1 SET c2=0 WHERE c1 IN (1,2);
1957 SELECT * FROM t1 WHERE c2=0;
1958 c1 c2 c3
1959 1 0 1
1960 2 0 2
1961 INSERT INTO t1 VALUES(106,-106,9),(107,-107,10),(108,-108,11),(109,-109,12),(255,127,13);
1962 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
1963 UPDATE t1 SET c1=65536,c2=32768 WHERE c2 BETWEEN -108 AND -106;
1964 Warnings:
1965 Warning 1264 Out of range value for column 'c1' at row 1
1966 Warning 1264 Out of range value for column 'c2' at row 1
1967 Warning 1264 Out of range value for column 'c1' at row 2
1968 Warning 1264 Out of range value for column 'c2' at row 2
1969 Warning 1264 Out of range value for column 'c1' at row 3
1970 Warning 1264 Out of range value for column 'c2' at row 3
1971 SELECT COUNT(*) FROM t1 WHERE c1=65536 AND c2=32768 /* no rows */;
1972 COUNT(*)
1973 0
1974 SELECT * FROM t1 WHERE c1=65535 AND c2=32767;
1975 c1 c2 c3
1976 65535 32767 10
1977 65535 32767 11
1978 65535 32767 9
1979 UPDATE t1 SET c2=-32769 WHERE c1=109 ORDER BY c1;
1980 Warnings:
1981 Warning 1264 Out of range value for column 'c2' at row 13
1982 SELECT c1,c2 FROM t1 WHERE c1=109;
1983 c1 c2
1984 109 -32768
1985 SET SQL_MODE=DEFAULT;
1986 INSERT INTO t1 VALUES(110,-110,14),(111,-111,15);
1987 SET SQL_MODE=STRICT_ALL_TABLES;
1988 Warnings:
1989 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
1990 UPDATE t1 SET c1=NULL WHERE c2=-110;
1991 ERROR 23000: Column 'c1' cannot be null
1992 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-110 /* updates to default value 0 */;
1993 Warnings:
1994 Warning 1048 Column 'c1' cannot be null
1995 SELECT c1,c2 FROM t1 WHERE c2=-110;
1996 c1 c2
1997 0 -110
1998 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
1999 UPDATE t1 SET c1=NULL WHERE c2=-111 /* updates to default value 0 */;
2000 Warnings:
2001 Warning 1048 Column 'c1' cannot be null
2002 SET SQL_MODE=DEFAULT;
2003 SELECT * FROM t1 WHERE c1=0;
2004 c1 c2 c3
2005 0 -102 5
2006 0 -110 14
2007 0 -111 15
2008 0 -128 0
2009 0 -32768 6
2010 SELECT * FROM t1 WHERE c2>=-101 AND c1<=101 ORDER BY c2;
2011 c1 c2 c3
2012 1 0 1
2013 2 0 2
2014 UPDATE t1 SET c1=c1+1,c2=c2+1 WHERE c2>=-101 AND c1<=101 ORDER BY c2 LIMIT 2;
2015 SELECT * FROM t1;
2016 c1 c2 c3
2017 0 -102 5
2018 0 -110 14
2019 0 -111 15
2020 0 -128 0
2021 0 -32768 6
2022 109 -32768 12
2023 110 -104 7
2024 110 -105 8
2025 120 NULL 3
2026 120 NULL 4
2027 2 1 1
2028 255 127 13
2029 3 1 2
2030 65535 32767 10
2031 65535 32767 11
2032 65535 32767 9
2033 CREATE TABLE t2(c1 SMALLINT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 SMALLINT UNSIGNED NULL, c3 INT);
2034 INSERT INTO t2(c1) VALUES(1),(2),(3),(4),(5);
2035 UPDATE t2 SET c1=1 WHERE c1=3;
2036 ERROR 23000: Duplicate entry '1' for key 't2.PRIMARY'
2037 UPDATE IGNORE t2 SET c1=1 WHERE c1>=3;
2038 Warnings:
2039 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
2040 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
2041 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
2042 SELECT c1 FROM t2;
2043 c1
2044 1
2045 2
2046 3
2047 4
2048 5
2049 TRUNCATE TABLE t1;
2050 ALTER TABLE t1 CHANGE c1 c1 SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(c1);
2051 INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
2052 TRUNCATE TABLE t2;
2053 INSERT INTO t2 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
2054 INSERT INTO t1(c2,c3) VALUES(13,14);
2055 SELECT c1 FROM t1 WHERE c2=13;
2056 c1
2057 11
2058 INSERT INTO t2(c2,c3) VALUES(13,14);
2059 SELECT c1 FROM t2 WHERE c2=13;
2060 c1
2061 11
2062 UPDATE t1 SET c1=65535,c2=32767 WHERE c2=13;
2063 SELECT c1,c2 FROM t1 ORDER BY c1;
2064 c1 c2
2065 1 2
2066 4 5
2067 7 8
2068 10 11
2069 65535 32767
2070 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
2071 UPDATE t2 SET c1=0,c2=-32768 WHERE c2=2;
2072 Warnings:
2073 Warning 1264 Out of range value for column 'c2' at row 1
2074 SELECT c1,c2 FROM t1 ORDER BY c1;
2075 c1 c2
2076 1 2
2077 4 5
2078 7 8
2079 10 11
2080 65535 32767
2081 UPDATE t2 SET c2=0 WHERE c2=5;
2082 SELECT c1,c2 FROM t1 ORDER BY c1;
2083 c1 c2
2084 1 2
2085 4 5
2086 7 8
2087 10 11
2088 65535 32767
2089 UPDATE t2 SET c1=32767,c2=65535 WHERE c2=13;
2090 SELECT c1,c2 FROM t2 ORDER BY c1;
2091 c1 c2
2092 0 0
2093 4 0
2094 7 8
2095 10 11
2096 32767 65535
2097 UPDATE t2 SET c1=-32768,c2=0 WHERE c2=2;
2098 SELECT c1,c2 FROM t2 ORDER BY c1;
2099 c1 c2
2100 0 0
2101 4 0
2102 7 8
2103 10 11
2104 32767 65535
2105 UPDATE t2 SET c1=0,c2=0 WHERE c2=5;
2106 SELECT c1,c2 FROM t2 ORDER BY c1;
2107 c1 c2
2108 0 0
2109 4 0
2110 7 8
2111 10 11
2112 32767 65535
2113 SET SQL_MODE=DEFAULT;
2114 CREATE TABLE mt1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
2115 CREATE TABLE mt2 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
2116 CREATE TABLE mt3 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
2117 INSERT INTO mt1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
2118 INSERT INTO mt2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
2119 INSERT INTO mt3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
2120 UPDATE IGNORE mt1, mt2 ,mt3 SET mt1.c2 = 30, mt2.c2 = 40, mt3.c2=50 WHERE mt1.c1=mt2.c2 AND mt2.c1=mt3.c2;
2121 SELECT * FROM mt1;
2122 c1 c2
2123 1 30
2124 2 30
2125 3 3
2126 4 4
2127 5 5
2128 SELECT * FROM mt2;
2129 c1 c2
2130 11 40
2131 12 1
2132 13 40
2133 14 40
2134 15 6
2135 SELECT * FROM mt3;
2136 c1 c2
2137 21 50
2138 22 50
2139 23 50
2140 24 50
2141 25 15
2142 DROP TABLE mt1,mt2,mt3;
2143 TRUNCATE TABLE t1;
2144 TRUNCATE TABLE t2;
2145 ALTER TABLE t2 CHANGE c2 c2 SMALLINT UNSIGNED NULL, ADD KEY(c2);
2146 INSERT INTO t1 VALUES(1,-1,1),(2,-2,2),(3,-3,3),(4,-4,4),(5,-5,5),(6,-6,6),(7,-7,7),(8,-8,8),(9,-9,9),(10,-10,10),(11,NULL,11);
2147 INSERT INTO t2 VALUES(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(-7,7,7),(-8,8,8),(-9,9,9),(10,10,10),(-11,NULL,11),(-12,12,12);
2148 UPDATE t1,t2 SET t1.c1=50,t1.c2=50,t2.c1=50,t2.c2=50 WHERE t2.c1=t1.c1;
2149 SELECT * FROM t1,t2 WHERE t1.c1=50 AND t1.c2=50 AND t2.c1=50 AND t2.c2=50;
2150 c1 c2 c3 c1 c2 c3
2151 50 50 10 50 50 10
2152 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
2153 c1 c2 c3 c1 c2 c3
2154 50 50 10 50 50 10
2155 ALTER TABLE t2 CHANGE c1 c1 SMALLINT SIGNED NOT NULL, DROP PRIMARY KEY;
2156 ALTER TABLE t2 CHANGE c1 c1 SMALLINT SIGNED NOT NULL, ADD KEY(c1);
2157 INSERT INTO t2 VALUES(-32768,0,12),(0,255,13),(32767,65535,14);
2158 UPDATE t2 SET c2=65535 WHERE c1 <> 0 ORDER BY c1 LIMIT 2;
2159 SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c1;
2160 c1 c2 c3
2161 -32768 65535 12
2162 -12 65535 12
2163 -9 9 9
2164 -8 8 8
2165 -7 7 7
2166 -6 6 6
2167 -5 5 5
2168 -4 4 4
2169 -3 3 3
2170 -2 2 2
2171 -1 1 1
2172 0 255 13
2173 50 50 10
2174 32767 65535 14
2175 UPDATE t2 SET c2=65535 WHERE c1 >= 0 ORDER BY c1 DESC LIMIT 2;
2176 SELECT * FROM t2 WHERE c1 >= 0 ORDER BY c1 DESC;
2177 c1 c2 c3
2178 32767 65535 14
2179 50 65535 10
2180 0 255 13
2181 UPDATE t2 SET c2=65535 WHERE c1 <= -9 ORDER BY c1 LIMIT 2;
2182 SELECT * FROM t2 WHERE c1 <= -9 ORDER BY c1;
2183 c1 c2 c3
2184 -32768 65535 12
2185 -12 65535 12
2186 -11 NULL 11
2187 -9 9 9
2188 UPDATE t2 SET c2=65535 WHERE c1 <=> -3 ORDER BY c1 DESC LIMIT 2;
2189 SELECT * FROM t2 WHERE c1 <=> -3 ORDER BY c1;
2190 c1 c2 c3
2191 -3 65535 3
2192 UPDATE t2 SET c2=65535 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1 LIMIT 2;
2193 SELECT * FROM t2 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1;
2194 c1 c2 c3
2195 -6 65535 6
2196 -5 65535 5
2197 -4 4 4
2198 UPDATE t2 SET c2=65535 WHERE c1 IN(-7,-8) ORDER BY c1 DESC LIMIT 2;
2199 SELECT * FROM t2 WHERE c1 IN(-7,-8) ORDER BY c1 DESC;
2200 c1 c2 c3
2201 -7 65535 7
2202 -8 65535 8
2203 UPDATE t2 SET c2=65535 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
2204 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
2205 c1 c2 c3
2206 UPDATE t2 SET c2=65535 WHERE c1>= -32768 AND c1 < -9 ORDER BY c1 LIMIT 2;
2207 SELECT * FROM t2 WHERE c1>= -32768 AND c1 < -9 ORDER BY c1;
2208 c1 c2 c3
2209 -32768 65535 12
2210 -12 65535 12
2211 -11 65535 11
2212 UPDATE t2 SET c2=65535 WHERE c1=-2 OR c2=1;
2213 SELECT * FROM t2 WHERE c2=65535;
2214 c1 c2 c3
2215 -1 65535 1
2216 -11 65535 11
2217 -12 65535 12
2218 -2 65535 2
2219 -3 65535 3
2220 -32768 65535 12
2221 -5 65535 5
2222 -6 65535 6
2223 -7 65535 7
2224 -8 65535 8
2225 32767 65535 14
2226 50 65535 10
2227 SELECT * FROM t2;
2228 c1 c2 c3
2229 -1 65535 1
2230 -11 65535 11
2231 -12 65535 12
2232 -2 65535 2
2233 -3 65535 3
2234 -32768 65535 12
2235 -4 4 4
2236 -5 65535 5
2237 -6 65535 6
2238 -7 65535 7
2239 -8 65535 8
2240 -9 9 9
2241 0 255 13
2242 32767 65535 14
2243 50 65535 10
2244 SELECT c2 FROM t2;
2245 c2
2246 255
2247 4
2248 65535
2249 65535
2250 65535
2251 65535
2252 65535
2253 65535
2254 65535
2255 65535
2256 65535
2257 65535
2258 65535
2259 65535
2260 9
2261 SELECT c2 FROM t2 ORDER BY c2 DESC;
2262 c2
2263 65535
2264 65535
2265 65535
2266 65535
2267 65535
2268 65535
2269 65535
2270 65535
2271 65535
2272 65535
2273 65535
2274 65535
2275 255
2276 9
2277 4
2278 SELECT * FROM t2 WHERE c2=65535;
2279 c1 c2 c3
2280 -1 65535 1
2281 -11 65535 11
2282 -12 65535 12
2283 -2 65535 2
2284 -3 65535 3
2285 -32768 65535 12
2286 -5 65535 5
2287 -6 65535 6
2288 -7 65535 7
2289 -8 65535 8
2290 32767 65535 14
2291 50 65535 10
2292 SELECT * FROM t2 WHERE c2 <> 65535 ORDER BY c2,c1;
2293 c1 c2 c3
2294 -4 4 4
2295 -9 9 9
2296 0 255 13
2297 SELECT * FROM t2 WHERE c2 <> 65535 ORDER BY c2,c1 DESC LIMIT 2;
2298 c1 c2 c3
2299 -4 4 4
2300 -9 9 9
2301 SELECT * FROM t2 WHERE c2 > 65535 ORDER BY c2,c1 DESC;
2302 c1 c2 c3
2303 SELECT * FROM t2 WHERE c2 >= 65535 ORDER BY c2,c1 LIMIT 2;
2304 c1 c2 c3
2305 -32768 65535 12
2306 -12 65535 12
2307 SELECT * FROM t2 WHERE c2 < 65535 ORDER BY c2,c1;
2308 c1 c2 c3
2309 -4 4 4
2310 -9 9 9
2311 0 255 13
2312 SELECT * FROM t2 WHERE c2 <= 65535 ORDER BY c2,c1 DESC LIMIT 2;
2313 c1 c2 c3
2314 -4 4 4
2315 -9 9 9
2316 SELECT * FROM t2 WHERE c2 <=> 65535 ORDER BY c2,c1 DESC;
2317 c1 c2 c3
2318 32767 65535 14
2319 50 65535 10
2320 -1 65535 1
2321 -2 65535 2
2322 -3 65535 3
2323 -5 65535 5
2324 -6 65535 6
2325 -7 65535 7
2326 -8 65535 8
2327 -11 65535 11
2328 -12 65535 12
2329 -32768 65535 12
2330 SELECT * FROM t2 WHERE c2 <=> 65535 ORDER BY c2,c1 LIMIT 2;
2331 c1 c2 c3
2332 -32768 65535 12
2333 -12 65535 12
2334 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 65535 ORDER BY c2,c1;
2335 c1 c2 c3
2336 -4 4 4
2337 -9 9 9
2338 0 255 13
2339 -32768 65535 12
2340 -12 65535 12
2341 -11 65535 11
2342 -8 65535 8
2343 -7 65535 7
2344 -6 65535 6
2345 -5 65535 5
2346 -3 65535 3
2347 -2 65535 2
2348 -1 65535 1
2349 50 65535 10
2350 32767 65535 14
2351 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 65535 ORDER BY c2,c1 DESC LIMIT 2;
2352 c1 c2 c3
2353 -4 4 4
2354 -9 9 9
2355 SELECT * FROM t2 WHERE c2 IN(0,65535) ORDER BY c2,c1 DESC;
2356 c1 c2 c3
2357 32767 65535 14
2358 50 65535 10
2359 -1 65535 1
2360 -2 65535 2
2361 -3 65535 3
2362 -5 65535 5
2363 -6 65535 6
2364 -7 65535 7
2365 -8 65535 8
2366 -11 65535 11
2367 -12 65535 12
2368 -32768 65535 12
2369 SELECT * FROM t2 WHERE c2 IN(0,65535) ORDER BY c2,c1 LIMIT 2;
2370 c1 c2 c3
2371 -32768 65535 12
2372 -12 65535 12
2373 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 65535 ORDER BY c2,c1 DESC;
2374 c1 c2 c3
2375 -4 4 4
2376 -9 9 9
2377 0 255 13
2378 32767 65535 14
2379 50 65535 10
2380 -1 65535 1
2381 -2 65535 2
2382 -3 65535 3
2383 -5 65535 5
2384 -6 65535 6
2385 -7 65535 7
2386 -8 65535 8
2387 -11 65535 11
2388 -12 65535 12
2389 -32768 65535 12
2390 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 65535 ORDER BY c2,c1 LIMIT 2;
2391 c1 c2 c3
2392 -4 4 4
2393 -9 9 9
2394 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1;
2395 c1 c2 c3
2396 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
2397 c1 c2 c3
2398 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
2399 c1 c2 c3
2400 -4 4 4
2401 -9 9 9
2402 0 255 13
2403 32767 65535 14
2404 50 65535 10
2405 -1 65535 1
2406 -2 65535 2
2407 -3 65535 3
2408 -5 65535 5
2409 -6 65535 6
2410 -7 65535 7
2411 -8 65535 8
2412 -11 65535 11
2413 -12 65535 12
2414 -32768 65535 12
2415 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 LIMIT 2;
2416 c1 c2 c3
2417 -4 4 4
2418 -9 9 9
2419 SELECT * FROM t2 WHERE c2=65535 OR c1=-4;
2420 c1 c2 c3
2421 -1 65535 1
2422 -11 65535 11
2423 -12 65535 12
2424 -2 65535 2
2425 -3 65535 3
2426 -32768 65535 12
2427 -4 4 4
2428 -5 65535 5
2429 -6 65535 6
2430 -7 65535 7
2431 -8 65535 8
2432 32767 65535 14
2433 50 65535 10
2434 CREATE TABLE mt1 (a smallint not null primary key, b smallint not null, key (b));
2435 CREATE TABLE mt2 (a smallint not null primary key, b smallint not null, key (b));
2436 INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
2437 INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
2438 update mt1,mt2 set mt1.a=mt1.a+100;
2439 select * from mt1;
2440 a b
2441 101 1
2442 102 2
2443 103 3
2444 104 4
2445 105 5
2446 106 6
2447 107 7
2448 108 8
2449 109 9
2450 update mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
2451 select * from mt1;
2452 a b
2453 102 2
2454 103 3
2455 104 4
2456 105 5
2457 106 6
2458 107 7
2459 108 8
2460 109 9
2461 201 1
2462 update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
2463 select * from mt1;
2464 a b
2465 102 12
2466 103 3
2467 104 4
2468 105 5
2469 106 6
2470 107 7
2471 108 8
2472 109 9
2473 201 1
2474 update mt1 straight_join mt2 set mt1.b=mt1.b+2,mt2.b=mt1.b+10 where mt1.b between 3 and 5 and mt2.a=mt1.a-100;
2475 select * from mt1;
2476 a b
2477 102 12
2478 103 5
2479 104 6
2480 105 7
2481 106 6
2482 107 7
2483 108 8
2484 109 9
2485 201 1
2486 update mt1,mt2 set mt1.b=mt2.b, mt1.a=mt2.a where mt1.a=mt2.a and not exists (select * from mt2 where mt2.a > 10);
2487 drop table mt1,mt2;
2488 TRUNCATE TABLE t2;
2489 INSERT INTO t2 VALUES(-128,0,1),(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(0,0,7),(1,1,8),(127,255,9);
2490 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
2491 c1 c2 c3
2492 -3 3 3
2493 127 255 9
2494 DELETE FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1 LIMIT 2;
2495 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
2496 c1 c2 c3
2497 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
2498 c1 c2 c3
2499 -128 0 1
2500 -6 6 6
2501 -5 5 5
2502 -4 4 4
2503 -2 2 2
2504 -1 1 1
2505 0 0 7
2506 1 1 8
2507 DELETE FROM t2 WHERE c1 >= -128 ORDER BY c1 LIMIT 2;
2508 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
2509 c1 c2 c3
2510 -5 5 5
2511 -4 4 4
2512 -2 2 2
2513 -1 1 1
2514 0 0 7
2515 1 1 8
2516 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
2517 c1 c2 c3
2518 1 1 8
2519 0 0 7
2520 -1 1 1
2521 -2 2 2
2522 -4 4 4
2523 -5 5 5
2524 DELETE FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC LIMIT 2;
2525 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
2526 c1 c2 c3
2527 -1 1 1
2528 -2 2 2
2529 -4 4 4
2530 -5 5 5
2531 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
2532 c1 c2 c3
2533 -5 5 5
2534 -4 4 4
2535 -2 2 2
2536 -1 1 1
2537 DELETE FROM t2 WHERE c1 < 0 ORDER BY c1 LIMIT 2;
2538 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
2539 c1 c2 c3
2540 -2 2 2
2541 -1 1 1
2542 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
2543 c1 c2 c3
2544 -1 1 1
2545 -2 2 2
2546 DELETE FROM t2 WHERE c1 > -3 ORDER BY c1 DESC LIMIT 2;
2547 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
2548 c1 c2 c3
2549 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
2550 c1 c2 c3
2551 DELETE FROM t2 WHERE c1=-3 OR c2=2;
2552 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
2553 c1 c2 c3
2554 INSERT INTO t2 VALUES(-128,0,1),(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(0,0,7),(1,1,8),(2,NULL,9),(3,NULL,10),(127,255,11);
2555 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
2556 c1 c2 c3
2557 -128 0 1
2558 -6 6 6
2559 -5 5 5
2560 -4 4 4
2561 -3 3 3
2562 -2 2 2
2563 -1 1 1
2564 0 0 7
2565 1 1 8
2566 2 NULL 9
2567 3 NULL 10
2568 127 255 11
2569 DELETE FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1 LIMIT 2;
2570 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
2571 c1 c2 c3
2572 -5 5 5
2573 -4 4 4
2574 -3 3 3
2575 -2 2 2
2576 -1 1 1
2577 0 0 7
2578 1 1 8
2579 2 NULL 9
2580 3 NULL 10
2581 127 255 11
2582 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
2583 c1 c2 c3
2584 0 0 7
2585 -2 2 2
2586 DELETE FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
2587 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
2588 c1 c2 c3
2589 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
2590 c1 c2 c3
2591 -1 1 1
2592 1 1 8
2593 DELETE FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1 LIMIT 2;
2594 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
2595 c1 c2 c3
2596 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
2597 c1 c2 c3
2598 -5 5 5
2599 -4 4 4
2600 -3 3 3
2601 127 255 11
2602 DELETE FROM t2 WHERE c2 IS NOT NULL ORDER BY c1 LIMIT 2;
2603 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
2604 c1 c2 c3
2605 -3 3 3
2606 127 255 11
2607 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
2608 c1 c2 c3
2609 3 NULL 10
2610 2 NULL 9
2611 DELETE FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
2612 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
2613 c1 c2 c3
2614 TRUNCATE TABLE t1;
2615 TRUNCATE TABLE t2;
2616 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
2617 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
2618 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
2619 c1 c2 c3 c1 c2 c3
2620 1 127 3 127 1 2
2621 1 127 3 127 255 1
2622 2 -128 4 -128 0 3
2623 2 -128 4 -128 2 4
2624 254 127 1 127 1 2
2625 254 127 1 127 255 1
2626 255 -128 2 -128 0 3
2627 255 -128 2 -128 2 4
2628 255 -128 2 127 255 1
2629 3 NULL 5 -1 NULL 5
2630 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
2631 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
2632 c1 c2 c3 c1 c2 c3
2633 TRUNCATE TABLE t1;
2634 TRUNCATE TABLE t2;
2635 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
2636 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
2637 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
2638 c1 c2 c3 c1 c2 c3
2639 1 127 3 127 1 2
2640 1 127 3 127 255 1
2641 2 -128 4 -128 0 3
2642 2 -128 4 -128 2 4
2643 254 127 1 127 1 2
2644 254 127 1 127 255 1
2645 255 -128 2 -128 0 3
2646 255 -128 2 -128 2 4
2647 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
2648 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
2649 c1 c2 c3 c1 c2 c3
2650 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
2651 ERROR HY000: You can't specify target table 't1' for update in FROM clause
2652 CREATE TABLE t3(c1 SMALLINT UNSIGNED NOT NULL PRIMARY KEY, c2 SMALLINT SIGNED NULL, c3 INT);
2653 CREATE TABLE t4(c1 SMALLINT UNSIGNED, c2 INT);
2654 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
2655 INSERT INTO t4 VALUES(200,1),(150,3);
2656 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
2657 c1 c2 c3 c1 c2
2658 200 126 1 200 1
2659 DELETE t3.*,t4.* FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
2660 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
2661 c1 c2 c3 c1 c2
2662 DROP TABLE t1,t2,t3,t4;
2663 CREATE TABLE t1(c1 MEDIUMINT UNSIGNED NOT NULL, c2 MEDIUMINT SIGNED NULL, c3 INT, INDEX idx2(c2));
2664 INSERT INTO t1 VALUES(0,-128,0),(1,1,1),(2,2,2),(0,NULL,3),(101,-101,4),(102,-102,5),(103,-103,6),(104,-104,7),(105,-105,8);
2665 SELECT * FROM t1;
2666 c1 c2 c3
2667 0 -128 0
2668 0 NULL 3
2669 1 1 1
2670 101 -101 4
2671 102 -102 5
2672 103 -103 6
2673 104 -104 7
2674 105 -105 8
2675 2 2 2
2676 UPDATE t1 SET c1=110 WHERE c2 >-128 ORDER BY c2 LIMIT 2;
2677 SELECT * FROM t1 WHERE c1=110;
2678 c1 c2 c3
2679 110 -104 7
2680 110 -105 8
2681 UPDATE t1 SET c1=c1+1,c2=NULL WHERE c1=101;
2682 SELECT * FROM t1 WHERE c1=102 AND c2 IS NULL;
2683 c1 c2 c3
2684 102 NULL 4
2685 UPDATE t1 SET c1=120 WHERE c2 IS NULL;
2686 SELECT c1,c2 FROM t1 WHERE c1=120;
2687 c1 c2
2688 120 NULL
2689 120 NULL
2690 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
2691 UPDATE t1 SET c1=-120 WHERE c2=-102;
2692 Warnings:
2693 Warning 1264 Out of range value for column 'c1' at row 1
2694 SELECT c1,c2 FROM t1 WHERE c2=-102;
2695 c1 c2
2696 0 -102
2697 UPDATE t1 SET c1=0,c2=-8388608 WHERE c1=103 AND c2=-103;
2698 SELECT * FROM t1 WHERE c1=0 AND c2=-8388608;
2699 c1 c2 c3
2700 0 -8388608 6
2701 UPDATE t1 SET c1=16777215,c2=8388607 WHERE c1=104 OR c2=105;
2702 SELECT * FROM t1 WHERE c1=16777215 AND c2=8388607;
2703 c1 c2 c3
2704 UPDATE t1 SET c2=0 WHERE c1 IN (1,2);
2705 SELECT * FROM t1 WHERE c2=0;
2706 c1 c2 c3
2707 1 0 1
2708 2 0 2
2709 INSERT INTO t1 VALUES(106,-106,9),(107,-107,10),(108,-108,11),(109,-109,12),(255,127,13);
2710 UPDATE t1 SET c1=16777216,c2=8388608 WHERE c2 BETWEEN -108 AND -106;
2711 Warnings:
2712 Warning 1264 Out of range value for column 'c1' at row 1
2713 Warning 1264 Out of range value for column 'c2' at row 1
2714 Warning 1264 Out of range value for column 'c1' at row 2
2715 Warning 1264 Out of range value for column 'c2' at row 2
2716 Warning 1264 Out of range value for column 'c1' at row 3
2717 Warning 1264 Out of range value for column 'c2' at row 3
2718 SELECT COUNT(*) FROM t1 WHERE c1=16777216 AND c2=8388608 /* no rows */;
2719 COUNT(*)
2720 0
2721 SELECT * FROM t1 WHERE c1=16777215 AND c2=8388607;
2722 c1 c2 c3
2723 16777215 8388607 10
2724 16777215 8388607 11
2725 16777215 8388607 9
2726 UPDATE t1 SET c2=-8388609 WHERE c1=109 ORDER BY c1;
2727 Warnings:
2728 Warning 1264 Out of range value for column 'c2' at row 13
2729 SELECT c1,c2 FROM t1 WHERE c1=109;
2730 c1 c2
2731 109 -8388608
2732 SET SQL_MODE=DEFAULT;
2733 INSERT INTO t1 VALUES(110,-110,14),(111,-111,15);
2734 SET SQL_MODE=STRICT_ALL_TABLES;
2735 Warnings:
2736 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2737 UPDATE t1 SET c1=NULL WHERE c2=-110;
2738 ERROR 23000: Column 'c1' cannot be null
2739 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-110 /* updates to default value 0 */;
2740 Warnings:
2741 Warning 1048 Column 'c1' cannot be null
2742 SELECT c1,c2 FROM t1 WHERE c2=-110;
2743 c1 c2
2744 0 -110
2745 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
2746 UPDATE t1 SET c1=NULL WHERE c2=-111 /* updates to default value 0 */;
2747 Warnings:
2748 Warning 1048 Column 'c1' cannot be null
2749 SET SQL_MODE=DEFAULT;
2750 SELECT * FROM t1 WHERE c1=0;
2751 c1 c2 c3
2752 0 -102 5
2753 0 -110 14
2754 0 -111 15
2755 0 -128 0
2756 0 -8388608 6
2757 SELECT * FROM t1 WHERE c2>=-101 AND c1<=101 ORDER BY c2;
2758 c1 c2 c3
2759 1 0 1
2760 2 0 2
2761 UPDATE t1 SET c1=c1+1,c2=c2+1 WHERE c2>=-101 AND c1<=101 ORDER BY c2 LIMIT 2;
2762 SELECT * FROM t1;
2763 c1 c2 c3
2764 0 -102 5
2765 0 -110 14
2766 0 -111 15
2767 0 -128 0
2768 0 -8388608 6
2769 109 -8388608 12
2770 110 -104 7
2771 110 -105 8
2772 120 NULL 3
2773 120 NULL 4
2774 16777215 8388607 10
2775 16777215 8388607 11
2776 16777215 8388607 9
2777 2 1 1
2778 255 127 13
2779 3 1 2
2780 CREATE TABLE t2(c1 MEDIUMINT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 MEDIUMINT UNSIGNED NULL, c3 INT);
2781 INSERT INTO t2(c1) VALUES(1),(2),(3),(4),(5);
2782 UPDATE t2 SET c1=1 WHERE c1=3;
2783 ERROR 23000: Duplicate entry '1' for key 't2.PRIMARY'
2784 UPDATE IGNORE t2 SET c1=1 WHERE c1>=3;
2785 Warnings:
2786 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
2787 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
2788 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
2789 SELECT c1 FROM t2;
2790 c1
2791 1
2792 2
2793 3
2794 4
2795 5
2796 TRUNCATE TABLE t1;
2797 ALTER TABLE t1 CHANGE c1 c1 MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(c1);
2798 INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
2799 TRUNCATE TABLE t2;
2800 INSERT INTO t2 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
2801 INSERT INTO t1(c2,c3) VALUES(13,14);
2802 SELECT c1 FROM t1 WHERE c2=13;
2803 c1
2804 11
2805 INSERT INTO t2(c2,c3) VALUES(13,14);
2806 SELECT c1 FROM t2 WHERE c2=13;
2807 c1
2808 11
2809 UPDATE t1 SET c1=16777215,c2=8388607 WHERE c2=13;
2810 SELECT c1,c2 FROM t1 ORDER BY c1;
2811 c1 c2
2812 1 2
2813 4 5
2814 7 8
2815 10 11
2816 16777215 8388607
2817 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
2818 UPDATE t2 SET c1=0,c2=-8388608 WHERE c2=2;
2819 Warnings:
2820 Warning 1264 Out of range value for column 'c2' at row 1
2821 SELECT c1,c2 FROM t1 ORDER BY c1;
2822 c1 c2
2823 1 2
2824 4 5
2825 7 8
2826 10 11
2827 16777215 8388607
2828 UPDATE t2 SET c2=0 WHERE c2=5;
2829 SELECT c1,c2 FROM t1 ORDER BY c1;
2830 c1 c2
2831 1 2
2832 4 5
2833 7 8
2834 10 11
2835 16777215 8388607
2836 UPDATE t2 SET c1=8388607,c2=16777215 WHERE c2=13;
2837 SELECT c1,c2 FROM t2 ORDER BY c1;
2838 c1 c2
2839 0 0
2840 4 0
2841 7 8
2842 10 11
2843 8388607 16777215
2844 UPDATE t2 SET c1=-8388608,c2=0 WHERE c2=2;
2845 SELECT c1,c2 FROM t2 ORDER BY c1;
2846 c1 c2
2847 0 0
2848 4 0
2849 7 8
2850 10 11
2851 8388607 16777215
2852 UPDATE t2 SET c1=0,c2=0 WHERE c2=5;
2853 SELECT c1,c2 FROM t2 ORDER BY c1;
2854 c1 c2
2855 0 0
2856 4 0
2857 7 8
2858 10 11
2859 8388607 16777215
2860 SET SQL_MODE=DEFAULT;
2861 CREATE TABLE mt1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
2862 CREATE TABLE mt2 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
2863 CREATE TABLE mt3 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
2864 INSERT INTO mt1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
2865 INSERT INTO mt2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
2866 INSERT INTO mt3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
2867 UPDATE IGNORE mt1, mt2 ,mt3 SET mt1.c2 = 30, mt2.c2 = 40, mt3.c2=50 WHERE mt1.c1=mt2.c2 AND mt2.c1=mt3.c2;
2868 SELECT * FROM mt1;
2869 c1 c2
2870 1 30
2871 2 30
2872 3 3
2873 4 4
2874 5 5
2875 SELECT * FROM mt2;
2876 c1 c2
2877 11 40
2878 12 1
2879 13 40
2880 14 40
2881 15 6
2882 SELECT * FROM mt3;
2883 c1 c2
2884 21 50
2885 22 50
2886 23 50
2887 24 50
2888 25 15
2889 DROP TABLE mt1,mt2,mt3;
2890 TRUNCATE TABLE t1;
2891 TRUNCATE TABLE t2;
2892 ALTER TABLE t2 CHANGE c2 c2 MEDIUMINT UNSIGNED NULL, ADD KEY(c2);
2893 INSERT INTO t1 VALUES(1,-1,1),(2,-2,2),(3,-3,3),(4,-4,4),(5,-5,5),(6,-6,6),(7,-7,7),(8,-8,8),(9,-9,9),(10,-10,10),(11,NULL,11);
2894 INSERT INTO t2 VALUES(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(-7,7,7),(-8,8,8),(-9,9,9),(10,10,10),(-11,NULL,11),(-12,12,12);
2895 UPDATE t1,t2 SET t1.c1=50,t1.c2=50,t2.c1=50,t2.c2=50 WHERE t2.c1=t1.c1;
2896 SELECT * FROM t1,t2 WHERE t1.c1=50 AND t1.c2=50 AND t2.c1=50 AND t2.c2=50;
2897 c1 c2 c3 c1 c2 c3
2898 50 50 10 50 50 10
2899 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
2900 c1 c2 c3 c1 c2 c3
2901 50 50 10 50 50 10
2902 ALTER TABLE t2 CHANGE c1 c1 MEDIUMINT SIGNED NOT NULL, DROP PRIMARY KEY;
2903 ALTER TABLE t2 CHANGE c1 c1 MEDIUMINT SIGNED NOT NULL, ADD KEY(c1);
2904 INSERT INTO t2 VALUES(-8388608,0,12),(0,255,13),(8388607,16777215,14);
2905 UPDATE t2 SET c2=16777215 WHERE c1 <> 0 ORDER BY c1 LIMIT 2;
2906 SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c1;
2907 c1 c2 c3
2908 -8388608 16777215 12
2909 -12 16777215 12
2910 -9 9 9
2911 -8 8 8
2912 -7 7 7
2913 -6 6 6
2914 -5 5 5
2915 -4 4 4
2916 -3 3 3
2917 -2 2 2
2918 -1 1 1
2919 0 255 13
2920 50 50 10
2921 8388607 16777215 14
2922 UPDATE t2 SET c2=16777215 WHERE c1 >= 0 ORDER BY c1 DESC LIMIT 2;
2923 SELECT * FROM t2 WHERE c1 >= 0 ORDER BY c1 DESC;
2924 c1 c2 c3
2925 8388607 16777215 14
2926 50 16777215 10
2927 0 255 13
2928 UPDATE t2 SET c2=16777215 WHERE c1 <= -9 ORDER BY c1 LIMIT 2;
2929 SELECT * FROM t2 WHERE c1 <= -9 ORDER BY c1;
2930 c1 c2 c3
2931 -8388608 16777215 12
2932 -12 16777215 12
2933 -11 NULL 11
2934 -9 9 9
2935 UPDATE t2 SET c2=16777215 WHERE c1 <=> -3 ORDER BY c1 DESC LIMIT 2;
2936 SELECT * FROM t2 WHERE c1 <=> -3 ORDER BY c1;
2937 c1 c2 c3
2938 -3 16777215 3
2939 UPDATE t2 SET c2=16777215 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1 LIMIT 2;
2940 SELECT * FROM t2 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1;
2941 c1 c2 c3
2942 -6 16777215 6
2943 -5 16777215 5
2944 -4 4 4
2945 UPDATE t2 SET c2=16777215 WHERE c1 IN(-7,-8) ORDER BY c1 DESC LIMIT 2;
2946 SELECT * FROM t2 WHERE c1 IN(-7,-8) ORDER BY c1 DESC;
2947 c1 c2 c3
2948 -7 16777215 7
2949 -8 16777215 8
2950 UPDATE t2 SET c2=16777215 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
2951 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
2952 c1 c2 c3
2953 UPDATE t2 SET c2=16777215 WHERE c1>= -8388608 AND c1 < -9 ORDER BY c1 LIMIT 2;
2954 SELECT * FROM t2 WHERE c1>= -8388608 AND c1 < -9 ORDER BY c1;
2955 c1 c2 c3
2956 -8388608 16777215 12
2957 -12 16777215 12
2958 -11 16777215 11
2959 UPDATE t2 SET c2=16777215 WHERE c1=-2 OR c2=1;
2960 SELECT * FROM t2 WHERE c2=16777215;
2961 c1 c2 c3
2962 -1 16777215 1
2963 -11 16777215 11
2964 -12 16777215 12
2965 -2 16777215 2
2966 -3 16777215 3
2967 -5 16777215 5
2968 -6 16777215 6
2969 -7 16777215 7
2970 -8 16777215 8
2971 -8388608 16777215 12
2972 50 16777215 10
2973 8388607 16777215 14
2974 SELECT * FROM t2;
2975 c1 c2 c3
2976 -1 16777215 1
2977 -11 16777215 11
2978 -12 16777215 12
2979 -2 16777215 2
2980 -3 16777215 3
2981 -4 4 4
2982 -5 16777215 5
2983 -6 16777215 6
2984 -7 16777215 7
2985 -8 16777215 8
2986 -8388608 16777215 12
2987 -9 9 9
2988 0 255 13
2989 50 16777215 10
2990 8388607 16777215 14
2991 SELECT c2 FROM t2;
2992 c2
2993 16777215
2994 16777215
2995 16777215
2996 16777215
2997 16777215
2998 16777215
2999 16777215
3000 16777215
3001 16777215
3002 16777215
3003 16777215
3004 16777215
3005 255
3006 4
3007 9
3008 SELECT c2 FROM t2 ORDER BY c2 DESC;
3009 c2
3010 16777215
3011 16777215
3012 16777215
3013 16777215
3014 16777215
3015 16777215
3016 16777215
3017 16777215
3018 16777215
3019 16777215
3020 16777215
3021 16777215
3022 255
3023 9
3024 4
3025 SELECT * FROM t2 WHERE c2=16777215;
3026 c1 c2 c3
3027 -1 16777215 1
3028 -11 16777215 11
3029 -12 16777215 12
3030 -2 16777215 2
3031 -3 16777215 3
3032 -5 16777215 5
3033 -6 16777215 6
3034 -7 16777215 7
3035 -8 16777215 8
3036 -8388608 16777215 12
3037 50 16777215 10
3038 8388607 16777215 14
3039 SELECT * FROM t2 WHERE c2 <> 16777215 ORDER BY c2,c1;
3040 c1 c2 c3
3041 -4 4 4
3042 -9 9 9
3043 0 255 13
3044 SELECT * FROM t2 WHERE c2 <> 16777215 ORDER BY c2,c1 DESC LIMIT 2;
3045 c1 c2 c3
3046 -4 4 4
3047 -9 9 9
3048 SELECT * FROM t2 WHERE c2 > 16777215 ORDER BY c2,c1 DESC;
3049 c1 c2 c3
3050 SELECT * FROM t2 WHERE c2 >= 16777215 ORDER BY c2,c1 LIMIT 2;
3051 c1 c2 c3
3052 -8388608 16777215 12
3053 -12 16777215 12
3054 SELECT * FROM t2 WHERE c2 < 16777215 ORDER BY c2,c1;
3055 c1 c2 c3
3056 -4 4 4
3057 -9 9 9
3058 0 255 13
3059 SELECT * FROM t2 WHERE c2 <= 16777215 ORDER BY c2,c1 DESC LIMIT 2;
3060 c1 c2 c3
3061 -4 4 4
3062 -9 9 9
3063 SELECT * FROM t2 WHERE c2 <=> 16777215 ORDER BY c2,c1 DESC;
3064 c1 c2 c3
3065 8388607 16777215 14
3066 50 16777215 10
3067 -1 16777215 1
3068 -2 16777215 2
3069 -3 16777215 3
3070 -5 16777215 5
3071 -6 16777215 6
3072 -7 16777215 7
3073 -8 16777215 8
3074 -11 16777215 11
3075 -12 16777215 12
3076 -8388608 16777215 12
3077 SELECT * FROM t2 WHERE c2 <=> 16777215 ORDER BY c2,c1 LIMIT 2;
3078 c1 c2 c3
3079 -8388608 16777215 12
3080 -12 16777215 12
3081 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 16777215 ORDER BY c2,c1;
3082 c1 c2 c3
3083 -4 4 4
3084 -9 9 9
3085 0 255 13
3086 -8388608 16777215 12
3087 -12 16777215 12
3088 -11 16777215 11
3089 -8 16777215 8
3090 -7 16777215 7
3091 -6 16777215 6
3092 -5 16777215 5
3093 -3 16777215 3
3094 -2 16777215 2
3095 -1 16777215 1
3096 50 16777215 10
3097 8388607 16777215 14
3098 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 16777215 ORDER BY c2,c1 DESC LIMIT 2;
3099 c1 c2 c3
3100 -4 4 4
3101 -9 9 9
3102 SELECT * FROM t2 WHERE c2 IN(0,16777215) ORDER BY c2,c1 DESC;
3103 c1 c2 c3
3104 8388607 16777215 14
3105 50 16777215 10
3106 -1 16777215 1
3107 -2 16777215 2
3108 -3 16777215 3
3109 -5 16777215 5
3110 -6 16777215 6
3111 -7 16777215 7
3112 -8 16777215 8
3113 -11 16777215 11
3114 -12 16777215 12
3115 -8388608 16777215 12
3116 SELECT * FROM t2 WHERE c2 IN(0,16777215) ORDER BY c2,c1 LIMIT 2;
3117 c1 c2 c3
3118 -8388608 16777215 12
3119 -12 16777215 12
3120 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 16777215 ORDER BY c2,c1 DESC;
3121 c1 c2 c3
3122 -4 4 4
3123 -9 9 9
3124 0 255 13
3125 8388607 16777215 14
3126 50 16777215 10
3127 -1 16777215 1
3128 -2 16777215 2
3129 -3 16777215 3
3130 -5 16777215 5
3131 -6 16777215 6
3132 -7 16777215 7
3133 -8 16777215 8
3134 -11 16777215 11
3135 -12 16777215 12
3136 -8388608 16777215 12
3137 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 16777215 ORDER BY c2,c1 LIMIT 2;
3138 c1 c2 c3
3139 -4 4 4
3140 -9 9 9
3141 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1;
3142 c1 c2 c3
3143 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
3144 c1 c2 c3
3145 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
3146 c1 c2 c3
3147 -4 4 4
3148 -9 9 9
3149 0 255 13
3150 8388607 16777215 14
3151 50 16777215 10
3152 -1 16777215 1
3153 -2 16777215 2
3154 -3 16777215 3
3155 -5 16777215 5
3156 -6 16777215 6
3157 -7 16777215 7
3158 -8 16777215 8
3159 -11 16777215 11
3160 -12 16777215 12
3161 -8388608 16777215 12
3162 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 LIMIT 2;
3163 c1 c2 c3
3164 -4 4 4
3165 -9 9 9
3166 SELECT * FROM t2 WHERE c2=16777215 OR c1=-4;
3167 c1 c2 c3
3168 -1 16777215 1
3169 -11 16777215 11
3170 -12 16777215 12
3171 -2 16777215 2
3172 -3 16777215 3
3173 -4 4 4
3174 -5 16777215 5
3175 -6 16777215 6
3176 -7 16777215 7
3177 -8 16777215 8
3178 -8388608 16777215 12
3179 50 16777215 10
3180 8388607 16777215 14
3181 CREATE TABLE mt1 (a mediumint not null primary key, b mediumint not null, key (b));
3182 CREATE TABLE mt2 (a mediumint not null primary key, b mediumint not null, key (b));
3183 INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
3184 INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
3185 update mt1,mt2 set mt1.a=mt1.a+100;
3186 select * from mt1;
3187 a b
3188 101 1
3189 102 2
3190 103 3
3191 104 4
3192 105 5
3193 106 6
3194 107 7
3195 108 8
3196 109 9
3197 update mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
3198 select * from mt1;
3199 a b
3200 102 2
3201 103 3
3202 104 4
3203 105 5
3204 106 6
3205 107 7
3206 108 8
3207 109 9
3208 201 1
3209 update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
3210 select * from mt1;
3211 a b
3212 102 12
3213 103 3
3214 104 4
3215 105 5
3216 106 6
3217 107 7
3218 108 8
3219 109 9
3220 201 1
3221 update mt1 straight_join mt2 set mt1.b=mt1.b+2,mt2.b=mt1.b+10 where mt1.b between 3 and 5 and mt2.a=mt1.a-100;
3222 select * from mt1;
3223 a b
3224 102 12
3225 103 5
3226 104 6
3227 105 7
3228 106 6
3229 107 7
3230 108 8
3231 109 9
3232 201 1
3233 update mt1,mt2 set mt1.b=mt2.b, mt1.a=mt2.a where mt1.a=mt2.a and not exists (select * from mt2 where mt2.a > 10);
3234 drop table mt1,mt2;
3235 TRUNCATE TABLE t2;
3236 INSERT INTO t2 VALUES(-128,0,1),(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(0,0,7),(1,1,8),(127,255,9);
3237 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
3238 c1 c2 c3
3239 -3 3 3
3240 127 255 9
3241 DELETE FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1 LIMIT 2;
3242 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
3243 c1 c2 c3
3244 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
3245 c1 c2 c3
3246 -128 0 1
3247 -6 6 6
3248 -5 5 5
3249 -4 4 4
3250 -2 2 2
3251 -1 1 1
3252 0 0 7
3253 1 1 8
3254 DELETE FROM t2 WHERE c1 >= -128 ORDER BY c1 LIMIT 2;
3255 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
3256 c1 c2 c3
3257 -5 5 5
3258 -4 4 4
3259 -2 2 2
3260 -1 1 1
3261 0 0 7
3262 1 1 8
3263 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
3264 c1 c2 c3
3265 1 1 8
3266 0 0 7
3267 -1 1 1
3268 -2 2 2
3269 -4 4 4
3270 -5 5 5
3271 DELETE FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC LIMIT 2;
3272 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
3273 c1 c2 c3
3274 -1 1 1
3275 -2 2 2
3276 -4 4 4
3277 -5 5 5
3278 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
3279 c1 c2 c3
3280 -5 5 5
3281 -4 4 4
3282 -2 2 2
3283 -1 1 1
3284 DELETE FROM t2 WHERE c1 < 0 ORDER BY c1 LIMIT 2;
3285 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
3286 c1 c2 c3
3287 -2 2 2
3288 -1 1 1
3289 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
3290 c1 c2 c3
3291 -1 1 1
3292 -2 2 2
3293 DELETE FROM t2 WHERE c1 > -3 ORDER BY c1 DESC LIMIT 2;
3294 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
3295 c1 c2 c3
3296 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
3297 c1 c2 c3
3298 DELETE FROM t2 WHERE c1=-3 OR c2=2;
3299 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
3300 c1 c2 c3
3301 INSERT INTO t2 VALUES(-128,0,1),(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(0,0,7),(1,1,8),(2,NULL,9),(3,NULL,10),(127,255,11);
3302 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
3303 c1 c2 c3
3304 -128 0 1
3305 -6 6 6
3306 -5 5 5
3307 -4 4 4
3308 -3 3 3
3309 -2 2 2
3310 -1 1 1
3311 0 0 7
3312 1 1 8
3313 2 NULL 9
3314 3 NULL 10
3315 127 255 11
3316 DELETE FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1 LIMIT 2;
3317 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
3318 c1 c2 c3
3319 -5 5 5
3320 -4 4 4
3321 -3 3 3
3322 -2 2 2
3323 -1 1 1
3324 0 0 7
3325 1 1 8
3326 2 NULL 9
3327 3 NULL 10
3328 127 255 11
3329 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
3330 c1 c2 c3
3331 0 0 7
3332 -2 2 2
3333 DELETE FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
3334 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
3335 c1 c2 c3
3336 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
3337 c1 c2 c3
3338 -1 1 1
3339 1 1 8
3340 DELETE FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1 LIMIT 2;
3341 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
3342 c1 c2 c3
3343 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
3344 c1 c2 c3
3345 -5 5 5
3346 -4 4 4
3347 -3 3 3
3348 127 255 11
3349 DELETE FROM t2 WHERE c2 IS NOT NULL ORDER BY c1 LIMIT 2;
3350 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
3351 c1 c2 c3
3352 -3 3 3
3353 127 255 11
3354 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
3355 c1 c2 c3
3356 3 NULL 10
3357 2 NULL 9
3358 DELETE FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
3359 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
3360 c1 c2 c3
3361 TRUNCATE TABLE t1;
3362 TRUNCATE TABLE t2;
3363 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
3364 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
3365 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
3366 c1 c2 c3 c1 c2 c3
3367 1 127 3 127 1 2
3368 1 127 3 127 255 1
3369 2 -128 4 -128 0 3
3370 2 -128 4 -128 2 4
3371 254 127 1 127 1 2
3372 254 127 1 127 255 1
3373 255 -128 2 -128 0 3
3374 255 -128 2 -128 2 4
3375 255 -128 2 127 255 1
3376 3 NULL 5 -1 NULL 5
3377 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
3378 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
3379 c1 c2 c3 c1 c2 c3
3380 TRUNCATE TABLE t1;
3381 TRUNCATE TABLE t2;
3382 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
3383 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
3384 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
3385 c1 c2 c3 c1 c2 c3
3386 1 127 3 127 1 2
3387 1 127 3 127 255 1
3388 2 -128 4 -128 0 3
3389 2 -128 4 -128 2 4
3390 254 127 1 127 1 2
3391 254 127 1 127 255 1
3392 255 -128 2 -128 0 3
3393 255 -128 2 -128 2 4
3394 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
3395 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
3396 c1 c2 c3 c1 c2 c3
3397 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
3398 ERROR HY000: You can't specify target table 't1' for update in FROM clause
3399 CREATE TABLE t3(c1 MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, c2 MEDIUMINT SIGNED NULL, c3 INT);
3400 CREATE TABLE t4(c1 MEDIUMINT UNSIGNED, c2 INT);
3401 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
3402 INSERT INTO t4 VALUES(200,1),(150,3);
3403 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
3404 c1 c2 c3 c1 c2
3405 200 126 1 200 1
3406 DELETE t3.*,t4.* FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
3407 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
3408 c1 c2 c3 c1 c2
3409 DROP TABLE t1,t2,t3,t4;
3410 CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL, c2 BIGINT SIGNED NULL, c3 INT, INDEX idx2(c2));
3411 INSERT INTO t1 VALUES(0,-128,0),(1,1,1),(2,2,2),(0,NULL,3),(101,-101,4),(102,-102,5),(103,-103,6),(104,-104,7),(105,-105,8);
3412 SELECT * FROM t1;
3413 c1 c2 c3
3414 0 -128 0
3415 0 NULL 3
3416 1 1 1
3417 101 -101 4
3418 102 -102 5
3419 103 -103 6
3420 104 -104 7
3421 105 -105 8
3422 2 2 2
3423 UPDATE t1 SET c1=110 WHERE c2 >-128 ORDER BY c2 LIMIT 2;
3424 SELECT * FROM t1 WHERE c1=110;
3425 c1 c2 c3
3426 110 -104 7
3427 110 -105 8
3428 UPDATE t1 SET c1=c1+1,c2=NULL WHERE c1=101;
3429 SELECT * FROM t1 WHERE c1=102 AND c2 IS NULL;
3430 c1 c2 c3
3431 102 NULL 4
3432 UPDATE t1 SET c1=120 WHERE c2 IS NULL;
3433 SELECT c1,c2 FROM t1 WHERE c1=120;
3434 c1 c2
3435 120 NULL
3436 120 NULL
3437 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
3438 UPDATE t1 SET c1=-120 WHERE c2=-102;
3439 Warnings:
3440 Warning 1264 Out of range value for column 'c1' at row 1
3441 SELECT c1,c2 FROM t1 WHERE c2=-102;
3442 c1 c2
3443 0 -102
3444 UPDATE t1 SET c1=0,c2=-9223372036854775808 WHERE c1=103 AND c2=-103;
3445 SELECT * FROM t1 WHERE c1=0 AND c2=-9223372036854775808;
3446 c1 c2 c3
3447 0 -9223372036854775808 6
3448 UPDATE t1 SET c1=18446744073709551615,c2=9223372036854775807 WHERE c1=104 OR c2=105;
3449 SELECT * FROM t1 WHERE c1=18446744073709551615 AND c2=9223372036854775807;
3450 c1 c2 c3
3451 UPDATE t1 SET c2=0 WHERE c1 IN (1,2);
3452 SELECT * FROM t1 WHERE c2=0;
3453 c1 c2 c3
3454 1 0 1
3455 2 0 2
3456 INSERT INTO t1 VALUES(106,-106,9),(107,-107,10),(108,-108,11),(109,-109,12),(255,127,13);
3457 UPDATE t1 SET c1=18446744073709551616,c2=9223372036854775808 WHERE c2 BETWEEN -108 AND -106;
3458 Warnings:
3459 Warning 1264 Out of range value for column 'c1' at row 1
3460 Warning 1264 Out of range value for column 'c2' at row 1
3461 Warning 1264 Out of range value for column 'c1' at row 2
3462 Warning 1264 Out of range value for column 'c2' at row 2
3463 Warning 1264 Out of range value for column 'c1' at row 3
3464 Warning 1264 Out of range value for column 'c2' at row 3
3465 SELECT COUNT(*) FROM t1 WHERE c1=18446744073709551616 AND c2=9223372036854775808 /* no rows */;
3466 COUNT(*)
3467 0
3468 SELECT * FROM t1 WHERE c1=18446744073709551615 AND c2=9223372036854775807;
3469 c1 c2 c3
3470 18446744073709551615 9223372036854775807 10
3471 18446744073709551615 9223372036854775807 11
3472 18446744073709551615 9223372036854775807 9
3473 UPDATE t1 SET c2=-9223372036854775809 WHERE c1=109 ORDER BY c1;
3474 Warnings:
3475 Warning 1264 Out of range value for column 'c2' at row 13
3476 SELECT c1,c2 FROM t1 WHERE c1=109;
3477 c1 c2
3478 109 -9223372036854775808
3479 SET SQL_MODE=DEFAULT;
3480 INSERT INTO t1 VALUES(110,-110,14),(111,-111,15);
3481 SET SQL_MODE=STRICT_ALL_TABLES;
3482 Warnings:
3483 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
3484 UPDATE t1 SET c1=NULL WHERE c2=-110;
3485 ERROR 23000: Column 'c1' cannot be null
3486 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-110 /* updates to default value 0 */;
3487 Warnings:
3488 Warning 1048 Column 'c1' cannot be null
3489 SELECT c1,c2 FROM t1 WHERE c2=-110;
3490 c1 c2
3491 0 -110
3492 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
3493 UPDATE t1 SET c1=NULL WHERE c2=-111 /* updates to default value 0 */;
3494 Warnings:
3495 Warning 1048 Column 'c1' cannot be null
3496 SET SQL_MODE=DEFAULT;
3497 SELECT * FROM t1 WHERE c1=0;
3498 c1 c2 c3
3499 0 -102 5
3500 0 -110 14
3501 0 -111 15
3502 0 -128 0
3503 0 -9223372036854775808 6
3504 SELECT * FROM t1 WHERE c2>=-101 AND c1<=101 ORDER BY c2;
3505 c1 c2 c3
3506 1 0 1
3507 2 0 2
3508 UPDATE t1 SET c1=c1+1,c2=c2+1 WHERE c2>=-101 AND c1<=101 ORDER BY c2 LIMIT 2;
3509 SELECT * FROM t1;
3510 c1 c2 c3
3511 0 -102 5
3512 0 -110 14
3513 0 -111 15
3514 0 -128 0
3515 0 -9223372036854775808 6
3516 109 -9223372036854775808 12
3517 110 -104 7
3518 110 -105 8
3519 120 NULL 3
3520 120 NULL 4
3521 18446744073709551615 9223372036854775807 10
3522 18446744073709551615 9223372036854775807 11
3523 18446744073709551615 9223372036854775807 9
3524 2 1 1
3525 255 127 13
3526 3 1 2
3527 CREATE TABLE t2(c1 BIGINT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 BIGINT UNSIGNED NULL, c3 INT);
3528 INSERT INTO t2(c1) VALUES(1),(2),(3),(4),(5);
3529 UPDATE t2 SET c1=1 WHERE c1=3;
3530 ERROR 23000: Duplicate entry '1' for key 't2.PRIMARY'
3531 UPDATE IGNORE t2 SET c1=1 WHERE c1>=3;
3532 Warnings:
3533 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
3534 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
3535 Warning 1062 Duplicate entry '1' for key 't2.PRIMARY'
3536 SELECT c1 FROM t2;
3537 c1
3538 1
3539 2
3540 3
3541 4
3542 5
3543 TRUNCATE TABLE t1;
3544 ALTER TABLE t1 CHANGE c1 c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(c1);
3545 INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
3546 TRUNCATE TABLE t2;
3547 INSERT INTO t2 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
3548 INSERT INTO t1(c2,c3) VALUES(13,14);
3549 SELECT c1 FROM t1 WHERE c2=13;
3550 c1
3551 11
3552 INSERT INTO t2(c2,c3) VALUES(13,14);
3553 SELECT c1 FROM t2 WHERE c2=13;
3554 c1
3555 11
3556 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
3557 UPDATE t1 SET c1=18446744073709551615,c2=9223372036854775807 WHERE c2=13;
3558 SELECT c1,c2 FROM t1 ORDER BY c1;
3559 c1 c2
3560 1 2
3561 4 5
3562 7 8
3563 10 11
3564 18446744073709551615 9223372036854775807
3565 UPDATE t2 SET c1=0,c2=-9223372036854775808 WHERE c2=2;
3566 Warnings:
3567 Warning 1264 Out of range value for column 'c2' at row 1
3568 SELECT c1,c2 FROM t1 ORDER BY c1;
3569 c1 c2
3570 1 2
3571 4 5
3572 7 8
3573 10 11
3574 18446744073709551615 9223372036854775807
3575 UPDATE t2 SET c2=0 WHERE c2=5;
3576 SELECT c1,c2 FROM t1 ORDER BY c1;
3577 c1 c2
3578 1 2
3579 4 5
3580 7 8
3581 10 11
3582 18446744073709551615 9223372036854775807
3583 UPDATE t2 SET c1=9223372036854775807,c2=18446744073709551615 WHERE c2=13;
3584 SELECT c1,c2 FROM t2 ORDER BY c1;
3585 c1 c2
3586 0 0
3587 4 0
3588 7 8
3589 10 11
3590 9223372036854775807 18446744073709551615
3591 UPDATE t2 SET c1=-9223372036854775808,c2=0 WHERE c2=2;
3592 SELECT c1,c2 FROM t2 ORDER BY c1;
3593 c1 c2
3594 0 0
3595 4 0
3596 7 8
3597 10 11
3598 9223372036854775807 18446744073709551615
3599 UPDATE t2 SET c1=0,c2=0 WHERE c2=5;
3600 SELECT c1,c2 FROM t2 ORDER BY c1;
3601 c1 c2
3602 0 0
3603 4 0
3604 7 8
3605 10 11
3606 9223372036854775807 18446744073709551615
3607 SET SQL_MODE=DEFAULT;
3608 CREATE TABLE mt1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
3609 CREATE TABLE mt2 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
3610 CREATE TABLE mt3 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
3611 INSERT INTO mt1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
3612 INSERT INTO mt2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
3613 INSERT INTO mt3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
3614 UPDATE IGNORE mt1, mt2 ,mt3 SET mt1.c2 = 30, mt2.c2 = 40, mt3.c2=50 WHERE mt1.c1=mt2.c2 AND mt2.c1=mt3.c2;
3615 SELECT * FROM mt1;
3616 c1 c2
3617 1 30
3618 2 30
3619 3 3
3620 4 4
3621 5 5
3622 SELECT * FROM mt2;
3623 c1 c2
3624 11 40
3625 12 1
3626 13 40
3627 14 40
3628 15 6
3629 SELECT * FROM mt3;
3630 c1 c2
3631 21 50
3632 22 50
3633 23 50
3634 24 50
3635 25 15
3636 DROP TABLE mt1,mt2,mt3;
3637 TRUNCATE TABLE t1;
3638 TRUNCATE TABLE t2;
3639 ALTER TABLE t2 CHANGE c2 c2 BIGINT UNSIGNED NULL, ADD KEY(c2);
3640 INSERT INTO t1 VALUES(1,-1,1),(2,-2,2),(3,-3,3),(4,-4,4),(5,-5,5),(6,-6,6),(7,-7,7),(8,-8,8),(9,-9,9),(10,-10,10),(11,NULL,11);
3641 INSERT INTO t2 VALUES(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(-7,7,7),(-8,8,8),(-9,9,9),(10,10,10),(-11,NULL,11),(-12,12,12);
3642 UPDATE t1,t2 SET t1.c1=50,t1.c2=50,t2.c1=50,t2.c2=50 WHERE t2.c1=t1.c1;
3643 SELECT * FROM t1,t2 WHERE t1.c1=50 AND t1.c2=50 AND t2.c1=50 AND t2.c2=50;
3644 c1 c2 c3 c1 c2 c3
3645 50 50 10 50 50 10
3646 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
3647 c1 c2 c3 c1 c2 c3
3648 50 50 10 50 50 10
3649 ALTER TABLE t2 CHANGE c1 c1 BIGINT SIGNED NOT NULL, DROP PRIMARY KEY;
3650 ALTER TABLE t2 CHANGE c1 c1 BIGINT SIGNED NOT NULL, ADD KEY(c1);
3651 INSERT INTO t2 VALUES(-9223372036854775808,0,12),(0,255,13),(9223372036854775807,18446744073709551615,14);
3652 UPDATE t2 SET c2=18446744073709551615 WHERE c1 <> 0 ORDER BY c1 LIMIT 2;
3653 SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c1;
3654 c1 c2 c3
3655 -9223372036854775808 18446744073709551615 12
3656 -12 18446744073709551615 12
3657 -9 9 9
3658 -8 8 8
3659 -7 7 7
3660 -6 6 6
3661 -5 5 5
3662 -4 4 4
3663 -3 3 3
3664 -2 2 2
3665 -1 1 1
3666 0 255 13
3667 50 50 10
3668 9223372036854775807 18446744073709551615 14
3669 UPDATE t2 SET c2=18446744073709551615 WHERE c1 >= 0 ORDER BY c1 DESC LIMIT 2;
3670 SELECT * FROM t2 WHERE c1 >= 0 ORDER BY c1 DESC;
3671 c1 c2 c3
3672 9223372036854775807 18446744073709551615 14
3673 50 18446744073709551615 10
3674 0 255 13
3675 UPDATE t2 SET c2=18446744073709551615 WHERE c1 <= -9 ORDER BY c1 LIMIT 2;
3676 SELECT * FROM t2 WHERE c1 <= -9 ORDER BY c1;
3677 c1 c2 c3
3678 -9223372036854775808 18446744073709551615 12
3679 -12 18446744073709551615 12
3680 -11 NULL 11
3681 -9 9 9
3682 UPDATE t2 SET c2=18446744073709551615 WHERE c1 <=> -3 ORDER BY c1 DESC LIMIT 2;
3683 SELECT * FROM t2 WHERE c1 <=> -3 ORDER BY c1;
3684 c1 c2 c3
3685 -3 18446744073709551615 3
3686 UPDATE t2 SET c2=18446744073709551615 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1 LIMIT 2;
3687 SELECT * FROM t2 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1;
3688 c1 c2 c3
3689 -6 18446744073709551615 6
3690 -5 18446744073709551615 5
3691 -4 4 4
3692 UPDATE t2 SET c2=18446744073709551615 WHERE c1 IN(-7,-8) ORDER BY c1 DESC LIMIT 2;
3693 SELECT * FROM t2 WHERE c1 IN(-7,-8) ORDER BY c1 DESC;
3694 c1 c2 c3
3695 -7 18446744073709551615 7
3696 -8 18446744073709551615 8
3697 UPDATE t2 SET c2=18446744073709551615 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
3698 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
3699 c1 c2 c3
3700 UPDATE t2 SET c2=18446744073709551615 WHERE c1>= -9223372036854775808 AND c1 < -9 ORDER BY c1 LIMIT 2;
3701 SELECT * FROM t2 WHERE c1>= -9223372036854775808 AND c1 < -9 ORDER BY c1;
3702 c1 c2 c3
3703 -9223372036854775808 18446744073709551615 12
3704 -12 18446744073709551615 12
3705 -11 18446744073709551615 11
3706 UPDATE t2 SET c2=18446744073709551615 WHERE c1=-2 OR c2=1;
3707 SELECT * FROM t2 WHERE c2=18446744073709551615;
3708 c1 c2 c3
3709 -1 18446744073709551615 1
3710 -11 18446744073709551615 11
3711 -12 18446744073709551615 12
3712 -2 18446744073709551615 2
3713 -3 18446744073709551615 3
3714 -5 18446744073709551615 5
3715 -6 18446744073709551615 6
3716 -7 18446744073709551615 7
3717 -8 18446744073709551615 8
3718 -9223372036854775808 18446744073709551615 12
3719 50 18446744073709551615 10
3720 9223372036854775807 18446744073709551615 14
3721 SELECT * FROM t2;
3722 c1 c2 c3
3723 -1 18446744073709551615 1
3724 -11 18446744073709551615 11
3725 -12 18446744073709551615 12
3726 -2 18446744073709551615 2
3727 -3 18446744073709551615 3
3728 -4 4 4
3729 -5 18446744073709551615 5
3730 -6 18446744073709551615 6
3731 -7 18446744073709551615 7
3732 -8 18446744073709551615 8
3733 -9 9 9
3734 -9223372036854775808 18446744073709551615 12
3735 0 255 13
3736 50 18446744073709551615 10
3737 9223372036854775807 18446744073709551615 14
3738 SELECT c2 FROM t2;
3739 c2
3740 18446744073709551615
3741 18446744073709551615
3742 18446744073709551615
3743 18446744073709551615
3744 18446744073709551615
3745 18446744073709551615
3746 18446744073709551615
3747 18446744073709551615
3748 18446744073709551615
3749 18446744073709551615
3750 18446744073709551615
3751 18446744073709551615
3752 255
3753 4
3754 9
3755 SELECT c2 FROM t2 ORDER BY c2 DESC;
3756 c2
3757 18446744073709551615
3758 18446744073709551615
3759 18446744073709551615
3760 18446744073709551615
3761 18446744073709551615
3762 18446744073709551615
3763 18446744073709551615
3764 18446744073709551615
3765 18446744073709551615
3766 18446744073709551615
3767 18446744073709551615
3768 18446744073709551615
3769 255
3770 9
3771 4
3772 SELECT * FROM t2 WHERE c2=18446744073709551615;
3773 c1 c2 c3
3774 -1 18446744073709551615 1
3775 -11 18446744073709551615 11
3776 -12 18446744073709551615 12
3777 -2 18446744073709551615 2
3778 -3 18446744073709551615 3
3779 -5 18446744073709551615 5
3780 -6 18446744073709551615 6
3781 -7 18446744073709551615 7
3782 -8 18446744073709551615 8
3783 -9223372036854775808 18446744073709551615 12
3784 50 18446744073709551615 10
3785 9223372036854775807 18446744073709551615 14
3786 SELECT * FROM t2 WHERE c2 <> 18446744073709551615 ORDER BY c2,c1;
3787 c1 c2 c3
3788 -4 4 4
3789 -9 9 9
3790 0 255 13
3791 SELECT * FROM t2 WHERE c2 <> 18446744073709551615 ORDER BY c2,c1 DESC LIMIT 2;
3792 c1 c2 c3
3793 -4 4 4
3794 -9 9 9
3795 SELECT * FROM t2 WHERE c2 > 18446744073709551615 ORDER BY c2,c1 DESC;
3796 c1 c2 c3
3797 SELECT * FROM t2 WHERE c2 >= 18446744073709551615 ORDER BY c2,c1 LIMIT 2;
3798 c1 c2 c3
3799 -9223372036854775808 18446744073709551615 12
3800 -12 18446744073709551615 12
3801 SELECT * FROM t2 WHERE c2 < 18446744073709551615 ORDER BY c2,c1;
3802 c1 c2 c3
3803 -4 4 4
3804 -9 9 9
3805 0 255 13
3806 SELECT * FROM t2 WHERE c2 <= 18446744073709551615 ORDER BY c2,c1 DESC LIMIT 2;
3807 c1 c2 c3
3808 -4 4 4
3809 -9 9 9
3810 SELECT * FROM t2 WHERE c2 <=> 18446744073709551615 ORDER BY c2,c1 DESC;
3811 c1 c2 c3
3812 9223372036854775807 18446744073709551615 14
3813 50 18446744073709551615 10
3814 -1 18446744073709551615 1
3815 -2 18446744073709551615 2
3816 -3 18446744073709551615 3
3817 -5 18446744073709551615 5
3818 -6 18446744073709551615 6
3819 -7 18446744073709551615 7
3820 -8 18446744073709551615 8
3821 -11 18446744073709551615 11
3822 -12 18446744073709551615 12
3823 -9223372036854775808 18446744073709551615 12
3824 SELECT * FROM t2 WHERE c2 <=> 18446744073709551615 ORDER BY c2,c1 LIMIT 2;
3825 c1 c2 c3
3826 -9223372036854775808 18446744073709551615 12
3827 -12 18446744073709551615 12
3828 SELECT * FROM t2 WHERE c2 >= 0 AND c2 <= 18446744073709551615 ORDER BY c2,c1;
3829 c1 c2 c3
3830 -4 4 4
3831 -9 9 9
3832 0 255 13
3833 -9223372036854775808 18446744073709551615 12
3834 -12 18446744073709551615 12
3835 -11 18446744073709551615 11
3836 -8 18446744073709551615 8
3837 -7 18446744073709551615 7
3838 -6 18446744073709551615 6
3839 -5 18446744073709551615 5
3840 -3 18446744073709551615 3
3841 -2 18446744073709551615 2
3842 -1 18446744073709551615 1
3843 50 18446744073709551615 10
3844 9223372036854775807 18446744073709551615 14
3845 SELECT * FROM t2 WHERE c2 >= 0 AND c2 <= 18446744073709551615 ORDER BY c2,c1 DESC LIMIT 2;
3846 c1 c2 c3
3847 -4 4 4
3848 -9 9 9
3849 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 18446744073709551615 ORDER BY c2,c1;
3850 c1 c2 c3
3851 -4 4 4
3852 -9 9 9
3853 0 255 13
3854 -9223372036854775808 18446744073709551615 12
3855 -12 18446744073709551615 12
3856 -11 18446744073709551615 11
3857 -8 18446744073709551615 8
3858 -7 18446744073709551615 7
3859 -6 18446744073709551615 6
3860 -5 18446744073709551615 5
3861 -3 18446744073709551615 3
3862 -2 18446744073709551615 2
3863 -1 18446744073709551615 1
3864 50 18446744073709551615 10
3865 9223372036854775807 18446744073709551615 14
3866 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 18446744073709551615 ORDER BY c2,c1 DESC LIMIT 2;
3867 c1 c2 c3
3868 -4 4 4
3869 -9 9 9
3870 SELECT * FROM t2 WHERE c2 IN(0,18446744073709551615) ORDER BY c2,c1 DESC;
3871 c1 c2 c3
3872 9223372036854775807 18446744073709551615 14
3873 50 18446744073709551615 10
3874 -1 18446744073709551615 1
3875 -2 18446744073709551615 2
3876 -3 18446744073709551615 3
3877 -5 18446744073709551615 5
3878 -6 18446744073709551615 6
3879 -7 18446744073709551615 7
3880 -8 18446744073709551615 8
3881 -11 18446744073709551615 11
3882 -12 18446744073709551615 12
3883 -9223372036854775808 18446744073709551615 12
3884 SELECT * FROM t2 WHERE c2 IN(0,18446744073709551615) ORDER BY c2,c1 LIMIT 2;
3885 c1 c2 c3
3886 -9223372036854775808 18446744073709551615 12
3887 -12 18446744073709551615 12
3888 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 18446744073709551615 ORDER BY c2,c1 DESC;
3889 c1 c2 c3
3890 -4 4 4
3891 -9 9 9
3892 0 255 13
3893 9223372036854775807 18446744073709551615 14
3894 50 18446744073709551615 10
3895 -1 18446744073709551615 1
3896 -2 18446744073709551615 2
3897 -3 18446744073709551615 3
3898 -5 18446744073709551615 5
3899 -6 18446744073709551615 6
3900 -7 18446744073709551615 7
3901 -8 18446744073709551615 8
3902 -11 18446744073709551615 11
3903 -12 18446744073709551615 12
3904 -9223372036854775808 18446744073709551615 12
3905 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 18446744073709551615 ORDER BY c2,c1 LIMIT 2;
3906 c1 c2 c3
3907 -4 4 4
3908 -9 9 9
3909 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1;
3910 c1 c2 c3
3911 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
3912 c1 c2 c3
3913 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
3914 c1 c2 c3
3915 -4 4 4
3916 -9 9 9
3917 0 255 13
3918 9223372036854775807 18446744073709551615 14
3919 50 18446744073709551615 10
3920 -1 18446744073709551615 1
3921 -2 18446744073709551615 2
3922 -3 18446744073709551615 3
3923 -5 18446744073709551615 5
3924 -6 18446744073709551615 6
3925 -7 18446744073709551615 7
3926 -8 18446744073709551615 8
3927 -11 18446744073709551615 11
3928 -12 18446744073709551615 12
3929 -9223372036854775808 18446744073709551615 12
3930 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 LIMIT 2;
3931 c1 c2 c3
3932 -4 4 4
3933 -9 9 9
3934 SELECT * FROM t2 WHERE c2=18446744073709551615 OR c1=-4;
3935 c1 c2 c3
3936 -1 18446744073709551615 1
3937 -11 18446744073709551615 11
3938 -12 18446744073709551615 12
3939 -2 18446744073709551615 2
3940 -3 18446744073709551615 3
3941 -4 4 4
3942 -5 18446744073709551615 5
3943 -6 18446744073709551615 6
3944 -7 18446744073709551615 7
3945 -8 18446744073709551615 8
3946 -9223372036854775808 18446744073709551615 12
3947 50 18446744073709551615 10
3948 9223372036854775807 18446744073709551615 14
3949 CREATE TABLE mt1 (a bigint not null primary key, b bigint not null, key (b));
3950 CREATE TABLE mt2 (a bigint not null primary key, b bigint not null, key (b));
3951 INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
3952 INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
3953 update mt1,mt2 set mt1.a=mt1.a+100;
3954 select * from mt1;
3955 a b
3956 101 1
3957 102 2
3958 103 3
3959 104 4
3960 105 5
3961 106 6
3962 107 7
3963 108 8
3964 109 9
3965 update mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
3966 select * from mt1;
3967 a b
3968 102 2
3969 103 3
3970 104 4
3971 105 5
3972 106 6
3973 107 7
3974 108 8
3975 109 9
3976 201 1
3977 update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
3978 select * from mt1;
3979 a b
3980 102 12
3981 103 3
3982 104 4
3983 105 5
3984 106 6
3985 107 7
3986 108 8
3987 109 9
3988 201 1
3989 update mt1 straight_join mt2 set mt1.b=mt1.b+2,mt2.b=mt1.b+10 where mt1.b between 3 and 5 and mt2.a=mt1.a-100;
3990 select * from mt1;
3991 a b
3992 102 12
3993 103 5
3994 104 6
3995 105 7
3996 106 6
3997 107 7
3998 108 8
3999 109 9
4000 201 1
4001 update mt1,mt2 set mt1.b=mt2.b, mt1.a=mt2.a where mt1.a=mt2.a and not exists (select * from mt2 where mt2.a > 10);
4002 drop table mt1,mt2;
4003 TRUNCATE TABLE t2;
4004 INSERT INTO t2 VALUES(-128,0,1),(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(0,0,7),(1,1,8),(127,255,9);
4005 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
4006 c1 c2 c3
4007 -3 3 3
4008 127 255 9
4009 DELETE FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1 LIMIT 2;
4010 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
4011 c1 c2 c3
4012 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
4013 c1 c2 c3
4014 -128 0 1
4015 -6 6 6
4016 -5 5 5
4017 -4 4 4
4018 -2 2 2
4019 -1 1 1
4020 0 0 7
4021 1 1 8
4022 DELETE FROM t2 WHERE c1 >= -128 ORDER BY c1 LIMIT 2;
4023 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
4024 c1 c2 c3
4025 -5 5 5
4026 -4 4 4
4027 -2 2 2
4028 -1 1 1
4029 0 0 7
4030 1 1 8
4031 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
4032 c1 c2 c3
4033 1 1 8
4034 0 0 7
4035 -1 1 1
4036 -2 2 2
4037 -4 4 4
4038 -5 5 5
4039 DELETE FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC LIMIT 2;
4040 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
4041 c1 c2 c3
4042 -1 1 1
4043 -2 2 2
4044 -4 4 4
4045 -5 5 5
4046 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
4047 c1 c2 c3
4048 -5 5 5
4049 -4 4 4
4050 -2 2 2
4051 -1 1 1
4052 DELETE FROM t2 WHERE c1 < 0 ORDER BY c1 LIMIT 2;
4053 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
4054 c1 c2 c3
4055 -2 2 2
4056 -1 1 1
4057 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
4058 c1 c2 c3
4059 -1 1 1
4060 -2 2 2
4061 DELETE FROM t2 WHERE c1 > -3 ORDER BY c1 DESC LIMIT 2;
4062 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
4063 c1 c2 c3
4064 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
4065 c1 c2 c3
4066 DELETE FROM t2 WHERE c1=-3 OR c2=2;
4067 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
4068 c1 c2 c3
4069 INSERT INTO t2 VALUES(-128,0,1),(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(0,0,7),(1,1,8),(2,NULL,9),(3,NULL,10),(127,255,11);
4070 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
4071 c1 c2 c3
4072 -128 0 1
4073 -6 6 6
4074 -5 5 5
4075 -4 4 4
4076 -3 3 3
4077 -2 2 2
4078 -1 1 1
4079 0 0 7
4080 1 1 8
4081 2 NULL 9
4082 3 NULL 10
4083 127 255 11
4084 DELETE FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1 LIMIT 2;
4085 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
4086 c1 c2 c3
4087 -5 5 5
4088 -4 4 4
4089 -3 3 3
4090 -2 2 2
4091 -1 1 1
4092 0 0 7
4093 1 1 8
4094 2 NULL 9
4095 3 NULL 10
4096 127 255 11
4097 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
4098 c1 c2 c3
4099 0 0 7
4100 -2 2 2
4101 DELETE FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
4102 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
4103 c1 c2 c3
4104 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
4105 c1 c2 c3
4106 -1 1 1
4107 1 1 8
4108 DELETE FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1 LIMIT 2;
4109 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
4110 c1 c2 c3
4111 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
4112 c1 c2 c3
4113 -5 5 5
4114 -4 4 4
4115 -3 3 3
4116 127 255 11
4117 DELETE FROM t2 WHERE c2 IS NOT NULL ORDER BY c1 LIMIT 2;
4118 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
4119 c1 c2 c3
4120 -3 3 3
4121 127 255 11
4122 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
4123 c1 c2 c3
4124 3 NULL 10
4125 2 NULL 9
4126 DELETE FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
4127 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
4128 c1 c2 c3
4129 TRUNCATE TABLE t1;
4130 TRUNCATE TABLE t2;
4131 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
4132 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
4133 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
4134 c1 c2 c3 c1 c2 c3
4135 1 127 3 127 1 2
4136 1 127 3 127 255 1
4137 2 -128 4 -128 0 3
4138 2 -128 4 -128 2 4
4139 254 127 1 127 1 2
4140 254 127 1 127 255 1
4141 255 -128 2 -128 0 3
4142 255 -128 2 -128 2 4
4143 255 -128 2 127 255 1
4144 3 NULL 5 -1 NULL 5
4145 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
4146 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
4147 c1 c2 c3 c1 c2 c3
4148 TRUNCATE TABLE t1;
4149 TRUNCATE TABLE t2;
4150 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
4151 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
4152 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
4153 c1 c2 c3 c1 c2 c3
4154 1 127 3 127 1 2
4155 1 127 3 127 255 1
4156 2 -128 4 -128 0 3
4157 2 -128 4 -128 2 4
4158 254 127 1 127 1 2
4159 254 127 1 127 255 1
4160 255 -128 2 -128 0 3
4161 255 -128 2 -128 2 4
4162 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
4163 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
4164 c1 c2 c3 c1 c2 c3
4165 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
4166 ERROR HY000: You can't specify target table 't1' for update in FROM clause
4167 CREATE TABLE t3(c1 BIGINT UNSIGNED NOT NULL PRIMARY KEY, c2 BIGINT SIGNED NULL, c3 INT);
4168 CREATE TABLE t4(c1 BIGINT UNSIGNED, c2 INT);
4169 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
4170 INSERT INTO t4 VALUES(200,1),(150,3);
4171 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
4172 c1 c2 c3 c1 c2
4173 200 126 1 200 1
4174 DELETE t3.*,t4.* FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
4175 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
4176 c1 c2 c3 c1 c2
4177 DROP TABLE t1,t2,t3,t4;