Thread
-
BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
The Post Office <noreply@postgresql.org> — 2023-09-16T12:35:58Z
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. -
Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
Wèi Cōngruì <crvv.mail@gmail.com> — 2023-09-16T13:32:15Z
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 > > 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. > > -
Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
Ilya Anfimov <ilan@tzirechnoy.com> — 2023-09-16T17:06:39Z
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. -
Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
Wèi Cōngruì <crvv.mail@gmail.com> — 2023-09-17T01:03:21Z
Yes, I also realized it. The result depends on the JOIN order and the comma changed the order. 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. > > >