From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: leis@in.tum.de
Subject: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
Date: Fri, 16 Oct 2026 05:35:05 +0000
Message-ID: <19765-5778b18918040583@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19765
Logged by: Viktor Leis
Email address: leis@in.tum.de
PostgreSQL version: 19rc1
Operating system: Ubuntu 26.04.1
Description:
Hi,
The following self-contained query returns a wrong answer on every release
since PostgreSQL 16 (verified on 16.0, 17.9, 18.6 and current master at
3c157ad1167; PostgreSQL 15.0 and 14.7 return the correct answer):
select a.a, b.b, c.c, d.d from
(values (1),(2)) as a(a)
left join (values (1),(2)) as b(b) on a.a = b.b
left join (values (7),(2)) as c(c) on b.b = c.c
left join (values (1),(2)) as d(d) on c.c = d.d
where a.a = 1;
a | b | c | d
---+---+---+---
1 | 1 | | 1
(1 row)
c.c is correctly null-extended: b.b is 1 and c contains only 7 and 2, so
the third join has no match. But then "c.c = d.d" is NULL = d.d, which is
never true, so d.d must be NULL as well:
a | b | c | d
---+---+---+---
1 | 1 | |
(1 row)
Ordinary tables behave the same way; the VALUES lists are only for brevity.
EXPLAIN shows that all three join conditions have disappeared, leaving
three unqualified nested-loop left joins:
Nested Loop Left Join
-> Values Scan on "*VALUES*"
Filter: (column1 = 1)
-> Nested Loop Left Join
-> Nested Loop Left Join
-> Values Scan on "*VALUES*_1"
Filter: (column1 = 1)
-> Values Scan on "*VALUES*_2"
Filter: (column1 = 1)
-> Values Scan on "*VALUES*_3"
Filter: (column1 = 1)
Best regards,
Viktor Leis
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, leis@in.tum.de
Subject: Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
In-Reply-To: <19765-5778b18918040583@postgresql.org>
References: <19765-5778b18918040583@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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