Agent-Written SQL Keeps Breaking Across Dialects? Transpile or Re-Ask the Model

When AI agents' generated SQL migrates from Postgres to MySQL, the most dangerous errors aren't syntax failures—they're queries that run on both engines but return different results. These silent errors don't raise exceptions, yet they quietly corrupt downstream reports. Drawing on real project experience, this article explores how to combine deterministic SQL transpilation with model re-asking to build a reliable dialect adaptation workflow.

Dialect Drift: Quiet but Expensive Errors

Dialect problems look like syntax errors, but that's not where the real cost lies. Statements that fail to parse are actually easy to handle—the parser rejects them outright, and you can retry in a loop with the error message attached. The real trouble is with constructs that parse on both engines but mean different things: ILIKE versus case-insensitive collations, the ordering of LIMIT and OFFSET, integer division, and the jsonb arrow operators that have no direct equivalent in MySQL.

A query that executes on both sides but returns 41 rows instead of 44 can pass every validation flow that only checks for exceptions. This asymmetry is the root of the choice between transpilation and model re-asking: the two approaches fail in different places, so the decision rule must be based on which failure you can detect cheaply.

AST (Abstract Syntax Tree) is the tree-shaped representation a SQL parser turns statement text into, with nodes corresponding to syntactic elements like SELECT, WHERE, and function calls, stripping away surface differences such as whitespace and case. sqlglot's transpilation flow reads the source dialect to produce an AST, then re-serializes that AST according to the target dialect's rules. The reason it guarantees determinism is that the AST-to-output mapping is purely functional—the same AST always yields the same text. But this also means the transpiler can only express correspondences that exist between the two dialects' ASTs; when the target dialect's AST has no equivalent node (for example, MySQL lacks a counterpart to the jsonb arrow operators), the transpiler can only pick an approximate substitute, and the semantic deviation is silently introduced at that step.

Option A: Deterministic Transpilation then Execution

The first stance holds that dialect conversion is fundamentally a parsing problem and should stay one. sqlglot reads the Postgres AST and outputs MySQL, and the same input always produces the same output—which means the entire conversion step can be reviewed via diff in code review. It's also fast enough to run on every draft generation, making the cost of fixing nearly zero.

**Window frame** is the part of the `OVER` clause that defines aggregate window boundaries, like `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. When the frame clause is omitted, the SQL standard specifies a default frame for window functions with `ORDER BY`, but different engines interpret and implement the default frame differently—especially with tied sort values, where the default frame may or may not include tied rows. Transpilers often fail to explicitly materialize the default frame on output, so the same window query may aggregate different numbers of rows on two engines. This kind of problem never raises a syntax error; it just shifts the aggregate result, falling squarely into the "executes but returns different results" silent-error category.
# dialect_probe.py — illustrative code; adjust identifiers to your 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))

Its weakness is equally obvious: the transpiler preserves only the semantics it can express, and guesses at the rest. Window frame defaults, string concatenation, and NULL ordering in ORDER BY are all places where the converted statement is legal, executable, and quietly wrong. Option A therefore only holds up when paired with execution verification on the target engine.

Option B: Re-Ask the Model with the Target Dialect in the Prompt

The second stance holds that the model already knows both dialects and just needs to be told which one matters. Free model access makes the cost of an extra call low enough to put inside a repair loop. I performed the re-ask step on MonkeyCode's free model access, whose free server option provides a disposable target engine for execution verification. (Disclosure: this article is part of MonkeyCode product promotion. Free quotas and their limits change over time—check your current account terms before planning a loop that runs on every commit.)

**Collation** determines how case and accents are treated during string comparison and sorting. PostgreSQL's `ILIKE` is an operator-level explicit case-insensitive match, while MySQL's behavior depends on the collation set for the column or connection—for example, `utf8mb4_general_ci` is case-insensitive by default. So rewriting `ILIKE` directly to `LIKE` may happen to be correct on MySQL, or may behave inconsistently depending on collation. Model re-asking has an advantage in these scenarios because it can choose `LOWER()` wrapping or a `COLLATE` clause based on the target column's collation, whereas a deterministic transpiler can only do symbol-level substitution and has no awareness of collation configuration in the schema.
# reask.py — unexecuted example; replace 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()

Re-asking can handle cases the transpiler can only guess at, including collation-sensitive comparisons and ON DUPLICATE KEY UPDATE conversions that need rewriting rather than simple symbol substitution. Its weakness is the mirror image: nondeterminism, the possibility of quietly dropping a WHERE clause, and the impossibility of distinguishing a plausible rewrite from a correct one before execution. Model re-asking without execution verification is just confident guessing with a few extra steps.

My Decision Rule

Neither approach survives on its own, and the dividing line isn't "simple queries versus complex queries" but "can I detect the failure by running a fixture." Whichever path you take, the fixture is the same artifact.

Observation after the step	Routing	Rationale
Transpilation throws a parse error	Re-ask, then execute	Syntax-level gap; the model often knows the correct form
Transpilation parses, target engine errors	Re-ask, then execute	AST is valid, but the function or syntax is unsupported
Execution succeeds, column names or order differ	Reject the draft	Silently breaks downstream consumers
Execution succeeds, row count differs	Re-ask, then execute	Semantic difference; never merge on syntax passing alone
Execution succeeds, row count matches, checksum differs	Hand-write, add to fixtures	Aggregation or rounding drift the model won't catch
Execution succeeds, row count and checksum both match	Accept and cache the rewrite	Stop spending calls on solved pairs

The rule beneath the table is straightforward: transpile first, because it's free and deterministic; escalate to re-asking only when the target engine's execution result diverges from the source. Cache accepted rewrites keyed by normalized SQL text, or the loop will re-derive the same conversions on every pass.

Building Verification Fixtures in Five Steps

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

Checksum here refers to an aggregate fingerprint (such as SUM or a hash) computed over one ordered numeric column of the result set in a fixed order, used to detect result differences without row-by-row comparison. It's more sensitive than row count—aggregation drift, rounding differences, and row-order misalignment can all change the checksum while leaving row count unchanged. Choosing which column to checksum matters: pick one that is numerically stable and unaffected by time zones or localization, and ensure the ORDER BY is deterministically unambiguous. The limitation of checksums is that they only cover the column included in the computation; deviations in other columns go undetected, so they supplement rather than replace the row-count check.

Limitations and Who Should Skip This

Equal row counts are necessary but not sufficient, and checksums only cover the columns you chose to cover. Fixtures that ignore NULLs, duplicate keys, time zone boundaries, and Unicode will let a rewrite pass tests while failing in a real month-end load. Temperatures above zero make the re-ask step irreproducible, so the repair path should stay at zero temperature and treat accepted rewrites as code rather than prompt state. If you have no fixture data, no disposable environment, or a policy against sending schema text to hosted models, skip this workflow—in those cases a deterministic transpiler plus human review is the honest option.

Keep the model away from the decision of "is the rewrite correct" and let execution adjudicate. If you want to try the loop without paying for a second engine or model bill, start with one fixture query and the free server option, and let the table above decide the routing.

One-line Summary

Transpile deterministically first, re-ask the model only on mismatch, and let execution fixtures adjudicate SQL dialect adaptation.

Paragraph Summary

The article discusses dialect adaptation when AI-generated SQL migrates from Postgres to MySQL, noting that the real danger isn't syntax errors but silent errors where both engines execute yet return different results. The author compares two approaches: deterministic transpilation (sqlglot) is free and diffable but quietly fails on window frames, NULL ordering, and collations; model re-asking handles complex semantic conversion but is nondeterministic and may drop WHERE clauses, so it must pair with execution verification. The core rule: transpile first, escalate to re-asking only when target-engine execution diverges from source. Verification relies on fixtures: capture source column names, row count, and checksum, execute against a disposable target engine, escalate only mismatches, and cap retries. The article stresses that equal row counts aren't sufficient, and fixtures must cover NULLs, time zones, and Unicode or they'll fail in real month-end loads.