Horton

Thread

Commits

  1. doc: PG 16 relnotes, fix PREPARE/EXECUTE wording

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

    The Post Office <noreply@postgresql.org> — 2025-03-14T08:01:15Z

    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

  2. Re: BUG #18846: Incorrect Filtering Behavior with FULL OUTER JOIN and WHERE Condition

    David G. Johnston <david.g.johnston@gmail.com> — 2025-03-14T13:48:35Z

    On Friday, March 14, 2025, PG Bug reporting form <noreply@postgresql.org>
wrote:

> 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
>

16beta1 is not a valid version to be reporting bugs against or asking for
support on.  Or to be running.

Testing against 16.6 shows the expected result.

David J.