BUG REPORT: MySQL 3.22.21
---------------------------------------------
In some situations sum() generates a value of
-0.00 which fails to match a HAVING condition if
that HAVING condition is =0.
This is in MySQL version 3.22.21 on FreeBSD 3.1.
Here's how to reproduce the problem:
CREATE TABLE test_decimal_bug (
id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
amount decimal(10,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO test_decimal_bug VALUES (1,19.95);
INSERT INTO test_decimal_bug VALUES (2,0.00);
INSERT INTO test_decimal_bug VALUES (3,-39.95);
INSERT INTO test_decimal_bug VALUES (4,20.00);
mysql> select sum(amount) from test_decimal_bug;
+-------------+
| sum(amount) |
+-------------+
| -0.00 |
+-------------+
mysql> select sum(amount) as total from test_decimal_bug having total=0;
Empty set (0.01 sec)
mysql> select sum(amount) as total from test_decimal_bug having total<=0;
+-------+
| total |
+-------+
| -0.00 |
+-------+
1 row in set (0.00 sec)
mysql> select sum(amount) as total from test_decimal_bug having total<0;
+-------+
| total |
+-------+
| -0.00 |
+-------+
1 row in set (0.00 sec
--------------------------
This bug has just caused one of my clients some major hassles.
-Troy Cobb
Circle Net, Inc.
http://www.circle.net
