Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan - Mailing list pgsql-bugs

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@tzirechnoy.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@postgresql.org>
> >    wrote:
> >
> >      The following bug has been logged on the website:
> >
> >      Bug reference:      18114
> >      Logged by:          crvv
> >      Email address:      crvv.mail@gmail.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.
>
>
>