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 |
+------+
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 | +------+