{"record":{"id":"f82ab5c56814d5b8","repo":"clockworklabs/SpacetimeDB","slug":"event-tables-cannot-be-used-as-the-lookup-table-in","errorCode":null,"errorMessage":"Event tables cannot be used as the lookup table in subscription joins","messagePattern":"Event tables cannot be used as the lookup table in subscription joins","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"crates/subscription/src/lib.rs","lineNumber":653,"sourceCode":"                }\n                _ => {}\n            });\n            (table_ids, table_aliases)\n        }\n\n        let mut subscriptions = vec![];\n        let mut physical_plans = vec![];\n        let params = ExecutionParams::from_auth(auth);\n\n        for plan in plans {\n            let plan_opt = plan.clone().optimize()?;\n\n            if has_non_index_join(&plan_opt) {\n                bail!(\"Subscriptions require indexes on join columns\")\n            }\n\n            if plan_opt.reads_from_event_table() {\n                bail!(\"Event tables cannot be used as the lookup table in subscription joins\")\n            }\n\n            let (table_ids, table_aliases) = table_ids_for_plan(&plan);\n\n            let fragments = Fragments::compile_from_plan(&plan, &table_aliases)?;\n            let is_join = fragments.insert_plans.len() > 1 && fragments.delete_plans.len() > 1;\n\n            let mut view_ids = HashSet::new();\n            plan_opt.collect_views(&mut view_ids);\n\n            let metadata = SubscriptionMetadata {\n                table_ids,\n                return_schema: plan_opt.return_table(),\n                view_ids: view_ids.into_iter().collect(),\n                reads_anonymous_view: plan_opt.reads_from_view(true),\n                reads_non_anonymous_view: plan_opt.reads_from_view(false),\n                search_args: plan_opt.physical_plan().search_args(&params),\n                join_edge: Self::join_edge_for_plan(&plan_opt, return_id, is_join, &params),","sourceCodeStart":635,"sourceCodeEnd":671,"githubUrl":"https://github.com/clockworklabs/SpacetimeDB/blob/6dee26c6efc2856793e12b148a59742964f5d783/crates/subscription/src/lib.rs#L635-L671","documentation":"Thrown by the SpacetimeDB subscription planner (crates/subscription/src/lib.rs:653) when compiling a subscription query. After optimization, the plan is inspected with reads_from_event_table(), which returns true if any IxJoin uses a table with is_event as its right-hand lookup table (crates/physical-plan/src/plan.rs:1176-1181). Event tables are append-only logs that cannot be incrementally maintained as join lookup sides, so such subscriptions are rejected. Subscribing to an event table directly as the outer (FROM) table is allowed; only its use as a join lookup table is not.","triggerScenarios":"Calling subscribe with a query like `SELECT p.* FROM Player p JOIN ChatLog c ON c.player_id = p.id` where ChatLog is declared with #[spacetimedb::table(event, ...)]. The join must already pass the has_non_index_join check (indexes exist on the join columns), then the rhs-is-event check fires. Any IxJoin whose rhs table schema has is_event == true in any plan of the subscription triggers it.","commonSituations":"Modeling history/log data as event tables and then writing enrichment queries that join current-state tables against them; converting a regular table to an event table in a module and forgetting to update subscription SQL; queries authored by analogy with regular tables where any side of a join is legal.","solutions":["Rewrite the query so the event table is the outer (subscribed) table: `SELECT c.*, p.* FROM ChatLog c JOIN Player p ON p.id = c.player_id` - the regular indexed table becomes the lookup side.","Drop the join and subscribe to the event table alone (`SELECT * FROM ChatLog`), resolving player data client-side.","If you need the event table on the lookup side, replace it with a regular indexed table (remove the `event` table attribute) so it can be incrementally maintained."],"exampleFix":"-- before (invalid: event table as lookup/rhs table)\nSELECT p.* FROM Player p JOIN ChatLog c ON c.player_id = p.id;\n-- after (valid: event table is the outer, subscribed table)\nSELECT c.*, p.name FROM ChatLog c JOIN Player p ON p.id = c.player_id;","handlingStrategy":"validation","validationCode":"// Before subscribing, verify no event table appears as a join lookup side.\n// Keep the list of your module's event table names and check each JOIN target:\nconst EVENT_TABLES: &[&str] = &[\"ChatLog\", \"AuditEvent\"];\nfn event_as_lookup_side(sql: &str) -> bool {\n    // every JOIN ... ON target must not be an event table\n    for cap in regex::Regex::new(r\"(?i)JOIN\\s+(\\w+)\").unwrap().captures_iter(sql) {\n        if EVENT_TABLES.contains(&&cap[1]) {\n            return true;\n        }\n    }\n    false\n}\nassert!(!event_as_lookup_side(&query), \"event table used as join lookup side\");","typeGuard":null,"tryCatchPattern":"// On the client, surface the planner rejection distinctly:\nmatch client.subscribe(vec![query]).await {\n    Ok(_) => {}\n    Err(e) if e.to_string().contains(\"Event tables cannot be used as the lookup table\") => {\n        return Err(anyhow!(\"rewrite the subscription: put the event table first and join regular tables to it\"));\n    }\n    Err(e) => return Err(e),\n}","preventionTips":["Keep a project-level list of event tables and lint subscription SQL so event tables only ever appear as the first (FROM) table.","Write subscription queries with the event table outermost from the start; treat it as the stream you subscribe to.","Add an integration test that subscribes with the exact production queries during CI so planner rejections surface before deploy."],"tags":["spacetimedb","subscription","sql","index-join","event-tables"],"backgroundTag":"invalid-subscription-query","analyzedSha":"6dee26c6efc2856793e12b148a59742964f5d783","analyzedAt":"2026-08-20T06:08:37.179Z","contentChangedAt":"2026-08-20T06:08:37.179Z","schemaVersion":2},"datasetVersion":"2026-09-14T00:17:10.932Z"}