pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
4+ messages / 3 participants
[nested] [flat]
* BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
@ 2023-09-16 12:35 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2023-09-16 12:35 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: crvv.mail@gmail.com
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
@ 2023-09-16 13:32 Wèi Cōngruì <crvv.mail@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Wèi Cōngruì @ 2023-09-16 13:32 UTC (permalink / raw)
To: Cōngruì Wèi <crvv.mail@gmail.com>; pgsql-bugs@lists.postgresql.org
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.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
@ 2023-09-16 17:06 Ilya Anfimov <ilan@tzirechnoy.com>
parent: Wèi Cōngruì <crvv.mail@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ilya Anfimov @ 2023-09-16 17:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan
@ 2023-09-17 01:03 Wèi Cōngruì <crvv.mail@gmail.com>
parent: Ilya Anfimov <ilan@tzirechnoy.com>
0 siblings, 0 replies; 4+ messages in thread
From: Wèi Cōngruì @ 2023-09-17 01:03 UTC (permalink / raw)
To: Ilya Anfimov <ilan@tzirechnoy.com>; pgsql-bugs@lists.postgresql.org
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.
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-09-17 01:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-16 12:35 BUG #18114: FULL JOIN is replaced by LEFT JOIN in plan PG Bug reporting form <noreply@postgresql.org>
2023-09-16 13:32 ` Wèi Cōngruì <crvv.mail@gmail.com>
2023-09-16 17:06 ` Ilya Anfimov <ilan@tzirechnoy.com>
2023-09-17 01:03 ` Wèi Cōngruì <crvv.mail@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox