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