From: | Wèi Cōngruì <crvv(dot)mail(at)gmail(dot)com> |
---|---|
To: | Cōngruì Wèi <crvv(dot)mail(at)gmail(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan |
Date: | 2023-09-16 13:32:15 |
Message-ID: | CAPxZtjGrmfOgfRnHmyaoyeKk5GGo2NCxyc9E8NL7QWg5RjqvHQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(at)postgresql(dot)org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18114
> Logged by: crvv
> Email address: crvv(dot)mail(at)gmail(dot)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.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-09-16 14:34:45 | Re: BUG #18070: Assertion failed when processing error from plpy's iterator |
Previous Message | PG Bug reporting form | 2023-09-16 12:35:58 | BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan |