Bug #119520 Inconsistent NULL and Negative Zero Handling in EXISTS Subquery with HAVING Clause

• Submitted: 6 Dec 2025 17:55
• Modified: 13 Oct 9:31
• Reporter: jinhui lai
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.5.0
• OS: Ubuntu (22.04)
• CPU Architecture: Any

[6 Dec 2025 17:55] jinhui lai

Description:
Hi, MySQL developers.

Hi, MySQL developers. I find a bug where a logically contradictory query containing `WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0)` and `HAVING EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0)` incorrectly returns the row with value `-0.0` from `t0`, instead of the logically required empty result set, indicating a failure in the optimizer or executor to correctly unify the evaluation of the identical subquery condition across the WHERE and HAVING clauses.

How to repeat:
-- docker pull mysql:latest
SET SESSION internal_tmp_mem_storage_engine = MEMORY;
CREATE TABLE t0(c0 FLOAT);
CREATE TABLE t1(c0 FLOAT);
INSERT INTO t0(c0) VALUES(NULL),(NULL),(NULL),(NULL),('-0.0'),(0);
INSERT INTO t1(c0) VALUES(0),(0),(0),(0);
SELECT * FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0) HAVING EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);-- expect: empty set, actually: -0.0
+------+
| c0   |
+------+
|   -0 |
+------+

[8 Dec 2025 3:51] Chaithra Marsur Gopala Reddy

Hi jinhui lai,

Thank you for the test case. Verified as described.

[13 Oct 9:31] Knut Anders Hatlen

Looks like the same issue as bug#67978. Closing as duplicate.