Transpile or Re-Ask: Fixing Dialect Breaks in Agent-Written SQL

A quarterly reporting job moved from Postgres to MySQL last spring, and every agent-drafted query in the pipeline kept parsing fine while returning the wrong rows. I have already described one loud failure on this account, an AI-generated query that locked a production table, but this quieter class of break costs more engineering hours. The model never sees the target engine, so it keeps writing date_trunc('month', ...) and ON CONFLICT long after those idioms stop meaning anything. Below is the debate I ran against my own fixture set, plus the decision rule I now apply before touching a prompt.

Where agent SQL actually breaks

Dialect drift looks like a syntax problem and usually is not one. The statements that break loudly are cheap to fix, because the parser refuses them and the loop simply retries with the error text. The expensive ones parse on both engines and disagree about results: ILIKE against a case-insensitive collation, LIMIT before OFFSET, integer division, and jsonb arrow operators with no direct MySQL equivalent. A query that runs cleanly on both engines and returns 41 rows instead of 44 will pass every check that only looks for exceptions.

That asymmetry is why the choice between deterministic transpilation and a model re-ask is not a tooling preference. The two approaches fail in different places, and the decision rule has to be driven by which failure you can detect cheaply.

Position A: transpile deterministically, then execute

The first position says dialect conversion is a parsing problem and should stay one. sqlglot reads the Postgres AST and writes MySQL, and the same input always produces the same output, which makes the whole step reviewable in a diff. It is also fast enough to run on every draft, so repair cost stays near zero for the common cases.

# dialect_probe.py — illustrative; adjust identifiers to your own schema
import sqlglot

SOURCE, TARGET = "postgres", "mysql"

def transpile(sql: str) -> str:
    """Deterministic: same input, same output, diffable in code review."""
    return sqlglot.transpile(sql, read=SOURCE, write=TARGET, pretty=True)[0]

if __name__ == "__main__":
    src = """
        SELECT user_id, date_trunc('month', created_at) AS m, count(*)
        FROM orders WHERE note ILIKE '%refund%' GROUP BY 1, 2
    """
    print(transpile(src))

The weakness is equally clear: transpilers preserve the semantics they can express and guess at the rest. Window frame defaults, string concatenation and null ordering under ORDER BY are all places where a translated statement is valid, executable, and quietly wrong. Position A therefore only holds up when it is paired with execution against the target engine.

Position B: re-ask the model with the target dialect in the prompt

The second position says the model already knows both dialects and only needs to be told which one matters. Free model access makes the extra call cheap enough to sit inside the repair loop, which is exactly where I use it: MonkeyCode's free model access covers the re-ask step, and the free server option gives me a disposable target engine to execute against. Disclosure: This article was prepared as part of MonkeyCode's product outreach. The allowance and limits on that access change over time, so check the current account terms before you budget a loop that runs on every commit.

# reask.py — unexecuted example; swap the client if your endpoint differs
import os

PROMPT = """Rewrite this {src} query for {dst}.
Keep the output column names and their order identical.
Do not change filtering semantics. Return SQL only.

{sql}
"""

def reask(client, sql: str, src="postgres", dst="mysql") -> str:
    r = client.chat.completions.create(
        model=os.environ["LLM_MODEL"],
        temperature=0,
        messages=[{"role": "user", "content": PROMPT.format(src=src, dst=dst, sql=sql)}],
    )
    return r.choices[0].message.content.strip()

The re-ask handles the cases the transpiler guesses at, including collation-sensitive comparisons and ON DUPLICATE KEY UPDATE conversions that need a rewrite rather than a symbol swap. Its weakness is the inverse: it is non-deterministic, it can silently drop a WHERE clause, and a plausible rewrite is indistinguishable from a correct one until you execute it. Position B without execution is a confident guess with extra steps.

The decision rule I use

Neither position survives alone, and the split is not "simple queries versus complex queries". It is "can I detect the failure with a fixture run", and the fixture is the same artifact either way.

Observation after the step	Route	Reason
Transpile raises a parse error	re-ask, then execute	grammar-level gap; the model often knows the idiom
Transpile parses, target engine errors	re-ask, then execute	valid AST, unsupported function or syntax
Executes, column names or order differ	reject the draft	breaks downstream consumers silently
Executes, row count differs	re-ask, then execute	semantic difference; never merge on syntax alone
Executes, counts match, checksum differs	hand-write, add fixture	aggregate or rounding drift the model will not catch
Executes, counts and checksum match	accept and cache the rewrite	stop spending calls on a solved pair

The rule underneath the table is straightforward: transpile first because it is free and deterministic, and escalate to a re-ask only when execution on the target engine disagrees with the source. Cache accepted rewrites keyed by normalised SQL text, or the loop re-derives the same conversion on every run.

Building the harness in five steps

1. Capture the source result once, on the source engine, with a read-only role. Store column names, row count, and a checksum over an ordered numeric column.
2. Transpile the statement and record whether the parse succeeded before you run anything at all.
3. Execute the output against a disposable target engine — I load the free server option with a schema-only dump plus a small fixture, run the probe, then destroy the instance.
4. Escalate only the disagreements to the re-ask function, and cap the loop at two attempts so a bad prompt cannot burn a budget overnight.
5. Promote every query that needed a hand fix into the fixture set, so the regression is covered the next time the agent proposes that shape.

Limits and who should skip this

Row-count equality is necessary and not sufficient, and the checksum only protects the columns you chose to cover. Fixtures that omit NULLs, duplicate keys, timezone boundaries and unicode will pass a rewrite that fails on the first real month-end load. Temperatures above zero make the re-ask step non-reproducible, so keep the repair path at zero and treat accepted rewrites as code rather than as prompt state. Skip this workflow if you have no fixture data, no disposable environment, or a policy that forbids sending schema text to a hosted model — in those cases a deterministic transpiler plus human review is the honest option.

Keep the model out of the decision about whether the rewrite is correct, and let execution decide. If you want to try the loop without paying for a second engine or a model bill, start with one fixture query against the free server option and let the table above route it.