MySQL Bugs: #14274: having clause with aggregate as boolean value

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

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

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

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

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>

The changelog versions are actually 4.1.17 and 5.0.19.