Description:
Summary
MySQL returns different results for two logically equivalent queries on a FLOAT UNIQUE column in the MEMORY (HEAP) engine. The original query is incorrectly optimized to Zero rows (no matching row in const table), while an equivalent derived-table form returns one row. This indicates a wrong-result bug caused by incorrect constant-table pruning or predicate simplification.
Actual Result
Query 1 returns:
Empty set
Query 2 returns one row:
0
Expected Result
Both queries should return the same result.
The predicate in Query 2 is exactly the same predicate as in Query 1, except that it is first projected as a boolean column ref1 and then filtered by WHERE ref1. This rewriting should preserve semantics. Since Query 2 returns one row, Query 1 should also return one row.
Execution Plans
Query 1:
EXPLAIN SELECT t0.c0 AS ref0 FROM t0 WHERE NOT (-("") NOT IN (t0.c0));
Plan:
-> Zero rows (no matching row in const table) (cost=0..0 rows=0)
Query 2:
EXPLAIN SELECT ref0
FROM (
SELECT t0.c0 AS ref0, (NOT (-("") NOT IN (t0.c0))) AS ref1
FROM t0
) AS s
WHERE ref1;
Plan:
-> Filter: (0 <> (<cache>(-('')) = t0.c0)) (cost=2.73 rows=2)
-> Table scan on t0 (cost=2.73 rows=2)
Additional Notes
This issue appears to require both of the following conditions:
1. ENGINE = HEAP / MEMORY
2. a UNIQUE index on the FLOAT column
If either condition is removed, both queries return 0 rows.
This suggests that the bug may be related to the interaction between:
constant folding or predicate simplification,
unique-key reasoning / const-table optimization,
floating-point comparison in the MEMORY engine.
How to repeat:
DROP DATABASE IF EXISTS test_db;
CREATE DATABASE test_db;
USE test_db;
-- Key factors: ENGINE = HEAP and UNIQUE index. If any condition is removed, both Query 1 and Query 2 return 0
CREATE TABLE t0(c0 FLOAT UNIQUE) ENGINE = HEAP;
INSERT INTO t0(c0) VALUES(0.6840049395257071);
REPLACE INTO t0(c0) VALUES(0);
-- Query 1: Returns Empty Set (Incorrect)
-- Explain shows: "Zero rows (no matching row in const table)"
SELECT t0.c0 AS ref0 FROM t0 WHERE NOT (-("") NOT IN (t0.c0));
-- Query 2: Returns 1 row (Correct)
SELECT ref0 FROM (SELECT t0.c0 AS ref0, (NOT (-("") NOT IN (t0.c0))) AS ref1 FROM t0) AS s WHERE ref1;
© 2026, Oracle Corporation and/or its affiliates
Description: Summary MySQL returns different results for two logically equivalent queries on a FLOAT UNIQUE column in the MEMORY (HEAP) engine. The original query is incorrectly optimized to Zero rows (no matching row in const table), while an equivalent derived-table form returns one row. This indicates a wrong-result bug caused by incorrect constant-table pruning or predicate simplification. Actual Result Query 1 returns: Empty set Query 2 returns one row: 0 Expected Result Both queries should return the same result. The predicate in Query 2 is exactly the same predicate as in Query 1, except that it is first projected as a boolean column ref1 and then filtered by WHERE ref1. This rewriting should preserve semantics. Since Query 2 returns one row, Query 1 should also return one row. Execution Plans Query 1: EXPLAIN SELECT t0.c0 AS ref0 FROM t0 WHERE NOT (-("") NOT IN (t0.c0)); Plan: -> Zero rows (no matching row in const table) (cost=0..0 rows=0) Query 2: EXPLAIN SELECT ref0 FROM ( SELECT t0.c0 AS ref0, (NOT (-("") NOT IN (t0.c0))) AS ref1 FROM t0 ) AS s WHERE ref1; Plan: -> Filter: (0 <> (<cache>(-('')) = t0.c0)) (cost=2.73 rows=2) -> Table scan on t0 (cost=2.73 rows=2) Additional Notes This issue appears to require both of the following conditions: 1. ENGINE = HEAP / MEMORY 2. a UNIQUE index on the FLOAT column If either condition is removed, both queries return 0 rows. This suggests that the bug may be related to the interaction between: constant folding or predicate simplification, unique-key reasoning / const-table optimization, floating-point comparison in the MEMORY engine. How to repeat: DROP DATABASE IF EXISTS test_db; CREATE DATABASE test_db; USE test_db; -- Key factors: ENGINE = HEAP and UNIQUE index. If any condition is removed, both Query 1 and Query 2 return 0 CREATE TABLE t0(c0 FLOAT UNIQUE) ENGINE = HEAP; INSERT INTO t0(c0) VALUES(0.6840049395257071); REPLACE INTO t0(c0) VALUES(0); -- Query 1: Returns Empty Set (Incorrect) -- Explain shows: "Zero rows (no matching row in const table)" SELECT t0.c0 AS ref0 FROM t0 WHERE NOT (-("") NOT IN (t0.c0)); -- Query 2: Returns 1 row (Correct) SELECT ref0 FROM (SELECT t0.c0 AS ref0, (NOT (-("") NOT IN (t0.c0))) AS ref1 FROM t0) AS s WHERE ref1;