Bug #120800 LATERAL Derived Table Merge Causes Type Propagation Error, FLOAT Precision Loss in CASE Expression Constants

• Submitted: 28 Jun 11:51
• Modified: 29 Jun 2:48
• Reporter: Annie liu
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: MySQL 9.6
• OS: Any
• CPU Architecture: Any

[28 Jun 11:51] Annie liu

Description:
When a derived table containing a LATERAL JOIN is merged into the outer query via derived_merge, the optimizer incorrectly propagates the type of a FLOAT column from inside the derived table to the outer CASE expression.

As a result, the entire CASE expression is inferred as FLOAT, causing the integer constant in the ELSE branch (e.g., 1455738264) to be implicitly converted to FLOAT before evaluation.

Because FLOAT is a 32-bit single‑precision type, the constant is rounded to the nearest representable value (1455738240), losing precision by 24.
The SUM aggregate then accumulates the rounded value, leading to incorrect results.

In the equivalent query executed directly on the base table, the CASE expression retains its integer context and the constant remains exact.

Results

Query	                Actual Result (ref0)	Status
Base table (FROM s)	1455738264 (per row)	✓
Derived table (LATERAL)	1455738240 (per row)	✗ (diff 24)

EXPLAIN Analysis (Derived Table Version – Key Part)

Group aggregate: sum(tmp_field), sum(distinct l.vp_rowid)
  -> Sort: ref1
    -> Stream results
      -> Nested loop inner join
        -> Covering index scan on l using PRIMARY
        -> Single-row index lookup on rr using PRIMARY (vp_rowid = l.vp_rowid)

The plan shows the aggregate input comes from a Nested loop inner join between l and r, with derived_merge having merged the derived table.

After merging, the CASE ... ELSE 1455738264 expression is inferred as FLOAT, not INT/DECIMAL.

The tmp_field stores the converted floating‑point value 1455738240.0 instead of the original integer 1455738264.

How to repeat:
DROP DATABASE IF EXISTS codex_db8;
CREATE DATABASE codex_db8;
USE codex_db8;

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(
  vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c0 FLOAT NULL,
  KEY i0(vp_rowid, c0)
) ENGINE=InnoDB;

INSERT INTO s(c0) VALUES (NULL), (NULL), (NULL), (NULL);

CREATE TABLE l(
  vp_rowid BIGINT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE r(
  vp_rowid BIGINT NOT NULL PRIMARY KEY,
  c0 FLOAT NULL
) ENGINE=InnoDB;

INSERT INTO l(vp_rowid)
SELECT vp_rowid FROM s;

INSERT INTO r(vp_rowid, c0)
SELECT vp_rowid, c0 FROM s;

-- Base table query (correct): SUM returns 1455738264
SELECT
  SUM(IF((+ (NULL)),
         (CASE 0.7946739770279212
            WHEN '0.9061888832149968' THEN c0
            ELSE vp_rowid
          END),
         (+ (1455738264)))) AS ref0,
  CAST(IFNULL(vp_rowid, c0) AS SIGNED) AS ref1,
  SUM(DISTINCT vp_rowid) AS ref2
FROM s
GROUP BY CAST(IFNULL(vp_rowid, c0) AS SIGNED);

-- LATERAL derived table rewrite (incorrect): SUM returns 1455738240, difference of 24
SELECT
  SUM(IF((+ (NULL)),
         (CASE 0.7946739770279212
            WHEN '0.9061888832149968' THEN d.c0
            ELSE d.vp_rowid
          END),
         (+ (1455738264)))) AS ref0,
  CAST(IFNULL(d.vp_rowid, d.c0) AS SIGNED) AS ref1,
  SUM(DISTINCT d.vp_rowid) AS ref2
FROM (
  SELECT l.vp_rowid AS vp_rowid, r.c0 AS c0
  FROM l
  JOIN LATERAL (
    SELECT * FROM r rr WHERE rr.vp_rowid = l.vp_rowid
  ) r ON TRUE
) d
GROUP BY CAST(IFNULL(d.vp_rowid, d.c0) AS SIGNED);

[28 Jun 20:26] Roy Lyseng

Thank you for the bug report.

However, your analysis is slightly incorrect.

You say "In the equivalent query executed directly on the base table, the CASE expression retains its integer context and the constant remains exact."
But the CASE expression does have an "integer context". It's type is always FLOAT.

What happens here is that in one case, the integer value is converted directly to a DOUBLE, whereas in the other case, it is converted first to a FLOAT value, then to a DOUBLE. This is what causes the discrepancy in results, however it is within the accuracy of a FLOAT value.

[29 Jun 2:48] Annie liu

Thank you for the detailed explanation. I agree with your assessment.

The CASE expression's type is indeed FLOAT according to MySQL's aggregation rules, and the precision loss of the integer constant when converted to FLOAT is within the expected accuracy of the FLOAT type.