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