MySQL Bugs: #17742: Imprecise math: SUM, AVG seem to convert DECIMAL to FLOAT data type

Bug #17742	Imprecise math: SUM, AVG seem to convert DECIMAL to FLOAT data type
Submitted:	27 Feb 2006 14:57	Modified:	28 Feb 2006 14:14
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.14-log	OS:	Linux (Gentoo Linux)
Assigned to:		CPU Architecture:	Any

Description:
DECIMAL(8,2) seems to be converted to floating-point when using SUM in a grouped SELECT.  SELECT ... GROUP BY... HAVING SUM(COL) <> 0 will return rows with values that display as 0.00, but are actually floating point values very close to 0.  Sometimes adding an ORDER BY clause will cause all the spurious rows to go away.

How to repeat:
Run the following statements to create a table, populate it with values totalling zero, then select with GROUP BY:

create table isam(id int, amount decimal(8,2)) type=myisam;
insert into isam values(1, 28.50), (1, 121.85), (1, 157.23),
(1, 1351.00), (1, -1965.35), (1, 81.75), (1, 217.08), (1, 7.94);
select id, sum(amount) as amount from isam group by id having sum(amount) <> 0;

You should get the following:

+------+--------+
| id   | amount |
+------+--------+
|    1 |   0.00 |
+------+--------+

The following statement shows amount is really 3.1086244689504e-13, not 0.00:

select id, sum(amount) * 1e1 as amount from isam group by id having sum(amount) <> 0;

On my own actual data set, I have many rows and many groups, some of which do not sum up to zero.  They show either 0.00 or -0.00 (negative zero).  Adding ORDER BY AMOUNT to the query will cause the spurious rows to disappear.  I cannot repro that on this small data set.  On my actual data set, I see different behavior (but still wrong; different 0.00 rows are returned) between MyISAM and InnoDB, though on this sample data set they are the same.

Hi!

The results you're experiencing are expected in 4.1 distributions.

What's happening is (in 4.1) the SUM() uses floating point (non-precision math) so it's probably comparing 0.00 to 0.00000000000000000000000000000000000000000000001 or something similar.

You can read more about this here:

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

Thanks for the explanation.  I'd still count that as a bug but I won't argue with you :-)

The issue with ORDER BY making the resultset have different rows certainly seems like a bug to me, though.  ORDER BY should order the rows in the resultset, never change them.  Should I enter that bug separately once I can find a set of data that will reproduce it?