Hi,
After finding the SJE bug involving inheritance scans and ONLY, I
asked an LLM to generate fuzz tests to look for further issues in
self-join elimination.
This uncovered another wrong-result case involving restriction clauses
with different equality operators.
How to reproduce:
...
postgres=# CREATE FUNCTION text_ci_eq(text, text) RETURNS boolean
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE
AS $$
BEGIN
RETURN lower($1) = lower($2);
END $$;
CREATE FUNCTION text_ci_cmp(text, text) RETURNS int
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
AS $$SELECT CASE WHEN lower($1) < lower($2) THEN -1
WHEN lower($1) > lower($2) THEN 1 ELSE 0 END$$;
CREATE OPERATOR === (LEFTARG=text, RIGHTARG=text, PROCEDURE=text_ci_eq,
COMMUTATOR='===', RESTRICT=eqsel, JOIN=eqjoinsel, MERGES);
CREATE OPERATOR FAMILY ci_text_fam USING btree;
CREATE OPERATOR CLASS ci_text_ops FOR TYPE text USING btree FAMILY
ci_text_fam AS
OPERATOR 1 <, OPERATOR 2 <=, OPERATOR 3 ===, OPERATOR 4 >=, OPERATOR 5 >,
FUNCTION 1 text_ci_cmp;
CREATE TABLE ci_t(id int, a text, b int);
CREATE UNIQUE INDEX ci_t_a ON ci_t(a); -- deterministic (standard)
unique index
INSERT INTO ci_t VALUES (1,'a',1),(2,'A',1),(3,'b',2);
ANALYZE ci_t;
SET enable_self_join_elimination = on;
SELECT p1.id, p1.a, p2.id, p2.a
FROM ci_t p1 JOIN ci_t p2 ON p1.b = p2.b
WHERE p1.a === 'a' AND p2.a = 'a'
ORDER BY 1, 3;
id | a | id | a
----+---+----+---
1 | a | 1 | a
(1 row)
SET enable_self_join_elimination = off;
SELECT p1.id, p1.a, p2.id, p2.a
FROM ci_t p1 JOIN ci_t p2 ON p1.b = p2.b
WHERE p1.a === 'a' AND p2.a = 'a'
ORDER BY 1, 3;
id | a | id | a
----+---+----+---
1 | a | 1 | a
2 | A | 1 | a
(2 rows)
...
SJE incorrectly considers the two restrictions equivalent and
eliminates the join.
The unique index and p2.a = 'a' establish uniqueness on the kept side.
match_unique_clauses() then checks for a corresponding restriction on
the removed side, but compares only the operands, ignoring the
operator and input collation.
It therefore treats p1.a === 'a' as matching p2.a = 'a', even though
they select different rows.
The attached patch requires both the operator and input collation to
match, in addition to the existing operand checks.
It includes a regression test for the different-operator case and
verifies that identical restrictions still allow self-join
elimination.
The full reproducer, including the custom operator definitions, is in
the regression test.
--
Thanks,
Tender Wang
From cf458c5390ddc2803704569fd79c0f0796558dcb Mon Sep 17 00:00:00 2001
From: Tender Wang <[email protected]>
Date: Mon, 12 Oct 2026 13:44:33 +0800
Subject: [PATCH] Fix restriction matching in self-join elimination
When a uniqueness proof relies on base restrictions,
match_unique_clauses() checks that the other relation has matching
restrictions. Previously, it compared only the operands, ignoring
the operator and input collation. Restrictions with identical
operands but different semantics could therefore be considered
equivalent, leading to incorrect results from self-join elimination.
Require the operator and input collation to match as well. Add
regression coverage using equality operators with different
case-sensitivity semantics.
---
src/backend/optimizer/plan/analyzejoins.c | 12 +++
src/test/regress/expected/join.out | 96 +++++++++++++++++++++++
src/test/regress/sql/join.sql | 83 ++++++++++++++++++++
3 files changed, 191 insertions(+)
diff --git a/src/backend/optimizer/plan/analyzejoins.c
b/src/backend/optimizer/plan/analyzejoins.c
index 1fafca57004..565999b9fe8 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1722,6 +1722,7 @@ match_unique_clauses(PlannerInfo *root, RelOptInfo
*outer, List *uclauses,
{
Expr *clause;
Node *iclause;
+ OpExpr *opexpr;
Node *c1;
bool matched = false;
@@ -1731,6 +1732,7 @@ match_unique_clauses(PlannerInfo *root, RelOptInfo
*outer, List *uclauses,
Assert(bms_is_empty(rinfo->left_relids) ^
bms_is_empty(rinfo->right_relids));
+ opexpr = castNode(OpExpr, rinfo->clause);
clause = (Expr *) copyObject(rinfo->clause);
ChangeVarNodes((Node *) clause, relid, outer->relid, 0);
@@ -1745,6 +1747,7 @@ match_unique_clauses(PlannerInfo *root, RelOptInfo
*outer, List *uclauses,
*/
foreach_node(RestrictInfo, orinfo, outer->baserestrictinfo)
{
+ OpExpr *oexpr;
Node *oclause;
Node *c2;
@@ -1754,6 +1757,15 @@ match_unique_clauses(PlannerInfo *root, RelOptInfo
*outer, List *uclauses,
Assert(is_opclause(orinfo->clause));
+ oexpr = castNode(OpExpr, orinfo->clause);
+ /*
+ * Matching operands are not sufficient: the equality
operator and
+ * input collation must also match to ensure equivalent
restrictions.
+ */
+ if (oexpr->opno != opexpr->opno ||
+ oexpr->inputcollid != opexpr->inputcollid)
+ continue;
+
oclause = bms_is_empty(orinfo->left_relids) ?
get_rightop(orinfo->clause) :
get_leftop(orinfo->clause);
c2 = (bms_is_empty(orinfo->left_relids) ?
diff --git a/src/test/regress/expected/join.out
b/src/test/regress/expected/join.out
index 4ee94d00ade..084220654ef 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -10017,6 +10017,102 @@ SELECT a1.a FROM sj a1,sj a2 WHERE (a1.a=a2.a) FOR
UPDATE;
Filter: (a IS NOT NULL)
(3 rows)
+-- Self-join elimination must not accept a removed-side restriction clause
+-- with a different equality operator than the clause
+-- that matched the unique index on the kept side.
+CREATE FUNCTION sje_ci_eq(text, text) RETURNS boolean
+ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$
+ BEGIN
+ RETURN lower($1) = lower($2);
+ END $$;
+CREATE FUNCTION sje_ci_cmp(text, text) RETURNS int
+ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$SELECT CASE WHEN lower($1) < lower($2) THEN -1
+ WHEN lower($1) > lower($2) THEN 1 ELSE 0 END$$;
+CREATE FUNCTION sje_ci_lt(text, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$SELECT lower($1) < lower($2)$$;
+CREATE FUNCTION sje_ci_le(text, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$SELECT lower($1) <= lower($2)$$;
+CREATE FUNCTION sje_ci_ge(text, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$SELECT lower($1) >= lower($2)$$;
+CREATE FUNCTION sje_ci_gt(text, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$SELECT lower($1) > lower($2)$$;
+CREATE OPERATOR === (LEFTARG=text, RIGHTARG=text, PROCEDURE=sje_ci_eq,
+ COMMUTATOR='===', RESTRICT=eqsel, JOIN=eqjoinsel, MERGES);
+CREATE OPERATOR <# (LEFTARG=text, RIGHTARG=text,
+ PROCEDURE=sje_ci_lt);
+CREATE OPERATOR <=# (LEFTARG=text, RIGHTARG=text,
+ PROCEDURE=sje_ci_le);
+CREATE OPERATOR >=# (LEFTARG=text, RIGHTARG=text,
+ PROCEDURE=sje_ci_ge);
+CREATE OPERATOR ># (LEFTARG=text, RIGHTARG=text,
+ PROCEDURE=sje_ci_gt);
+CREATE OPERATOR FAMILY sje_ci_text_fam USING btree;
+CREATE OPERATOR CLASS sje_ci_text_ops FOR TYPE text USING btree
+ FAMILY sje_ci_text_fam AS
+ OPERATOR 1 <#, OPERATOR 2 <=#, OPERATOR 3 ===,
+ OPERATOR 4 >=#, OPERATOR 5 >#,
+ FUNCTION 1 sje_ci_cmp;
+CREATE TABLE sje_ci (id int, a text COLLATE "C", b int);
+CREATE UNIQUE INDEX sje_ci_a_idx ON sje_ci (a);
+INSERT INTO sje_ci VALUES (1, 'a', 1), (2, 'A', 1), (3, 'b', 2);
+ANALYZE sje_ci;
+-- 'a' and 'A' are different rows for the index's equality, but equal under
+-- ===, so the join of p1 (a === 'a') with p2 (a = 'a') must keep both.
+SELECT p1.id, p1.a, p2.id, p2.a
+FROM sje_ci p1 JOIN sje_ci p2 ON p1.b = p2.b
+WHERE p1.a === 'a' AND p2.a = 'a'
+ORDER BY 1, 3;
+ id | a | id | a
+----+---+----+---
+ 1 | a | 1 | a
+ 2 | A | 1 | a
+(2 rows)
+
+-- Same operator on both sides still doesn't prove uniqueness against the
+-- standard index, so the join is kept here too.
+SELECT p1.id, p1.a, p2.id, p2.a
+FROM sje_ci p1 JOIN sje_ci p2 ON p1.b = p2.b
+WHERE p1.a === 'a' AND p2.a === 'a'
+ORDER BY 1, 3;
+ id | a | id | a
+----+---+----+---
+ 1 | a | 1 | a
+ 1 | a | 2 | A
+ 2 | A | 1 | a
+ 2 | A | 2 | A
+(4 rows)
+
+-- Identical clauses on both sides allow the self-join to be removed.
+EXPLAIN (COSTS OFF)
+SELECT p1.id, p2.id
+FROM sje_ci p1 JOIN sje_ci p2 ON p1.b = p2.b
+WHERE p1.a = 'a' AND p2.a = 'a';
+ QUERY PLAN
+-------------------------------------------------
+ Seq Scan on sje_ci p2
+ Filter: ((b IS NOT NULL) AND (a = 'a'::text))
+(2 rows)
+
+DROP TABLE sje_ci;
+DROP OPERATOR CLASS sje_ci_text_ops USING btree;
+DROP OPERATOR FAMILY sje_ci_text_fam USING btree;
+DROP OPERATOR === (text, text);
+DROP OPERATOR <# (text, text);
+DROP OPERATOR <=# (text, text);
+DROP OPERATOR >=# (text, text);
+DROP OPERATOR ># (text, text);
+DROP FUNCTION sje_ci_eq(text, text);
+DROP FUNCTION sje_ci_cmp(text, text);
+DROP FUNCTION sje_ci_lt(text, text);
+DROP FUNCTION sje_ci_le(text, text);
+DROP FUNCTION sje_ci_ge(text, text);
+DROP FUNCTION sje_ci_gt(text, text);
reset enable_hashjoin;
reset enable_mergejoin;
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index bf8153afbcd..6c867566f40 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3906,6 +3906,89 @@ ON sj_t1.id = _t2t3t4.id;
EXPLAIN (COSTS OFF)
SELECT a1.a FROM sj a1,sj a2 WHERE (a1.a=a2.a) FOR UPDATE;
+-- Self-join elimination must not accept a removed-side restriction clause
+-- with a different equality operator than the clause
+-- that matched the unique index on the kept side.
+CREATE FUNCTION sje_ci_eq(text, text) RETURNS boolean
+ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$
+ BEGIN
+ RETURN lower($1) = lower($2);
+ END $$;
+
+CREATE FUNCTION sje_ci_cmp(text, text) RETURNS int
+ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$SELECT CASE WHEN lower($1) < lower($2) THEN -1
+ WHEN lower($1) > lower($2) THEN 1 ELSE 0 END$$;
+CREATE FUNCTION sje_ci_lt(text, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$SELECT lower($1) < lower($2)$$;
+CREATE FUNCTION sje_ci_le(text, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$SELECT lower($1) <= lower($2)$$;
+CREATE FUNCTION sje_ci_ge(text, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$SELECT lower($1) >= lower($2)$$;
+CREATE FUNCTION sje_ci_gt(text, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
+ AS $$SELECT lower($1) > lower($2)$$;
+CREATE OPERATOR === (LEFTARG=text, RIGHTARG=text, PROCEDURE=sje_ci_eq,
+ COMMUTATOR='===', RESTRICT=eqsel, JOIN=eqjoinsel, MERGES);
+CREATE OPERATOR <# (LEFTARG=text, RIGHTARG=text,
+ PROCEDURE=sje_ci_lt);
+CREATE OPERATOR <=# (LEFTARG=text, RIGHTARG=text,
+ PROCEDURE=sje_ci_le);
+CREATE OPERATOR >=# (LEFTARG=text, RIGHTARG=text,
+ PROCEDURE=sje_ci_ge);
+CREATE OPERATOR ># (LEFTARG=text, RIGHTARG=text,
+ PROCEDURE=sje_ci_gt);
+CREATE OPERATOR FAMILY sje_ci_text_fam USING btree;
+CREATE OPERATOR CLASS sje_ci_text_ops FOR TYPE text USING btree
+ FAMILY sje_ci_text_fam AS
+ OPERATOR 1 <#, OPERATOR 2 <=#, OPERATOR 3 ===,
+ OPERATOR 4 >=#, OPERATOR 5 >#,
+ FUNCTION 1 sje_ci_cmp;
+
+CREATE TABLE sje_ci (id int, a text COLLATE "C", b int);
+CREATE UNIQUE INDEX sje_ci_a_idx ON sje_ci (a);
+INSERT INTO sje_ci VALUES (1, 'a', 1), (2, 'A', 1), (3, 'b', 2);
+ANALYZE sje_ci;
+
+-- 'a' and 'A' are different rows for the index's equality, but equal under
+-- ===, so the join of p1 (a === 'a') with p2 (a = 'a') must keep both.
+SELECT p1.id, p1.a, p2.id, p2.a
+FROM sje_ci p1 JOIN sje_ci p2 ON p1.b = p2.b
+WHERE p1.a === 'a' AND p2.a = 'a'
+ORDER BY 1, 3;
+
+-- Same operator on both sides still doesn't prove uniqueness against the
+-- standard index, so the join is kept here too.
+SELECT p1.id, p1.a, p2.id, p2.a
+FROM sje_ci p1 JOIN sje_ci p2 ON p1.b = p2.b
+WHERE p1.a === 'a' AND p2.a === 'a'
+ORDER BY 1, 3;
+
+-- Identical clauses on both sides allow the self-join to be removed.
+EXPLAIN (COSTS OFF)
+SELECT p1.id, p2.id
+FROM sje_ci p1 JOIN sje_ci p2 ON p1.b = p2.b
+WHERE p1.a = 'a' AND p2.a = 'a';
+
+DROP TABLE sje_ci;
+DROP OPERATOR CLASS sje_ci_text_ops USING btree;
+DROP OPERATOR FAMILY sje_ci_text_fam USING btree;
+DROP OPERATOR === (text, text);
+DROP OPERATOR <# (text, text);
+DROP OPERATOR <=# (text, text);
+DROP OPERATOR >=# (text, text);
+DROP OPERATOR ># (text, text);
+DROP FUNCTION sje_ci_eq(text, text);
+DROP FUNCTION sje_ci_cmp(text, text);
+DROP FUNCTION sje_ci_lt(text, text);
+DROP FUNCTION sje_ci_le(text, text);
+DROP FUNCTION sje_ci_ge(text, text);
+DROP FUNCTION sje_ci_gt(text, text);
+
reset enable_hashjoin;
reset enable_mergejoin;
--
2.43.0
