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

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.