MySQL Bugs: #9037: Negative Zero

Bug #9037	Negative Zero
Submitted:	8 Mar 2005 1:37	Modified:	8 Mar 2005 17:50
Reporter:	Frederick Aubert	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.2	OS:	MacOS (MacOS X)
Assigned to:		CPU Architecture:	Any

Description:
Suppose you are developing an application that rely essentially on the aggregation of dated transaction into monthly total amount of let's say many different accounts, I found out that some aggregation are added to "negative zero".

Please notice that it is more than a cosmetic bug since whenever you use a WHERE clause with myColumn=0, the server returns only row where myColumn is "positive zero" dropping rows where myColumn is equal to "negative zero". It even considers "negative zero" to be strictly less than zero.

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 following queries on the test table
Input: SELECT SUM(Value) FROM Test;
Output:-0.000000 instead of just 0.000000

Input: SELECT SUM(Value)=0, SUM(Value)<0 FROM Test;
Output: 0 (FALSE), 1 (TRUE)

Suggested fix:
Here is a workaround that may work in some situation:

Input: SELECT CAST(SUM(Value) AS BINARY)=0, CAST(SUM(Value) AS BINARY)<0 FROM Test;
Output: 1 (TRUE), 1 (FALSE)

It gives the expected output to the query, but does not correct the internal representation of the data in the database, and this is specially meaningful if you decide to INSERT INTO another table the output for later use.

Frederick Aubert

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

mysql> SELECT * FROM Test;
+--------------+
| Value        |
+--------------+
|  1693.620000 |
| -1608.939000 |
|   -84.681000 |
+--------------+
3 rows in set (0.00 sec)

mysql> SELECT ROUND(SUM(Value), 20) FROM Test;
+-------------------------+
| ROUND(SUM(Value), 20)   |
+-------------------------+
| -0.00000000000018474111 |
+-------------------------+
1 row in set (0.00 sec)

This is a terrible bug.

It turns out that any sum totalling -0.00 is less than a constant of -0.00.

MySQL seriously needs to fix this.

Version 5.0.2 is very old, you should try with the newest version of 5.0.

According to documentation, precision math was introduced only in version 5.0.3:

    http://dev.mysql.com/doc/refman/5.0/en/precision-math.html