SQL MERGE on UNIQUE-indexed property throws DuplicatedKeyException when same key appears twice in a batch (Neo4j matches the second occurrence) (original) (raw)

Context

Reported by a user migrating from Neo4j in the blog post Moving a Graph RAG system from Neo4j to ArcadeDB (May 2026, against ArcadeDB 26.4.1-SNAPSHOT).

Problem

When a batch of statements issues MERGE on a property that has a UNIQUE index, and the same key value appears twice in the same batch (or within a single transaction before the first record is fully indexed/visible), the second MERGE throws DuplicatedKeyException instead of matching the row created earlier in the batch.

Example batch (Cypher or SQL flavour):

MERGE (w:Worker {name: 'John'}) ON CREATE SET w.role = 'eng';
MERGE (w:Worker {name: 'John'}) ON CREATE SET w.role = 'pm';

Expected: second statement matches the first MERGE's result.
Actual: second statement throws DuplicatedKeyException because the index entry from the first MERGE collides with the lookup-then-insert path of the second.

Expected

MERGE semantics (in both Cypher and SQL) require an atomic match-or-create. When a unique index already contains the key (regardless of whether that index entry was just produced in the same transaction), MERGE must match it, not throw.

Neo4j handles this correctly even in batched scripts and within a single transaction.

Notes

Related but distinct from #4226 (MERGE planner) and #4089 (MERGE not matching existing vertex). Those are about MATCH-side lookup; this is about the unique-index conflict path triggering during MERGE's create branch.

Workaround used in the blog post: split MERGE statements across separate transactions, or deduplicate keys client-side. Both defeat the purpose of MERGE.