1
drop table if exists t1,t2;
2
SELECT 10,10.0,10.,.1e+2,100.0e-1;
3
10 10.0 10. .1e+2 100.0e-1
5
SELECT 6e-16, -6e-16, --6e-16, -6e-16+1.000000;
6
6e-16 -6e-16 --6e-16 -6e-16+1.000000
7
6e-16 -6e-16 6e-16 0.999999999999999
8
SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1;
9
1e1 1.e1 1.0e1 1e+1 1.e+1 1.0e+1 1e-1 1.e-1 1.0e-1
10
10 10 10 10 10 10 0.1 0.1 0.1
11
SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01;
12
0.001e+1 0.001e-1 -0.001e+01 -0.001e-01
13
0.01 0.0001 -0.01 -0.0001
14
SELECT 123.23E+02,-123.23E-02,"123.23E+02"+0.0,"-123.23E-02"+0.0;
15
123.23E+02 -123.23E-02 "123.23E+02"+0.0 "-123.23E-02"+0.0
16
12323 -1.2323 12323 -1.2323
17
SELECT 2147483647E+02,21474836.47E+06;
18
2147483647E+02 21474836.47E+06
19
214748364700 21474836470000
20
create table t1 (f1 float,f2 float);
22
Field Type Null Default Default_is_NULL On_Update
25
insert into t1 values(10,10),(1e+5,1e+5),(1234567890,1234567890),(1e+10,1e+10),(1e+15,1e+15),(1e+20,1e+20),(1e+50,1e+50),(1e+150,1e+150);
26
insert into t1 values(-10,-10),(1e-5,1e-5),(1e-10,1e-10),(1e-15,1e-15),(1e-20,1e-20),(1e-50,1e-50),(1e-150,1e-150);
32
10000000000 10000000000
39
0.0000000001 0.0000000001
40
0.000000000000001 0.000000000000001
45
create table t1 (datum double);
46
insert into t1 values (0.5),(1.0),(1.5),(2.0),(2.5);
54
select * from t1 where datum < 1.5;
58
select * from t1 where datum > 1.5;
62
select * from t1 where datum = 1.5;
66
create table t1 (a decimal(7,3) not null, key (a));
67
insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1");
68
select a from t1 order by a;
75
select min(a) from t1;
79
create table t1 (c1 double, c2 varchar(20));
80
insert t1 values (121,"16");
81
select c1 + c1 * (c2 / 100) as col from t1;
84
create table t2 select c1 + c1 * (c2 / 100) as col1, round(c1, 5) as col2, round(c1, 35) as col3, sqrt(c1*1e-15) col4 from t1;
87
140.36 121.00000 121 0.000000347850542618522
90
t2 CREATE TABLE `t2` (
91
`col1` DOUBLE DEFAULT NULL,
92
`col2` DOUBLE(22,5) DEFAULT NULL,
93
`col3` DOUBLE DEFAULT NULL,
94
`col4` DOUBLE DEFAULT NULL
95
) ENGINE=DEFAULT COLLATE = utf8_general_ci
97
create table t1 (a float);
98
insert into t1 values (1);
99
select max(a),min(a),avg(a) from t1;
103
create table t1 (f float, f2 float, f3 float, d double, d2 float, d3 double, de decimal, de2 decimal(6), de3 decimal(5,2), n numeric, n2 numeric(8), n3 numeric(7,6));
104
show columns from t1;
105
Field Type Null Default Default_is_NULL On_Update
119
create table t1 (a decimal(7,3) not null, key (a));
120
insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1");
121
select a from t1 order by a;
128
select min(a) from t1;
132
create table t1 (c20 char);
133
insert into t1 values (5000.0);
134
ERROR 22001: Data too long for column 'c20' at row 1
135
insert into t1 values (0.5e4);
136
ERROR 22001: Data too long for column 'c20' at row 1
138
create table t1 (d1 double, d2 double);
139
insert into t1 set d1 = -1.0;
140
update t1 set d2 = d1;
145
create table t1 (f float(4,3));
146
insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
147
ERROR 22003: Out of range value for column 'f' at row 1
150
drop table if exists t1;
151
create table t1 (f double(4,3));
152
insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
153
ERROR 22003: Out of range value for column 'f' at row 1
156
drop table if exists t1;
157
create table t1 (c char(20));
158
insert into t1 values (5e-28);
163
create table t1 (c char(6));
164
insert into t1 values (2e5),(2e6),(2e-4),(2e-5);
172
CREATE TEMPORARY TABLE t1 (
174
recdesc varchar(50) NOT NULL,
177
INSERT INTO t1 VALUES (108, 'Has 108 as key');
178
INSERT INTO t1 VALUES (109, 'Has 109 as key');
179
select * from t1 where reckey=108;
182
select * from t1 where reckey=1.08E2;
185
select * from t1 where reckey=109;
188
select * from t1 where reckey=1.09E2;
192
create table t1 (d double);
193
create table t2 (d double);
194
insert into t1 values ("100000000.0");
195
insert into t2 values ("1.23456780");
196
create table t3 select * from t2 union select * from t1;
201
show create table t3;
203
t3 CREATE TABLE `t3` (
204
`d` DOUBLE DEFAULT NULL
205
) ENGINE=DEFAULT COLLATE = utf8_general_ci
206
drop table t1, t2, t3;
207
create table t1 select 105213674794682365.00 + 0.0 x;
211
Field Type Null Default Default_is_NULL On_Update
214
create table t1 select 0.0 x;
216
Field Type Null Default Default_is_NULL On_Update
218
create table t2 select 105213674794682365.00 y;
220
Field Type Null Default Default_is_NULL On_Update
222
create table t3 select x+y a from t1,t2;
226
Field Type Null Default Default_is_NULL On_Update
229
select 1e-308, 1.00000001e-300, 100000000e-300;
230
1e-308 1.00000001e-300 100000000e-300
231
1e-308 1.00000001e-300 1e-292
235
create table t1(a int, b double(8, 2));
236
insert into t1 values
237
(1, 28.50), (1, 121.85), (1, 157.23), (1, 1351.00), (1, -1965.35), (1, 81.75),
238
(1, 217.08), (1, 7.94), (4, 96.07), (4, 6404.65), (4, -6500.72), (2, 100.00),
239
(5, 5.00), (5, -2104.80), (5, 2033.80), (5, 0.07), (5, 65.93),
240
(3, -4986.24), (3, 5.00), (3, 4857.34), (3, 123.74), (3, 0.16),
241
(6, -1695.31), (6, 1003.77), (6, 499.72), (6, 191.82);
242
explain select sum(b) s from t1 group by a;
243
id select_type table type possible_keys key key_len ref rows Extra
244
1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using temporary; Using filesort
245
select sum(b) s from t1 group by a;
253
select sum(b) s from t1 group by a having s <> 0;
256
select sum(b) s from t1 group by a having s <> 0 order by s;
259
select sum(b) s from t1 group by a having s <=> 0;
266
select sum(b) s from t1 group by a having s <=> 0 order by s;
273
alter table t1 add key (a, b);
274
explain select sum(b) s from t1 group by a;
275
id select_type table type possible_keys key key_len ref rows Extra
276
1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using temporary; Using filesort
277
select sum(b) s from t1 group by a;
285
select sum(b) s from t1 group by a having s <> 0;
288
select sum(b) s from t1 group by a having s <> 0 order by s;
291
select sum(b) s from t1 group by a having s <=> 0;
298
select sum(b) s from t1 group by a having s <=> 0 order by s;
307
create table t1 (s1 float(0,2));
308
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 's1').
309
create table t1 (s1 float(1,2));
310
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 's1').
311
create table t1 (f1 double(200, 0));
312
insert into t1 values (1e199), (-1e199);
313
insert into t1 values (1e200), (-1e200);
314
insert into t1 values (2e200), (-2e200);
315
ERROR 22003: Out of range value for column 'f1' at row 1
316
select f1 + 0e0 from t1;
323
create table t1 (f1 float(30, 0));
324
insert into t1 values (1e29), (-1e29);
325
insert into t1 values (1e30), (-1e30);
326
insert into t1 values (2e30), (-2e30);
327
ERROR 22003: Out of range value for column 'f1' at row 1
328
select f1 + 0e0 from t1;
335
create table t1 (c char(6));
336
insert into t1 values (2e6),(2e-5);
342
CREATE TABLE d1 (d DOUBLE);
343
INSERT INTO d1 VALUES (1.7976931348623157E+308);
347
INSERT INTO d1 VALUES (1.79769313486232e+308);
348
ERROR 22007: Illegal double '1.79769313486232e+308' value found during parsing
353
create table t1 (a char(20));
354
insert into t1 values (1.225e-05);