>>>>> "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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.