List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 18 1999 12:54pm
Subject:Serious sum() bug in MySQL 3.22.21
View as plain text  
>>>>> "tcobb" == tcobb  <tcobb@stripped> writes:

tcobb> BUG REPORT:  MySQL 3.22.21
tcobb> ---------------------------------------------

tcobb> In some situations sum() generates a value of
tcobb> -0.00 which fails to match a HAVING condition if
tcobb> that HAVING condition is =0.

tcobb> This is in MySQL version 3.22.21 on FreeBSD 3.1.

tcobb> Here's how to reproduce the problem:

tcobb> CREATE TABLE test_decimal_bug (
tcobb>   id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
tcobb>   amount decimal(10,2) DEFAULT '0.00' NOT NULL,
tcobb>   PRIMARY KEY (id)
tcobb> );

tcobb> INSERT INTO test_decimal_bug VALUES (1,19.95);
tcobb> INSERT INTO test_decimal_bug VALUES (2,0.00);
tcobb> INSERT INTO test_decimal_bug VALUES (3,-39.95);
tcobb> INSERT INTO test_decimal_bug VALUES (4,20.00);

mysql> select sum(amount) from test_decimal_bug;
tcobb> +-------------+
tcobb> | sum(amount) |
tcobb> +-------------+
tcobb> |       -0.00 |
tcobb> +-------------+

mysql> select sum(amount) as total from test_decimal_bug having total=0;
tcobb> Empty set (0.01 sec)

mysql> select sum(amount) as total from test_decimal_bug having total<=0;
tcobb> +-------+
tcobb> | total |
tcobb> +-------+
tcobb> | -0.00 |
tcobb> +-------+
tcobb> 1 row in set (0.00 sec)

mysql> select sum(amount) as total from test_decimal_bug having total<0;
tcobb> +-------+
tcobb> | total |
tcobb> +-------+
tcobb> | -0.00 |
tcobb> +-------+
tcobb> 1 row in set (0.00 sec

tcobb> --------------------------

tcobb> This bug has just caused one of my clients some major hassles.

tcobb> -Troy Cobb
tcobb>  Circle Net, Inc.
tcobb>  http://www.circle.net

Hi!

This is probably a bug in the FreeBSD math library.

Note that its common practice in most languages to NEVER compare a
floating point value that may have fractions with '=', but instead
always use:

total between -0.5 and 0.5

Note that in MySQL 3.23 you will have not fixed point float fields, so
in this case the sum above will be a very small value <> 0

I think that if I change the '=' compare in MySQL to be 'smarter' it
will brake more applications than it will fix :(

Regards,
Monty
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