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 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.

Thanks
Richard