Debian Codegraph @

mysql-test/suite/engines/iuds/r/update_decimal.result

raw
1 DROP TABLE IF EXISTS t1,t2,t3,t4;
2 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));
3 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
4 INSERT IGNORE INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
5 Warnings:
6 Warning 1264 Out of range value for column 'c1' at row 1
7 Warning 1264 Out of range value for column 'c2' at row 1
8 Note 1265 Data truncated for column 'c3' at row 1
9 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
10 Warnings:
11 Note 1265 Data truncated for column 'c1' at row 1
12 Note 1265 Data truncated for column 'c2' at row 1
13 Note 1265 Data truncated for column 'c3' at row 1
14 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
15 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
16 INSERT IGNORE INTO t1 VALUES ("-.1","-.1","-.1",8);
17 Warnings:
18 Warning 1264 Out of range value for column 'c1' at row 1
19 Note 1265 Data truncated for column 'c3' at row 1
20 INSERT IGNORE INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
21 Warnings:
22 Warning 1264 Out of range value for column 'c1' at row 1
23 Warning 1264 Out of range value for column 'c2' at row 1
24 Note 1265 Data truncated for column 'c3' at row 1
25 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
26 SELECT * FROM t1;
27 c1 c2 c3 c4
28 0.00000 -0.10000 0 8
29 0.00000 0.00000 0 6
30 1.00000 1.00000 1 7
31 100.00000 1001.00000 10000 4
32 100.05000 -100.05000 100119 1
33 10000.00000 0.00001 1000000000 10
34 11111.00009 -9999.99999 9999999 2
35 99999.99999 -99999.99999 9999999999 5
36 99999.99999 99999.99999 1000000000 3
37 99999.99999 99999.99999 111111111 9
38 UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
39 SELECT c1,c2 FROM t1;
40 c1 c2
41 0.00000 -0.10000
42 0.00000 0.00000
43 1.00000 1.00000
44 100.05000 -100.05000
45 10000.00000 0.00001
46 10001.00001 1001.00000
47 11111.00009 -9999.99999
48 99999.99999 -99999.99999
49 99999.99999 99999.99999
50 99999.99999 99999.99999
51 UPDATE IGNORE t1 SET c1=c2+c3 WHERE c3 < 10000;
52 Warnings:
53 Warning 1264 Out of range value for column 'c1' at row 8
54 SELECT * FROM t1;
55 c1 c2 c3 c4
56 0.00000 -0.10000 0 8
57 0.00000 0.00000 0 6
58 100.05000 -100.05000 100119 1
59 10000.00000 0.00001 1000000000 10
60 10001.00001 1001.00000 10000 4
61 11111.00009 -9999.99999 9999999 2
62 2.00000 1.00000 1 7
63 99999.99999 -99999.99999 9999999999 5
64 99999.99999 99999.99999 1000000000 3
65 99999.99999 99999.99999 111111111 9
66 UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
67 SELECT * FROM t1;
68 c1 c2 c3 c4
69 0.00000 -0.10000 0 8
70 0.00000 0.00000 0 6
71 100.05000 -0.05000 100119 1
72 10000.00000 100.00001 1000000000 10
73 10001.00001 1101.00000 10000 4
74 11111.00009 -9899.99999 9999999 2
75 2.00000 1.00000 1 7
76 99999.99999 -99999.99999 9999999999 5
77 99999.99999 99999.99999 1000000000 3
78 99999.99999 99999.99999 111111111 9
79 UPDATE t1 SET c2=NULL WHERE c4=4;
80 SELECT * FROM t1;
81 c1 c2 c3 c4
82 0.00000 -0.10000 0 8
83 0.00000 0.00000 0 6
84 100.05000 -0.05000 100119 1
85 10000.00000 100.00001 1000000000 10
86 10001.00001 NULL 10000 4
87 11111.00009 -9899.99999 9999999 2
88 2.00000 1.00000 1 7
89 99999.99999 -99999.99999 9999999999 5
90 99999.99999 99999.99999 1000000000 3
91 99999.99999 99999.99999 111111111 9
92 UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
93 SELECT * FROM t1;
94 c1 c2 c3 c4
95 0.00000 -0.10000 0 8
96 0.00000 0.00000 0 6
97 100.05000 -0.05000 100119 1
98 10000.00000 100.00001 1000000000 10
99 10001.00001 NULL 10000 4
100 11111.00009 -9899.99999 9999999 2
101 2.00000 1.00000 1 7
102 99999.99999 -99999.99999 9999999999 5
103 99999.99999 99999.99999 1000000000 3
104 99999.99999 99999.99999 111111111 9
105 UPDATE IGNORE t1 SET c1=-1.0e+2 WHERE c4=2;
106 Warnings:
107 Warning 1264 Out of range value for column 'c1' at row 2
108 SHOW WARNINGS;
109 Level Code Message
110 Warning 1264 Out of range value for column 'c1' at row 2
111 SELECT * FROM t1;
112 c1 c2 c3 c4
113 0.00000 -0.10000 0 8
114 0.00000 -9899.99999 9999999 2
115 0.00000 0.00000 0 6
116 100.05000 -0.05000 100119 1
117 10000.00000 100.00001 1000000000 10
118 10001.00001 NULL 10000 4
119 2.00000 1.00000 1 7
120 99999.99999 -99999.99999 9999999999 5
121 99999.99999 99999.99999 1000000000 3
122 99999.99999 99999.99999 111111111 9
123 UPDATE IGNORE t1 SET c1=1.0e+20 WHERE c4=2;
124 Warnings:
125 Warning 1264 Out of range value for column 'c1' at row 2
126 SHOW WARNINGS;
127 Level Code Message
128 Warning 1264 Out of range value for column 'c1' at row 2
129 SELECT * FROM t1;
130 c1 c2 c3 c4
131 0.00000 -0.10000 0 8
132 0.00000 0.00000 0 6
133 100.05000 -0.05000 100119 1
134 10000.00000 100.00001 1000000000 10
135 10001.00001 NULL 10000 4
136 2.00000 1.00000 1 7
137 99999.99999 -9899.99999 9999999 2
138 99999.99999 -99999.99999 9999999999 5
139 99999.99999 99999.99999 1000000000 3
140 99999.99999 99999.99999 111111111 9
141 UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
142 SELECT * FROM t1;
143 c1 c2 c3 c4
144 0.00000 -0.10000 0 8
145 0.00000 0.00000 0 6
146 100.05000 -0.05000 100119 1
147 10000.00000 100.00001 1000000000 10
148 10001.00001 NULL 10000 4
149 99999.99999 -9899.99999 9999999 2
150 99999.99999 -99999.99999 9999999999 5
151 99999.99999 1.00000 1 7
152 99999.99999 99999.99999 1000000000 3
153 99999.99999 99999.99999 111111111 9
154 UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
155 SELECT * FROM t1 WHERE c1=1.2e+2;
156 c1 c2 c3 c4
157 120.00000 -99994.99999 9999999999 5
158 UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
159 SELECT * FROM t1;
160 c1 c2 c3 c4
161 0.00000 -0.10000 0 8
162 0.00000 0.00000 1234567890 6
163 100.05000 -0.05000 100119 1
164 10000.00000 100.00001 1000000000 10
165 10001.00001 NULL 1234567890 4
166 120.00000 -99994.99999 1234567890 5
167 99999.99999 -9899.99999 9999999 2
168 99999.99999 1.00000 1 7
169 99999.99999 99999.99999 1000000000 3
170 99999.99999 99999.99999 111111111 9
171 UPDATE IGNORE t1 SET c1=99999.999999 WHERE c4=7;
172 Warnings:
173 Warning 1264 Out of range value for column 'c1' at row 7
174 SELECT c1,c4 FROM t1;
175 c1 c4
176 0.00000 6
177 0.00000 8
178 100.05000 1
179 10000.00000 10
180 10001.00001 4
181 120.00000 5
182 99999.99999 2
183 99999.99999 3
184 99999.99999 7
185 99999.99999 9
186 UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
187 SELECT c2,c4 FROM t1;
188 c2 c4
189 -0.05000 1
190 -0.10000 8
191 -9899.99999 2
192 -9999.00990 5
193 0.00000 6
194 1.00000 7
195 100.00001 10
196 99999.99999 3
197 99999.99999 9
198 NULL 4
199 SELECT c1 FROM t1 WHERE c1>1000;
200 c1
201 10000.00000
202 10001.00001
203 99999.99999
204 99999.99999
205 99999.99999
206 99999.99999
207 SET SQL_MODE=STRICT_ALL_TABLES;
208 UPDATE t1 SET c1=NULL WHERE c1>1000;
209 ERROR 23000: Column 'c1' cannot be null
210 UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
211 Warnings:
212 Warning 1048 Column 'c1' cannot be null
213 Warning 1048 Column 'c1' cannot be null
214 Warning 1048 Column 'c1' cannot be null
215 Warning 1048 Column 'c1' cannot be null
216 Warning 1048 Column 'c1' cannot be null
217 Warning 1048 Column 'c1' cannot be null
218 SELECT c1 from t1 WHERE c1>1000;
219 c1
220 SET SQL_MODE=DEFAULT;
221 TRUNCATE t1;
222 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);
223 SELECT * FROM t1;
224 c1 c2 c3 c4
225 11111.11111 -11111.11111 1111111111 1
226 22222.22222 -22222.22222 2222222222 2
227 33333.33333 -33333.33333 3333333333 3
228 44444.44444 -44444.44444 4444444444 4
229 55555.55555 -55555.55555 5555555555 5
230 66666.66666 -66666.66666 6666666666 6
231 77777.77777 -77777.77777 7777777777 7
232 88888.88888 -88888.88888 8888888888 8
233 99999.99999 -99999.99999 9999999999 9
234 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
235 c1
236 22222.22222
237 33333.33333
238 44444.44444
239 55555.55555
240 UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
241 SELECT * FROM t1;
242 c1 c2 c3 c4
243 11111.11111 -11111.11111 1111111111 1
244 22122.22222 -100.00000 2222222222 2
245 33233.33333 -100.00000 3333333333 3
246 44344.44444 -100.00000 4444444444 4
247 55455.55555 -100.00000 5555555555 5
248 66666.66666 -66666.66666 6666666666 6
249 77777.77777 -77777.77777 7777777777 7
250 88888.88888 -88888.88888 8888888888 8
251 99999.99999 -99999.99999 9999999999 9
252 CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx2(c1,c3));
253 CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1,c3));
254 CREATE TABLE t4(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx4(c1,c3));
255 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
256 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
257 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
258 SELECT * FROM t2;
259 c1 c2 c3
260 11111.11111 1111111111 1
261 22222.22222 2222222222 2
262 33333.33333 3333333333 3
263 SELECT * FROM t3;
264 c1 c2 c3
265 44444.44444 4444444444 4
266 55555.55555 5555555555 5
267 66666.66666 6666666666 6
268 SELECT * FROM t4;
269 c1 c2 c3
270 77777.77777 7777777777 7
271 88888.88888 8888888888 8
272 99999.99999 9999999999 9
273 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' ;
274 SELECT * FROM t1;
275 c1 c2 c3 c4
276 11111.11111 -11111.11111 1111111111 1
277 22122.22222 -100.00000 2222222222 2
278 33233.33333 -100.00000 3333333333 3
279 44344.44444 -100.00000 4444444444 4
280 55455.55555 -100.00000 5555555555 5
281 66666.66666 -66666.66666 6666666666 6
282 77777.77777 -77777.77777 7777777777 7
283 88888.88888 -88888.88888 8888888888 8
284 99999.99999 -99999.99999 9999999999 9
285 SELECT * FROM t1;
286 c1 c2 c3 c4
287 11111.11111 -11111.11111 1111111111 1
288 22122.22222 -100.00000 2222222222 2
289 33233.33333 -100.00000 3333333333 3
290 44344.44444 -100.00000 4444444444 4
291 55455.55555 -100.00000 5555555555 5
292 66666.66666 -66666.66666 6666666666 6
293 77777.77777 -77777.77777 7777777777 7
294 88888.88888 -88888.88888 8888888888 8
295 99999.99999 -99999.99999 9999999999 9
296 SELECT * FROM t1;
297 c1 c2 c3 c4
298 11111.11111 -11111.11111 1111111111 1
299 22122.22222 -100.00000 2222222222 2
300 33233.33333 -100.00000 3333333333 3
301 44344.44444 -100.00000 4444444444 4
302 55455.55555 -100.00000 5555555555 5
303 66666.66666 -66666.66666 6666666666 6
304 77777.77777 -77777.77777 7777777777 7
305 88888.88888 -88888.88888 8888888888 8
306 99999.99999 -99999.99999 9999999999 9
307 TRUNCATE t2;
308 TRUNCATE t3;
309 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
310 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
311 UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
312 SELECT * FROM t2;
313 c1 c2 c3
314 11111.11111 1111111111 1
315 33333.33333 3333333333 3
316 44444.44444 2222222222 4
317 update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
318 SELECT * FROM t2;
319 c1 c2 c3
320 44444.44444 2222222222 4
321 99999.99999 1111111111 1
322 99999.99999 3333333333 3
323 UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
324 SELECT * FROM t2;
325 c1 c2 c3
326 44443.44444 2222222221 4
327 99998.99999 1111111110 1
328 99998.99999 3333333332 3
329 DROP TABLE t2,t3;
330 CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT);
331 CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1));
332 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
333 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
334 UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
335 UPDATE IGNORE t1 SET c3='asdf' WHERE c1='11111.11111';
336 Warnings:
337 Warning 1366 Incorrect decimal value: 'asdf' for column `test`.`t1`.`c3` at row 1
338 SELECT c3 FROM t1;
339 c3
340 0
341 2222222222
342 3333333333
343 4444444444
344 5555555555
345 6666666666
346 7777777777
347 8888888888
348 9999999999
349 UPDATE IGNORE t1 SET c2="1 e 1" WHERE c4=2;
350 Warnings:
351 Warning 1265 Data truncated for column 'c2' at row 2
352 SELECT c2 FROM t1;
353 c2
354 -100.00000
355 -100.00000
356 -100.00000
357 -11111.11111
358 -66666.66666
359 -77777.77777
360 -88888.88888
361 -99999.99999
362 1.00000
363 UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
364 ERROR 23000: Duplicate entry '88888.88888-8' for key 'idx4'
365 SELECT * FROM t1;
366 c1 c2 c3 c4
367 11111.11111 -11111.11111 0 1
368 22122.22222 1.00000 2222222222 2
369 33233.33333 -100.00000 3333333333 3
370 44344.44444 -100.00000 4444444444 4
371 55455.55555 -100.00000 5555555555 5
372 66666.66666 -66666.66666 6666666666 6
373 77777.77777 -77777.77777 7777777777 7
374 88888.88888 -88888.88888 8888888888 8
375 99999.99999 -99999.99999 9999999999 9
376 SELECT * FROM t2;
377 c1 c2 c3
378 11111.11111 1111111111 1
379 22222.22222 2222222222 2
380 33333.33333 3333333333 3
381 SELECT * FROM t3;
382 c1 c2 c3
383 22222.22222 4444444444 1
384 55555.55555 5555555555 2
385 66666.66666 6666666666 3
386 SELECT * FROM t4;
387 c1 c2 c3
388 77777.77777 7777777777 7
389 88888.88888 8888888888 8
390 99999.99999 9999999999 9
391 DROP TABLES IF EXISTS t1,t2,t3,t4;
392 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));
393 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
394 INSERT IGNORE INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
395 Warnings:
396 Warning 1264 Out of range value for column 'c1' at row 1
397 Warning 1264 Out of range value for column 'c2' at row 1
398 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
399 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
400 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
401 INSERT IGNORE INTO t1 VALUES ("-.1","-.1","-.1",8);
402 Warnings:
403 Warning 1264 Out of range value for column 'c1' at row 1
404 INSERT IGNORE INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
405 Warnings:
406 Warning 1264 Out of range value for column 'c1' at row 1
407 Warning 1264 Out of range value for column 'c2' at row 1
408 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
409 SELECT * FROM t1;
410 c1 c2 c3 c4
411 0.00000 -0.10000 -0.1 8
412 0.00000 0.00000 0 6
413 1.00000 1.00000 1 7
414 100.00000 1001.00000 10000 4
415 100.05000 -100.05000 100119 1
416 10000.00000 0.00001 1000000000 10
417 100000.00000 -100000.00000 10000000000 5
418 100000.00000 100000.00000 1000000000 3
419 100000.00000 100000.00000 111111000 9
420 11111.00000 -10000.00000 10000000 2
421 UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
422 SELECT c1,c2 FROM t1;
423 c1 c2
424 0.00000 -0.10000
425 0.00000 0.00000
426 1.00000 1.00000
427 100.05000 -100.05000
428 10000.00000 0.00001
429 100000.00000 -100000.00000
430 100000.00000 100000.00000
431 100000.00000 100000.00000
432 10001.00000 1001.00000
433 11111.00000 -10000.00000
434 UPDATE IGNORE t1 SET c1=c2+c3 WHERE c3 < 10000;
435 Warnings:
436 Warning 1264 Out of range value for column 'c1' at row 8
437 SELECT * FROM t1;
438 c1 c2 c3 c4
439 0.00000 -0.10000 -0.1 8
440 0.00000 0.00000 0 6
441 100.05000 -100.05000 100119 1
442 10000.00000 0.00001 1000000000 10
443 100000.00000 -100000.00000 10000000000 5
444 100000.00000 100000.00000 1000000000 3
445 100000.00000 100000.00000 111111000 9
446 10001.00000 1001.00000 10000 4
447 11111.00000 -10000.00000 10000000 2
448 2.00000 1.00000 1 7
449 UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
450 SELECT * FROM t1;
451 c1 c2 c3 c4
452 0.00000 -0.10000 -0.1 8
453 0.00000 0.00000 0 6
454 100.05000 -0.05000 100119 1
455 10000.00000 100.00001 1000000000 10
456 100000.00000 -100000.00000 10000000000 5
457 100000.00000 100000.00000 1000000000 3
458 100000.00000 100000.00000 111111000 9
459 10001.00000 1101.00000 10000 4
460 11111.00000 -9900.00000 10000000 2
461 2.00000 1.00000 1 7
462 UPDATE t1 SET c2=NULL WHERE c4=4;
463 SELECT * FROM t1;
464 c1 c2 c3 c4
465 0.00000 -0.10000 -0.1 8
466 0.00000 0.00000 0 6
467 100.05000 -0.05000 100119 1
468 10000.00000 100.00001 1000000000 10
469 100000.00000 -100000.00000 10000000000 5
470 100000.00000 100000.00000 1000000000 3
471 100000.00000 100000.00000 111111000 9
472 10001.00000 NULL 10000 4
473 11111.00000 -9900.00000 10000000 2
474 2.00000 1.00000 1 7
475 UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
476 SELECT * FROM t1;
477 c1 c2 c3 c4
478 0.00000 -0.10000 -0.1 8
479 0.00000 0.00000 0 6
480 100.05000 -0.05000 100119 1
481 10000.00000 100.00001 1000000000 10
482 100000.00000 -100000.00000 10000000000 5
483 100000.00000 100000.00000 1000000000 3
484 100000.00000 100000.00000 111111000 9
485 10001.00000 NULL 10000 4
486 11111.00000 -9900.00000 10000000 2
487 2.00000 1.00000 1 7
488 UPDATE IGNORE t1 SET c1=-1.0e+2 WHERE c4=2;
489 Warnings:
490 Warning 1264 Out of range value for column 'c1' at row 2
491 SHOW WARNINGS;
492 Level Code Message
493 Warning 1264 Out of range value for column 'c1' at row 2
494 SELECT * FROM t1;
495 c1 c2 c3 c4
496 0.00000 -0.10000 -0.1 8
497 0.00000 -9900.00000 10000000 2
498 0.00000 0.00000 0 6
499 100.05000 -0.05000 100119 1
500 10000.00000 100.00001 1000000000 10
501 100000.00000 -100000.00000 10000000000 5
502 100000.00000 100000.00000 1000000000 3
503 100000.00000 100000.00000 111111000 9
504 10001.00000 NULL 10000 4
505 2.00000 1.00000 1 7
506 UPDATE IGNORE t1 SET c1=1.0e+20 WHERE c4=2;
507 Warnings:
508 Warning 1264 Out of range value for column 'c1' at row 2
509 SHOW WARNINGS;
510 Level Code Message
511 Warning 1264 Out of range value for column 'c1' at row 2
512 SELECT * FROM t1;
513 c1 c2 c3 c4
514 0.00000 -0.10000 -0.1 8
515 0.00000 0.00000 0 6
516 100.05000 -0.05000 100119 1
517 10000.00000 100.00001 1000000000 10
518 100000.00000 -100000.00000 10000000000 5
519 100000.00000 -9900.00000 10000000 2
520 100000.00000 100000.00000 1000000000 3
521 100000.00000 100000.00000 111111000 9
522 10001.00000 NULL 10000 4
523 2.00000 1.00000 1 7
524 UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
525 SELECT * FROM t1;
526 c1 c2 c3 c4
527 0.00000 -0.10000 -0.1 8
528 0.00000 0.00000 0 6
529 100.05000 -0.05000 100119 1
530 10000.00000 100.00001 1000000000 10
531 100000.00000 -100000.00000 10000000000 5
532 100000.00000 -9900.00000 10000000 2
533 100000.00000 1.00000 1 7
534 100000.00000 100000.00000 1000000000 3
535 100000.00000 100000.00000 111111000 9
536 10001.00000 NULL 10000 4
537 UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
538 SELECT * FROM t1 WHERE c1=1.2e+2;
539 c1 c2 c3 c4
540 UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
541 SELECT * FROM t1;
542 c1 c2 c3 c4
543 0.00000 -0.10000 -0.1 8
544 0.00000 0.00000 1234570000 6
545 100.05000 -0.05000 100119 1
546 10000.00000 100.00001 1000000000 10
547 100000.00000 -100000.00000 1234570000 5
548 100000.00000 -9900.00000 10000000 2
549 100000.00000 1.00000 1 7
550 100000.00000 100000.00000 1000000000 3
551 100000.00000 100000.00000 111111000 9
552 10001.00000 NULL 1234570000 4
553 UPDATE IGNORE t1 SET c1=99999.999999 WHERE c4=7;
554 Warnings:
555 Warning 1264 Out of range value for column 'c1' at row 7
556 SELECT c1,c4 FROM t1;
557 c1 c4
558 0.00000 6
559 0.00000 8
560 100.05000 1
561 10000.00000 10
562 100000.00000 2
563 100000.00000 3
564 100000.00000 5
565 100000.00000 7
566 100000.00000 9
567 10001.00000 4
568 UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
569 SELECT c2,c4 FROM t1;
570 c2 c4
571 -0.05000 1
572 -0.10000 8
573 -9900.00000 2
574 -9999.00977 5
575 0.00000 6
576 1.00000 7
577 100.00001 10
578 100000.00000 3
579 100000.00000 9
580 NULL 4
581 SELECT c1 FROM t1 WHERE c1>1000;
582 c1
583 10000.00000
584 10001.00000
585 100000.00000
586 100000.00000
587 100000.00000
588 100000.00000
589 100000.00000
590 SET SQL_MODE=STRICT_ALL_TABLES;
591 UPDATE t1 SET c1=NULL WHERE c1>1000;
592 ERROR 23000: Column 'c1' cannot be null
593 UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
594 Warnings:
595 Warning 1048 Column 'c1' cannot be null
596 Warning 1048 Column 'c1' cannot be null
597 Warning 1048 Column 'c1' cannot be null
598 Warning 1048 Column 'c1' cannot be null
599 Warning 1048 Column 'c1' cannot be null
600 Warning 1048 Column 'c1' cannot be null
601 Warning 1048 Column 'c1' cannot be null
602 SELECT c1 from t1 WHERE c1>1000;
603 c1
604 SET SQL_MODE=DEFAULT;
605 TRUNCATE t1;
606 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);
607 SELECT * FROM t1;
608 c1 c2 c3 c4
609 100000.00000 -100000.00000 10000000000 9
610 11111.11133 -11111.11133 1111110000 1
611 22222.22266 -22222.22266 2222220000 2
612 33333.33203 -33333.33203 3333330000 3
613 44444.44531 -44444.44531 4444440000 4
614 55555.55469 -55555.55469 5555560000 5
615 66666.66406 -66666.66406 6666670000 6
616 77777.78125 -77777.78125 7777780000 7
617 88888.89062 -88888.89062 8888890000 8
618 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
619 c1
620 22222.22266
621 33333.33203
622 44444.44531
623 55555.55469
624 UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
625 SELECT * FROM t1;
626 c1 c2 c3 c4
627 100000.00000 -100000.00000 10000000000 9
628 11111.11133 -11111.11133 1111110000 1
629 22122.22266 -100.00000 2222220000 2
630 33233.33203 -100.00000 3333330000 3
631 44344.44531 -100.00000 4444440000 4
632 55455.55469 -100.00000 5555560000 5
633 66666.66406 -66666.66406 6666670000 6
634 77777.78125 -77777.78125 7777780000 7
635 88888.89062 -88888.89062 8888890000 8
636 CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx2(c1,c3));
637 CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1,c3));
638 CREATE TABLE t4(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx4(c1,c3));
639 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
640 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
641 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
642 SELECT * FROM t2;
643 c1 c2 c3
644 11111.11133 1111110000 1
645 22222.22266 2222220000 2
646 33333.33203 3333330000 3
647 SELECT * FROM t3;
648 c1 c2 c3
649 44444.44531 4444440000 4
650 55555.55469 5555560000 5
651 66666.66406 6666670000 6
652 SELECT * FROM t4;
653 c1 c2 c3
654 100000.00000 10000000000 9
655 77777.78125 7777780000 7
656 88888.89062 8888890000 8
657 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' ;
658 SELECT * FROM t1;
659 c1 c2 c3 c4
660 100000.00000 -100000.00000 10000000000 9
661 11111.11133 -11111.11133 1111110000 1
662 22122.22266 -100.00000 2222220000 2
663 33233.33203 -100.00000 3333330000 3
664 44344.44531 -100.00000 4444440000 4
665 55455.55469 -100.00000 5555560000 5
666 66666.66406 -66666.66406 6666670000 6
667 77777.78125 -77777.78125 7777780000 7
668 88888.89062 -88888.89062 8888890000 8
669 SELECT * FROM t1;
670 c1 c2 c3 c4
671 100000.00000 -100000.00000 10000000000 9
672 11111.11133 -11111.11133 1111110000 1
673 22122.22266 -100.00000 2222220000 2
674 33233.33203 -100.00000 3333330000 3
675 44344.44531 -100.00000 4444440000 4
676 55455.55469 -100.00000 5555560000 5
677 66666.66406 -66666.66406 6666670000 6
678 77777.78125 -77777.78125 7777780000 7
679 88888.89062 -88888.89062 8888890000 8
680 SELECT * FROM t1;
681 c1 c2 c3 c4
682 100000.00000 -100000.00000 10000000000 9
683 11111.11133 -11111.11133 1111110000 1
684 22122.22266 -100.00000 2222220000 2
685 33233.33203 -100.00000 3333330000 3
686 44344.44531 -100.00000 4444440000 4
687 55455.55469 -100.00000 5555560000 5
688 66666.66406 -66666.66406 6666670000 6
689 77777.78125 -77777.78125 7777780000 7
690 88888.89062 -88888.89062 8888890000 8
691 TRUNCATE t2;
692 TRUNCATE t3;
693 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
694 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
695 UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
696 SELECT * FROM t2;
697 c1 c2 c3
698 11111.11133 1111110000 1
699 22222.22266 2222220000 2
700 33333.33203 3333330000 3
701 update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
702 SELECT * FROM t2;
703 c1 c2 c3
704 100000.00000 1111110000 1
705 100000.00000 2222220000 2
706 100000.00000 3333330000 3
707 UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
708 SELECT * FROM t2;
709 c1 c2 c3
710 100000.00000 1111110000 1
711 100000.00000 2222220000 2
712 100000.00000 3333330000 3
713 DROP TABLE t2,t3;
714 CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT);
715 CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1));
716 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
717 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
718 UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
719 UPDATE t1 SET c3='asdf' WHERE c1='11111.11111';
720 SELECT c3 FROM t1;
721 c3
722 10000000000
723 1111110000
724 2222220000
725 3333330000
726 4444440000
727 5555560000
728 6666670000
729 7777780000
730 8888890000
731 UPDATE IGNORE t1 SET c2="1 e 1" WHERE c4=2;
732 Warnings:
733 Warning 1265 Data truncated for column 'c2' at row 2
734 SELECT c2 FROM t1;
735 c2
736 -100.00000
737 -100.00000
738 -100.00000
739 -100000.00000
740 -11111.11133
741 -66666.66406
742 -77777.78125
743 -88888.89062
744 1.00000
745 UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
746 ERROR 23000: Duplicate entry '88888.89062-8' for key 'idx4'
747 SELECT * FROM t1;
748 c1 c2 c3 c4
749 100000.00000 -100000.00000 10000000000 9
750 11111.11133 -11111.11133 1111110000 1
751 22122.22266 1.00000 2222220000 2
752 33233.33203 -100.00000 3333330000 3
753 44344.44531 -100.00000 4444440000 4
754 55455.55469 -100.00000 5555560000 5
755 66666.66406 -66666.66406 6666670000 6
756 77777.78125 -77777.78125 7777780000 7
757 88888.89062 -88888.89062 8888890000 8
758 SELECT * FROM t2;
759 c1 c2 c3
760 11111.11133 1111110000 1
761 22222.22266 2222220000 2
762 33333.33203 3333330000 3
763 SELECT * FROM t3;
764 c1 c2 c3
765 22222.22266 4444440000 1
766 55555.55469 5555560000 2
767 66666.66406 6666670000 3
768 SELECT * FROM t4;
769 c1 c2 c3
770 100000.00000 10000000000 9
771 77777.78125 7777780000 7
772 88888.89062 8888890000 8
773 DROP TABLES IF EXISTS t1,t2,t3,t4;
774 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));
775 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
776 INSERT IGNORE INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
777 Warnings:
778 Warning 1264 Out of range value for column 'c1' at row 1
779 Warning 1264 Out of range value for column 'c2' at row 1
780 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
781 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
782 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
783 INSERT IGNORE INTO t1 VALUES ("-.1","-.1","-.1",8);
784 Warnings:
785 Warning 1264 Out of range value for column 'c1' at row 1
786 INSERT IGNORE INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
787 Warnings:
788 Warning 1264 Out of range value for column 'c1' at row 1
789 Warning 1264 Out of range value for column 'c2' at row 1
790 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
791 SELECT * FROM t1;
792 c1 c2 c3 c4
793 0.00000 -0.10000 -0.1 8
794 0.00000 0.00000 0 6
795 1.00000 1.00000 1 7
796 100.00000 1001.00000 9999.999999 4
797 100.05000 -100.05000 100119 1
798 10000.00000 0.00001 1000000000 10
799 11111.00009 -9999.99999 9999999 2
800 99999.99999 -99999.99999 9999999999 5
801 99999.99999 99999.99999 1000000000.0001 3
802 99999.99999 99999.99999 111111111.11 9
803 UPDATE t1 SET c1='10001.00001' WHERE c2 > 100 ORDER BY c2 LIMIT 1;
804 SELECT c1,c2 FROM t1;
805 c1 c2
806 0.00000 -0.10000
807 0.00000 0.00000
808 1.00000 1.00000
809 100.05000 -100.05000
810 10000.00000 0.00001
811 10001.00001 1001.00000
812 11111.00009 -9999.99999
813 99999.99999 -99999.99999
814 99999.99999 99999.99999
815 99999.99999 99999.99999
816 UPDATE IGNORE t1 SET c1=c2+c3 WHERE c3 < 10000;
817 Warnings:
818 Warning 1264 Out of range value for column 'c1' at row 8
819 SELECT * FROM t1;
820 c1 c2 c3 c4
821 0.00000 -0.10000 -0.1 8
822 0.00000 0.00000 0 6
823 100.05000 -100.05000 100119 1
824 10000.00000 0.00001 1000000000 10
825 11001.00000 1001.00000 9999.999999 4
826 11111.00009 -9999.99999 9999999 2
827 2.00000 1.00000 1 7
828 99999.99999 -99999.99999 9999999999 5
829 99999.99999 99999.99999 1000000000.0001 3
830 99999.99999 99999.99999 111111111.11 9
831 UPDATE t1 SET c2=c2+100 WHERE c1 >=100 ORDER BY c1 LIMIT 4;
832 SELECT * FROM t1;
833 c1 c2 c3 c4
834 0.00000 -0.10000 -0.1 8
835 0.00000 0.00000 0 6
836 100.05000 -0.05000 100119 1
837 10000.00000 100.00001 1000000000 10
838 11001.00000 1101.00000 9999.999999 4
839 11111.00009 -9899.99999 9999999 2
840 2.00000 1.00000 1 7
841 99999.99999 -99999.99999 9999999999 5
842 99999.99999 99999.99999 1000000000.0001 3
843 99999.99999 99999.99999 111111111.11 9
844 UPDATE t1 SET c2=NULL WHERE c4=4;
845 SELECT * FROM t1;
846 c1 c2 c3 c4
847 0.00000 -0.10000 -0.1 8
848 0.00000 0.00000 0 6
849 100.05000 -0.05000 100119 1
850 10000.00000 100.00001 1000000000 10
851 11001.00000 NULL 9999.999999 4
852 11111.00009 -9899.99999 9999999 2
853 2.00000 1.00000 1 7
854 99999.99999 -99999.99999 9999999999 5
855 99999.99999 99999.99999 1000000000.0001 3
856 99999.99999 99999.99999 111111111.11 9
857 UPDATE t1 SET c2=1.0e+5 WHERE c2=NULL;
858 SELECT * FROM t1;
859 c1 c2 c3 c4
860 0.00000 -0.10000 -0.1 8
861 0.00000 0.00000 0 6
862 100.05000 -0.05000 100119 1
863 10000.00000 100.00001 1000000000 10
864 11001.00000 NULL 9999.999999 4
865 11111.00009 -9899.99999 9999999 2
866 2.00000 1.00000 1 7
867 99999.99999 -99999.99999 9999999999 5
868 99999.99999 99999.99999 1000000000.0001 3
869 99999.99999 99999.99999 111111111.11 9
870 UPDATE IGNORE t1 SET c1=-1.0e+2 WHERE c4=2;
871 Warnings:
872 Warning 1264 Out of range value for column 'c1' at row 2
873 SHOW WARNINGS;
874 Level Code Message
875 Warning 1264 Out of range value for column 'c1' at row 2
876 SELECT * FROM t1;
877 c1 c2 c3 c4
878 0.00000 -0.10000 -0.1 8
879 0.00000 -9899.99999 9999999 2
880 0.00000 0.00000 0 6
881 100.05000 -0.05000 100119 1
882 10000.00000 100.00001 1000000000 10
883 11001.00000 NULL 9999.999999 4
884 2.00000 1.00000 1 7
885 99999.99999 -99999.99999 9999999999 5
886 99999.99999 99999.99999 1000000000.0001 3
887 99999.99999 99999.99999 111111111.11 9
888 UPDATE IGNORE t1 SET c1=1.0e+20 WHERE c4=2;
889 Warnings:
890 Warning 1264 Out of range value for column 'c1' at row 2
891 SHOW WARNINGS;
892 Level Code Message
893 Warning 1264 Out of range value for column 'c1' at row 2
894 SELECT * FROM t1;
895 c1 c2 c3 c4
896 0.00000 -0.10000 -0.1 8
897 0.00000 0.00000 0 6
898 100.05000 -0.05000 100119 1
899 10000.00000 100.00001 1000000000 10
900 11001.00000 NULL 9999.999999 4
901 2.00000 1.00000 1 7
902 99999.99999 -9899.99999 9999999 2
903 99999.99999 -99999.99999 9999999999 5
904 99999.99999 99999.99999 1000000000.0001 3
905 99999.99999 99999.99999 111111111.11 9
906 UPDATE t1 SET c1=0.9999999999e+5 WHERE c3=1 AND c4=7;
907 SELECT * FROM t1;
908 c1 c2 c3 c4
909 0.00000 -0.10000 -0.1 8
910 0.00000 0.00000 0 6
911 100.05000 -0.05000 100119 1
912 10000.00000 100.00001 1000000000 10
913 11001.00000 NULL 9999.999999 4
914 99999.99999 -9899.99999 9999999 2
915 99999.99999 -99999.99999 9999999999 5
916 99999.99999 1.00000 1 7
917 99999.99999 99999.99999 1000000000.0001 3
918 99999.99999 99999.99999 111111111.11 9
919 UPDATE t1 SET c1=1.2e+2,c2=c4+c2 WHERE c3=9999999999 OR c2=1.0e-5;
920 SELECT * FROM t1 WHERE c1=1.2e+2;
921 c1 c2 c3 c4
922 120.00000 -99994.99999 9999999999 5
923 UPDATE t1 SET c3=1234567890 WHERE c4 IN (4,5,6);
924 SELECT * FROM t1;
925 c1 c2 c3 c4
926 0.00000 -0.10000 -0.1 8
927 0.00000 0.00000 1234567890 6
928 100.05000 -0.05000 100119 1
929 10000.00000 100.00001 1000000000 10
930 11001.00000 NULL 1234567890 4
931 120.00000 -99994.99999 1234567890 5
932 99999.99999 -9899.99999 9999999 2
933 99999.99999 1.00000 1 7
934 99999.99999 99999.99999 1000000000.0001 3
935 99999.99999 99999.99999 111111111.11 9
936 UPDATE IGNORE t1 SET c1=99999.999999 WHERE c4=7;
937 Warnings:
938 Warning 1264 Out of range value for column 'c1' at row 7
939 SELECT c1,c4 FROM t1;
940 c1 c4
941 0.00000 6
942 0.00000 8
943 100.05000 1
944 10000.00000 10
945 11001.00000 4
946 120.00000 5
947 99999.99999 2
948 99999.99999 3
949 99999.99999 7
950 99999.99999 9
951 UPDATE t1 SET c2=-9999.0099 WHERE c4=5;
952 SELECT c2,c4 FROM t1;
953 c2 c4
954 -0.05000 1
955 -0.10000 8
956 -9899.99999 2
957 -9999.00990 5
958 0.00000 6
959 1.00000 7
960 100.00001 10
961 99999.99999 3
962 99999.99999 9
963 NULL 4
964 SELECT c1 FROM t1 WHERE c1>1000;
965 c1
966 10000.00000
967 11001.00000
968 99999.99999
969 99999.99999
970 99999.99999
971 99999.99999
972 SET SQL_MODE=STRICT_ALL_TABLES;
973 UPDATE t1 SET c1=NULL WHERE c1>1000;
974 ERROR 23000: Column 'c1' cannot be null
975 UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
976 Warnings:
977 Warning 1048 Column 'c1' cannot be null
978 Warning 1048 Column 'c1' cannot be null
979 Warning 1048 Column 'c1' cannot be null
980 Warning 1048 Column 'c1' cannot be null
981 Warning 1048 Column 'c1' cannot be null
982 Warning 1048 Column 'c1' cannot be null
983 SELECT c1 from t1 WHERE c1>1000;
984 c1
985 SET SQL_MODE=DEFAULT;
986 TRUNCATE t1;
987 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);
988 SELECT * FROM t1;
989 c1 c2 c3 c4
990 11111.11111 -11111.11111 1111111111 1
991 22222.22222 -22222.22222 2222222222 2
992 33333.33333 -33333.33333 3333333333 3
993 44444.44444 -44444.44444 4444444444 4
994 55555.55555 -55555.55555 5555555555 5
995 66666.66666 -66666.66666 6666666666 6
996 77777.77777 -77777.77777 7777777777 7
997 88888.88888 -88888.88888 8888888888 8
998 99999.99999 -99999.99999 9999999999 9
999 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
1000 c1
1001 22222.22222
1002 33333.33333
1003 44444.44444
1004 55555.55555
1005 UPDATE t1 SET c1=c1-100, c2=c1+c2 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
1006 SELECT * FROM t1;
1007 c1 c2 c3 c4
1008 11111.11111 -11111.11111 1111111111 1
1009 22122.22222 -100.00000 2222222222 2
1010 33233.33333 -100.00000 3333333333 3
1011 44344.44444 -100.00000 4444444444 4
1012 55455.55555 -100.00000 5555555555 5
1013 66666.66666 -66666.66666 6666666666 6
1014 77777.77777 -77777.77777 7777777777 7
1015 88888.88888 -88888.88888 8888888888 8
1016 99999.99999 -99999.99999 9999999999 9
1017 CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx2(c1,c3));
1018 CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1,c3));
1019 CREATE TABLE t4(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx4(c1,c3));
1020 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
1021 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
1022 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
1023 SELECT * FROM t2;
1024 c1 c2 c3
1025 11111.11111 1111111111 1
1026 22222.22222 2222222222 2
1027 33333.33333 3333333333 3
1028 SELECT * FROM t3;
1029 c1 c2 c3
1030 44444.44444 4444444444 4
1031 55555.55555 5555555555 5
1032 66666.66666 6666666666 6
1033 SELECT * FROM t4;
1034 c1 c2 c3
1035 77777.77777 7777777777 7
1036 88888.88888 8888888888 8
1037 99999.99999 9999999999 9
1038 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' ;
1039 SELECT * FROM t1;
1040 c1 c2 c3 c4
1041 11111.11111 -11111.11111 1111111111 1
1042 22122.22222 -100.00000 2222222222 2
1043 33233.33333 -100.00000 3333333333 3
1044 44344.44444 -100.00000 4444444444 4
1045 55455.55555 -100.00000 5555555555 5
1046 66666.66666 -66666.66666 6666666666 6
1047 77777.77777 -77777.77777 7777777777 7
1048 88888.88888 -88888.88888 8888888888 8
1049 99999.99999 -99999.99999 9999999999 9
1050 SELECT * FROM t1;
1051 c1 c2 c3 c4
1052 11111.11111 -11111.11111 1111111111 1
1053 22122.22222 -100.00000 2222222222 2
1054 33233.33333 -100.00000 3333333333 3
1055 44344.44444 -100.00000 4444444444 4
1056 55455.55555 -100.00000 5555555555 5
1057 66666.66666 -66666.66666 6666666666 6
1058 77777.77777 -77777.77777 7777777777 7
1059 88888.88888 -88888.88888 8888888888 8
1060 99999.99999 -99999.99999 9999999999 9
1061 SELECT * FROM t1;
1062 c1 c2 c3 c4
1063 11111.11111 -11111.11111 1111111111 1
1064 22122.22222 -100.00000 2222222222 2
1065 33233.33333 -100.00000 3333333333 3
1066 44344.44444 -100.00000 4444444444 4
1067 55455.55555 -100.00000 5555555555 5
1068 66666.66666 -66666.66666 6666666666 6
1069 77777.77777 -77777.77777 7777777777 7
1070 88888.88888 -88888.88888 8888888888 8
1071 99999.99999 -99999.99999 9999999999 9
1072 TRUNCATE t2;
1073 TRUNCATE t3;
1074 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
1075 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
1076 UPDATE t2 SET t2.c1='44444.44444', t2.c3=4 WHERE t2.c1='22222.22222' AND t2.c3=2;
1077 SELECT * FROM t2;
1078 c1 c2 c3
1079 11111.11111 1111111111 1
1080 33333.33333 3333333333 3
1081 44444.44444 2222222222 4
1082 update t2 set t2.c1='99999.99999' where c1 BETWEEN '11111' AND '44444';
1083 SELECT * FROM t2;
1084 c1 c2 c3
1085 44444.44444 2222222222 4
1086 99999.99999 1111111111 1
1087 99999.99999 3333333333 3
1088 UPDATE t2 SET c1=c1-1,c2=c2-1 WHERE c1 IN ('44444.44444','99999.99999');
1089 SELECT * FROM t2;
1090 c1 c2 c3
1091 44443.44444 2222222221 4
1092 99998.99999 1111111110 1
1093 99998.99999 3333333332 3
1094 DROP TABLE t2,t3;
1095 CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT);
1096 CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1));
1097 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
1098 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
1099 UPDATE t2,t3 SET t3.c1='22222.22222' WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
1100 UPDATE IGNORE t1 SET c3='asdf' WHERE c1='11111.11111';
1101 Warnings:
1102 Warning 1366 Incorrect double value: 'asdf' for column `test`.`t1`.`c3` at row 1
1103 SELECT c3 FROM t1;
1104 c3
1105 0
1106 2222222222
1107 3333333333
1108 4444444444
1109 5555555555
1110 6666666666
1111 7777777777
1112 8888888888
1113 9999999999
1114 UPDATE IGNORE t1 SET c2="1 e 1" WHERE c4=2;
1115 Warnings:
1116 Warning 1265 Data truncated for column 'c2' at row 2
1117 SELECT c2 FROM t1;
1118 c2
1119 -100.00000
1120 -100.00000
1121 -100.00000
1122 -11111.11111
1123 -66666.66666
1124 -77777.77777
1125 -88888.88888
1126 -99999.99999
1127 1.00000
1128 UPDATE t4 SET c1=88888.88888,c3=8 WHERE c3=7;
1129 ERROR 23000: Duplicate entry '88888.88888-8' for key 'idx4'
1130 SELECT * FROM t1;
1131 c1 c2 c3 c4
1132 11111.11111 -11111.11111 0 1
1133 22122.22222 1.00000 2222222222 2
1134 33233.33333 -100.00000 3333333333 3
1135 44344.44444 -100.00000 4444444444 4
1136 55455.55555 -100.00000 5555555555 5
1137 66666.66666 -66666.66666 6666666666 6
1138 77777.77777 -77777.77777 7777777777 7
1139 88888.88888 -88888.88888 8888888888 8
1140 99999.99999 -99999.99999 9999999999 9
1141 SELECT * FROM t2;
1142 c1 c2 c3
1143 11111.11111 1111111111 1
1144 22222.22222 2222222222 2
1145 33333.33333 3333333333 3
1146 SELECT * FROM t3;
1147 c1 c2 c3
1148 22222.22222 4444444444 1
1149 55555.55555 5555555555 2
1150 66666.66666 6666666666 3
1151 SELECT * FROM t4;
1152 c1 c2 c3
1153 77777.77777 7777777777 7
1154 88888.88888 8888888888 8
1155 99999.99999 9999999999 9
1156 DROP TABLES IF EXISTS t1,t2,t3,t4;