Chained VLP endpoint projection not rewritten to CTE column across WITH barrier (recursive-VLP form)

[genezhang]

Summary

When a variable-length path is chained to a further hop ((person)-[:KNOWS*2..2]-(friend)-[:IS_LOCATED_IN]->(city)) and crosses a WITH barrier, the VLP endpoint projections in the generated WITH CTE are not rewritten to the VLP CTE columns (t.start_id/t.end_id). The CTE selects raw friend.id / person.id, but its FROM is vlp_person_friend AS t (+ chained joins), where those base-table aliases are not in scope → ClickHouse Code 47.

Minimal repro (LDBC schema)

MATCH (person:Person {id: 14})-[:KNOWS*2..2]-(friend:Person)-[:IS_LOCATED_IN]->(city:City)
WITH person, friend, city
RETURN friend.id

Generated (abridged):

with_city_friend_person_cte_0 AS (SELECT
      friend.id AS "p6_friend_id"          -- ❌ should be t.end_id
FROM vlp_person_friend AS t
INNER JOIN ldbc.Person_isLocatedIn_Place AS t2 ON t2.PersonId = t.end_id
INNER JOIN ldbc.Place AS city ON city.id = t2.CityId
WHERE (t.start_id = 14 AND ...))

→ Code 47: Unknown expression identifier 'friend.id'.

Isolation

• Without the chained -[:IS_LOCATED_IN]->(city) hop, *2..2 renders as flat joins (not a recursive VLP CTE) and friend.id resolves correctly (r2.Person2Id). The chained hop is what forces the recursive vlp_person_friend CTE form.
• In that recursive form, rewrite_vlp_union_branch_aliases does not descend into the generated WITH CTE's inner SELECT, so person(start)→t.start_id and friend(end)→t.end_id rewrites are missed.

Impact

• Blocks LDBC SNB IC10 (its (person)-[:KNOWS*2..2]-(friend)-[:IS_LOCATED_IN]->(city) + WITH is exactly this shape). Related to the closed VLP endpoint referenced across a WITH/UNWIND barrier fails to resolve (standard schema; 7 of 17 LDBC SNB non-passing queries) #1100 (VLP endpoint across WITH barrier) and Multiple VLPs with non-overlapping aliases in one scope: one VLP silently dropped, its endpoint conflated with the other's #544 (chained VLP), but this is the chained-hop-forces-recursive-VLP variant.

Acceptance

• The chained-VLP endpoint projections in the WITH CTE resolve to t.start_id/t.end_id.
• Golden SQL fixture + live execution on LDBC SF1.

Surfaced from the LDBC SNB re-measurement (IC10 root cause), after the #1100 slices landed.

[genezhang]

Root-cause analysis — deeper than a single alias rewrite

Traced this fully. There are two coupled layers:

Layer 1 (surgical): WITH-CTE SELECT not rewritten to VLP columns

The WITH-barrier CTE (FROM vlp_person_friend AS t) projects raw friend.id / person.id / friend.firstName instead of the VLP CTE columns t.end_id / t.start_id / t.end_firstName. The chained JOIN in the same CTE does correctly use t.end_id, so the endpoint→VLP-column mapping is known — it's just not applied to the CTE's SELECT items.

The existing CTE-body rewrite (plan_builder_utils.rs ~2486) deliberately skips endpoint aliases, on the assumption "WITH CTEs have their own endpoint JOINs (JOIN users AS u1)". That assumption is false for the chained-VLP case: here the endpoint has no node-table JOIN; it must resolve to t.start_id/t.end_id.

Layer 2 (deeper): endpoint property propagation across the barrier is inconsistent

The VLP CTE exposes end_<prop> columns only when the property-requirements analysis registers them. Across the WITH barrier this is fragile:

• RETURN friend.firstName alone → VLP CTE does propagate end_firstName (and the non-barrier version renders t.end_firstName correctly).
• RETURN friend.firstName, friend.id, person.id → VLP CTE propagates only start_id/end_id, dropping end_firstName.

So a naive SELECT rewrite to t.end_firstName would reference a column the CTE didn't project. The full fix must also ensure WITH-projected endpoint properties are registered as VLP endpoint requirements.

Scoping

• .id-only subset is clean and surgical: start_id/end_id are always present in the VLP CTE, so rewriting friend.id→t.end_id / person.id→t.start_id needs no requirements change. This alone fixes the correlated-EXISTS person.id reference that is IC10's first execution error.
• Full IC10 additionally needs non-id endpoint properties (friend.firstName, friend.gender, friend.lastName) propagated across the barrier (Layer 2).

Both layers live in the WITH→CTE machinery (plan_builder_utils.rs / with_to_cte/), the highest-regression-risk area — needs a full golden + corpus sweep.

[genezhang]

Further findings — property propagation, and a strong lead

Confirmed the VLP CTE does NOT propagate end_<prop> columns across the barrier even for a single property (RETURN friend.firstName alone fails Code 47), so it's not a mixed-property interaction — the chained-VLP-WITH renderer never carries endpoint properties.

Strong lead: in cte_extraction.rs, the block that adds endpoint properties from PropertyRequirements for downstream WITH/RETURN usage (the one whose comment literally says "properties like friend.birthday referenced after WITH won't be included in the VLP CTE columns", ~line 3906) is gated behind if graph_rel.shortest_path_mode.is_some() (~line 3881) — i.e. it only runs for shortestPath queries. A regular chained VLP (*2..2) takes the else branch (~3953), which consults PropertyRequirements too — but this repro doesn't emit that branch's VLP property pruning debug log, so this VLP is rendered by yet another path (the vlp_*_inner + WHERE hop_count >= N exact-bound/chained renderer), which does its own property collection and misses the post-barrier endpoint properties.

Next investigation step: identify the exact renderer that produces vlp_person_friend_inner (min-bound wrapper) for a VLP chained to a further hop across a WITH barrier, and ensure it threads the barrier's endpoint PropertyRequirements into the CTE's projected columns (mirroring the shortestPath-gated block and the else branch), then rewrite the WITH-CTE SELECT endpoints to t.start_<prop>/t.end_<prop>.

This is a genuine design-cycle fix in the WITH→CTE / VLP-render machinery (the repo's highest-regression-risk area), not a surgical one-liner. Documenting here for a focused follow-up rather than a rushed patch.

[genezhang]

Architectural root cause identified

The difference is a flat-joins vs recursive-VLP-CTE planner decision:

• Non-chained (person)-[:KNOWS*2..2]-(friend) WITH person, friend RETURN friend.firstName → rendered as flat joins with a real INNER JOIN ldbc.Person AS friend ON r2.Person2Id = friend.id. friend.firstName resolves correctly. Executes fine.
• Chained (person)-[:KNOWS*2..2]-(friend)-[:IS_LOCATED_IN]->(city) WITH ... → the extra hop forces the recursive vlp_person_friend CTE form, which drops the friend endpoint node-table join and exposes only start_id/end_id. friend.firstName (and even friend.id/person.id) then can't resolve in the WITH CTE → Code 47.

So the recursive-VLP path, when the VLP is chained to a further hop and crosses a WITH barrier, neither (a) joins the endpoint node table into the WITH CTE nor (b) propagates endpoint properties into the VLP CTE columns. The flat path does the former correctly.

This is a design-cycle fix, not surgical: it touches the planner's flat-vs-recursive VLP decision and/or the recursive chained-VLP renderer, in the WITH→CTE machinery (the repo's highest-regression-risk area). Two viable directions:

1. Make the recursive chained-VLP WITH CTE JOIN the endpoint node table (JOIN ldbc.Person AS friend ON friend.id = t.end_id), mirroring the flat path — cleanest, all endpoint properties resolve naturally.
2. Propagate endpoint PropertyRequirements into the VLP CTE columns + rewrite the WITH-CTE SELECT to t.end_<prop> — more invasive, must also handle the _inner/min-hop wrapper.

Direction 1 is preferred. Left as a scoped follow-up rather than a rushed change in the delicate area. Blocks LDBC IC10; related to #544 (chained VLP) and the closed #1100.