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

On Tue, Jun 25, 2024 at 12:24 PM Antti Lampinen <antti(at)lampinen(dot)eu> wrote:
> On Tue, Jun 25, 2024 at 5:07 AM Richard Guo <guofenglinux(at)gmail(dot)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