From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: dafoer_x@163.com
Subject: BUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data.
Date: Thu, 21 Sep 2023 06:31:16 +0000
Message-ID: <18123-8604341c777ccee6@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, dafoer_x@163.com
Subject: Re: BUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data.
In-Reply-To: <18123-8604341c777ccee6@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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