MySQL Bugs: #38072: Wrong result: HAVING not observed in a query with aggregate

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

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.

Test case for bug38072

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

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

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)

The above example is repeatable on 5.1 also. Changed the synopsis to a more accurate.

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)

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

Which version should this be fixed in? 6.0 or 5.1?

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

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.

triage: updating to I4 and P3 and changing tag to SR51MRU as problem is visible in all releases

So which version do I push this into? Please clarify.

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

Pushed into mysql-5.0-bugteam, mysql-5.1-bugteam

Pushed into 6.0.7-alpha  (revid:sergefp@mysql.com-20080909193623-od8fv79a2cs7kfsw) (version source revid:sergefp@mysql.com-20080909193623-od8fv79a2cs7kfsw) (pib:5)

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.

Pushed into 6.0.8-alpha  (revid:sergefp@mysql.com-20081024021622-sugqqsappmbe39gj) (version source revid:davi.arnaut@sun.com-20081024153711-yhcswhfwz9z5b07g) (pib:5)

Pushed into 5.0.72  (revid:sergefp@mysql.com-20081024021622-sugqqsappmbe39gj) (version source revid:kgeorge@mysql.com-20081103095032-zv904ucm2e9lgyxf) (pib:5)

Pushed into 5.1.30  (revid:sergefp@mysql.com-20081024021622-sugqqsappmbe39gj) (version source revid:davi.arnaut@sun.com-20081024125059-98c0zv1xym4oruam) (pib:5)

Noted in 5.0.72, 5.1.31 changelogs.

A push on Nov 10 never made it into 5.0.72 - that must be 5.0.74.

Noted in 5.0.74 changelog, not 5.0.72.

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)

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)

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)