Hi, I found a wrong-result case in self-join elimination involving an inheritance scan and an ONLY scan.
Here is a reproducer: CREATE TABLE r1 (id int, v int); INSERT INTO r1 VALUES (1, 1); CREATE TABLE r1c () INHERITS (r1); INSERT INTO r1c VALUES (1, 100); CREATE UNIQUE INDEX r1_id ON r1 (id); ANALYZE r1, r1c; SELECT * FROM r1 p1 JOIN ONLY r1 p2 ON p1.id = p2.id; With enable_self_join_elimination enabled, this returns: postgres=# show enable_self_join_elimination ; enable_self_join_elimination ------------------------------ on (1 row) postgres=# SELECT * FROM r1 p1 JOIN ONLY r1 p2 ON p1.id = p2.id; id | v | id | v ----+---+----+--- 1 | 1 | 1 | 1 (1 row) With it disabled, the query correctly returns: postgres=# set enable_self_join_elimination = off; SET postgres=# SELECT * FROM r1 p1 JOIN ONLY r1 p2 ON p1.id = p2.id; id | v | id | v ----+-----+----+--- 1 | 1 | 1 | 1 1 | 100 | 1 | 1 (2 rows) remove_self_joins_recurse() groups candidates by relation OID without checking rte->inh. In this case, the two references have the same OID but different scan scopes. The unique index on r1 does not prove that a matching row from r1c is the same physical row as the matching row in r1. The attached patch excludes RTEs with inh set from SJE candidates. Partitioned tables are already excluded by the relkind check, while references to ordinary tables without children have their inh flag cleared during preprocessing. The patch also adds regression tests for both reference orders and verifies that a join between two ONLY scans can still be eliminated. -- Thanks, Tender Wang
From 489896e07d3ad3b14b214129d407f67f36ece5a5 Mon Sep 17 00:00:00 2001 From: Tender Wang <[email protected]> Date: Mon, 12 Oct 2026 09:02:07 +0800 Subject: [PATCH] Fix self-join elimination for inheritance scans Self-join elimination groups candidate relations by their relation OID, but references to the same relation can have different scan scopes when one includes inheritance children and another uses ONLY. A unique index on the parent does not prove that matching rows from these scans are the same physical row. Eliminating the join can therefore produce incorrect results. Exclude RTEs with inh set from self-join elimination candidates. Add regression coverage for joins between inheritance scans and ONLY scans in both orders, and verify that joins between two ONLY scans can still be eliminated. --- src/backend/optimizer/plan/analyzejoins.c | 14 ++++--- src/test/regress/expected/join.out | 46 +++++++++++++++++++++++ src/test/regress/sql/join.sql | 30 +++++++++++++++ 3 files changed, 85 insertions(+), 5 deletions(-) diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 1fafca57004..0946ace1b6d 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -1981,15 +1981,19 @@ remove_self_joins_recurse(PlannerInfo *root, List *joinlist) RangeTblEntry *rte = root->simple_rte_array[varno]; /* - * We only consider ordinary relations as candidates to be - * removed, and these relations should not have TABLESAMPLE - * clauses specified. Removing a relation with TABLESAMPLE clause - * could potentially change the semantics of the query. Because of - * UPDATE/DELETE EPQ mechanism, currently Query->resultRelation or + * We only consider ordinary relations without inheritance scans as + * candidates to be removed. A unique index on an inheritance parent + * does not establish uniqueness across its children. + * + * These relations should not have TABLESAMPLE clauses specified. + * Removing a relation with a TABLESAMPLE clause could potentially + * change the semantics of the query. Because of the UPDATE/DELETE + * EPQ mechanism, currently Query->resultRelation or * Query->mergeTargetRelation associated rel cannot be eliminated. */ if (rte->rtekind == RTE_RELATION && rte->relkind == RELKIND_RELATION && + !rte->inh && rte->tablesample == NULL && varno != root->parse->resultRelation && varno != root->parse->mergeTargetRelation) diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 4ee94d00ade..424f7b33d6f 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -10017,6 +10017,52 @@ 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 merge an inheritance scan with an ONLY scan. +CREATE TABLE sje_inh (id int, v int); +CREATE TABLE sje_inh_child () INHERITS (sje_inh); +CREATE UNIQUE INDEX sje_inh_id_idx ON sje_inh (id); +INSERT INTO sje_inh VALUES (1, 1); +INSERT INTO sje_inh_child VALUES (1, 100); +ANALYZE sje_inh; +ANALYZE sje_inh_child; +SELECT p1.id, p1.v, p2.id, p2.v +FROM sje_inh p1 JOIN ONLY sje_inh p2 ON p1.id = p2.id +ORDER BY p1.v, p2.v; + id | v | id | v +----+-----+----+--- + 1 | 1 | 1 | 1 + 1 | 100 | 1 | 1 +(2 rows) + +-- Check the opposite order as well. +SELECT p1.id, p1.v, p2.id, p2.v +FROM ONLY sje_inh p1 JOIN sje_inh p2 ON p1.id = p2.id +ORDER BY p1.v, p2.v; + id | v | id | v +----+---+----+----- + 1 | 1 | 1 | 1 + 1 | 1 | 1 | 100 +(2 rows) + +-- Self-join elimination is still valid when both references use ONLY. +EXPLAIN (COSTS OFF) +SELECT p1.id, p1.v, p2.id, p2.v +FROM ONLY sje_inh p1 JOIN ONLY sje_inh p2 ON p1.id = p2.id; + QUERY PLAN +---------------------------- + Seq Scan on sje_inh p2 + Filter: (id IS NOT NULL) +(2 rows) + +SELECT p1.id, p1.v, p2.id, p2.v +FROM ONLY sje_inh p1 JOIN ONLY sje_inh p2 ON p1.id = p2.id; + id | v | id | v +----+---+----+--- + 1 | 1 | 1 | 1 +(1 row) + +DROP TABLE sje_inh_child; +DROP TABLE sje_inh; reset enable_hashjoin; reset enable_mergejoin; -- diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index bf8153afbcd..50c65089df0 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -3906,6 +3906,36 @@ 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 merge an inheritance scan with an ONLY scan. +CREATE TABLE sje_inh (id int, v int); +CREATE TABLE sje_inh_child () INHERITS (sje_inh); +CREATE UNIQUE INDEX sje_inh_id_idx ON sje_inh (id); + +INSERT INTO sje_inh VALUES (1, 1); +INSERT INTO sje_inh_child VALUES (1, 100); +ANALYZE sje_inh; +ANALYZE sje_inh_child; + +SELECT p1.id, p1.v, p2.id, p2.v +FROM sje_inh p1 JOIN ONLY sje_inh p2 ON p1.id = p2.id +ORDER BY p1.v, p2.v; + +-- Check the opposite order as well. +SELECT p1.id, p1.v, p2.id, p2.v +FROM ONLY sje_inh p1 JOIN sje_inh p2 ON p1.id = p2.id +ORDER BY p1.v, p2.v; + +-- Self-join elimination is still valid when both references use ONLY. +EXPLAIN (COSTS OFF) +SELECT p1.id, p1.v, p2.id, p2.v +FROM ONLY sje_inh p1 JOIN ONLY sje_inh p2 ON p1.id = p2.id; + +SELECT p1.id, p1.v, p2.id, p2.v +FROM ONLY sje_inh p1 JOIN ONLY sje_inh p2 ON p1.id = p2.id; + +DROP TABLE sje_inh_child; +DROP TABLE sje_inh; + reset enable_hashjoin; reset enable_mergejoin; -- 2.43.0
