Debian Codegraph @

mysql-test/suite/engines/iuds/t/update_delete_number.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2;
3 --enable_warnings
4
5 ######## Running UPDATE tests for INT ########
6
7 # Create table
8 CREATE TABLE t1(c1 INT UNSIGNED NOT NULL, c2 INT SIGNED NULL, c3 INT, INDEX idx2(c2));
9
10 # Insert into tables
11 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);
12 --sorted_result
13 SELECT * FROM t1;
14
15 # Update order by limit
16 UPDATE t1 SET c1=110 WHERE c2 >-128 ORDER BY c2 LIMIT 2;
17 --sorted_result
18 SELECT * FROM t1 WHERE c1=110;
19
20 # Update with NULL(null to numbers, numbers to null)
21 UPDATE t1 SET c1=c1+1,c2=NULL WHERE c1=101;
22 --sorted_result
23 SELECT * FROM t1 WHERE c1=102 AND c2 IS NULL;
24 UPDATE t1 SET c1=120 WHERE c2 IS NULL;
25 --sorted_result
26 SELECT c1,c2 FROM t1 WHERE c1=120;
27
28 # Update negative value to unsigned col
29 UPDATE IGNORE t1 SET c1=-120 WHERE c2=-102;
30 --sorted_result
31 SELECT c1,c2 FROM t1 WHERE c2=-102;
32
33 # Update range values
34 UPDATE t1 SET c1=0,c2=-2147483648 WHERE c1=103 AND c2=-103;
35 --sorted_result
36 SELECT * FROM t1 WHERE c1=0 AND c2=-2147483648;
37 UPDATE t1 SET c1=4294967295,c2=2147483647 WHERE c1=104 OR c2=105;
38 --sorted_result
39 SELECT * FROM t1 WHERE c1=4294967295 AND c2=2147483647;
40 UPDATE t1 SET c2=0 WHERE c1 IN (1,2);
41 --sorted_result
42 SELECT * FROM t1 WHERE c2=0;
43
44 # Update outside range, would be clipped to closest endpoint
45 INSERT INTO t1 VALUES(106,-106,9),(107,-107,10),(108,-108,11),(109,-109,12),(255,127,13);
46 UPDATE IGNORE t1 SET c1=4294967296,c2=2147483648 WHERE c2 BETWEEN -108 AND -106;
47 SELECT COUNT(*) FROM t1 WHERE c1=4294967296 AND c2=2147483648 /* no rows */;
48 --sorted_result
49 SELECT * FROM t1 WHERE c1=4294967295 AND c2=2147483647;
50 UPDATE IGNORE t1 SET c2=-2147483649 WHERE c1=109 ORDER BY c1;
51 --sorted_result
52 SELECT c1,c2 FROM t1 WHERE c1=109;
53
54 # Update ignore on bad null error
55 INSERT INTO t1 VALUES(110,-110,14),(111,-111,15);
56 SET SQL_MODE=STRICT_ALL_TABLES;
57 --error ER_BAD_NULL_ERROR
58 UPDATE t1 SET c1=NULL WHERE c2=-110;
59 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-110 /* updates to default value 0 */;
60 --sorted_result
61 SELECT c1,c2 FROM t1 WHERE c2=-110;
62 SET SQL_MODE=DEFAULT;
63 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-111 /* updates to default value 0 */;
64 --sorted_result
65 SELECT * FROM t1 WHERE c1=0;
66
67 # Update with multi-range
68 SELECT * FROM t1 WHERE c2>=-101 AND c1<=101 ORDER BY c2;
69 UPDATE t1 SET c1=c1+1,c2=c2+1 WHERE c2>=-101 AND c1<=101 ORDER BY c2 LIMIT 2;
70 --sorted_result
71 SELECT * FROM t1;
72
73 # Create table with AUTO_INCREMENT Primary Key
74 CREATE TABLE t2(c1 INT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT UNSIGNED NULL, c3 INT);
75
76 # Update ignore on duplicate key error
77 INSERT INTO t2(c1) VALUES(1),(2),(3),(4),(5);
78 --error ER_DUP_ENTRY
79 UPDATE t2 SET c1=1 WHERE c1=3;
80 UPDATE IGNORE t2 SET c1=1 WHERE c1>=3;
81 --sorted_result
82 SELECT c1 FROM t2;
83
84 # Update the auto_incremented value with boundary values to unsigned/signed field
85 TRUNCATE TABLE t1;
86 ALTER TABLE t1 CHANGE c1 c1 INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(c1);
87 INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
88 TRUNCATE TABLE t2;
89 INSERT INTO t2 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
90 INSERT INTO t1(c2,c3) VALUES(13,14);
91 SELECT c1 FROM t1 WHERE c2=13;
92 INSERT INTO t2(c2,c3) VALUES(13,14);
93 SELECT c1 FROM t2 WHERE c2=13;
94 # Update the unsigned auto_increment field
95 UPDATE t1 SET c1=4294967295,c2=2147483647 WHERE c2=13;
96 SELECT c1,c2 FROM t1 ORDER BY c1;
97 UPDATE IGNORE t2 SET c1=0,c2=-2147483648 WHERE c2=2;
98 SELECT c1,c2 FROM t1 ORDER BY c1;
99 UPDATE t2 SET c2=0 WHERE c2=5;
100 SELECT c1,c2 FROM t1 ORDER BY c1;
101 # Update the signed auto_increment field
102 UPDATE t2 SET c1=2147483647,c2=4294967295 WHERE c2=13;
103 SELECT c1,c2 FROM t2 ORDER BY c1;
104 UPDATE t2 SET c1=-2147483648,c2=0 WHERE c2=2;
105 SELECT c1,c2 FROM t2 ORDER BY c1;
106 UPDATE t2 SET c1=0,c2=0 WHERE c2=5;
107 SELECT c1,c2 FROM t2 ORDER BY c1;
108
109 # Multi table update
110 CREATE TABLE mt1 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
111 CREATE TABLE mt2 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
112 CREATE TABLE mt3 (c1 INT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
113 INSERT INTO mt1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
114 INSERT INTO mt2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
115 INSERT INTO mt3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
116 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;
117 --sorted_result
118 SELECT * FROM mt1;
119 --sorted_result
120 SELECT * FROM mt2;
121 --sorted_result
122 SELECT * FROM mt3;
123 DROP TABLE mt1,mt2,mt3;
124
125 # Update,Select,Delete,Select using various access methods
126 TRUNCATE TABLE t1;
127 TRUNCATE TABLE t2;
128 ALTER TABLE t2 CHANGE c2 c2 INT UNSIGNED NULL, ADD KEY(c2);
129 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);
130 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);
131
132 # Update using various methods
133
134 # Using eq-ref
135 UPDATE t1,t2 SET t1.c1=50,t1.c2=50,t2.c1=50,t2.c2=50 WHERE t2.c1=t1.c1;
136 --sorted_result
137 SELECT * FROM t1,t2 WHERE t1.c1=50 AND t1.c2=50 AND t2.c1=50 AND t2.c2=50;
138 --sorted_result
139 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
140
141 ALTER TABLE t2 CHANGE c1 c1 INT SIGNED NOT NULL, DROP PRIMARY KEY;
142 ALTER TABLE t2 CHANGE c1 c1 INT SIGNED NOT NULL, ADD KEY(c1);
143 INSERT INTO t2 VALUES(-2147483648,0,13),(2147483647,4294967295,14),(0,2147483648,15),(2147483647,2147483647,16);
144
145 # range access
146 UPDATE t2 SET c1=-2147483648 WHERE c2 <> 0 ORDER BY c2 LIMIT 2;
147 SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c2;
148 UPDATE t2 SET c1=-2147483648 WHERE c2 >= 0 ORDER BY c2 DESC LIMIT 2;
149 SELECT * FROM t2 WHERE c2 >= 0 ORDER BY c2 DESC;
150 UPDATE t2 SET c1=-2147483648 WHERE c2 <= 3 ORDER BY c2 LIMIT 2;
151 SELECT * FROM t2 WHERE c2 <= 3 ORDER BY c2;
152 UPDATE t2 SET c1=-2147483648 WHERE c2 <=> 4 ORDER BY c2 DESC LIMIT 2;
153 SELECT * FROM t2 WHERE c2 <=> 4 ORDER BY c2;
154 UPDATE t2 SET c1=-2147483648 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2 LIMIT 2;
155 SELECT * FROM t2 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2;
156 UPDATE t2 SET c1=-2147483648 WHERE c2 IN(8,9) ORDER BY c2 DESC LIMIT 2;
157 SELECT * FROM t2 WHERE c2 IN(8,9) ORDER BY c2 DESC;
158 UPDATE t2 SET c1=-2147483648 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
159 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
160 UPDATE t2 SET c1=-2147483648 WHERE c2>= 6 AND c2 < 9 ORDER BY c2 LIMIT 2;
161 SELECT * FROM t2 WHERE c2>= 6 AND c2 < 9 ORDER BY c2;
162
163 # Using index-merge
164 UPDATE t2 SET c1=-2147483648 WHERE c1=-12 OR c2=1;
165 --sorted_result
166 SELECT * FROM t2 WHERE c1=-2147483648;
167
168 # Select using various access methods
169 ## Full table scan
170 --sorted_result
171 SELECT * FROM t2;
172 ## Forward index scan, covering ##
173 --sorted_result
174 SELECT c1 FROM t2;
175 ## Backward index scan, covering ##
176 SELECT c1 FROM t2 ORDER BY c1 DESC;
177 # ref access
178 --sorted_result
179 SELECT * FROM t2 WHERE c1=-2147483648;
180 ## Range access ##
181 SELECT * FROM t2 WHERE c1 <> -2147483648 ORDER BY c1,c2;
182 SELECT * FROM t2 WHERE c1 <> -2147483648 ORDER BY c1,c2 DESC LIMIT 2;
183 SELECT * FROM t2 WHERE c1 > -2147483648 ORDER BY c1,c2 DESC;
184 SELECT * FROM t2 WHERE c1 >= -2147483648 ORDER BY c1,c2 LIMIT 2;
185 SELECT * FROM t2 WHERE c1 < -2147483648 ORDER BY c1,c2;
186 SELECT * FROM t2 WHERE c1 <= -2147483648 ORDER BY c1,c2 DESC LIMIT 2;
187 SELECT * FROM t2 WHERE c1 <=> -2147483648 ORDER BY c1,c2 DESC;
188 SELECT * FROM t2 WHERE c1 <=> -2147483648 ORDER BY c1,c2 LIMIT 2;
189 SELECT * FROM t2 WHERE c1 BETWEEN -2147483648 AND 2147483647 ORDER BY c1,c2;
190 SELECT * FROM t2 WHERE c1 BETWEEN -2147483648 AND 2147483647 ORDER BY c1,c2 DESC LIMIT 2;
191 SELECT * FROM t2 WHERE c1 IN(-2147483648,2147483647) ORDER BY c1,c2 DESC;
192 SELECT * FROM t2 WHERE c1 IN(-2147483648,2147483647) ORDER BY c1,c2 LIMIT 2;
193 SELECT * FROM t2 WHERE c1>=-2147483648 AND c1 <= 2147483647 ORDER BY c1,c2 DESC;
194 SELECT * FROM t2 WHERE c1>=-2147483648 AND c1 <= 2147483647 ORDER BY c1,c2 LIMIT 2;
195 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1;
196 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
197 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
198 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 LIMIT 2;
199
200 # Test multi update with different join methods
201 CREATE TABLE mt1 (a int not null primary key, b int not null, key (b));
202 CREATE TABLE mt2 (a int not null primary key, b int not null, key (b));
203 INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
204 INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
205 # Full join, without key
206 update mt1,mt2 set mt1.a=mt1.a+100;
207 --sorted_result
208 select * from mt1;
209 # unique key
210 update mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
211 --sorted_result
212 select * from mt1;
213 # ref key
214 update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
215 --sorted_result
216 select * from mt1;
217 # Range key (in mt1)
218 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;
219 --sorted_result
220 select * from mt1;
221 --sorted_result
222 select * from mt2;
223 # test for non-updating table which is also used in sub-select
224 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);
225 drop table mt1,mt2;
226
227 # Testing delete using various access methods
228 TRUNCATE TABLE t2;
229 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);
230
231 # Using Index-merge
232 SELECT * FROM t2 WHERE c1=127 OR c2=3;
233 DELETE FROM t2 WHERE c1=127 OR c2=3;
234 SELECT * FROM t2 WHERE c1=127 OR c2=3;
235
236 # Range access
237 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
238 DELETE FROM t2 WHERE c1 >= -128 ORDER BY c1 LIMIT 2;
239 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
240 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
241 DELETE FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC LIMIT 2;
242 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
243 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
244 DELETE FROM t2 WHERE c1 < 0 ORDER BY c1 LIMIT 2;
245 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
246 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
247 DELETE FROM t2 WHERE c1 > -3 ORDER BY c1 DESC LIMIT 2;
248 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
249 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);
250 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
251 DELETE FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1 LIMIT 2;
252 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
253 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
254 DELETE FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
255 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
256 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
257 DELETE FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1 LIMIT 2;
258 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
259 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
260 DELETE FROM t2 WHERE c2 IS NOT NULL ORDER BY c1 LIMIT 2;
261 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
262 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
263 DELETE FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
264 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
265
266 # Muti-table delete
267
268 TRUNCATE TABLE t1;
269 TRUNCATE TABLE t2;
270 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
271 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
272
273 # join (Range checked for each record)
274 --sorted_result
275 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
276 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
277 --sorted_result
278 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
279 TRUNCATE TABLE t1;
280 TRUNCATE TABLE t2;
281 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
282 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
283 --sorted_result
284 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
285 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
286 --sorted_result
287 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
288 TRUNCATE TABLE t1;
289 TRUNCATE TABLE t2;
290 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(3,NULL,5);
291 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-1,NULL,5);
292 # After the patch for MDEV-28883 this should not report
293 # ER_UPDATE_TABLE_USED anymore
294 DELETE FROM t1,t2 using t1,t2
295 where t1.c1=(select c1 from t1 where c2 < 10) and t2.c2 < 10;
296 --sorted_result
297 SELECT * FROM t1;
298 --sorted_result
299 SELECT * FROM t2;
300
301 # eq-ref join
302 CREATE TABLE t3(c1 INT UNSIGNED NOT NULL PRIMARY KEY, c2 INT SIGNED NULL, c3 INT);
303 CREATE TABLE t4(c1 INT UNSIGNED, c2 INT);
304 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
305 INSERT INTO t4 VALUES(200,1),(150,3);
306 --sorted_result
307 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
308 DELETE t3.*,t4.* FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
309 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
310
311 DROP TABLE t1,t2,t3,t4;
312
313 # More tests for updates.............
314 # Update with ORDER BY
315 create table mt1 (a int not null, b int not null, key (a));
316 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);
317 SET @tmp=0;
318 update mt1 set b=(@tmp:=@tmp+1) order by a;
319 update mt1 set b=99 where a=1 order by b asc limit 1;
320 select * from mt1 order by a,b;
321 update mt1 set b=100 where a=1 order by b desc limit 2;
322 update mt1 set a=a+10+b where a=1 order by b;
323 select * from mt1 order by a,b;
324 create table mt2 (a int not null, b int not null);
325 insert into mt2 values (1,1),(1,2),(1,3);
326 update mt1 set b=(select distinct 1 from (select * from mt2) a);
327 drop table mt1,mt2;
328
329 # Multi table update
330 create table mt1 (f1 int);
331 create table mt2 (f2 int);
332 insert into mt1 values(1),(2);
333 insert into mt2 values(1),(1);
334 --enable_info
335 update mt1,mt2 set f1=3,f2=3 where f1=f2 and f1=1;
336 --disable_info
337 update mt2 set f2=1;
338 update mt1 set f1=1 where f1=3;
339 --enable_info
340 update mt2,mt1 set f1=3,f2=3 where f1=f2 and f1=1;
341 --disable_info
342 --sorted_result
343 SELECT * FROM mt1;
344 --sorted_result
345 SELECT * FROM mt2;
346 drop table mt1,mt2;
347
348 # Update with subquery
349 create table mt1(f1 int, f2 int);
350 create table mt2(f3 int, f4 int);
351 create index idx on mt2(f3);
352 insert into mt1 values(1,0),(2,0);
353 insert into mt2 values(1,1),(2,2);
354 UPDATE mt1 SET mt1.f2=(SELECT MAX(mt2.f4) FROM mt2 WHERE mt2.f3=mt1.f1);
355 select * from mt1;
356 drop table mt1,mt2;
357
358 # Update and partial key part
359 create table mt1 (a int, b char(255), key(a, b(20)));
360 insert into mt1 values (0, '1');
361 update mt1 set b = b + 1 where a = 0;
362 --sorted_result
363 select * from mt1;
364 drop table mt1;
365
366 # Update with subquery with ref built with a key from the updated table
367 create table mt1(f1 int, f2 int);
368 create table mt2(f3 int, f4 int);
369 create index idx on mt2(f3);
370 insert into mt1 values(1,0),(2,0);
371 insert into mt2 values(1,1),(2,2);
372 UPDATE mt1 SET mt1.f2=(SELECT MAX(mt2.f4) FROM mt2 WHERE mt2.f3=mt1.f1);
373 --sorted_result
374 select * from mt1;
375 drop table mt1,mt2;
376
377 # Update on last_insert_id()
378 create table mt1 (id int not null auto_increment primary key, id_str varchar(32));
379 insert into mt1 (id_str) values ("test");
380 update mt1 set id_str = concat(id_str, id) where id = last_insert_id();
381 --sorted_result
382 select * from mt1;
383 drop table mt1;
384
385 # More updates
386 create table mt1 (n int(10) not null primary key, d int(10));
387 create table mt2 (n int(10) not null primary key, d int(10));
388 insert into mt1 values(1,1), (3,3);
389 insert into mt2 values(1,10),(2,20);
390 UPDATE mt2 left outer join mt1 on mt1.n=mt2.n SET mt1.d=mt2.d;
391 --sorted_result
392 select * from mt1;
393 --sorted_result
394 select * from mt2;
395 drop table mt1,mt2;
396 create table mt1 (n int(10), d int(10));
397 create table mt2 (n int(10), d int(10));
398 insert into mt1 values(1,1),(1,2);
399 insert into mt2 values(1,10),(2,20);
400 UPDATE mt1,mt2 SET mt1.d=mt2.d,mt2.d=30 WHERE mt1.n=mt2.n;
401 --sorted_result
402 select * from mt1;
403 --sorted_result
404 select * from mt2;
405 drop table mt1,mt2;
406 create table mt1 (n int(10), d int(10));
407 create table mt2 (n int(10), d int(10));
408 insert into mt1 values(1,1),(3,2);
409 insert into mt2 values(1,10),(1,20);
410 UPDATE mt1,mt2 SET mt1.d=mt2.d,mt2.d=30 WHERE mt1.n=mt2.n;
411 --sorted_result
412 select * from mt1;
413 --sorted_result
414 select * from mt2;
415 UPDATE mt1 a ,mt2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
416 --sorted_result
417 select * from mt1;
418 --sorted_result
419 select * from mt2;
420 DELETE a, b FROM mt1 a,mt2 b where a.n=b.n;
421 --sorted_result
422 select * from mt1;
423 --sorted_result
424 select * from mt2;
425 drop table mt1,mt2;
426
427 # Multi table UPDATE IGNORE on duplicate keys
428 CREATE TABLE mt1 (`colA` int(10) unsigned NOT NULL auto_increment, `colB` int(11) NOT NULL default '0', PRIMARY KEY (`colA`));
429 INSERT INTO mt1 VALUES (4433,5424);
430 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`));
431 INSERT INTO mt2 VALUES (3,4433,10005,495,500);
432 INSERT INTO mt2 VALUES (3,4433,10005,496,500);
433 INSERT INTO mt2 VALUES (3,4433,10009,494,500);
434 INSERT INTO mt2 VALUES (3,4433,10011,494,500);
435 INSERT INTO mt2 VALUES (3,4433,10005,497,500);
436 INSERT INTO mt2 VALUES (3,4433,10013,489,500);
437 INSERT INTO mt2 VALUES (3,4433,10005,494,500);
438 INSERT INTO mt2 VALUES (3,4433,10005,493,500);
439 INSERT INTO mt2 VALUES (3,4433,10005,492,500);
440 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;
441 --sorted_result
442 SELECT * FROM mt2;
443 DROP TABLE mt1,mt2;
444
445 # Test multi-update and multi-delete with impossible where
446 create table mt1(id1 smallint(5), field char(5));
447 create table mt2(id2 smallint(5), field char(5));
448 insert into mt1 values (1, 'a'), (2, 'aa');
449 insert into mt2 values (1, 'b'), (2, 'bb');
450 --sorted_result
451 select * from mt1;
452 --sorted_result
453 select * from mt2;
454 update mt2 inner join mt1 on mt1.id1=mt2.id2 set mt2.field=mt1.field where 0=1;
455 update mt2, mt1 set mt2.field=mt1.field where mt1.id1=mt2.id2 and 0=1;
456 delete mt1, mt2 from mt2 inner join mt1 on mt1.id1=mt2.id2 where 0=1;
457 delete mt1, mt2 from mt2,mt1 where mt1.id1=mt2.id2 and 0=1;
458 drop table mt1,mt2;
459
460 create table mt1 (n numeric(10));
461 create table mt2 (n numeric(10));
462 insert into mt2 values (1),(2),(4),(8),(16),(32);
463 --sorted_result
464 select * from mt2 left outer join mt1 using (n);
465 delete mt1,mt2 from mt2 left outer join mt1 using (n);
466 --sorted_result
467 select * from mt2 left outer join mt1 using (n);
468 drop table mt1,mt2 ;
469
470 create table mt1(id1 int not null primary key, t varchar(100)) pack_keys = 1;
471 create table mt2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1;
472 disable_query_log;
473 let $1 = 1000;
474 while ($1)
475 {
476 let $2 = 5;
477 eval insert into mt1 values ($1,'aaaaaaaaaaaaaaaaaaaa');
478 while ($2)
479 {
480 eval insert into mt2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb');
481 dec $2;
482 }
483 dec $1;
484 }
485 enable_query_log;
486 delete mt1 from mt1,mt2 where mt1.id1 = mt2.id2 and mt1.id1 > 500;
487 drop table mt1,mt2;
488
489 # Test delete with outer join and const tables
490 --disable_warnings
491 create table mt1 (aclid bigint not null primary key, status tinyint(1) not null);
492 create table mt2 (refid bigint not null primary key, aclid bigint, index idx_acl(aclid));
493 --enable_warnings
494 insert into mt2 values(1,null);
495 delete mt2, mt1 from mt2 left join mt1 on (mt2.aclid=mt1.aclid) where mt2.refid='1';
496 drop table mt1, mt2;
497
498 # multi delete wrong table check
499 create table mt1 (a int, primary key (a));
500 create table mt2 (a int, primary key (a));
501 create table mt3 (a int, primary key (a));
502 -- error ER_UNKNOWN_TABLE
503 delete mt1,mt3 from mt1,mt2 where mt1.a=mt2.a and mt2.a=(select mt3.a from mt3 where mt1.a=mt3.a);
504 drop table mt1, mt2, mt3;
505 # multi* unique updating table check
506 create table mt1 (col1 int);
507 create table mt2 (col1 int);
508 update mt1,mt2 set mt1.col1 = (select max(col1) from mt1) where mt1.col1 = mt2.col1;
509 # -- error ER_UPDATE_TABLE_USED
510 delete mt1 from mt1,mt2 where mt1.col1 < (select max(col1) from mt1) and mt1.col1 = mt2.col1;
511 drop table mt1,mt2;
512
513 # With Foreign Key
514 CREATE TABLE IF NOT EXISTS `mt1` (`id` int(11) NOT NULL auto_increment, `tst` text, `tsmt1` text, PRIMARY KEY (`id`));
515 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`));
516 INSERT INTO mt1(tst,tsmt1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
517 INSERT INTO mt2(ParId) VALUES(1), (2), (3);
518 --sorted_result
519 select * from mt2;
520 UPDATE mt2, mt1 SET mt2.tst = mt1.tst, mt2.tsmt1 = mt1.tsmt1 WHERE mt2.ParId = mt1.Id;
521 --sorted_result
522 select * from mt2;
523 drop table mt2,mt1;
524
525 # Test alias
526 CREATE TABLE mt1 ( a int );
527 CREATE TABLE mt2 ( a int );
528 DELETE mt1 FROM mt1, mt2 AS t3;
529 DELETE t4 FROM mt1, mt1 AS t4;
530 DELETE t3 FROM mt1 AS t3, mt1 AS t4;
531 --error ER_UNKNOWN_TABLE
532 DELETE mt1 FROM mt1 AS t3, mt2 AS t4;
533 INSERT INTO mt1 values (1),(2);
534 INSERT INTO mt2 values (1),(2);
535 DELETE mt1 FROM mt1 AS mt2, mt2 AS mt1 where mt1.a=mt2.a and mt1.a=1;
536 SELECT * from mt1;
537 SELECT * from mt2;
538 DELETE mt2 FROM mt1 AS mt2, mt2 AS mt1 where mt1.a=mt2.a and mt1.a=2;
539 SELECT * from mt1;
540 SELECT * from mt2;
541 DROP TABLE mt1,mt2;
542
543 create table mt1(id1 int not null auto_increment primary key, t char(12));
544 create table mt2(id2 int not null, t char(12));
545 create table mt3(id3 int not null, t char(12), index(id3));
546 disable_query_log;
547 let $1 = 100;
548 while ($1)
549 {
550 let $2 = 5;
551 eval insert into mt1(t) values ('$1');
552 while ($2)
553 {
554 eval insert into mt2(id2,t) values ($1,'$2');
555 let $3 = 10;
556 while ($3)
557 {
558 eval insert into mt3(id3,t) values ($1,'$2');
559 dec $3;
560 }
561 dec $2;
562 }
563 dec $1;
564 }
565 enable_query_log;
566 select count(*) from mt1 where id1 > 95;
567 select count(*) from mt2 where id2 > 95;
568 select count(*) from mt3 where id3 > 95;
569 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;
570 select count(*) from mt1 where t = "aaa";
571 select count(*) from mt1 where id1 > 90;
572 select count(*) from mt2 where t = "bbb";
573 select count(*) from mt2 where id2 > 90;
574 select count(*) from mt3 where t = "cc";
575 select count(*) from mt3 where id3 > 90;
576 delete mt1.*, mt2.*, mt3.* from mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3 and mt1.id1 > 95;
577 check table mt1, mt2, mt3;
578 select count(*) from mt1 where id1 > 95;
579 select count(*) from mt2 where id2 > 95;
580 select count(*) from mt3 where id3 > 95;
581 delete mt1, mt2, mt3 from mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3 and mt1.id1 > 5;
582 select count(*) from mt1 where id1 > 5;
583 select count(*) from mt2 where id2 > 5;
584 select count(*) from mt3 where id3 > 5;
585 delete from mt1, mt2, mt3 using mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3 and mt1.id1 > 0;
586 # These queries will force a scan of the table
587 select count(*) from mt1 where id1;
588 select count(*) from mt2 where id2;
589 select count(*) from mt3 where id3;
590 drop table mt1,mt2,mt3;
591
592 ######## Running UPDATE tests for TINYINT ########
593
594 # Create table
595 CREATE TABLE t1(c1 TINYINT UNSIGNED NOT NULL, c2 TINYINT SIGNED NULL, c3 INT, INDEX idx2(c2));
596
597 # Insert into tables
598 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);
599 --sorted_result
600 SELECT * FROM t1;
601
602 # Update order by limit
603 UPDATE t1 SET c1=110 WHERE c2 >-128 ORDER BY c2 LIMIT 2;
604 --sorted_result
605 SELECT * FROM t1 WHERE c1=110;
606
607 # Update with NULL(null to numbers, numbers to null)
608 UPDATE t1 SET c1=c1+1,c2=NULL WHERE c1=101;
609 --sorted_result
610 SELECT * FROM t1 WHERE c1=102 AND c2 IS NULL;
611 UPDATE t1 SET c1=120 WHERE c2 IS NULL;
612 --sorted_result
613 SELECT c1,c2 FROM t1 WHERE c1=120;
614
615 # Update negative value to unsigned col
616 UPDATE IGNORE t1 SET c1=-120 WHERE c2=-102;
617 --sorted_result
618 SELECT c1,c2 FROM t1 WHERE c2=-102;
619
620 # Update range values
621 UPDATE t1 SET c1=0,c2=-128 WHERE c1=103 AND c2=-103;
622 --sorted_result
623 SELECT * FROM t1 WHERE c1=0 AND c2=-128;
624 UPDATE t1 SET c1=255,c2=127 WHERE c1=104 OR c2=105;
625 --sorted_result
626 SELECT * FROM t1 WHERE c1=255 AND c2=127;
627 UPDATE t1 SET c2=0 WHERE c1 IN (1,2);
628 --sorted_result
629 SELECT * FROM t1 WHERE c2=0;
630
631 # Update outside range, would be clipped to closest endpoint
632 INSERT INTO t1 VALUES(106,-106,9),(107,-107,10),(108,-108,11),(109,-109,12),(255,127,13);
633 UPDATE IGNORE t1 SET c1=256,c2=128 WHERE c2 BETWEEN -108 AND -106;
634 SELECT COUNT(*) FROM t1 WHERE c1=256 AND c2=128 /* no rows */;
635 --sorted_result
636 SELECT * FROM t1 WHERE c1=255 AND c2=127;
637 UPDATE IGNORE t1 SET c2=-129 WHERE c1=109 ORDER BY c1;
638 --sorted_result
639 SELECT c1,c2 FROM t1 WHERE c1=109;
640
641 # Update ignore on bad null error
642 INSERT INTO t1 VALUES(110,-110,14),(111,-111,15);
643 SET SQL_MODE=STRICT_ALL_TABLES;
644 --error ER_BAD_NULL_ERROR
645 UPDATE t1 SET c1=NULL WHERE c2=-110;
646 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-110 /* updates to default value 0 */;
647 --sorted_result
648 SELECT c1,c2 FROM t1 WHERE c2=-110;
649 SET SQL_MODE=DEFAULT;
650 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-111 /* updates to default value 0 */;
651 --sorted_result
652 SELECT * FROM t1 WHERE c1=0;
653
654 # Update with multi-range
655 SELECT * FROM t1 WHERE c2>=-101 AND c1<=101 ORDER BY c2;
656 UPDATE t1 SET c1=c1+1,c2=c2+1 WHERE c2>=-101 AND c1<=101 ORDER BY c2 LIMIT 2;
657 --sorted_result
658 SELECT * FROM t1;
659
660 # Create table with AUTO_INCREMENT Primary Key
661 CREATE TABLE t2(c1 TINYINT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 TINYINT UNSIGNED NULL, c3 INT);
662
663 # Update ignore on duplicate key error
664 INSERT INTO t2(c1) VALUES(1),(2),(3),(4),(5);
665 --error ER_DUP_ENTRY
666 UPDATE t2 SET c1=1 WHERE c1=3;
667 UPDATE IGNORE t2 SET c1=1 WHERE c1>=3;
668 --sorted_result
669 SELECT c1 FROM t2;
670
671 # Update the auto_increment value
672 TRUNCATE TABLE t1;
673 ALTER TABLE t1 CHANGE c1 c1 TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(c1);
674 INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
675 TRUNCATE TABLE t2;
676 INSERT INTO t2 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
677 INSERT INTO t1(c2,c3) VALUES(13,14);
678 SELECT c1 FROM t1 WHERE c2=13;
679 INSERT INTO t2(c2,c3) VALUES(13,14);
680 SELECT c1 FROM t2 WHERE c2=13;
681 # Update the unsigned auto_increment field
682 UPDATE t1 SET c1=255,c2=127 WHERE c2=13;
683 SELECT c1,c2 FROM t1 ORDER BY c1;
684 UPDATE IGNORE t2 SET c1=0,c2=-128 WHERE c2=2;
685 SELECT c1,c2 FROM t1 ORDER BY c1;
686 UPDATE t2 SET c2=0 WHERE c2=5;
687 SELECT c1,c2 FROM t1 ORDER BY c1;
688 # Update the signed auto_increment field
689 UPDATE t2 SET c1=127,c2=255 WHERE c2=13;
690 SELECT c1,c2 FROM t2 ORDER BY c1;
691 UPDATE t2 SET c1=-128,c2=0 WHERE c2=2;
692 SELECT c1,c2 FROM t2 ORDER BY c1;
693 UPDATE t2 SET c1=0,c2=0 WHERE c2=5;
694 SELECT c1,c2 FROM t2 ORDER BY c1;
695
696 # Multi table update
697 CREATE TABLE mt1 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
698 CREATE TABLE mt2 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
699 CREATE TABLE mt3 (c1 TINYINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
700 INSERT INTO mt1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
701 INSERT INTO mt2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
702 INSERT INTO mt3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
703 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;
704 --sorted_result
705 SELECT * FROM mt1;
706 --sorted_result
707 SELECT * FROM mt2;
708 --sorted_result
709 SELECT * FROM mt3;
710 DROP TABLE mt1,mt2,mt3;
711
712 # Update,Select,Delete,Select using various access methods
713 TRUNCATE TABLE t1;
714 TRUNCATE TABLE t2;
715 ALTER TABLE t2 CHANGE c2 c2 TINYINT UNSIGNED NULL, ADD KEY(c2);
716 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);
717 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);
718
719 # Update using various methods
720 # eq-ref join
721 UPDATE t1,t2 SET t1.c1=50,t1.c2=50,t2.c1=50,t2.c2=50 WHERE t2.c1=t1.c1;
722 --sorted_result
723 SELECT * FROM t1,t2 WHERE t1.c1=50 AND t1.c2=50 AND t2.c1=50 AND t2.c2=50;
724 --sorted_result
725 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
726
727 ALTER TABLE t2 CHANGE c1 c1 TINYINT SIGNED NOT NULL, DROP PRIMARY KEY;
728 ALTER TABLE t2 CHANGE c1 c1 TINYINT SIGNED NOT NULL, ADD KEY(c1);
729 INSERT INTO t2 VALUES(-128,0,13),(127,255,14),(0,128,15),(127,127,16);
730
731 # range access
732 UPDATE t2 SET c1=-128 WHERE c2 <> 0 ORDER BY c2 LIMIT 2;
733 SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c2;
734 UPDATE t2 SET c1=-128 WHERE c2 >= 0 ORDER BY c2 DESC LIMIT 2;
735 SELECT * FROM t2 WHERE c2 >= 0 ORDER BY c2 DESC;
736 UPDATE t2 SET c1=-128 WHERE c2 <= 3 ORDER BY c2 LIMIT 2;
737 SELECT * FROM t2 WHERE c2 <= 3 ORDER BY c2;
738 UPDATE t2 SET c1=-128 WHERE c2 <=> 4 ORDER BY c2 DESC LIMIT 2;
739 SELECT * FROM t2 WHERE c2 <=> 4 ORDER BY c2;
740 UPDATE t2 SET c1=-128 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2 LIMIT 2;
741 SELECT * FROM t2 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2;
742 UPDATE t2 SET c1=-128 WHERE c2 IN(8,9) ORDER BY c2 DESC LIMIT 2;
743 SELECT * FROM t2 WHERE c2 IN(8,9) ORDER BY c2 DESC;
744 UPDATE t2 SET c1=-128 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
745 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
746 UPDATE t2 SET c1=-128 WHERE c2>= 6 AND c2 < 9 ORDER BY c2 LIMIT 2;
747 SELECT * FROM t2 WHERE c2>= 6 AND c2 < 9 ORDER BY c2;
748
749 # Using index-merge
750 UPDATE t2 SET c1=-128 WHERE c1=-12 OR c2=1;
751 --sorted_result
752 SELECT * FROM t2 WHERE c1=-128;
753
754 # Select using various access methods
755 ## Full table scan
756 --sorted_result
757 SELECT * FROM t2;
758 ## Forward index scan, covering ##
759 --sorted_result
760 SELECT c1 FROM t2;
761 ## Backward index scan, covering ##
762 SELECT c1 FROM t2 ORDER BY c1 DESC;
763 # ref access
764 --sorted_result
765 SELECT * FROM t2 WHERE c1=-128;
766 ## Range access ##
767 SELECT * FROM t2 WHERE c1 <> -128 ORDER BY c1,c2;
768 SELECT * FROM t2 WHERE c1 <> -128 ORDER BY c1,c2 DESC LIMIT 2;
769 SELECT * FROM t2 WHERE c1 > -128 ORDER BY c1,c2 DESC;
770 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1,c2 LIMIT 2;
771 SELECT * FROM t2 WHERE c1 < -128 ORDER BY c1,c2;
772 SELECT * FROM t2 WHERE c1 <= -128 ORDER BY c1,c2 DESC LIMIT 2;
773 SELECT * FROM t2 WHERE c1 <=> -128 ORDER BY c1,c2 DESC;
774 SELECT * FROM t2 WHERE c1 <=> -128 ORDER BY c1,c2 LIMIT 2;
775 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1,c2;
776 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1,c2 DESC LIMIT 2;
777 SELECT * FROM t2 WHERE c1 IN(-128,127) ORDER BY c1,c2 DESC;
778 SELECT * FROM t2 WHERE c1 IN(-128,127) ORDER BY c1,c2 LIMIT 2;
779 SELECT * FROM t2 WHERE c1>=-128 AND c1 <= 127 ORDER BY c1,c2 DESC;
780 SELECT * FROM t2 WHERE c1>=-128 AND c1 <= 127 ORDER BY c1,c2 LIMIT 2;
781 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1;
782 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
783 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
784 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 LIMIT 2;
785
786 --sorted_result
787 SELECT * FROM t2 WHERE c1=-128 OR c2=3;
788
789 # Test multi update with different join methods
790 CREATE TABLE mt1 (a tinyint not null primary key, b tinyint not null, key (b));
791 CREATE TABLE mt2 (a tinyint not null primary key, b tinyint not null, key (b));
792 INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
793 INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
794 # Full join, without key
795 update mt1,mt2 set mt1.a=mt1.a+100;
796 --sorted_result
797 select * from mt1;
798 # unique key
799 update ignore mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
800 --sorted_result
801 select * from mt1;
802 # ref key
803 update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
804 --sorted_result
805 select * from mt1;
806 # Range key (in mt1)
807 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;
808 --sorted_result
809 select * from mt1;
810 --sorted_result
811 select * from mt2;
812 # test for non-updating table which is also used in sub-select
813 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);
814 drop table mt1,mt2;
815
816 # Testing delete using various access methods
817 TRUNCATE TABLE t2;
818 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);
819
820 # Using Index-merge
821 # explain SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
822 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
823 DELETE FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1 LIMIT 2;
824 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
825
826 # Range access
827 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
828 DELETE FROM t2 WHERE c1 >= -128 ORDER BY c1 LIMIT 2;
829 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
830 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
831 DELETE FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC LIMIT 2;
832 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
833 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
834 DELETE FROM t2 WHERE c1 < 0 ORDER BY c1 LIMIT 2;
835 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
836 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
837 DELETE FROM t2 WHERE c1 > -3 ORDER BY c1 DESC LIMIT 2;
838 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
839 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);
840 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
841 DELETE FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1 LIMIT 2;
842 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
843 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
844 DELETE FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
845 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
846 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
847 DELETE FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1 LIMIT 2;
848 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
849 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
850 DELETE FROM t2 WHERE c2 IS NOT NULL ORDER BY c1 LIMIT 2;
851 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
852 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
853 DELETE FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
854 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
855
856 # Muti-table delete
857
858 TRUNCATE TABLE t1;
859 TRUNCATE TABLE t2;
860 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
861 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
862
863 # join(Range checked for each record)
864 --sorted_result
865 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
866 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
867 --sorted_result
868 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
869 TRUNCATE TABLE t1;
870 TRUNCATE TABLE t2;
871 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
872 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
873 --sorted_result
874 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
875 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
876 --sorted_result
877 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
878 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
879
880 # eq-ref join
881 CREATE TABLE t3(c1 TINYINT UNSIGNED NOT NULL PRIMARY KEY, c2 TINYINT SIGNED NULL, c3 INT);
882 CREATE TABLE t4(c1 TINYINT UNSIGNED, c2 INT);
883 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
884 INSERT INTO t4 VALUES(200,1),(150,3);
885 --sorted_result
886 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
887 DELETE t3.*,t4.* FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
888 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
889 DROP TABLE t1,t2,t3,t4;
890
891 ######## Running UPDATE tests for SMALLINT ########
892
893 # Create table
894 CREATE TABLE t1(c1 SMALLINT UNSIGNED NOT NULL, c2 SMALLINT SIGNED NULL, c3 INT, INDEX idx2(c2));
895
896 # Insert into tables
897 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);
898 --sorted_result
899 SELECT * FROM t1;
900
901 # Update order by limit
902 UPDATE t1 SET c1=110 WHERE c2 >-128 ORDER BY c2 LIMIT 2;
903 --sorted_result
904 SELECT * FROM t1 WHERE c1=110;
905
906 # Update with NULL(null to numbers, numbers to null)
907 UPDATE t1 SET c1=c1+1,c2=NULL WHERE c1=101;
908 --sorted_result
909 SELECT * FROM t1 WHERE c1=102 AND c2 IS NULL;
910 UPDATE t1 SET c1=120 WHERE c2 IS NULL;
911 --sorted_result
912 SELECT c1,c2 FROM t1 WHERE c1=120;
913
914 # Update negative value to unsigned col
915 UPDATE IGNORE t1 SET c1=-120 WHERE c2=-102;
916 --sorted_result
917 SELECT c1,c2 FROM t1 WHERE c2=-102;
918
919 # Update range values
920 UPDATE t1 SET c1=0,c2=-32768 WHERE c1=103 AND c2=-103;
921 --sorted_result
922 SELECT * FROM t1 WHERE c1=0 AND c2=-32768;
923 UPDATE t1 SET c1=65535,c2=32767 WHERE c1=104 OR c2=105;
924 --sorted_result
925 SELECT * FROM t1 WHERE c1=65535 AND c2=32767;
926 UPDATE t1 SET c2=0 WHERE c1 IN (1,2);
927 --sorted_result
928 SELECT * FROM t1 WHERE c2=0;
929
930 # Update outside range, would be clipped to closest endpoint
931 INSERT INTO t1 VALUES(106,-106,9),(107,-107,10),(108,-108,11),(109,-109,12),(255,127,13);
932 UPDATE IGNORE t1 SET c1=65536,c2=32768 WHERE c2 BETWEEN -108 AND -106;
933 SELECT COUNT(*) FROM t1 WHERE c1=65536 AND c2=32768 /* no rows */;
934 --sorted_result
935 SELECT * FROM t1 WHERE c1=65535 AND c2=32767;
936 UPDATE IGNORE t1 SET c2=-32769 WHERE c1=109 ORDER BY c1;
937 --sorted_result
938 SELECT c1,c2 FROM t1 WHERE c1=109;
939
940 # Update ignore on bad null error
941 INSERT INTO t1 VALUES(110,-110,14),(111,-111,15);
942 SET SQL_MODE=STRICT_ALL_TABLES;
943 --error ER_BAD_NULL_ERROR
944 UPDATE t1 SET c1=NULL WHERE c2=-110;
945 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-110 /* updates to default value 0 */;
946 --sorted_result
947 SELECT c1,c2 FROM t1 WHERE c2=-110;
948 SET SQL_MODE=DEFAULT;
949 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-111 /* updates to default value 0 */;
950 --sorted_result
951 SELECT * FROM t1 WHERE c1=0;
952
953 # Update with multi-range
954 SELECT * FROM t1 WHERE c2>=-101 AND c1<=101 ORDER BY c2;
955 UPDATE t1 SET c1=c1+1,c2=c2+1 WHERE c2>=-101 AND c1<=101 ORDER BY c2 LIMIT 2;
956 --sorted_result
957 SELECT * FROM t1;
958
959 # Create table with AUTO_INCREMENT Primary Key
960 CREATE TABLE t2(c1 SMALLINT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 SMALLINT UNSIGNED NULL, c3 INT);
961
962 # Update ignore on duplicate key error
963 INSERT INTO t2(c1) VALUES(1),(2),(3),(4),(5);
964 --error ER_DUP_ENTRY
965 UPDATE t2 SET c1=1 WHERE c1=3;
966 UPDATE IGNORE t2 SET c1=1 WHERE c1>=3;
967 --sorted_result
968 SELECT c1 FROM t2;
969
970 # Update the auto_incremented value with boundary values to unsigned/signed field
971 TRUNCATE TABLE t1;
972 ALTER TABLE t1 CHANGE c1 c1 SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(c1);
973 INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
974 TRUNCATE TABLE t2;
975 INSERT INTO t2 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
976 INSERT INTO t1(c2,c3) VALUES(13,14);
977 SELECT c1 FROM t1 WHERE c2=13;
978 INSERT INTO t2(c2,c3) VALUES(13,14);
979 SELECT c1 FROM t2 WHERE c2=13;
980 # Update the unsigned auto_increment field
981 UPDATE t1 SET c1=65535,c2=32767 WHERE c2=13;
982 SELECT c1,c2 FROM t1 ORDER BY c1;
983 UPDATE IGNORE t2 SET c1=0,c2=-32768 WHERE c2=2;
984 SELECT c1,c2 FROM t1 ORDER BY c1;
985 UPDATE t2 SET c2=0 WHERE c2=5;
986 SELECT c1,c2 FROM t1 ORDER BY c1;
987 # Update the signed auto_increment field
988 UPDATE t2 SET c1=32767,c2=65535 WHERE c2=13;
989 SELECT c1,c2 FROM t2 ORDER BY c1;
990 UPDATE t2 SET c1=-32768,c2=0 WHERE c2=2;
991 SELECT c1,c2 FROM t2 ORDER BY c1;
992 UPDATE t2 SET c1=0,c2=0 WHERE c2=5;
993 SELECT c1,c2 FROM t2 ORDER BY c1;
994
995 # Multi table update
996 CREATE TABLE mt1 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
997 CREATE TABLE mt2 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
998 CREATE TABLE mt3 (c1 SMALLINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
999 INSERT INTO mt1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
1000 INSERT INTO mt2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
1001 INSERT INTO mt3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
1002 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;
1003 --sorted_result
1004 SELECT * FROM mt1;
1005 --sorted_result
1006 SELECT * FROM mt2;
1007 --sorted_result
1008 SELECT * FROM mt3;
1009 DROP TABLE mt1,mt2,mt3;
1010
1011 # Update,Select,Delete,Select using various access methods
1012 TRUNCATE TABLE t1;
1013 TRUNCATE TABLE t2;
1014 ALTER TABLE t2 CHANGE c2 c2 SMALLINT UNSIGNED NULL, ADD KEY(c2);
1015 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);
1016 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);
1017
1018 # Update using various methods
1019 # eq-ref join
1020 UPDATE t1,t2 SET t1.c1=50,t1.c2=50,t2.c1=50,t2.c2=50 WHERE t2.c1=t1.c1;
1021 --sorted_result
1022 SELECT * FROM t1,t2 WHERE t1.c1=50 AND t1.c2=50 AND t2.c1=50 AND t2.c2=50;
1023 --sorted_result
1024 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
1025 ALTER TABLE t2 CHANGE c1 c1 SMALLINT SIGNED NOT NULL, DROP PRIMARY KEY;
1026 ALTER TABLE t2 CHANGE c1 c1 SMALLINT SIGNED NOT NULL, ADD KEY(c1);
1027 INSERT INTO t2 VALUES(-32768,0,12),(0,255,13),(32767,65535,14);
1028
1029 # range access
1030 UPDATE t2 SET c2=65535 WHERE c1 <> 0 ORDER BY c1 LIMIT 2;
1031 SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c1;
1032 UPDATE t2 SET c2=65535 WHERE c1 >= 0 ORDER BY c1 DESC LIMIT 2;
1033 SELECT * FROM t2 WHERE c1 >= 0 ORDER BY c1 DESC;
1034 UPDATE t2 SET c2=65535 WHERE c1 <= -9 ORDER BY c1 LIMIT 2;
1035 SELECT * FROM t2 WHERE c1 <= -9 ORDER BY c1;
1036 UPDATE t2 SET c2=65535 WHERE c1 <=> -3 ORDER BY c1 DESC LIMIT 2;
1037 SELECT * FROM t2 WHERE c1 <=> -3 ORDER BY c1;
1038 UPDATE t2 SET c2=65535 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1 LIMIT 2;
1039 SELECT * FROM t2 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1;
1040 UPDATE t2 SET c2=65535 WHERE c1 IN(-7,-8) ORDER BY c1 DESC LIMIT 2;
1041 SELECT * FROM t2 WHERE c1 IN(-7,-8) ORDER BY c1 DESC;
1042 UPDATE t2 SET c2=65535 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
1043 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
1044 UPDATE t2 SET c2=65535 WHERE c1>= -32768 AND c1 < -9 ORDER BY c1 LIMIT 2;
1045 SELECT * FROM t2 WHERE c1>= -32768 AND c1 < -9 ORDER BY c1;
1046
1047 # Using index-merge
1048 UPDATE t2 SET c2=65535 WHERE c1=-2 OR c2=1;
1049 --sorted_result
1050 SELECT * FROM t2 WHERE c2=65535;
1051
1052 # Select using various access methods
1053 ## Full table scan
1054 --sorted_result
1055 SELECT * FROM t2;
1056 ## Forward index scan, covering ##
1057 --sorted_result
1058 SELECT c2 FROM t2;
1059 ## Backward index scan, covering ##
1060 SELECT c2 FROM t2 ORDER BY c2 DESC;
1061 # ref access
1062 --sorted_result
1063 SELECT * FROM t2 WHERE c2=65535;
1064 ## Range access ##
1065 SELECT * FROM t2 WHERE c2 <> 65535 ORDER BY c2,c1;
1066 SELECT * FROM t2 WHERE c2 <> 65535 ORDER BY c2,c1 DESC LIMIT 2;
1067 SELECT * FROM t2 WHERE c2 > 65535 ORDER BY c2,c1 DESC;
1068 SELECT * FROM t2 WHERE c2 >= 65535 ORDER BY c2,c1 LIMIT 2;
1069 SELECT * FROM t2 WHERE c2 < 65535 ORDER BY c2,c1;
1070 SELECT * FROM t2 WHERE c2 <= 65535 ORDER BY c2,c1 DESC LIMIT 2;
1071 SELECT * FROM t2 WHERE c2 <=> 65535 ORDER BY c2,c1 DESC;
1072 SELECT * FROM t2 WHERE c2 <=> 65535 ORDER BY c2,c1 LIMIT 2;
1073 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 65535 ORDER BY c2,c1;
1074 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 65535 ORDER BY c2,c1 DESC LIMIT 2;
1075 SELECT * FROM t2 WHERE c2 IN(0,65535) ORDER BY c2,c1 DESC;
1076 SELECT * FROM t2 WHERE c2 IN(0,65535) ORDER BY c2,c1 LIMIT 2;
1077 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 65535 ORDER BY c2,c1 DESC;
1078 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 65535 ORDER BY c2,c1 LIMIT 2;
1079 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1;
1080 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
1081 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1082 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 LIMIT 2;
1083
1084 --sorted_result
1085 SELECT * FROM t2 WHERE c2=65535 OR c1=-4;
1086
1087 # Test multi update with different join methods
1088 CREATE TABLE mt1 (a smallint not null primary key, b smallint not null, key (b));
1089 CREATE TABLE mt2 (a smallint not null primary key, b smallint not null, key (b));
1090 INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1091 INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1092 # Full join, without key
1093 update mt1,mt2 set mt1.a=mt1.a+100;
1094 --sorted_result
1095 select * from mt1;
1096 # unique key
1097 update mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
1098 --sorted_result
1099 select * from mt1;
1100 # ref key
1101 update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
1102 --sorted_result
1103 select * from mt1;
1104 # Range key (in mt1)
1105 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;
1106 --sorted_result
1107 select * from mt1;
1108 --sorted_result
1109 select * from mt2;
1110 # test for non-updating table which is also used in sub-select
1111 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);
1112 drop table mt1,mt2;
1113
1114 # Testing delete using various access methods
1115 TRUNCATE TABLE t2;
1116 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);
1117
1118 # Using Index-merge
1119 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
1120 DELETE FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1 LIMIT 2;
1121 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
1122
1123 # Range access
1124 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
1125 DELETE FROM t2 WHERE c1 >= -128 ORDER BY c1 LIMIT 2;
1126 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
1127 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
1128 DELETE FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC LIMIT 2;
1129 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
1130 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
1131 DELETE FROM t2 WHERE c1 < 0 ORDER BY c1 LIMIT 2;
1132 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
1133 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
1134 DELETE FROM t2 WHERE c1 > -3 ORDER BY c1 DESC LIMIT 2;
1135 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
1136 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
1137 DELETE FROM t2 WHERE c1=-3 OR c2=2;
1138 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
1139 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);
1140 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
1141 DELETE FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1 LIMIT 2;
1142 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
1143 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1144 DELETE FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1145 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1146 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
1147 DELETE FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1 LIMIT 2;
1148 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
1149 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
1150 DELETE FROM t2 WHERE c2 IS NOT NULL ORDER BY c1 LIMIT 2;
1151 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
1152 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1153 DELETE FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
1154 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1155
1156 # Muti-table delete
1157
1158 TRUNCATE TABLE t1;
1159 TRUNCATE TABLE t2;
1160 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
1161 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
1162
1163 # join(Range checked for each record)
1164 --sorted_result
1165 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1166 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1167 --sorted_result
1168 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1169 TRUNCATE TABLE t1;
1170 TRUNCATE TABLE t2;
1171 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
1172 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
1173 --sorted_result
1174 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1175 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
1176 --sorted_result
1177 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1178 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
1179
1180 # eq-ref join
1181 CREATE TABLE t3(c1 SMALLINT UNSIGNED NOT NULL PRIMARY KEY, c2 SMALLINT SIGNED NULL, c3 INT);
1182 CREATE TABLE t4(c1 SMALLINT UNSIGNED, c2 INT);
1183 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
1184 INSERT INTO t4 VALUES(200,1),(150,3);
1185 --sorted_result
1186 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1187 DELETE t3.*,t4.* FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1188 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1189 DROP TABLE t1,t2,t3,t4;
1190
1191 ######## Running UPDATE tests for MEDIUMINT ########
1192
1193 # Create table
1194 CREATE TABLE t1(c1 MEDIUMINT UNSIGNED NOT NULL, c2 MEDIUMINT SIGNED NULL, c3 INT, INDEX idx2(c2));
1195
1196 # Insert into tables
1197 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);
1198 --sorted_result
1199 SELECT * FROM t1;
1200
1201 # Update order by limit
1202 UPDATE t1 SET c1=110 WHERE c2 >-128 ORDER BY c2 LIMIT 2;
1203 --sorted_result
1204 SELECT * FROM t1 WHERE c1=110;
1205
1206 # Update with NULL(null to numbers, numbers to null)
1207 UPDATE t1 SET c1=c1+1,c2=NULL WHERE c1=101;
1208 --sorted_result
1209 SELECT * FROM t1 WHERE c1=102 AND c2 IS NULL;
1210 UPDATE t1 SET c1=120 WHERE c2 IS NULL;
1211 --sorted_result
1212 SELECT c1,c2 FROM t1 WHERE c1=120;
1213
1214 # Update negative value to unsigned col
1215 UPDATE IGNORE t1 SET c1=-120 WHERE c2=-102;
1216 --sorted_result
1217 SELECT c1,c2 FROM t1 WHERE c2=-102;
1218
1219 # Update range values
1220 UPDATE t1 SET c1=0,c2=-8388608 WHERE c1=103 AND c2=-103;
1221 --sorted_result
1222 SELECT * FROM t1 WHERE c1=0 AND c2=-8388608;
1223 UPDATE t1 SET c1=16777215,c2=8388607 WHERE c1=104 OR c2=105;
1224 --sorted_result
1225 SELECT * FROM t1 WHERE c1=16777215 AND c2=8388607;
1226 UPDATE t1 SET c2=0 WHERE c1 IN (1,2);
1227 --sorted_result
1228 SELECT * FROM t1 WHERE c2=0;
1229
1230 # Update outside range, would be clipped to closest endpoint
1231 INSERT INTO t1 VALUES(106,-106,9),(107,-107,10),(108,-108,11),(109,-109,12),(255,127,13);
1232 UPDATE IGNORE t1 SET c1=16777216,c2=8388608 WHERE c2 BETWEEN -108 AND -106;
1233 SELECT COUNT(*) FROM t1 WHERE c1=16777216 AND c2=8388608 /* no rows */;
1234 --sorted_result
1235 SELECT * FROM t1 WHERE c1=16777215 AND c2=8388607;
1236 UPDATE IGNORE t1 SET c2=-8388609 WHERE c1=109 ORDER BY c1;
1237 --sorted_result
1238 SELECT c1,c2 FROM t1 WHERE c1=109;
1239
1240 # Update ignore on bad null error
1241 INSERT INTO t1 VALUES(110,-110,14),(111,-111,15);
1242 SET SQL_MODE=STRICT_ALL_TABLES;
1243 --error ER_BAD_NULL_ERROR
1244 UPDATE t1 SET c1=NULL WHERE c2=-110;
1245 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-110 /* updates to default value 0 */;
1246 --sorted_result
1247 SELECT c1,c2 FROM t1 WHERE c2=-110;
1248 SET SQL_MODE=DEFAULT;
1249 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-111 /* updates to default value 0 */;
1250 --sorted_result
1251 SELECT * FROM t1 WHERE c1=0;
1252
1253 # Update with multi-range
1254 SELECT * FROM t1 WHERE c2>=-101 AND c1<=101 ORDER BY c2;
1255 UPDATE t1 SET c1=c1+1,c2=c2+1 WHERE c2>=-101 AND c1<=101 ORDER BY c2 LIMIT 2;
1256 --sorted_result
1257 SELECT * FROM t1;
1258
1259 # Create table with AUTO_INCREMENT Primary Key
1260 CREATE TABLE t2(c1 MEDIUMINT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 MEDIUMINT UNSIGNED NULL, c3 INT);
1261
1262 # Update ignore on duplicate key error
1263 INSERT INTO t2(c1) VALUES(1),(2),(3),(4),(5);
1264 --error ER_DUP_ENTRY
1265 UPDATE t2 SET c1=1 WHERE c1=3;
1266 UPDATE IGNORE t2 SET c1=1 WHERE c1>=3;
1267 --sorted_result
1268 SELECT c1 FROM t2;
1269
1270 # Update the auto_incremented value with boundary values to unsigned/signed field
1271 TRUNCATE TABLE t1;
1272 ALTER TABLE t1 CHANGE c1 c1 MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(c1);
1273 INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
1274 TRUNCATE TABLE t2;
1275 INSERT INTO t2 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
1276 INSERT INTO t1(c2,c3) VALUES(13,14);
1277 SELECT c1 FROM t1 WHERE c2=13;
1278 INSERT INTO t2(c2,c3) VALUES(13,14);
1279 SELECT c1 FROM t2 WHERE c2=13;
1280 # Update the unsigned auto_increment field
1281 UPDATE t1 SET c1=16777215,c2=8388607 WHERE c2=13;
1282 SELECT c1,c2 FROM t1 ORDER BY c1;
1283 UPDATE IGNORE t2 SET c1=0,c2=-8388608 WHERE c2=2;
1284 SELECT c1,c2 FROM t1 ORDER BY c1;
1285 UPDATE t2 SET c2=0 WHERE c2=5;
1286 SELECT c1,c2 FROM t1 ORDER BY c1;
1287 # Update the signed auto_increment field
1288 UPDATE t2 SET c1=8388607,c2=16777215 WHERE c2=13;
1289 SELECT c1,c2 FROM t2 ORDER BY c1;
1290 UPDATE t2 SET c1=-8388608,c2=0 WHERE c2=2;
1291 SELECT c1,c2 FROM t2 ORDER BY c1;
1292 UPDATE t2 SET c1=0,c2=0 WHERE c2=5;
1293 SELECT c1,c2 FROM t2 ORDER BY c1;
1294
1295 # Multi table update
1296 CREATE TABLE mt1 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
1297 CREATE TABLE mt2 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
1298 CREATE TABLE mt3 (c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
1299 INSERT INTO mt1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
1300 INSERT INTO mt2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
1301 INSERT INTO mt3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
1302 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;
1303 --sorted_result
1304 SELECT * FROM mt1;
1305 --sorted_result
1306 SELECT * FROM mt2;
1307 --sorted_result
1308 SELECT * FROM mt3;
1309 DROP TABLE mt1,mt2,mt3;
1310
1311 # Update,Select,Delete,Select using various access methods
1312 TRUNCATE TABLE t1;
1313 TRUNCATE TABLE t2;
1314 ALTER TABLE t2 CHANGE c2 c2 MEDIUMINT UNSIGNED NULL, ADD KEY(c2);
1315 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);
1316 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);
1317
1318 # Update using various methods
1319 # eq-ref join
1320 UPDATE t1,t2 SET t1.c1=50,t1.c2=50,t2.c1=50,t2.c2=50 WHERE t2.c1=t1.c1;
1321 --sorted_result
1322 SELECT * FROM t1,t2 WHERE t1.c1=50 AND t1.c2=50 AND t2.c1=50 AND t2.c2=50;
1323 --sorted_result
1324 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
1325 ALTER TABLE t2 CHANGE c1 c1 MEDIUMINT SIGNED NOT NULL, DROP PRIMARY KEY;
1326 ALTER TABLE t2 CHANGE c1 c1 MEDIUMINT SIGNED NOT NULL, ADD KEY(c1);
1327 INSERT INTO t2 VALUES(-8388608,0,12),(0,255,13),(8388607,16777215,14);
1328
1329 # range access
1330 UPDATE t2 SET c2=16777215 WHERE c1 <> 0 ORDER BY c1 LIMIT 2;
1331 SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c1;
1332 UPDATE t2 SET c2=16777215 WHERE c1 >= 0 ORDER BY c1 DESC LIMIT 2;
1333 SELECT * FROM t2 WHERE c1 >= 0 ORDER BY c1 DESC;
1334 UPDATE t2 SET c2=16777215 WHERE c1 <= -9 ORDER BY c1 LIMIT 2;
1335 SELECT * FROM t2 WHERE c1 <= -9 ORDER BY c1;
1336 UPDATE t2 SET c2=16777215 WHERE c1 <=> -3 ORDER BY c1 DESC LIMIT 2;
1337 SELECT * FROM t2 WHERE c1 <=> -3 ORDER BY c1;
1338 UPDATE t2 SET c2=16777215 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1 LIMIT 2;
1339 SELECT * FROM t2 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1;
1340 UPDATE t2 SET c2=16777215 WHERE c1 IN(-7,-8) ORDER BY c1 DESC LIMIT 2;
1341 SELECT * FROM t2 WHERE c1 IN(-7,-8) ORDER BY c1 DESC;
1342 UPDATE t2 SET c2=16777215 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
1343 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
1344 UPDATE t2 SET c2=16777215 WHERE c1>= -8388608 AND c1 < -9 ORDER BY c1 LIMIT 2;
1345 SELECT * FROM t2 WHERE c1>= -8388608 AND c1 < -9 ORDER BY c1;
1346
1347 # Using index-merge
1348 UPDATE t2 SET c2=16777215 WHERE c1=-2 OR c2=1;
1349 --sorted_result
1350 SELECT * FROM t2 WHERE c2=16777215;
1351
1352 # Select using various access methods
1353 ## Full table scan
1354 --sorted_result
1355 SELECT * FROM t2;
1356 ## Forward index scan, covering ##
1357 --sorted_result
1358 SELECT c2 FROM t2;
1359 ## Backward index scan, covering ##
1360 SELECT c2 FROM t2 ORDER BY c2 DESC;
1361 # ref access
1362 --sorted_result
1363 SELECT * FROM t2 WHERE c2=16777215;
1364 ## Range access ##
1365 SELECT * FROM t2 WHERE c2 <> 16777215 ORDER BY c2,c1;
1366 SELECT * FROM t2 WHERE c2 <> 16777215 ORDER BY c2,c1 DESC LIMIT 2;
1367 SELECT * FROM t2 WHERE c2 > 16777215 ORDER BY c2,c1 DESC;
1368 SELECT * FROM t2 WHERE c2 >= 16777215 ORDER BY c2,c1 LIMIT 2;
1369 SELECT * FROM t2 WHERE c2 < 16777215 ORDER BY c2,c1;
1370 SELECT * FROM t2 WHERE c2 <= 16777215 ORDER BY c2,c1 DESC LIMIT 2;
1371 SELECT * FROM t2 WHERE c2 <=> 16777215 ORDER BY c2,c1 DESC;
1372 SELECT * FROM t2 WHERE c2 <=> 16777215 ORDER BY c2,c1 LIMIT 2;
1373 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 16777215 ORDER BY c2,c1;
1374 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 16777215 ORDER BY c2,c1 DESC LIMIT 2;
1375 SELECT * FROM t2 WHERE c2 IN(0,16777215) ORDER BY c2,c1 DESC;
1376 SELECT * FROM t2 WHERE c2 IN(0,16777215) ORDER BY c2,c1 LIMIT 2;
1377 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 16777215 ORDER BY c2,c1 DESC;
1378 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 16777215 ORDER BY c2,c1 LIMIT 2;
1379 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1;
1380 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
1381 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1382 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 LIMIT 2;
1383
1384 --sorted_result
1385 SELECT * FROM t2 WHERE c2=16777215 OR c1=-4;
1386
1387 # Test multi update with different join methods
1388 CREATE TABLE mt1 (a mediumint not null primary key, b mediumint not null, key (b));
1389 CREATE TABLE mt2 (a mediumint not null primary key, b mediumint not null, key (b));
1390 INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1391 INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1392 # Full join, without key
1393 update mt1,mt2 set mt1.a=mt1.a+100;
1394 --sorted_result
1395 select * from mt1;
1396 # unique key
1397 update mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
1398 --sorted_result
1399 select * from mt1;
1400 # ref key
1401 update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
1402 --sorted_result
1403 select * from mt1;
1404 # Range key (in mt1)
1405 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;
1406 --sorted_result
1407 select * from mt1;
1408 --sorted_result
1409 select * from mt2;
1410 # test for non-updating table which is also used in sub-select
1411 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);
1412 drop table mt1,mt2;
1413
1414 # Testing delete using various access methods
1415 TRUNCATE TABLE t2;
1416 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);
1417
1418 # Using Index-merge
1419 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
1420 DELETE FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1 LIMIT 2;
1421 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
1422
1423 # Range access
1424 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
1425 DELETE FROM t2 WHERE c1 >= -128 ORDER BY c1 LIMIT 2;
1426 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
1427 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
1428 DELETE FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC LIMIT 2;
1429 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
1430 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
1431 DELETE FROM t2 WHERE c1 < 0 ORDER BY c1 LIMIT 2;
1432 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
1433 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
1434 DELETE FROM t2 WHERE c1 > -3 ORDER BY c1 DESC LIMIT 2;
1435 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
1436 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
1437 DELETE FROM t2 WHERE c1=-3 OR c2=2;
1438 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
1439 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);
1440 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
1441 DELETE FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1 LIMIT 2;
1442 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
1443 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1444 DELETE FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1445 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1446 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
1447 DELETE FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1 LIMIT 2;
1448 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
1449 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
1450 DELETE FROM t2 WHERE c2 IS NOT NULL ORDER BY c1 LIMIT 2;
1451 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
1452 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1453 DELETE FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
1454 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1455
1456 # Muti-table delete
1457
1458 TRUNCATE TABLE t1;
1459 TRUNCATE TABLE t2;
1460 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
1461 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
1462
1463 # join(Range checked for each record)
1464 --sorted_result
1465 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1466 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1467 --sorted_result
1468 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1469 TRUNCATE TABLE t1;
1470 TRUNCATE TABLE t2;
1471 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
1472 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
1473 --sorted_result
1474 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1475 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
1476 --sorted_result
1477 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1478 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
1479
1480 # eq-ref join
1481 CREATE TABLE t3(c1 MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, c2 MEDIUMINT SIGNED NULL, c3 INT);
1482 CREATE TABLE t4(c1 MEDIUMINT UNSIGNED, c2 INT);
1483 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
1484 INSERT INTO t4 VALUES(200,1),(150,3);
1485 --sorted_result
1486 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1487 DELETE t3.*,t4.* FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1488 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1489 DROP TABLE t1,t2,t3,t4;
1490
1491 ######## Running UPDATE tests for BIGINT ########
1492
1493 # Create table
1494 CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL, c2 BIGINT SIGNED NULL, c3 INT, INDEX idx2(c2));
1495
1496 # Insert into tables
1497 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);
1498 --sorted_result
1499 SELECT * FROM t1;
1500
1501 # Update order by limit
1502 UPDATE t1 SET c1=110 WHERE c2 >-128 ORDER BY c2 LIMIT 2;
1503 --sorted_result
1504 SELECT * FROM t1 WHERE c1=110;
1505
1506 # Update with NULL(null to numbers, numbers to null)
1507 UPDATE t1 SET c1=c1+1,c2=NULL WHERE c1=101;
1508 --sorted_result
1509 SELECT * FROM t1 WHERE c1=102 AND c2 IS NULL;
1510 UPDATE t1 SET c1=120 WHERE c2 IS NULL;
1511 --sorted_result
1512 SELECT c1,c2 FROM t1 WHERE c1=120;
1513
1514 # Update negative value to unsigned col
1515 UPDATE IGNORE t1 SET c1=-120 WHERE c2=-102;
1516 --sorted_result
1517 SELECT c1,c2 FROM t1 WHERE c2=-102;
1518
1519 # Update range values
1520 UPDATE t1 SET c1=0,c2=-9223372036854775808 WHERE c1=103 AND c2=-103;
1521 --sorted_result
1522 SELECT * FROM t1 WHERE c1=0 AND c2=-9223372036854775808;
1523 UPDATE t1 SET c1=18446744073709551615,c2=9223372036854775807 WHERE c1=104 OR c2=105;
1524 --sorted_result
1525 SELECT * FROM t1 WHERE c1=18446744073709551615 AND c2=9223372036854775807;
1526 UPDATE t1 SET c2=0 WHERE c1 IN (1,2);
1527 --sorted_result
1528 SELECT * FROM t1 WHERE c2=0;
1529
1530 # Update outside range, would be clipped to closest endpoint
1531 INSERT INTO t1 VALUES(106,-106,9),(107,-107,10),(108,-108,11),(109,-109,12),(255,127,13);
1532 UPDATE IGNORE t1 SET c1=18446744073709551616,c2=9223372036854775808 WHERE c2 BETWEEN -108 AND -106;
1533 SELECT COUNT(*) FROM t1 WHERE c1=18446744073709551616 AND c2=9223372036854775808 /* no rows */;
1534 --sorted_result
1535 SELECT * FROM t1 WHERE c1=18446744073709551615 AND c2=9223372036854775807;
1536 UPDATE IGNORE t1 SET c2=-9223372036854775809 WHERE c1=109 ORDER BY c1;
1537 --sorted_result
1538 SELECT c1,c2 FROM t1 WHERE c1=109;
1539
1540 # Update ignore on bad null error
1541 INSERT INTO t1 VALUES(110,-110,14),(111,-111,15);
1542 SET SQL_MODE=STRICT_ALL_TABLES;
1543 --error ER_BAD_NULL_ERROR
1544 UPDATE t1 SET c1=NULL WHERE c2=-110;
1545 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-110 /* updates to default value 0 */;
1546 --sorted_result
1547 SELECT c1,c2 FROM t1 WHERE c2=-110;
1548 SET SQL_MODE=DEFAULT;
1549 UPDATE IGNORE t1 SET c1=NULL WHERE c2=-111 /* updates to default value 0 */;
1550 --sorted_result
1551 SELECT * FROM t1 WHERE c1=0;
1552
1553 # Update with multi-range
1554 SELECT * FROM t1 WHERE c2>=-101 AND c1<=101 ORDER BY c2;
1555 UPDATE t1 SET c1=c1+1,c2=c2+1 WHERE c2>=-101 AND c1<=101 ORDER BY c2 LIMIT 2;
1556 --sorted_result
1557 SELECT * FROM t1;
1558
1559 # Create table with AUTO_INCREMENT Primary Key
1560 CREATE TABLE t2(c1 BIGINT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 BIGINT UNSIGNED NULL, c3 INT);
1561
1562 # Update ignore on duplicate key error
1563 INSERT INTO t2(c1) VALUES(1),(2),(3),(4),(5);
1564 --error ER_DUP_ENTRY
1565 UPDATE t2 SET c1=1 WHERE c1=3;
1566 UPDATE IGNORE t2 SET c1=1 WHERE c1>=3;
1567 --sorted_result
1568 SELECT c1 FROM t2;
1569
1570 # Update the auto_incremented value with boundary values to unsigned/signed field
1571 TRUNCATE TABLE t1;
1572 ALTER TABLE t1 CHANGE c1 c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(c1);
1573 INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
1574 TRUNCATE TABLE t2;
1575 INSERT INTO t2 VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
1576 INSERT INTO t1(c2,c3) VALUES(13,14);
1577 SELECT c1 FROM t1 WHERE c2=13;
1578 INSERT INTO t2(c2,c3) VALUES(13,14);
1579 SELECT c1 FROM t2 WHERE c2=13;
1580 # Update the unsigned auto_increment field
1581 UPDATE t1 SET c1=18446744073709551615,c2=9223372036854775807 WHERE c2=13;
1582 SELECT c1,c2 FROM t1 ORDER BY c1;
1583 UPDATE IGNORE t2 SET c1=0,c2=-9223372036854775808 WHERE c2=2;
1584 SELECT c1,c2 FROM t1 ORDER BY c1;
1585 UPDATE t2 SET c2=0 WHERE c2=5;
1586 SELECT c1,c2 FROM t1 ORDER BY c1;
1587 # Update the signed auto_increment field
1588 UPDATE t2 SET c1=9223372036854775807,c2=18446744073709551615 WHERE c2=13;
1589 SELECT c1,c2 FROM t2 ORDER BY c1;
1590 UPDATE t2 SET c1=-9223372036854775808,c2=0 WHERE c2=2;
1591 SELECT c1,c2 FROM t2 ORDER BY c1;
1592 UPDATE t2 SET c1=0,c2=0 WHERE c2=5;
1593 SELECT c1,c2 FROM t2 ORDER BY c1;
1594
1595 # Multi table update
1596 CREATE TABLE mt1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
1597 CREATE TABLE mt2 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
1598 CREATE TABLE mt3 (c1 BIGINT NOT NULL PRIMARY KEY, c2 INTEGER, KEY(c2));
1599 INSERT INTO mt1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5);
1600 INSERT INTO mt2 VALUES(11,1),(12,1),(13,1),(14,2),(15,6);
1601 INSERT INTO mt3 VALUES(21,11),(22,11),(23,13),(24,14),(25,15);
1602 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;
1603 --sorted_result
1604 SELECT * FROM mt1;
1605 --sorted_result
1606 SELECT * FROM mt2;
1607 --sorted_result
1608 SELECT * FROM mt3;
1609 DROP TABLE mt1,mt2,mt3;
1610
1611 # Update,Select,Delete,Select using various access methods
1612 TRUNCATE TABLE t1;
1613 TRUNCATE TABLE t2;
1614 ALTER TABLE t2 CHANGE c2 c2 BIGINT UNSIGNED NULL, ADD KEY(c2);
1615 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);
1616 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);
1617
1618 # Update using various methods
1619 # eq-ref join
1620 UPDATE t1,t2 SET t1.c1=50,t1.c2=50,t2.c1=50,t2.c2=50 WHERE t2.c1=t1.c1;
1621 --sorted_result
1622 SELECT * FROM t1,t2 WHERE t1.c1=50 AND t1.c2=50 AND t2.c1=50 AND t2.c2=50;
1623 --sorted_result
1624 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
1625 ALTER TABLE t2 CHANGE c1 c1 BIGINT SIGNED NOT NULL, DROP PRIMARY KEY;
1626 ALTER TABLE t2 CHANGE c1 c1 BIGINT SIGNED NOT NULL, ADD KEY(c1);
1627 INSERT INTO t2 VALUES(-9223372036854775808,0,12),(0,255,13),(9223372036854775807,18446744073709551615,14);
1628
1629 # range access
1630 UPDATE t2 SET c2=18446744073709551615 WHERE c1 <> 0 ORDER BY c1 LIMIT 2;
1631 SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c1;
1632 UPDATE t2 SET c2=18446744073709551615 WHERE c1 >= 0 ORDER BY c1 DESC LIMIT 2;
1633 SELECT * FROM t2 WHERE c1 >= 0 ORDER BY c1 DESC;
1634 UPDATE t2 SET c2=18446744073709551615 WHERE c1 <= -9 ORDER BY c1 LIMIT 2;
1635 SELECT * FROM t2 WHERE c1 <= -9 ORDER BY c1;
1636 UPDATE t2 SET c2=18446744073709551615 WHERE c1 <=> -3 ORDER BY c1 DESC LIMIT 2;
1637 SELECT * FROM t2 WHERE c1 <=> -3 ORDER BY c1;
1638 UPDATE t2 SET c2=18446744073709551615 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1 LIMIT 2;
1639 SELECT * FROM t2 WHERE c1 BETWEEN -6 AND -4 ORDER BY c1;
1640 UPDATE t2 SET c2=18446744073709551615 WHERE c1 IN(-7,-8) ORDER BY c1 DESC LIMIT 2;
1641 SELECT * FROM t2 WHERE c1 IN(-7,-8) ORDER BY c1 DESC;
1642 UPDATE t2 SET c2=18446744073709551615 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
1643 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
1644 UPDATE t2 SET c2=18446744073709551615 WHERE c1>= -9223372036854775808 AND c1 < -9 ORDER BY c1 LIMIT 2;
1645 SELECT * FROM t2 WHERE c1>= -9223372036854775808 AND c1 < -9 ORDER BY c1;
1646
1647 # Using index-merge
1648 UPDATE t2 SET c2=18446744073709551615 WHERE c1=-2 OR c2=1;
1649 --sorted_result
1650 SELECT * FROM t2 WHERE c2=18446744073709551615;
1651
1652 # Select using various access methods
1653 ## Full table scan
1654 --sorted_result
1655 SELECT * FROM t2;
1656 ## Forward index scan, covering ##
1657 --sorted_result
1658 SELECT c2 FROM t2;
1659 ## Backward index scan, covering ##
1660 SELECT c2 FROM t2 ORDER BY c2 DESC;
1661 # ref access
1662 --sorted_result
1663 SELECT * FROM t2 WHERE c2=18446744073709551615;
1664 ## Range access ##
1665 SELECT * FROM t2 WHERE c2 <> 18446744073709551615 ORDER BY c2,c1;
1666 SELECT * FROM t2 WHERE c2 <> 18446744073709551615 ORDER BY c2,c1 DESC LIMIT 2;
1667 SELECT * FROM t2 WHERE c2 > 18446744073709551615 ORDER BY c2,c1 DESC;
1668 SELECT * FROM t2 WHERE c2 >= 18446744073709551615 ORDER BY c2,c1 LIMIT 2;
1669 SELECT * FROM t2 WHERE c2 < 18446744073709551615 ORDER BY c2,c1;
1670 SELECT * FROM t2 WHERE c2 <= 18446744073709551615 ORDER BY c2,c1 DESC LIMIT 2;
1671 SELECT * FROM t2 WHERE c2 <=> 18446744073709551615 ORDER BY c2,c1 DESC;
1672 SELECT * FROM t2 WHERE c2 <=> 18446744073709551615 ORDER BY c2,c1 LIMIT 2;
1673 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 18446744073709551615 ORDER BY c2,c1;
1674 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 18446744073709551615 ORDER BY c2,c1 DESC LIMIT 2;
1675 SELECT * FROM t2 WHERE c2 IN(0,18446744073709551615) ORDER BY c2,c1 DESC;
1676 SELECT * FROM t2 WHERE c2 IN(0,18446744073709551615) ORDER BY c2,c1 LIMIT 2;
1677 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 18446744073709551615 ORDER BY c2,c1 DESC;
1678 SELECT * FROM t2 WHERE c2 >=0 AND c2 <= 18446744073709551615 ORDER BY c2,c1 LIMIT 2;
1679 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1;
1680 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
1681 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1682 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c2,c1 LIMIT 2;
1683
1684 --sorted_result
1685 SELECT * FROM t2 WHERE c2=18446744073709551615 OR c1=-4;
1686
1687 # Test multi update with different join methods
1688 CREATE TABLE mt1 (a bigint not null primary key, b bigint not null, key (b));
1689 CREATE TABLE mt2 (a bigint not null primary key, b bigint not null, key (b));
1690 INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1691 INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
1692 # Full join, without key
1693 update mt1,mt2 set mt1.a=mt1.a+100;
1694 --sorted_result
1695 select * from mt1;
1696 # unique key
1697 update mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
1698 --sorted_result
1699 select * from mt1;
1700 # ref key
1701 update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
1702 --sorted_result
1703 select * from mt1;
1704 # Range key (in mt1)
1705 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;
1706 --sorted_result
1707 select * from mt1;
1708 --sorted_result
1709 select * from mt2;
1710 # test for non-updating table which is also used in sub-select
1711 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);
1712 drop table mt1,mt2;
1713
1714 # Testing delete using various access methods
1715 TRUNCATE TABLE t2;
1716 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);
1717
1718 # Using Index-merge
1719 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
1720 DELETE FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1 LIMIT 2;
1721 SELECT * FROM t2 WHERE c1=127 OR c2=3 ORDER BY c1;
1722
1723 # Range access
1724 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
1725 DELETE FROM t2 WHERE c1 >= -128 ORDER BY c1 LIMIT 2;
1726 SELECT * FROM t2 WHERE c1 >= -128 ORDER BY c1;
1727 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
1728 DELETE FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC LIMIT 2;
1729 SELECT * FROM t2 WHERE c1 <= 127 ORDER BY c1 DESC;
1730 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
1731 DELETE FROM t2 WHERE c1 < 0 ORDER BY c1 LIMIT 2;
1732 SELECT * FROM t2 WHERE c1 < 0 ORDER BY c1;
1733 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
1734 DELETE FROM t2 WHERE c1 > -3 ORDER BY c1 DESC LIMIT 2;
1735 SELECT * FROM t2 WHERE c1 > -3 ORDER BY c1 DESC;
1736 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
1737 DELETE FROM t2 WHERE c1=-3 OR c2=2;
1738 SELECT * FROM t2 WHERE c1=-3 OR c2=2;
1739 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);
1740 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
1741 DELETE FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1 LIMIT 2;
1742 SELECT * FROM t2 WHERE c1 BETWEEN -128 AND 127 ORDER BY c1;
1743 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1744 DELETE FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1745 SELECT * FROM t2 WHERE c1 IN(-2,0) ORDER BY c1 DESC;
1746 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
1747 DELETE FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1 LIMIT 2;
1748 SELECT * FROM t2 WHERE c1 >= -6 AND c2 < 3 ORDER BY c1;
1749 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
1750 DELETE FROM t2 WHERE c2 IS NOT NULL ORDER BY c1 LIMIT 2;
1751 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1;
1752 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1753 DELETE FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC LIMIT 2;
1754 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1755
1756 # Muti-table delete
1757 #
1758 TRUNCATE TABLE t1;
1759 TRUNCATE TABLE t2;
1760 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
1761 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
1762
1763 # join(Range checked for each record)
1764 --sorted_result
1765 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1766 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1767 --sorted_result
1768 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2 OR t2.c2=t1.c1 OR t2.c2<=>t1.c2;
1769 TRUNCATE TABLE t1;
1770 TRUNCATE TABLE t2;
1771 INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(2,-128,4),(3,NULL,5);
1772 INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-128,2,4),(-1,NULL,5);
1773 --sorted_result
1774 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1775 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
1776 --sorted_result
1777 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1778 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
1779
1780 # eq-ref join
1781 CREATE TABLE t3(c1 BIGINT UNSIGNED NOT NULL PRIMARY KEY, c2 BIGINT SIGNED NULL, c3 INT);
1782 CREATE TABLE t4(c1 BIGINT UNSIGNED, c2 INT);
1783 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
1784 INSERT INTO t4 VALUES(200,1),(150,3);
1785 --sorted_result
1786 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1787 DELETE t3.*,t4.* FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1788 SELECT * FROM t3,t4 WHERE t3.c1=t4.c1 AND t3.c3=t4.c2;
1789 DROP TABLE t1,t2,t3,t4;
1790