Bug #38072 Wrong result: HAVING not observed in a query with aggregate
Submitted: 12 Jul 2008 17:28 Modified: 10 Nov 2008 18:20
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:6.0, 5.1, 5.0 bzr OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any

[12 Jul 2008 17:28] Philip Stoev
Description:
In 6.0 with 'no_semijoin' optimizer switch, queries like:

SELECT COUNT( OUTR . `varchar_key` ) AS X
FROM C AS OUTR
WHERE ( OUTR . `pk` ) IN ( SELECT  INNR . `pk` AS Y FROM BB AS INNR )
AND OUTR . `pk` = 8
HAVING X = NULL;

Return a row with value "0", even though this row does not match the HAVING clause.

How to repeat:
A test case will be uploaded shortly.
[12 Jul 2008 17:33] Philip Stoev
Test case for bug38072

Attachment: bug38072.test (application/octet-stream, text), 2.53 KiB.

[13 Jul 2008 14:14] Sveta Smirnova
Thank you for the report.

Verified as described using version 6.0.6:

SET optimizer_switch='no_semijoin';
SELECT COUNT(varchar_key) AS X
FROM t2
WHERE pk IN (SELECT pk FROM t1 )
AND pk = 8
HAVING X = NULL;
X
0
[14 Jul 2008 9:31] Sergey Petrunya
Repeatable without subquery: try this:

mysql> SELECT COUNT(varchar_key) AS X FROM t2 WHERE pk = 8 having 'foo'='bar';
+---+
| X |
+---+
| 0 | 
+---+
1 row in set (0.05 sec)
[7 Sep 2008 20:02] Sergey Petrunya
The above example is repeatable on 5.1 also. Changed the synopsis to a more accurate.
[7 Sep 2008 20:04] Sergey Petrunya
EXPLAIN shows this (same on 5.1 and 6.0): 

mysql> explain SELECT COUNT(varchar_key) AS X FROM t2 WHERE pk = 8 having 'foo'='bar';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra             |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible HAVING | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
1 row in set (0.00 sec)
[7 Sep 2008 21:59] 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/53460

2689 Sergey Petrunia	2008-09-06
      BUG#38072: Wrong result: HAVING not observed in a query with aggregate
      - Make send_row_on_empty_set() return FALSE when simplify_cond() has found out 
        that HAVING is always FALSE
[7 Sep 2008 22:01] Sergey Petrunya
Which version should this be fixed in? 6.0 or 5.1?
[9 Sep 2008 19:36] 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/53644

2689 Sergey Petrunia	2008-09-09
      BUG#38072: Wrong result: HAVING not observed in a query with aggregate
      - Make send_row_on_empty_set() return FALSE when simplify_cond() has found out
        that HAVING is always FALSE
[11 Sep 2008 19:42] Sveta Smirnova
Problem with query ` SELECT COUNT(varchar_key) AS X FROM t2 WHERE pk = 8 having 'foo'='bar';` is repeatable with version 5.0 as well.
[21 Oct 2008 0:02] Omer Barnir
triage: updating to I4 and P3 and changing tag to SR51MRU as problem is visible in all releases
[21 Oct 2008 8:14] Sergey Petrunya
So which version do I push this into? Please clarify.
[24 Oct 2008 11:36] 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/56996

2709 Sergey Petrunia	2008-10-24
      BUG#38072: Wrong result: HAVING not observed in a query with aggregate
      - Make send_row_on_empty_set() return FALSE when simplify_cond() has found out
        that HAVING is always FALSE
      re-committing to put the fix into 5.0 and 5.1
[24 Oct 2008 18:24] Sergey Petrunya
Pushed into mysql-5.0-bugteam, mysql-5.1-bugteam
[1 Nov 2008 18:31] Bugs System
Pushed into 6.0.7-alpha  (revid:sergefp@mysql.com-20080909193623-od8fv79a2cs7kfsw) (version source revid:sergefp@mysql.com-20080909193623-od8fv79a2cs7kfsw) (pib:5)
[3 Nov 2008 16:53] Paul DuBois
Noted in 6.0.7 changelog.

Queries with a HAVING clause could return a spurious row.

Setting report to NDI pending push into 5.0.x, 5.1.x.
[10 Nov 2008 10:52] Bugs System
Pushed into 6.0.8-alpha  (revid:sergefp@mysql.com-20081024021622-sugqqsappmbe39gj) (version source revid:davi.arnaut@sun.com-20081024153711-yhcswhfwz9z5b07g) (pib:5)
[10 Nov 2008 10:59] Bugs System
Pushed into 5.0.72  (revid:sergefp@mysql.com-20081024021622-sugqqsappmbe39gj) (version source revid:kgeorge@mysql.com-20081103095032-zv904ucm2e9lgyxf) (pib:5)
[10 Nov 2008 11:36] Bugs System
Pushed into 5.1.30  (revid:sergefp@mysql.com-20081024021622-sugqqsappmbe39gj) (version source revid:davi.arnaut@sun.com-20081024125059-98c0zv1xym4oruam) (pib:5)
[10 Nov 2008 18:20] Paul DuBois
Noted in 5.0.72, 5.1.31 changelogs.
[18 Nov 2008 21:49] Joerg Bruehe
A push on Nov 10 never made it into 5.0.72 - that must be 5.0.74.
[19 Nov 2008 0:01] Paul DuBois
Noted in 5.0.74 changelog, not 5.0.72.
[19 Jan 2009 11:30] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:08] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:13] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)