pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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








view thread (13+ messages)  latest in thread

Message-ID: <19765-5778b18918040583@postgresql.org>
Permalink:  ../19765-5778b18918040583@postgresql.org/
Also on:    postgresql.org/message-id/19765-5778b18918040583@postgresql.org

 · 

reply

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