Where clause on right outer join might lead to rows not being fetched

[EmilyOng]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 INT);
INSERT INTO t0 VALUES (1);
CREATE TABLE t1(c0 INT);
INSERT INTO t1(c0) VALUES (NULL), (0), (1);

SELECT
    t0.c0 AS ref0,
    t1.c0 AS ref1
FROM t0
RIGHT OUTER JOIN t1 ON t0.c0 = t1.c0
WHERE (t1.c0 AND (t0.c0 != NULL)) IS FALSE;

2. What did you expect to see? (Required)

ref0	ref1
null	0

This is also the expected result in MySQL (8.0) and MariaDB (11.5).

3. What did you see instead (Required)

no rows

Moreover, the result obtained in the above query is inconsistent with the following:

SELECT
    t0.c0 AS ref0,
    t1.c0 AS ref1,
    (t1.c0 AND (t0.c0 != NULL)) IS FALSE AS ref2
FROM t0
RIGHT OUTER JOIN t1 ON t0.c0 = t1.c0;

In this query, the result is:

ref0	ref1	ref2
null	null	0
null	0	1
1	1	0

Thus, the condition in the WHERE clause evaluates to true in the 2nd row, and the 2nd row should be returned in the original query.

4. What is your TiDB version? (Required)

                      tidb_version()                       
-----------------------------------------------------------
 Release Version: v8.5.1                                  +
 Edition: Community                                       +
 Git Commit Hash: fea86c8e35ad4a86a5e1160701f99493c2ee547c+
 Git Branch: HEAD                                         +
 UTC Build Time: 2025-01-16 07:40:33                      +
 GoVersion: go1.23.2                                      +
 Race Enabled: false                                      +
 Check Table Before Drop: false                           +
 Store: tikv 
(1 row)