Unexpected Result with NATURAL RIGHT JOIN and Bitwise NOT

[suyZhong]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 INT);
CREATE VIEW v0(c0) AS SELECT (0) FROM t0;
INSERT INTO t0 (c0) VALUES (1);

SELECT * FROM t0 NATURAL RIGHT  JOIN v0; -- 0
SELECT ((~ (v0.c0 AND NULL))-(CASE 0 WHEN t0.c0 THEN 0 ELSE 1 END )) FROM t0 NATURAL RIGHT  JOIN v0 ; -- 18446744073709551614
SELECT * FROM t0 NATURAL RIGHT  JOIN v0 WHERE ((~ (v0.c0 AND NULL))-(CASE 0 WHEN t0.c0 THEN 0 ELSE 1 END ));
-- Expected: 0
-- Actual: empty set

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

The query should return a row with value 0, since the expression ((~ (v0.c0 AND NULL)) - (CASE 0 WHEN t0.c0 THEN 0 ELSE 1 END)) is not evaluated to 0 or NULL, which should not filter out the row in the WHERE clause. MySQL 5.7 and 8.0 could return 0.

3. What did you see instead (Required)

Empty table

4. What is your TiDB version? (Required)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.2.0-alpha-218-g22dc064
Edition: Community
Git Commit Hash: 22dc064694aa6047696914bb40f271c3e164b46f
Git Branch: HEAD
UTC Build Time: 2024-05-23 05:29:43
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[suyZhong]

/label fuzz/sqlancer

[ghazalfamilyusa]

Seems to be another incorrect outer to inner join conversion involving case expression. ((~ ('0' AND NULL))-(CASE 0 WHEN t0.c0 THEN 0 ELSE 1 END )) is TRUE if t0.c0 is substituted with '0' It is false if t0.c0 is substituted with '1' and leads to incorrect nullability check.