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

On Tue, 5 Oct 2021 at 13:14, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> 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.

It looks like that's exactly what the problem is here. I've made a
small adjustment to your script to call analyze on the watchers table.
This does not result in the exact plan that you reported, but it does
result in a Memoize plan that shows the same bug.

I'm currently thinking over what the best fix for this should be. My
current thinking is that all parameters that are from above the
Memoize that are required below the Memoize must be part of the Cache
Key. Currently, it seems, that's not the case and in the plan from the
attached plan, i1.id is being used below the Memoize but originates
from above it.

I'll need to look into how exactly to determine around when
get_memoize_path() is called how we determine if there's any subplan
parameters that will be needed in the Memoize path's sub path that
originates above the memoize node.

David