On Tue, Nov 25, 2025 at 9:07 PM Tender Wang <[email protected]> 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
