{"record":{"id":"df7a4fa790751a0d","repo":"clockworklabs/SpacetimeDB","slug":"query-returns-but-view-expects","errorCode":null,"errorMessage":"query returns `{}` but view expects `{}`","messagePattern":"query returns `(.+?)` but view expects `(.+?)`","errorType":"validation","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/core/src/host/wasm_common/module_host_actor.rs","lineNumber":200,"sourceCode":"    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);\n\n    for plan in plans {\n        // Track read sets for all tables involved in this plan.\n        // TODO(jsdt): This means we will rerun the view and query for any change to these tables, so we should optimize this asap.\n        for table_id in plan.table_ids() {\n            tx.record_table_scan(&op, table_id);","sourceCodeStart":182,"sourceCodeEnd":218,"githubUrl":"https://github.com/clockworklabs/SpacetimeDB/blob/9e0d92412ff2248f401a8ad12d535f2b5ac30912/crates/core/src/host/wasm_common/module_host_actor.rs#L182-L218","documentation":"For each plan, the host compares the row type of the source table the query returns against the view's expected row type. If column count, order, or types differ, view creation is rejected and both row types are printed so the exact mismatch is visible. The comparison is on the full product type, not just column names.","triggerScenarios":"SELECTing a subset of columns or a reordered list; replacing a column with an expression or cast; the view declaration expecting a row type that no longer matches the source table after the table's schema changed.","commonSituations":"Hand-written column lists drifting from the table definition; a table altered (column added/retyped) while dependent views stayed unchanged; reordering columns for convenience in the view.","solutions":["If the view should mirror the whole row, use SELECT * FROM t","Otherwise list exactly the columns, in order and with matching types, of the expected row type shown in the error message","After altering a table, recreate dependent views to match the new schema","Diff the two row types printed in the message to find the offending column"],"exampleFix":"-- before: subset drifts from the expected row (id, name, email)\nCREATE VIEW v AS SELECT name, email FROM users;\n\n-- after: match the row type exactly\nCREATE VIEW v AS SELECT id, name, email FROM users;","handlingStrategy":"validation","validationCode":"-- confirm the source row shape before writing the view\nspacetime sql my-db 'SELECT * FROM users LIMIT 0';  -- shows exact columns/order\n-- then mirror exactly those columns (and order) in CREATE VIEW","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Default to SELECT * when the view should mirror the row","After any table alter, recreate dependent views in the same change","Use the two row types printed by the error to pinpoint the divergent column","Avoid expressions/casts in view column lists - they change the row type"],"tags":["sql","views","schema","type-mismatch"],"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"}