public inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Andrei Lepikhov <lepihov@gmail.com>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Tender Wang <tndrwang@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>
Cc: Fujii Masao <masao.fujii@gmail.com>
Cc: ammmkilo@163.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
Date: Wed, 18 Mar 2026 14:31:00 +0100
Message-ID: <a498f5b8-2f17-4ee0-b021-63ff9829b45b@gmail.com> (raw)
In-Reply-To: <CAPpHfduTWFCHaK8U7bDfYid5pjVA=FHG1b0nTEMFqFKHebGJxQ@mail.gmail.com>
References: <19435-3cc1a87f291129f1@postgresql.org>
<CAHGQGwEEHFnH8DsZbwxdZeiHozm2LCRTLSgL8Qn=5MoN4450ZA@mail.gmail.com>
<CAPpHfdsDQhDqvqi+-UxvyYRP72ASkdWzCn43e2Hj6WsY15opvA@mail.gmail.com>
<CAHewXNmU3E0oXLgvoOtXw7kfTiAZsKu8La_hQ=pyScgxvP45iw@mail.gmail.com>
<CAHewXN=LjuWz3PcyhjdbJAyo+Zs9MisPDRYnSZBUy4PMeKi+zA@mail.gmail.com>
<CALdSSPj1kTTQvmV3H3HMf5P3um8ybxoH3DaTPm+XgdYAur1Q4A@mail.gmail.com>
<CAHewXNndByMu3S+_h4LLDkXA5qrO1s=s-CE8HqUtc9vTA9yrjg@mail.gmail.com>
<CAPpHfdv6gzSTXHJxYSgB8sULadXM4wvhgoQODaOxYCJfagKNPw@mail.gmail.com>
<CAHewXN=7kDJjUcgEm+6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg@mail.gmail.com>
<CAHewXNm5OOREJ8wZ1cLJdQz7O1aQ0E1RBB55S6O138K8vBdc9g@mail.gmail.com>
<CAPpHfducqLJ=o3LkoPKGfZJVQuuei+P=2oUF6hX6rzHTZSxoyA@mail.gmail.com>
<a78fe5d4-e6b8-4b3c-9cfd-135edbb68e4c@gmail.com>
<CAPpHfduTWFCHaK8U7bDfYid5pjVA=FHG1b0nTEMFqFKHebGJxQ@mail.gmail.com>
On 18/3/26 13:21, Alexander Korotkov wrote:
> On Wed, Mar 18, 2026 at 2:18 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
>> What is the reason for the second change? Tender, can you show us how to
>> reproduce the issue so we can support your update to
>> restrict_infos_logically_equal? If we include it, we should add a test.
>
> I think Tender already shown this in [1]. The same qual is present
> twice in the plan.
Got it. I suggest making this a separate commit to keep the history
clear. Let me share a draft with a test case for the bug fix first.
--
regards, Andrei Lepikhov,
pgEdge
From 86e65e16c76fe404cb5a16dd0d89a583da9b62e6 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Wed, 18 Mar 2026 14:23:46 +0100
Subject: [PATCH] Fix self-join removal to update bare Var references in join
clauses
Self-join removal failed to update Var nodes when the join clause was a
bare Var (e.g., ON t1.bool_col) rather than an expression containing
Vars. ChangeVarNodesWalkExpression() used expression_tree_walker(),
which descends into child nodes but does not process the top-level node
itself. When a bare Var referencing the removed relation appeared as
the clause, its varno was left unchanged, leading to "no relation entry
for relid N" errors.
Fix by calling ChangeVarNodes_walker() directly instead of
expression_tree_walker(), so the top-level node is also processed.
Bug: #19435
Author: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/19435-3cc1a87f291129f1%40postgresql.org
---
src/backend/rewrite/rewriteManip.c | 4 +---
src/test/regress/expected/join.out | 16 ++++++++++++++++
src/test/regress/sql/join.sql | 6 ++++++
3 files changed, 23 insertions(+), 3 deletions(-)
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index fe89754a73c..610edf10284 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -744,9 +744,7 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
{
- return expression_tree_walker(node,
- ChangeVarNodes_walker,
- (void *) context);
+ return ChangeVarNodes_walker(node, context);
}
/*
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index ea3dabff77c..bf758f881ac 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8091,6 +8091,22 @@ WHERE q0.a = 1;
-> Seq Scan on sj n1
(7 rows)
+-- BUG #19435 Do not forget to replace relid in bare Var join clause
+ALTER TABLE sl ADD COLUMN cool_bool boolean;
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
+ ON sl2.cool_bool LEFT JOIN sl AS sl4 ON sl2.cool_bool;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ -> Nested Loop Left Join
+ Join Filter: sl3.cool_bool
+ -> Seq Scan on sl sl3
+ Filter: (cool_bool AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL) AND (cool_bool IS NOT NULL))
+ -> Seq Scan on sl sl4
+(7 rows)
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index ad90c326c00..faf07f7c4ab 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3156,6 +3156,12 @@ SELECT * FROM
(SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a) q0, sl
WHERE q0.a = 1;
+-- BUG #19435 Do not forget to replace relid in bare Var join clause
+ALTER TABLE sl ADD COLUMN cool_bool boolean;
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
+ ON sl2.cool_bool LEFT JOIN sl AS sl4 ON sl2.cool_bool;
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
--
2.51.0
Attachments:
[text/plain] 0001-Fix-self-join-removal-to-update-bare-Var-references-.patch (3.9K, 2-0001-Fix-self-join-removal-to-update-bare-Var-references-.patch)
download | inline diff:
From 86e65e16c76fe404cb5a16dd0d89a583da9b62e6 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Wed, 18 Mar 2026 14:23:46 +0100
Subject: [PATCH] Fix self-join removal to update bare Var references in join
clauses
Self-join removal failed to update Var nodes when the join clause was a
bare Var (e.g., ON t1.bool_col) rather than an expression containing
Vars. ChangeVarNodesWalkExpression() used expression_tree_walker(),
which descends into child nodes but does not process the top-level node
itself. When a bare Var referencing the removed relation appeared as
the clause, its varno was left unchanged, leading to "no relation entry
for relid N" errors.
Fix by calling ChangeVarNodes_walker() directly instead of
expression_tree_walker(), so the top-level node is also processed.
Bug: #19435
Author: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/19435-3cc1a87f291129f1%40postgresql.org
---
src/backend/rewrite/rewriteManip.c | 4 +---
src/test/regress/expected/join.out | 16 ++++++++++++++++
src/test/regress/sql/join.sql | 6 ++++++
3 files changed, 23 insertions(+), 3 deletions(-)
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index fe89754a73c..610edf10284 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -744,9 +744,7 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
{
- return expression_tree_walker(node,
- ChangeVarNodes_walker,
- (void *) context);
+ return ChangeVarNodes_walker(node, context);
}
/*
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index ea3dabff77c..bf758f881ac 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8091,6 +8091,22 @@ WHERE q0.a = 1;
-> Seq Scan on sj n1
(7 rows)
+-- BUG #19435 Do not forget to replace relid in bare Var join clause
+ALTER TABLE sl ADD COLUMN cool_bool boolean;
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
+ ON sl2.cool_bool LEFT JOIN sl AS sl4 ON sl2.cool_bool;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ -> Nested Loop Left Join
+ Join Filter: sl3.cool_bool
+ -> Seq Scan on sl sl3
+ Filter: (cool_bool AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL) AND (cool_bool IS NOT NULL))
+ -> Seq Scan on sl sl4
+(7 rows)
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index ad90c326c00..faf07f7c4ab 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3156,6 +3156,12 @@ SELECT * FROM
(SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a) q0, sl
WHERE q0.a = 1;
+-- BUG #19435 Do not forget to replace relid in bare Var join clause
+ALTER TABLE sl ADD COLUMN cool_bool boolean;
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
+ ON sl2.cool_bool LEFT JOIN sl AS sl4 ON sl2.cool_bool;
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
--
2.51.0
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-bugs@postgresql.org
Cc: lepihov@gmail.com, aekorotkov@gmail.com, tndrwang@gmail.com, reshkekirill@gmail.com, masao.fujii@gmail.com, ammmkilo@163.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
In-Reply-To: <a498f5b8-2f17-4ee0-b021-63ff9829b45b@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