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 по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: BUG #18070: Assertion failed when processing error from plpy's iterator
Следующее
От: Wèi Cōngruì
Дата:
Сообщение: Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan