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