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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jacob Brazeal <jacob(dot)brazeal(at)gmail(dot)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]
> https://www.postgresql.org/message-id/2727652.1784997994%40sss.pgh.pa.us
>