Обсуждение: BUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data.

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

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
actuallyexecuting the query.
 

regards, Sergei