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(dot)milyutin(at)tantorlabs(dot)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