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: 
 <CAMbWs48-_meMqqbkFQoVEEKBYYsanMvRk3zCqmVr23gyxkNhag@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>
 <3818495.1740191559@sss.pgh.pa.us>
 <CAMbWs4_BCC8+4PqJi2NcO5WTu5vsJPMh7iAe7e=nXNVTZNp+LQ@mail.gmail.com>
 <CAMbWs49rShAwK0sMz+-6d9hGd1RsRQd0tBuVUGvh88pNu9hG1w@mail.gmail.com>
 <1716556.1740551169@sss.pgh.pa.us>
 <CAMbWs4_SqFDZ1Mnp0Kmy-H2s7_-PdeuXva+kBG-jKSampoDHnw@mail.gmail.com>
 <CAMbWs48-_meMqqbkFQoVEEKBYYsanMvRk3zCqmVr23gyxkNhag@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Wed, 26 Feb 2025 17:46:27 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2337382.1740630494.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 26 Feb 2025 23:28:14 -0500
Message-ID: <2337383.1740630494@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2337383.1740630494%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
>> On Wed, Feb 26, 2025 at 3:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Thanks for that.  The code and comment added to
>>> restriction_is_always_true look good, but I can't help wondering
>>> whether we don't need the same in restriction_is_always_false.

> After some attempts, I failed to find such a query.  An IS NULL qual
> is not strict, and it makes the outer join not commutable with other
> joins per outer-join identity 3.  We could add another strict qual to
> the join clauses to make the outer join commutable.  However, in this
> case whether we reduce the IS NULL qual to a constant false would not
> affect the join outputs, due to the strict qual.

> I'm starting to believe that reducing an IS NULL clone clause to a
> constant false should be safe.  Any thoughts?

Not sure.  I think the risk factor here is fooling the clone-clause
mechanisms by failing to install some clone of an original outer
join clause in a rel's restriction list.  Looking at the callers
of restriction_is_always_false:

add_base_clause_to_rel
add_join_clause_to_rels

	These two replace the clause with a Const "false" that is
	marked with the same rinfo_serial.  Therefore the expectation
	that every group of clones will have a representative is
	still satisfied, even though the representative doesn't
	contain quite what you might expect.

apply_child_basequals

	This just summarily rejects the child rel as not needing
	to be built.  I think that might be okay, since we don't
	really make any further deductions with an appendrel
	child's quals.

I've not actually worked through the bug case to verify my hypothesis
that it's the lack of a representative for a clone group that is
breaking things.  (Maybe you did?)  But if that is the direct
cause of the failure, then it's plausible that none of these
callers are subject to it.

I'm a bit inclined to argue that we should put in the no-clone-quals
limitation anyway, to protect future callers that might not manage
to dodge the problem.  OTOH, if we get rid of these functions
altogether as I expressed my desire to do nearby, it's all moot.

			regards, tom lane