DuckDB incorrectly preserves rows from LEFT JOIN LATERAL ... ON TRUE even when an outer WHERE l.col IS NOT NULL filter should remove them #21609

What happens?

DuckDB appears to return or insert a row from a LEFT JOIN LATERAL even when the lateral subquery produces no match and the outer query explicitly filters on l.k IS NOT NULL.

In the repro below:

the lateral subquery has no matching row because l.k > r.k is false
therefore the joined l.k should be NULL
the outer WHERE l.k IS NOT NULL should eliminate the row
However, DuckDB still keeps the row. In the DML form, it inserts one row with lk = NULL.

Expected Result

The predicate l.k > r.k is false, so the lateral subquery should produce no row.

That means:

l.k should be NULL
WHERE l.k IS NOT NULL should filter the row out
bug_out should remain empty
Expected:

inserted_rows
0

Actual Result

DuckDB inserts one row:

inserted_rows
1

and the contents are:

id | lk   | rk
1  | NULL | 2001-01-01 00:00:00

So the row survives even though l.k IS NOT NULL is false.

To Reproduce

DROP TABLE IF EXISTS src_r;
DROP TABLE IF EXISTS src_l;
DROP TABLE IF EXISTS bug_out;

CREATE TABLE src_r(k TIMESTAMP);
CREATE TABLE src_l(k TIMESTAMP);
CREATE TABLE bug_out(
  id INTEGER,
  lk TIMESTAMP,
  rk TIMESTAMP
);

INSERT INTO src_r VALUES (TIMESTAMP '2001-01-01 00:00:00');
INSERT INTO src_l VALUES (TIMESTAMP '2001-01-01 00:00:00');

INSERT INTO bug_out
SELECT 1, l.k, r.k
FROM src_r AS r
LEFT JOIN LATERAL (
  SELECT *
  FROM src_l AS l
  WHERE l.k > r.k
) AS l ON TRUE
WHERE l.k IS NOT NULL;

SELECT COUNT(*) AS inserted_rows FROM bug_out;
SELECT * FROM bug_out;

OS:

x86_64

DuckDB Version:

Reproduced on DuckDB v1.5.0

DuckDB Client:

python

Hardware:

No response

Full Name:

LieHang Chen

Affiliation:

Student

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

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

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

No - Other reason (please specify in the issue body)