Bug #1961 | SUM function doesn't return reasonable value for float field type | ||
---|---|---|---|
Submitted: | 26 Nov 2003 8:04 | Modified: | 5 Feb 2004 13:44 |
Reporter: | Seokhee Kim | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 4.0.16 | OS: | Linux (Linux 2.4, linux 2.6) |
Assigned to: | Sergei Golubchik | CPU Architecture: | Any |
[26 Nov 2003 8:04]
Seokhee Kim
[26 Nov 2003 8:51]
Dean Ellis
FLOAT and DOUBLE use floating point math, which deals with approximate values for numbers and can thus result in confusion like this. Using DECIMAL or NUMERIC is better if you must avoid this behavior. Thank you.
[30 Jan 2004 6:07]
darren hartford
Switching from float/double to a much larger in size decimal/numeric doesn't seem like a good solution. I'm pretty sure the majority of people expect float and doubles to SUM correctly. My version is MySQL 3.23.56/Linux 2.4
[5 Feb 2004 13:19]
Sergei Golubchik
it cannot sum correctly. or, rather, it does sum correctly. You cannot store 0.1 in a column of type float. You can only store 0.00999999977648258. Try this: #include <stdio.h> main() { float a=0.01; double b=0.0099999997764826; printf("%.14g %g\n", a, b); } This program will print 0.0099999997764826 0.01 That is, 0.01 in float is stored as 0.009999999... When a width is specified printf prints the number as is, otherwise is guesses and rounds. That's why the second number is printed as 0.01 which is just as bad. If you want you results to be rounded like printf does - use FORMAT, e.g.: SELECT FORMAT(SUM(cost), 4) FROM ...
[5 Feb 2004 13:44]
Sergei Golubchik
alternatively you can define a column as FLOAT(M,N) - with a specified precision: create table t1 (a float, b float(5,2)); insert into t1 values (0.1,0.1); select a,sum(a),b,sum(b) from t1; +------+------------------+------+--------+ | a | sum(a) | b | sum(b) | +------+------------------+------+--------+ | 0.1 | 0.10000000149012 | 0.10 | 0.10 | +------+------------------+------+--------+