| From: | Jacob Brazeal <jacob(dot)brazeal(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Self-join elimination drops an equality qual |
| Date: | 2026-07-24 21:32:07 |
| Message-ID: | CA+COZaBVFHS-eXL7a53iZfNJyv5LqD5t1eSbYDavYxLSyfkL6A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Tom Lane | 2026-07-24 21:18:23 | Re: bug: having clause evaluated before group by |