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 <[email protected]> wrote: > Jacob Brazeal <[email protected]> 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 >
