Debian Codegraph @

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

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