Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan

Yes, I also realized it.
The result depends on the JOIN order and the comma changed the order.

On Sun, Sep 17, 2023 at 3:06 AM Ilya Anfimov <ilan(at)tzirechnoy(dot)com> wrote:
>
> On Sat, Sep 16, 2023 at 11:32:15PM +1000, W??i C??ngru?? wrote:
> > This is not a bug. The row is filtered by the WHERE clause.
> > Sorry for disturbing.
> >
> > On Sat, Sep 16, 2023, 22:36 PG Bug reporting form <noreply(at)postgresql(dot)org>
> > wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference: 18114
> > Logged by: crvv
> > Email address: crvv(dot)mail(at)gmail(dot)com
> > PostgreSQL version: 16.0
> > Operating system: Linux
> > Description:
> >
> > SELECT * FROM (VALUES (1)) AS t(id)
> > CROSS JOIN unnest('{1,2}'::int[]) AS a(x)
> > FULL JOIN unnest('{2,3}'::int[]) AS b(x) ON a.x = b.x
> > WHERE t.id = 1
> >
> > Execute this SQL, I get the result
> > id | x | x
> > ----+---+---
> > 1 | 1 |
> > 1 | 2 | 2
> >
> > My expection is
> > id | x | x
> > ----+---+---
> > 1 | 1 |
> > 1 | 2 | 2
> > | | 3
>
> Your expectation is incorrect.
> Last record has null t.id (not 1), so it was
> filtered out by WHERE.
>
>
>