List:General Discussion« Previous MessageNext Message »
From:tcobb Date:April 18 1999 5:08am
Subject:Serious sum() bug in MySQL 3.22.21
View as plain text  
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
Thread
Serious sum() bug in MySQL 3.22.21tcobb18 Apr
  • Re: Serious sum() bug in MySQL 3.22.21Mike Machado18 Apr
  • Serious sum() bug in MySQL 3.22.21Michael Widenius18 Apr
    • Re: Serious sum() bug in MySQL 3.22.21Michael R. Gile18 Apr
      • Re: Serious sum() bug in MySQL 3.22.21Michael Widenius19 Apr
        • Re: Serious sum() bug in MySQL 3.22.21Michael R. Gile19 Apr
  • Re: Serious sum() bug in MySQL 3.22.21Barry18 Apr
  • Re: Seriously Disruptive DBA Jonathan G. Lampe24 Aug
    • Re: Seriously Disruptive DBAMark Schoonover24 Aug