Bug #121478 Wrong result: join on "float_key <= double_col" loses the match when the DOUBLE value exceeds FLT_MAX

• Submitted: 11 Oct 15:39
• Modified: 13 Oct 9:36
• Reporter: Ann Calla
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 26.7.1
• OS: Ubuntu (24.04)
• CPU Architecture: x86 (x86_64)

[11 Oct 15:39] Ann Calla

Description:
A join whose inner table is read with "Index range scan (re-planned for each iteration)" on a FLOAT key returns no row when the outer DOUBLE value is larger than the FLOAT range (1e39 here). The predicate itself evaluates to 1 for the pair, and the join is correct with IGNORE INDEX. A LEFT JOIN returns the outer row padded with NULL instead of the match.

Possibly related to my other report about MIN()/MAX() on a FLOAT index: both involve a constant or outer value being converted to FLOAT for an index range.

How to repeat:
CREATE TABLE k(c FLOAT PRIMARY KEY);
CREATE TABLE o(d DOUBLE);
INSERT INTO k VALUES (0);
INSERT INTO o VALUES (1e39);

SELECT o.d, k.c, k.c <= o.d FROM o, k;
-- 1e39  0  1        (the predicate is true)

SELECT o.d, k.c FROM o JOIN k IGNORE INDEX (PRIMARY) ON k.c <= o.d;
-- 1e39  0           (correct)

SELECT o.d, k.c FROM o JOIN k ON k.c <= o.d;
-- expected: 1e39  0
-- actual:   empty

SELECT o.d, k.c FROM o LEFT JOIN k ON k.c <= o.d;
-- expected: 1e39  0
-- actual:   1e39  NULL

EXPLAIN FORMAT=TREE SELECT o.d, k.c FROM o JOIN k ON k.c <= o.d;
-- -> Nested loop inner join
--     -> Table scan on o
--     -> Filter: (k.c <= o.d)
--         -> Index range scan on k (re-planned for each iteration)

[13 Oct 9:36] Chaithra Marsur Gopala Reddy

Hi Ann Calla,

Thank you for the test case. Verified as described. However, we think this is in the same area of problem that Bug#121431 reports. So this bug will be taken care of when we look into fixing that issue.

Thanks,
Chaithra