Unexpected Results of IN expression With NATURAL RIGHT JOIN

[suyZhong]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 BOOLEAN);
INSERT INTO t0 (c0) VALUES (0);

SELECT * FROM t1 NATURAL RIGHT JOIN t0; -- 0
SELECT (false IN (t1.c0, t0.c0)) FROM t1 NATURAL RIGHT JOIN t0; -- 1
SELECT * FROM t1 NATURAL RIGHT JOIN t0 WHERE (false IN (t1.c0, t0.c0));
-- Expected: 0
-- Actual: Empty set

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

The third SELECT returns an empty set, which is surprising: If the result of second query is 1 (TRUE), the value of the IN expression should be 1, and thus the third query should return 0.

Not sure if the issue is related to #49108

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

Release Version: v7.6.0-alpha
Edition: Community
Git Commit Hash: 918df0ae50568ebc54bd6d89f2d9a49bc9b79b06
Git Branch: heads/refs/tags/v7.6.0-alpha
UTC Build Time: 2023-12-14 04:15:47
GoVersion: go1.21.5
Race Enabled: false
Check Table Before Drop: false
Store: unistore |

[suyZhong]

/label fuzz/sqlancer

[winoros]

This one is related to the null-rejective checking.
Cannot solve in a short time.

[ghazalfamilyusa]

As @winoros said, it is caused by null rejection logic which incorrectly converts the right outer join to inner join.
See plan below (plan 1). The equivalent query explain SELECT * FROM t1 NATURAL RIGHT JOIN t0 WHERE (false IN (t1.c0) or false in (t0.c0)) works OK (see plan 2). The null rejection logic executes incorrectly false in (0,NULL) as false since false = 0 is true. But, it does executes false in (0) or false in (null) as true.

Plan1
explain format="brief" SELECT * FROM t1 NATURAL RIGHT JOIN t0 WHERE (false IN (t1.c0, t0.c0))

+---------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------+
| HashJoin | 1.00 | root | | inner join, equal:[eq(test.t1.c0, test.t0.c0)], other cond:in(0, test.t1.c0, test.t0.c0) |
| ├─TableReader(Build) | 1.00 | root | | data:Selection |
| │ └─Selection | 1.00 | cop[tikv] | | not(isnull(test.t0.c0)) |
| │ └─TableFullScan | 1.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
| └─TableReader(Probe) | 1.00 | root | | data:Selection |
| └─Selection | 1.00 | cop[tikv] | | not(isnull(test.t1.c0)) |
| └─TableFullScan | 1.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------+

Plan2
explain format="brief" SELECT * FROM t1 NATURAL RIGHT JOIN t0 WHERE (false IN (t1.c0) or false in (t0.c0))

+-------------------------------+---------+-----------+---------------+------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+---------------+------------------------------------------------------+
| Projection | 0.80 | root | | test.t0.c0 |
| └─Selection | 0.80 | root | | or(eq(0, test.t1.c0), eq(0, test.t0.c0)) |
| └─HashJoin | 1.00 | root | | right outer join, equal:[eq(test.t1.c0, test.t0.c0)] |
| ├─TableReader(Build) | 1.00 | root | | data:Selection |
| │ └─Selection | 1.00 | cop[tikv] | | not(isnull(test.t1.c0)) |
| │ └─TableFullScan | 1.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
| └─TableReader(Probe) | 1.00 | root | | data:TableFullScan |
| └─TableFullScan | 1.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+---------------+------------------------------------------------------+