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
Triage: Triaged: D2 (Serious)

[7 Aug 2008 18:35] Dean Ellis
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
[7 Aug 2008 18:59] Dean Ellis
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.
[17 Oct 2008 10:19] Georgi Kodinov
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
[17 Oct 2008 10:21] Georgi Kodinov
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.
[17 Oct 2008 10:56] 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/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.
[20 Jan 2009 18:59] Bugs System
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)
[29 Jan 2009 21:17] Paul Dubois
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.
[3 Feb 2009 9:12] Bugs System
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)
[3 Feb 2009 9:39] Bugs System
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)
[11 Feb 2009 3:50] Paul Dubois
Noted in 5.0.78, 5.1.32 changelogs.
[17 Feb 2009 15:00] Bugs System
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)
[17 Feb 2009 16:47] Bugs System
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)
[17 Feb 2009 18:23] Bugs System
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)