Date: Sat, 16 Sep 2023 20:06:39 +0300
From: Ilya Anfimov <ilan@tzirechnoy.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
Message-ID: <ZQXgn6Dz2sjDnkrv@azor.tzirechnoy.ru>
Mail-Followup-To: Ilya Anfimov <ilan@tzirechnoy.com>,
	pgsql-bugs@lists.postgresql.org
References: <18114-c360a22e03a2fe2b@postgresql.org>
 <CAPxZtjGrmfOgfRnHmyaoyeKk5GGo2NCxyc9E8NL7QWg5RjqvHQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAPxZtjGrmfOgfRnHmyaoyeKk5GGo2NCxyc9E8NL7QWg5RjqvHQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZQXgn6Dz2sjDnkrv%40azor.tzirechnoy.ru>
Precedence: bulk

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.