execute_sql default_filters rewrite corrupts valid SQL: scope-blind injection (CTEs, EXISTS subqueries) + dialect-blind INTERVAL mangling

[sandeep-agami]

Summary

apply_default_filters in packages/agami-core/src/semantic_model/runtime.py rewrites a query to AND-in each in-scope table's default_filters (row-scoping predicates such as <table>.is_deleted = false) before execution. The rewrite is not scope-aware and is dialect-blind, so it turns valid analytical SQL into invalid SQL for three common query shapes: queries whose base table lives inside a CTE, EXISTS / NOT EXISTS anti-joins, and any query carrying an INTERVAL 'N days' literal.

The docstring on apply_default_filters actually promises the correct behavior — "only the outermost SELECT is touched; a filter is injected only when its table appears as a base table there" — but the implementation does not enforce it. It collects table/alias scope with tree.find_all(exp.Table), which recurses into every CTE and subquery, and then splices all resolved predicates into the single outermost WHERE.

A server log line confirms the mechanism, e.g.:

[agami] applied default_filters: ['o.is_deleted = false', 'a.is_deleted = false']

Note the returned sql field in the response is the pre-rewrite text, so the Postgres error is the client's only window into the mangled post-rewrite SQL.

Reproduction

All three inputs are valid SQL that a client legitimately sends; each fails only after the rewrite. (Reproduced against the vendored sqlglot 30.10.0 by mimicking apply_default_filters: parse → collect scope via find_all(exp.Table) → inject into the outer WHERE → tree.sql().)

1. CTE outer-scope injection

Base table orders is referenced only inside a CTE:

WITH monthly AS (
  SELECT date_trunc('month', close_date) AS m, SUM(amount) AS rev
  FROM orders WHERE stage = 'won' GROUP BY 1)
SELECT m, rev, LAG(rev) OVER (ORDER BY m) FROM monthly ORDER BY m;

_tables_in_scope returns {'monthly': 'monthly', 'orders': 'orders'} (it sees orders inside the CTE). The orders.is_deleted = false filter is then injected into the outer query, whose FROM is only monthly:

... SELECT m, rev, LAG(rev) OVER (ORDER BY m) FROM monthly
    WHERE (orders.is_deleted = FALSE) ORDER BY m

→ Postgres: missing FROM-clause entry for table "orders".

2. Subquery-alias hoisting (EXISTS / NOT EXISTS anti-join)

SELECT a.id FROM accounts a
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.account_id = a.id AND o.stage = 'won')
  AND NOT EXISTS (SELECT 1 FROM contacts c WHERE c.account_id = a.id);

_tables_in_scope returns {'a': 'accounts', 'o': 'orders', 'c': 'contacts'} — the subquery aliases o/c are hoisted into the outer scope. The rewrite appends their filters to the outer WHERE:

SELECT a.id FROM accounts AS a
WHERE (EXISTS(...) AND NOT EXISTS(...))
  AND ((a.is_deleted = FALSE) AND (o.is_deleted = FALSE))

→ Postgres: missing FROM-clause entry for table "o" (o is not in scope in the outer WHERE). The observable normalizations (false → FALSE, added parenthesization, accounts → accounts AS a) confirm the whole statement is round-tripped through sqlglot's generator.

3. INTERVAL literal mangling

SELECT * FROM orders o WHERE o.created_at < CURRENT_DATE - INTERVAL '180 days';

Whenever the rewrite round-trips the SQL (i.e. any default filter is applied), the dialect-blind tree.sql() re-emits the interval in sqlglot's generic form:

... WHERE o.created_at < CURRENT_DATE - INTERVAL '180' DAYS AND (o.is_deleted = FALSE)

→ Postgres: syntax error at or near "DAYS". Passing the target dialect fixes it — tree.sql(dialect="postgres") emits the valid INTERVAL '180 DAYS'.

Observed vs. expected

• Observed: valid client SQL using a CTE, a correlated EXISTS/NOT EXISTS subquery, or an INTERVAL literal fails at execution after the default-filter rewrite.
• Expected: each default_filters predicate is applied only to the query block whose FROM actually introduces that table/alias, dialect-specific literals survive the round-trip unchanged, and the rewrite is a semantics-preserving transform of correct input.

Root cause

apply_default_filters — runtime.py:1500-1565:

1. Scope-blind injection (failure classes 1 & 2). Scope is built by _tables_in_scope (runtime.py:1387-1394) via tree.find_all(exp.Table), which walks the entire tree — CTE bodies and subqueries included — returning one flat alias -> table map with no notion of which query block each alias belongs to (scope = _tables_in_scope(tree), runtime.py:1535). Every resolved predicate is then AND-ed into the single outermost WHERE (runtime.py:1555-1562). Nothing checks that a table/alias is actually a base table of that outer SELECT, so filters for CTE-internal tables and subquery aliases land in a scope where the alias is undefined. This contradicts the function's own docstring at runtime.py:1508-1516.

2. Dialect-blind round-trip (failure class 3, plus the cosmetic churn seen above). Both the parse (sqlglot.parse_one(sql, error_level="ignore"), runtime.py:1529; and the GuardContext parse at runtime.py:91) and the re-emit (tree.sql(), runtime.py:1563) run with no dialect, so sqlglot normalizes to its generic dialect and reformats INTERVAL 'N days' into INTERVAL 'N' DAYS, which Postgres rejects.

Impact

Any query using a CTE, a correlated / EXISTS subquery, or an INTERVAL literal can fail even when the client SQL is correct. This blocks a large and common class of analytical queries — window functions over CTEs, anti-joins, and aging conditions — on any org whose model declares default_filters.

Suggested fix directions

1. Make default-filter injection scope-aware. Apply each predicate only to the specific query block whose FROM/JOIN introduces that table/alias, rather than flattening all tables and injecting into the outermost WHERE. Prefer operating on the AST per-scope (sqlglot's scope/Scope traversal, or by injecting into each exp.Select's own WHERE based on that select's direct sources) over string splicing. At minimum, restrict the outer injection to the base tables of the outermost SELECT (exclude CTE-internal tables and subquery-local aliases) so a filter is never emitted into a scope where its alias is undefined — matching what the docstring already promises.
2. Fix INTERVAL / dialect handling. Round-trip with the target engine's dialect on both parse_one(..., dialect=...) and tree.sql(dialect=...) so dialect-specific literals (and casing/parenthesization) survive unchanged. tree.sql(dialect="postgres") already emits a valid INTERVAL '180 DAYS' in the repro above.
3. Return the executed SQL. The sql returned in the response should reflect what actually ran (post-rewrite), so a client can diagnose failures without inferring the mangled text from the Postgres error.

Suggested regression tests

Add cases that round-trip through apply_default_filters / execute_sql and assert the result is semantically unchanged (executes successfully and returns the same rows as the un-rewritten query on a table that declares a default_filters predicate):

• CTE: a WITH ... SELECT where the filtered base table appears only inside the CTE — assert the filter lands inside the CTE body and the outer WHERE gains no out-of-scope predicate.
• EXISTS / NOT EXISTS: an anti-join whose filtered tables are subquery-local — assert each filter stays inside its own subquery and no subquery alias leaks into the outer WHERE.
• INTERVAL: a query with ... < CURRENT_DATE - INTERVAL '180 days' on a table that declares a default_filters predicate — assert the emitted SQL still contains a valid INTERVAL '180 days' / INTERVAL '180 DAYS' and executes without a syntax error.

[sandeep-agami]

Follow-up: the blast radius is much larger than CTE / EXISTS / INTERVAL — and it is gated by whether the datasource declares default_filters.

I ran a batch of 36 advanced queries (UNION / UNION ALL, INTERSECT / EXCEPT, scalar and correlated subqueries, IN / NOT IN / ANY / ALL, CROSS JOIN, LEFT / RIGHT / FULL OUTER JOIN, derived tables, and HAVING-with-subquery) across three datasources on three different engines. The failure is not specific to CTE / EXISTS / INTERVAL — it hits every construct that introduces more than one FROM scope, and only on datasources that declare default_filters.

It is gated by default_filters

• A datasource that declares is_deleted = false default filters: 11 of 12 advanced queries failed, every one with missing FROM-clause entry for <alias>.
• Two datasources with no default filters: 9/9 and 9/9 passed — identical construct mix, no corruption. Nothing is injected, so nothing is mis-scoped.

So whether a query survives depends on the datasource config, not the SQL: the same shape passes on a datasource with no default filters and fails on one that has them.

Constructs confirmed broken

All generic; each is valid SQL that only fails after the rewrite injects <table>.is_deleted = false into the wrong query block (the alias is then out of scope in the block the predicate is spliced into):

construct	where the injected predicate lands wrongly
scalar subquery in SELECT — SELECT o.id, (SELECT AVG(amount) FROM orders o2) FROM orders o	o2.is_deleted hoisted to the outer query
> ALL (subquery)	subquery alias hoisted to outer
derived table in FROM — ... JOIN (SELECT ... FROM orders o GROUP BY ...) d ON ...	o.is_deleted injected into the outer/final query
UNION ALL (+ window over it)	a branch's table alias injected into the wrapping query
INTERSECT / EXCEPT	set-operation branch alias
CROSS JOIN + LEFT JOIN	joined-table alias
LEFT / FULL OUTER JOIN	joined-table alias
NOT IN / NOT EXISTS	subquery alias hoisted into the outer WHERE
HAVING with subquery	subquery alias

Reframe

The defect is not "CTE / EXISTS / INTERVAL fail." It is: scope-blind default-filter injection breaks any query with more than one FROM scope, on any datasource that declares default_filters. (The INTERVAL '180 days' → INTERVAL '180' DAYS case is a separate, dialect-less-round-trip facet of the same code path.) In practical terms, on a governed (default-filtered) datasource the executor cannot run subqueries, set operations, derived tables, or outer joins — a large fraction of real analytical SQL.

Fix direction (unchanged, restated for the wider scope)

Attach each default-filter predicate only to the specific query block whose FROM introduces that table/alias — for every block: the main query, each CTE, each subquery, each set-operation branch, and each derived table — resolved over the AST rather than by string splicing. A regression suite should cover: scalar subquery, > ALL, derived table, UNION ALL, INTERSECT, EXCEPT, CROSS JOIN, LEFT/FULL OUTER JOIN, NOT IN/NOT EXISTS, and HAVING-with-subquery — each round-tripping through the filter injection with unchanged semantics, on a datasource that declares default filters.