List:General Discussion« Previous MessageNext Message »
From:Barry Date:April 18 1999 5:09pm
Subject:Re: Serious sum() bug in MySQL 3.22.21
View as plain text  
Hello,

This is very likely a bug (rounding error) with the floating point library
code used to compile + link your executable of MySQL. The conversion back
and forth from binary to text floating point sometimes introduces this error.

I had this same problem with another Unix-type box and a different
application may years ago. My solution was to use the IEEE compatible
"libf" rather than a faster non-standard "libf" during linking of my
application.

I hope this helps some.
Barry.


At 01:08 AM 4/18/99 -0400, you wrote:
>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