Description:
When a table contains multiple extremely large DOUBLE values (e.g., 1.0E308, near DBL_MAX) that are grouped into the same GROUP BY group, the AVG aggregate returns different results depending on the execution path:
In the covering index scan (single‑table) path, AVG returns 0 (incorrect).
In the view + JOIN + temporary table deduplication path, AVG returns 1.7976931348623157e308 (i.e., DBL_MAX).
According to floating‑point arithmetic, the average of two identical large values should be that value itself (or a value of similar magnitude). The correct result should be the same regardless of the path. The 0 result is clearly wrong, while the temporary‑table result, though not strictly correct (due to overflow handling), at least preserves the magnitude. This indicates that the optimizer’s “fast path” (covering index scan aggregation) skips necessary overflow protection or precision handling when processing extremely large DOUBLE values.
Actual Results and Status
Query Actual AVG(x) Result Status
Single‑table (covering index scan) 0 Incorrect
View (temporary table dedup) 1.7976931348623157e308 correct
EXPLAIN Analysis
Single‑table query plan (fast path):
-> Group aggregate: sum(distinct 0.829...), avg(s.x)
-> Sort: b
-> Covering index scan on s using sx
View query plan (slow path):
-> Sort with duplicate removal: a, b, d
-> Stream results
-> Group aggregate: sum(distinct 0.829...), avg(r.x)
-> Sort: ((coalesce(r.x,l.id) <> cast(l.id as signed)) or (r.x >= l.id))
-> Stream results
-> Nested loop inner join
-> Covering index scan on l using PRIMARY
-> Single-row index lookup on r using PRIMARY
How to repeat:
DROP DATABASE IF EXISTS repro628_5;
CREATE DATABASE repro628_5;
USE repro628_5;
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';
CREATE TABLE s (
id BIGINT NOT NULL PRIMARY KEY,
x DOUBLE NULL
) ENGINE=InnoDB;
INSERT INTO s VALUES
(1, NULL),
(2, 1.0E308),
(3, 1.0E308);
CREATE INDEX sx ON s(x, id);
CREATE TABLE l (id BIGINT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE r (id BIGINT NOT NULL PRIMARY KEY, x DOUBLE NULL) ENGINE=InnoDB;
INSERT INTO l SELECT id FROM s;
INSERT INTO r SELECT id, x FROM s;
CREATE VIEW v AS
SELECT l.id, r.x
FROM l JOIN r ON l.id = r.id;
-- Single-table query (covering index scan, returns AVG = 0, incorrect)
SELECT DISTINCT
SUM(DISTINCT 0.829074650150795) AS a,
((COALESCE(s.x, s.id)) NOT IN (CAST(s.id AS SIGNED)))
|| ((+ ((s.x) >= (s.id)))) AS b,
EXISTS (SELECT 1 WHERE FALSE) AS c,
AVG(x) AS d
FROM s
GROUP BY
((COALESCE(s.x, s.id)) NOT IN (CAST(s.id AS SIGNED)))
|| ((+ ((s.x) >= (s.id))));
-- View query (temporary table dedup, returns AVG = 1.7976931348623157e308)
SELECT DISTINCT
SUM(DISTINCT 0.829074650150795) AS a,
((COALESCE(v.x, v.id)) NOT IN (CAST(v.id AS SIGNED)))
|| ((+ ((v.x) >= (v.id)))) AS b,
EXISTS (SELECT 1 WHERE FALSE) AS c,
AVG(x) AS d
FROM v
GROUP BY
((COALESCE(v.x, v.id)) NOT IN (CAST(v.id AS SIGNED)))
|| ((+ ((v.x) >= (v.id))));
Description: When a table contains multiple extremely large DOUBLE values (e.g., 1.0E308, near DBL_MAX) that are grouped into the same GROUP BY group, the AVG aggregate returns different results depending on the execution path: In the covering index scan (single‑table) path, AVG returns 0 (incorrect). In the view + JOIN + temporary table deduplication path, AVG returns 1.7976931348623157e308 (i.e., DBL_MAX). According to floating‑point arithmetic, the average of two identical large values should be that value itself (or a value of similar magnitude). The correct result should be the same regardless of the path. The 0 result is clearly wrong, while the temporary‑table result, though not strictly correct (due to overflow handling), at least preserves the magnitude. This indicates that the optimizer’s “fast path” (covering index scan aggregation) skips necessary overflow protection or precision handling when processing extremely large DOUBLE values. Actual Results and Status Query Actual AVG(x) Result Status Single‑table (covering index scan) 0 Incorrect View (temporary table dedup) 1.7976931348623157e308 correct EXPLAIN Analysis Single‑table query plan (fast path): -> Group aggregate: sum(distinct 0.829...), avg(s.x) -> Sort: b -> Covering index scan on s using sx View query plan (slow path): -> Sort with duplicate removal: a, b, d -> Stream results -> Group aggregate: sum(distinct 0.829...), avg(r.x) -> Sort: ((coalesce(r.x,l.id) <> cast(l.id as signed)) or (r.x >= l.id)) -> Stream results -> Nested loop inner join -> Covering index scan on l using PRIMARY -> Single-row index lookup on r using PRIMARY How to repeat: DROP DATABASE IF EXISTS repro628_5; CREATE DATABASE repro628_5; USE repro628_5; 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'; CREATE TABLE s ( id BIGINT NOT NULL PRIMARY KEY, x DOUBLE NULL ) ENGINE=InnoDB; INSERT INTO s VALUES (1, NULL), (2, 1.0E308), (3, 1.0E308); CREATE INDEX sx ON s(x, id); CREATE TABLE l (id BIGINT NOT NULL PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE r (id BIGINT NOT NULL PRIMARY KEY, x DOUBLE NULL) ENGINE=InnoDB; INSERT INTO l SELECT id FROM s; INSERT INTO r SELECT id, x FROM s; CREATE VIEW v AS SELECT l.id, r.x FROM l JOIN r ON l.id = r.id; -- Single-table query (covering index scan, returns AVG = 0, incorrect) SELECT DISTINCT SUM(DISTINCT 0.829074650150795) AS a, ((COALESCE(s.x, s.id)) NOT IN (CAST(s.id AS SIGNED))) || ((+ ((s.x) >= (s.id)))) AS b, EXISTS (SELECT 1 WHERE FALSE) AS c, AVG(x) AS d FROM s GROUP BY ((COALESCE(s.x, s.id)) NOT IN (CAST(s.id AS SIGNED))) || ((+ ((s.x) >= (s.id)))); -- View query (temporary table dedup, returns AVG = 1.7976931348623157e308) SELECT DISTINCT SUM(DISTINCT 0.829074650150795) AS a, ((COALESCE(v.x, v.id)) NOT IN (CAST(v.id AS SIGNED))) || ((+ ((v.x) >= (v.id)))) AS b, EXISTS (SELECT 1 WHERE FALSE) AS c, AVG(x) AS d FROM v GROUP BY ((COALESCE(v.x, v.id)) NOT IN (CAST(v.id AS SIGNED))) || ((+ ((v.x) >= (v.id))));