Debian Codegraph @

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

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