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
