Re: COALESCE with single argument looks like identity function

Updated patchset is attached


On 4/14/25 17:25, Maksim Milyutin wrote:
> On 4/11/25 17:00, Tom Lane wrote:
>> Maksim Milyutin<maksim.milyutin@tantorlabs.ru> writes:
>>> I've noticed that COALESCE function doesn't converge to argument
>>> expression if it is alone in argument list of COALESCE as part
>>> simplification routine for expressions in planner. This might suppress
>>> further useful transformations when non-strict ops are required from
>>> some expression like converging OUTER JOIN to INNER one with WHERE qual
>>> containing COALESCE over single column from inner side.
>> Seems like a reasonable idea --- it's probably a rare case, but the
>> check is cheap enough.  I'd add some comments though.
>
>
> Thanks for your comments.
>
>
>> Please add this to the open commitfest so we don't lose track of it.
>
>
> Done. In regression tests I've replaced all COALESCEs with single 
> argument to ones with dummy second argument to preserve coalesce calls 
> as AFAICS their usages are intentional for wrapping attributes to 
> generate PHVs above.
>
>
> Also I've noticed the issue in query (in join.sql test suite):
>
> SELECT 1 FROM group_tbl t1
>     LEFT JOIN (SELECT a c1, *COALESCE(a)* c2 FROM group_tbl t2) s ON TRUE
> GROUP BY s.c1, s.c2
>
> repeatable t2.a in GROUP BY clauses are not converged to single 
> appearance:
>
>                  QUERY PLAN
> --------------------------------------------
>  Group
>    Group Key: t2.a, *t2.a*
>    ->  Sort
>          Sort Key: t2.a, *t2.a*
>          ->  Nested Loop Left Join
>                ->  Seq Scan on group_tbl t1
>                ->  Seq Scan on group_tbl t2
>
> IMO the cause is in PHV surrounding s.c2 that differentiates its 
> internal expression with the same first grouping key.
>
-- 
Best regard,
Maksim Milyutin