MySQL Bugs: #119992: Incorrect result for LEFT/RIGHT JOIN when using IS TRUE / IS FALSE expressions on the right table with NULL padding.

Bug #119992	Incorrect result for LEFT/RIGHT JOIN when using IS TRUE / IS FALSE expressions on the right table with NULL padding.
Submitted:	6 Mar 6:23	Modified:	9 Mar 8:07
Reporter:	Weipeng Wang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.45	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
When a LEFT JOIN involves an expression on the right table that uses IS TRUE or IS FALSE combined with an implicit numeric comparison, MySQL sometimes incorrectly filters rows that should be preserved due to NULL padding.

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

CREATE INDEX i0 ON t1(((t1.c0) IS NOT TRUE));

INSERT INTO t0(c0) VALUES (1);

/* Query 1: basic LEFT JOIN */
SELECT t0.c0 AS ref1, t1.c0 AS ref2 FROM t0 LEFT JOIN t1 ON (t0.c0) <= (t1.c0);
/* Expected & actual 1 row: (1, NULL) */

/* Query 2: LEFT JOIN with IS TRUE / IS FALSE on t1.c0 */
SELECT t0.c0 AS ref1, t1.c0 AS ref2 FROM t0 LEFT JOIN t1 ON (t0.c0) <= (t1.c0) WHERE (((t1.c0) IS TRUE) IS FALSE) >= 0;
/* Actual empty set; Expected: same 1 row as Query 1 */

This issue is similar to Bug #119991, but uses a functional index with IS NOT TRUE.

Thank you for the bug report.
However, I think this is the same underlying issue as bug#119991, so I am closing this as a duplicate report.