Bug #119363 Complex WHERE Expression on Indexed FLOAT Column Evaluates Incorrectly

• Submitted: 12 Nov 2:52
• Modified: 12 Nov 3:09
• Reporter: zz z
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.4.0 8.4.6
• OS: Any
• CPU Architecture: Any

[12 Nov 2:52] zz z

Description:
A WHERE clause containing a complex expression with IF, CAST, and FROM_DAYS functions is severely mis-evaluated, but only under very specific conditions. Logically, the expression should always resolve to NULL, causing the WHERE clause to match no rows. However, the query incorrectly returns all rows. This bug is triggered only when: 1) The FLOAT column is indexed (KEY), and 2) The table contains the 5 specific rows provided. 

How to repeat:
CREATE TABLE t363 (c1 VARBINARY (9), c2 FLOAT KEY);
INSERT t363 () VALUES ('1Q0C',2.9068204E38);
INSERT t363 () VALUES ('3',-2.3296179E38);
INSERT t363 () VALUES ('zY',2.3984515E38);
INSERT t363 () VALUES ('NInse5',-2.9963384E38);
INSERT t363 () VALUES ('x',2.4499855E38);

SELECT * FROM t363 WHERE (IF((CAST(0 AS DATE) IN (FROM_DAYS(t363.c2), '14:52:51')), '2001-09-24 12:00:22', NULL) IN (SELECT '2001-09-24 12:00:22' FROM t363));
-- Returned Row Count: 5

SELECT SUM(count) FROM (SELECT ((IF((CAST(0 AS DATE) IN (FROM_DAYS(t363.c2), '14:52:51')), '2001-09-24 12:00:22', NULL) IN (SELECT '2001-09-24 12:00:22' FROM t363))) IS TRUE AS count FROM t363) AS ta_norec;
-- Returned: 0

[12 Nov 3:09] Chaithra Marsur Gopala Reddy

Hi zz z,

Thank you for the test case. Verified as described.