clockworklabs/SpacetimeDB · error

Failed to determine TableId for query

Error message

Failed to determine TableId for query

What it means

A subscription must return rows of a single identifiable table so the server can compute insert/delete deltas. After parsing and type checking, compile_subscription calls plan.return_table_id(); if the projection does not resolve to a table's row type, the id is None and compilation fails.

Source

Thrown at crates/query/src/lib.rs:38

/// DIRTY HACK ALERT: Maximum allowed length, in UTF-8 bytes, of SQL queries.
/// Any query longer than this will be rejected.
/// This prevents a stack overflow when compiling queries with deeply-nested `AND` and `OR` conditions.
const MAX_SQL_LENGTH: usize = 50_000;

pub fn compile_subscription(
    sql: &str,
    tx: &impl SchemaView,
    auth: &AuthCtx,
) -> Result<(Vec<ProjectPlan>, TableId, TableName, bool)> {
    if sql.len() > MAX_SQL_LENGTH {
        bail!("SQL query exceeds maximum allowed length: \"{sql:.120}...\"")
    }

    let (plan, mut has_param) = parse_and_type_sub(sql, tx, auth)?;

    let Some(return_id) = plan.return_table_id() else {
        bail!("Failed to determine TableId for query")
    };

    let Some(return_name) = tx.schema_for_table(return_id).map(|schema| schema.table_name.clone()) else {
        bail!("TableId `{return_id}` does not exist")
    };

    // Resolve any RLS filters
    let plan_fragments = resolve_views_for_sub(tx, plan, auth, &mut has_param)?
        .into_iter()
        .map(compile_select)
        .collect::<Vec<_>>();

    // Does this subscription read from a client-specific view?
    // If so, it is as if the view is parameterized by `:sender`.
    // We must know this in order to generate the correct query hash.
    let reads_view = plan_fragments.iter().any(|plan| plan.reads_from_view(false));

    Ok((plan_fragments, return_id, return_name, has_param || reads_view))

View on GitHub (pinned to 6dee26c6ef)

Solutions

1. Rewrite the subscription to return table rows, e.g. SELECT * FROM t WHERE ....
2. For aggregates, maintain a summary table updated by reducers and subscribe to that table.
3. Compute derived values client-side from the subscribed rows.

Example fix

-- before: aggregate return type has no TableId
SELECT COUNT(*) FROM events;

-- after: subscribe to rows (or to a reducer-maintained summary table)
SELECT * FROM events;
-- or: SELECT * FROM event_stats;

-- validate the shape before subscribing: the query must return rows of one table
-- SELECT * FROM t;          -- ok
-- SELECT t.* FROM t JOIN ..; -- ok (row type of t)
-- SELECT COUNT(*) FROM t;    -- will fail: no TableId

try {
  await db.subscription.build(["SELECT COUNT(*) FROM orders"]).subscribe();
} catch (e: any) {
  if (String(e.message).includes("Failed to determine TableId")) {
    // subscribe to table rows and aggregate client-side
  }
}

When it happens

Trigger: Subscribing to a query whose return type is not a table row: pure aggregates (SELECT COUNT(*) ...), scalar-only projections, or result shapes with no backing table.

Common situations: Trying to use subscriptions as continuous aggregates or live counters; selecting only computed expressions; porting ad-hoc SQL queries into subscription calls.

Related errors

• Could not determine return type during RLS resolution
• Subscriptions must have at least one query
• Subscriptions must be SQL strings or typed queries
• Estimated cardinality ({estimate} rows) exceeds limit ({limi
• Unsubscribe has already been called

AI-assisted analysis of clockworklabs/SpacetimeDB@6dee26c6ef (2026-08-20). Data as JSON: /api/errors/5f3a2686cc3f3a95. Report an issue: GitHub.