Contexto y CódigoSoftware development and artificial intelligence
Español

AI-generated SQL: why it fails silently

Leonardo Gurgitano7 minLeer en español

Rows of identical seats repeating into the distance, in black and white.
Rows of identical seats repeating into the distance, in black and white.

When a language model writes code in almost any language, something catches the mistake before it reaches production: the compiler rejects what does not type check, the test fails, the program throws an exception.

With SQL that safeguard does not exist. A badly written query runs without complaint and returns rows. The rows look reasonable. So does the number in the report. Nobody finds out until somebody compares that number with another source, weeks later.

This is not an argument against using language models with SQL: they save a lot of time, especially on long queries where you know what you want but cannot recall the exact syntax. It is an argument for verifying them differently.

The two mistakes that repeat most

These are not exotic cases. They are the two that come up over and over, and both look perfectly correct when you read them.

The JOIN that multiplies the rows

You have orders, and each order has several items. You want the total billed per customer:

SELECT   o.customer_id,
         SUM(o.total) AS billed
FROM     orders o
JOIN     order_items i ON i.order_id = o.id
GROUP BY o.customer_id;

It runs. It returns one customer per row and a number in each. And it is wrong.

The JOIN produces one row per item. A 300-peso order with three items appears three times, so SUM(o.total) adds up to 900. The report’s total is inflated in proportion to the number of items per order — a factor that also varies between customers, so the ranking comes out wrong too.

The JOIN with order_items was not even needed for this question. That is what usually happens: the items get mentioned while describing the problem, and they end up in the query.

The NOT IN that a single NULL cancels

You want the customers who never bought anything:

SELECT * FROM customers
WHERE  id NOT IN (SELECT customer_id FROM orders);

If one single row in orders has customer_id as NULL, this query returns zero rows, always. Not an incomplete result: zero, in every case.

The reason is how NULL works in SQL. id NOT IN (1, 2, NULL) evaluates as id <> 1 AND id <> 2 AND id <> NULL, and that last comparison is neither true nor false: it is unknown. And true AND unknown is unknown, which does not pass the filter.

The version that does not have that problem:

SELECT   c.* FROM customers c
WHERE    NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

A language model writes the first version frequently, because it is the one that appears most in the material it learned from. And “zero customers without purchases” is a result that can sound plausible.

Five checks before accepting one

None of them takes more than a minute, and they catch most of these cases.

1. Count the rows before and after the JOIN

This is the most profitable check of all, and it is exactly for the first mistake:

SELECT COUNT(*) FROM orders;                                  -- 12,480
SELECT COUNT(*) FROM orders o JOIN order_items i ON i.order_id = o.id;  -- 41,902

If the number grows, the JOIN is multiplying rows. That is not always wrong — sometimes it is exactly what you want — but if there is a SUM or an AVG afterwards over a column from the left-hand table, then it is definitely wrong.

2. Ask the database for the plan, and read the real row counts

EXPLAIN ANALYZE SELECT ...;

EXPLAIN on its own shows what the database estimates. With ANALYZE it runs the query and shows what actually happened. What matters is not the cost: it is comparing the estimated rows with the real ones at each step. A difference of several orders of magnitude means the database is working from wrong assumptions, and it is usually why a query takes too long.

In SQL Server the equivalent is SET STATISTICS PROFILE ON or the actual execution plan.

3. Every write, inside a transaction you roll back

For any UPDATE or DELETE you did not write yourself in full:

BEGIN;
UPDATE orders SET status = 'cancelled' WHERE ...;
-- How many rows does it say it affected? Is that the number you expected?
ROLLBACK;   -- COMMIT only if the number adds up

The affected-row count is the most direct check there is. A WHERE with one condition too many returns zero; one with a condition too few returns everything. Both are obvious in that number, and neither throws an error.

4. Give it the schema, do not let it guess

Almost every mistake with columns and relations comes from the model having to guess the structure. Pasting the definition of the tables involved takes ten seconds:

-- PostgreSQL: \d orders    in psql
-- SQL Server:
SELECT column_name, data_type, is_nullable
FROM   information_schema.columns
WHERE  table_name IN ('orders', 'order_items');

The detail that changes the result most is which columns allow NULL, which is exactly what never gets mentioned when describing the problem and what causes the second mistake above.

What is worth delegating

With those checks in place, there is work where a language model does very well and the risk is low:

  • Translating between dialects. From SQL Server syntax to PostgreSQL, or the other way round. It is mechanical, tedious work where the mistakes show up when you run it.
  • Explaining an inherited query. Asking it to describe in words what a two-hundred-line query nobody remembers actually does. You verify by reading, not by running.
  • Writing the window function or recursive CTE you know exists but whose exact syntax you never remember.
  • Generating test data that respects the schema’s constraints.

The new part: semantic search inside the database

There is a recent change worth knowing about, because it removes a whole piece of infrastructure.

Until recently, searching by meaning — finding documents similar to a question, not ones containing the same words — required a separate specialised database, with its own synchronisation and its own deployment.

That changed. PostgreSQL 18 brought the vector type and the HNSW and IVFFlat indexes into the engine, alongside the traditional indexes; before that you had to install the pgvector extension. SQL Server 2025 ships a native VECTOR(n) type and functions such as VECTOR_DISTANCE, with an approximate index integrated into the optimiser.

The technique consists of turning each text into a list of numbers representing its meaning — an embedding — and searching for the ones closest to each other.

But the interesting part is not semantic search itself, which already existed. It is that it now combines with relational filters in a single query:

SELECT   d.title,
         d.embedding <=> :query AS distance
FROM     documents d
JOIN     projects p ON p.id = d.project_id
WHERE    p.customer_id = 42         -- ordinary relational filter
  AND    d.published_at > '2026-01-01'
  AND    d.status = 'published'
ORDER BY distance                    -- ordered by semantic similarity
LIMIT    10;

With a separate vector database, this is two queries and a join in the application code: fetch the hundred most similar, filter by permissions and dates, and hope ten are left. Here the engine resolves it all together, with the integrity constraints and the permissions you already had.

Before adding a vector database to your stack, it is worth checking whether the one you already use is enough. For medium volumes, it almost always is.

In short

SQL is the worst language in which to accept generated code without reviewing it, because it is the only one where the mistake does not announce itself: the query runs, returns believable rows, and the problem shows up weeks later in a report that does not add up.

The two checks that give back the most for the time they cost: count the rows before and after every JOIN, and wrap every write in a transaction you roll back until the affected-row count is the one you expected.

Comments