Unexpected result when using `LEFT JOIN`

[suyZhong]

What happens?

Consider the below test case. The third query returns an unexpected result since the expression t0.c1 < t2.c1 cannot be evaluated as true or NULL at the same time.

To Reproduce

CREATE  TABLE  t2(c1 INTEGER);
CREATE  TABLE  t0(c1 DOUBLE);
INSERT INTO t0(c1) VALUES ( 0.1);
INSERT INTO t2(c1) VALUES (2);

SELECT * FROM t2 LEFT  JOIN t0 ON true; -- 2 0.1
SELECT * FROM t2 LEFT  JOIN t0 ON true WHERE (t0.c1<t2.c1); -- 2 0.1
SELECT * FROM t2 LEFT  JOIN t0 ON true WHERE ((t0.c1<t2.c1) IS NULL);
-- Expected: empty result set
-- Actual: 2 0.1

OS:

Ubuntu 22.04

DuckDB Version:

v1.3.0-dev2262 589e10a

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