Self-join elimination drops an equality qual

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.

Browse pgsql-hackers by date

  From Date Subject
Previous Message Tom Lane 2026-07-24 21:18:23 Re: bug: having clause evaluated before group by