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

On 11/12/26 10:22, Tomas Vondra wrote:
>
> On 11/12/26 07:38, Kirill Reshke wrote:
>> 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;
>>
>
> Why would that matter? That returns 'true' for all PlaceHolderVars,
> treating them as null-preserving, i.e. allowing pushdown. Why would it
> block the optimization in the example you posted?
>

Correction. It does return true for placeholders with (phlevelsup == 0),
but that is the case of the expression at hand:

{OPEXPR :opno 551 :opfuncid 177 :opresulttype 23 :opretset false
:opcollid 0 :inputcollid 0 :args ({PLACEHOLDERVAR :phexpr {COALESCEEXPR
:coalescetype 23 :coalescecollid 0 :args ({VAR :varno 4 :varattno 1
:vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup
0 :varreturningtype 0 :varnosyn 4 :varattnosyn 1 :location -1} {CONST
:consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval
true :constisnull false :location -1 :constvalue 4 [ 0 0 0 0 0 0 0 0 ]})
:location -1} :phrels (b 4) :phnullingrels (b) :phid 1 :phlevelsup 0}
{CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4
:constbyval true :constisnull false :location -1 :constvalue 4 [ 0 0 0 0
0 0 0 0 ]}) :location -1}

because that has ":phlevelsup 0".

--
Tomas Vondra