MySQL Bugs: #38637: COUNT DISTINCT prevents NULL testing in HAVING clause

Bug #38637	COUNT DISTINCT prevents NULL testing in HAVING clause
Submitted:	7 Aug 2008 18:35	Modified:	11 Feb 2009 3:50
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.51, 5.1.28, 5.0.66a	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
COUNT(DISTINCT) appears to prevent testing for NULL in the HAVING clause.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1), (2);
SELECT c.a AS bug, COUNT(DISTINCT a.a) AS rows FROM t1 a CROSS JOIN t1 b LEFT JOIN t1 c ON a.a=c.a and b.a=1 GROUP BY bug HAVING bug IS NOT NULL;
SELECT c.a AS bug, COUNT(DISTINCT a.a) AS rows FROM t1 a CROSS JOIN t1 b LEFT JOIN t1 c ON a.a=c.a and b.a=1 GROUP BY bug HAVING bug IS NULL;

Produces:
+------+------+
| bug  | rows |
+------+------+
| NULL |    2 | 
|    1 |    1 | 
|    2 |    1 | 
+------+------+

and

Empty set (0.00 sec)

Should produce:
+------+------+
| bug  | rows |
+------+------+
|    1 |    1 | 
|    2 |    1 | 
+------+------+

and

+------+------+
| bug  | rows |
+------+------+
| NULL |    2 | 
+------+------+

Removing the DISTINCT produces expected results.

Suggested fix:
n/a

For this particular query form there is a partial workaround, which is to move the NULL check to the WHERE clause (where it belongs), but there may be other cases where this does not work.

The problem lies in the way IS NULL is implemented.
Item_func_ifnull::val_int() calls Item::is_null() ot its argument and relies on getting the correct value.
Other comparator functions (e.g. equality) call one of the Item::val_xxx functions (e.g. Item::val_int()).

However in the context of the HAVING clause (where all the column references are actually Item_ref instances) calling Item::is_null() is not enough.
Item_ref::is_null() will return the nullability of the current row, not the previous row. Let's compare this to e.g. Item_ref::val_int(). Item_ref::val_int() will call (*ref)->val_int_result() and thus get the integer value of the previous row in the GROUP BY calculation function end_send_group() (that is called for the first row of the new group).
There are two fixes to this : 
 1. make Item_func_ifnull() call e.g. val_int() first and then take the nullability. 
 2. Implement Item::is_null_result() (similarly to Item::val_int_result) and call this from Item_ref::is_null

Here's a simplified test case :

CREATE TABLE t1 ( a INT, b INT);
INSERT INTO t1 VALUES (1, 1), (3, NULL);
SELECT b, COUNT(DISTINCT a) FROM t1 GROUP BY b HAVING b is NULL;

This returns no rows, but should return 1 row.

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/56454

2707 Georgi Kodinov	2008-10-17
      Bug #38637: COUNT DISTINCT prevents NULL testing in HAVING clause
      
      IS NULL was not checking the correct row in a HAVING context.
      At the first row of a new group (where the HAVING clause is evaluated)
      the column and SELECT list references in the HAVING clause should 
      refer to the last row of the previous group and not to the current one. 
      This was not done for IS NULL, because it was using Item::is_null() doesn't
      have a  Item_is_null_result() counterpart to access the data from the 
      last row of the previous group. Note that all the Item::val_xxx() functions 
      (e.g. Item::val_int()) have their _result counterparts (e.g. Item::val_int_result()).
      
      Fixed by implementing a is_null_result() (similarly to int_result()) and
      calling this instead of is_null() column and SELECT list references inside
      the HAVING clause.

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:timothy.smith@sun.com-20090116170937-hvddomjy5hop3zg9) (merge vers: 6.0.10-alpha) (pib:6)

Noted in 6.0.10 changelog.

Use of COUNT(DISTINCT) prevented NULL testing in the HAVING clause.

Setting report to NDI pending push into 5.0.x/5.1.x.

Pushed into 5.0.78 (revid:joro@sun.com-20090203090422-v91rh3gmx2ulhbu9) (version source revid:chad@mysql.com-20090126173455-ia9503yxdqi30guh) (merge vers: 5.0.77) (pib:6)

Pushed into 5.1.32 (revid:joro@sun.com-20090203090549-gos3v4320vimrzg6) (version source revid:horst@mysql.com-20090123182108-2efo5zwexcrf0h3o) (merge vers: 5.1.32) (pib:6)

Noted in 5.0.78, 5.1.32 changelogs.

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090202111723-1zzwax187rtls913) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)