🗄️ SQL Server: Fix a Graph Table Query Returning Duplicate Edges
SQL Server’s graph database features let you traverse relationships with a MATCH clause, but a pattern that checks for a relationship in both directions using two separate MATCH clauses joined with OR can match the same underlying edge row twice — once satisfying each half of the OR — so the result set returns what looks like two distinct relationships when it’s actually one edge counted from both traversal directions.
🔎 The Problem
SELECT Person1.Name, Person2.Name FROM Person Person1, Connection, Person Person2 WHERE MATCH(Person1-(Connection)->Person2) OR MATCH(Person2-(Connection)->Person1); -- a single bidirectional-looking edge can satisfy BOTH halves -- of the OR, so it's returned twice in the result set
✅ Fix: Deduplicate by Edge Identity, Not by Row Shape
- Use
SELECT DISTINCTon the edge’s own identity column ($edge_id) when a query’s pattern could plausibly match the same edge from more than one direction, rather than assuming each returned row is a unique relationship. - Where the relationship is inherently bidirectional (a “connected to” edge), model it with a single directionless edge table and query it once, instead of relying on two opposite-direction MATCH clauses combined with OR.
- Test traversal queries against a small, manually-verified graph where you know the exact expected edge count, since duplicate-looking results from a bidirectional OR pattern are easy to miss when eyeballing a larger result set.
🚨 Why This Is Dangerous
- Code that aggregates or counts relationships from a graph query without deduplicating by edge identity can silently double-count connections in anything built on top of it — a mutual-connections feature or a relationship-strength score — producing numbers that look plausible but are systematically inflated.
A MATCH pattern tells SQL Server how to walk the graph, not how many times it’s allowed to arrive at the same edge from a different direction — that part is still on you to deduplicate.
