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
>
