What happens?

ASOF join takes last non null value instead of last value. As far as I know, other databases and tools get last value regardless if it NULL or not. Expected behaviour would be adding condition, if you don't want null values:

ASOF JOIN some_table ON some_table.time <= other_table.time AND some_table.value IS NOT NULL

To Reproduce

CREATE TABLE asof_test (
	time TIMESTAMP,
	value FLOAT
);

INSERT INTO asof_test (time, value) VALUES ('2025-07-15 00:00:00', 42);
INSERT INTO asof_test (time, value) VALUES ('2025-07-15 01:00:00', null);

SELECT time_series.time, asof_test.value
FROM (VALUES ('2025-07-15 02:00:00'::TIMESTAMP)) as time_series(time)
ASOF LEFT JOIN asof_test ON asof_test.time <= time_series.time; -- returns 42 but should be null

OS:

x86_64

DuckDB Version:

1.3.1

DuckDB Client:

/

Hardware:

No response

Full Name:

Jure Vito Srovin

Affiliation:

Individual

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

I have tested with a stable release

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

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

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