Re: BUG #18522: Wrong results with Merge Right Anti Join, inconsistent with Merge Anti Join - Mailing list pgsql-bugs

On Tue, Jun 25, 2024 at 10:45 PM Richard Guo <guofenglinux@gmail.com> wrote:
> On Tue, Jun 25, 2024 at 7:54 PM Richard Guo <guofenglinux@gmail.com> wrote:
> > Thank you so much for the repro script.  I've found the root cause:
> > for an inner_unique join we assume that the executor will stop scanning
> > for matches after the first match.  Therefore, we set skip_mark_restore
> > to true to indicate that we can skip mark/restore overhead.  However,
> > merge right anti join does not get this memo and continues scanning the
> > inner side for matches after the first match, totally ignoring the
> > single_match flag, while still thinking that it can skip mark/restore.
> >
> > Will fix this later.
>
> I think we can fix this issue by ensuring that merge-right-anti-join
> also advances to next outer tuple after the first match in inner_unique
> cases.  This can also help save cycles by avoiding unnecessary scanning
> of inner tuples after the first match.
>
> Although hash-right-anti-join does not suffer from this wrong results
> issue, I think we can apply the same change to it as well, to help save
> cycles for the same reason.
>
> Please see attached patch for the fix.  This patch still lacks a test
> case.  I'll try to see if I can come up with one.

I've added a test case in the v2 patch.  I'll park it in the July
commitfest.

Thanks
Richard