2 DROP TABLE IF EXISTS t1,t2,t3,t4;
5 ######## Running UPDATE tests for DECIMAL ########
8 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 # Inserting data into table t1
12 # Trailing zeroes are not stripped, D=5
13 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
15 # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped;
16 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
18 # c1, c2, c3 will be rounded automatically
19 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
21 # Inserting maximum values
22 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
24 # Test insert leading zero, +/- signs, overflow handling
25 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
26 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
27 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
29 # Inserting in scientific notations
30 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
31 #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
38 # Update order by limit
39 UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
43 # Update with arithmetic operations
44 UPDATE t1 SET c1=c2+c3 WHERE c3 < 10000;
47 UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
51 # Update with NULL ( NULL to number & number to NULL)
52 UPDATE t1 SET c2=NULL WHERE c4=4;
55 UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
59 # Update negative value to unsigned column
60 UPDATE t1 SET c1=-1.0e+2 WHERE c4=2;
64 UPDATE t1 SET c1=1.0e+20 WHERE c4=2;
70 UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
73 UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
75 SELECT * FROM t1 WHERE c1=1.2e+2;
76 UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
80 # Update outside range would be clipped to closest endpoints
81 UPDATE t1 SET c1=99999.999999 WHERE c4=7;
84 UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
88 # Update ignore on bad null error
89 SELECT c1 FROM t1 WHERE c1>1000;
90 SET SQL_MODE=STRICT_ALL_TABLES;
91 --error ER_BAD_NULL_ERROR
92 UPDATE t1 SET c1=NULL WHERE c1>1000;
93 UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
95 SELECT c1 from t1 WHERE c1>1000;
99 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);
103 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
104 UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
109 CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx2(c1,c3));
110 CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1,c3));
111 CREATE TABLE t4(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx4(c1,c3));
112 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
113 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
114 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
121 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' ;
129 # Update using various access methods
132 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
133 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
136 # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
137 UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
142 # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
143 update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
146 # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
147 UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
151 # Update using eq_ref
153 CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT);
154 CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1));
155 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
156 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
157 # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
158 UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
160 #Updating with invalid values
161 UPDATE t1 SET c3='asdf' WHERE c1='11111.11111';
164 UPDATE t1 SET c2="1 e 1" WHERE c4=2;
170 UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
182 DROP TABLES IF EXISTS t1,t2,t3,t4;
185 ######## Running UPDATE tests for FLOAT ########
188 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 # Inserting data into table t1
192 # Trailing zeroes are not stripped, D=5
193 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
195 # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped;
196 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
198 # c1, c2, c3 will be rounded automatically
199 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
201 # Inserting maximum values
202 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
204 # Test insert leading zero, +/- signs, overflow handling
205 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
206 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
207 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
209 # Inserting in scientific notations
210 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
211 #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
218 # Update order by limit
219 UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
221 SELECT c1,c2 FROM t1;
223 # Update with arithmetic operations
224 UPDATE t1 SET c1=c2+c3 WHERE c3 < 10000;
227 UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
231 # Update with NULL ( NULL to number & number to NULL)
232 UPDATE t1 SET c2=NULL WHERE c4=4;
235 UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
239 # Update negative value to unsigned column
240 UPDATE t1 SET c1=-1.0e+2 WHERE c4=2;
244 UPDATE t1 SET c1=1.0e+20 WHERE c4=2;
249 # Update range values
250 UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
253 UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
255 SELECT * FROM t1 WHERE c1=1.2e+2;
256 UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
260 # Update outside range would be clipped to closest endpoints
261 UPDATE t1 SET c1=99999.999999 WHERE c4=7;
263 SELECT c1,c4 FROM t1;
264 UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
266 SELECT c2,c4 FROM t1;
268 # Update ignore on bad null error
269 SELECT c1 FROM t1 WHERE c1>1000;
270 SET SQL_MODE=STRICT_ALL_TABLES;
271 --error ER_BAD_NULL_ERROR
272 UPDATE t1 SET c1=NULL WHERE c1>1000;
273 UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
275 SELECT c1 from t1 WHERE c1>1000;
276 SET SQL_MODE=DEFAULT;
279 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);
280 --replace_result 88888.89063 88888.89062
284 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
285 UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
286 --replace_result 88888.89063 88888.89062
291 CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx2(c1,c3));
292 CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1,c3));
293 CREATE TABLE t4(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx4(c1,c3));
294 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
295 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
296 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
301 --replace_result 88888.89063 88888.89062
304 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' ;
305 --replace_result 88888.89063 88888.89062
308 --replace_result 88888.89063 88888.89062
311 --replace_result 88888.89063 88888.89062
315 # Update using various access methods
318 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
319 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
322 # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
323 UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
328 # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
329 update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
332 # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
333 UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
337 # Update using eq_ref
339 CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT);
340 CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1));
341 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
342 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
343 # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
344 UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
346 #Updating with invalid values
347 UPDATE t1 SET c3='asdf' WHERE c1='11111.11111';
350 UPDATE t1 SET c2="1 e 1" WHERE c4=2;
351 --replace_result 88888.89063 88888.89062
356 --replace_result 88888.89063 88888.89062
358 UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
359 --replace_result 88888.89063 88888.89062
366 --replace_result 88888.89063 88888.89062
371 DROP TABLES IF EXISTS t1,t2,t3,t4;
374 ######## Running UPDATE tests for DOUBLE ########
377 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 # Inserting data into table t1
381 # Trailing zeroes are not stripped, D=5
382 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
384 # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped;
385 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
387 # c1, c2, c3 will be rounded automatically
388 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
390 # Inserting maximum values
391 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
393 # Test insert leading zero, +/- signs, overflow handling
394 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
395 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
396 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
398 # Inserting in scientific notations
399 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
400 #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
407 # Update order by limit
408 UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
410 SELECT c1,c2 FROM t1;
412 # Update with arithmetic operations
413 UPDATE t1 SET c1=c2+c3 WHERE c3 < 10000;
416 UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
420 # Update with NULL ( NULL to number & number to NULL)
421 UPDATE t1 SET c2=NULL WHERE c4=4;
424 UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
428 # Update negative value to unsigned column
429 UPDATE t1 SET c1=-1.0e+2 WHERE c4=2;
433 UPDATE t1 SET c1=1.0e+20 WHERE c4=2;
438 # Update range values
439 UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
442 UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
444 SELECT * FROM t1 WHERE c1=1.2e+2;
445 UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
449 # Update outside range would be clipped to closest endpoints
450 UPDATE t1 SET c1=99999.999999 WHERE c4=7;
452 SELECT c1,c4 FROM t1;
453 UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
455 SELECT c2,c4 FROM t1;
457 # Update ignore on bad null error
458 SELECT c1 FROM t1 WHERE c1>1000;
459 SET SQL_MODE=STRICT_ALL_TABLES;
460 --error ER_BAD_NULL_ERROR
461 UPDATE t1 SET c1=NULL WHERE c1>1000;
462 UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
464 SELECT c1 from t1 WHERE c1>1000;
465 SET SQL_MODE=DEFAULT;
468 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);
472 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
473 UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
478 CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx2(c1,c3));
479 CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1,c3));
480 CREATE TABLE t4(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx4(c1,c3));
481 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
482 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
483 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
490 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' ;
498 # Update using various access methods
501 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
502 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
505 # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
506 UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
511 # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
512 update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
515 # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
516 UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
520 # Update using eq_ref
522 CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT);
523 CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1));
524 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
525 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
526 # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
527 UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
529 #Updating with invalid values
530 UPDATE t1 SET c3='asdf' WHERE c1='11111.11111';
533 UPDATE t1 SET c2="1 e 1" WHERE c4=2;
539 UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
551 DROP TABLES IF EXISTS t1,t2,t3,t4;