Bug #14274 having clause with aggregate as boolean value
Submitted: 24 Oct 2005 21:30 Modified: 13 Jan 2006 0:02
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.15 / 4.1.14 OS:Windows (Windows / freebsd / *)
Assigned to: Igor Babaev CPU Architecture:Any

[24 Oct 2005 21:30] Martin Friebe
Description:
Using the result of an aggregate function directly as the boolean input to "having" (instead of comparing it, returns always false.

select x from tbl group by x having sum(x);

returns an empty result, even if sum(x) is non zero and non NULL.
Comparing the result "sum(x) > 0" returns the expected result.

This is also true for min/max/avg

How to repeat:
create table tbl1 (a int);
insert into tbl1 values (1),(2),(3),(3);

select sum(a) from tbl1 group by a having sum(a);
Empty set (0.00 sec)

select sum(a) from tbl1 group by a having sum(a) > 0;
+--------+
| sum(a) |
+--------+
|      1 |
|      2 |
|      6 |
+--------+

Suggested fix:
if sum(a) is non-NULL / non-zero, it should be treated as true value in a having clause
[8 Jan 2006 7:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/749
[12 Jan 2006 6:54] Igor Babaev
hangeSet
  1.2472 06/01/07 23:00:06 igor@rurik.mysql.com +3 -0
  Fixed bug #14274: a query with a having clause containing only set function returned a wrong result set.

  sql/sql_select.cc
    1.445 06/01/07 22:59:56 igor@rurik.mysql.com +1 -1
    Fixed bug #14274: a query with a having clause containing only set function returned a wrong result set.
    It happened because processing of the set functions in having started with a call of the split_sum_func
    method, instead of the split_sum_func2 method. 

  mysql-test/t/having.test
    1.13 06/01/07 22:59:56 igor@rurik.mysql.com +12 -0
    Added a test case for bug #14274.

  mysql-test/r/having.result
    1.14 06/01/07 22:59:56 igor@rurik.mysql.com +13 -0
    Added a test case for bug #14274.

Fix will appear in 4.1.16, 5.0.20
[13 Jan 2006 0:02] Mike Hillyer
Entry added to 4.1.16/5.0.20 changelogs:

  <listitem>
    <para>
      Using an aggregate function as the argument for a HAVING
      clause would result in the aggregate function always returning
      <literal>FALSE</literal>. (Bug #14274)
    </para>
  </listitem>
[13 Jan 2006 17:51] Paul DuBois
The changelog versions are actually 4.1.17 and 5.0.19.