Description:
When a query with a scalar subquery is rewritten into a derived table (which gets materialized), the optimizer may automatically create an index (<auto_key0>) on the materialized table to speed up an outer IN condition.
If the left side of the comparison is a floating‑point constant (e.g., 0.026605629777798234) and the right side is a BIGINT column returned by the subquery, the optimizer, during index lookup, truncates the floating‑point value to a BIGINT (CAST(0.0266... AS SIGNED) = 0). This causes an incorrect match against rows where the actual value is 0.
In the original single‑table query, the comparison correctly yields no matches (empty result), revealing an inconsistency.
Query Actual Status
Original single‑table query Empty set (0 rows) ✓
Derived table query 1 row returned (ref0 = 0.4034060568939616) ✗
EXPLAIN Analysis
Key part of EXPLAIN for the derived table query (formatted):
Sort: s.id
Index lookup on s using <auto_key0> (c1 = coalesce(NULL,0.026605629777798234))
Materialize
Covering index scan on l using PRIMARY
Select #3 (subquery in projection; dependent)
Single-row index lookup on r using PRIMARY
Observations:
Derived table s is materialized and an automatic index <auto_key0> is created on column c1;
The outer WHERE condition is applied via index lookup with the value COALESCE(NULL, 0.026605629777798234) (the floating‑point constant);
During the index lookup, the constant is converted to the index column type (BIGINT), resulting in 0, which matches the row with c1 = 0.
Root Cause Analysis
Constant folding: COALESCE(NULL, 0.026605629777798234) is optimized to the DOUBLE constant 0.026605629777798234.
Auto‑index creation: The materialized derived table gets an index on c1 with type BIGINT (matching the subquery's return type).
Implicit truncation: During index lookup (c1 = constant), the optimizer converts the DOUBLE constant to BIGINT using truncation toward zero (CAST(0.0266 AS SIGNED) = 0).
False match: The truncated value 0 matches an existing row (c1 = 0), so the index lookup returns that row, incorrectly producing output.
Why the original query works correctly:
In the single‑table query, the optimizer does not use an index lookup (or uses a different comparison path) and follows standard SQL comparison rules (floating‑point vs. integer, no truncation). 0.0266... is not equal to 0, so no rows match.
Conclusion: When materializing a derived table and creating an auto‑index, the optimizer mishandles data‑type conversion for comparisons, truncating floating‑point values instead of preserving exact floating‑point semantics.
How to repeat:
DROP DATABASE IF EXISTS repro_m616_6_min;
CREATE DATABASE repro_m616_6_min;
USE repro_m616_6_min;
CREATE TABLE src(id BIGINT PRIMARY KEY AUTO_INCREMENT, c1 BIGINT NULL) ENGINE=InnoDB;
INSERT INTO src(c1) VALUES(0),(1);
CREATE TABLE l(id BIGINT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE r(id BIGINT PRIMARY KEY, c1 BIGINT NULL) ENGINE=InnoDB;
INSERT INTO l SELECT id FROM src;
INSERT INTO r SELECT id,c1 FROM src;
-- Original query (single table): returns empty (correct)
SELECT 0.4034060568939616 AS ref0
FROM src
WHERE COALESCE(NULL,0.026605629777798234) IN (c1)
ORDER BY id;
-- Rewritten with derived table (materialized): incorrectly returns one row
SELECT 0.4034060568939616 AS ref0
FROM (
SELECT l.id, (SELECT r.c1 FROM r WHERE r.id=l.id) AS c1
FROM l
) s
WHERE COALESCE(NULL,0.026605629777798234) IN (c1)
ORDER BY id;
Description: When a query with a scalar subquery is rewritten into a derived table (which gets materialized), the optimizer may automatically create an index (<auto_key0>) on the materialized table to speed up an outer IN condition. If the left side of the comparison is a floating‑point constant (e.g., 0.026605629777798234) and the right side is a BIGINT column returned by the subquery, the optimizer, during index lookup, truncates the floating‑point value to a BIGINT (CAST(0.0266... AS SIGNED) = 0). This causes an incorrect match against rows where the actual value is 0. In the original single‑table query, the comparison correctly yields no matches (empty result), revealing an inconsistency. Query Actual Status Original single‑table query Empty set (0 rows) ✓ Derived table query 1 row returned (ref0 = 0.4034060568939616) ✗ EXPLAIN Analysis Key part of EXPLAIN for the derived table query (formatted): Sort: s.id Index lookup on s using <auto_key0> (c1 = coalesce(NULL,0.026605629777798234)) Materialize Covering index scan on l using PRIMARY Select #3 (subquery in projection; dependent) Single-row index lookup on r using PRIMARY Observations: Derived table s is materialized and an automatic index <auto_key0> is created on column c1; The outer WHERE condition is applied via index lookup with the value COALESCE(NULL, 0.026605629777798234) (the floating‑point constant); During the index lookup, the constant is converted to the index column type (BIGINT), resulting in 0, which matches the row with c1 = 0. Root Cause Analysis Constant folding: COALESCE(NULL, 0.026605629777798234) is optimized to the DOUBLE constant 0.026605629777798234. Auto‑index creation: The materialized derived table gets an index on c1 with type BIGINT (matching the subquery's return type). Implicit truncation: During index lookup (c1 = constant), the optimizer converts the DOUBLE constant to BIGINT using truncation toward zero (CAST(0.0266 AS SIGNED) = 0). False match: The truncated value 0 matches an existing row (c1 = 0), so the index lookup returns that row, incorrectly producing output. Why the original query works correctly: In the single‑table query, the optimizer does not use an index lookup (or uses a different comparison path) and follows standard SQL comparison rules (floating‑point vs. integer, no truncation). 0.0266... is not equal to 0, so no rows match. Conclusion: When materializing a derived table and creating an auto‑index, the optimizer mishandles data‑type conversion for comparisons, truncating floating‑point values instead of preserving exact floating‑point semantics. How to repeat: DROP DATABASE IF EXISTS repro_m616_6_min; CREATE DATABASE repro_m616_6_min; USE repro_m616_6_min; CREATE TABLE src(id BIGINT PRIMARY KEY AUTO_INCREMENT, c1 BIGINT NULL) ENGINE=InnoDB; INSERT INTO src(c1) VALUES(0),(1); CREATE TABLE l(id BIGINT PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE r(id BIGINT PRIMARY KEY, c1 BIGINT NULL) ENGINE=InnoDB; INSERT INTO l SELECT id FROM src; INSERT INTO r SELECT id,c1 FROM src; -- Original query (single table): returns empty (correct) SELECT 0.4034060568939616 AS ref0 FROM src WHERE COALESCE(NULL,0.026605629777798234) IN (c1) ORDER BY id; -- Rewritten with derived table (materialized): incorrectly returns one row SELECT 0.4034060568939616 AS ref0 FROM ( SELECT l.id, (SELECT r.c1 FROM r WHERE r.id=l.id) AS c1 FROM l ) s WHERE COALESCE(NULL,0.026605629777798234) IN (c1) ORDER BY id;