Description:
When the WHERE condition is bigint_column IN (COALESCE(float_constant, NULL, NULL, int_constant)), and COALESCE evaluates to a floating‑point number (e.g., 0.36047097332362965), the optimizer, when choosing an index lookup path, converts the floating‑point constant to BIGINT (truncating it to 0), thereby incorrectly matching rows where the column equals 0.
In the original full‑table scan path, the comparison preserves floating‑point precision, so 0.360... is not equal to 0, and no rows match (empty set).
In the rewritten query, because the column c1 comes from l and has an index, the optimizer uses an index lookup, triggering the truncation and causing an incorrect result (NULL from DISTINCTROW over a single NULL row).
Actual Results and Status
Query Actual Result Status
Original (FROM s) Empty set Correct
Rewritten (with index) NULL Incorrect
EXPLAIN Analysis (Rewritten Query – Key Part)
Table scan on <temporary>
-> Temporary table with deduplication
-> Nested loop inner join
-> Covering index lookup on l using l_idx
(c1 = coalesce(0.36047097332362965,NULL,NULL,275915752))
-> Single-row index lookup on r using PRIMARY
The optimizer converts the IN condition to an equality c1 = COALESCE(...).
Because l.c1 has index l_idx, an Index lookup is chosen. The constant COALESCE(...) (value 0.360...) is cast to BIGINT, truncating to 0.
The index lookup matches the row with c1 = 0, causing the outer DISTINCTROW to output a row with NULL.
Full‑table scan path: s.c1 has no index, so a full scan is used. The comparison follows standard implicit conversion: the BIGINT value 0 is cast to DOUBLE (0.0) and compared with 0.360..., yielding FALSE.
How to repeat:
DROP DATABASE IF EXISTS codex_db7;
CREATE DATABASE codex_db7;
USE codex_db7;
SET SESSION sql_mode =
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
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';
SET SESSION big_tables = 1;
SET SESSION internal_tmp_mem_storage_engine = 'MEMORY';
CREATE TABLE s(
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 FLOAT NULL,
c1 BIGINT NULL
) ENGINE=InnoDB;
INSERT INTO s(c0, c1) VALUES (NULL, 0);
CREATE TABLE l(
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c1 BIGINT NULL,
KEY l_idx(c1)
) ENGINE=InnoDB;
CREATE TABLE r(
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c0 FLOAT NULL
) ENGINE=InnoDB;
INSERT INTO l(vp_rowid, c1)
SELECT vp_rowid, c1 FROM s;
INSERT INTO r(vp_rowid, c0)
SELECT vp_rowid, c0 FROM s;
-- Original query (correct): Empty set
SELECT DISTINCTROW c0 AS ref0
FROM s
WHERE c1 IN (COALESCE(0.36047097332362965, NULL, NULL, 275915752));
-- Rewritten query (incorrect): returns NULL (should be Empty set)
SELECT DISTINCTROW d.c0 AS ref0
FROM (
SELECT l.vp_rowid AS vp_rowid, r.c0 AS c0, l.c1 AS c1
FROM l
JOIN r ON l.vp_rowid = r.vp_rowid
) d
WHERE d.c1 IN (COALESCE(0.36047097332362965, NULL, NULL, 275915752));
Description: When the WHERE condition is bigint_column IN (COALESCE(float_constant, NULL, NULL, int_constant)), and COALESCE evaluates to a floating‑point number (e.g., 0.36047097332362965), the optimizer, when choosing an index lookup path, converts the floating‑point constant to BIGINT (truncating it to 0), thereby incorrectly matching rows where the column equals 0. In the original full‑table scan path, the comparison preserves floating‑point precision, so 0.360... is not equal to 0, and no rows match (empty set). In the rewritten query, because the column c1 comes from l and has an index, the optimizer uses an index lookup, triggering the truncation and causing an incorrect result (NULL from DISTINCTROW over a single NULL row). Actual Results and Status Query Actual Result Status Original (FROM s) Empty set Correct Rewritten (with index) NULL Incorrect EXPLAIN Analysis (Rewritten Query – Key Part) Table scan on <temporary> -> Temporary table with deduplication -> Nested loop inner join -> Covering index lookup on l using l_idx (c1 = coalesce(0.36047097332362965,NULL,NULL,275915752)) -> Single-row index lookup on r using PRIMARY The optimizer converts the IN condition to an equality c1 = COALESCE(...). Because l.c1 has index l_idx, an Index lookup is chosen. The constant COALESCE(...) (value 0.360...) is cast to BIGINT, truncating to 0. The index lookup matches the row with c1 = 0, causing the outer DISTINCTROW to output a row with NULL. Full‑table scan path: s.c1 has no index, so a full scan is used. The comparison follows standard implicit conversion: the BIGINT value 0 is cast to DOUBLE (0.0) and compared with 0.360..., yielding FALSE. How to repeat: DROP DATABASE IF EXISTS codex_db7; CREATE DATABASE codex_db7; USE codex_db7; SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; 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'; SET SESSION big_tables = 1; SET SESSION internal_tmp_mem_storage_engine = 'MEMORY'; CREATE TABLE s( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 FLOAT NULL, c1 BIGINT NULL ) ENGINE=InnoDB; INSERT INTO s(c0, c1) VALUES (NULL, 0); CREATE TABLE l( vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 BIGINT NULL, KEY l_idx(c1) ) ENGINE=InnoDB; CREATE TABLE r( vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 FLOAT NULL ) ENGINE=InnoDB; INSERT INTO l(vp_rowid, c1) SELECT vp_rowid, c1 FROM s; INSERT INTO r(vp_rowid, c0) SELECT vp_rowid, c0 FROM s; -- Original query (correct): Empty set SELECT DISTINCTROW c0 AS ref0 FROM s WHERE c1 IN (COALESCE(0.36047097332362965, NULL, NULL, 275915752)); -- Rewritten query (incorrect): returns NULL (should be Empty set) SELECT DISTINCTROW d.c0 AS ref0 FROM ( SELECT l.vp_rowid AS vp_rowid, r.c0 AS c0, l.c1 AS c1 FROM l JOIN r ON l.vp_rowid = r.vp_rowid ) d WHERE d.c1 IN (COALESCE(0.36047097332362965, NULL, NULL, 275915752));