clockworklabs/SpacetimeDB · error

Event tables cannot be used as the lookup table in subscript

Error message

Event tables cannot be used as the lookup table in subscription joins

What it means

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.

Source

Thrown at crates/subscription/src/lib.rs:653

                }
                _ => {}
            });
            (table_ids, table_aliases)
        }

        let mut subscriptions = vec![];
        let mut physical_plans = vec![];
        let params = ExecutionParams::from_auth(auth);

        for plan in plans {
            let plan_opt = plan.clone().optimize()?;

            if has_non_index_join(&plan_opt) {
                bail!("Subscriptions require indexes on join columns")
            }

            if plan_opt.reads_from_event_table() {
                bail!("Event tables cannot be used as the lookup table in subscription joins")
            }

            let (table_ids, table_aliases) = table_ids_for_plan(&plan);

            let fragments = Fragments::compile_from_plan(&plan, &table_aliases)?;
            let is_join = fragments.insert_plans.len() > 1 && fragments.delete_plans.len() > 1;

            let mut view_ids = HashSet::new();
            plan_opt.collect_views(&mut view_ids);

            let metadata = SubscriptionMetadata {
                table_ids,
                return_schema: plan_opt.return_table(),
                view_ids: view_ids.into_iter().collect(),
                reads_anonymous_view: plan_opt.reads_from_view(true),
                reads_non_anonymous_view: plan_opt.reads_from_view(false),
                search_args: plan_opt.physical_plan().search_args(&params),
                join_edge: Self::join_edge_for_plan(&plan_opt, return_id, is_join, &params),

View on GitHub (pinned to 6dee26c6ef)

Solutions

1. 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.
2. Drop the join and subscribe to the event table alone (`SELECT * FROM ChatLog`), resolving player data client-side.
3. 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.

Example fix

-- before (invalid: event table as lookup/rhs table)
SELECT p.* FROM Player p JOIN ChatLog c ON c.player_id = p.id;
-- after (valid: event table is the outer, subscribed table)
SELECT c.*, p.name FROM ChatLog c JOIN Player p ON p.id = c.player_id;

// Before subscribing, verify no event table appears as a join lookup side.
// Keep the list of your module's event table names and check each JOIN target:
const EVENT_TABLES: &[&str] = &["ChatLog", "AuditEvent"];
fn event_as_lookup_side(sql: &str) -> bool {
    // every JOIN ... ON target must not be an event table
    for cap in regex::Regex::new(r"(?i)JOIN\s+(\w+)").unwrap().captures_iter(sql) {
        if EVENT_TABLES.contains(&&cap[1]) {
            return true;
        }
    }
    false
}
assert!(!event_as_lookup_side(&query), "event table used as join lookup side");

// On the client, surface the planner rejection distinctly:
match client.subscribe(vec![query]).await {
    Ok(_) => {}
    Err(e) if e.to_string().contains("Event tables cannot be used as the lookup table") => {
        return Err(anyhow!("rewrite the subscription: put the event table first and join regular tables to it"));
    }
    Err(e) => return Err(e),
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• Index '${indexLabel}' on table '${tableLabel}' must define a
• Invalid sequence: start value {} is out of bounds for sequen
• Invalid sequence: max_value must be greater than min_value
• Invalid sequence: increment must be non-zero
• Invalid sequence: increment must be less than or equal to th

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