Check whether two SQL queries are equivalent

A SQL equivalence check runs two queries against the same generated tables and reports the first set of tables where their results differ, shrunk to the fewest rows that still show the difference. It runs SQLite, so it answers for SQLite.

Compare two snippets

JavaScript, TypeScript and Python run in your browser. Nothing to install, no account.

How it works

In Code mode, pick SQL (SQLite) from either language menu. Both sides become query editors, and a Schema editor appears above them for the CREATE TABLE statements the two queries share. Equivl never parses the schema itself: SQLite, compiled to WebAssembly and running in a Web Worker in your browser, executes it, and the row generator reads the tables, column types, primary keys, UNIQUE indexes, NOT NULLs and foreign keys back from SQLite. Nothing is uploaded.

Each of the 1,000 cases fills every table with up to 5 rows. Values come from a handful of small numbers and short strings plus the literals written in either query, and a number n in a query also brings n - 1 and n + 1, which is how a boundary such as total > 100 against total >= 100 is found. About one nullable cell in five is NULL, every foreign key points at a real row, and the first case has every table empty. Both queries run on the same rows, which are rolled back before the next case. A seed makes every run reproducible.

On five pairs chosen to differ in these ways (a LEFT JOIN rewritten as an INNER JOIN, NOT IN against NOT EXISTS when a key is NULL, COUNT(*) against COUNT(column), > against >=, and = against >= on a bind parameter), the difference was found on each of 50 seeds, never later than the 25th case. 1,000 cases take a fraction of a second.

Android Room queries

A Room @Query can be pasted as written. Kotlin templates such as ${Employee.TABLE} and $TABLE_NAME are read as the table or column they name, the same way in the schema and in both queries. Equivl takes the value from a const val you paste into the Room constants field, or from the same template written in the schema, or by matching the constant's name to exactly one table or column in the schema. Every template it read is listed under the editors, and one it cannot place is an error that names it, never a guess. A whole @Query("…") annotation, and Java's "SELECT * FROM " + Employee.TABLE concatenation, are unwrapped. Bind parameters such as :id get a generated value in each case, the same value for both queries.

What is compared

• The rows, as a set with duplicates counted, unless both queries end in ORDER BY. Then the order counts too, and a result that differs only in order is reported separately, because rows that tie on the ORDER BY may come back in any order. Settings can turn order off.
• The values, the way SQLite's = compares them, except that NULL matches NULL: 1 equals 1.0, and 1 never equals '1'.
• Column names are noted, not counted as a difference, unless Settings says they must match. A different number of columns is always a difference.

What you see

• The smallest tables the two queries disagree on, found by dropping rows and then simplifying values for as long as the disagreement survives, with each query's result under them. The tables shown are read back from SQLite, so they are the rows the queries saw.
• Every case in a table, each opening to its generated tables and both results.
• A schema or a query SQLite rejects stops the run before any case, with SQLite's own message under the editor. The schema holds definitions only, and each query must be one statement that only reads. Two broken queries never read as equivalent.
• A query that runs longer than 2 seconds, such as a recursive WITH with no end, is stopped. That case counts as a difference if the other query finished, and as neither finishing if both were stopped, never as agreement.

Limits

• No difference found is evidence, not proof. The tables are small and their values are guided by the queries rather than exhaustive, so a difference that needs more rows or other values can go unfound.
• This is SQLite's SQL. A query written for PostgreSQL, MySQL or SQL Server can mean something different under SQLite, or not run at all, and two queries that agree here can disagree on another database.
• A Room list parameter such as IN (:ids) gets one value, not a list.
• A template is read as a constant. One that holds an expression, like ${ids.joinToString()}, cannot be known and is refused.