Re: BUG #17213: Wrong result from a query involving Merge Semi Join and Memoize

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(at)postgresql(dot)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