pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18846: Incorrect Filtering Behavior with FULL OUTER JOIN and WHERE Condition
2+ messages / 2 participants
[nested] [flat]
* BUG #18846: Incorrect Filtering Behavior with FULL OUTER JOIN and WHERE Condition
@ 2025-03-14 08:01 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2025-03-14 08:01 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: cheng.zhi.qiang@outlook.com
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18846: Incorrect Filtering Behavior with FULL OUTER JOIN and WHERE Condition
@ 2025-03-14 13:48 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2025-03-14 13:48 UTC (permalink / raw)
To: cheng.zhi.qiang@outlook.com <cheng.zhi.qiang@outlook.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-03-14 13:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-14 08:01 BUG #18846: Incorrect Filtering Behavior with FULL OUTER JOIN and WHERE Condition PG Bug reporting form <noreply@postgresql.org>
2025-03-14 13:48 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox