Horton

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

David G. Johnston <david.g.johnston@gmail.com>

View thread View on postgr.es
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Benoit Ryder <b.ryder@ateme.com>, "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Date: 2024-04-12T15:57:58Z
Lists: pgsql-bugs 
On Fri, Apr 12, 2024 at 8:50 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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).
>
>
Q1 is trivially fixed by specifying "WITH c2 AS MATERIALIZED ( ... )", No?
Which is why it isn't broken in 9.4 and any of the other versions where we
materialized CTEs by default.

David J.

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.