Re: Query result differences between PostgreSQL 17 vs 16

On Sat, Feb 22, 2025 at 8:51 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "git bisect" fingers this commit:
>
> b262ad440edecda0b1aba81d967ab560a83acb8a is the first bad commit
> commit b262ad440edecda0b1aba81d967ab560a83acb8a
> Author: David Rowley <drowley@postgresql.org>
> Date:   Tue Jan 23 18:09:18 2024 +1300
>
>     Add better handling of redundant IS [NOT] NULL quals
>
>
> I've not looked at the code, but I suspect that it is failing
> to check varnullingrels before believing that it can trust
> the applicability of table constraints.

Hmm, we do check varnullingrels in expr_is_nonnullable().  My best
guess is that we have generated two versions of the qual 'customer.cid
IS NOT NULL': one with customer.cid marked as nullable by the left
join to customer, and one without.  The latter is dropped because of
the not null constraint on customer.cid, while the former fails to be
applied on the left join to int4_tbl j.

Thanks
Richard