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:
None 
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
Description:
mysql> select * from aaa;
+----+------------+------+
| id | month_year | cost |
+----+------------+------+
|  1 | 2002-05-01 | 0.01 |
|  2 | 2002-06-01 | 1.65 |
|  3 | 2002-07-01 | 0.01 |
|  4 | 2002-01-01 | 0.01 |
+----+------------+------+

mysql> select id, sum(cost) from aaa group by id;
+----+---------------------+
| id | sum(cost)           |
+----+---------------------+
|  1 | 0.00999999977648258 |
|  2 |    1.64999997615814 |
|  3 | 0.00999999977648258 |
|  4 | 0.00999999977648258 |
+----+---------------------+

The SUM function changes 0.01 to 0.00999999977648258.
This problem also comes when float type altered to double.

How to repeat:
CREATE TABLE aaa (
  id int(11) NOT NULL auto_increment,
  month_year date NOT NULL default '2000-00-00',
  cost float default NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

--
-- Dumping data for table 'aaa'
--

INSERT INTO aaa VALUES (1,'2002-05-01',0.01);
INSERT INTO aaa VALUES (2,'2002-06-01',1.65);
INSERT INTO aaa VALUES (3,'2002-07-01',0.01);
INSERT INTO aaa VALUES (4,'2002-01-01',0.01);

Suggested fix:
The sum function looks like change float to double type.
I know that's very reasonable but I think changing value 0.01 to 0.00999999977648258 might be fixed.
[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 |
+------+------------------+------+--------+