Unexpected Result When Using `IS DISTINCT FROM` and `CASE WHEN` in a JOIN clause

[suyZhong]

What happens?

The query involving the IS DISTINCT FROM operator with a NATURAL RIGHT JOIN returns an empty table when it should return one row. The expected behavior is that the WHERE condition would evaluate to TRUE (as the second query) and thus include the joined row in the result set.

To Reproduce

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

SELECT * FROM t0 NATURAL RIGHT JOIN t1; -- 0 1
SELECT ((CASE t0.c0 WHEN t0.c0 THEN 1 ELSE NULL END )IS DISTINCT FROM((1+(CASE t1.c1 WHEN t1.c1 THEN 2 ELSE NULL END )))) FROM t0 NATURAL RIGHT JOIN t1; -- true
SELECT * FROM t0 NATURAL RIGHT JOIN t1 WHERE ((CASE t0.c0 WHEN t0.c0 THEN 1 ELSE NULL END )IS DISTINCT FROM((1+(CASE t1.c1 WHEN t1.c1 THEN 2 ELSE NULL END ))));
-- Expected: 0 1
-- Actual: empty table

OS:

ubuntu 22.04

DuckDB Version:

v0.10.3-dev1500 202ebd8

DuckDB Client:

CLI

Full Name:

Suyang Zhong

Affiliation:

NUS

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a nightly build

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have