Debian Codegraph @

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

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2;
3 --enable_warnings
4
5 ######## Running INSERT 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,c2));
9 CREATE TABLE t2(c1 DECIMAL(10,0) SIGNED NOT NULL, c2 DECIMAL(10) UNSIGNED NULL, c3 DECIMAL, c4 INT);
10
11 # Test insert values, trailing zeroes are not stripped
12 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100.05000',1);
13 INSERT INTO t2 VALUES('-1000000000','1000000000','1000000000',2);
14
15 # Test no approximation
16 INSERT INTO t1(c1) VALUES('12345.000009');
17
18 # Test insert max values
19 INSERT INTO t1 VALUES('99999.99999','-99999.99999','99999.99999',3);
20 INSERT IGNORE INTO t2 VALUES('-9999999999','-9999999999','9999999999',4);
21
22 # Test insert outside max value, would be clipped to closest endpoint
23 --sorted_result
24 SELECT * FROM t1;
25 --error ER_DUP_ENTRY
26 SET STATEMENT sql_mode = '' FOR
27 INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5);
28 DELETE FROM t1 WHERE c1='99999.99999' AND c2='-99999.99999';
29 --sorted_result
30 SELECT * FROM t1;
31 INSERT IGNORE INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5);
32 INSERT IGNORE INTO t2 VALUES('10000000000','-10000000000','10000000000',6);
33
34 # Test insert leading zero, +/- signs, overflow handling
35 insert into t1 values ("0.0","0.0","0.0",7),("01.0","01.0","01.0",10);
36 insert ignore into t1 values ("-.1","-.1","-.1",13);
37 insert ignore into t1 values ("+111111111.11","+111111111.11","+111111111.11",19);
38 insert ignore into t2 values ("0.0","0.0","0.0",7),("-0.0","-0.0","-0.0",8),("+0.0","+0.0","+0.0",9),("01.0","01.0","01.0",10),("+01.0","+01.0","+01.0",11),("-01.0","-01.0","-01.0",12);
39 insert ignore into t2 values ("-.1","-.1","-.1",13),("+.1","+.1","+.1",14),(".1",".1",".1",15);
40 insert ignore into t2 values ("00000000000001","00000000000001","00000000000001",16),("+0000000000001","+0000000000001","+0000000000001",17),("-0000000000001","-0000000000001","-0000000000001",18);
41 insert ignore into t2 values ("+111111111.11","+111111111.11","+111111111.11",19),("111111111.11","111111111.11","111111111.11",20),("-11111111.11","-11111111.11","-11111111.11",21);
42 insert ignore into t2 values ("-111111111.11","-111111111.11","-111111111.11",22),("+1111111111.11","+1111111111.11","+1111111111.11",23),("1111111111.11","1111111111.11","1111111111.11",24);
43 insert ignore into t2 values ("1e+1000","1e+1000","1e+1000",25),("1e-1000","1e-1000","1e-1000",26),("-1e+1000","-1e+1000","-1e+1000",27);
44 insert ignore into t2 values ("1e+4294967296","1e+4294967296","1e+4294967296",28),("1e-4294967296","1e-4294967296","1e-4294967296",29);
45 insert ignore into t2 values ("1e+18446744073709551615","1e+18446744073709551615","1e+18446744073709551615",30),("1e+18446744073709551616","1e+18446744073709551616","1e+18446744073709551616",31),("1e-9223372036854775807","1e-9223372036854775807","1e-9223372036854775807",32),("1e-9223372036854775809","1e-9223372036854775809","1e-9223372036854775809",33);
46
47 # Now Select
48 --sorted_result
49 SELECT * FROM t1;
50 --sorted_result
51 SELECT * FROM t2;
52 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t1;
53 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t1;
54 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t1;
55 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t2;
56 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t2;
57 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t2;
58
59 # Select using various access methods
60 ## Full table scan ##
61 --sorted_result
62 SELECT * FROM t1;
63 #Check after fix MDEV-31730
64 --disable_cursor_protocol
65 --sorted_result
66 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1;
67 --enable_cursor_protocol
68 --sorted_result
69 SELECT * FROM t1 WHERE c3 = '0.0';
70
71 ## Forward index scan, covering ##
72 --sorted_result
73 SELECT c1,c2 FROM t1;
74
75 ## Backward index scan, covering ##
76 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC;
77
78 ## Forward index scan, non-covering ##
79 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2;
80
81 ## Backward index scan, non-covering ##
82 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2;
83
84 ## ref type access
85 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2;
86 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 LIMIT 2;
87 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC;
88 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC LIMIT 2;
89
90 ## Range access, ordered ##
91 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2;
92 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 LIMIT 2;
93 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2;
94 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 LIMIT 2;
95 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2;
96 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 LIMIT 2;
97 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2;
98 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 LIMIT 2;
99 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2;
100 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 LIMIT 2;
101 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2;
102 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 LIMIT 2;
103 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2;
104 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 LIMIT 2;
105 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2;
106 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 LIMIT 2;
107 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2;
108 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 LIMIT 2;
109 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2;
110 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
111 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2;
112 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
113
114 ## Range access, backwards scan ##
115 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC;
116 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC LIMIT 2;
117 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC;
118 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC LIMIT 2;
119 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC;
120 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC LIMIT 2;
121 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC;
122 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC LIMIT 2;
123 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC;
124 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC LIMIT 2;
125 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC;
126 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC LIMIT 2;
127 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC;
128 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
129 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC;
130 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC LIMIT 2;
131 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC;
132 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC LIMIT 2;
133 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
134 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
135 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
136 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
137 ## Full table scan ##
138 --sorted_result
139 SELECT * FROM t1;
140 #Check after fix MDEV-31730
141 --disable_cursor_protocol
142 --sorted_result
143 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1;
144 --enable_cursor_protocol
145 --sorted_result
146 SELECT * FROM t1 WHERE c3 = '-0.0';
147
148 ## Forward index scan, covering ##
149 --sorted_result
150 SELECT c1,c2 FROM t1;
151
152 ## Backward index scan, covering ##
153 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC;
154
155 ## Forward index scan, non-covering ##
156 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2;
157
158 ## Backward index scan, non-covering ##
159 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2;
160
161 ## ref type access
162 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2;
163 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2;
164 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC;
165 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
166
167 ## Range access, ordered ##
168 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2;
169 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 LIMIT 2;
170 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2;
171 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 LIMIT 2;
172 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2;
173 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 LIMIT 2;
174 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2;
175 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 LIMIT 2;
176 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2;
177 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 LIMIT 2;
178 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2;
179 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 LIMIT 2;
180 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2;
181 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 LIMIT 2;
182 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2;
183 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 LIMIT 2;
184 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2;
185 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 LIMIT 2;
186 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2;
187 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
188 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2;
189 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
190
191 ## Range access, backwards scan ##
192 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC;
193 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
194 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC;
195 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
196 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC;
197 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
198 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC;
199 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
200 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC;
201 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
202 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC;
203 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
204 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC;
205 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
206 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC;
207 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC LIMIT 2;
208 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC;
209 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
210 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
211 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
212 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
213 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
214 ## Full table scan ##
215 --sorted_result
216 SELECT * FROM t1;
217 --sorted_result
218 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1;
219 --sorted_result
220 SELECT * FROM t1 WHERE c3 = '-0.0';
221
222 ## Forward index scan, covering ##
223 --sorted_result
224 SELECT c1,c2 FROM t1;
225
226 ## Backward index scan, covering ##
227 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC;
228
229 ## Forward index scan, non-covering ##
230 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2;
231
232 ## Backward index scan, non-covering ##
233 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2;
234
235 ## ref type access
236 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2;
237 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 LIMIT 2;
238 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC;
239 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
240
241 ## Range access, ordered ##
242 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2;
243 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 LIMIT 2;
244 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2;
245 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 LIMIT 2;
246 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2;
247 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 LIMIT 2;
248 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2;
249 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 LIMIT 2;
250 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2;
251 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 LIMIT 2;
252 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2;
253 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 LIMIT 2;
254 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2;
255 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 LIMIT 2;
256 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2;
257 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 LIMIT 2;
258 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2;
259 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2;
260 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2;
261 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
262 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2;
263 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
264
265 ## Range access, backwards scan ##
266 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC;
267 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
268 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC;
269 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
270 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC;
271 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
272 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC;
273 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
274 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC;
275 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
276 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC;
277 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
278 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC;
279 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC LIMIT 2;
280 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC;
281 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC LIMIT 2;
282 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC;
283 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
284 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
285 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
286 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
287 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
288
289 # Using index for group-by
290 --sorted_result
291 SELECT c1 FROM t1 GROUP BY c1;
292 --sorted_result
293 SELECT DISTINCT c1 FROM t1;
294 --sorted_result
295 SELECT c1,MIN(c2) FROM t1 GROUP BY c1;
296
297 # Test extremes
298 CREATE TABLE t5(c1 DECIMAL(65,0) UNSIGNED NOT NULL, c2 DECIMAL(65,0) SIGNED NULL, c3 DECIMAL, c4 INT, INDEX idx(c1,c2));
299 INSERT IGNORE INTO t5 VALUES('1e+64','-1e+64','1e+64',1),('1e-64','-1e-64','1e-64',2);
300 INSERT IGNORE INTO t5 VALUES('1.2e+65','-1.2e+65','1.2e+65',3),('1.2e-65','-1.2e-65','1.2e-65',4);
301 --sorted_result
302 SELECT * FROM t5;
303 DROP TABLE t5;
304
305 # Test encoding to various bytes
306 CREATE TABLE t5(c1 DECIMAL(18,9) UNSIGNED, c2 DECIMAL(18,9) SIGNED) /* requires 8 bytes */;
307 INSERT INTO t5 VALUES(123456789.123456789,-123456789.123456789);
308 --sorted_result
309 SELECT c1,c2 FROM t5;
310 ALTER TABLE t5 CHANGE c1 c1 DECIMAL(19,9) UNSIGNED, CHANGE c2 c2 DECIMAL(19,9) SIGNED /* requires 9 bytes */;
311 INSERT INTO t5 VALUES(1234567891.123456789,-1234567891.123456789);
312 --sorted_result
313 SELECT c1,c2 FROM t5;
314 ALTER TABLE t5 CHANGE c1 c1 DECIMAL(21,10) UNSIGNED, CHANGE c2 c2 DECIMAL(21,10) SIGNED /* requires 10 bytes */;
315 INSERT INTO t5 VALUES(12345678912.1234567891,-12345678912.1234567891);
316 --sorted_result
317 SELECT c1,c2 FROM t5;
318 ALTER TABLE t5 CHANGE c1 c1 DECIMAL(23,11) UNSIGNED, CHANGE c2 c2 DECIMAL(23,11) SIGNED /* requires 11 bytes */;
319 INSERT INTO t5 VALUES(123456789123.12345678912,-123456789123.12345678912);
320 --sorted_result
321 SELECT c1,c2 FROM t5;
322 ALTER TABLE t5 CHANGE c1 c1 DECIMAL(25,12) UNSIGNED, CHANGE c2 c2 DECIMAL(25,12) SIGNED /* requires 12 bytes */;
323 INSERT INTO t5 VALUES(1234567891234.123456789123,-1234567891234.123456789123);
324 --sorted_result
325 SELECT c1,c2 FROM t5;
326 SELECT SUM(c1),SUM(c2) FROM t5;
327 DROP TABLE t5;
328
329 # Test ROUND() and TRUNCATE()
330 CREATE TABLE t5(c1 DECIMAL(16,6), c2 tinyint);
331 INSERT INTO t5 VALUES(1.1325,3);
332 SELECT ROUND(c1,c2),TRUNCATE(c1,c2) FROM t5;
333 DROP TABLE t5;
334
335 # Test wrong decimal types
336 --error ER_TOO_BIG_PRECISION
337 CREATE TABLE t7(c1 DECIMAL(66,0));
338 --error ER_M_BIGGER_THAN_D
339 CREATE TABLE t7(c1 DECIMAL(5,10));
340 DROP TABLE t1,t2;
341
342 ######## Running INSERT tests for FLOAT ########
343
344 # Create tables
345 CREATE TABLE t1(c1 FLOAT(10,5) UNSIGNED NOT NULL, c2 FLOAT(10,5) SIGNED NULL, c3 FLOAT, c4 INT, UNIQUE INDEX idx(c1,c2));
346 CREATE TABLE t2(c1 FLOAT(10,0) SIGNED NOT NULL, c2 FLOAT(10,0) UNSIGNED NULL, c3 FLOAT, c4 INT);
347
348 # Test insert values, trailing zeroes are not stripped
349 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100.05000',1);
350 INSERT INTO t2 VALUES('-1000000000','1000000000','1000000000',2);
351
352 # Test approximation
353 INSERT INTO t1(c1) VALUES('12345.000009');
354
355 # Test insert max values
356 INSERT INTO t1 VALUES('99999.99999','-99999.99999','99999.99999',3);
357 INSERT IGNORE INTO t2 VALUES('-9999999999','-9999999999','9999999999',4);
358
359 # Test insert outside max value, would be clipped to closest endpoint
360 SELECT * FROM t1;
361 --error ER_DUP_ENTRY
362 SET STATEMENT sql_mode = '' FOR
363 INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5);
364 DELETE FROM t1 WHERE c1='100000.00000' AND c2='-100000.00000';
365 INSERT IGNORE INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5);
366 INSERT IGNORE INTO t2 VALUES('10000000000','-10000000000','10000000000',6);
367
368 # Test insert leading zero, +/- signs, overflow handling
369 insert into t1 values ("0.0","0.0","0.0",7),("01.0","01.0","01.0",10);
370 insert ignore into t1 values ("-.1","-.1","-.1",13);
371 insert ignore into t1 values ("+111111111.11","+111111111.11","+111111111.11",19);
372 insert ignore into t2 values ("0.0","0.0","0.0",7),("-0.0","-0.0","-0.0",8),("+0.0","+0.0","+0.0",9),("01.0","01.0","01.0",10),("+01.0","+01.0","+01.0",11),("-01.0","-01.0","-01.0",12);
373 insert ignore into t2 values ("-.1","-.1","-.1",13),("+.1","+.1","+.1",14),(".1",".1",".1",15);
374 insert ignore into t2 values ("00000000000001","00000000000001","00000000000001",16),("+0000000000001","+0000000000001","+0000000000001",17),("-0000000000001","-0000000000001","-0000000000001",18);
375 insert ignore into t2 values ("+111111111.11","+111111111.11","+111111111.11",19),("111111111.11","111111111.11","111111111.11",20),("-11111111.11","-11111111.11","-11111111.11",21);
376 insert ignore into t2 values ("-111111111.11","-111111111.11","-111111111.11",22),("+1111111111.11","+1111111111.11","+1111111111.11",23),("1111111111.11","1111111111.11","1111111111.11",24);
377 insert ignore into t2 values ("1e+1000","1e+1000","1e+1000",25),("1e-1000","1e-1000","1e-1000",26),("-1e+1000","-1e+1000","-1e+1000",27);
378 insert ignore into t2 values ("1e+4294967296","1e+4294967296","1e+4294967296",28),("1e-4294967296","1e-4294967296","1e-4294967296",29);
379 insert ignore into t2 values ("1e+18446744073709551615","1e+18446744073709551615","1e+18446744073709551615",30),("1e+18446744073709551616","1e+18446744073709551616","1e+18446744073709551616",31),("1e-9223372036854775807","1e-9223372036854775807","1e-9223372036854775807",32),("1e-9223372036854775809","1e-9223372036854775809","1e-9223372036854775809",33);
380
381 # Now Select
382 --sorted_result
383 SELECT * FROM t1;
384 --sorted_result
385 SELECT * FROM t2;
386 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t1;
387 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t1;
388 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t1;
389 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t2;
390 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t2;
391 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t2;
392
393 # Select using various access methods
394 ## Full table scan ##
395 --sorted_result
396 SELECT * FROM t1;
397 #Check after fix MDEV-31730
398 --disable_cursor_protocol
399 --sorted_result
400 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1;
401 --enable_cursor_protocol
402 --sorted_result
403 SELECT * FROM t1 WHERE c3 = '0.0';
404
405 ## Forward index scan, covering ##
406 --sorted_result
407 SELECT c1,c2 FROM t1;
408
409 ## Backward index scan, covering ##
410 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC;
411
412 ## Forward index scan, non-covering ##
413 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2;
414
415 ## Backward index scan, non-covering ##
416 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2;
417
418 ## ref type access
419 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2;
420 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 LIMIT 2;
421 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC;
422 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC LIMIT 2;
423
424 ## Range access, ordered ##
425 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2;
426 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 LIMIT 2;
427 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2;
428 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 LIMIT 2;
429 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2;
430 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 LIMIT 2;
431 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2;
432 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 LIMIT 2;
433 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2;
434 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 LIMIT 2;
435 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2;
436 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 LIMIT 2;
437 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2;
438 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 LIMIT 2;
439 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2;
440 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 LIMIT 2;
441 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2;
442 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 LIMIT 2;
443 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2;
444 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
445 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2;
446 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
447
448 ## Range access, backwards scan ##
449 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC;
450 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC LIMIT 2;
451 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC;
452 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC LIMIT 2;
453 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC;
454 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC LIMIT 2;
455 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC;
456 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC LIMIT 2;
457 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC;
458 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC LIMIT 2;
459 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC;
460 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC LIMIT 2;
461 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC;
462 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
463 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC;
464 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC LIMIT 2;
465 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC;
466 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC LIMIT 2;
467 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
468 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
469 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
470 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
471 ## Full table scan ##
472 --sorted_result
473 SELECT * FROM t1;
474 #Check after fix MDEV-31730
475 --disable_cursor_protocol
476 --sorted_result
477 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1;
478 --enable_cursor_protocol
479 --sorted_result
480 SELECT * FROM t1 WHERE c3 = '-0.0';
481
482 ## Forward index scan, covering ##
483 --sorted_result
484 SELECT c1,c2 FROM t1;
485
486 ## Backward index scan, covering ##
487 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC;
488
489 ## Forward index scan, non-covering ##
490 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2;
491
492 ## Backward index scan, non-covering ##
493 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2;
494
495 ## ref type access
496 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2;
497 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2;
498 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC;
499 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
500
501 ## Range access, ordered ##
502 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2;
503 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 LIMIT 2;
504 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2;
505 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 LIMIT 2;
506 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2;
507 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 LIMIT 2;
508 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2;
509 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 LIMIT 2;
510 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2;
511 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 LIMIT 2;
512 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2;
513 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 LIMIT 2;
514 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2;
515 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 LIMIT 2;
516 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2;
517 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 LIMIT 2;
518 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2;
519 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 LIMIT 2;
520 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2;
521 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
522 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2;
523 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
524
525 ## Range access, backwards scan ##
526 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC;
527 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
528 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC;
529 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
530 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC;
531 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
532 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC;
533 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
534 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC;
535 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
536 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC;
537 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
538 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC;
539 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
540 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC;
541 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC LIMIT 2;
542 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC;
543 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
544 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
545 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
546 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
547 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
548 ## Full table scan ##
549 --sorted_result
550 SELECT * FROM t1;
551 --sorted_result
552 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1;
553 --sorted_result
554 SELECT * FROM t1 WHERE c3 = '-0.0';
555
556 ## Forward index scan, covering ##
557 --sorted_result
558 SELECT c1,c2 FROM t1;
559
560 ## Backward index scan, covering ##
561 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC;
562
563 ## Forward index scan, non-covering ##
564 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2;
565
566 ## Backward index scan, non-covering ##
567 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2;
568
569 ## ref type access
570 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2;
571 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 LIMIT 2;
572 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC;
573 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
574
575 ## Range access, ordered ##
576 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2;
577 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 LIMIT 2;
578 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2;
579 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 LIMIT 2;
580 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2;
581 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 LIMIT 2;
582 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2;
583 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 LIMIT 2;
584 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2;
585 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 LIMIT 2;
586 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2;
587 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 LIMIT 2;
588 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2;
589 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 LIMIT 2;
590 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2;
591 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 LIMIT 2;
592 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2;
593 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2;
594 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2;
595 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
596 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2;
597 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
598
599 ## Range access, backwards scan ##
600 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC;
601 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
602 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC;
603 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
604 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC;
605 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
606 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC;
607 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
608 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC;
609 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
610 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC;
611 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
612 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC;
613 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC LIMIT 2;
614 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC;
615 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC LIMIT 2;
616 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC;
617 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
618 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
619 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
620 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
621 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
622
623 # Using index for group-by
624 --sorted_result
625 SELECT c1 FROM t1 GROUP BY c1;
626 --sorted_result
627 SELECT DISTINCT c1 FROM t1;
628 --sorted_result
629 SELECT c1,MIN(c2) FROM t1 GROUP BY c1;
630
631 # Test encoding to 4 or 8 bytes
632 CREATE TABLE t5(c1 FLOAT(23,0) UNSIGNED NOT NULL, c2 FLOAT(23,0) SIGNED NULL, c3 FLOAT, c4 INT, INDEX idx(c1,c2));
633 INSERT INTO t5 VALUES('1e+22','-1e+22','1e+22',1),('1e-22','-1e-22','1e-22',2);
634 #Output is platform dependent
635 --disable_result_log
636 --sorted_result
637 SELECT * FROM t5;
638 --enable_result_log
639 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
640 ALTER TABLE t5 CHANGE c1 c1 FLOAT(24,0) SIGNED NOT NULL, CHANGE c2 c2 FLOAT(24,0) UNSIGNED NULL;
641 INSERT INTO t5 VALUES('-1e+23','1e+23','1e+23',3),('-1e-23','1e-23','1e-23',4);
642 #Output is platform dependent
643 --disable_result_log
644 --sorted_result
645 SELECT * FROM t5;
646 --enable_result_log
647 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
648 ALTER TABLE t5 CHANGE c1 c1 FLOAT(53,0) UNSIGNED, CHANGE c2 c2 FLOAT(53,0) SIGNED;
649 INSERT IGNORE INTO t5 VALUES('1e+52','-1e+52','1e+52',5),('1e-52','-1e-52','1e-52',6);
650 INSERT IGNORE INTO t5 VALUES('1e+53','-1e+53','1e+53',7),('1e-53','-1e-53','1e-53',8);
651
652 #Output is platform dependent
653 --replace_column 1 # 2 #
654 --sorted_result
655 SELECT * FROM t5;
656 DROP TABLE t5;
657
658 # Test ROUND() and TRUNCATE()
659 CREATE TABLE t5(c1 FLOAT(16,6), c2 tinyint);
660 INSERT INTO t5 VALUES(1.1325,3);
661 SELECT ROUND(c1,c2),TRUNCATE(c1,c2) FROM t5;
662 DROP TABLE t5;
663
664 # Test wrong decimal types
665 --error ER_M_BIGGER_THAN_D
666 CREATE TABLE t7(c1 FLOAT(5,10));
667 DROP TABLE t1,t2;
668
669 ######## Running INSERT tests for DOUBLE ########
670
671 # Create tables
672 CREATE TABLE t1(c1 DOUBLE(10,5) UNSIGNED NOT NULL, c2 DOUBLE(10,5) SIGNED NULL, c3 DOUBLE, c4 INT, UNIQUE INDEX idx(c1,c2));
673 CREATE TABLE t2(c1 DOUBLE(10,0) SIGNED NOT NULL, c2 DOUBLE(10,0) UNSIGNED NULL, c3 DOUBLE, c4 INT);
674
675 # Test insert values, trailing zeroes are not stripped
676 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100.05000',1);
677 INSERT INTO t2 VALUES('-1000000000','1000000000','1000000000',2);
678
679 # Test approximation
680 INSERT INTO t1(c1) VALUES('12345.000009');
681
682 # Test insert max values
683 INSERT INTO t1 VALUES('99999.99999','-99999.99999','99999.99999',3);
684 INSERT IGNORE INTO t2 VALUES('-9999999999','-9999999999','9999999999',4);
685
686 # Test insert outside max value, would be clipped to closest endpoint
687 SELECT * FROM t1;
688 --error ER_DUP_ENTRY
689 SET STATEMENT sql_mode = '' FOR
690 INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5);
691 DELETE FROM t1 WHERE c1='99999.99999' AND c2='-99999.99999';
692 INSERT IGNORE INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5);
693 INSERT IGNORE INTO t2 VALUES('10000000000','-10000000000','10000000000',6);
694
695 # Test insert leading zero, +/- signs, overflow handling
696 insert into t1 values ("0.0","0.0","0.0",7),("01.0","01.0","01.0",10);
697 insert ignore into t1 values ("-.1","-.1","-.1",13);
698 insert ignore into t1 values ("+111111111.11","+111111111.11","+111111111.11",19);
699 insert ignore into t2 values ("0.0","0.0","0.0",7),("-0.0","-0.0","-0.0",8),("+0.0","+0.0","+0.0",9),("01.0","01.0","01.0",10),("+01.0","+01.0","+01.0",11),("-01.0","-01.0","-01.0",12);
700 insert ignore into t2 values ("-.1","-.1","-.1",13),("+.1","+.1","+.1",14),(".1",".1",".1",15);
701 insert ignore into t2 values ("00000000000001","00000000000001","00000000000001",16),("+0000000000001","+0000000000001","+0000000000001",17),("-0000000000001","-0000000000001","-0000000000001",18);
702 insert ignore into t2 values ("+111111111.11","+111111111.11","+111111111.11",19),("111111111.11","111111111.11","111111111.11",20),("-11111111.11","-11111111.11","-11111111.11",21);
703 insert ignore into t2 values ("-111111111.11","-111111111.11","-111111111.11",22),("+1111111111.11","+1111111111.11","+1111111111.11",23),("1111111111.11","1111111111.11","1111111111.11",24);
704 insert ignore into t2 values ("1e+1000","1e+1000","1e+1000",25),("1e-1000","1e-1000","1e-1000",26),("-1e+1000","-1e+1000","-1e+1000",27);
705 insert ignore into t2 values ("1e+4294967296","1e+4294967296","1e+4294967296",28),("1e-4294967296","1e-4294967296","1e-4294967296",29);
706 insert ignore into t2 values ("1e+18446744073709551615","1e+18446744073709551615","1e+18446744073709551615",30),("1e+18446744073709551616","1e+18446744073709551616","1e+18446744073709551616",31),("1e-9223372036854775807","1e-9223372036854775807","1e-9223372036854775807",32),("1e-9223372036854775809","1e-9223372036854775809","1e-9223372036854775809",33);
707
708 # Now Select
709 --sorted_result
710 SELECT * FROM t1;
711 --sorted_result
712 SELECT * FROM t2;
713 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t1;
714 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t1;
715 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t1;
716 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t2;
717 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t2;
718 #SELECT below outputs 'inf' and 'infinity' for Linux/SunOS respectively
719 --replace_column 4 inf 5 inf
720 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t2;
721
722 # Select using various access methods
723 ## Full table scan ##
724 --sorted_result
725 SELECT * FROM t1;
726 #Check after fix MDEV-31730
727 --disable_cursor_protocol
728 --sorted_result
729 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1;
730 --enable_cursor_protocol
731 --sorted_result
732 SELECT * FROM t1 WHERE c3 = '0.0';
733
734 ## Forward index scan, covering ##
735 --sorted_result
736 SELECT c1,c2 FROM t1;
737
738 ## Backward index scan, covering ##
739 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC;
740
741 ## Forward index scan, non-covering ##
742 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2;
743
744 ## Backward index scan, non-covering ##
745 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2;
746
747 ## ref type access
748 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2;
749 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 LIMIT 2;
750 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC;
751 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC LIMIT 2;
752
753 ## Range access, ordered ##
754 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2;
755 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 LIMIT 2;
756 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2;
757 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 LIMIT 2;
758 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2;
759 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 LIMIT 2;
760 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2;
761 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 LIMIT 2;
762 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2;
763 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 LIMIT 2;
764 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2;
765 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 LIMIT 2;
766 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2;
767 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 LIMIT 2;
768 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2;
769 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 LIMIT 2;
770 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2;
771 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 LIMIT 2;
772 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2;
773 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
774 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2;
775 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
776
777 ## Range access, backwards scan ##
778 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC;
779 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC LIMIT 2;
780 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC;
781 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC LIMIT 2;
782 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC;
783 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC LIMIT 2;
784 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC;
785 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC LIMIT 2;
786 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC;
787 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC LIMIT 2;
788 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC;
789 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC LIMIT 2;
790 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC;
791 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
792 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC;
793 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC LIMIT 2;
794 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC;
795 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC LIMIT 2;
796 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
797 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
798 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
799 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
800 ## Full table scan ##
801 --sorted_result
802 SELECT * FROM t1;
803 #Check after fix MDEV-31730
804 --disable_cursor_protocol
805 --sorted_result
806 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1;
807 --enable_cursor_protocol
808 --sorted_result
809 SELECT * FROM t1 WHERE c3 = '-0.0';
810
811 ## Forward index scan, covering ##
812 --sorted_result
813 SELECT c1,c2 FROM t1;
814
815 ## Backward index scan, covering ##
816 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC;
817
818 ## Forward index scan, non-covering ##
819 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2;
820
821 ## Backward index scan, non-covering ##
822 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2;
823
824 ## ref type access
825 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2;
826 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2;
827 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC;
828 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
829
830 ## Range access, ordered ##
831 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2;
832 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 LIMIT 2;
833 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2;
834 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 LIMIT 2;
835 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2;
836 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 LIMIT 2;
837 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2;
838 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 LIMIT 2;
839 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2;
840 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 LIMIT 2;
841 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2;
842 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 LIMIT 2;
843 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2;
844 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 LIMIT 2;
845 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2;
846 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 LIMIT 2;
847 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2;
848 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 LIMIT 2;
849 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2;
850 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
851 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2;
852 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
853
854 ## Range access, backwards scan ##
855 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC;
856 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
857 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC;
858 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
859 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC;
860 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
861 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC;
862 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
863 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC;
864 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
865 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC;
866 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
867 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC;
868 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
869 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC;
870 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC LIMIT 2;
871 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC;
872 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
873 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
874 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
875 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
876 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
877 ## Full table scan ##
878 --sorted_result
879 SELECT * FROM t1;
880 --sorted_result
881 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1;
882 --sorted_result
883 SELECT * FROM t1 WHERE c3 = '-0.0';
884
885 ## Forward index scan, covering ##
886 --sorted_result
887 SELECT c1,c2 FROM t1;
888
889 ## Backward index scan, covering ##
890 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC;
891
892 ## Forward index scan, non-covering ##
893 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2;
894
895 ## Backward index scan, non-covering ##
896 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2;
897
898 ## ref type access
899 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2;
900 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 LIMIT 2;
901 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC;
902 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
903
904 ## Range access, ordered ##
905 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2;
906 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 LIMIT 2;
907 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2;
908 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 LIMIT 2;
909 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2;
910 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 LIMIT 2;
911 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2;
912 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 LIMIT 2;
913 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2;
914 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 LIMIT 2;
915 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2;
916 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 LIMIT 2;
917 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2;
918 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 LIMIT 2;
919 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2;
920 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 LIMIT 2;
921 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2;
922 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2;
923 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2;
924 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
925 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2;
926 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
927
928 ## Range access, backwards scan ##
929 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC;
930 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
931 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC;
932 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
933 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC;
934 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
935 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC;
936 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
937 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC;
938 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
939 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC;
940 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
941 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC;
942 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC LIMIT 2;
943 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC;
944 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC LIMIT 2;
945 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC;
946 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
947 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
948 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
949 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
950 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
951
952 # Using index for group-by
953 --sorted_result
954 SELECT c1 FROM t1 GROUP BY c1;
955 --sorted_result
956 SELECT DISTINCT c1 FROM t1;
957 --sorted_result
958 SELECT c1,MIN(c2) FROM t1 GROUP BY c1;
959
960 # Test encoding to 4 or 8 bytes
961 CREATE TABLE t5(c1 DOUBLE(23,0) UNSIGNED NOT NULL, c2 DOUBLE(23,0) SIGNED NULL, c3 DOUBLE, c4 INT, INDEX idx(c1,c2));
962 INSERT INTO t5 VALUES('1e+22','-1e+22','1e+22',1),('1e-22','-1e-22','1e-22',2);
963 --sorted_result
964 SELECT * FROM t5;
965 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
966 ALTER TABLE t5 CHANGE c1 c1 DOUBLE(24,0) SIGNED NOT NULL, CHANGE c2 c2 DOUBLE(24,0) UNSIGNED NULL;
967 INSERT INTO t5 VALUES('-1e+23','1e+23','1e+23',3),('-1e-23','1e-23','1e-23',4);
968 #Output is platform dependent
969 --disable_result_log
970 --sorted_result
971 SELECT * FROM t5;
972 --enable_result_log
973 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
974 ALTER TABLE t5 CHANGE c1 c1 DOUBLE(53,0) UNSIGNED, CHANGE c2 c2 DOUBLE(53,0) SIGNED;
975 INSERT INTO t5 VALUES('1e+52','-1e+52','1e+52',5),('1e-52','-1e-52','1e-52',6);
976 INSERT INTO t5 VALUES('1e+53','-1e+53','1e+53',7),('1e-53','-1e-53','1e-53',8);
977 #Output is platform dependent
978 --disable_result_log
979 --sorted_result
980 SELECT * FROM t5;
981 --enable_result_log
982 DROP TABLE t5;
983
984 # Test ROUND() and TRUNCATE()
985 CREATE TABLE t5(c1 DOUBLE(16,6), c2 tinyint);
986 INSERT INTO t5 VALUES(1.1325,3);
987 SELECT ROUND(c1,c2),TRUNCATE(c1,c2) FROM t5;
988 DROP TABLE t5;
989
990 # Test wrong decimal types
991 --error ER_M_BIGGER_THAN_D
992 CREATE TABLE t7(c1 DOUBLE(5,10));
993 DROP TABLE t1,t2;
994