column_out_of_scope falsely rejects DATEDIFF(second/hour/…) and EXTRACT — date-part keywords misread as columns

[sandeep-agami]

Title

`column_out_of_scope` safety check falsely rejects queries with `DATEDIFF(second/hour/…)` and `EXTRACT(…)` — keywords read as undeclared columns.

Severity

High — blocks all time-bucketing and duration queries (MTTR, aging, response time, handle time, etc.).

Impact

The `preflight.column_out_of_scope` guard rule rejects any query using a date-part keyword (`second`, `hour`, `day`, `month`, `quarter`, `year`) inside `DATEDIFF(unit, ...)` or `EXTRACT(unit FROM ...)`, refusing the entire query with kind=column_out_of_scope.

This prevents analytics on duration/time-based metrics:

• Mean time to resolution / handle time (MTTR, handle time in hours)
• Account/invoice aging (days past due, days since creation)
• Response/resolution time bucketing
• Any temporal metric that uses `DATEDIFF`/`EXTRACT` with a time unit

Root cause

The `column_out_of_scope` check scans the SQL for identifier tokens and matches them against the model's declared columns. It does not parse the SQL syntax, so it cannot distinguish:

• A column reference: `t.opened_at` ✓
• A date-part keyword: `DATEDIFF(second, t.opened_at, t.resolved_at)` → reads `second` as a column name ✗

Reproduction

Valid query using DATEDIFF:
```sql
SELECT ROUND(AVG(DATEDIFF(second, created_at, updated_at) / 86400.0), 2) AS avg_duration_days
FROM orders
WHERE updated_at IS NOT NULL
```

Expected: execute normally.
Actual: refused with kind=column_out_of_scope, cols=['second'] — "references column(s) not in the semantic model: second".

Same issue with EXTRACT(hour FROM ...) — all date-part keywords trigger this.

Fix directions

1. AST-aware parsing (preferred): Parse SQL to AST; skip first argument of DATEDIFF / EXTRACT from column-reference check.
2. Keyword allowlist: Maintain a list of built-in date-part keywords, exclude from "undeclared column" check.
3. Regex pattern: Identify DATEDIFF(KEYWORD, ...) / EXTRACT(KEYWORD FROM ...) patterns before scanning columns.

Regression tests

1. Query with DATEDIFF(second, ...) on two timestamp columns — should execute, not refuse.
2. Query with EXTRACT(hour FROM ...) — should execute, not refuse.
3. Query with DATEDIFF(day, ...) in a WHERE aging clause — should not refuse.
4. Verify that actual undeclared columns are still caught and refused.