Description:
When a CASE expression has a THEN branch referencing a FLOAT column and an ELSE branch with a large integer constant that exceeds the precision of FLOAT (e.g., 1043103758), MySQL infers the result type of the whole expression as FLOAT.
In a direct base‑table scan, the optimizer can detect that the CASE condition is always false (e.g., 0.5 = id where id is an integer) and fold the expression to the constant 1043103758, avoiding any floating‑point conversion. As a result, the exact integer value is preserved.
However, when the query is rewritten to include multiple JOINs and derived tables, the optimizer cannot perform this constant folding, so the expression must be evaluated with its inferred FLOAT type. The large integer constant is converted to single‑precision FLOAT, rounded to a nearby representable value (e.g., 1043103744), leading to an incorrect result.
This creates inconsistent results between two semantically equivalent queries, violating the principle of query rewrite correctness.
Actual Results and Status
Query Actual Result (ref0) Status
Single‑table (FROM src) 1043103758 Correct
Derived table (multi‑JOIN) 1043103744 Incorrect (precision loss)
Note: The difference is 14, caused by rounding 1043103758 to the nearest FLOAT representable value 1043103744.
EXPLAIN Analysis
Single‑table query (correct):
-> Group aggregate: sum(distinct (case 0.5 when src.id then src.c1 else 1043103758 end))
-> Index scan on src using PRIMARY
Direct index scan; the optimizer folds the CASE expression early, avoiding the conversion.
Derived‑table query (incorrect) – key part:
-> Group aggregate: sum(distinct tmp_field)
-> Sort: id
-> Stream results
-> Nested loop inner join
-> Inner hash join (concat('vp:',l.id) = concat('vp:',r.id))
-> Covering index scan on r using PRIMARY
-> Hash
-> Covering index scan on l using li
-> Single-row covering index lookup on r using PRIMARY
Involves JOINs, hash joins, and materialization; the optimizer cannot fold the CASE expression early, so it is evaluated with FLOAT type, causing the constant to be rounded.
Root Cause Analysis
Constant folding in the single‑table path: In the simple base‑table scan, the optimizer analyzes the CASE condition 0.5 WHEN id. Since id is an integer and 0.5 is a decimal, the condition is always false; the THEN branch is never reached. The optimizer replaces the entire CASE with the ELSE constant 1043103758, without converting it to FLOAT. Thus, SUM(DISTINCT) operates on the exact integer.
No folding in derived‑table path: In the complex query, the CASE expression is embedded in multiple JOINs and a derived table. The optimizer cannot or does not attempt constant‑folding analysis. Therefore, the expression must be evaluated with its static FLOAT type. The constant 1043103758 is converted to FLOAT before evaluation. Near 2^30, the step size of single‑precision FLOAT is 128, so the constant rounds to 1043103744, leading to the discrepancy.
Inconsistency: The two semantically equivalent queries yield different results due to different optimizer optimization depths, which is a bug.
How to repeat:
DROP DATABASE IF EXISTS repro8_final;
CREATE DATABASE repro8_final;
USE repro8_final;
CREATE TABLE src (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c1 FLOAT NULL
) ENGINE=InnoDB;
INSERT INTO src(c1) VALUES (NULL);
CREATE TABLE l (
id BIGINT NOT NULL PRIMARY KEY,
c1 FLOAT NULL,
KEY li(id,c1)
) ENGINE=InnoDB;
CREATE TABLE r (
id BIGINT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
INSERT INTO l SELECT id,c1 FROM src;
INSERT INTO r SELECT id FROM src;
-- Single‑table query (correct): returns 1043103758
SELECT 'single' AS q,
SUM(DISTINCT CASE 0.5 WHEN id THEN c1 ELSE 1043103758 END) AS ref0
FROM src
GROUP BY id;
-- Derived‑table query (incorrect): returns 1043103744
SELECT 'split' AS q,
SUM(DISTINCT CASE 0.5 WHEN s.id THEN s.c1 ELSE 1043103758 END) AS ref0
FROM (
SELECT l.id AS id, l.c1 AS c1
FROM l
JOIN (
SELECT id, CONCAT('vp:', id) AS payload
FROM r
) sp ON CONCAT('vp:', l.id) = sp.payload
JOIN r ON r.id = sp.id
) s
GROUP BY id;
Description: When a CASE expression has a THEN branch referencing a FLOAT column and an ELSE branch with a large integer constant that exceeds the precision of FLOAT (e.g., 1043103758), MySQL infers the result type of the whole expression as FLOAT. In a direct base‑table scan, the optimizer can detect that the CASE condition is always false (e.g., 0.5 = id where id is an integer) and fold the expression to the constant 1043103758, avoiding any floating‑point conversion. As a result, the exact integer value is preserved. However, when the query is rewritten to include multiple JOINs and derived tables, the optimizer cannot perform this constant folding, so the expression must be evaluated with its inferred FLOAT type. The large integer constant is converted to single‑precision FLOAT, rounded to a nearby representable value (e.g., 1043103744), leading to an incorrect result. This creates inconsistent results between two semantically equivalent queries, violating the principle of query rewrite correctness. Actual Results and Status Query Actual Result (ref0) Status Single‑table (FROM src) 1043103758 Correct Derived table (multi‑JOIN) 1043103744 Incorrect (precision loss) Note: The difference is 14, caused by rounding 1043103758 to the nearest FLOAT representable value 1043103744. EXPLAIN Analysis Single‑table query (correct): -> Group aggregate: sum(distinct (case 0.5 when src.id then src.c1 else 1043103758 end)) -> Index scan on src using PRIMARY Direct index scan; the optimizer folds the CASE expression early, avoiding the conversion. Derived‑table query (incorrect) – key part: -> Group aggregate: sum(distinct tmp_field) -> Sort: id -> Stream results -> Nested loop inner join -> Inner hash join (concat('vp:',l.id) = concat('vp:',r.id)) -> Covering index scan on r using PRIMARY -> Hash -> Covering index scan on l using li -> Single-row covering index lookup on r using PRIMARY Involves JOINs, hash joins, and materialization; the optimizer cannot fold the CASE expression early, so it is evaluated with FLOAT type, causing the constant to be rounded. Root Cause Analysis Constant folding in the single‑table path: In the simple base‑table scan, the optimizer analyzes the CASE condition 0.5 WHEN id. Since id is an integer and 0.5 is a decimal, the condition is always false; the THEN branch is never reached. The optimizer replaces the entire CASE with the ELSE constant 1043103758, without converting it to FLOAT. Thus, SUM(DISTINCT) operates on the exact integer. No folding in derived‑table path: In the complex query, the CASE expression is embedded in multiple JOINs and a derived table. The optimizer cannot or does not attempt constant‑folding analysis. Therefore, the expression must be evaluated with its static FLOAT type. The constant 1043103758 is converted to FLOAT before evaluation. Near 2^30, the step size of single‑precision FLOAT is 128, so the constant rounds to 1043103744, leading to the discrepancy. Inconsistency: The two semantically equivalent queries yield different results due to different optimizer optimization depths, which is a bug. How to repeat: DROP DATABASE IF EXISTS repro8_final; CREATE DATABASE repro8_final; USE repro8_final; CREATE TABLE src ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c1 FLOAT NULL ) ENGINE=InnoDB; INSERT INTO src(c1) VALUES (NULL); CREATE TABLE l ( id BIGINT NOT NULL PRIMARY KEY, c1 FLOAT NULL, KEY li(id,c1) ) ENGINE=InnoDB; CREATE TABLE r ( id BIGINT NOT NULL PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO l SELECT id,c1 FROM src; INSERT INTO r SELECT id FROM src; -- Single‑table query (correct): returns 1043103758 SELECT 'single' AS q, SUM(DISTINCT CASE 0.5 WHEN id THEN c1 ELSE 1043103758 END) AS ref0 FROM src GROUP BY id; -- Derived‑table query (incorrect): returns 1043103744 SELECT 'split' AS q, SUM(DISTINCT CASE 0.5 WHEN s.id THEN s.c1 ELSE 1043103758 END) AS ref0 FROM ( SELECT l.id AS id, l.c1 AS c1 FROM l JOIN ( SELECT id, CONCAT('vp:', id) AS payload FROM r ) sp ON CONCAT('vp:', l.id) = sp.payload JOIN r ON r.id = sp.id ) s GROUP BY id;