From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: crvv.mail@gmail.com
Subject: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
Date: Sat, 16 Sep 2023 12:35:58 +0000
Message-ID: <18114-c360a22e03a2fe2b@postgresql.org> (raw)
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.
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, crvv.mail@gmail.com
Subject: Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
In-Reply-To: <18114-c360a22e03a2fe2b@postgresql.org>
* 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