SQL Comparison Library Architecture

This article describes the design of a deterministic-first library that compares SQL queries and their results on a database instance, explains mismatches, and optionally adds AI judgment.

Details

The purpose of this library is to design a deterministic-first system that compares SQL queries and their results, explains any mismatches, and optionally incorporates AI judgment. The library evaluates the user's query, the actual SQL executed, the expected SQL, the actual result, and the expected result. The key design principles include: 1) Deterministic metrics are the source of truth, with AI being advisory; 2) SQL structure and output correctness are scored independently; 3) Diagnostics and explanations are provided for every important score; 4) Configurable semantics like set, multiset, ordered, and numeric tolerance modes are supported; and 5) The outputs are production-safe, with both machine-friendly schema and human-readable explanations. The high-level architecture consists of five layers: Validation, Structural Comparison, Result Comparison, Diagnostic Attribution, and Optional AI Judge. The Validation layer parses the SQL, canonicalizes the strings, and executes the queries in a read-only sandbox. The subsequent layers perform structural and result comparisons, provide detailed diagnostics, and optionally incorporate AI-based judgments on the query intent, equivalence, and acceptable deviations.