Debian Codegraph @

mysql-test/suite/engines/iuds/t/delete_decimal.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2,t3,t4;
3 --enable_warnings
4 SET sql_mode='NO_ENGINE_SUBSTITUTION';
5
6 ######## Running DELETE tests for DECIMAL ########
7
8 # Create tables
9 CREATE TABLE t1(c1 DECIMAL(10,5) UNSIGNED NOT NULL, c2 DECIMAL(10,5) SIGNED NULL, c3 DECIMAL, c4 INT, UNIQUE INDEX idx(c1,c4));
10
11 # Inserting data into table t1
12
13 # Trailing zeroes are not stripped, D=5
14 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
15
16 # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped;
17 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
18
19 # c1, c2, c3 will be rounded automatically
20 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
21
22 # Inserting maximum values
23 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
24
25 # Test insert leading zero, +/- signs, overflow handling
26 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
27 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
28 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
29
30 # Inserting in scientific notations
31 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
32 #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
33
34 --sorted_result
35 SELECT * FROM t1;
36
37 # Deletion starts here
38
39 # Delete by order by limit
40 DELETE FROM t1 WHERE c2 > 100 ORDER BY c2 LIMIT 1;
41 --sorted_result
42 SELECT c1,c2 FROM t1;
43
44 # Delete using range values
45 DELETE FROM t1 WHERE c3=1 AND c4=7;
46 --sorted_result
47 SELECT * FROM t1;
48 DELETE FROM t1 WHERE c3=9999999999 OR c2=1.0e-5;
49 --sorted_result
50 SELECT * FROM t1 WHERE c1=1.2e+2;
51 DELETE FROM t1 WHERE c4 IN (4,5,6);
52 --sorted_result
53 SELECT * FROM t1;
54
55 TRUNCATE t1;
56 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);
57 --sorted_result
58 SELECT * FROM t1;
59
60 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
61 DELETE FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
62 --sorted_result
63 SELECT * FROM t1;
64
65 # Multi table delete
66 CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx2(c1,c3));
67 CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1,c3));
68 CREATE TABLE t4(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx4(c1,c3));
69 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
70 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
71 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
72 --sorted_result
73 SELECT * FROM t2;
74 --sorted_result
75 SELECT * FROM t3;
76 --sorted_result
77 SELECT * FROM t4;
78 DELETE t1,t2,t3,t4 FROM t1,t2,t3,t4 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888';
79 --sorted_result
80 SELECT * FROM t1;
81 --sorted_result
82 SELECT * FROM t1;
83 --sorted_result
84 SELECT * FROM t1;
85
86 # Delete using various access methods
87 TRUNCATE t2;
88 TRUNCATE t3;
89 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
90 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
91
92 # Delete using Const
93 # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
94 DELETE FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
95 --sorted_result
96 SELECT * FROM t2;
97
98 # Delete using range
99 # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
100 DELETE FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
101 --sorted_result
102 SELECT * FROM t2;
103 # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
104 DELETE FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
105 --sorted_result
106 SELECT * FROM t2;
107
108 # Delete using eq_ref
109 DROP TABLE t2,t3;
110 CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT);
111 CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1));
112 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
113 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
114 # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
115 DELETE t2,t3 FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
116
117 --sorted_result
118 SELECT * FROM t1;
119 --sorted_result
120 SELECT * FROM t2;
121 --sorted_result
122 SELECT * FROM t3;
123 --sorted_result
124 SELECT * FROM t4;
125 # Droping the tables
126 --disable_warnings
127 DROP TABLES IF EXISTS t1,t2,t3,t4;
128 --enable_warnings
129
130 ######## Running DELETE tests for FLOAT ########
131
132 # Create tables
133 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));
134
135 # Inserting data into table t1
136
137 # Trailing zeroes are not stripped, D=5
138 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
139
140 # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped;
141 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
142
143 # c1, c2, c3 will be rounded automatically
144 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
145
146 # Inserting maximum values
147 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
148
149 # Test insert leading zero, +/- signs, overflow handling
150 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
151 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
152 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
153
154 # Inserting in scientific notations
155 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
156 #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
157
158 --sorted_result
159 SELECT * FROM t1;
160
161 # Deletion starts here
162
163 # Delete by order by limit
164 DELETE FROM t1 WHERE c2 > 100 ORDER BY c2 LIMIT 1;
165 --sorted_result
166 SELECT c1,c2 FROM t1;
167
168 # Delete using range values
169 DELETE FROM t1 WHERE c3=1 AND c4=7;
170 --sorted_result
171 SELECT * FROM t1;
172 DELETE FROM t1 WHERE c3=9999999999 OR c2=1.0e-5;
173 --sorted_result
174 SELECT * FROM t1 WHERE c1=1.2e+2;
175 DELETE FROM t1 WHERE c4 IN (4,5,6);
176 --sorted_result
177 SELECT * FROM t1;
178
179 TRUNCATE t1;
180 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);
181 --replace_result 88888.89063 88888.89062
182 --sorted_result
183 SELECT * FROM t1;
184
185 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
186 DELETE FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
187 --replace_result 88888.89063 88888.89062
188 --sorted_result
189 SELECT * FROM t1;
190
191 # Multi table delete
192 CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx2(c1,c3));
193 CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1,c3));
194 CREATE TABLE t4(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx4(c1,c3));
195 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
196 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
197 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
198 --sorted_result
199 SELECT * FROM t2;
200 --sorted_result
201 SELECT * FROM t3;
202 --replace_result 88888.89063 88888.89062
203 --sorted_result
204 SELECT * FROM t4;
205 DELETE t1,t2,t3,t4 FROM t1,t2,t3,t4 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888';
206 --replace_result 88888.89063 88888.89062
207 --sorted_result
208 SELECT * FROM t1;
209 --replace_result 88888.89063 88888.89062
210 --sorted_result
211 SELECT * FROM t1;
212 --replace_result 88888.89063 88888.89062
213 --sorted_result
214 SELECT * FROM t1;
215
216 # Delete using various access methods
217 TRUNCATE t2;
218 TRUNCATE t3;
219 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
220 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
221
222 # Delete using Const
223 # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
224 DELETE FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
225 --sorted_result
226 SELECT * FROM t2;
227
228 # Delete using range
229 # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
230 DELETE FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
231 --sorted_result
232 SELECT * FROM t2;
233 # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
234 DELETE FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
235 --sorted_result
236 SELECT * FROM t2;
237
238 # Delete using eq_ref
239 DROP TABLE t2,t3;
240 CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT);
241 CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1));
242 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
243 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
244 # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
245 DELETE t2,t3 FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
246 --replace_result 88888.89063 88888.89062
247 --sorted_result
248 SELECT * FROM t1;
249 --sorted_result
250 SELECT * FROM t2;
251 --sorted_result
252 SELECT * FROM t3;
253 --replace_result 88888.89063 88888.89062
254 --sorted_result
255 SELECT * FROM t4;
256 # Droping the tables
257 --disable_warnings
258 DROP TABLES IF EXISTS t1,t2,t3,t4;
259 --enable_warnings
260
261 ######## Running DELETE tests for DOUBLE ########
262
263 # Create tables
264 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));
265
266 # Inserting data into table t1
267
268 # Trailing zeroes are not stripped, D=5
269 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2);
270
271 # DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped;
272 INSERT INTO t1 VALUES('1000000','10000000','1000000000.0001',3);
273
274 # c1, c2, c3 will be rounded automatically
275 INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4);
276
277 # Inserting maximum values
278 INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5);
279
280 # Test insert leading zero, +/- signs, overflow handling
281 INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7);
282 INSERT INTO t1 VALUES ("-.1","-.1","-.1",8);
283 INSERT INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9);
284
285 # Inserting in scientific notations
286 INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10);
287 #INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11);
288
289 --sorted_result
290 SELECT * FROM t1;
291
292 # Deletion starts here
293
294 # Delete by order by limit
295 DELETE FROM t1 WHERE c2 > 100 ORDER BY c2 LIMIT 1;
296 --sorted_result
297 SELECT c1,c2 FROM t1;
298
299 # Delete using range values
300 DELETE FROM t1 WHERE c3=1 AND c4=7;
301 --sorted_result
302 SELECT * FROM t1;
303 DELETE FROM t1 WHERE c3=9999999999 OR c2=1.0e-5;
304 --sorted_result
305 SELECT * FROM t1 WHERE c1=1.2e+2;
306 DELETE FROM t1 WHERE c4 IN (4,5,6);
307 --sorted_result
308 SELECT * FROM t1;
309
310 TRUNCATE t1;
311 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);
312 --sorted_result
313 SELECT * FROM t1;
314
315 SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
316 DELETE FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1;
317 --sorted_result
318 SELECT * FROM t1;
319
320 # Multi table delete
321 CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx2(c1,c3));
322 CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1,c3));
323 CREATE TABLE t4(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx4(c1,c3));
324 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
325 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
326 INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9);
327 --sorted_result
328 SELECT * FROM t2;
329 --sorted_result
330 SELECT * FROM t3;
331 --sorted_result
332 SELECT * FROM t4;
333 DELETE t1,t2,t3,t4 FROM t1,t2,t3,t4 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888';
334 --sorted_result
335 SELECT * FROM t1;
336 --sorted_result
337 SELECT * FROM t1;
338 --sorted_result
339 SELECT * FROM t1;
340
341 # Delete using various access methods
342 TRUNCATE t2;
343 TRUNCATE t3;
344 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
345 INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6);
346
347 # Delete using Const
348 # EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
349 DELETE FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2;
350 --sorted_result
351 SELECT * FROM t2;
352
353 # Delete using range
354 # EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
355 DELETE FROM t2 WHERE c1 BETWEEN '11111' AND '44444';
356 --sorted_result
357 SELECT * FROM t2;
358 # EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
359 DELETE FROM t2 WHERE c1 IN ('44444.44444','99999.99999');
360 --sorted_result
361 SELECT * FROM t2;
362
363 # Delete using eq_ref
364 DROP TABLE t2,t3;
365 CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT);
366 CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1));
367 INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3);
368 INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3);
369 # EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
370 DELETE t2,t3 FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
371
372 --sorted_result
373 SELECT * FROM t1;
374 --sorted_result
375 SELECT * FROM t2;
376 --sorted_result
377 SELECT * FROM t3;
378 --sorted_result
379 SELECT * FROM t4;
380 # Droping the tables
381 --disable_warnings
382 DROP TABLES IF EXISTS t1,t2,t3,t4;
383 --enable_warnings
384 SET sql_mode=default;