3
# Numeric floating point.
6
drop table if exists t1,t2;
9
SELECT 10,10.0,10.,.1e+2,100.0e-1;
10
SELECT 6e-16, -6e-16, --6e-16, -6e-16+1.000000;
11
SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1;
12
SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01;
13
SELECT 123.23E+02,-123.23E-02,"123.23E+02"+0.0,"-123.23E-02"+0.0;
14
SELECT 2147483647E+02,21474836.47E+06;
16
create table t1 (f1 float primary key,f2 float);
17
# We mask out Privileges column because it differs for embedded server
20
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);
21
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);
26
create table t1 (datum double primary key);
27
insert into t1 values (0.5),(1.0),(1.5),(2.0),(2.5);
29
select * from t1 where datum < 1.5;
30
select * from t1 where datum > 1.5;
31
select * from t1 where datum = 1.5;
35
# BUG#3612, BUG#4393, BUG#4356, BUG#4394
38
create table t1 (c1 double primary key, c2 varchar(20));
39
insert t1 values (121,"16");
40
select c1 + c1 * (c2 / 100) as col from t1;
41
create temporary table t2 engine=myisam as select c1 + c1 * (c2 / 100) as col1, round(c1, 5) as col2, round(c1, 35) as col3, sqrt(c1*1e-15) col4 from t1;
42
# Floats are a bit different in PS
46
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
50
# Bug #1022: When a table contains a 'float' field,
51
# and one of the functions MAX, MIN, or AVG is used on that field,
54
create table t1 (a float primary key);
55
insert into t1 values (1);
56
select max(a),min(a),avg(a) from t1;
60
# float in a char(1) field
62
create table t1 (c20 char primary key);
63
--error ER_DATA_TOO_LONG
64
insert into t1 values (5000.0);
65
--error ER_DATA_TOO_LONG
66
insert into t1 values (0.5e4);
69
# Don't allow 'double' to be set to a negative value (Bug #7700)
70
create table t1 (d1 double primary key, d2 double);
71
insert into t1 set d1 = -1.0;
72
update t1 set d2 = d1;
76
# Ensure that maximum values as the result of number of decimals
77
# being specified in table schema are enforced (Bug #7361)
78
create table t1 (f float(4,3) primary key);
79
--error ER_WARN_DATA_OUT_OF_RANGE
80
insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
82
drop table if exists t1;
83
create table t1 (f double(4,3) primary key);
84
--error ER_WARN_DATA_OUT_OF_RANGE
85
insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
87
drop table if exists t1;
89
# Check conversion of floats to character field (Bug #7774)
90
create table t1 (c char(20) primary key);
91
insert into t1 values (5e-28);
94
create table t1 (c char(6) primary key);
95
insert into t1 values (2e5),(2e6),(2e-4),(2e-5);
100
# Test of comparison of integer with float-in-range (Bug #7840)
101
# This is needed because some ODBC applications (like Foxpro) uses
102
# floats for everything.
105
CREATE TEMPORARY TABLE t1 (
107
recdesc varchar(50) NOT NULL,
111
INSERT INTO t1 VALUES (108, 'Has 108 as key');
112
INSERT INTO t1 VALUES (109, 'Has 109 as key');
113
select * from t1 where reckey=108;
114
select * from t1 where reckey=1.08E2;
115
select * from t1 where reckey=109;
116
select * from t1 where reckey=1.09E2;
120
# Bug #13372 (decimal union)
122
create table t1 (d double primary key);
123
create table t2 (d double primary key);
124
insert into t1 values ("100000000.0");
125
insert into t2 values ("1.23456780");
126
create table t3 (d double primary key) as select t2.d from t2 union select t1.d from t1;
128
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
129
show create table t3;
130
drop table t1, t2, t3;
134
# Bug #9855 (inconsistent column type for create select
136
create temporary table t1 engine=myisam as select 105213674794682365.00 + 0.0 x;
141
create temporary table t1 engine=myisam as select 0.0 x;
143
create temporary table t2 engine=myisam as select 105213674794682365.00 y;
145
create temporary table t3 engine=myisam as select x+y a from t1,t2;
151
# Bug #22129: A small double precision number becomes zero
153
# check if underflows are detected correctly
154
select 1e-308, 1.00000001e-300, 100000000e-300;
156
# check if overflows are detected correctly
160
# Bug #19690: ORDER BY eliminates rows from the result
162
create table t1(a int, b double(8, 2), pk int auto_increment primary key);
163
insert into t1 (a,b) values
164
(1, 28.50), (1, 121.85), (1, 157.23), (1, 1351.00), (1, -1965.35), (1, 81.75),
165
(1, 217.08), (1, 7.94), (4, 96.07), (4, 6404.65), (4, -6500.72), (2, 100.00),
166
(5, 5.00), (5, -2104.80), (5, 2033.80), (5, 0.07), (5, 65.93),
167
(3, -4986.24), (3, 5.00), (3, 4857.34), (3, 123.74), (3, 0.16),
168
(6, -1695.31), (6, 1003.77), (6, 499.72), (6, 191.82);
169
explain select sum(b) s from t1 group by a;
170
select sum(b) s from t1 group by a;
171
select sum(b) s from t1 group by a having s <> 0;
172
select sum(b) s from t1 group by a having s <> 0 order by s;
173
select sum(b) s from t1 group by a having s <=> 0;
174
select sum(b) s from t1 group by a having s <=> 0 order by s;
175
alter table t1 add key (a, b);
176
explain select sum(b) s from t1 group by a;
177
select sum(b) s from t1 group by a;
178
select sum(b) s from t1 group by a having s <> 0;
179
select sum(b) s from t1 group by a having s <> 0 order by s;
180
select sum(b) s from t1 group by a having s <=> 0;
181
select sum(b) s from t1 group by a having s <=> 0 order by s;
184
--echo End of 4.1 tests
187
# bug #12694 (float(m,d) specifications)
190
--error ER_M_BIGGER_THAN_D
191
create table t1 (s1 float(0,2));
192
--error ER_M_BIGGER_THAN_D
193
create table t1 (s1 float(1,2));
195
# Bug #28121 "INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits"
198
create table t1 (f1 double(200, 0) primary key);
199
insert into t1 values (1e199), (-1e199);
200
insert into t1 values (1e200), (-1e200);
201
--error ER_WARN_DATA_OUT_OF_RANGE
202
insert into t1 values (2e200), (-2e200);
203
select f1 + 0e0 from t1;
206
create table t1 (f1 float(30, 0) primary key);
207
insert into t1 values (1e29), (-1e29);
208
insert into t1 values (1e30), (-1e30);
209
--error ER_WARN_DATA_OUT_OF_RANGE
210
insert into t1 values (2e30), (-2e30);
211
select f1 + 0e0 from t1;
215
# Bug #12860 "Difference in zero padding of exponent between Unix and Windows"
218
create table t1 (c char(6) primary key);
219
insert into t1 values (2e6),(2e-5);
224
# Bug #21497 "DOUBLE truncated to unusable value"
227
CREATE TABLE d1 (d DOUBLE primary key);
228
INSERT INTO d1 VALUES (1.7976931348623157E+308);
230
--error ER_ILLEGAL_VALUE_FOR_TYPE
231
INSERT INTO d1 VALUES (1.79769313486232e+308);
236
# Bug #26788 "mysqld (debug) aborts when inserting specific numbers into char
240
create table t1 (a char(20) primary key);
241
insert into t1 values (1.225e-05);
245
--echo End of 5.0 tests