Re: reconsider_full_join_clause fails to consider if COALESCE() arguments are NULL-preserving

On Thu, 12 Nov 2026 at 02:48, Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>
> Hi,
>
> Here's a rebased version of the patch(es). I've made no change to the
> code itself, I've tried to improve the commit message to explain why the
> expressions need to be null-preserving, handling of COALESCE clauses and
> placeholders.

Hi!

> 1) Is is_full_join_expr_null_preserving() necessary? Or is there some
existing piece of code checking the expression preserves NULLs? Possibly
with some additional smartness I haven't thought of?

I think it is. There is find_nonnullable_rels which solves similar
task, but I don't see any additional smartness in it.
There is also is_standard_join_alias_expression function but it
doesn't check for functions strictness

> 2) Is is_full_join_expr_null_preserving() correct?

I think it is.

It does lose some optimizations for safe filter pushdows, because
this doesn't recurse
+ if (IsA(node, PlaceHolderVar))
+ return ((PlaceHolderVar *) node)->phlevelsup == 0;

With v2:

CREATE TEMP TABLE a(x int);
CREATE TEMP TABLE b(x int);

EXPLAIN (COSTS OFF)
SELECT *
FROM (SELECT coalesce(x, 0) AS k FROM a) s
FULL JOIN b ON s.k + 0 = b.x
WHERE coalesce(s.k + 0, b.x) = 0;
CREATE TABLE
CREATE TABLE
QUERY PLAN
---------------------------------------------------------
Merge Full Join
Merge Cond: (((COALESCE(a.x, 0) + 0)) = b.x)
Filter: (COALESCE(((COALESCE(a.x, 0)) + 0), b.x) = 0)
-> Sort
Sort Key: ((COALESCE(a.x, 0) + 0))
-> Seq Scan on a
-> Sort
Sort Key: b.x
-> Seq Scan on b
(9 rows)

before, there is two filters

QUERY PLAN
--------------------------------------------
Merge Full Join
-> Seq Scan on a
Filter: ((COALESCE(x, 0) + 0) = 0)
-> Materialize
-> Seq Scan on b
Filter: (x = 0)
(6 rows)

Is this regression important? or can we accept this for sake of simplicity?

--
Best regards,
Kirill Reshke