Bug #119991 Incorrect result with LEFT/RIGHT JOIN when comparing boolean expression with = 0 in presence of functional index

• Submitted: 6 Mar 5:56
• Modified: 6 Mar 11:27
• Reporter: Weipeng Wang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.45
• OS: Linux
• CPU Architecture: x86

[6 Mar 5:56] Weipeng Wang

Description:
A query using a LEFT JOIN with a predicate of the form:

(t0.c0 IS NOT NULL) = 0

returns an empty result set, while a logically equivalent predicate:

(t0.c0 IS NOT NULL) IS FALSE

returns a row.

How to repeat:
CREATE TABLE t0(c0 INT);
CREATE TABLE t1 LIKE t0; 
CREATE UNIQUE INDEX i1 ON t0((c0 IS NOT NULL));

INSERT INTO t1(c0) VALUES(1);

SELECT t1.c0 AS ref0, t0.c0 AS ref1 FROM t1 LEFT JOIN t0 ON (t1.c0) = (t0.c0) WHERE ((t0.c0) IS NOT NULL) = 0;
/* returns empty set */

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

[6 Mar 6:40] Weipeng Wang

I also observed that this issue occurs with similar RIGHT JOIN queries.

[6 Mar 11:27] Roy Lyseng

Thank you for the bug report.
Verified as described.