Repository navigation
Nullable expression over non-null columns are planned as non-null-aware-joins #25024
Description
Describe the bug
As seen in the query below, we currently only take column nullability into consideration when planning joins instead of full expression nullability, in cases where we join on an expression that might introduce nulls.
To Reproduce
CREATE TABLE outer_t(id VARCHAR NOT NULL) AS VALUES ('z');
CREATE TABLE inner_t(id VARCHAR NOT NULL) AS VALUES ('a');
SELECT id
FROM outer_t
WHERE (NULLIF(id, 'z') NOT IN (SELECT id FROM inner_t)) IS NULL;Expected behavior
The query above currently returns no results, but should return a single row with the value z (verified with DuckDB).
Additional context
No response
Activity
AdamGS commentedon Sep 7, 2026
take
alamb commentedon Oct 28, 2026
When fixing this bug, I think we should make the query error rather than trying to make it work / give correct results. To make it work and have confidence it will be correct, I think we need a more general approach than patches. See here for details
raghavyadav01 commentedon Oct 29, 2026
Checked on current main (14aa185), this returns the right row now:
Same as DuckDB 1.5.6. I think this can be closed.
alamb commentedon Oct 29, 2026
Agreed -- thank you @raghavyadav01