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 12:24 PM Antti Lampinen <antti@lampinen.eu> wrote:
> On Tue, Jun 25, 2024 at 5:07 AM Richard Guo <guofenglinux@gmail.com> wrote:
> > Could you please provide the schema and necessary data for the two
> > tables to reproduce this bug?  If there is a self-contained repro, that
> > would be great.

> I managed to create a self-contained repro:
> https://gist.github.com/arlampin/0b86963694a936147383f3af3c83224c
>
> This gives me consistently different results based on superfluous condition
> change. See the two EXPLAIN queries in the sample.

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.

Thanks
Richard