Self-join elimination drops an equality qual
Hi,
Self-join elimination can drop an equality qual in a three-way self-join,
producing wrong results.
This reproduces on current master:
CREATE SCHEMA sje;
CREATE TABLE sje.t (id int PRIMARY KEY, a int, b int, c int);
INSERT INTO sje.t
SELECT g, g % 5, g % 7, g % 3
FROM generate_series(1, 50) g;
SET search_path = sje;
SELECT t3.id, t3.a, t3.b, t3.c
FROM t t1
JOIN t t2 ON t2.id = t1.id
JOIN t t3 ON t3.id = t2.id
WHERE t1.a = t2.b
AND t3.c = t2.b
ORDER BY t3.id;
Because all three aliases are joined on the primary key, they refer to the
same row. The WHERE clause therefore requires:
a = b AND c = b
With self-join elimination disabled, the query correctly returns only ids 1
and 2:
SET enable_self_join_elimination = off;
id | a | b | c
----+---+---+---
1 | 1 | 1 | 1
2 | 2 | 2 | 2
With the default setting, enable_self_join_elimination = on, it returns
additional rows where b <> c, including:
id | a | b | c
----+---+---+---
3 | 3 | 3 | 0
4 | 4 | 4 | 1
35 | 0 | 0 | 2
The resulting plan contains only one of the two required equalities:
Sort
Sort Key: t3.id
-> Seq Scan on t t3
Filter: (a = b)
The b = c equality has been lost.
The two qualifications t1.a = t2.b and t3.c = t2.b form an EquivalenceClass
containing a, b, and c. It appears that, after the self-joined relations
are collapsed, two distinct single-relation equality clauses sharing the
same parent EquivalenceClass are incorrectly treated as redundant.
Reproduced on master at 1c9c3589042.
Jacob Brazeal <jacob.brazeal@gmail.com> writes:
Self-join elimination can drop an equality qual in a three-way self-join,
producing wrong results.
...
The two qualifications t1.a = t2.b and t3.c = t2.b form an EquivalenceClass
containing a, b, and c. It appears that, after the self-joined relations
are collapsed, two distinct single-relation equality clauses sharing the
same parent EquivalenceClass are incorrectly treated as redundant.
I think this is the same as, or closely related to, bug #19560.
The patchset I posted at [1]/messages/by-id/2727652.1784997994@sss.pgh.pa.us seems to have the desired behavior:
regression=# explain SELECT t3.id, t3.a, t3.b, t3.c
FROM t t1
JOIN t t2 ON t2.id = t1.id
JOIN t t3 ON t3.id = t2.id
WHERE t1.a = t2.b
AND t3.c = t2.b
ORDER BY t3.id;
QUERY PLAN
------------------------------------------------------------
Sort (cost=37.76..37.77 rows=1 width=16)
Sort Key: t3.id
-> Seq Scan on t t3 (cost=0.00..37.75 rows=1 width=16)
Filter: ((a = b) AND (b = c))
(4 rows)
regression=# SELECT t3.id, t3.a, t3.b, t3.c
FROM t t1
JOIN t t2 ON t2.id = t1.id
JOIN t t3 ON t3.id = t2.id
WHERE t1.a = t2.b
AND t3.c = t2.b
ORDER BY t3.id;
id | a | b | c
----+---+---+---
1 | 1 | 1 | 1
2 | 2 | 2 | 2
(2 rows)
regards, tom lane
Great! We can close this discussion then, I think. Thank you for looking
and for your work on the other thread.
On Sat, Jul 25, 2026 at 3:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Jacob Brazeal <jacob.brazeal@gmail.com> writes:
Self-join elimination can drop an equality qual in a three-way self-join,
producing wrong results.
...
The two qualifications t1.a = t2.b and t3.c = t2.b form anEquivalenceClass
containing a, b, and c. It appears that, after the self-joined relations
are collapsed, two distinct single-relation equality clauses sharing the
same parent EquivalenceClass are incorrectly treated as redundant.I think this is the same as, or closely related to, bug #19560.
The patchset I posted at [1] seems to have the desired behavior:regression=# explain SELECT t3.id, t3.a, t3.b, t3.c
FROM t t1
JOIN t t2 ON t2.id = t1.id
JOIN t t3 ON t3.id = t2.id
WHERE t1.a = t2.b
AND t3.c = t2.b
ORDER BY t3.id;
QUERY PLAN
------------------------------------------------------------
Sort (cost=37.76..37.77 rows=1 width=16)
Sort Key: t3.id
-> Seq Scan on t t3 (cost=0.00..37.75 rows=1 width=16)
Filter: ((a = b) AND (b = c))
(4 rows)regression=# SELECT t3.id, t3.a, t3.b, t3.c
FROM t t1
JOIN t t2 ON t2.id = t1.id
JOIN t t3 ON t3.id = t2.id
WHERE t1.a = t2.b
AND t3.c = t2.b
ORDER BY t3.id;
id | a | b | c
----+---+---+---
1 | 1 | 1 | 1
2 | 2 | 2 | 2
(2 rows)regards, tom lane