Thread

Re: Some optimizations for COALESCE expressions during constant folding

Richard Guo <guofenglinux@gmail.com> — 2025-11-26T06:33:31Z

On Tue, Nov 25, 2025 at 9:07 PM Tender Wang <tndrwang@gmail.com> wrote:
> I took a quick look at the 0001. It seems correct to me.
> One thing I want to confirm is that if var_is_nonnullable() returns true, we can make sure that
> the Var is 100% nonnullable, no matter what kind of join reorder happens.

This is a good question.  The answer is NO: A Var that is non-nullable
in the original query tree might become nullable due to join
reordering.  For instance, consider when we transform

  A leftjoin (B leftjoin C on (Pbc)) on (Pab)

to

  (A leftjoin B on (Pab)) leftjoin C on (Pbc)

In the first form, the B Vars in Pbc are non-nullable, assuming they
are defined NOT NULL.  But in the second form they become nullable by
the A/B join.

However, this doesn't introduce correctness hazards when simplifying
expressions based on NOT NULL constraints.  For instance, if we
simplify COALESCE(b.id, 1) to just b.id based on var_is_nonnullable()
returning TRUE in the original tree, the query results remain correct
even after the transformation: if A fails to match B, both query trees
return (A, NULL, NULL).

BTW, if we do not simplify COALESCE(b.id, 1) to b.id, the above
transformation would not happen because Pbc fails the strictness
requirement.  This is what I meant in the commit message that the
change in 0001 can lead to better plans.

> I have no objections to the 0002 code logic.
> But I wonder how often users write "COALECE()  is not null" in their query.
> Before this patch, I didn't find the case in the regression test cases.

While it might be true that humans rarely write COALESCE(...) IS NULL
by hand, this pattern is likely not uncommon after view expansion,
function inlining, and ORM query generation.  Besides, this
optimization doesn't seem to cost too much, so I think the benefit
justifies the cost.

- Richard