ClickHouse: "returned without throwing" isn't "done"

I ran into both of these doing a contribution to an open-source analytics product. General enough to keep on its own.

A ClickHouse dictionary sits on a normal table and exposes fast key-to-value lookups via dictGet(...). Used well, this makes a rules engine genuinely data-driven — the SQL calls dictGet once, and every new row in the backing table is picked up with zero SQL changes.

The sharp edge

A HASHED or COMPLEX_KEY_HASHED dictionary doesn't define behaviour when two rows share a key. A migration that reclassifies an existing key with a plain INSERT leaves both rows, and which one the dictionary returns is undefined — nothing throws, queries keep working, they just return either value unpredictably.

If a migration might touch a key that already exists, delete the old row first. Don't assume insert-only is safe just because the table technically allows duplicates.

The sharper edge

ALTER TABLE ... DELETE is an async mutation by default. The statement returns once the mutation is queued, not once it's applied — so a delete immediately followed by a dependent insert can run against data that hasn't actually been deleted yet.

I confirmed this was real by testing it directly rather than reading the docs and deciding: inserted a stale row, ran delete-then-insert, queried right after — the stale row was still there. system.mutations showed is_done = 1 shortly after, consistent with ordinary completion lag, but the query in the gap saw stale data.

ALTER TABLE my_table DELETE WHERE key IN (...) SETTINGS mutations_sync = 2

mutations_sync = 1 waits for the local replica; 2 waits for all replicas before returning. With it, delete-then-insert is genuinely safe. I reproduced the same race test with the setting added — the duplicate was reliably gone, every time.

The transferable lesson: "returned without throwing" is a much weaker claim than "done," for any ClickHouse mutation — and honestly for a lot of async operations. If correctness depends on ordering between a mutation and a later statement, enforce that ordering explicitly. Being sequential in the same script doesn't imply it. And test it by reproducing the actual race, not by reading the code and deciding it looks right.