Thread

1. 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-21T06:31:16Z

   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
   
   

2. Re: BUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data.

   Sergei Kornilov <sk@zsrv.org> — 2023-09-21T11:30:12Z

   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