Horton

Re: BUG #18429: Inconsistent results on similar queries with join lateral

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
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>
Date: 2024-04-12T15:50:02Z
Lists: pgsql-bugs 
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

Commits

  1. Ensure generated join clauses for child rels have correct relids.

  2. Fix generation of EC join conditions at the wrong plan level.

  3. Make Vars be outer-join-aware.