Bug #120083 incorrect result when comparing floating constant in OUTER JOIN condition

• Submitted: 17 Mar 8:59
• Modified: 17 Mar 11:38
• Reporter: Weipeng Wang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.45
• OS: Linux
• CPU Architecture: x86

[17 Mar 8:59] Weipeng Wang

Description:
A LEFT JOIN condition involving a floating-point constant may be evaluated incorrectly.

The following two queries should be equivalent, since 0.1 = t1.c0 evaluates to FALSE for t1.c0 = 0.

However, they return different results.

How to repeat:
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);

CREATE INDEX i1 ON t1(c0);
   
INSERT INTO t0(c0) VALUES(1);
INSERT INTO t1(c0) VALUES(1);

SELECT t0.c0 AS ref0, t1.c0 AS ref1 FROM t0 LEFT JOIN t1 ON t1.c0 = 1.1;
/* returns a row: (1, 1) */

SELECT t0.c0 AS ref0, t1.c0 AS ref1 FROM t0 LEFT JOIN t1 ON FALSE;
/* returns a row: (1, NULL) */

[17 Mar 11:38] Roy Lyseng

Thank you for the bug report.
Verified as described.