Repository navigation
Description
Current ownership after the SDK migration
Current bounded iterative execution work is tracked in stackql-labs/omnisdk#50. StackQL retains SQL semantics, integration and end-to-end acceptance.
Upstream dependencies
Keep this issue open until the relevant upstream changes are available, any required StackQL integration is complete, and the acceptance checks here pass. Upstream issue closure alone is not sufficient.
Summary
WITH RECURSIVE is not currently accepted by stackql. This is a frontend
(parser/planner) limitation, not an engine limitation: recursive CTEs are
part of the ANSI standard. They are fully supported in postgres and have
been core SQLite since 3.8.3 (2014) and are fully supported by the embedded
engine (verified against both the retired cgo build, SQLite 3.45.1, and the
current pure Go modernc.org/sqlite engine, SQLite 3.53.4 - the
PRAGMA compile_options diff from the modernc migration shows no
SQLITE_OMIT_CTE in either build). The postgres backend supports
WITH RECURSIVE natively as well, so the feature is backend-agnostic once
the frontend accepts it.
This issue covers investigating and implementing frontend support so that
recursion itself is delegated to the backend engine - the executor does not
need to implement recursion semantics.
Current behavior
A WITH RECURSIVE statement fails at the parse/analysis layer (stackql /
vitess-derived parser error), before reaching the embedded engine. The
engine never sees the statement.
Canonical use case: Google Resource Manager hierarchy
Cloud resource hierarchies are naturally recursive. Google Resource Manager
models an organization as a tree: organization -> folders -> subfolders ->
projects, with each node carrying a parent reference. Walking a subtree
of arbitrary depth requires recursion.
Walk all folders beneath an organization:
WITH RECURSIVE folder_tree AS (
SELECT name, displayName, parent
FROM google.cloudresourcemanager.folders
WHERE parent = 'organizations/123456789012'
UNION ALL
SELECT f.name, f.displayName, f.parent
FROM google.cloudresourcemanager.folders f
JOIN folder_tree t ON f.parent = t.name
)
SELECT * FROM folder_tree;Extend to projects for the full tree:
WITH RECURSIVE folder_tree AS (
SELECT name, parent FROM google.cloudresourcemanager.folders
WHERE parent = 'organizations/123456789012'
UNION ALL
SELECT f.name, f.parent
FROM google.cloudresourcemanager.folders f
JOIN folder_tree t ON f.parent = t.name
)
SELECT p.projectId, p.name, p.parent
FROM google.cloudresourcemanager.projects p
JOIN folder_tree t ON p.parent = t.name;Other hierarchical targets with the same shape: AWS Organizations
(OUs/accounts), Azure management groups, IAM policy inheritance, resource
dependency graphs.
Proposed approach
- Parser: accept
WITH [RECURSIVE]grammar (investigate what the
vitess-derived parser already carries vs what needs to be added to the
fork). - Analyzer/planner: walk the WITH list; identify provider table
references in the anchor member and the recursive member; treat
references to the CTE's own name as local relations, not provider
resources. - Materialization: materialize each referenced provider table once,
rewrite names as per existing planning, and hand the rewritten
statement to the backend - the engine performs the recursion natively
over the materialized tables. - Non-recursive CTEs: confirm plain
WITHhandling as part of the
same work (likely shares most of the plumbing).
Investigation items
- Predicate pushdown semantics inside the recursive member: the join
predicate against the CTE (f.parent = t.name) varies per iteration and
cannot be pushed into per-iteration provider calls. Proposed v1: fetch
the full provider result set once (existing list semantics), recurse
locally. Document this so users understand one list call feeds the whole
walk. - Interaction with existing CTE/subquery handling in the planner, if any.
- Behavior across both backends (embedded sqlite, postgres) - should be
identical given native engine support. - Depth/cycle safety: SQLite and postgres both handle cycles per SQL
semantics (UNIONvsUNION ALL); confirm no additional guard is
needed at the stackql layer.
Acceptance criteria
WITH RECURSIVEstatements parse, plan, and execute against the
embedded backend, with provider tables referenced in anchor and
recursive members materialized correctly.- Robot test coverage for at least the canonical folder-walk case
(registry-mocked Google Resource Manager hierarchy fixture). - Plain (non-recursive)
WITHcovered by the same tests if implemented in
the same change. - Documented in the query language reference, including the
materialization semantics noted above.
Out of scope
- Iterative/per-level provider fetching driven by recursion depth
(possible future optimization). - Engine-level changes: validate the required iterative execution contract in omnisdk okta-pagination-saml-query-use-case #50; do not assume any-sdk behavior establishes migration parity;
recursion executes natively in the backend.
Activity