Re: Support "Right Semi Join" plan shapes

Thank you for reviewing.

On Mon, Jun 24, 2024 at 1:27 PM Li Japin <japinli@hotmail.com> wrote:
> +       /*
> +        * For now we do not support RIGHT_SEMI join in mergejoin or nestloop
> +        * join.
> +        */
> +       if (jointype == JOIN_RIGHT_SEMI)
> +               return;
> +
>
> How about adding some reasons here?

I've included a brief explanation in select_mergejoin_clauses.

> + * this is a right-semi join, or this is a right/right-anti/full join and
> + * there are nonmergejoinable join clauses.  The executor's mergejoin
>
> Maybe we can put the right-semi join together with the right/right-anti/full
> join.  Is there any other significance by putting it separately?

I don't think so.  The logic is different: for right-semi join we will
always set *mergejoin_allowed to false, while for right/right-anti/full
join it is set to false only if there are nonmergejoinable join clauses.

> Maybe the following comments also should be updated. Right?

Correct.  And there are a few more places where we need to mention
JOIN_RIGHT_SEMI, such as in reduce_outer_joins_pass2 and in the comment
for SpecialJoinInfo.


I noticed that this patch changes the plan of a query in join.sql from
a semi join to right semi join, compromising the original purpose of
this query, which was to test the fix for neqjoinsel's behavior for
semijoins (see commit 7ca25b7d).

--
-- semijoin selectivity for <>
--
explain (costs off)
select * from int4_tbl i4, tenk1 a
where exists(select * from tenk1 b
             where a.twothousand = b.twothousand and a.fivethous <> b.fivethous)
      and i4.f1 = a.tenthous;

So I've changed this test case a bit so that it is still testing what it
is supposed to test.

In passing, I've also updated the commit message to clarify that this
patch does not address the support of "Right Semi Join" for merge joins.

Thanks
Richard