Bug #100610 HAVING clause is not applied to all rows

• Submitted: 22 Aug 2020 22:01
• Modified: 24 Aug 2020 7:11
• Reporter: Jhon Saway
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.7.31
• OS: Ubuntu (18.04)
• CPU Architecture: x86

[22 Aug 2020 22:01] Jhon Saway

Description:
Problem:

Query like this:
 SELECT count(*) AS count
 FROM test
 GROUP BY description
 HAVING count >= 2
 ORDER BY count ASC;

returns also some rows where column `count` is equal to 1 (but should only returns those where `count` is >= 2).

Wrong results are returned when 'innodb_buffer_pool_size' is set to default value 128M. Increasing value to 256M solves issue for provided data.

Expected results:
Only rows where `count` is >= 2 or some kind of warning/error rather silently returning wrong data.

Thanks,
Bartek

How to repeat:
1. Create a database:
CREATE DATABASE bug CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. Download database dump: https://f001.backblazeb2.com/b2api/v1/b2_download_file_by_id?fileId=4_z3eec3c1800331e85734...

3. Unzip and import dump into created database.

4. Run the query
SELECT count(*) AS count
FROM test
GROUP BY description
HAVING count >= 2
ORDER BY count ASC;

5. See some rows where column `count` is equal to 1. In my case those rows don't start at the beginning but e.g on row 23.

[24 Aug 2020 7:11] MySQL Verification Team

Hello Jhon Saway,

Thank you for the report and test case.
Observed that 5.7.31 is affected.

regards,
Umesh