ELI5: Why does my AI assistant sometimes suggest obviously wrong SQL?

Hello everyone. I've noticed a recurring theme in questions from developers who are new to using AI coding assistants, particularly around SQL generation. It often boils down to a variation of: "The assistant gave me SQL that looked right but was completely wrong for my database. Why is it so confidently incorrect?"

This is an excellent and crucial question. As someone who designs APIs and works with data layers daily, I can explain the core reasons. It's not that the model is "stupid"—it's operating under specific constraints and with specific training data that lead to these characteristic failure modes.

Fundamentally, the AI assistant is a **pattern completion engine**, not a reasoning database engine. It has been trained on a massive corpus of text and code from the public internet, including countless SQL examples, blog tutorials, and Stack Overflow answers. When you give it a prompt, it generates the most statistically likely sequence of tokens (words, symbols) to follow. This has little to do with *understanding* your specific schema, your RDBMS's dialect, or the actual relationships in your data.

Let's break down the most common reasons for these "obviously wrong" suggestions:

* **Lack of Live Schema Context:** The model cannot introspect your database. If you simply ask, "Write a query to get users and their last order date," it must invent plausible table and column names. It will pull common patterns like `users`, `orders`, `customer_id`, and `created_at`.
```sql
-- AI might generate this generic guess
SELECT u.name, MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
```
But your actual schema might have `tbl_customer`, `purchase_record`, `cust_id`, and `purchase_timestamp`. The query is syntactically valid SQL but semantically useless to you.

* **Dialect Hallucination:** SQL is not a single language. PostgreSQL, MySQL, SQL Server, and SQLite have important differences in functions, syntax, and even core features (e.g., full outer joins, date math, string manipulation). The model's training data is a mix of all dialects. Without explicit guidance, it might give you a `LIMIT` clause (MySQL, PostgreSQL) when you're using SQL Server (which needs `TOP` or `FETCH NEXT`).

* **Over-reliance on Common Patterns:** The model excels at the most frequent patterns. Need a `JOIN`? It will almost always default to an `INNER JOIN`. However, for a report that should list *all* customers regardless of orders, a `LEFT JOIN` is correct. The AI may not grasp the subtle semantic difference you intend unless you are extremely precise in your prompt.

* **The "Confidence" Illusion:** These models are designed to produce fluent, coherent output. There is no inherent "I don't know" signal in the way they are typically presented to us. So, when faced with ambiguity, it will still generate a complete, well-formed query. This fluency masks the underlying guesswork.

So, what can you do? **Treat the AI as a powerful auto-complete, not an oracle.** Your role is to provide the critical context it lacks:

1. **Be exhaustively specific in your prompt.** Include your RDBMS, key table names, and column names.
* *Weak Prompt:* "Write a query to find duplicate email addresses."
* **Strong Prompt:** "Using PostgreSQL, write a query for a table named `app_user` with columns `id`, `email_address`, and `created_at`. Find all duplicate values in the `email_address` field and show the count per duplicate."

2. **Use it to generate a template or a starting point,** then apply your own knowledge to correct dialect specifics and verify logic against your schema.

3. **For complex logic,** break the problem down. Ask it to write a CTE for a sub-problem, or to explain the step-by-step logic before writing the final query.

The key takeaway is that the assistant suggests wrong SQL for the same reason a brilliant chef given random ingredients might make a strange stew: they're working with what they've been given and their general experience, not the specific recipe and pantry you have at home. Your domain knowledge—your schema—is the most vital ingredient.

I'm curious to hear about the specific types of "wrong SQL" you've encountered. Was it a dialect issue, a completely hallucinated schema, or a subtle logic error? Sharing examples can help us all craft better prompts.

—Felix