MySQL Bugs: #9000: HAVING not filtering correctly when used with SUM

Bug #9000	HAVING not filtering correctly when used with SUM
Submitted:	6 Mar 2005 22:25	Modified:	9 Mar 2005 1:12
Reporter:	Lachlan Mulcahy	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.0, 4.1	OS:	Any (Any)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
HAVING does not filter the resultset properly when used with the SUM aggregate function. See "How to repeat".

How to repeat:
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (a char(1), b decimal(11,2));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES ('A', '1546.92'),('A', '119.19'),('A', '-1546.92'),('A', '-119.19');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT a, sum(b) AS s FROM t1 GROUP BY a HAVING s <> 0;
+------+------+
| a    | s    |
+------+------+
| A    | 0.00 |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT a, round(sum(b)) AS s FROM t1 GROUP BY a HAVING s <> 0;
Empty set (0.00 sec)

Hi Lachlan,

I was able to reproduce these results in 4.1.11 from bk.  This behaviour didn't happen in 5.0.3

Precision math is not implemented in 4.0, 4.1.
So the comparison s <> 0 is evaluated in the float arithmetic rather than in
the arithmetic of decimals.
That' why we have:

mysql> SELECT a, sum(b) AS s FROM t2 GROUP BY a HAVING s > 0;
+------+------+
| a    | s    |
+------+------+
| A    | 0.00 |
+------+------+
1 row in set (0.00 sec)

and

mysql> SELECT a, sum(b) AS s FROM t2 GROUP BY a HAVING s - 0.0000000000001 > 0;
Empty set (0.01 sec)

and

mysql> SELECT a, sum(b) AS s FROM t2 GROUP BY a HAVING s - 0.00000000000001 > 0;
+------+------+
| a    | s    |
+------+------+
| A    | 0.00 |
+------+------+
1 row in set (0.00 sec)

Standard compliant decimals have been implemented in 5.0.3.
We do not plan to port them down to 4.1, 4.0.