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
