What it does wrong
A column referenced through a CTE alias gets attributed to the underlying physical table even when the CTE never actually outputs a column by that name.
;WITH X AS (SELECT Id, Name FROM dbo.Real)
SELECT X.Missing FROM X
X only ever outputs Id and Name — Missing is neither a passthrough column nor an aliased output, it does not exist on X at all. SPXray still attributes MISSING to dbo.Real, because the qualified-column resolution loop only checks "does this prefix resolve to a physical table," never "does the CTE actually expose a column by this name."
Why it matters
This attaches a real-looking, non-existent column name to a real table in the report. Someone reading the migration plan would reasonably believe dbo.Real has a Missing column — it doesn't.
Test
Pinned as a strict-xfail: tests/test_known_limitations.py::test_KL6_nonexistent_cte_output_column_not_attributed.
Possible fix path
Needs a per-CTE output allow-list (every column a CTE actually outputs, passthrough and aliased) checked before attribution — the inverse of the existing extract_cte_output_map (which maps alias → source, not "what does this CTE expose at all"). This is additive to the existing regex-based extraction, not obviously blocked on the AST backend.
What it does wrong
A column referenced through a CTE alias gets attributed to the underlying physical table even when the CTE never actually outputs a column by that name.
Xonly ever outputsIdandName—Missingis neither a passthrough column nor an aliased output, it does not exist onXat all. SPXray still attributesMISSINGtodbo.Real, because the qualified-column resolution loop only checks "does this prefix resolve to a physical table," never "does the CTE actually expose a column by this name."Why it matters
This attaches a real-looking, non-existent column name to a real table in the report. Someone reading the migration plan would reasonably believe
dbo.Realhas aMissingcolumn — it doesn't.Test
Pinned as a strict-xfail:
tests/test_known_limitations.py::test_KL6_nonexistent_cte_output_column_not_attributed.Possible fix path
Needs a per-CTE output allow-list (every column a CTE actually outputs, passthrough and aliased) checked before attribution — the inverse of the existing
extract_cte_output_map(which maps alias → source, not "what does this CTE expose at all"). This is additive to the existing regex-based extraction, not obviously blocked on the AST backend.