JOIN-based subscription / client_visibility_filter does not retract a row when an UPDATE moves it out of the join result set (no delete delta) — self-join RLS ghost

[starpluckerstudios]

Runtime: SpacetimeDB 2.8.2 (CLI commit 524b4487), C# module + SpacetimeDB.ClientSDK 2.8.2, self-hosted (Docker, http://localhost:3000).

Summary

When a row leaves a JOIN-based subscription/[ClientVisibilityFilter] result set because of an UPDATE (not a DELETE), the runtime stops replicating that row to the client but never sends the delete delta. The client keeps a stale copy of the row forever (until unsubscribe/reconnect). The same scenario over a plain-column filter retracts correctly, so the defect is specific to the join.

Expected vs actual

• Expected: after the update moves the row out of the (RLS) join result, the subscriber receives a delete for that row (its OnDelete fires; the row leaves the client cache).
• Actual: no delete is delivered; the row stays in the client cache, frozen at its pre-update values.

Isolating experiments (all on the same runtime)

Subscription filter	UPDATE moves row out of filter → retracts?
No RLS; client SELECT * FROM t WHERE bucket = 0	✅ retracts
Plain-column RLS WHERE bucket = 0; client SELECT * FROM t	✅ retracts
Self-join RLS (below)	❌ no retraction (stale row persists)

Controls: an explicit DELETE of a matching row retracts correctly, and an UPDATE that moves a row into the filter inserts correctly. Only the join + update-out combination fails.

Minimal repro

Module (Public = true so no read auth needed; the RLS self-join is the trigger):

using SpacetimeDB;

public static partial class Module
{
    [SpacetimeDB.Table(Accessor = "entity", Public = true)]
    public partial struct entity
    {
        [PrimaryKey, AutoInc] public ulong Id;
        [SpacetimeDB.Index.BTree] public Identity Owner;
        [SpacetimeDB.Index.BTree] public long Cell;
    }

    [SpacetimeDB.Reducer]
    public static void Spawn(ReducerContext ctx, long cell) =>
        ctx.Db.entity.Insert(new entity { Owner = ctx.Sender, Cell = cell });

    [SpacetimeDB.Reducer]
    public static void SetCell(ReducerContext ctx, ulong id, long cell)
    {
        if (ctx.Db.entity.Id.Find(id) is { } e) { e.Cell = cell; ctx.Db.entity.Id.Update(e); }
    }

#pragma warning disable STDB_UNSTABLE
    // Roster: an account sees its own rows (plain equality).
    [SpacetimeDB.ClientVisibilityFilter]
    public static readonly Filter Roster = new Filter.Sql("SELECT * FROM entity WHERE Owner = :sender");

    // Spatial: an account also sees OTHER accounts' rows sharing its own row's Cell (self-join).
    [SpacetimeDB.ClientVisibilityFilter]
    public static readonly Filter Spatial = new Filter.Sql(
        "SELECT q.* FROM entity p JOIN entity q ON p.Cell = q.Cell WHERE p.Owner = :sender");
#pragma warning restore STDB_UNSTABLE
}

Two clients A and B (distinct identities):

1. A Spawn(0); B Spawn(0) — both in cell 0.
2. A subscribes SELECT * FROM entity. A's cache now contains B's row (visible via Spatial, same cell).
3. B SetCell(<B.Id>, 1) — B leaves cell 0.
4. Bug: A's cache still contains B's row, frozen at Cell = 0; no OnDelete fires for it. A never receives B's Cell = 1 update either.

Swapping Spatial for a plain-column filter (or removing RLS and using a client WHERE Cell = 0) makes step 4 retract as expected.

Impact

Any moving area-of-interest built on an RLS CellKey self-join leaks stale "ghost" entities: a stationary observer sees other entities freeze at the cell boundary instead of disappearing.

Related

clockworklabs/SpacetimeDB#2810 ("Subscriptions fail to produce updates when joining a table with an RLS table") — same subsystem (incremental subscription maintenance for a JOIN against an RLS/client_visibility_filter table); closed, but no fix is present in a released build. The case here is the DELETE-delta-on-update-out variant (row leaves the join result via UPDATE → no retraction), which #2810's write-up does not call out explicitly.

[starpluckerstudios]

Closing: we resolved our use case by moving off join-based client_visibility_filter onto a per-viewer [View] (the maintainer-recommended path), which retracts correctly on UPDATE-out. Filed before we found that approach; no fix needed on our account. Related: #5704, #2810.