agora inbox for pgsql-committers@postgresql.org
help / color / mirror / Atom feedFrom: Richard Guo <rguo@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix nullability check for a sub-select's upper-level Vars
Date: Mon, 03 Aug 2026 06:53:58 +0000
Message-ID: <E1wqmYg-00000001SXs-0KUP@gemulon.postgresql.org> (raw)
Fix nullability check for a sub-select's upper-level Vars
When checking whether a sub-select's output columns can produce NULL,
so as to decide whether a NOT IN can be converted to an anti-join,
query_outputs_are_not_nullable() falls back on find_nonnullable_vars()
for targetlist entries that are plain Vars: if the sub-select's own
quals prove the Var non-null, the output is non-nullable. But that
test compared only varno and varattno, without checking varlevelsup.
An outer reference in the targetlist could thus be matched against a
Var of the sub-select's own range table that happens to share the same
varno and varattno, wrongly proving the output non-nullable and
allowing an invalid conversion to an anti-join, which yields wrong
answers when the outer reference is NULL.
To fix, restrict the fallback to Vars of the current query level.
Author: Rui Zhao <zhaorui126@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/CAHWVJhGuaFFRpmq4j+mcMcm_HC5QOT7LZsC9bf9b7BCBmvbfMA@mail.gmail.com
Backpatch-through: 19
Branch
------
REL_19_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/3fb599b26411a1322dec89759349ce4920e1055a
Modified Files
--------------
src/backend/optimizer/util/clauses.c | 3 ++-
src/test/regress/expected/subselect.out | 24 ++++++++++++++++++++++++
src/test/regress/sql/subselect.sql | 15 +++++++++++++++
3 files changed, 41 insertions(+), 1 deletion(-)
view thread (2+ messages)
Message-ID: <E1wqmYg-00000001SXs-0KUP@gemulon.postgresql.org>
Permalink: ../E1wqmYg-00000001SXs-0KUP@gemulon.postgresql.org/
Also on: postgresql.org/message-id/E1wqmYg-00000001SXs-0KUP@gemulon.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 using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: rguo@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix nullability check for a sub-select's upper-level Vars
In-Reply-To: <E1wqmYg-00000001SXs-0KUP@gemulon.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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox