agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19707: Reproduction of BUG #19553 on PostgreSQL 18.4: nested LEFT JOIN returns a constant instead of NULL
2+ messages / 2 participants
[nested] [flat]
* BUG #19707: Reproduction of BUG #19553 on PostgreSQL 18.4: nested LEFT JOIN returns a constant instead of NULL
@ 2026-09-20 11:33 PG Bug reporting form <noreply@postgresql.org>
2026-09-20 14:07 ` Re: BUG #19707: Reproduction of BUG #19553 on PostgreSQL 18.4: nested LEFT JOIN returns a constant instead of NULL David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-09-20 11:33 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 1482694023@qq.com
The following bug has been logged on the website:
Bug reference: 19707
Logged by: N J
Email address: 1482694023@qq.com
PostgreSQL version: 18.4
Operating system: Windows 11 64-bit
Description:
Environment:
PostgreSQL: 18.4
Client: pgAdmin 4
Operating system: Windows 11 64-bit
The following query returns a constant from the nullable side of a LEFT
JOIN,
although the corresponding subquery is guaranteed to be empty.
Reproduction query:
SELECT
input_rows.sample_id,
nullable_side.payload
FROM (VALUES (11), (22)) AS input_rows(sample_id)
LEFT JOIN (
SELECT payload
FROM (
SELECT 37 AS payload
FROM (SELECT WHERE FALSE) AS guaranteed_empty
) AS projected_empty
LEFT JOIN (
SELECT 99 AS auxiliary_value
) AS one_row_helper
ON TRUE
) AS nullable_side
ON TRUE;
Observed result on PostgreSQL 18.4:
sample_id | payload
-----------+---------
11 | 37
22 | 37
Expected result:
sample_id | payload
-----------+---------
11 | NULL
22 | NULL
The guaranteed_empty subquery cannot produce any rows. Therefore, the
right-hand side of the outer LEFT JOIN is empty and payload should be
NULL-extended for both input rows.
Instead, the constant value 37 is emitted for both rows. EXPLAIN (VERBOSE)
may show that the right-hand side has been optimized away while the constant
is retained in the output expression.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19707: Reproduction of BUG #19553 on PostgreSQL 18.4: nested LEFT JOIN returns a constant instead of NULL
2026-09-20 11:33 BUG #19707: Reproduction of BUG #19553 on PostgreSQL 18.4: nested LEFT JOIN returns a constant instead of NULL PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-20 14:07 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2026-09-20 14:07 UTC (permalink / raw)
To: 1482694023@qq.com <1482694023@qq.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sunday, September 20, 2026, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19707
> Logged by: N J
> Email address: 1482694023@qq.com
> PostgreSQL version: 18.4
> Operating system: Windows 11 64-bit
> Description:
>
> Environment:
> PostgreSQL: 18.4
> Client: pgAdmin 4
> Operating system: Windows 11 64-bit
It’s usually not that helpful to report problems against
obsolete/unsupported versions. The supported 18.6 release notes has an
entry that appears to cover this. You need to upgrade.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-20 14:07 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-20 11:33 BUG #19707: Reproduction of BUG #19553 on PostgreSQL 18.4: nested LEFT JOIN returns a constant instead of NULL PG Bug reporting form <noreply@postgresql.org>
2026-09-20 14:07 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox