A Generated SQL Query Got Faster by Returning Fewer Rows. Test That Before You Merge It

Have you ever watched a generated SQL refactor run faster and assumed it must be correct? That assumption breaks down when the speedup comes from an inner join that silently drops rows the old left join preserved. The output still looks plausible because every displayed row has a customer name, so a quick smoke test misses the loss. I treat a generated query change as a patch, not a proof, until a differential check compares the old and new result sets. Disclosure: This article was prepared as part of MonkeyCode's product outreach. A failure that hides in a join change Start with a tiny data fixture that contains an order for a customer who does not exist in the customers table. That dangling reference is common enough in legacy systems, and it is exactly where a join conversion changes the meaning of a query. CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, amount_cents INTEGER NOT NULL ); INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Grace'); INSERT INTO orders VALUES (10, 1, 2500), (11, 2, 1800), (12, 2, 900), (13, 3, 1200), (14, 4, 1500); The original query preserves every order, including the orphaned one. SELECT o.id, c.name FROM orders o LEFT JOIN customers c ON c.id = o.customer_id; It returns five rows: four matched orders and one row where name is NULL. The generated rewrite changes only the join type, because INNER JOIN reads better and often runs faster when the schema guarantees every customer_id exists. In this fixture, that guarantee does not hold. SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id; It returns four rows. The missing row is the whole point: a performance improvement that also changes the result is not an optimization. Build a golden result check before you build the new query Do not compare queries by reading the first few rows. Instead, create a known-good baseline, normalize the result set, and fail any candidate that does not match. import sqlite3 SCHEMA = """ CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, amount_cents INTEGER NOT NULL ); INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Grace'); INSERT INTO orders VALUES (10, 1, 2500), (11, 2, 1800), (12, 2, 900), (13, 3, 1200), (14, 4, 1500); """ BASELINE = """ SELECT o.id, c.name FROM orders o LEFT JOIN customers c ON c.id = o.customer_id; """ CANDIDATE = """ SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id; """ def result_set(sql): conn = sqlite3.connect(":memory:") conn.executescript(SCHEMA) rows = conn.execute(sql).fetchall() conn.close() return [tuple(row) for row in rows] baseline = result_set(BASELINE) candidate = result_set(CANDIDATE) print("baseline rows:", len(baseline), "candidate rows:", len(candidate)) print("missing from candidate:", set(baseline) - set(candidate)) if baseline != candidate: raise SystemExit("candidate changed the result set") The script prints the rows that disappeared instead of letting a developer squint at two tables. That difference is the debugging signal, not the query's stated intent. Make the fixture behave like production data A passing test means very little if the fixture contains only happy-path rows. Include several edge cases so the golden check can catch more than the join example above. An orphaned child row where the parent is missing. A parent with no children. Duplicate child rows. NULL values that differ from empty strings. Boundary values such as zero, negative amounts, or the maximum supported length. Rows in a different insertion order than the index order. If the baseline and candidate return the same normalized set for this ugly fixture, you still have not proven correctness. You have only removed one class of silent result-set changes. Use free model access to generate alternatives instead of accepting the first rewrite When an assistant returns a query that runs faster, the worst next step is to merge it because the explanation sounded confident. A safer loop is to ask for several rewrites, then send every candidate through the same golden result check. That is where MonkeyCode's free model access changes the economics: the cost of generating a second or third candidate no longer forces you to stop after the first plausible one. This is not about trusting the model more. It is about making model output cheap enough to treat as a hypothesis that has to survive an automated check. Generate candidates and compare them in one pass Here is the workflow I use when replacing a slow query. Save the current result set from a synthetic or sanitized fixture. Record a normalized signature of the baseline rows. Ask the model for three candidate rewrites instead of one. Run each candidate against the same fixture and signature. Keep only candidates that match the baseline result set. Use EXPLAIN or EXPLAIN QUERY PLAN to check whether any matching candidate actually improves the plan. If no candidate matches and runs faster, the right output is not a merged patch. The right output is a list of constraints the model should respect on the next attempt. Where the free server option fits A laptop works for a small fixture, but some query bugs only appear with a larger snapshot that cannot be copied into every development machine. In that case, you can put the golden result set behind a small comparison endpoint and let each candidate post its output for a pass or fail response. The free server option makes this practical for a review loop when you can keep the service small and the data sanitized. The endpoint can do two things: report whether the candidate row set equals the baseline, and return a diff of the missing or extra rows. That diff is more useful than a model's explanation because it comes from the data. Do not send real customer data to a model or a public server. Generate a fixture that preserves the same join, null, and cardinality traps, then run the full privacy-sensitive comparison in an environment you control. Limits of this approach Differential testing catches semantic regressions, but it cannot prove that a query is correct. It only proves that a candidate matches a chosen baseline. SQLite is a good local fixture, but SQL semantics and planner behavior differ from PostgreSQL, MySQL, SQL Server, or Oracle. The golden baseline may itself be wrong or outdated. A query can match the fixture and still fail on data shapes you did not include. A faster plan can still consume too much memory, lock too long, or ignore useful indexes in production. The approach adds a step, so it is overkill for read-only dashboards with no silent-loss risk. Who should skip this? If you do not have a known-good result set or cannot safely create a representative fixture, do not pretend a local pass is a database guarantee. The same discipline applies to any generated code: verify the behavior that matters before you trust the explanation.