execute_sql column-scope guard false-refuses valid date-math SQL: DATEDIFF/EXTRACT date-part keyword read as an out-of-scope column (column_out_of_scope)

[sandeep-agami]

Summary

execute_sql's column-scope pre-flight guard (check_column_scope, error kind: column_out_of_scope) falsely refuses valid SQL that uses date-math functions. In DATEDIFF(<unit>, start, end) the first argument is a bare date-part keyword (second, minute, hour, day, month, quarter, year, ...), not a column. The guard collects the keyword as a column reference, fails to find it in the semantic model, and refuses the whole query, even though every real column it touches is declared.

Net effect: any query that measures a duration or a time bucket by unit (MTTR / resolution time / handle time in seconds or hours, aging in days, grouping by hour or quarter) is refused despite being correct SQL.

This is the same family as #148: the SQL is analyzed by a parser that is not dialect-aware, so a dialect-specific construct is mis-modeled.

Reproduction (generic tables, all real columns declared)

-- refused: 'second' read as an out-of-scope column
SELECT AVG(DATEDIFF(second, opened_at, resolved_at)) AS avg_seconds FROM incidents;

-- refused: 'hour' read as an out-of-scope column
SELECT AVG(DATEDIFF(hour, opened_at, closed_at)) AS avg_hours FROM tasks;

-- same class (date-part unit is a keyword, not a column)
SELECT EXTRACT(hour FROM created_at) AS h, COUNT(*) FROM events GROUP BY 1;

Observed refusal (generic form):

kind    = column_out_of_scope
columns = ["second"]     (also observed: ["hour"])
reason  = "query references column(s) not in the semantic model: second
           only columns declared on the model's tables may be queried."

Observed vs expected

• Observed: the date-part unit keyword (second, hour, ...) is reported as a column that is "not in the semantic model", and the valid query is refused.
• Expected: the unit keyword is a SQL date-part token, not a column, so it is excluded from the column-scope check. The query passes the guard because the only columns it actually references (opened_at, resolved_at, closed_at, created_at) are all declared.

Root cause

Guard: check_column_scope in packages/agami-core/src/semantic_model/runtime.py:716.

It collects referenced columns by walking tree.find_all(exp.Column):

• packages/agami-core/src/semantic_model/runtime.py:807 -> for col in tree.find_all(exp.Column):
• For a bare (unqualified) column judged against the single declared in-scope table, an unmatched name is added to offending at packages/agami-core/src/semantic_model/runtime.py:838.
• Refusal is built at packages/agami-core/src/semantic_model/runtime.py:843-850 and emitted as column_out_of_scope at packages/agami-core/src/execute_sql.py:959-964.

The SQL is parsed without a dialect. Every guard parse in this module uses sqlglot.parse_one(sql, error_level="ignore") with no dialect= (the shared _parse_sql at runtime.py:85-93, plus the standalone parse at runtime.py:743). sqlglot is pinned sqlglot>=20 (packages/agami-core/pyproject.toml:35).

Empirically (verified against sqlglot 30.10.0), the dialect-less parse of

DATEDIFF(second, opened_at, resolved_at)

produces:

DateDiff(
  this=Column(this=Identifier(this=second)),          <-- unit keyword parsed as a COLUMN
  expression=Column(this=Identifier(this=opened_at)),
  unit=Var(this=RESOLVED_AT))                          <-- real end column swallowed into the unit slot

So find_all(exp.Column) yields second and opened_at. second is a bare unqualified name; the only declared in-scope table is incidents; second is not one of its columns, so it is added to offending and the query is refused. This matches the observed columns=["second"] exactly. (Secondary problem: the real end column resolved_at is mis-slotted into the unit=Var position and therefore escapes the column-scope check entirely, i.e. the guard both false-refuses AND fails to check a real column.)

Parsing the same SQL with the target dialect fixes both:

sqlglot.parse_one("SELECT AVG(DATEDIFF(second, opened_at, resolved_at)) FROM incidents",
                   error_level="ignore", dialect="redshift")
# unit    -> Var(this=SECOND)         (NOT a Column, excluded from the scope check)
# columns -> opened_at, resolved_at   (both real, both declared)

Note: in sqlglot 30.10.0 with no dialect, EXTRACT(... FROM ...), DATE_TRUNC('unit', ...) and DATE_PART('unit', ...) are recognized natively and their unit is not represented as an exp.Column, so they do not reproduce today. DATEDIFF(unit, ...) is the confirmed reproducer. But all of these share the same class (the date-part is a keyword, not a column), and which representation sqlglot chooses is version- and dialect-dependent, so the fix and its regression tests should cover the whole family.

Fix directions

Make the column extraction in check_column_scope dialect- and AST-aware so date-part keyword arguments are never treated as columns:

1. Parse with the target dialect. The db type is already derived from the connection scheme in execute_sql.py (postgres / redshift / mysql / snowflake / bigquery / ...) and maps to a sqlglot dialect. Thread it into build_guard_context / the guard parses so DATEDIFF(unit, start, end) is modeled correctly (unit becomes exp.Var, not exp.Column). This also fixes the mis-slotted end column and is consistent with the dialect fix contemplated in execute_sql default_filters rewrite corrupts valid SQL: scope-blind injection (CTEs, EXISTS subqueries) + dialect-blind INTERVAL mangling #148.
2. Special-case the date-part position in the column walk: skip the unit argument of DATEDIFF / DATE_DIFF / TIMESTAMPDIFF, and the unit of EXTRACT / DATE_TRUNC / DATE_PART, before matching against declared columns (handle both the correctly-parsed exp.Var unit and the dialect-less exp.Column-in-unit-slot case).
3. Keyword allowlist (belt and suspenders): never flag a bare identifier that is a known date-part keyword (second, minute, hour, day, week, month, quarter, year, ...) as an out-of-scope column.

Option 1 is the most principled and aligns the whole guard battery with #148; option 3 is a cheap safety net.

Suggested regression test

Add cases to the column-scope guard tests where all real columns are declared and assert the guard allows:

• SELECT AVG(DATEDIFF(second, opened_at, resolved_at)) AS avg_seconds FROM incidents
• SELECT AVG(DATEDIFF(hour, opened_at, closed_at)) AS avg_hours FROM tasks
• SELECT EXTRACT(hour FROM created_at) AS h, COUNT(*) FROM events GROUP BY 1

Each must pass check_column_scope (no column_out_of_scope refusal). Keeping an EXTRACT case guards against a future sqlglot/dialect change regressing that variant into the same failure mode.

Related

• execute_sql default_filters rewrite corrupts valid SQL: scope-blind injection (CTEs, EXISTS subqueries) + dialect-blind INTERVAL mangling #148 (same "parser not dialect-aware" family: dialect-blind SQL analysis in runtime.py).