1
by brian
clean slate |
1 |
# Description |
2 |
# ----------- |
|
3 |
# Numeric floating point. |
|
4 |
||
5 |
--disable_warnings
|
|
6 |
drop table if exists t1,t2; |
|
7 |
--enable_warnings
|
|
8 |
||
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; |
|
15 |
||
506
by Brian Aker
Added back more tests. |
16 |
create table t1 (f1 float,f2 float); |
1
by brian
clean slate |
17 |
# We mask out Privileges column because it differs for embedded server |
18 |
--replace_column 8 #
|
|
1273.13.37
by Brian Aker
Remove "full" syntax. |
19 |
show columns from t1; |
1
by brian
clean slate |
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); |
|
22 |
select * from t1; |
|
23 |
drop table t1; |
|
24 |
||
25 |
create table t1 (datum double); |
|
26 |
insert into t1 values (0.5),(1.0),(1.5),(2.0),(2.5); |
|
27 |
select * from t1; |
|
28 |
select * from t1 where datum < 1.5; |
|
29 |
select * from t1 where datum > 1.5; |
|
30 |
select * from t1 where datum = 1.5; |
|
31 |
drop table t1; |
|
32 |
||
33 |
create table t1 (a decimal(7,3) not null, key (a)); |
|
34 |
insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1"); |
|
35 |
select a from t1 order by a; |
|
36 |
select min(a) from t1; |
|
37 |
drop table t1; |
|
38 |
||
39 |
#
|
|
40 |
# BUG#3612, BUG#4393, BUG#4356, BUG#4394 |
|
41 |
#
|
|
42 |
||
43 |
create table t1 (c1 double, c2 varchar(20)); |
|
44 |
insert t1 values (121,"16"); |
|
45 |
select c1 + c1 * (c2 / 100) as col from t1; |
|
46 |
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; |
|
47 |
# Floats are a bit different in PS |
|
48 |
--disable_ps_protocol
|
|
49 |
select * from t2; |
|
50 |
--enable_ps_protocol
|
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
51 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
|
1
by brian
clean slate |
52 |
show create table t2; |
53 |
drop table t1,t2; |
|
54 |
||
55 |
# Bug #1022: When a table contains a 'float' field, |
|
56 |
# and one of the functions MAX, MIN, or AVG is used on that field, |
|
57 |
# the system crashes. |
|
58 |
||
59 |
create table t1 (a float); |
|
60 |
insert into t1 values (1); |
|
61 |
select max(a),min(a),avg(a) from t1; |
|
62 |
drop table t1; |
|
63 |
||
64 |
#
|
|
65 |
# FLOAT/DOUBLE/DECIMAL handling |
|
66 |
#
|
|
67 |
||
506
by Brian Aker
Added back more tests. |
68 |
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)); |
1
by brian
clean slate |
69 |
# We mask out Privileges column because it differs for embedded server |
70 |
--replace_column 8 #
|
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
71 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
|
1273.13.37
by Brian Aker
Remove "full" syntax. |
72 |
show columns from t1; |
1
by brian
clean slate |
73 |
drop table t1; |
74 |
||
75 |
create table t1 (a decimal(7,3) not null, key (a)); |
|
76 |
insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1"); |
|
77 |
select a from t1 order by a; |
|
78 |
select min(a) from t1; |
|
79 |
drop table t1; |
|
80 |
||
81 |
#
|
|
82 |
# float in a char(1) field |
|
83 |
#
|
|
84 |
create table t1 (c20 char); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
85 |
--error ER_DATA_TOO_LONG
|
1
by brian
clean slate |
86 |
insert into t1 values (5000.0); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
87 |
--error ER_DATA_TOO_LONG
|
1
by brian
clean slate |
88 |
insert into t1 values (0.5e4); |
89 |
drop table t1; |
|
90 |
||
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
91 |
# Don't allow 'double' to be set to a negative value (Bug #7700) |
92 |
create table t1 (d1 double, d2 double);
|
|
1
by brian
clean slate |
93 |
insert into t1 set d1 = -1.0;
|
94 |
update t1 set d2 = d1;
|
|
95 |
select * from t1;
|
|
96 |
drop table t1;
|
|
97 |
||
98 |
# Ensure that maximum values as the result of number of decimals
|
|
99 |
# being specified in table schema are enforced (Bug #7361)
|
|
100 |
create table t1 (f float(4,3));
|
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
101 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
102 |
insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
|
103 |
select * from t1;
|
|
104 |
drop table if exists t1;
|
|
105 |
create table t1 (f double(4,3));
|
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
106 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
107 |
insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
|
108 |
select * from t1;
|
|
109 |
drop table if exists t1;
|
|
110 |
||
111 |
# Check conversion of floats to character field (Bug #7774)
|
|
112 |
create table t1 (c char(20));
|
|
113 |
insert into t1 values (5e-28);
|
|
114 |
select * from t1;
|
|
115 |
drop table t1;
|
|
116 |
create table t1 (c char(6));
|
|
117 |
insert into t1 values (2e5),(2e6),(2e-4),(2e-5);
|
|
118 |
select * from t1;
|
|
119 |
drop table t1;
|
|
120 |
||
121 |
#
|
|
122 |
# Test of comparison of integer with float-in-range (Bug #7840)
|
|
123 |
# This is needed because some ODBC applications (like Foxpro) uses
|
|
124 |
# floats for everything.
|
|
125 |
#
|
|
126 |
||
1063.9.42
by Stewart Smith
type_float.test for MyISAM as temp only |
127 |
CREATE TEMPORARY TABLE t1 (
|
413.2.2
by Brian Aker
Removed UNSIGNED from parser. |
128 |
reckey int NOT NULL,
|
1
by brian
clean slate |
129 |
recdesc varchar(50) NOT NULL,
|
130 |
PRIMARY KEY (reckey)
|
|
506
by Brian Aker
Added back more tests. |
131 |
) ENGINE=MyISAM;
|
1
by brian
clean slate |
132 |
|
133 |
INSERT INTO t1 VALUES (108, 'Has 108 as key'); |
|
134 |
INSERT INTO t1 VALUES (109, 'Has 109 as key'); |
|
135 |
select * from t1 where reckey=108; |
|
136 |
select * from t1 where reckey=1.08E2; |
|
137 |
select * from t1 where reckey=109; |
|
138 |
select * from t1 where reckey=1.09E2; |
|
139 |
drop table t1; |
|
140 |
||
141 |
#
|
|
142 |
# Bug #13372 (decimal union) |
|
143 |
#
|
|
506
by Brian Aker
Added back more tests. |
144 |
create table t1 (d double); |
145 |
create table t2 (d double); |
|
1
by brian
clean slate |
146 |
insert into t1 values ("100000000.0"); |
147 |
insert into t2 values ("1.23456780"); |
|
148 |
create table t3 select * from t2 union select * from t1; |
|
149 |
select * from t3; |
|
942.3.1
by Vladimir Kolesnikov
test generalizations |
150 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
|
1
by brian
clean slate |
151 |
show create table t3; |
152 |
drop table t1, t2, t3; |
|
153 |
||
154 |
||
155 |
#
|
|
156 |
# Bug #9855 (inconsistent column type for create select |
|
157 |
#
|
|
158 |
create table t1 select 105213674794682365.00 + 0.0 x; |
|
159 |
show warnings; |
|
160 |
desc t1; |
|
161 |
drop table t1; |
|
162 |
||
163 |
create table t1 select 0.0 x; |
|
164 |
desc t1; |
|
165 |
create table t2 select 105213674794682365.00 y; |
|
166 |
desc t2; |
|
2141.4.2
by Andrew Hutchings
Implicit joins of the form "SELECT * FROM t1, t2" without WHERE or ON now error. |
167 |
create table t3 select x+y a from t1 CROSS JOIN t2; |
1
by brian
clean slate |
168 |
show warnings; |
169 |
desc t3; |
|
170 |
drop table t1,t2,t3; |
|
171 |
||
172 |
#
|
|
173 |
# Bug #22129: A small double precision number becomes zero |
|
174 |
#
|
|
175 |
# check if underflows are detected correctly |
|
176 |
select 1e-308, 1.00000001e-300, 100000000e-300; |
|
177 |
||
178 |
# check if overflows are detected correctly |
|
179 |
select 10e307; |
|
180 |
||
181 |
#
|
|
182 |
# Bug #19690: ORDER BY eliminates rows from the result |
|
183 |
#
|
|
184 |
create table t1(a int, b double(8, 2)); |
|
185 |
insert into t1 values |
|
186 |
(1, 28.50), (1, 121.85), (1, 157.23), (1, 1351.00), (1, -1965.35), (1, 81.75), |
|
187 |
(1, 217.08), (1, 7.94), (4, 96.07), (4, 6404.65), (4, -6500.72), (2, 100.00), |
|
188 |
(5, 5.00), (5, -2104.80), (5, 2033.80), (5, 0.07), (5, 65.93), |
|
189 |
(3, -4986.24), (3, 5.00), (3, 4857.34), (3, 123.74), (3, 0.16), |
|
190 |
(6, -1695.31), (6, 1003.77), (6, 499.72), (6, 191.82); |
|
191 |
explain select sum(b) s from t1 group by a; |
|
192 |
select sum(b) s from t1 group by a; |
|
193 |
select sum(b) s from t1 group by a having s <> 0; |
|
194 |
select sum(b) s from t1 group by a having s <> 0 order by s; |
|
195 |
select sum(b) s from t1 group by a having s <=> 0; |
|
196 |
select sum(b) s from t1 group by a having s <=> 0 order by s; |
|
197 |
alter table t1 add key (a, b); |
|
198 |
explain select sum(b) s from t1 group by a; |
|
199 |
select sum(b) s from t1 group by a; |
|
200 |
select sum(b) s from t1 group by a having s <> 0; |
|
201 |
select sum(b) s from t1 group by a having s <> 0 order by s; |
|
202 |
select sum(b) s from t1 group by a having s <=> 0; |
|
203 |
select sum(b) s from t1 group by a having s <=> 0 order by s; |
|
204 |
drop table t1; |
|
205 |
||
206 |
--echo End of 4.1 tests
|
|
207 |
||
208 |
#
|
|
209 |
# bug #12694 (float(m,d) specifications) |
|
210 |
#
|
|
211 |
||
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
212 |
--error ER_M_BIGGER_THAN_D
|
1
by brian
clean slate |
213 |
create table t1 (s1 float(0,2)); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
214 |
--error ER_M_BIGGER_THAN_D
|
1
by brian
clean slate |
215 |
create table t1 (s1 float(1,2)); |
216 |
||
217 |
# Bug #28121 "INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits" |
|
218 |
#
|
|
219 |
||
220 |
create table t1 (f1 double(200, 0)); |
|
221 |
insert into t1 values (1e199), (-1e199); |
|
222 |
insert into t1 values (1e200), (-1e200); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
223 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
224 |
insert into t1 values (2e200), (-2e200); |
225 |
select f1 + 0e0 from t1; |
|
226 |
drop table t1; |
|
227 |
||
228 |
create table t1 (f1 float(30, 0)); |
|
229 |
insert into t1 values (1e29), (-1e29); |
|
230 |
insert into t1 values (1e30), (-1e30); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
231 |
--error ER_WARN_DATA_OUT_OF_RANGE
|
1
by brian
clean slate |
232 |
insert into t1 values (2e30), (-2e30); |
233 |
select f1 + 0e0 from t1; |
|
234 |
drop table t1; |
|
235 |
||
236 |
#
|
|
237 |
# Bug #12860 "Difference in zero padding of exponent between Unix and Windows" |
|
238 |
#
|
|
239 |
||
240 |
create table t1 (c char(6)); |
|
241 |
insert into t1 values (2e6),(2e-5); |
|
242 |
select * from t1; |
|
243 |
drop table t1; |
|
244 |
||
245 |
#
|
|
246 |
# Bug #21497 "DOUBLE truncated to unusable value" |
|
247 |
#
|
|
248 |
||
249 |
CREATE TABLE d1 (d DOUBLE); |
|
250 |
INSERT INTO d1 VALUES (1.7976931348623157E+308); |
|
251 |
SELECT * FROM d1; |
|
252 |
--error ER_ILLEGAL_VALUE_FOR_TYPE
|
|
253 |
INSERT INTO d1 VALUES (1.79769313486232e+308); |
|
254 |
SELECT * FROM d1; |
|
255 |
DROP TABLE d1; |
|
256 |
||
257 |
#
|
|
258 |
# Bug #26788 "mysqld (debug) aborts when inserting specific numbers into char |
|
259 |
# fields"
|
|
260 |
#
|
|
261 |
||
262 |
create table t1 (a char(20)); |
|
263 |
insert into t1 values (1.225e-05); |
|
264 |
select a+0 from t1; |
|
265 |
drop table t1; |
|
266 |
||
267 |
--echo End of 5.0 tests
|