MySQL Bugs: #13237: sum() and case return -0.00 as a number result

Bug #13237	sum() and case return -0.00 as a number result
Submitted:	15 Sep 2005 21:56	Modified:	6 Nov 2005 8:51
Reporter:	Sergio Gómez	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0	OS:	Linux (linux,Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
when I have a Sum() function that contains a case statement inside, and I have a * - 1 in a when section it dosent return 0.00 everytime, sometimes I'got -0.00 as the result of the Sum().
A cant filter values = 0 with this situation, because the -0.00 are apparently negative values.

How to repeat:
SELECT sum(case tc.SumaResta
                  when 'S' then cs.Importe
                  when 'R' then cs.Importe * -1 end) as Saldo
FROM ...
WHERE ...
GROUP BY Cliente

/* The type of the column Importe is decimal(10,2)  and type of SumaResta is char(1) */

I'got:

Saldo
-----------
10.20
0.00
5.78
-0.00           <-- ¿?
9.15

Thank you for your bug report. 

I verified it as a bug with the test case from bug #9061.

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)

Exits also in for 4.1.14

One more comment. This behavior does not occur with MySQL Server 5.0 (tested with 5.0.12).

You can see the real reason of the negative value if you change the column type and reexecute the query:

alter table test modify column Value decimal(20,16);

mysql> SELECT SUM(Value) FROM Test;
+---------------------+
| SUM(Value)          |
+---------------------+
| -0.0000000000001847 |
+---------------------+

see bug #9037: "Negative Zero".