Description:
When a query requires a temporary table for deduplication (e.g., due to NOT EXISTS anti‑join transformation, leading to “Temporary table with deduplication”), the uniqueness check for FLOAT columns in that temporary table uses binary byte‑by‑byte comparison instead of numeric‑value comparison.
Because -0 and +0 are numerically equal in IEEE 754 but have different binary representations (sign bit differs), the deduplication treats them as distinct values. As a result, GROUP BY or DISTINCT operations that should merge them produce an extra row containing 0.
When the same GROUP BY is performed directly on the base table, MySQL uses numeric comparison and correctly merges -0 and +0 into one row, complying with the SQL standard.
Results
Query Actual Result Status
Single‑table GROUP BY 4 rows: 1903340000, 1894990000, -0, NULL ✓
Rewritten query 5 rows: 1903340000, 1894990000, -0, 0, NULL ✗
EXPLAIN Analysis (Incorrect Query)
-> Table scan on <temporary> (cost=6.86..9.01 rows=6)
-> Temporary table with deduplication (cost=6.43..6.43 rows=6)
-> Nested loop antijoin (cost=5.05 rows=6)
-> Nested loop inner join (cost=2.95 rows=6)
-> Covering index scan on l using PRIMARY (cost=0.85 rows=6)
-> Single-row index lookup on r using PRIMARY (id = l.id) (cost=0.267 rows=1)
-> Filter: (l.id <> l.id) (cost=0.267 rows=1)
-> Single-row covering index lookup on rx using PRIMARY (id = l.id) (cost=0.267 rows=1)
Key point:
The optimizer transforms NOT EXISTS into an anti‑join and requires materialization into a temporary table.
The temporary table uses a unique index to perform deduplication (Temporary table with deduplication).
This unique index compares FLOAT values using binary‑level bytes instead of numeric semantics.
Root Cause Analysis
IEEE 754 representation: -0 is stored as 0x80000000, +0 as 0x00000000. They are numerically equal but bitwise different.
Base‑table GROUP BY: MySQL performs numeric comparisons (floating‑point equality) during grouping, so -0 and +0 are placed in the same group.
Temporary table deduplication: When deduplication is performed via a temporary table (e.g., after anti‑join), the unique index uses memcmp()‑style binary comparison (or direct byte‑by‑byte comparison) without normalizing the floating‑point value. This treats -0 and +0 as different keys, resulting in both appearing in the final DISTINCT/GROUP BY output.
Design flaw: Temporary‑table deduplication should honour SQL‑standard value equality (numeric equality), not the underlying storage representation.
How to repeat:
DROP DATABASE IF EXISTS codex_db12;
CREATE DATABASE codex_db12;
USE codex_db12;
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 = 0;
SET SESSION internal_tmp_mem_storage_engine = 'MEMORY';
CREATE TABLE s(
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 FLOAT NULL,
KEY idx(c0 DESC)
) ENGINE=InnoDB;
INSERT INTO s(c0)
VALUES (1903340000), (1894990000), (-0e0), (-0e0), (0e0), (NULL);
CREATE TABLE l(id BIGINT NOT NULL PRIMARY KEY, c0 FLOAT NULL) ENGINE=InnoDB;
CREATE TABLE r(id BIGINT NOT NULL PRIMARY KEY, c0 FLOAT NULL) ENGINE=InnoDB;
INSERT INTO l SELECT id, c0 FROM s;
INSERT INTO r SELECT id, c0 FROM s;
-- Single‑table query (correct, returns 4 rows, -0 and 0 merged as -0)
SELECT DISTINCT s.c0 AS ref0
FROM s
GROUP BY s.c0;
-- Rewritten query (incorrect, returns 5 rows, extra 0)
SELECT DISTINCT d.c0 AS ref0
FROM (
SELECT l.id, r.c0
FROM l
JOIN r ON l.id = r.id
WHERE NOT EXISTS (
SELECT 1
FROM r rx
WHERE rx.id = l.id
AND rx.id <> l.id
)
) d
GROUP BY d.c0;
Description: When a query requires a temporary table for deduplication (e.g., due to NOT EXISTS anti‑join transformation, leading to “Temporary table with deduplication”), the uniqueness check for FLOAT columns in that temporary table uses binary byte‑by‑byte comparison instead of numeric‑value comparison. Because -0 and +0 are numerically equal in IEEE 754 but have different binary representations (sign bit differs), the deduplication treats them as distinct values. As a result, GROUP BY or DISTINCT operations that should merge them produce an extra row containing 0. When the same GROUP BY is performed directly on the base table, MySQL uses numeric comparison and correctly merges -0 and +0 into one row, complying with the SQL standard. Results Query Actual Result Status Single‑table GROUP BY 4 rows: 1903340000, 1894990000, -0, NULL ✓ Rewritten query 5 rows: 1903340000, 1894990000, -0, 0, NULL ✗ EXPLAIN Analysis (Incorrect Query) -> Table scan on <temporary> (cost=6.86..9.01 rows=6) -> Temporary table with deduplication (cost=6.43..6.43 rows=6) -> Nested loop antijoin (cost=5.05 rows=6) -> Nested loop inner join (cost=2.95 rows=6) -> Covering index scan on l using PRIMARY (cost=0.85 rows=6) -> Single-row index lookup on r using PRIMARY (id = l.id) (cost=0.267 rows=1) -> Filter: (l.id <> l.id) (cost=0.267 rows=1) -> Single-row covering index lookup on rx using PRIMARY (id = l.id) (cost=0.267 rows=1) Key point: The optimizer transforms NOT EXISTS into an anti‑join and requires materialization into a temporary table. The temporary table uses a unique index to perform deduplication (Temporary table with deduplication). This unique index compares FLOAT values using binary‑level bytes instead of numeric semantics. Root Cause Analysis IEEE 754 representation: -0 is stored as 0x80000000, +0 as 0x00000000. They are numerically equal but bitwise different. Base‑table GROUP BY: MySQL performs numeric comparisons (floating‑point equality) during grouping, so -0 and +0 are placed in the same group. Temporary table deduplication: When deduplication is performed via a temporary table (e.g., after anti‑join), the unique index uses memcmp()‑style binary comparison (or direct byte‑by‑byte comparison) without normalizing the floating‑point value. This treats -0 and +0 as different keys, resulting in both appearing in the final DISTINCT/GROUP BY output. Design flaw: Temporary‑table deduplication should honour SQL‑standard value equality (numeric equality), not the underlying storage representation. How to repeat: DROP DATABASE IF EXISTS codex_db12; CREATE DATABASE codex_db12; USE codex_db12; 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 = 0; SET SESSION internal_tmp_mem_storage_engine = 'MEMORY'; CREATE TABLE s( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 FLOAT NULL, KEY idx(c0 DESC) ) ENGINE=InnoDB; INSERT INTO s(c0) VALUES (1903340000), (1894990000), (-0e0), (-0e0), (0e0), (NULL); CREATE TABLE l(id BIGINT NOT NULL PRIMARY KEY, c0 FLOAT NULL) ENGINE=InnoDB; CREATE TABLE r(id BIGINT NOT NULL PRIMARY KEY, c0 FLOAT NULL) ENGINE=InnoDB; INSERT INTO l SELECT id, c0 FROM s; INSERT INTO r SELECT id, c0 FROM s; -- Single‑table query (correct, returns 4 rows, -0 and 0 merged as -0) SELECT DISTINCT s.c0 AS ref0 FROM s GROUP BY s.c0; -- Rewritten query (incorrect, returns 5 rows, extra 0) SELECT DISTINCT d.c0 AS ref0 FROM ( SELECT l.id, r.c0 FROM l JOIN r ON l.id = r.id WHERE NOT EXISTS ( SELECT 1 FROM r rx WHERE rx.id = l.id AND rx.id <> l.id ) ) d GROUP BY d.c0;