{"record":{"id":"12f4f165e6136267","repo":"clockworklabs/SpacetimeDB","slug":"query-does-not-return-plain-table-rows","errorCode":null,"errorMessage":"query does not return plain table rows","messagePattern":"query does not return plain table rows","errorType":"validation","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/core/src/host/wasm_common/module_host_actor.rs","lineNumber":194,"sourceCode":"    if the_query.trim().is_empty() {\n        return Ok(Vec::new());\n    }\n\n    // Views bypass RLS, since views should enforce their own access control procedurally.\n    let auth = AuthCtx::for_current(database_identity);\n    let schema_view = SchemaViewer::new(&*tx, &auth);\n\n    // Compile to subscription plans.\n    let (plans, has_params) = SubscriptionPlan::compile(the_query, &schema_view, &auth)?;\n    ensure!(\n        !has_params,\n        \"parameterized SQL is not supported for view materialization yet\"\n    );\n\n    // Validate shape and disallow views-on-views.\n    for plan in &plans {\n        let Some(source_schema) = plan.return_table() else {\n            bail!(\"query does not return plain table rows\");\n        };\n        if plan.reads_from_view(true) || plan.reads_from_view(false) {\n            bail!(\"view definition cannot read from other views\");\n        }\n        if source_schema.row_type != *expected_row_type {\n            bail!(\n                \"query returns `{}` but view expects `{}`\",\n                fmt_algebraic_type(&AlgebraicType::Product(source_schema.row_type.clone())),\n                fmt_algebraic_type(&AlgebraicType::Product(expected_row_type.clone())),\n            );\n        }\n    }\n\n    let op = FuncCallType::View(call_info.clone());\n    let mut metrics = ExecutionMetrics::default();\n    let mut rows = Vec::new();\n\n    let params = ExecutionParams::from_auth(&auth);","sourceCodeStart":176,"sourceCodeEnd":212,"githubUrl":"https://github.com/clockworklabs/SpacetimeDB/blob/9e0d92412ff2248f401a8ad12d535f2b5ac30912/crates/core/src/host/wasm_common/module_host_actor.rs#L176-L212","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Define the view over a single base table returning its rows verbatim: SELECT * FROM t, or an explicit column list matching t's row","Move aggregation/filtering logic into a reducer or a client-side subscription query instead of the view","Test the candidate query with `spacetime sql` first and confirm it yields plain table rows before CREATE VIEW"],"exampleFix":"-- before: aggregate rows have no source table\nCREATE VIEW v AS SELECT COUNT(*) FROM moves;\n\n-- after: plain rows of one base table\nCREATE VIEW v AS SELECT * FROM moves;","handlingStrategy":"validation","validationCode":"-- dry-run the intended view body; the result must be plain rows of one base table\nspacetime sql my-db 'SELECT * FROM moves LIMIT 1';\n-- if the query aggregates or computes expressions, it will not materialize as a view","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Write views as SELECT * FROM one_table, or an exact column list of it","Keep aggregates and expressions in reducers or client queries, not views","Test candidate view bodies with spacetime sql before CREATE VIEW","Remember parameterized SQL is also rejected for views today"],"tags":["sql","views","schema","materialization"],"backgroundTag":"sql-view-definition-invalid","analyzedSha":"9e0d92412ff2248f401a8ad12d535f2b5ac30912","analyzedAt":"2026-08-20T06:08:37.179Z","contentChangedAt":"2026-08-20T06:08:37.179Z","schemaVersion":2},"datasetVersion":"2026-09-14T00:17:10.932Z"}