Bug #121336 SQL_BUFFER_RESULT causes aggregate row failing HAVING predicate to be returned

• Submitted: 21 Sep 10:42
• Modified: 23 Sep 6:51
• Reporter: Ann C
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.46, 8.4.11, 9.7.2
• OS: Ubuntu (24.04)
• CPU Architecture: x86
• Tags: aggregate, having, SQL_BUFFER_RESULT, wrong-result

[21 Sep 10:42] Ann C

Description:
MySQL 26.7.1 returns an incorrect result for an aggregate query using SQL_BUFFER_RESULT with a HAVING predicate.

The table contains exactly one row, so COUNT(*) = 1. Therefore HAVING COUNT(*) <= 0 evaluates to FALSE and the query should return an empty result set.

However, when SQL_BUFFER_RESULT is used, MySQL incorrectly returns:

+---+
| c |
+---+
| 1 |
+---+

Without SQL_BUFFER_RESULT, the same query correctly returns an empty result set.

This appears to be a wrong-result bug involving SQL_BUFFER_RESULT and HAVING evaluation.

How to repeat:
CREATE TABLE t (a INT);
INSERT INTO t VALUES (1);

SELECT SQL_BUFFER_RESULT COUNT(*) AS c
FROM t
HAVING COUNT(*) <= 0;

Actual result:

+---+
| c |
+---+
| 1 |
+---+

Expected result:

Empty set

Control query:

SELECT COUNT(*) AS c
FROM t
HAVING COUNT(*) <= 0;

The control query correctly returns an empty result set.

EXPLAIN FORMAT=TREE:

EXPLAIN FORMAT=TREE
SELECT SQL_BUFFER_RESULT COUNT(*) AS c
FROM t
HAVING COUNT(*) <= 0;

Output:

-> Filter: (count(0) <= 0)
    -> Count rows in t

[23 Sep 6:51] Chaithra Marsur Gopala Reddy

Hi Ann C,

Thank you for the test case. Verifies as described.