The Dialect Trap: Why AI Writes SQL Your Database Refuses to Run

You ask an AI assistant for "the 10 most recent orders." It confidently hands you this:

SELECT TOP 10 *
FROM orders
ORDER BY created_at DESC;

Looks fine — until you run it on PostgreSQL and get syntax error at or near "10". The logic was perfect. The dialect was wrong. TOP is SQL Server syntax; Postgres wants LIMIT.

This is one of the most common and most frustrating failure modes of text-to-SQL. The query reads like valid SQL, passes a human eyeball test, and still bounces off your database because SQL isn't really one language — it's a family of closely-related dialects that disagree on the details. If you're building any kind of natural-language-to-SQL feature (or just pasting AI output into a query console), understanding the dialect trap will save you a lot of confusing error messages.

SQL is a standard that nobody fully follows

There's an ANSI SQL standard, but every database vendor extends and diverges from it. The core SELECT ... FROM ... WHERE is portable. Almost everything interesting around it is not. Here's a small slice of where the major engines disagree:

Task	PostgreSQL	MySQL	SQL Server	BigQuery
Limit rows	LIMIT 10	LIMIT 10	TOP 10	LIMIT 10
Concatenate strings	a || b	CONCAT(a, b)	a + b	CONCAT(a, b)
Current timestamp	NOW()	NOW()	GETDATE()	CURRENT_TIMESTAMP()
Extract month	EXTRACT(MONTH FROM d)	MONTH(d)	DATEPART(month, d)	EXTRACT(MONTH FROM d)
Quote an identifier	"my col"	`my col`	[my col]	`my col`

None of these are cosmetic. Each one is the difference between a query that runs and a query that throws. And some differences are worse than a clean error: they run and quietly return the wrong result.

Why AI gets this wrong so often

Large language models learn SQL from the enormous pile of SQL on the public internet. Two things about that pile work against you.

First, the training data is dominated by certain dialects. A lot of public text-to-SQL research and tutorial content is written against SQLite and MySQL, so models lean toward that syntax by default. Ask for a query without saying which database you use, and you'll often get SQLite-flavored or MySQL-flavored SQL — which may or may not match what you're actually running. One analysis found that roughly 32% of queries from a popular text-to-SQL benchmark (Spider) threw syntax errors when executed against PostgreSQL, even though they were "correct" for the dialect they were generated in.

Second, models blend dialects. Because the training data mixes every flavor together, a model can start a query in one dialect and finish it in another — using a Postgres || concatenation in the SELECT and a SQL Server TOP in the same statement. The result is SQL that belongs to no real database.

The model isn't "wrong" about SQL. It just doesn't know which of the several SQLs you meant, so it guesses.

The fix starts with telling the model what it's talking to

The single highest-leverage thing you can do is stop making the model guess. State the exact engine — and version — every time.

Compare a vague prompt:

Give me the 10 newest users.

with a dialect-anchored one:

You are writing SQL for PostgreSQL 16.
Return only a query, no explanation.
Give me the 10 newest users.

Schema:
users(id BIGINT, email TEXT, created_at TIMESTAMPTZ, plan TEXT)

The second prompt reliably produces:

SELECT id, email, created_at
FROM users
ORDER BY created_at DESC
LIMIT 10;

Notice two things are doing the work here. "PostgreSQL 16" pins the dialect. And the schema snippet — with real column types — quietly reinforces it: TIMESTAMPTZ and TEXT are Postgres-isms, so the model stays in the right neighborhood. Sharing your schema isn't just about avoiding hallucinated column names; the types are a dialect signal too.

Add a couple of examples in your dialect

If you're building this into a product, a system prompt plus one or two worked examples (few-shot prompting) pins the dialect far more firmly than an instruction alone. You're showing, not just telling:

-- Example (PostgreSQL):
-- Q: revenue per plan last month
SELECT plan, SUM(amount) AS revenue
FROM subscriptions
WHERE created_at >= date_trunc('month', now()) - interval '1 month'
  AND created_at <  date_trunc('month', now())
GROUP BY plan;

That one example teaches the model your date-math idioms (date_trunc, interval), your casing conventions, and your dialect all at once. For a handful of canonical question shapes, a few pinned examples go a long way. For a large, varied set, retrieving the most relevant example pairs per question (a RAG approach) scales better than stuffing them all in.

Validate before you trust — then let the error teach the model

Even with a perfect prompt, treat generated SQL as a draft. The cheapest safety net is to check the query compiles before you run it for real. Most engines give you a dry-run path:

-- PostgreSQL / MySQL: ask the planner to parse & plan without returning rows
EXPLAIN
SELECT plan, COUNT(*) FROM subscriptions GROUP BY plan;

-- BigQuery supports an actual dry run that validates and estimates cost,
-- without scanning data or incurring charges.

If the parse fails, don't just surface a raw error to your user — feed it back to the model:

Your query failed on PostgreSQL 16 with:
ERROR: function datepart(unknown, timestamp with time zone) does not exist

Rewrite it using PostgreSQL-native date functions.

This execution-feedback loop catches most dialect slips automatically. The model used DATEPART (SQL Server); the error tells it exactly what the target engine rejected, and the retry almost always lands on EXTRACT or date_trunc.

A transpiler as a backstop

Prompting reduces dialect errors; it doesn't eliminate them. If you need a hard guarantee, put a SQL transpiler between the model and the database. Libraries like sqlglot can parse SQL written in one dialect and re-emit it in another:

import sqlglot

# Model produced SQL Server-flavored SQL; we run Postgres.
generated = "SELECT TOP 10 name + '!' AS shout FROM users"

fixed = sqlglot.transpile(generated, read="tsql", write="postgres")[0]
# -> SELECT name || '!' AS shout FROM users LIMIT 10

Now even if the model drifts into the wrong dialect, the SQL that reaches your database is normalized to the one you actually run. It also gives you a parse step for free: if the transpiler can't parse it, you reject it before execution.

Common gotchas to watch for

A few dialect traps burn people repeatedly, even when the basic syntax is right:

• Silent wrong answers, not errors. Integer division is the classic one: 5 / 2 is 2 in PostgreSQL and SQL Server but 2.5 in MySQL and BigQuery. No error — just a quietly wrong number. These are the dangerous cases validation won't catch.
• Case sensitivity of identifiers. Postgres folds unquoted names to lowercase; MySQL's behavior depends on the OS and config. An AI that quotes "UserID" can make a column that worked suddenly "not exist."
• Fully-qualified names in BigQuery. BigQuery references tables as `project.dataset.table` and uses STRING instead of VARCHAR, with no timezone-naive TIMESTAMP. Models trained mostly on other engines routinely get this wrong.
• NULL handling and string funcs. CONCAT with a NULL argument behaves differently across engines; so does sorting NULLs. Don't assume portability.
• Version drift. FETCH FIRST 10 ROWS ONLY works on modern SQL Server and Oracle but not older versions. Saying "SQL Server 2022," not just "SQL Server," matters.

Key takeaways

SQL's dialects are the hidden reason so much AI-generated SQL "looks right but doesn't run." To stay out of the trap:

Do this	Why it helps
Name the engine and version in every prompt	Stops the model from guessing or blending dialects
Pass the schema with real column types	Types are a dialect signal and kill hallucinated columns
Give one or two examples in your dialect	Shows idioms the model can't infer from an instruction
Dry-run / EXPLAIN before executing	Catches syntax errors cheaply, before users see them
Feed execution errors back for a retry	Self-corrects most dialect slips automatically
Transpile as a backstop	Guarantees the SQL that reaches the DB is the right flavor

If you're wiring an AI assistant to a database through a schema-aware layer — a managed connector or MCP server, for example — much of this can be handled for you: the layer already knows which engine it's connected to and can pass that context (and the schema) along so the model writes for the right dialect from the start. That's a nicer place to solve the problem than re-explaining your database in every prompt.

Have you been bitten by the dialect trap? I'm curious which engine trips up your AI tools the most — drop your worst "looked right, wouldn't run" query in the comments, and share the tricks that fixed it for you.