Debian Codegraph @

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

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2,t3,t4;
3 --enable_warnings
4 SET sql_mode='NO_ENGINE_SUBSTITUTION';
5
6 ######## Running UPDATE tests for DECIMAL ########
7
8 # Create tables
9 CREATE TABLE t1(c1 DECIMAL(10,5) UNSIGNED NOT NULL, c2 DECIMAL(10,5) SIGNED NULL, c3 DECIMAL, c4 INT, UNIQUE INDEX idx(c1,c4));
10
11 # Inserting data into table t1
12
13 # Trailing zeroes are not stripped, D=5
14 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
15
16 # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped;
17 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
18
19 # c1, c2, c3 will be rounded automatically
20 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
21
22 # Inserting maximum values
23 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
24
25 # Test insert leading zero, +/- signs, overflow handling
26 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
27 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
28 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
29
30 # Inserting in scientific notations
31 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
32 #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
33
34 --sorted_result
35 SELECT * FROM t1;
36
37 # UPDATE starts here
38
39 # Update order by limit
40 UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
41 --sorted_result
42 SELECT c1,c2 FROM t1;
43
44 # Update with arithmetic operations
45 UPDATE t1 SET c1=c2+c3 WHERE c3 < 10000;
46 --sorted_result
47 SELECT * FROM t1;
48 UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
49 --sorted_result
50 SELECT * FROM t1;
51
52 # Update with NULL ( NULL to number & number to NULL)
53 UPDATE t1 SET c2=NULL WHERE c4=4;
54 --sorted_result
55 SELECT * FROM t1;
56 UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
57 --sorted_result
58 SELECT * FROM t1;
59
60 # Update negative value to unsigned column
61 UPDATE t1 SET c1=-1.0e+2 WHERE c4=2;
62 SHOW WARNINGS;
63 --sorted_result
64 SELECT * FROM t1;
65 UPDATE t1 SET c1=1.0e+20 WHERE c4=2;
66 SHOW WARNINGS;
67 --sorted_result
68 SELECT * FROM t1;
69
70 # Update range values
71 UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
72 --sorted_result
73 SELECT * FROM t1;
74 UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
75 --sorted_result
76 SELECT * FROM t1 WHERE c1=1.2e+2;
77 UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
78 --sorted_result
79 SELECT * FROM t1;
80
81 # Update outside range would be clipped to closest endpoints
82 UPDATE t1 SET c1=99999.999999 WHERE c4=7;
83 --sorted_result
84 SELECT c1,c4 FROM t1;
85 UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
86 --sorted_result
87 SELECT c2,c4 FROM t1;
88
89 # Update ignore on bad null error
90 SELECT c1 FROM t1 WHERE c1>1000;
91 SET SQL_MODE=STRICT_ALL_TABLES;
92 --error ER_BAD_NULL_ERROR
93 UPDATE t1 SET c1=NULL WHERE c1>1000;
94 UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
95 --sorted_result
96 SELECT c1 from t1 WHERE c1>1000;
97 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
98
99 TRUNCATE t1;
100 INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9);
101 --sorted_result
102 SELECT * FROM t1;
103
104 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
105 UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
106 --sorted_result
107 SELECT * FROM t1;
108
109 # Multi table update
110 CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx2(c1,c3));
111 CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1,c3));
112 CREATE TABLE t4(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx4(c1,c3));
113 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
114 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
115 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
116 --sorted_result
117 SELECT * FROM t2;
118 --sorted_result
119 SELECT * FROM t3;
120 --sorted_result
121 SELECT * FROM t4;
122 UPDATE t1,t2,t3,t4 SET t2.c1=t2.c1+t3.c1, t3.c2=t3.c2-t2.c1, t4.c1=t1.c1*t1.c3 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888' ;
123 --sorted_result
124 SELECT * FROM t1;
125 --sorted_result
126 SELECT * FROM t1;
127 --sorted_result
128 SELECT * FROM t1;
129
130 # Update using various access methods
131 TRUNCATE t2;
132 TRUNCATE t3;
133 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
134 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
135
136 # Update using Const
137 # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
138 UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
139 --sorted_result
140 SELECT * FROM t2;
141
142 # Update using range
143 # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
144 update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
145 --sorted_result
146 SELECT * FROM t2;
147 # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
148 UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
149 --sorted_result
150 SELECT * FROM t2;
151
152 # Update using eq_ref
153 DROP TABLE t2,t3;
154 CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT);
155 CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1));
156 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
157 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
158 # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
159 UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
160
161 #Updating with invalid values
162 UPDATE t1 SET c3='asdf' WHERE c1='11111.11111';
163 --sorted_result
164 SELECT c3 FROM t1;
165 UPDATE t1 SET c2="1 e 1" WHERE c4=2;
166 --sorted_result
167 SELECT c2 FROM t1;
168
169 #Duplicate keys
170 --error ER_DUP_ENTRY
171 UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
172
173 --sorted_result
174 SELECT * FROM t1;
175 --sorted_result
176 SELECT * FROM t2;
177 --sorted_result
178 SELECT * FROM t3;
179 --sorted_result
180 SELECT * FROM t4;
181 # Droping the tables
182 --disable_warnings
183 DROP TABLES IF EXISTS t1,t2,t3,t4;
184 --enable_warnings
185
186 ######## Running UPDATE tests for FLOAT ########
187
188 # Create tables
189 CREATE TABLE t1(c1 FLOAT(10,5) UNSIGNED NOT NULL, c2 FLOAT(10,5) SIGNED NULL, c3 FLOAT, c4 INT, UNIQUE INDEX idx(c1,c4));
190
191 # Inserting data into table t1
192
193 # Trailing zeroes are not stripped, D=5
194 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
195
196 # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped;
197 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
198
199 # c1, c2, c3 will be rounded automatically
200 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
201
202 # Inserting maximum values
203 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
204
205 # Test insert leading zero, +/- signs, overflow handling
206 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
207 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
208 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
209
210 # Inserting in scientific notations
211 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
212 #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
213
214 --sorted_result
215 SELECT * FROM t1;
216
217 # UPDATE starts here
218
219 # Update order by limit
220 UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
221 --sorted_result
222 SELECT c1,c2 FROM t1;
223
224 # Update with arithmetic operations
225 UPDATE t1 SET c1=c2+c3 WHERE c3 < 10000;
226 --sorted_result
227 SELECT * FROM t1;
228 UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
229 --sorted_result
230 SELECT * FROM t1;
231
232 # Update with NULL ( NULL to number & number to NULL)
233 UPDATE t1 SET c2=NULL WHERE c4=4;
234 --sorted_result
235 SELECT * FROM t1;
236 UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
237 --sorted_result
238 SELECT * FROM t1;
239
240 # Update negative value to unsigned column
241 UPDATE t1 SET c1=-1.0e+2 WHERE c4=2;
242 SHOW WARNINGS;
243 --sorted_result
244 SELECT * FROM t1;
245 UPDATE t1 SET c1=1.0e+20 WHERE c4=2;
246 SHOW WARNINGS;
247 --sorted_result
248 SELECT * FROM t1;
249
250 # Update range values
251 UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
252 --sorted_result
253 SELECT * FROM t1;
254 UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
255 --sorted_result
256 SELECT * FROM t1 WHERE c1=1.2e+2;
257 UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
258 --sorted_result
259 SELECT * FROM t1;
260
261 # Update outside range would be clipped to closest endpoints
262 UPDATE t1 SET c1=99999.999999 WHERE c4=7;
263 --sorted_result
264 SELECT c1,c4 FROM t1;
265 UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
266 --sorted_result
267 SELECT c2,c4 FROM t1;
268
269 # Update ignore on bad null error
270 SELECT c1 FROM t1 WHERE c1>1000;
271 SET SQL_MODE=STRICT_ALL_TABLES;
272 --error ER_BAD_NULL_ERROR
273 UPDATE t1 SET c1=NULL WHERE c1>1000;
274 UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
275 --sorted_result
276 SELECT c1 from t1 WHERE c1>1000;
277 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
278
279 TRUNCATE t1;
280 INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9);
281 --replace_result 88888.89063 88888.89062
282 --sorted_result
283 SELECT * FROM t1;
284
285 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
286 UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
287 --replace_result 88888.89063 88888.89062
288 --sorted_result
289 SELECT * FROM t1;
290
291 # Multi table update
292 CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx2(c1,c3));
293 CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1,c3));
294 CREATE TABLE t4(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx4(c1,c3));
295 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
296 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
297 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
298 --sorted_result
299 SELECT * FROM t2;
300 --sorted_result
301 SELECT * FROM t3;
302 --replace_result 88888.89063 88888.89062
303 --sorted_result
304 SELECT * FROM t4;
305 UPDATE t1,t2,t3,t4 SET t2.c1=t2.c1+t3.c1, t3.c2=t3.c2-t2.c1, t4.c1=t1.c1*t1.c3 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888' ;
306 --replace_result 88888.89063 88888.89062
307 --sorted_result
308 SELECT * FROM t1;
309 --replace_result 88888.89063 88888.89062
310 --sorted_result
311 SELECT * FROM t1;
312 --replace_result 88888.89063 88888.89062
313 --sorted_result
314 SELECT * FROM t1;
315
316 # Update using various access methods
317 TRUNCATE t2;
318 TRUNCATE t3;
319 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
320 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
321
322 # Update using Const
323 # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
324 UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
325 --sorted_result
326 SELECT * FROM t2;
327
328 # Update using range
329 # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
330 update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
331 --sorted_result
332 SELECT * FROM t2;
333 # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
334 UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
335 --sorted_result
336 SELECT * FROM t2;
337
338 # Update using eq_ref
339 DROP TABLE t2,t3;
340 CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT);
341 CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1));
342 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
343 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
344 # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
345 UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
346
347 #Updating with invalid values
348 UPDATE t1 SET c3='asdf' WHERE c1='11111.11111';
349 --sorted_result
350 SELECT c3 FROM t1;
351 UPDATE t1 SET c2="1 e 1" WHERE c4=2;
352 --replace_result 88888.89063 88888.89062
353 --sorted_result
354 SELECT c2 FROM t1;
355
356 #Duplicate keys
357 --replace_result 88888.89063 88888.89062
358 --error ER_DUP_ENTRY
359 UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
360 --replace_result 88888.89063 88888.89062
361 --sorted_result
362 SELECT * FROM t1;
363 --sorted_result
364 SELECT * FROM t2;
365 --sorted_result
366 SELECT * FROM t3;
367 --replace_result 88888.89063 88888.89062
368 --sorted_result
369 SELECT * FROM t4;
370 # Droping the tables
371 --disable_warnings
372 DROP TABLES IF EXISTS t1,t2,t3,t4;
373 --enable_warnings
374
375 ######## Running UPDATE tests for DOUBLE ########
376
377 # Create tables
378 CREATE TABLE t1(c1 DOUBLE(10,5) UNSIGNED NOT NULL, c2 DOUBLE(10,5) SIGNED NULL, c3 DOUBLE, c4 INT, UNIQUE INDEX idx(c1,c4));
379
380 # Inserting data into table t1
381
382 # Trailing zeroes are not stripped, D=5
383 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
384
385 # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped;
386 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
387
388 # c1, c2, c3 will be rounded automatically
389 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
390
391 # Inserting maximum values
392 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
393
394 # Test insert leading zero, +/- signs, overflow handling
395 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
396 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
397 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
398
399 # Inserting in scientific notations
400 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
401 #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
402
403 --sorted_result
404 SELECT * FROM t1;
405
406 # UPDATE starts here
407
408 # Update order by limit
409 UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
410 --sorted_result
411 SELECT c1,c2 FROM t1;
412
413 # Update with arithmetic operations
414 UPDATE t1 SET c1=c2+c3 WHERE c3 < 10000;
415 --sorted_result
416 SELECT * FROM t1;
417 UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
418 --sorted_result
419 SELECT * FROM t1;
420
421 # Update with NULL ( NULL to number & number to NULL)
422 UPDATE t1 SET c2=NULL WHERE c4=4;
423 --sorted_result
424 SELECT * FROM t1;
425 UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
426 --sorted_result
427 SELECT * FROM t1;
428
429 # Update negative value to unsigned column
430 UPDATE t1 SET c1=-1.0e+2 WHERE c4=2;
431 SHOW WARNINGS;
432 --sorted_result
433 SELECT * FROM t1;
434 UPDATE t1 SET c1=1.0e+20 WHERE c4=2;
435 SHOW WARNINGS;
436 --sorted_result
437 SELECT * FROM t1;
438
439 # Update range values
440 UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
441 --sorted_result
442 SELECT * FROM t1;
443 UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
444 --sorted_result
445 SELECT * FROM t1 WHERE c1=1.2e+2;
446 UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
447 --sorted_result
448 SELECT * FROM t1;
449
450 # Update outside range would be clipped to closest endpoints
451 UPDATE t1 SET c1=99999.999999 WHERE c4=7;
452 --sorted_result
453 SELECT c1,c4 FROM t1;
454 UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
455 --sorted_result
456 SELECT c2,c4 FROM t1;
457
458 # Update ignore on bad null error
459 SELECT c1 FROM t1 WHERE c1>1000;
460 SET SQL_MODE=STRICT_ALL_TABLES;
461 --error ER_BAD_NULL_ERROR
462 UPDATE t1 SET c1=NULL WHERE c1>1000;
463 UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
464 --sorted_result
465 SELECT c1 from t1 WHERE c1>1000;
466 SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
467
468 TRUNCATE t1;
469 INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9);
470 --sorted_result
471 SELECT * FROM t1;
472
473 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
474 UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
475 --sorted_result
476 SELECT * FROM t1;
477
478 # Multi table update
479 CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx2(c1,c3));
480 CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1,c3));
481 CREATE TABLE t4(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx4(c1,c3));
482 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
483 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
484 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
485 --sorted_result
486 SELECT * FROM t2;
487 --sorted_result
488 SELECT * FROM t3;
489 --sorted_result
490 SELECT * FROM t4;
491 UPDATE t1,t2,t3,t4 SET t2.c1=t2.c1+t3.c1, t3.c2=t3.c2-t2.c1, t4.c1=t1.c1*t1.c3 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888' ;
492 --sorted_result
493 SELECT * FROM t1;
494 --sorted_result
495 SELECT * FROM t1;
496 --sorted_result
497 SELECT * FROM t1;
498
499 # Update using various access methods
500 TRUNCATE t2;
501 TRUNCATE t3;
502 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
503 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
504
505 # Update using Const
506 # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
507 UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
508 --sorted_result
509 SELECT * FROM t2;
510
511 # Update using range
512 # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
513 update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
514 --sorted_result
515 SELECT * FROM t2;
516 # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
517 UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
518 --sorted_result
519 SELECT * FROM t2;
520
521 # Update using eq_ref
522 DROP TABLE t2,t3;
523 CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT);
524 CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1));
525 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
526 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
527 # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
528 UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
529
530 #Updating with invalid values
531 UPDATE t1 SET c3='asdf' WHERE c1='11111.11111';
532 --sorted_result
533 SELECT c3 FROM t1;
534 UPDATE t1 SET c2="1 e 1" WHERE c4=2;
535 --sorted_result
536 SELECT c2 FROM t1;
537
538 #Duplicate keys
539 --error ER_DUP_ENTRY
540 UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
541
542 --sorted_result
543 SELECT * FROM t1;
544 --sorted_result
545 SELECT * FROM t2;
546 --sorted_result
547 SELECT * FROM t3;
548 --sorted_result
549 SELECT * FROM t4;
550 # Droping the tables
551 --disable_warnings
552 DROP TABLES IF EXISTS t1,t2,t3,t4;
553 --enable_warnings
554 SET sql_mode=default;