Skip to content

[FEATURE] Support recursive CTEs (WITH RECURSIVE) for hierarchical queries over provider resources #787

Open

Description

@jeffreyaven

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

  1. Parser: accept WITH [RECURSIVE] grammar (investigate what the
    vitess-derived parser already carries vs what needs to be added to the
    fork).
  2. 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.
  3. 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.
  4. Non-recursive CTEs: confirm plain WITH handling 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 (UNION vs UNION ALL); confirm no additional guard is
    needed at the stackql layer.

Acceptance criteria

  • WITH RECURSIVE statements 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) WITH covered 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.

Metadata

Metadata

Assignees

Labels

enhancementNew feature or request

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions