From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: Bruce Momjian <bruce@momjian.us>, David Rowley <dgrowleyml@gmail.com>,
        Ronald Cruz <cruz@rentec.com>, pgsql-bugs@lists.postgresql.org,
        Peter Ford <pford@rentec.com>, "Aaron J. Garcia" <agarcia@rentec.com>
Subject: Re: Query result differences between PostgreSQL 17 vs 16
In-reply-to: 
 <CAMbWs4_A=DFhTHQvbtCXpzs7OBGb0HcNX-yDsf01chVP60D9EQ@mail.gmail.com>
References: <f5320d3d-77af-4ce8-b9c3-4715ff33f213@rentec.com>
 <Z7kGplh70wbeX9su@momjian.us> <3736132.1740181858@sss.pgh.pa.us>
 <CAMbWs4_A=DFhTHQvbtCXpzs7OBGb0HcNX-yDsf01chVP60D9EQ@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Sat, 22 Feb 2025 10:01:13 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <3818494.1740191559.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Fri, 21 Feb 2025 21:32:39 -0500
Message-ID: <3818495.1740191559@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3818495.1740191559%40sss.pgh.pa.us>
Precedence: bulk

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