Re: Self-join elimination drops an equality qual

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