Bug #121482 Wrong result: with SQL_BUFFER_RESULT, HAVING COUNT(*) on a query without GROUP BY is evaluated as if COUNT(*) were 0

• Submitted: 11 Oct 15:47
• Modified: 13 Oct 5:25
• Reporter: Ann Calla
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.46, 8.4.11, 9.7.2, 26.07
• OS: Ubuntu (24.04)
• CPU Architecture: Any (x86_64)

[11 Oct 15:47] Ann Calla

Description:
For an aggregate query without GROUP BY, adding SQL_BUFFER_RESULT (or SET sql_buffer_result=ON) changes the result of a HAVING clause that references COUNT(*). The table has one row, so COUNT(*) is 1. HAVING COUNT(*) = 0 returns the row and HAVING COUNT(*) = 1 returns nothing, as if COUNT(*) were 0 inside HAVING. Without SQL_BUFFER_RESULT both are correct.

Possibly related to Bug #121403, which reports another wrong result with sql_buffer_result=ON.

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

SELECT COUNT(*) FROM t HAVING COUNT(*) = 0;
-- empty (correct)

SELECT SQL_BUFFER_RESULT COUNT(*) FROM t HAVING COUNT(*) = 0;
-- expected: empty
-- actual:   1

SELECT SQL_BUFFER_RESULT COUNT(*) FROM t HAVING COUNT(*) = 1;
-- expected: 1
-- actual:   empty

[12 Oct 10:03] Ann Calla

I found that this issue has already been reported as Bug #121336.

This report (Bug #121482) is a duplicate of Bug #121336.

Could you please close this report as a duplicate?

Sorry for the duplicate submission, and thank you for your understanding.

[13 Oct 5:25] Chaithra Marsur Gopala Reddy

Hi Ann Calla,

Thank you for the test case. Verified as described.