v3 SQL generation prunes a CTE column that another CTE still references

[shangyian]

Summary

The v3 builder can emit SQL where one CTE selects a column that its source CTE does not project, producing a query the engine rejects with an unresolved-column error.

This happens when a node enters the CTE set only as a transitive dependency. CTE projection pruning derives its "needed columns" from the requested dimensions' join paths, but the CTE set itself is built from the full transitive closure of the dependency graph. A node in the second set but not the first is never scanned, so the columns it reads are pruned out of its upstream CTE.

Repro

-- source: default.src_item  (item_id, kind_code, bundle_id)
-- source: default.src_event (event_id, item_id, amount)

-- dimension, PK item_id
CREATE default.item AS
SELECT item_id,
       bundle_id,
       CASE WHEN kind_code = 'AUTO' THEN 'auto' ELSE 'manual' END AS channel
FROM default.src_item;

-- transform reading bundle_id from the dimension node
CREATE default.bundles AS
SELECT i.item_id, i.bundle_id AS bundle
FROM default.item AS i;

-- fact joins the transform, pulling it into the CTE closure
CREATE default.fact AS
SELECT e.event_id, e.item_id, e.amount, b.bundle
FROM default.src_event AS e
LEFT JOIN default.bundles AS b ON e.item_id = b.item_id;

default.fact links to default.item on item_id, and default.total_amount
is SELECT SUM(amount) FROM default.fact.

Request:

GET /sql/measures/v3/?metrics=default.total_amount&dimensions=default.item.channel

Actual

The item CTE is pruned to the requested attribute plus the join key, while
bundles still selects the pruned column from it:

WITH default_item AS (
  SELECT item_id,
         CASE WHEN kind_code = 'AUTO' THEN 'auto' ELSE 'manual' END AS channel
  FROM default.src_item          -- bundle_id pruned away
),
default_bundles AS (
  SELECT i.item_id, i.bundle_id AS bundle
  FROM default_item AS i         -- but referenced here
)
...

Expected

default_item keeps bundle_id, because a CTE in the same query reads it. Whatever the node definitions, generated SQL should never reference a column its source CTE does not project.

The same shape occurs with LATERAL VIEW EXPLODE(i.<pruned_column>), and when the consuming query wraps the reference in its own inner WITH (which flatten_inner_ctes hoists to top level as <outer_cte>__<inner_cte>).