MySQL Bugs: #121477: Wrong result: MIN()/MAX() on an indexed FLOAT column return a value that does not satisfy the WHERE clause

Bug #121477	Wrong result: MIN()/MAX() on an indexed FLOAT column return a value that does not satisfy the WHERE clause
Submitted:	11 Oct 15:37	Modified:	13 Oct 8:07
Reporter:	Ann Calla	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	26.7.1	OS:	Ubuntu (24.04)
Assigned to:		CPU Architecture:	Any (x86_64)

Description:
When MIN() or MAX() over an indexed FLOAT column is answered directly from the index, the constant in the WHERE clause appears to be rounded to FLOAT before the lookup. The query then returns a value that the same WHERE clause rejects when rows are selected normally.

16777217 is not representable as FLOAT and rounds to 16777216; 33554431 rounds to 33554432. COUNT(*) and a plain SELECT with the same predicate are correct, and so are MIN()/MAX() with IGNORE INDEX.

How to repeat:
CREATE TABLE t(c FLOAT, KEY(c));
INSERT INTO t VALUES (1), (16777216), (33554432);

SELECT COUNT(*) FROM t WHERE c = 16777217;                  -- 0
SELECT MIN(c) FROM t IGNORE INDEX (c) WHERE c = 16777217;   -- NULL (correct)
SELECT MIN(c) FROM t WHERE c = 16777217;
-- expected: NULL
-- actual:   16777200   (the row 16777216)

SELECT c FROM t WHERE c <= 33554431;                        -- 1, 16777200
SELECT MAX(c) FROM t IGNORE INDEX (c) WHERE c <= 33554431;  -- 16777200 (correct)
SELECT MAX(c) FROM t WHERE c <= 33554431;
-- expected: 16777200
-- actual:   33554400   (the row 33554432, which is greater than 33554431)

Hi Ann Calla,

Thank you for the test case. Verified as described. However we do have some more bugs reports in the same area of concern like Bug#121325 and Bug#121329. When we work on the fix for these bugs, we will fix this case as well. So we are marking this as a duplicate.