From: Tom Lane <tgl@sss.pgh.pa.us>
To: Benoit Ryder <b.ryder@ateme.com>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18429: Inconsistent results on similar queries with join
 lateral
In-reply-to: 
 <MR1P264MB1971C01DA74122691EBBEE9FF9042@MR1P264MB1971.FRAP264.PROD.OUTLOOK.COM>
References: <18429-8982d4a348cc86c6@postgresql.org>
 <CAKFQuwaTm7gWL7S3KVFJCit92-JSFBoQ3Ax-msU+5P5YAw=C2w@mail.gmail.com>
 <441100.1712933417@sss.pgh.pa.us>
 <MR1P264MB1971C01DA74122691EBBEE9FF9042@MR1P264MB1971.FRAP264.PROD.OUTLOOK.COM>
Comments: In-reply-to Benoit Ryder <b.ryder@ateme.com>
	message dated "Fri, 12 Apr 2024 15:33:41 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <450116.1712937002.1@sss.pgh.pa.us>
Date: Fri, 12 Apr 2024 11:50:02 -0400
Message-ID: <450117.1712937002@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/450117.1712937002%40sss.pgh.pa.us>
Precedence: bulk

Benoit Ryder <b.ryder@ateme.com> writes:
> Would you know a way to avoid this bug? I could probably tweak the query
> until the result is looking good, but I would prefer to make sure the
> bug won't reappear if the query is used with different data or context.

I'm not seeing a bulletproof way offhand, other than "update to v16".
Disabling nestloop plans fixes your Q4 in the back branches, but not
Q1 (because there's no opportunity for a hash or merge join in Q1).

It looks like the problem is that the old join_clause_is_movable
logic is incorrectly deciding that the WHERE condition can be
pushed down to the sub-select relation.  So we should be able to
fix it there, but I'm not sure how messy that will be or whether
we'll lose the ability to generate some correct plans.  It's hard
to justify putting a huge amount of work into old branches though.

			regards, tom lane