On Thu, 12 Nov 2026 at 02:48, Tomas Vondra <[email protected]> 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