clockworklabs/SpacetimeDB · error · anyhow::Error

query does not return plain table rows

Error message

query does not return plain table rows

What it means

When a VIEW is created, its SQL is compiled into subscription plans and every plan must resolve to a single physical base table (plan.return_table()). Queries that aggregate, compute scalar expressions, join, or otherwise produce rows that are not verbatim rows of one table have no source-table schema to materialize from, so view creation is rejected.

Source

Thrown at crates/core/src/host/wasm_common/module_host_actor.rs:194

    if the_query.trim().is_empty() {
        return Ok(Vec::new());
    }

    // Views bypass RLS, since views should enforce their own access control procedurally.
    let auth = AuthCtx::for_current(database_identity);
    let schema_view = SchemaViewer::new(&*tx, &auth);

    // Compile to subscription plans.
    let (plans, has_params) = SubscriptionPlan::compile(the_query, &schema_view, &auth)?;
    ensure!(
        !has_params,
        "parameterized SQL is not supported for view materialization yet"
    );

    // Validate shape and disallow views-on-views.
    for plan in &plans {
        let Some(source_schema) = plan.return_table() else {
            bail!("query does not return plain table rows");
        };
        if plan.reads_from_view(true) || plan.reads_from_view(false) {
            bail!("view definition cannot read from other views");
        }
        if source_schema.row_type != *expected_row_type {
            bail!(
                "query returns `{}` but view expects `{}`",
                fmt_algebraic_type(&AlgebraicType::Product(source_schema.row_type.clone())),
                fmt_algebraic_type(&AlgebraicType::Product(expected_row_type.clone())),
            );
        }
    }

    let op = FuncCallType::View(call_info.clone());
    let mut metrics = ExecutionMetrics::default();
    let mut rows = Vec::new();

    let params = ExecutionParams::from_auth(&auth);

View on GitHub (pinned to 9e0d92412f)

Solutions

1. Define the view over a single base table returning its rows verbatim: SELECT * FROM t, or an explicit column list matching t's row
2. Move aggregation/filtering logic into a reducer or a client-side subscription query instead of the view
3. Test the candidate query with `spacetime sql` first and confirm it yields plain table rows before CREATE VIEW

Example fix

-- before: aggregate rows have no source table
CREATE VIEW v AS SELECT COUNT(*) FROM moves;

-- after: plain rows of one base table
CREATE VIEW v AS SELECT * FROM moves;

-- dry-run the intended view body; the result must be plain rows of one base table
spacetime sql my-db 'SELECT * FROM moves LIMIT 1';
-- if the query aggregates or computes expressions, it will not materialize as a view

When it happens

Trigger: CREATE VIEW whose SELECT uses aggregates (COUNT/SUM/MIN/...), scalar expressions on columns, joins across tables, or any projection whose output shape is not exactly one table's row type; parameterized SQL is likewise rejected earlier with a distinct message.

Common situations: Trying to define dashboard-style aggregate views; porting arbitrary stored queries from another SQL engine; misunderstanding SpacetimeDB views as general cached queries rather than materialized table rows.

Related errors

• view definition cannot read from other views
• Index '${indexLabel}' on table '${tableLabel}' must define a
• query returns `{}` but view expects `{}`
• Cannot define RLS rule on private table: {}. Please make tab
• Failed to read value from the `{}` column of `{}` for table_

AI-assisted analysis of clockworklabs/SpacetimeDB@9e0d92412f (2026-08-20). Data as JSON: /api/errors/12f4f165e6136267. Report an issue: GitHub.