From: Elvis Pranskevichus <elprans@gmail.com>
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>,
 David Rowley <dgrowleyml@gmail.com>
Subject: Re: BUG #17213: Wrong result from a query involving Merge Semi Join
 and Memoize
Date: Mon, 04 Oct 2021 17:36:09 -0700
Message-ID: <4374396.LvFx2qVVIh@vulcan.edgedb.net>
In-Reply-To: 
 <CAApHDvq-auQ9rK8VzD79X77cY4AOKP0mKp1whYjfNX8xYMEZGw@mail.gmail.com>
References: <17213-988ed34b225a2862@postgresql.org>
 <CAApHDvq-auQ9rK8VzD79X77cY4AOKP0mKp1whYjfNX8xYMEZGw@mail.gmail.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 7Bit
Content-Type: text/plain; charset="us-ascii"
Archived-At: 
 <https://www.postgresql.org/message-id/4374396.LvFx2qVVIh%40vulcan.edgedb.net>
Precedence: bulk

On Monday, October 4, 2021 5:14:47 PM PDT David Rowley wrote:
> On Tue, 5 Oct 2021 at 12:22, PG Bug reporting form
> 
> <noreply@postgresql.org> wrote:
> > It appears a combination of Merge Semi Join and Memoize in
> > PostgreSQL 14 produces incorrect results on a particular query. 
> > The bug might be present
> > in earlier versions, but I was only able to reproduce a particular
> > plan under version 14.
> 
> Are you able to send the output of EXPLAIN (ANALYZE, SETTINGS) from
> the problem query with SET enable_memoize = ON; ? My local setup here
> does not produce the same plan as you're getting and that might be
> because you have some non-standard cost settings.
> 
> It does look a bit like memoize is not properly taking into account
> the fact that there's a subplan below the memoize node with parameters
> from above the memoize node. Namely the i1.id in issue_id != i1.id.

All cost and planner settings are set to defaults on the cluster I'm 
getting this plan on (only `search_path` shows up in SETTINGS). 

It seems like a very particular set of stats is causing the plan there, 
as running ANALYZE turns the query into a Nested Loop Semi Join.

                              Elvis