Split out of #53 because this is graph-aware work rather than a lexical rule, and it was by a wide margin the largest single class in a real port.
PostgreSQL has no SELECT * EXCEPT (…), so every site must name its columns. In acuantia that was 141 sites across 132 files — the dominant cause of failure in the first run, and the thing that blocked most of the rest of the graph behind it.
It is fully decidable, but only with the graph:
- a
${ref(...)} to a declaration → its introspected columnTypes, in ordinal order
- a
${ref(...)} to a project view or table → that action's own select list, resolved recursively
- a CTE or subquery in the same file → its select list, same machinery
- a qualified star (
t.* except (…)) → whichever relation the alias binds to in the enclosing FROM/JOIN chain, expanded back as t.col
It must sweep to a fixpoint: a star over a view is only knowable once that view names its own columns, so a single pass leaves a long tail unresolved. Iterating took the acuantia result from 78 sites to 126 of 141.
Details that were each wrong on the first attempt
- BigQuery matches column names case-insensitively. An
EXCEPT (_data_date) list may not match a column introspected as _DATA_DATE. Match case-insensitively and emit the source's casing.
- Emit quoted where needed. A column that is not lowercase-safe must be emitted quoted or PostgreSQL folds it and the expansion fails at run time, not compile time.
- Ordinal order matters — it is the column order of the resulting relation.
- Enumerations do not track upstream. Once expanded, a new column added upstream will never appear, where
SELECT * would have picked it up. Worth emitting a marker recording the source relation and the exclusions so the expansion can be re-derived after a later introspect, and worth saying in the report that this is now a maintenance obligation.
What to do with the remainder
The residue is real: stars over relations whose columns are not statically knowable (an operation's output, a missing source). Those belong in the report (#56) with the reason, not silently left as broken SQL.
A regex implementation gets most of this and then hits a wall — mine did, on aliases shadowing column names and on files where naive string handling mis-parsed quotes. This wants the real parser, shared with #57.
Split out of #53 because this is graph-aware work rather than a lexical rule, and it was by a wide margin the largest single class in a real port.
PostgreSQL has no
SELECT * EXCEPT (…), so every site must name its columns. In acuantia that was 141 sites across 132 files — the dominant cause of failure in the first run, and the thing that blocked most of the rest of the graph behind it.It is fully decidable, but only with the graph:
${ref(...)}to a declaration → its introspectedcolumnTypes, in ordinal order${ref(...)}to a project view or table → that action's own select list, resolved recursivelyt.* except (…)) → whichever relation the alias binds to in the enclosing FROM/JOIN chain, expanded back ast.colIt must sweep to a fixpoint: a star over a view is only knowable once that view names its own columns, so a single pass leaves a long tail unresolved. Iterating took the acuantia result from 78 sites to 126 of 141.
Details that were each wrong on the first attempt
EXCEPT (_data_date)list may not match a column introspected as_DATA_DATE. Match case-insensitively and emit the source's casing.SELECT *would have picked it up. Worth emitting a marker recording the source relation and the exclusions so the expansion can be re-derived after a later introspect, and worth saying in the report that this is now a maintenance obligation.What to do with the remainder
The residue is real: stars over relations whose columns are not statically knowable (an operation's output, a missing source). Those belong in the report (#56) with the reason, not silently left as broken SQL.
A regex implementation gets most of this and then hits a wall — mine did, on aliases shadowing column names and on files where naive string handling mis-parsed quotes. This wants the real parser, shared with #57.