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
Date: Sat, 16 Sep 2023 20:06:39 +0300
Message-ID: <ZQXgn6Dz2sjDnkrv@azor.tzirechnoy.ru> (raw)
In-Reply-To: <CAPxZtjGrmfOgfRnHmyaoyeKk5GGo2NCxyc9E8NL7QWg5RjqvHQ@mail.gmail.com>
References: <18114-c360a22e03a2fe2b@postgresql.org>
<CAPxZtjGrmfOgfRnHmyaoyeKk5GGo2NCxyc9E8NL7QWg5RjqvHQ@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: ilan@tzirechnoy.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
In-Reply-To: <ZQXgn6Dz2sjDnkrv@azor.tzirechnoy.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox