Re: Query result differences between PostgreSQL 17 vs 16

Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> On Sat, Feb 22, 2025 at 8:51 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

If the check against table not-null constraints is applied after we
clone outer-join quals, that's probably bad. I think there are
assumptions in there that every clone qual will have doppelgangers,
so filtering NOT NULLs later would break that. Maybe not applying
the filter to quals marked has_clone or is_clone would help?

regards, tom lane