Unexpected result when `RIGHT JOIN` with a subquery

[suyZhong]

What happens?

Consider the following test case. The query unexpectedly returns a row 2023-10-31 false. If the expression t1.c1 IS NULL is evaluated as false, then the filter should be evaluated as false and the query should return an empty result.

MySQL or PostgreSQL can return the expected results.

To Reproduce

CREATE TABLE t1 (c1 DATE);
INSERT INTO t1 (c1) VALUES ('2023-10-31');

SELECT t1.c1, (t1.c1 IS NULL)
FROM t1 RIGHT JOIN (SELECT NULL AS col0 FROM t1) AS sub0 ON true
WHERE (t1.c1 IS NULL);
-- Expected: empty
-- Actual: 2023-10-31 false

OS:

Ubuntu 22.04

DuckDB Version:

v1.3.0-dev1894 77849ba

DuckDB Client:

CLI

Hardware:

No response

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 source build

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

Not applicable - the reproduction does not require a data set

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