live query with joins emits delete events on ready

[sunghwan2789]

• I've validated the bug against the latest version of DB packages

Describe the bug

When creating a live query with a three-collection chained LEFT JOIN (A -> B -> C), an issue occurs if the middle collection (B) is initially empty and is populated after the initial sync. The query subscription incorrectly emits "delete" and "insert" events, instead of "update" events, even though the data in the result set is only being updated, not removed or added.

A full reproduction is available in this test case:
https://github.com/sunghwan2789/db/blob/9e1f1d0bfad4e81634671671300467695e01eedc/packages/db/tests/query/join.test.ts#L1251-L1313

Test Setup:

// Test schema for three-collection join subscription
type Player = {
 name: string
 club_id: string
 position: string
}

type Client = {
 name: string
 player: string // references Player.name
 email: string
}

type Balance = {
 name: string
 client: string // references Client.name
 amount: number
}

// Sample data
const samplePlayers: Array<Player> = [
 { name: `player1`, club_id: `club1`, position: `forward` },
 { name: `player2`, club_id: `club1`, position: `midfielder` },
 { name: `player3`, club_id: `club1`, position: `defender` },
]

const sampleClients: Array<Client> = [
 { name: `client1`, player: `player1`, email: `client1@example.com` },
 { name: `client2`, player: `player2`, email: `client2@example.com` },
 { name: `client3`, player: `player3`, email: `client3@example.com` },
]

const sampleBalances: Array<Balance> = [
 { name: `balance1`, client: `client1`, amount: 1000 },
 { name: `balance2`, client: `client2`, amount: 2000 },
 { name: `balance3`, client: `client3`, amount: 1500 },
]

const playersCollection = createCollection(
 mockSyncCollectionOptions<Player>({
 id: `test-players-subscription-${autoIndex}`,
 getKey: (player) => player.name,
 initialData: samplePlayers,
 autoIndex,
 })
)

const clientsCollection = createCollection(
 mockSyncCollectionOptionsNoInitialState<Client>({
 id: `test-clients-subscription-${autoIndex}`,
 getKey: (client) => client.name,
 autoIndex,
 })
)

const balancesCollection = createCollection(
 mockSyncCollectionOptions<Balance>({
 id: `test-balances-subscription-${autoIndex}`,
 getKey: (balance) => balance.name,
 initialData: sampleBalances,
 autoIndex,
 })
)

// Create chained join query with three collections using left joins
const chainedJoinQuery = createLiveQueryCollection({
 startSync: true,
 query: (q) =>
 q
 .from({ player: playersCollection })
 .join(
 { client: clientsCollection },
 ({ client, player }) => eq(client.player, player.name),
 `left`
 )
 .join(
 { balance: balancesCollection },
 ({ balance, client }) => eq(balance.client, client?.name),
 `left`
 )
 .select(({ player, client, balance }) => ({
 player_name: player.name,
 client_name: client?.name,
 balance_amount: balance?.amount,
 })),
})

// Track all change events
const changeEvents: Array<any> = []
const subscription = chainedJoinQuery.subscribeChanges((changes) => {
 changeEvents.push(...changes)
})

// Initial state should have 3 results (left join includes all players even without clients)
expect(chainedJoinQuery.toArray).toHaveLength(3)
// Verify players are present but clients are null
const initialResults = chainedJoinQuery.toArray
expect(initialResults.every((r) => r.client_name === undefined)).toBe(
 true
)
expect(initialResults.every((r) => r.balance_amount === undefined)).toBe(
 true
)

// Clear any initial events from subscription setup
changeEvents.length = 0

// Insert client data after eager sync completion and first assertion
clientsCollection.utils.begin()
sampleClients.forEach((client) => {
 clientsCollection.utils.write({ type: `insert`, value: client })
})
clientsCollection.utils.commit()
clientsCollection.utils.markReady()

// Should still have 3 results, but now with client data
expect(chainedJoinQuery.toArray).toHaveLength(3)
// Verify clients are now populated
const resultsAfterInsert = chainedJoinQuery.toArray
expect(resultsAfterInsert.every((r) => r.client_name !== undefined)).toBe(
 true
)
// Verify change events are emitted
expect({
 delete: changeEvents.filter((c) => c.type === `delete`).length,
 update: changeEvents.filter((c) => c.type === `update`).length,
 insert: changeEvents.filter((c) => c.type === `insert`).length,
}).toEqual({ delete: 0, update: 3, insert: 0 })
// fail with actual: { delete 3, update:0, insert: 6 }

Expected Behavior

When the clientsCollection is populated, the existing items in the chainedJoinQuery result set should be updated. Since the change events are keyed to the player, we expect to receive 3 update events.

Actual Behavior

The subscription incorrectly reports 3 delete events and 6 insert events, instead of 3 update events.

Additional Context

When attempting to provide a custom getKey option to the createLiveQueryCollection to work around this, the query fails with a DuplicateKeySyncError.

diff --git a/packages/db/tests/query/join.test.ts b/packages/db/tests/query/join.test.ts
index a1812de..5a0004c 100644
--- a/packages/db/tests/query/join.test.ts
+++ b/packages/db/tests/query/join.test.ts
@@ -1250,6 +1250,7 @@ function createJoinTests(autoIndex: `off` | `eager`): void {
 // Create chained join query with three collections using left joins
 const chainedJoinQuery = createLiveQueryCollection({
 startSync: true,
+ getKey: (r) => r.player_name,
 query: (q) =>
 q
 .from({ player: playersCollection })

Uncaught CollectionOperationError: Cannot insert document with key "player1" from sync because it already exists in the collection "live-query-56"

I think this is a regression of #658 ?

[kevin-dp]

Hi @sunghwan2789,

Thank you for the detailed report and reproduction.
I created a reproduction based on yours and i get slightly different behaviour: 3 deletes, 3 inserts, and 0 updates.

The root cause is that the composite join key changes when the middle collection gets populated (e.g. [[player1,undefined],undefined] → [[player1,client1],balance1]), so the system treats them as entirely different rows instead of recognizing them as updates to existing logical rows.

Here is a more detailed analysis:

High-Level Analysis

How it works today

There are three layers involved:

1. IVM Layer (db-ivm) — The differential dataflow engine. Join operators emit rows with composite keys. When a LEFT JOIN matches, the composite key includes both sides: [player1,client1]. When it doesn't match, the right side is undefined: [player1,undefined].

2. Change Accumulator (collection-config-builder.ts) — Receives IVM output as a multiset of [[key, value], multiplicity] entries. It groups by key and tallies inserts (+1 multiplicity) vs deletes (-1 multiplicity). Then it decides the operation type: pure insert, pure delete, or update (when inserts and deletes balance out for the same key).

3. Collection Sync (sync.ts) — Receives write({type, value}) calls and stores data by key in the collection. Subscribers then see the resulting change events.

Why the bug happens

When the middle collection (clients) gets populated in a chained LEFT JOIN:

• The IVM layer emits -1 for the old row with key [[player1,undefined],undefined]
• The IVM layer emits +1 for the new row with key [[player1,client1],balance1]

Because the composite key changed, the accumulator puts these in two separate buckets. It sees:

• Key [[player1,undefined],undefined]: 1 delete, 0 inserts → DELETE
• Key [[player1,client1],balance1]: 1 insert, 0 deletes → INSERT

If these had the same key, the accumulator would see 1 delete + 1 insert = balanced → UPDATE. But since they're different keys, they're treated as independent operations.

The root cause

The composite key is built at line 600 of joins.ts:

const resultKey = `[${mainKey},${joinedKey}]`

This includes the joined side's key, which changes from undefined to an actual value when the right collection gets populated. For chained joins this cascades — the first join's output key becomes the mainKey for the second join, so the instability propagates through the entire chain.

How to fix it

The fix should happen at the key composition level in processJoinResults(). For LEFT JOINs (and by extension, FULL JOINs), the composite key should be stable — it should not change based on whether the right side has a match or not.

The approach: use only the left (main) side's key as the result key for LEFT JOINs. Specifically:

• LEFT JOIN: Use mainKey only (the left side always exists, and defines row identity)
• RIGHT JOIN: Use joinedKey only (the right side always exists)
• INNER JOIN: The current composite key [mainKey,joinedKey] is fine — both sides must exist, so neither can transition from undefined
• FULL JOIN: Needs the composite key since either side can be the anchor, but it should use a stable form (e.g. always use the non-null side's key, or a deterministic combination)

This way, when a player's row gains a client match, the key stays player1 instead of changing from [player1,undefined] to [player1,client1]. The accumulator would then group the -1 and +1 under the same key and correctly emit an UPDATE.

This also naturally fixes the getKey workaround failure mentioned in the issue — the DuplicateKeySyncError happens because the system tries to insert a "new" row (new composite key) while the old row (old composite key, same user-defined key) still exists. With stable keys, the IVM layer itself would handle the transition correctly as a balanced delete+insert on the same key.

Risk / things to consider

• Need to ensure the key is still unique within the result set. For LEFT JOINs this holds because each left row appears at most once in a 1:1 or many:1 relationship. But for 1:many joins (one player → multiple clients), using only mainKey would produce duplicate keys. The fix might need to be conditional on join cardinality, or we may need a smarter key strategy for 1:many left joins.
• The FULL JOIN case is trickier since the anchor side can be either left or right.

[kevin-dp]

After more thought, I believe this is working as intended.

The idea that populating the right side of a LEFT JOIN should emit "update" events relies on the assumption that the result row is the "same logical row" — just with previously-null columns filled in. That mental model seems intuitive for the 1:1 case, but it completely falls apart for 1:many joins.

If one player has 3 clients, populating the clients collection turns 1 result row into 3. That's not an update — it's 1 delete and 3 inserts. And if it's not an update in the 1:many case, there's no principled reason to treat it as an update in the 1:1 case either. The 1:1 case just happens to have the same row count before and after, but that's coincidental, not a signal of stable identity.

What's actually happening at the tuple level is consistent regardless of cardinality: the old composite tuple is retracted and new composite tuples are emitted. Delete + insert is the correct characterization of that.

Regarding the DuplicateKeySyncError when providing a custom getKey: (r) => r.player_name — this is fragile and not a good approach. It works for 1:1 relationships by coincidence, but for 1:many joins it would produce genuine duplicate keys (e.g. 3 rows all keyed as "player1"). It's also worth noting that by mapping two different composite tuples to the same user-provided key, you're essentially asserting that the deleted row and the inserted row are the same row — i.e. treating it as an update — which i'm arguing against.

That said, for the 1:1 case it should indeed work without erroring. There's a minor bug where we should process deletes before inserts within a batch so that the old row is removed before the new one is inserted, avoiding the collision. This is now fixed by #1290.