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

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.

On Sun, Sep 17, 2023 at 3:06 AM Ilya Anfimov <ilan@tzirechnoy.com> wrote:

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.