Debian Codegraph @

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