Wrong results from self-join elimination with inheritance and ONLY

Started by Tender Wangabout 1 month ago2 messageshackers
Jump to latest
#1Tender Wang
tndrwang@gmail.com

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

Attachments:

t254147_1
0001-Fix-self-join-elimination-for-inheritance-scans.patchtext/plain; charset=US-ASCII; name=0001-Fix-self-join-elimination-for-inheritance-scans.patchDownload+85-6
#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Tender Wang (#1)
Re: Wrong results from self-join elimination with inheritance and ONLY

Tender Wang <tndrwang@gmail.com> writes:

I found a wrong-result case in self-join elimination involving an
inheritance scan and an ONLY scan.

Pushed, thanks for the report.

(I left out the proposed regression tests, which seemed much too
heavyweight for a one-line change.)

regards, tom lane