Re: BUG #17700: An assert failed in prepjointree.c

On Tue, Nov 29, 2022 at 10:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Richard Guo <guofenglinux@gmail.com> writes:
> > BTW, for the test case
>
> > +explain (verbose, costs off)
> > +with ctetable as not materialized ( select 1 as f1 )
> > +select * from ctetable c1
> > +where f1 in ( select c3.f1 from ctetable c2 full join ctetable c3 on
> true
> > );
>
> > Actually we just need to keep 'c3' in a join's nullable side to have the
> > PHV created.  So we don't have to use full join in the subquery.  A left
> > join would do.
>
> Actually, the planner reduces the full join to left join anyway;
> if it did not, it wouldn't be able to reach the code in question.
> I think this formulation is fine because it tests that step along
> with the bug proper.


Hmm, I see your point.  You're right.  'c2 fulljoin c3' would be reduced
to 'c3 leftjoin c2'.

BTW, I wonder if we need the JOIN_RIGHT case here since JOIN_RIGHT
should have been flipped around to become JOIN_LEFT.

Thanks
Richard