Re: Anti join confusion

Richard Guo <guofenglinux(at)gmail(dot)com> 于2025年3月3日周一 15:34写道：

> On Wed, Feb 26, 2025 at 7:09 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> > Recently, I found Greenplum implement pull-up NOT IN subquery. They have
> the below comments in their codes:
> >
> > We normalize NOT subqueries using the following axioms:
> > *
> > * val NOT IN (subq) => val <> ALL (subq)
> >
> > Richard, do you have an impression about this?
>
> I vaguely recall that Greenplum converts NOT IN to some form of join
> in certain cases, but I can't remember the details.
>

I do some research about Greenplum planner work for the NOT IN, I think the
way they do is just like the second option you said:

* We can add support in the executor to handle the NULL semantics of
NOT IN. This may require inventing a new join type.

1.
They add a new join type left anti semi join
2.
The executor code can handle the case when the inner side returns NULL
tuple.

I‘m not sure this transformation is only available for certain cases. I
will continue to research.
I may provide a POC patch based on the Greenplum way.

--
Thanks,
Tender Wang