[PATCH] Prevent lateral dependency cycles in self-join elimination

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