On Wed, Feb 26, 2025 at 7:09 PM Tender Wang <tndrwang@gmail.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.
--
