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.
