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


Reply via email to