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

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
>
> The query plan is
>                             QUERY PLAN
> -------------------------------------------------------------------
>  Nested Loop Left Join  (cost=0.01..0.08 rows=1 width=12)
>    Join Filter: (a.x = b.x)
>    ->  Function Scan on unnest a  (cost=0.00..0.03 rows=1 width=8)
>          Filter: (1 = 1)
>    ->  Function Scan on unnest b  (cost=0.00..0.02 rows=2 width=4)
>
> So I think the FULL JOIN is replaced by LEFT JOIN.
>
> The following SQL statements both give me the expected result.
> 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;
>
> SELECT * FROM (VALUES (1)) AS t(id), 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;
>
> I can reproduce on PostgreSQL 16.0, 15.4 and 14.9.
>
>