public inbox for [email protected]
help / color / mirror / Atom feedFrom: Richard Guo <[email protected]>
To: David Rowley <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Tom Lane <[email protected]>
Subject: Re: BUG #19412: Wrong query result with not null constraint
Date: Wed, 18 Feb 2026 18:50:34 +0900
Message-ID: <CAMbWs49NVUNpQPwGb56SCSQL-bkjtxnkCb6Ot7dBxGndJoA3Pw@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs49kVgb_wS4VFuQpfZ+Re4tWVn7vmheR6HqKppnNuEaH-g@mail.gmail.com>
References: <[email protected]>
<CAApHDvpXarz-D6tP-7y4ZrtnhXtdbRY0kQxRDQTVc3Z9DckYVw@mail.gmail.com>
<CAMbWs49kVgb_wS4VFuQpfZ+Re4tWVn7vmheR6HqKppnNuEaH-g@mail.gmail.com>
On Wed, Feb 18, 2026 at 10:51 AM Richard Guo <[email protected]> wrote:
> Exactly. I think this is because when adjust_appendrel_attrs_mutator
> propagates the nullingrel bits from the parent rel's Var into the
> translated Var, it loses the translated Var's original bits. Instead
> of overwriting the translated Var's nullingrels, I think we should
> merge them.
>
> --- a/src/backend/optimizer/util/appendinfo.c
> +++ b/src/backend/optimizer/util/appendinfo.c
> @@ -291,8 +291,11 @@ adjust_appendrel_attrs_mutator(Node *node,
> var->varattno, get_rel_name(appinfo->parent_reloid));
> if (IsA(newnode, Var))
> {
> - ((Var *) newnode)->varreturningtype = var->varreturningtype;
> - ((Var *) newnode)->varnullingrels = var->varnullingrels;
> + Var *newvar = (Var *) newnode;
> +
> + newvar->varreturningtype = var->varreturningtype;
> + newvar->varnullingrels = bms_add_members(newvar->varnullingrels,
> + var->varnullingrels);
> }
Here is a more readable version of the patch.
- Richard
Attachments:
[application/octet-stream] v1-0001-Fix-computation-of-varnullingrels-when-translatin.patch (5.3K, 2-v1-0001-Fix-computation-of-varnullingrels-when-translatin.patch)
download | inline diff:
From 5f473566b134ca0ff46165d2c7cdd9e0259ba56c Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Wed, 18 Feb 2026 17:25:06 +0900
Subject: [PATCH v1] Fix computation of varnullingrels when translating
appendrel Var
When adjust_appendrel_attrs translates a Var referencing a parent
relation into a Var referencing a child relation, it propagates
varnullingrels from the parent Var to the translated Var. Previously,
the code simply overwrote the translated Var's varnullingrels with
those of the parent.
This was incorrect because the translated Var might already possess
nonempty varnullingrels. This happens, for example, when a LATERAL
subquery within a UNION ALL references a Var from the nullable side of
an outer join. In such cases, the translated Var correctly carries
the outer join's relid in its varnullingrels. Overwriting these bits
with the parent Var's set caused the planner to lose track of the fact
that the Var could be nulled by that outer join.
In the reported case, because the underlying column had a NOT NULL
constraint, the planner incorrectly deduced that the Var could never
be NULL and discarded essential IS NOT NULL filters. This led to
incorrect query results where NULL rows were returned instead of being
filtered out.
To fix, use bms_add_members to merge the parent Var's varnullingrels
into the translated Var's existing set, preserving both sources of
nullability.
---
src/backend/optimizer/util/appendinfo.c | 13 ++++++-
src/test/regress/expected/join.out | 51 +++++++++++++++++++++++++
src/test/regress/sql/join.sql | 24 ++++++++++++
3 files changed, 86 insertions(+), 2 deletions(-)
diff --git a/src/backend/optimizer/util/appendinfo.c b/src/backend/optimizer/util/appendinfo.c
index 689840d6564..2e7b1b202a3 100644
--- a/src/backend/optimizer/util/appendinfo.c
+++ b/src/backend/optimizer/util/appendinfo.c
@@ -291,8 +291,17 @@ adjust_appendrel_attrs_mutator(Node *node,
var->varattno, get_rel_name(appinfo->parent_reloid));
if (IsA(newnode, Var))
{
- ((Var *) newnode)->varreturningtype = var->varreturningtype;
- ((Var *) newnode)->varnullingrels = var->varnullingrels;
+ Var *newvar = (Var *) newnode;
+
+ newvar->varreturningtype = var->varreturningtype;
+
+ /*
+ * Propagate var->varnullingrels into the translated Var,
+ * merging them with any nullingrels already present in
+ * the translated Var rather than overwriting them.
+ */
+ newvar->varnullingrels = bms_add_members(newvar->varnullingrels,
+ var->varnullingrels);
}
else
{
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 63d3c5d3ac8..072a7347b81 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4486,6 +4486,57 @@ where ss.x is null;
Output: 'bar'::text
(12 rows)
+-- Test computation of varnullingrels when translating appendrel Var
+begin;
+create temp table t_append (a int not null, b int);
+insert into t_append values (1, 1);
+insert into t_append values (2, 3);
+explain (verbose, costs off)
+select t1.a, s.a from t_append t1
+ left join t_append t2 on t1.a = t2.b
+ join lateral (
+ select t1.a as a union all select t2.a as a
+ ) s on true
+where s.a is not null;
+ QUERY PLAN
+---------------------------------------------------
+ Nested Loop
+ Output: t1.a, (t1.a)
+ -> Merge Left Join
+ Output: t1.a, t2.a
+ Merge Cond: (t1.a = t2.b)
+ -> Sort
+ Output: t1.a
+ Sort Key: t1.a
+ -> Seq Scan on pg_temp.t_append t1
+ Output: t1.a
+ -> Sort
+ Output: t2.a, t2.b
+ Sort Key: t2.b
+ -> Seq Scan on pg_temp.t_append t2
+ Output: t2.a, t2.b
+ -> Append
+ -> Result
+ Output: t1.a
+ -> Result
+ Output: t2.a
+ One-Time Filter: (t2.a IS NOT NULL)
+(21 rows)
+
+select t1.a, s.a from t_append t1
+ left join t_append t2 on t1.a = t2.b
+ join lateral (
+ select t1.a as a union all select t2.a as a
+ ) s on true
+where s.a is not null;
+ a | a
+---+---
+ 1 | 1
+ 1 | 1
+ 2 | 2
+(3 rows)
+
+rollback;
--
-- test inlining of immutable functions
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 14cbec28766..4acd2512004 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1508,6 +1508,30 @@ select * from int4_tbl left join (
) ss(x) on true
where ss.x is null;
+-- Test computation of varnullingrels when translating appendrel Var
+begin;
+
+create temp table t_append (a int not null, b int);
+insert into t_append values (1, 1);
+insert into t_append values (2, 3);
+
+explain (verbose, costs off)
+select t1.a, s.a from t_append t1
+ left join t_append t2 on t1.a = t2.b
+ join lateral (
+ select t1.a as a union all select t2.a as a
+ ) s on true
+where s.a is not null;
+
+select t1.a, s.a from t_append t1
+ left join t_append t2 on t1.a = t2.b
+ join lateral (
+ select t1.a as a union all select t2.a as a
+ ) s on true
+where s.a is not null;
+
+rollback;
+
--
-- test inlining of immutable functions
--
--
2.39.5 (Apple Git-154)
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG #19412: Wrong query result with not null constraint
In-Reply-To: <CAMbWs49NVUNpQPwGb56SCSQL-bkjtxnkCb6Ot7dBxGndJoA3Pw@mail.gmail.com>
* 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