public inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Andrei Lepikhov <lepihov@gmail.com>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>
Cc: Tender Wang <tndrwang@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: Thu, 26 Mar 2026 20:59:13 +0100
Message-ID: <afb2c07f-05b7-403c-b10c-ca7390316e94@gmail.com> (raw)
In-Reply-To: <CAPpHfdsrmAg+aqpjAF4Fdp2c59-dFmwBuNLhNqrxzTguiAKf=w@mail.gmail.com>
References: <19435-3cc1a87f291129f1@postgresql.org>
<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>
<a498f5b8-2f17-4ee0-b021-63ff9829b45b@gmail.com>
<CALdSSPhpUdY7-5Zg38oS1uRtu5iTFzdo0R7Z2YZD603M9RpJxg@mail.gmail.com>
<5a039d60-d13b-4cf0-a807-9c7269f06831@gmail.com>
<CAPpHfdsyNYEbjjLdsa8i8Ds-5=4pFif1+uCHn3vwzx2Pq5y29A@mail.gmail.com>
<CAPpHfdsrmAg+aqpjAF4Fdp2c59-dFmwBuNLhNqrxzTguiAKf=w@mail.gmail.com>
On 20/3/26 15:02, Alexander Korotkov wrote:
> OK. I've pushed this. Let's go back to
> restrict_infos_logically_equal(). I'm still not convinced that we
> need to check if required_relids is singleton. Why we can ignore
> outer_relids for singleton, but can't do if, for instance, two
> relations involved?
Let's continue. In the attachment, the Tender's proposal that I changed
a little and added some tests.
As you can see in the tests, the SINGLETON limitation keeps duplicates
of clauses like 'a.x + b.y = c'.
This example shows the main flaw of this approach. Introducing the
restrict_infos_logically_equal(), we do a little more job than just the
equal() routine could because of the context where we call this function
and on which clauses.
But skipping all other RestrictInfo fields except required_relids seems
excessive. - see the example with security_level difference - ignoring
its value, we potentially might remove the clause with enforced security
level in favour of an unsecured one.
That's more, further some new optimisations might introduce more fields
into RestrictInfo that should be checked to correctly decide on the
equality, and we may forget to arrange this specific place.
So, formally it works, and making the following replacement, we close
the singleton issue:
- if (bms_membership(a->required_relids) == BMS_SINGLETON &&
- a->security_level == b->security_level)
+ if (bms_equal(a->required_relids, b->required_relids) &&
+ a->security_level == b->security_level &&
+ a->is_pushed_down == b->is_pushed_down)
but I'm unsure, in general, that this approach is conservative enough.
Maybe we shouldn’t change this logic and invent one more optimisation
‘deduplication’ stage later, before the selectivity estimation stage.
--
regards, Andrei Lepikhov,
pgEdge
From 01b9dc8b728bef49104fe1652841770eddb0c45c Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Thu, 26 Mar 2026 12:15:01 +0100
Subject: [PATCH] Improve RestrictInfo deduplication after self-join
elimination
After self-join elimination rewrites varnos, two RestrictInfos can
end up with identical clauses but different metadata (outer_relids,
rinfo_serial). The previous restrict_infos_logically_equal()
compared full RestrictInfo structs, missing these duplicates and
leaving redundant filter conditions in the plan.
For base restrictions (singleton required_relids), compare only the
clause expression, which is sufficient since these are pushed-down
filters that don't carry join-semantic meaning. Retain the full
struct comparison for join clauses (non-singleton required_relids)
to avoid incorrectly merging clauses at different join levels.
Also check security_level to prevent merging an RLS policy qual with a
user-written ON clause that happen to look identical after SJE.
Author: Tender Wang <tndrwang@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Discussion: https://www.postgresql.org/message-id/19435-3cc1a87f291129f1%40postgresql.org
---
src/backend/optimizer/plan/analyzejoins.c | 16 ++++-
src/test/regress/expected/join.out | 78 +++++++++++++++++++++++
src/test/regress/sql/join.sql | 48 ++++++++++++++
3 files changed, 139 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 12e9ed0d0c7..ed35c51a38f 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1635,9 +1635,19 @@ restrict_infos_logically_equal(RestrictInfo *a, RestrictInfo *b)
int saved_rinfo_serial = a->rinfo_serial;
bool result;
- a->rinfo_serial = b->rinfo_serial;
- result = equal(a, b);
- a->rinfo_serial = saved_rinfo_serial;
+ if (bms_membership(a->required_relids) == BMS_SINGLETON &&
+ a->security_level == b->security_level)
+ {
+ Assert(a->is_pushed_down && b->is_pushed_down);
+
+ result = equal(a->clause, b->clause);
+ }
+ else
+ {
+ a->rinfo_serial = b->rinfo_serial;
+ result = equal(a, b);
+ a->rinfo_serial = saved_rinfo_serial;
+ }
return result;
}
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 84872c6f04e..88766e5e626 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8108,6 +8108,84 @@ SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
-> Seq Scan on sl sl4
(7 rows)
+-- SJE: after self-join removal merges sl7 and sl6, the JOIN
+-- produces a pushed-down (bool_col IS NOT NULL) that duplicates the ON
+-- clause. Verify that clause deduplication removes the duplicate, leaving
+-- a single Filter condition.
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl AS sl5 LEFT JOIN (sl AS sl6 NATURAL JOIN sl AS sl7)
+ ON sl6.bool_col IS NOT NULL
+ LEFT JOIN sl AS sl8
+ ON sl6.bool_col;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl5
+ -> Nested Loop Left Join
+ Join Filter: sl7.bool_col
+ -> Seq Scan on sl sl7
+ Filter: ((bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+ -> Seq Scan on sl sl8
+(7 rows)
+
+-- SJE: multi-relation clause deduplication after self-join removal.
+-- After SJE removes sl4, both ON clauses collapse to the same expression
+-- but with non-singleton required_relids (referencing sl2 and the kept rel).
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN sl sl2
+ LEFT JOIN (sl AS sl4 NATURAL JOIN sl AS sl3)
+ ON (sl4.b + sl2.b) IS NOT NULL
+ ON (sl3.b + sl2.b) IS NOT NULL;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ -> Nested Loop
+ Join Filter: (((sl3.b + sl2.b) IS NOT NULL) AND ((sl3.b + sl2.b) IS NOT NULL))
+ -> Seq Scan on sl sl2
+ -> Seq Scan on sl sl3
+ Filter: ((a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL) AND (bool_col IS NOT NULL))
+(7 rows)
+
+-- SJE: clause deduplication must not merge clauses with different
+-- security_level values. An RLS policy qual and a
+-- user-written ON clause may look identical after SJE
+-- rewrites varnos, but removing the RLS qual would break the security
+-- barrier guarantee.
+ALTER TABLE sl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE sl FORCE ROW LEVEL SECURITY;
+CREATE POLICY sl_policy ON sl USING (bool_col IS NOT NULL);
+CREATE ROLE regress_sje_user LOGIN;
+GRANT SELECT ON sl TO regress_sje_user;
+SET ROLE regress_sje_user;
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN (
+ (sl AS sl2 NATURAL JOIN sl AS sl3)
+ LEFT JOIN sl sl4
+ ON sl2.bool_col IS NOT NULL
+ ) ON sl2.bool_col IS NOT NULL;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ Filter: (bool_col IS NOT NULL)
+ -> Nested Loop Left Join
+ Join Filter: (sl3.bool_col IS NOT NULL)
+ -> Seq Scan on sl sl3
+ Filter: ((bool_col IS NOT NULL) AND (bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+ -> Seq Scan on sl sl4
+ Filter: (bool_col IS NOT NULL)
+(9 rows)
+
+RESET ROLE;
+DROP POLICY sl_policy ON sl;
+ALTER TABLE sl DISABLE ROW LEVEL SECURITY;
+ALTER TABLE sl NO FORCE ROW LEVEL SECURITY;
+REVOKE SELECT ON sl FROM regress_sje_user;
+DROP ROLE regress_sje_user;
-- 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 30b479dda7c..864d95694f9 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3162,6 +3162,54 @@ EXPLAIN (COSTS OFF)
SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
ON sl2.bool_col LEFT JOIN sl AS sl4 ON sl2.bool_col;
+-- SJE: after self-join removal merges sl7 and sl6, the JOIN
+-- produces a pushed-down (bool_col IS NOT NULL) that duplicates the ON
+-- clause. Verify that clause deduplication removes the duplicate, leaving
+-- a single Filter condition.
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl AS sl5 LEFT JOIN (sl AS sl6 NATURAL JOIN sl AS sl7)
+ ON sl6.bool_col IS NOT NULL
+ LEFT JOIN sl AS sl8
+ ON sl6.bool_col;
+
+-- SJE: multi-relation clause deduplication after self-join removal.
+-- After SJE removes sl4, both ON clauses collapse to the same expression
+-- but with non-singleton required_relids (referencing sl2 and the kept rel).
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN sl sl2
+ LEFT JOIN (sl AS sl4 NATURAL JOIN sl AS sl3)
+ ON (sl4.b + sl2.b) IS NOT NULL
+ ON (sl3.b + sl2.b) IS NOT NULL;
+
+-- SJE: clause deduplication must not merge clauses with different
+-- security_level values. An RLS policy qual and a
+-- user-written ON clause may look identical after SJE
+-- rewrites varnos, but removing the RLS qual would break the security
+-- barrier guarantee.
+ALTER TABLE sl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE sl FORCE ROW LEVEL SECURITY;
+CREATE POLICY sl_policy ON sl USING (bool_col IS NOT NULL);
+CREATE ROLE regress_sje_user LOGIN;
+GRANT SELECT ON sl TO regress_sje_user;
+SET ROLE regress_sje_user;
+
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN (
+ (sl AS sl2 NATURAL JOIN sl AS sl3)
+ LEFT JOIN sl sl4
+ ON sl2.bool_col IS NOT NULL
+ ) ON sl2.bool_col IS NOT NULL;
+
+RESET ROLE;
+DROP POLICY sl_policy ON sl;
+ALTER TABLE sl DISABLE ROW LEVEL SECURITY;
+ALTER TABLE sl NO FORCE ROW LEVEL SECURITY;
+REVOKE SELECT ON sl FROM regress_sje_user;
+DROP ROLE regress_sje_user;
+
-- 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-Improve-RestrictInfo-deduplication-after-self-join-e.patch (8.5K, 2-0001-Improve-RestrictInfo-deduplication-after-self-join-e.patch)
download | inline diff:
From 01b9dc8b728bef49104fe1652841770eddb0c45c Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Thu, 26 Mar 2026 12:15:01 +0100
Subject: [PATCH] Improve RestrictInfo deduplication after self-join
elimination
After self-join elimination rewrites varnos, two RestrictInfos can
end up with identical clauses but different metadata (outer_relids,
rinfo_serial). The previous restrict_infos_logically_equal()
compared full RestrictInfo structs, missing these duplicates and
leaving redundant filter conditions in the plan.
For base restrictions (singleton required_relids), compare only the
clause expression, which is sufficient since these are pushed-down
filters that don't carry join-semantic meaning. Retain the full
struct comparison for join clauses (non-singleton required_relids)
to avoid incorrectly merging clauses at different join levels.
Also check security_level to prevent merging an RLS policy qual with a
user-written ON clause that happen to look identical after SJE.
Author: Tender Wang <tndrwang@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Discussion: https://www.postgresql.org/message-id/19435-3cc1a87f291129f1%40postgresql.org
---
src/backend/optimizer/plan/analyzejoins.c | 16 ++++-
src/test/regress/expected/join.out | 78 +++++++++++++++++++++++
src/test/regress/sql/join.sql | 48 ++++++++++++++
3 files changed, 139 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 12e9ed0d0c7..ed35c51a38f 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1635,9 +1635,19 @@ restrict_infos_logically_equal(RestrictInfo *a, RestrictInfo *b)
int saved_rinfo_serial = a->rinfo_serial;
bool result;
- a->rinfo_serial = b->rinfo_serial;
- result = equal(a, b);
- a->rinfo_serial = saved_rinfo_serial;
+ if (bms_membership(a->required_relids) == BMS_SINGLETON &&
+ a->security_level == b->security_level)
+ {
+ Assert(a->is_pushed_down && b->is_pushed_down);
+
+ result = equal(a->clause, b->clause);
+ }
+ else
+ {
+ a->rinfo_serial = b->rinfo_serial;
+ result = equal(a, b);
+ a->rinfo_serial = saved_rinfo_serial;
+ }
return result;
}
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 84872c6f04e..88766e5e626 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8108,6 +8108,84 @@ SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
-> Seq Scan on sl sl4
(7 rows)
+-- SJE: after self-join removal merges sl7 and sl6, the JOIN
+-- produces a pushed-down (bool_col IS NOT NULL) that duplicates the ON
+-- clause. Verify that clause deduplication removes the duplicate, leaving
+-- a single Filter condition.
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl AS sl5 LEFT JOIN (sl AS sl6 NATURAL JOIN sl AS sl7)
+ ON sl6.bool_col IS NOT NULL
+ LEFT JOIN sl AS sl8
+ ON sl6.bool_col;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl5
+ -> Nested Loop Left Join
+ Join Filter: sl7.bool_col
+ -> Seq Scan on sl sl7
+ Filter: ((bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+ -> Seq Scan on sl sl8
+(7 rows)
+
+-- SJE: multi-relation clause deduplication after self-join removal.
+-- After SJE removes sl4, both ON clauses collapse to the same expression
+-- but with non-singleton required_relids (referencing sl2 and the kept rel).
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN sl sl2
+ LEFT JOIN (sl AS sl4 NATURAL JOIN sl AS sl3)
+ ON (sl4.b + sl2.b) IS NOT NULL
+ ON (sl3.b + sl2.b) IS NOT NULL;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ -> Nested Loop
+ Join Filter: (((sl3.b + sl2.b) IS NOT NULL) AND ((sl3.b + sl2.b) IS NOT NULL))
+ -> Seq Scan on sl sl2
+ -> Seq Scan on sl sl3
+ Filter: ((a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL) AND (bool_col IS NOT NULL))
+(7 rows)
+
+-- SJE: clause deduplication must not merge clauses with different
+-- security_level values. An RLS policy qual and a
+-- user-written ON clause may look identical after SJE
+-- rewrites varnos, but removing the RLS qual would break the security
+-- barrier guarantee.
+ALTER TABLE sl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE sl FORCE ROW LEVEL SECURITY;
+CREATE POLICY sl_policy ON sl USING (bool_col IS NOT NULL);
+CREATE ROLE regress_sje_user LOGIN;
+GRANT SELECT ON sl TO regress_sje_user;
+SET ROLE regress_sje_user;
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN (
+ (sl AS sl2 NATURAL JOIN sl AS sl3)
+ LEFT JOIN sl sl4
+ ON sl2.bool_col IS NOT NULL
+ ) ON sl2.bool_col IS NOT NULL;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ Filter: (bool_col IS NOT NULL)
+ -> Nested Loop Left Join
+ Join Filter: (sl3.bool_col IS NOT NULL)
+ -> Seq Scan on sl sl3
+ Filter: ((bool_col IS NOT NULL) AND (bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+ -> Seq Scan on sl sl4
+ Filter: (bool_col IS NOT NULL)
+(9 rows)
+
+RESET ROLE;
+DROP POLICY sl_policy ON sl;
+ALTER TABLE sl DISABLE ROW LEVEL SECURITY;
+ALTER TABLE sl NO FORCE ROW LEVEL SECURITY;
+REVOKE SELECT ON sl FROM regress_sje_user;
+DROP ROLE regress_sje_user;
-- 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 30b479dda7c..864d95694f9 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3162,6 +3162,54 @@ EXPLAIN (COSTS OFF)
SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
ON sl2.bool_col LEFT JOIN sl AS sl4 ON sl2.bool_col;
+-- SJE: after self-join removal merges sl7 and sl6, the JOIN
+-- produces a pushed-down (bool_col IS NOT NULL) that duplicates the ON
+-- clause. Verify that clause deduplication removes the duplicate, leaving
+-- a single Filter condition.
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl AS sl5 LEFT JOIN (sl AS sl6 NATURAL JOIN sl AS sl7)
+ ON sl6.bool_col IS NOT NULL
+ LEFT JOIN sl AS sl8
+ ON sl6.bool_col;
+
+-- SJE: multi-relation clause deduplication after self-join removal.
+-- After SJE removes sl4, both ON clauses collapse to the same expression
+-- but with non-singleton required_relids (referencing sl2 and the kept rel).
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN sl sl2
+ LEFT JOIN (sl AS sl4 NATURAL JOIN sl AS sl3)
+ ON (sl4.b + sl2.b) IS NOT NULL
+ ON (sl3.b + sl2.b) IS NOT NULL;
+
+-- SJE: clause deduplication must not merge clauses with different
+-- security_level values. An RLS policy qual and a
+-- user-written ON clause may look identical after SJE
+-- rewrites varnos, but removing the RLS qual would break the security
+-- barrier guarantee.
+ALTER TABLE sl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE sl FORCE ROW LEVEL SECURITY;
+CREATE POLICY sl_policy ON sl USING (bool_col IS NOT NULL);
+CREATE ROLE regress_sje_user LOGIN;
+GRANT SELECT ON sl TO regress_sje_user;
+SET ROLE regress_sje_user;
+
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN (
+ (sl AS sl2 NATURAL JOIN sl AS sl3)
+ LEFT JOIN sl sl4
+ ON sl2.bool_col IS NOT NULL
+ ) ON sl2.bool_col IS NOT NULL;
+
+RESET ROLE;
+DROP POLICY sl_policy ON sl;
+ALTER TABLE sl DISABLE ROW LEVEL SECURITY;
+ALTER TABLE sl NO FORCE ROW LEVEL SECURITY;
+REVOKE SELECT ON sl FROM regress_sje_user;
+DROP ROLE regress_sje_user;
+
-- 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, reshkekirill@gmail.com, tndrwang@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: <afb2c07f-05b7-403c-b10c-ca7390316e94@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