pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data.
2+ messages / 2 participants
[nested] [flat]
* BUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data.
@ 2023-09-21 06:31 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2023-09-21 06:31 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: dafoer_x@163.com
The following bug has been logged on the website:
Bug reference: 18123
Logged by: The execution plan for the left join is incorrect, returning incorrect rows
of data.
Email address: dafoer_x@163.com
PostgreSQL version: 14.0
Operating system: centos_x86
Description:
create table tb11(a int, b int);
insert into tb11 values(1,1);
select count(1) from tb11 a1 where a1.a =1;
select count(1) from tb11 a1 left join tb11 a2 on a1.a=a2.a where a1.a=1 and
a2.a=3;
postgres=# select count(1) from tb11 a1 where a1.a =1;
count
-------
1
(1 row)
postgres=# select count(1) from tb11 a1 left join tb11 a2 on a1.a=a2.a where
a1.a=1 and a2.a=3;
count
-------
0
(1 row)
postgres=# explain select count(1) from tb11 a1 left join tb11 a2 on
a1.a=a2.a where a1.a=1 and a2.a=3;
QUERY PLAN
------------------------------------------------
Aggregate (cost=0.00..0.01 rows=1 width=8)
-> Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(3 rows)
In the left join scenario, the conclusion of 'result = false' cannot be
obtained.
Could you please help analyze this issue?
Thank you.
dafoer
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data.
@ 2023-09-21 11:30 Sergei Kornilov <sk@zsrv.org>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Sergei Kornilov @ 2023-09-21 11:30 UTC (permalink / raw)
To: dafoer_x@163.com; pgsql-bugs@lists.postgresql.org
Hello
Usual query error, not a bug.
"where a2.a=3" turns left join to inner join because a2.a can not be null here.
This way the conditions contradict each other and it is possible to immediately get an empty set as a result without actually executing the query.
regards, Sergei
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-09-21 11:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-21 06:31 BUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data. PG Bug reporting form <noreply@postgresql.org>
2023-09-21 11:30 ` Sergei Kornilov <sk@zsrv.org>
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