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

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.