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

This story explains how paying down technical debt in a query engine, specifically duplicated semantics, was achieved through building a testing harness. The focus is on MoteDB, a Rust-based embedded multimodal database for robotics workloads, but the pattern applies to any fast-growing engine.

Throughout the development of MoteDB, the same SQL predicate, evaluated by different code paths, returned different answers. This inconsistency stemmed from SQL comparison semantics living in 11 separate implementations. These included the interpreter's eval_binary_op, the fast per-row evaluators, the compiled WHERE path with hand-rolled arms per operator, vectorized scan leaves, JOIN predicate pushdown, aggregate pre-filter, and two inline closures.

To address this issue, the team took the following steps:

1. Lock the present before changing it: Characteristic tests were written to lock the current behavior of each path before making any changes. This allowed the team to enumerate every behavior and explicitly decide which changes to implement.

2. One semantics layer: An effort was made to have everything route through a single module, reducing the number of implementations from 11 to one. This module defined a single comparison function, Cmp3, and a coercion pair built on Cow to avoid unnecessary allocations. The choice between equality and ordering operations was made once, ensuring consistent behavior for incomparable types.

3. The differential harness: A differential harness was created to generate random predicates with mixed types, NULLs, boundary values, bracketed vector literals, ISO timestamps, and run them through nine execution paths. Any disagreement between paths would fail the run, printing the seed, predicate, and all nine answers. This allowed the team to catch seven pre-existing divergences present in the released version 0.17.

One notable divergence involved hash-set normalization for IN lists, where pure-numeric text was incorrectly parsed as a microsecond timestamp. The differential harness revealed that the equality route did not parse 7 as a timestamp, while the hash route did, contradicting the same predicate across paths. The verdict was to stop parsing pure-numeric text as timestamps in the equality route, and the characteristic tests were amended accordingly.

In conclusion, the story demonstrates how a testing harness can identify and unify duplicated semantics in a query engine, leading to more consistent behavior across implementations, and improving overall code quality.