BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
От | PG Bug reporting form |
---|---|
Тема | BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan |
Дата | |
Msg-id | 18114-c360a22e03a2fe2b@postgresql.org обсуждение исходный текст |
Список | pgsql-bugs |
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.
В списке pgsql-bugs по дате отправления: