Thread: BUG #18846: Incorrect Filtering Behavior with FULL OUTER JOIN and WHERE Condition

The following bug has been logged on the website:

Bug reference:      18846
Logged by:          zhiqiang cheng
Email address:      cheng.zhi.qiang@outlook.com
PostgreSQL version: 16.1
Operating system:   Ubuntu 20.04
Description:

Description:
When executing a multi-level FULL OUTER JOIN query with subqueries, the
filtering condition in the WHERE clause unexpectedly removes valid rows.
Based on the first query's output {9, NULL}, the second query should return
9, but it returns an empty result set instead.

Steps to Reproduce:
Create the test table and insert data:
CREATE TABLE t1 (
    c1 INTEGER
);
INSERT INTO t1 (c1) VALUES (9);

query1:
select  
  ref_0.c1 as c_1
from 
  (t1 as ref_0
    full outer join ((select distinct 
            ref_1.c1 as c_1
          from 
            t1 as ref_1
          ) as subq_0
      full outer join (select  
            ref_3.c1 as c_3
          from 
            (t1 as ref_2
              full outer join t1 as ref_3
              on (ref_2.c1 = ref_3.c1 ))
          where (false::bool)) as subq_1
      on (subq_0.c_1 = subq_1.c_3 ))
    on (ref_0.c1 = subq_1.c_3 ))

output:
 c_1  
------
    9
 NULL
(2 rows)

query2:
select  
  ref_0.c1 as c_1
from 
  (t1 as ref_0
    full outer join ((select distinct 
            ref_1.c1 as c_1
          from 
            t1 as ref_1
          ) as subq_0
      full outer join (select  
            ref_3.c1 as c_3
          from 
            (t1 as ref_2
              full outer join t1 as ref_3
              on (ref_2.c1 = ref_3.c1 ))
          where (false::bool)) as subq_1
      on (subq_0.c_1 = subq_1.c_3 ))
    on (ref_0.c1 = subq_1.c_3 ))
where ((ref_0.c1 ) > 0 )

output:
 c_1 
-----
(0 rows)

Expected behavior:
Since the first query produces {9, NULL}, the second query, which applies
the condition WHERE ref_0.c1 > 0, should return 9 instead of an empty result
set. 

Actual behavior:
However, the result is unexpectedly empty.

Postgres version:
Github commit: 3f1aaaa180689f2015e7f7bd01c9be6d7a993b42
Version:  PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit

Operating system:
Linux ubuntu 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16
UTC 2025 x86_64 x86_64 x86_64 GNU/Linux

The following bug has been logged on the website:

Bug reference:      18846
Logged by:          zhiqiang cheng
Email address:      cheng.zhi.qiang@outlook.com
PostgreSQL version: 16.1
Operating system:   Ubuntu 20.04
Description:       

Postgres version:
Github commit: 3f1aaaa180689f2015e7f7bd01c9be6d7a993b42
Version:  PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit