Self-join elimination drops an equality qual

Started by Jacob Brazeal1 day ago3 messageshackers
Jump to latest
#1Jacob Brazeal
jacob.brazeal@gmail.com

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.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jacob Brazeal (#1)
Re: Self-join elimination drops an equality qual

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

[1]: /messages/by-id/2727652.1784997994@sss.pgh.pa.us

#3Jacob Brazeal
jacob.brazeal@gmail.com
In reply to: Tom Lane (#2)
Re: Self-join elimination drops an equality qual

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 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] 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

[1]
/messages/by-id/2727652.1784997994@sss.pgh.pa.us