Description:
When a table contains a single row and is accessed via a primary key or unique index, the optimizer treats it as a const table and pre‑evaluates the WHERE condition during the optimization phase.
If the condition compares a FLOAT column with a DOUBLE constant that far exceeds the maximum representable value of FLOAT (e.g., 1.7976931348623157E308), the optimizer may truncate the constant to 0 or convert it to NaN during pre‑evaluation, incorrectly deciding that the condition is false and returning an empty result.
In a query involving a JOIN, the const‑table optimization is not applicable, so the condition is evaluated at runtime using correct DOUBLE semantics, yielding the correct result.
This is an optimizer defect in the handling of overflow during type conversion in the const‑table path.
Actual Results and Status
Query Actual Result Status
Single‑table (const table) Empty set Incorrect
View (JOIN path) 1 row (954517769) Correct
EXPLAIN Analysis
Single‑table query (incorrect):
-> Zero rows (no matching row in const table)
The optimizer treats s as a const table and pre‑evaluates the condition, incorrectly deciding no rows match.
View query (correct):
-> Table scan on <temporary>
-> Temporary table with deduplication
-> Nested loop inner join
-> Filter: (l.c0 < <cache>(ifnull(1.7976931348623157E308,-(1605651052))))
-> Table scan on l
-> Single-row index lookup on r using PRIMARY (id = l.id)
Because of the JOIN, const‑table optimization is bypassed, and the condition is evaluated at runtime correctly.
How to repeat:
DROP DATABASE IF EXISTS repro73_42;
CREATE DATABASE repro73_42;
USE repro73_42;
SET SESSION optimizer_switch =
'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on,hash_set_operations=on';
CREATE TABLE s (
id BIGINT NOT NULL PRIMARY KEY,
c0 FLOAT,
c1 DECIMAL(20,0)
) ENGINE=InnoDB;
INSERT INTO s VALUES (1, 0, 954517769);
CREATE INDEX s_c0 ON s(c0);
CREATE TABLE l (
id BIGINT NOT NULL PRIMARY KEY,
c0 FLOAT
) ENGINE=InnoDB;
CREATE TABLE r (
id BIGINT NOT NULL PRIMARY KEY,
c1 DECIMAL(20,0),
c0 FLOAT
) ENGINE=InnoDB;
INSERT INTO l SELECT id, c0 FROM s;
INSERT INTO r SELECT id, c1, c0 FROM s;
CREATE VIEW v AS
SELECT l.id, l.c0, r.c1
FROM l JOIN r ON l.id = r.id;
-- Single‑table query (incorrect): returns Empty set
SELECT 'single' AS qt, s.c1 AS ref0
FROM s
WHERE s.c0 < IFNULL(1.7976931348623157E308, -1605651052)
GROUP BY s.c1;
-- View query (correct): returns one row
SELECT 'split' AS qt, v.c1 AS ref0
FROM v
WHERE v.c0 < IFNULL(1.7976931348623157E308, -1605651052)
GROUP BY v.c1;
Description: When a table contains a single row and is accessed via a primary key or unique index, the optimizer treats it as a const table and pre‑evaluates the WHERE condition during the optimization phase. If the condition compares a FLOAT column with a DOUBLE constant that far exceeds the maximum representable value of FLOAT (e.g., 1.7976931348623157E308), the optimizer may truncate the constant to 0 or convert it to NaN during pre‑evaluation, incorrectly deciding that the condition is false and returning an empty result. In a query involving a JOIN, the const‑table optimization is not applicable, so the condition is evaluated at runtime using correct DOUBLE semantics, yielding the correct result. This is an optimizer defect in the handling of overflow during type conversion in the const‑table path. Actual Results and Status Query Actual Result Status Single‑table (const table) Empty set Incorrect View (JOIN path) 1 row (954517769) Correct EXPLAIN Analysis Single‑table query (incorrect): -> Zero rows (no matching row in const table) The optimizer treats s as a const table and pre‑evaluates the condition, incorrectly deciding no rows match. View query (correct): -> Table scan on <temporary> -> Temporary table with deduplication -> Nested loop inner join -> Filter: (l.c0 < <cache>(ifnull(1.7976931348623157E308,-(1605651052)))) -> Table scan on l -> Single-row index lookup on r using PRIMARY (id = l.id) Because of the JOIN, const‑table optimization is bypassed, and the condition is evaluated at runtime correctly. How to repeat: DROP DATABASE IF EXISTS repro73_42; CREATE DATABASE repro73_42; USE repro73_42; SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on,hash_set_operations=on'; CREATE TABLE s ( id BIGINT NOT NULL PRIMARY KEY, c0 FLOAT, c1 DECIMAL(20,0) ) ENGINE=InnoDB; INSERT INTO s VALUES (1, 0, 954517769); CREATE INDEX s_c0 ON s(c0); CREATE TABLE l ( id BIGINT NOT NULL PRIMARY KEY, c0 FLOAT ) ENGINE=InnoDB; CREATE TABLE r ( id BIGINT NOT NULL PRIMARY KEY, c1 DECIMAL(20,0), c0 FLOAT ) ENGINE=InnoDB; INSERT INTO l SELECT id, c0 FROM s; INSERT INTO r SELECT id, c1, c0 FROM s; CREATE VIEW v AS SELECT l.id, l.c0, r.c1 FROM l JOIN r ON l.id = r.id; -- Single‑table query (incorrect): returns Empty set SELECT 'single' AS qt, s.c1 AS ref0 FROM s WHERE s.c0 < IFNULL(1.7976931348623157E308, -1605651052) GROUP BY s.c1; -- View query (correct): returns one row SELECT 'split' AS qt, v.c1 AS ref0 FROM v WHERE v.c0 < IFNULL(1.7976931348623157E308, -1605651052) GROUP BY v.c1;