Re: BUG #18522: Wrong results with Merge Right Anti Join, inconsistent with Merge Anti Join

On Tue, Jun 25, 2024 at 10:45 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> On Tue, Jun 25, 2024 at 7:54 PM Richard Guo <guofenglinux(at)gmail(dot)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