Debian Codegraph @

mysql-test/suite/engines/iuds/r/delete_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 DELETE FROM t1 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 11111.00009 -9999.99999
50 99999.99999 -99999.99999
51 99999.99999 99999.99999
52 99999.99999 99999.99999
53 DELETE FROM t1 WHERE c3=1 AND c4=7;
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 11111.00009 -9999.99999 9999999 2
61 99999.99999 -99999.99999 9999999999 5
62 99999.99999 99999.99999 1000000000 3
63 99999.99999 99999.99999 111111111 9
64 DELETE FROM t1 WHERE c3=9999999999 OR c2=1.0e-5;
65 SELECT * FROM t1 WHERE c1=1.2e+2;
66 c1 c2 c3 c4
67 DELETE FROM t1 WHERE c4 IN (4,5,6);
68 SELECT * FROM t1;
69 c1 c2 c3 c4
70 0.00000 -0.10000 0 8
71 100.05000 -100.05000 100119 1
72 11111.00009 -9999.99999 9999999 2
73 99999.99999 99999.99999 1000000000 3
74 99999.99999 99999.99999 111111111 9
75 TRUNCATE t1;
76 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);
77 SELECT * FROM t1;
78 c1 c2 c3 c4
79 11111.11111 -11111.11111 1111111111 1
80 22222.22222 -22222.22222 2222222222 2
81 33333.33333 -33333.33333 3333333333 3
82 44444.44444 -44444.44444 4444444444 4
83 55555.55555 -55555.55555 5555555555 5
84 66666.66666 -66666.66666 6666666666 6
85 77777.77777 -77777.77777 7777777777 7
86 88888.88888 -88888.88888 8888888888 8
87 99999.99999 -99999.99999 9999999999 9
88 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
89 c1
90 22222.22222
91 33333.33333
92 44444.44444
93 55555.55555
94 DELETE FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
95 SELECT * FROM t1;
96 c1 c2 c3 c4
97 11111.11111 -11111.11111 1111111111 1
98 66666.66666 -66666.66666 6666666666 6
99 77777.77777 -77777.77777 7777777777 7
100 88888.88888 -88888.88888 8888888888 8
101 99999.99999 -99999.99999 9999999999 9
102 CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx2(c1,c3));
103 CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1,c3));
104 CREATE TABLE t4(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx4(c1,c3));
105 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
106 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
107 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
108 SELECT * FROM t2;
109 c1 c2 c3
110 11111.11111 1111111111 1
111 22222.22222 2222222222 2
112 33333.33333 3333333333 3
113 SELECT * FROM t3;
114 c1 c2 c3
115 44444.44444 4444444444 4
116 55555.55555 5555555555 5
117 66666.66666 6666666666 6
118 SELECT * FROM t4;
119 c1 c2 c3
120 77777.77777 7777777777 7
121 88888.88888 8888888888 8
122 99999.99999 9999999999 9
123 DELETE t1,t2,t3,t4 FROM t1,t2,t3,t4 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888';
124 SELECT * FROM t1;
125 c1 c2 c3 c4
126 11111.11111 -11111.11111 1111111111 1
127 66666.66666 -66666.66666 6666666666 6
128 77777.77777 -77777.77777 7777777777 7
129 88888.88888 -88888.88888 8888888888 8
130 99999.99999 -99999.99999 9999999999 9
131 SELECT * FROM t1;
132 c1 c2 c3 c4
133 11111.11111 -11111.11111 1111111111 1
134 66666.66666 -66666.66666 6666666666 6
135 77777.77777 -77777.77777 7777777777 7
136 88888.88888 -88888.88888 8888888888 8
137 99999.99999 -99999.99999 9999999999 9
138 SELECT * FROM t1;
139 c1 c2 c3 c4
140 11111.11111 -11111.11111 1111111111 1
141 66666.66666 -66666.66666 6666666666 6
142 77777.77777 -77777.77777 7777777777 7
143 88888.88888 -88888.88888 8888888888 8
144 99999.99999 -99999.99999 9999999999 9
145 TRUNCATE t2;
146 TRUNCATE t3;
147 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
148 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
149 DELETE FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
150 SELECT * FROM t2;
151 c1 c2 c3
152 11111.11111 1111111111 1
153 33333.33333 3333333333 3
154 DELETE FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
155 SELECT * FROM t2;
156 c1 c2 c3
157 DELETE FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
158 SELECT * FROM t2;
159 c1 c2 c3
160 DROP TABLE t2,t3;
161 CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT);
162 CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1));
163 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
164 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
165 DELETE t2,t3 FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
166 SELECT * FROM t1;
167 c1 c2 c3 c4
168 11111.11111 -11111.11111 1111111111 1
169 66666.66666 -66666.66666 6666666666 6
170 77777.77777 -77777.77777 7777777777 7
171 88888.88888 -88888.88888 8888888888 8
172 99999.99999 -99999.99999 9999999999 9
173 SELECT * FROM t2;
174 c1 c2 c3
175 22222.22222 2222222222 2
176 33333.33333 3333333333 3
177 SELECT * FROM t3;
178 c1 c2 c3
179 55555.55555 5555555555 2
180 66666.66666 6666666666 3
181 SELECT * FROM t4;
182 c1 c2 c3
183 77777.77777 7777777777 7
184 88888.88888 8888888888 8
185 99999.99999 9999999999 9
186 DROP TABLES IF EXISTS t1,t2,t3,t4;
187 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));
188 Warnings:
189 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
190 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
191 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
192 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
193 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
194 Warnings:
195 Warning 1264 Out of range value for column 'c1' at row 1
196 Warning 1264 Out of range value for column 'c2' at row 1
197 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
198 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
199 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
200 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
201 Warnings:
202 Warning 1264 Out of range value for column 'c1' at row 1
203 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
204 Warnings:
205 Warning 1264 Out of range value for column 'c1' at row 1
206 Warning 1264 Out of range value for column 'c2' at row 1
207 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
208 SELECT * FROM t1;
209 c1 c2 c3 c4
210 0.00000 -0.10000 -0.1 8
211 0.00000 0.00000 0 6
212 1.00000 1.00000 1 7
213 100.00000 1001.00000 10000 4
214 100.05000 -100.05000 100119 1
215 10000.00000 0.00001 1000000000 10
216 100000.00000 -100000.00000 10000000000 5
217 100000.00000 100000.00000 1000000000 3
218 100000.00000 100000.00000 111111000 9
219 11111.00000 -10000.00000 10000000 2
220 DELETE FROM t1 WHERE c2 > 100 ORDER BY c2 LIMIT 1;
221 SELECT c1,c2 FROM t1;
222 c1 c2
223 0.00000 -0.10000
224 0.00000 0.00000
225 1.00000 1.00000
226 100.05000 -100.05000
227 10000.00000 0.00001
228 100000.00000 -100000.00000
229 100000.00000 100000.00000
230 100000.00000 100000.00000
231 11111.00000 -10000.00000
232 DELETE FROM t1 WHERE c3=1 AND c4=7;
233 SELECT * FROM t1;
234 c1 c2 c3 c4
235 0.00000 -0.10000 -0.1 8
236 0.00000 0.00000 0 6
237 100.05000 -100.05000 100119 1
238 10000.00000 0.00001 1000000000 10
239 100000.00000 -100000.00000 10000000000 5
240 100000.00000 100000.00000 1000000000 3
241 100000.00000 100000.00000 111111000 9
242 11111.00000 -10000.00000 10000000 2
243 DELETE FROM t1 WHERE c3=9999999999 OR c2=1.0e-5;
244 SELECT * FROM t1 WHERE c1=1.2e+2;
245 c1 c2 c3 c4
246 DELETE FROM t1 WHERE c4 IN (4,5,6);
247 SELECT * FROM t1;
248 c1 c2 c3 c4
249 0.00000 -0.10000 -0.1 8
250 100.05000 -100.05000 100119 1
251 100000.00000 100000.00000 1000000000 3
252 100000.00000 100000.00000 111111000 9
253 11111.00000 -10000.00000 10000000 2
254 TRUNCATE t1;
255 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);
256 SELECT * FROM t1;
257 c1 c2 c3 c4
258 100000.00000 -100000.00000 10000000000 9
259 11111.11133 -11111.11133 1111110000 1
260 22222.22266 -22222.22266 2222220000 2
261 33333.33203 -33333.33203 3333330000 3
262 44444.44531 -44444.44531 4444440000 4
263 55555.55469 -55555.55469 5555560000 5
264 66666.66406 -66666.66406 6666670000 6
265 77777.78125 -77777.78125 7777780000 7
266 88888.89062 -88888.89062 8888890000 8
267 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
268 c1
269 22222.22266
270 33333.33203
271 44444.44531
272 55555.55469
273 DELETE FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
274 SELECT * FROM t1;
275 c1 c2 c3 c4
276 100000.00000 -100000.00000 10000000000 9
277 11111.11133 -11111.11133 1111110000 1
278 66666.66406 -66666.66406 6666670000 6
279 77777.78125 -77777.78125 7777780000 7
280 88888.89062 -88888.89062 8888890000 8
281 CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx2(c1,c3));
282 Warnings:
283 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
284 CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1,c3));
285 Warnings:
286 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
287 CREATE TABLE t4(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx4(c1,c3));
288 Warnings:
289 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
290 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
291 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
292 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
293 SELECT * FROM t2;
294 c1 c2 c3
295 11111.11133 1111110000 1
296 22222.22266 2222220000 2
297 33333.33203 3333330000 3
298 SELECT * FROM t3;
299 c1 c2 c3
300 44444.44531 4444440000 4
301 55555.55469 5555560000 5
302 66666.66406 6666670000 6
303 SELECT * FROM t4;
304 c1 c2 c3
305 100000.00000 10000000000 9
306 77777.78125 7777780000 7
307 88888.89062 8888890000 8
308 DELETE t1,t2,t3,t4 FROM t1,t2,t3,t4 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888';
309 SELECT * FROM t1;
310 c1 c2 c3 c4
311 100000.00000 -100000.00000 10000000000 9
312 11111.11133 -11111.11133 1111110000 1
313 66666.66406 -66666.66406 6666670000 6
314 77777.78125 -77777.78125 7777780000 7
315 88888.89062 -88888.89062 8888890000 8
316 SELECT * FROM t1;
317 c1 c2 c3 c4
318 100000.00000 -100000.00000 10000000000 9
319 11111.11133 -11111.11133 1111110000 1
320 66666.66406 -66666.66406 6666670000 6
321 77777.78125 -77777.78125 7777780000 7
322 88888.89062 -88888.89062 8888890000 8
323 SELECT * FROM t1;
324 c1 c2 c3 c4
325 100000.00000 -100000.00000 10000000000 9
326 11111.11133 -11111.11133 1111110000 1
327 66666.66406 -66666.66406 6666670000 6
328 77777.78125 -77777.78125 7777780000 7
329 88888.89062 -88888.89062 8888890000 8
330 TRUNCATE t2;
331 TRUNCATE t3;
332 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
333 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
334 DELETE FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
335 SELECT * FROM t2;
336 c1 c2 c3
337 11111.11133 1111110000 1
338 33333.33203 3333330000 3
339 DELETE FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
340 SELECT * FROM t2;
341 c1 c2 c3
342 DELETE FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
343 SELECT * FROM t2;
344 c1 c2 c3
345 DROP TABLE t2,t3;
346 CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT);
347 Warnings:
348 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
349 CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1));
350 Warnings:
351 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
352 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
353 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
354 DELETE t2,t3 FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
355 SELECT * FROM t1;
356 c1 c2 c3 c4
357 100000.00000 -100000.00000 10000000000 9
358 11111.11133 -11111.11133 1111110000 1
359 66666.66406 -66666.66406 6666670000 6
360 77777.78125 -77777.78125 7777780000 7
361 88888.89062 -88888.89062 8888890000 8
362 SELECT * FROM t2;
363 c1 c2 c3
364 22222.22266 2222220000 2
365 33333.33203 3333330000 3
366 SELECT * FROM t3;
367 c1 c2 c3
368 55555.55469 5555560000 2
369 66666.66406 6666670000 3
370 SELECT * FROM t4;
371 c1 c2 c3
372 100000.00000 10000000000 9
373 77777.78125 7777780000 7
374 88888.89062 8888890000 8
375 DROP TABLES IF EXISTS t1,t2,t3,t4;
376 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));
377 Warnings:
378 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
379 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
380 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
381 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
382 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
383 Warnings:
384 Warning 1264 Out of range value for column 'c1' at row 1
385 Warning 1264 Out of range value for column 'c2' at row 1
386 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
387 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
388 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
389 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
390 Warnings:
391 Warning 1264 Out of range value for column 'c1' at row 1
392 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
393 Warnings:
394 Warning 1264 Out of range value for column 'c1' at row 1
395 Warning 1264 Out of range value for column 'c2' at row 1
396 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
397 SELECT * FROM t1;
398 c1 c2 c3 c4
399 0.00000 -0.10000 -0.1 8
400 0.00000 0.00000 0 6
401 1.00000 1.00000 1 7
402 100.00000 1001.00000 9999.999999 4
403 100.05000 -100.05000 100119 1
404 10000.00000 0.00001 1000000000 10
405 11111.00009 -9999.99999 9999999 2
406 99999.99999 -99999.99999 9999999999 5
407 99999.99999 99999.99999 1000000000.0001 3
408 99999.99999 99999.99999 111111111.11 9
409 DELETE FROM t1 WHERE c2 > 100 ORDER BY c2 LIMIT 1;
410 SELECT c1,c2 FROM t1;
411 c1 c2
412 0.00000 -0.10000
413 0.00000 0.00000
414 1.00000 1.00000
415 100.05000 -100.05000
416 10000.00000 0.00001
417 11111.00009 -9999.99999
418 99999.99999 -99999.99999
419 99999.99999 99999.99999
420 99999.99999 99999.99999
421 DELETE FROM t1 WHERE c3=1 AND c4=7;
422 SELECT * FROM t1;
423 c1 c2 c3 c4
424 0.00000 -0.10000 -0.1 8
425 0.00000 0.00000 0 6
426 100.05000 -100.05000 100119 1
427 10000.00000 0.00001 1000000000 10
428 11111.00009 -9999.99999 9999999 2
429 99999.99999 -99999.99999 9999999999 5
430 99999.99999 99999.99999 1000000000.0001 3
431 99999.99999 99999.99999 111111111.11 9
432 DELETE FROM t1 WHERE c3=9999999999 OR c2=1.0e-5;
433 SELECT * FROM t1 WHERE c1=1.2e+2;
434 c1 c2 c3 c4
435 DELETE FROM t1 WHERE c4 IN (4,5,6);
436 SELECT * FROM t1;
437 c1 c2 c3 c4
438 0.00000 -0.10000 -0.1 8
439 100.05000 -100.05000 100119 1
440 11111.00009 -9999.99999 9999999 2
441 99999.99999 99999.99999 1000000000.0001 3
442 99999.99999 99999.99999 111111111.11 9
443 TRUNCATE t1;
444 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);
445 SELECT * FROM t1;
446 c1 c2 c3 c4
447 11111.11111 -11111.11111 1111111111 1
448 22222.22222 -22222.22222 2222222222 2
449 33333.33333 -33333.33333 3333333333 3
450 44444.44444 -44444.44444 4444444444 4
451 55555.55555 -55555.55555 5555555555 5
452 66666.66666 -66666.66666 6666666666 6
453 77777.77777 -77777.77777 7777777777 7
454 88888.88888 -88888.88888 8888888888 8
455 99999.99999 -99999.99999 9999999999 9
456 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
457 c1
458 22222.22222
459 33333.33333
460 44444.44444
461 55555.55555
462 DELETE FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
463 SELECT * FROM t1;
464 c1 c2 c3 c4
465 11111.11111 -11111.11111 1111111111 1
466 66666.66666 -66666.66666 6666666666 6
467 77777.77777 -77777.77777 7777777777 7
468 88888.88888 -88888.88888 8888888888 8
469 99999.99999 -99999.99999 9999999999 9
470 CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx2(c1,c3));
471 Warnings:
472 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
473 CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1,c3));
474 Warnings:
475 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
476 CREATE TABLE t4(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx4(c1,c3));
477 Warnings:
478 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
479 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
480 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
481 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
482 SELECT * FROM t2;
483 c1 c2 c3
484 11111.11111 1111111111 1
485 22222.22222 2222222222 2
486 33333.33333 3333333333 3
487 SELECT * FROM t3;
488 c1 c2 c3
489 44444.44444 4444444444 4
490 55555.55555 5555555555 5
491 66666.66666 6666666666 6
492 SELECT * FROM t4;
493 c1 c2 c3
494 77777.77777 7777777777 7
495 88888.88888 8888888888 8
496 99999.99999 9999999999 9
497 DELETE t1,t2,t3,t4 FROM t1,t2,t3,t4 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888';
498 SELECT * FROM t1;
499 c1 c2 c3 c4
500 11111.11111 -11111.11111 1111111111 1
501 66666.66666 -66666.66666 6666666666 6
502 77777.77777 -77777.77777 7777777777 7
503 88888.88888 -88888.88888 8888888888 8
504 99999.99999 -99999.99999 9999999999 9
505 SELECT * FROM t1;
506 c1 c2 c3 c4
507 11111.11111 -11111.11111 1111111111 1
508 66666.66666 -66666.66666 6666666666 6
509 77777.77777 -77777.77777 7777777777 7
510 88888.88888 -88888.88888 8888888888 8
511 99999.99999 -99999.99999 9999999999 9
512 SELECT * FROM t1;
513 c1 c2 c3 c4
514 11111.11111 -11111.11111 1111111111 1
515 66666.66666 -66666.66666 6666666666 6
516 77777.77777 -77777.77777 7777777777 7
517 88888.88888 -88888.88888 8888888888 8
518 99999.99999 -99999.99999 9999999999 9
519 TRUNCATE t2;
520 TRUNCATE t3;
521 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
522 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
523 DELETE FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
524 SELECT * FROM t2;
525 c1 c2 c3
526 11111.11111 1111111111 1
527 33333.33333 3333333333 3
528 DELETE FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
529 SELECT * FROM t2;
530 c1 c2 c3
531 DELETE FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
532 SELECT * FROM t2;
533 c1 c2 c3
534 DROP TABLE t2,t3;
535 CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT);
536 Warnings:
537 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
538 CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1));
539 Warnings:
540 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
541 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
542 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
543 DELETE t2,t3 FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
544 SELECT * FROM t1;
545 c1 c2 c3 c4
546 11111.11111 -11111.11111 1111111111 1
547 66666.66666 -66666.66666 6666666666 6
548 77777.77777 -77777.77777 7777777777 7
549 88888.88888 -88888.88888 8888888888 8
550 99999.99999 -99999.99999 9999999999 9
551 SELECT * FROM t2;
552 c1 c2 c3
553 22222.22222 2222222222 2
554 33333.33333 3333333333 3
555 SELECT * FROM t3;
556 c1 c2 c3
557 55555.55555 5555555555 2
558 66666.66666 6666666666 3
559 SELECT * FROM t4;
560 c1 c2 c3
561 77777.77777 7777777777 7
562 88888.88888 8888888888 8
563 99999.99999 9999999999 9
564 DROP TABLES IF EXISTS t1,t2,t3,t4;
565 SET sql_mode=default;