Hello, Commit 3138f248cd3 fixes a different planner problem: removing an RTE_RESULT that is needed by a nested PlaceHolderVar. It does not fix the lateral-dependency cycle triggered later by self-join elimination. The following query still fails on baseline 663c34c1eb2c71f35955814923ad578d0d9c0cf1, which contains that commit.
Minimal reproducer:
CREATE TABLE t (a int PRIMARY KEY);
INSERT INTO t VALUES (1), (2);
SELECT s.b
FROM t x,
abs(x.a) b,
LATERAL (SELECT b FROM t c WHERE c.a = x.a) s
GROUP BY CUBE (1);
With enable_self_join_elimination = on, an assertion-enabled build
fails at !bms_is_member(rti, lateral_relids) in
create_lateral_join_info(); a non-assertion build reports “failed to
build any 2-way joins”. With self-join elimination disabled, the query
returns the expected multiset {1, 2, NULL}.
The original outer-join form also fails during planning:
CREATE TABLE t (a int PRIMARY KEY);
INSERT INTO t VALUES (1), (2);
SELECT s.y
FROM t d LEFT JOIN
(t x CROSS JOIN LATERAL (SELECT x.a AS v OFFSET 0) b
CROSS JOIN LATERAL (SELECT coalesce(b.v, 0) FROM t c
WHERE c.a = x.a) s(y))
ON true;
With self-join elimination disabled, this returns four rows: two
copies each of 1 and 2.
The earlier fix does not apply because the failure follows a different
planner path. During self-join elimination, x and c are identified as
a removable self-join. Substituting one relation for the other
rewrites the correlated reference while a PlaceHolderVar still carries
a lateral dependency through the intermediate LATERAL relation.
Rebuilding lateral join information then finds a dependency cycle.
The attached patch checks the candidate relation substitution before
removing the self-join. It simulates the substitution, recomputes
PlaceHolderVar evaluation and lateral-dependency sets, and rejects a
merge if the resulting dependency graph contains a cycle. After safe
merges, it updates the relation and PlaceHolderVar state used by later
candidates in the same pass.
The patch is based on 663c34c1eb2c71f35955814923ad578d0d9c0cf1. The
assertion-enabled and non-assertion builds passed the 239-test core
regression schedule. The regression additions cover the minimal and
outer-join reproducers, PHV evaluation-site changes, nested PHVs,
multiple candidate joins, safe merge controls, and an empty physical
table.
Patch SHA-256: 978f682836a50ecd39453cb333c9396403d836e6c60a57d729c7947eccb3ff15
Regards,
Haibo Yan
v1-0001-Prevent-lateral-dependency-cycles-in-self-join-elimination.patch
Description: Binary data
