Bug #25938 minus zero (-0.00) can't be...

• Submitted: 30 Jan 2007 11:27
• Modified: 30 Jan 2007 22:57
• Reporter: Ali
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.19
• OS: Windows (WINDOWS XP)
• CPU Architecture: Any

[30 Jan 2007 11:27] Ali

Description:
I have a table which contain Three Fields; 

Table and records like this:
Field_A , Field_B, Field_C
True , (10.2) , 1
True, (5,4) , 1
False , (5,4) , 1
False , (10,2) , 1

and I want to sum Field_B according to Field_A for Field_C and run sqlscript like below on MySQL Query Browser:

SELECT SUM(IF(Field_A=True,-Field_B, Field_B)) AS Total FROM TestTable GROUP BY Field_C HAVING Total<>0

This is return -0.00, but there must be no return. I think Sql engine compare -0.00 and 0.00 and it find 0.00 is bigger than -0.00. Also There can't be -0.00

How to repeat:
I want to sum Field_B according to Field_A for Field_C and run sqlscript like below on MySQL Query Browser:

SELECT SUM(IF(Field_A=True,-Field_B, Field_B)) AS Total FROM TestTable GROUP BY Field_C HAVING Total<>0

Suggested fix:
This is return -0.00, but there must be no return. I think Sql engine compare -0.00 and 0.00 and it find 0.00 is bigger than -0.00. Also There can't be -0.00

[30 Jan 2007 11:32] Valeriy Kravchuk

Thank you for a problem report. Please, specify the exact MySQL server version used, 5.0.x. Send SHOW CREATE TABLE results for that TestTable also.

[30 Jan 2007 11:57] Ali

CREATE TABLE `testschema`.`TestTable` (
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `Field_A` BOOLEAN NOT NULL DEFAULT 0,
  `Field_B` DOUBLE(15,2) UNSIGNED NOT NULL DEFAULT 0,
  `Field_C` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;

[30 Jan 2007 22:57] Hartmut Holzgraefe

Not a bug then, just the typical binary to decimal rounding issues.
If you want exact results use the DECIMAL type instead of FLOAT and DOUBLE. Slower and taking more storage space, but definetly more precise.

[2 Mar 2007 16:28] Relu Botinant

select 1*-1*0.0;
+----------+
| 1*-1*0.0 |
+----------+
|     -0.0 |
+----------+
1 row in set (0.00 sec)

[2 Mar 2007 16:32] Relu Botinant

Workaround: Add a +0 at the end.

mysql> select 1*-1*0.0000*1 + 0;
+-----------------+
| 1*-1*0.0000*1+0 |
+-----------------+
|          0.0000 |
+-----------------+
1 row in set (0.00 sec)