MySQL Bugs: #9061: DECIMAL Precision (was Negative Zero)

Bug #9061	DECIMAL Precision (was Negative Zero)
Submitted:	8 Mar 2005 22:51	Modified:	11 Mar 2005 23:34
Reporter:	Frederick Aubert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.2	OS:	MacOS (MacOS X)
Assigned to:		CPU Architecture:	Any

Description:
I have been unable to add a comment to bug report 9037. The interface kept telling me I should add a comment since it had the closed status, but whenever I added the comment it was said I couldn't edit a closed bug. It might be a glitch is the interface... But that not the point of this report, after investigation, I came to the conclusion the bug was more general and had to do with the handling of decimal computation which lead to a new bug report.

As I just said I did explore a bit because whenever you add three specific numbers on paper it exactly sum to zero and not to the very small non null number shown by the SELECT ROUND(SUM(Value), 20) FROM Test query.

According to the documentation, DECIMAL is stored as a string, but everything seems to show that in the computation the number is casted to a float (or a double) for use by the CPU math routines. That is probably not what a user shall expect from such type. One could believe to have exact precision in calculation for it stores the number with exact precision... On a side note PostGreSQL do not have this behavior.

Finally may I ask the developer team what is the purpose of DECIMAL if it is not exact precision? Why not rather using FLOAT and not relying on some cast in the middle of a calculation?

Sincerely, Frederick Aubert

How to repeat:
#create a test table
CREATE TABLE Test(Value DECIMAL(10,6))

#fill the test table with some data
INSERT INTO Test SET value=1693.620000;
INSERT INTO Test SET value=-1608.939000;
INSERT INTO Test SET value=-84.681000

#run the query on the test table
SELECT SUM(Value) FROM Test;
output: -0.000000 (negative zero)

Suggested fix:
Implementing routines to handle decimal computation imitating "paper and pencil" calculation instead of relying on math routines for float or double precision numbers.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Tested in 5.0.3 from our src repos:

mysql> SELECT SUM(Value) FROM Test;
+------------+
| SUM(Value) |
+------------+
| 0.000000   |
+------------+
1 row in set (0.00 sec)