Bug #121046 HAVING applied after SELECT DISTINCT deduplication → order-dependent wrong (empty) result

• Submitted: 1 Aug 0:46
• Modified: 4 Aug 7:28
• Reporter: Junwen An
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.46, 8.4.10, 9.7.1
• OS: Any
• CPU Architecture: Any

[1 Aug 0:46] Junwen An

Description:
Please see the below repro, which might be an unexpected result.

I could reproduce it on 8.4.10 and 9.7.2 @008e09c2

How to repeat:
-- (a) NULL-id row FIRST -> WRONG (0 rows):
CREATE TABLE t (id BIGINT, name VARCHAR(255));
INSERT INTO t VALUES (NULL, 'b'), (1, 'a');
SELECT DISTINCT CAST(MAX(1) AS SIGNED) AS e0
FROM t GROUP BY name HAVING MAX(id) <= 100;
-- Expected 1 row: (1)   [group 'a': MAX(id)=1<=100 kept, e0=1; group 'b': MAX(id)=NULL -> HAVING NULL -> excluded]
-- MySQL actual: 0 rows.

-- (b) CONTROL — same rows, NULL-id row LAST -> CORRECT (1 row):
CREATE TABLE t (id BIGINT, name VARCHAR(255));
INSERT INTO t VALUES (1, 'a'), (NULL, 'b');
SELECT DISTINCT CAST(MAX(1) AS SIGNED) AS e0
FROM t GROUP BY name HAVING MAX(id) <= 100;
-- Expected 1 row: (1).  MySQL actual: (1).  <- correct for this order

[1 Aug 0:47] Junwen An

Changed category

[4 Aug 7:28] Chaithra Marsur Gopala Reddy

Hi Junwen An,

Thank you for the test case. Verified as described.