One SQL predicate, eleven implementations: unifying them and what our differential harness caught

This is a story about paying down the most expensive kind of technical debt in a query engine — duplicated semantics — and the testing harness that made it safe. It comes from building MoteDB, an embedded multimodal database in Rust for robotics workloads, but the pattern applies to any engine that grew fast.

The bug that kept coming back

Through four rounds of pre-release bug hunting on v0.17, we kept landing on the same shape of bug:

The same SQL predicate, evaluated by different code paths, returned different answers.

WHERE emb = '[1.0, 2.0]' matched rows on one path and not another. DELETE filtered differently than SELECT for the identical WHERE. A JOIN with the predicate as its condition disagreed with the comma-join form of the same query. Every fix was correct; every fix was also whack-a-mole, because the root cause wasn't any single bug. It was this:

SQL comparison semantics lived in 11 separate implementations. 

The interpreter had its own eval_binary_op. The fast per-row evaluators (two of them). The compiled WHERE path with hand-rolled arms per operator. The vectorized scan leaves. The JOIN predicate pushdown. The aggregate pre-filter. Plus two inline closures. They each made their own decisions about NULL handling, type coercion, and what happens when types are incomparable. Eleven implementations, eleven opinions.

Step 0: lock the present before changing it

The instinct is to immediately write the One True Implementation. We did the opposite first: characteristic tests that lock what every path currently does, before touching anything.

A 38-row matrix of type pairs × 8 operators, with the equality route and the ordering route asserted separately. Plus a dozen engine-level probes asserting NULL and three-valued logic behavior per path.

This mattered for two reasons:

1. When we unified semantics and intentionally changed behavior (more below), the characteristic tests told us exactly which changes were deliberate. Every behavior change was a conscious verdict, not an accident that slipped through review.
2. It produced the inventory. You can't unify what you haven't enumerated.

Step 1: one semantics layer

Everything now routes through a single module. The core is deliberately small — a three-valued result, one comparison function, and a coercion pair built on Cow so the common case allocates nothing:

pub enum Cmp3 { True, False, Unknown } pub fn compare(a: &Value, b: &Value, op: OrdOp) -> Cmp3 { if matches!(a, Value::Null) || matches!(b, Value::Null) { return Cmp3::Unknown; } if !needs_coercion(a, b) { return match a.partial_cmp(b) { Some(ord) => Cmp3::from_bool(op.matches_ordering(ord)), None => Cmp3::Unknown, // incomparable types }; } let (a2, b2) = coerce_pair(a, b); // Bool↔numeric, bracketed-vector text, … match a2.partial_cmp(&b2) { Some(ord) => Cmp3::from_bool(op.matches_ordering(ord)), None => Cmp3::Unknown, } } 

Eleven call sites became eleven thin adapters. The subtle decisions got made once:

• =/<> route through equality; <..>= route through ordering — because in SQL they are different questions when types are incomparable.
• Incomparable types in an ordering comparison return Unknown, which filters as "no match" — the standard three-valued behavior.

Step 2: the differential harness (the fun part)

Characteristic tests lock one path at a time. What we needed was a machine that asks all paths the same question and demands the same answer.

So: a differential harness that generates random predicates — mixed types, NULLs, boundary values, bracketed vector literals, ISO timestamps — and runs each one through nine execution paths:

1. SELECT … WHERE p (fast path)
2. DELETE … WHERE p (on a copy, comparing affected row sets)
3. UPDATE … WHERE p (same)
4. INNER JOIN with p as the condition, left-to-right
5. …and with the table order flipped
6. Comma-join with p in WHERE
7. Subquery IN (SELECT … WHERE p)
8. Aggregate with p in WHERE
9. GROUP BY … HAVING p

Any path disagreeing with any other fails the run, printing the seed, the predicate, and all nine answers. Seeded, so a failure reproduces with one environment variable.

Day one, it caught seven pre-existing divergences. All of them present in the released 0.17. My two favorites:

A path evaluated NOT (flag IN (0,1)) and returned every row. The IN branch used a bare == for membership. A NULL in the list matched a NULL value (bare equality says yes; SQL three-valued logic says UNKNOWN, which NOT keeps as UNKNOWN, which filters nothing through). Classic two-valued leak in one path while ten others had it right.

The harness filed a bug report against itself. Our UPDATE probe marked affected rows with SET a = a + 1000, then compared row sets. First run flagged disagreement everywhere. Diagnosis: rows with a = NULL stayed put — NULL + 1000 is NULL — so the "affected set" wasn't what the probe computed. The engine was right; our probe was wrong. (The fix was a sentinel constant instead of arithmetic.) If you build a differential harness, budget for it to catch your own test bugs first. That's it working.

Step 3: the verdict that overruled our own locked expectations

Here's the part that changed how I think about characteristic tests.

One divergence survived initial triage as "genuinely different": hash-set normalization for IN lists was parsing pure-numeric text like '7' as a microsecond timestamp. That's the timestamp parser's first-try bleeding into an unrelated normalization step. And our Step-0 characteristic tests had locked this in as current behavior — we'd documented the quirk as intentional.

The differential forced the question: the equality route doesn't parse '7' as a timestamp, the hash route does, the same predicate disagrees across paths, and one of them is clearly an accident. The verdict: pure-numeric text no longer normalizes as timestamps — and we amended the characteristic tests we'd written six weeks earlier.

The lesson I took: characteristic tests are for knowing what you do today. Differential tests are for discovering what's true. When they disagree, trust the differential.

Step 4: a ratchet, so it stays fixed

Unification decays if new code can quietly add a bare partial_cmp back. So the CI now counts raw value-comparison sites in src/sql (with an allowlist annotation for the legitimate ones inside the semantics layer itself) and compares against a ceiling number stored in a separate file:

check_sql_cmp: ok — violations 50 / ceiling 50 

The rule is simple: the number can only go down. Lower the ceiling when you retire sites; raising it requires editing a file whose diff is conspicuous in review. It's the same ratchet pattern as coverage or lint baselines, applied to semantic concentration.

What this means if you're building an engine

• Semantics that live in N places are not N implementations — they are N diverging timelines of the same spec. Budget for the unification before the divergence budget runs you.
• Lock current behavior with characteristic tests before unifying, so every behavior change is a deliberate verdict.
• Then build the differential. Same input, every path, demand agreement. It will catch your test tooling's bugs too; that's a feature.
• Ratchet the count so the debt can't silently regrow.

The product, briefly

If you're curious what the engine is for: MoteDB is an embedded, MIT-licensed, single-process database in Rust for robotics and embodied-AI workloads — VECTOR columns with DiskANN indexes, time-series with continuous views, BM25 full text (CJK tokenization built in), BLOB, ACID, Arrow interop, and single-file snapshot export for fleet distribution. cargo add motedb, pip install motedb-python, or npm install motedb. There's a 10-minute runnable notebook in the repo's examples/ directory.

The 0.18 release this work shipped in also added CREATE CONTINUOUS VIEW, ALTER TABLE DROP/RENAME COLUMN, multi-process shared read-only mode, and EXPLAIN that records real execution decision points instead of a heuristic guess. Ten good first issues are up if you'd like to poke around a storage engine with the safety net on.

Thanks for reading. I'm happy to go deeper on the harness design or the coercion rules in the comments.