Wrong SQL and hallucinated columns

Description

This lesson shows, run for real, the first two text-to-SQL dangers —the correctness dangers—. The first is the most insidious: SQL that runs without error but answers something else. A number shows up, it looks reasonable, and it's wrong. The second is louder but just as real: hallucinated columns and tables, when the model invents bookings.total or a reservations table that don't exist, and the engine rejects the query.

You'll run both against Reservo. The incorrect SQL will give you a false number next to the correct one so you can see the difference. The hallucinated column will give you a real no such column. The underlying lesson: generating SQL that looks fine isn't the same as generating correct SQL, and discovering the difference is exactly what validation (Module 4) and good schema context (Module 2) will solve.

Connection to the module

Lesson 02 showed you the translation working and planted a clue: a model that omits the status = 'confirmed' filter would give a different number. This lesson harvests that clue and runs it. It's the first time you see the model "get it wrong," and seeing it with real output is what makes it unforgettable why validation engineering exists.

Analogy: the calculator that never says "I don't know"

Think of two assistants you ask to add up a column in a spreadsheet.

The first, if it can't find the column you asked for, stops and tells you: "that column doesn't exist". Annoying, but honest —you know something went wrong—.

The second never says "I don't know." If it can't find the column, it adds up the one next to it and hands you a number with total confidence. The number looks perfectly plausible. You take it into the meeting. And it's wrong.

A language model can behave like either one, depending on the error. When it invents a column that doesn't exist, the database engine acts like the first assistant: it stops and objects. But when it generates syntactically perfect SQL that answers the wrong question, it behaves like the second: it hands over a number with confidence, and nobody objects, because as far as the engine is concerned the query was flawless. That second case —the confidently wrong number— is the most expensive danger of all.

Danger 1: incorrect SQL that looks fine

Let's go back to the previous lesson's question:

"How much revenue did each room bring in during March?"

In lesson 02, the model returned correct SQL that included WHERE b.status = 'confirmed'. But suppose this time the model omits that filter —a very common mistake, because "revenue" doesn't explicitly mention "confirmed"; the condition is implicit—.

Step 1 — The model generates SQL (conceptual). Assume claude-sonnet-5 returned this:

-- SQL generated by the model (realistic example) — MISSING the status filter
SELECT r.name AS room, SUM(b.price_cents) AS revenue_cents
FROM bookings b
JOIN rooms r ON r.id = b.room_id
WHERE b.start_at >= '2026-03-01' AND b.start_at < '2026-04-01'
GROUP BY r.name
ORDER BY revenue_cents DESC;

At first glance, it looks perfect: it joins the tables, sums, filters by month, groups by room. It runs without the slightest error.

Step 2 — We run it, next to the correct SQL, to compare (real execution):

import sqlite3

con = sqlite3.connect("reservo.db")

def show(sql):
    cur = con.execute(sql)
    print(" | ".join(d[0] for d in cur.description))
    for row in cur.fetchall():
        print(" | ".join(str(v) for v in row))
    print()

print("INCORRECT (no status filter):")
show("""
SELECT r.name AS room, SUM(b.price_cents) AS revenue_cents
FROM bookings b
JOIN rooms r ON r.id = b.room_id
WHERE b.start_at >= '2026-03-01' AND b.start_at < '2026-04-01'
GROUP BY r.name
ORDER BY revenue_cents DESC
""")

print("CORRECT (confirmed only):")
show("""
SELECT r.name AS room, SUM(b.price_cents) AS revenue_cents
FROM bookings b
JOIN rooms r ON r.id = b.room_id
WHERE b.status = 'confirmed'
  AND b.start_at >= '2026-03-01' AND b.start_at < '2026-04-01'
GROUP BY r.name
ORDER BY revenue_cents DESC
""")

con.close()

What to expect:

INCORRECT (no status filter):
room | revenue_cents
Boardroom | 32000
Lounge | 15000
Focus | 9500
Studio | 6400

CORRECT (confirmed only):
room | revenue_cents
Boardroom | 32000
Lounge | 15000
Studio | 6400
Focus | 2000

Look at Focus. The correct SQL says it brought in 2000. The incorrect one says 9500. A difference of 7500 cents —75 dollars— on a single room, and not a single error in sight.

Where did the difference come from? In March, Focus had two bookings: a confirmed one for 2000 (booking 14) and a cancelled one for 7500 (booking 10). The correct SQL ignores the cancelled one; the incorrect one adds it as if it had brought in money. And since a cancelled booking brings in no money, 9500 is simply false.

The scary part: if you had only seen the incorrect result —without the correct one next to it— you'd have had no way to know it was wrong. The number exists. It looks reasonable. The query ran. Everything screams "correct," and everything is wrong. This is the central danger of text-to-SQL, and why the guide devotes entire modules to validation (M4) and evaluation (M7).

Danger 2: hallucinated columns and tables

The second danger is louder, and that —paradoxically— makes it less dangerous: when the model invents a column or table that doesn't exist, the engine rejects it. But you only discover it if you run the SQL; if you showed it to the user as "here's your query" without running it, the hallucination would go unnoticed.

Hallucinated column

Suppose you ask the model for March revenue, and this time —because its idea of the schema is fuzzy— it assumes the bookings table has a column called total. It doesn't exist: the real column is called price_cents.

Step 1 — The model generates SQL (conceptual). Assume claude-sonnet-5 returned:

-- SQL generated by the model (realistic example) — 'b.total' DOES NOT EXIST
SELECT r.name AS room, SUM(b.total) AS revenue_cents
FROM bookings b
JOIN rooms r ON r.id = b.room_id
WHERE b.start_at LIKE '2026-03%'
GROUP BY r.name;

Step 2 — We run it, capturing the error (real execution):

import sqlite3

con = sqlite3.connect("reservo.db")

bad_sql = """
SELECT r.name AS room, SUM(b.total) AS revenue_cents
FROM bookings b
JOIN rooms r ON r.id = b.room_id
WHERE b.start_at LIKE '2026-03%'
GROUP BY r.name
"""

try:
    con.execute(bad_sql).fetchall()
except sqlite3.Error as e:
    print(f"{type(e).__name__}: {e}")

con.close()

What to expect:

OperationalError: no such column: b.total

The engine doesn't guess what you meant. It doesn't add up some "similar-looking" column instead. It stops and objects: no such column: b.total. This honest behavior is a blessing —it turns a hallucination into an explicit error— as long as you run the SQL and capture the error. All of lesson 04 in Module 4 is built on this idea: run the SQL inside a block that catches the error, and (later) feed it back to the model so it can correct itself.

Hallucinated table

The same thing happens at the table level. If the model believes bookings live in a reservations table (a reasonable-sounding name in English) instead of bookings:

con = sqlite3.connect("reservo.db")
try:
    con.execute("SELECT COUNT(*) FROM reservations").fetchall()
except sqlite3.Error as e:
    print(f"{type(e).__name__}: {e}")
con.close()

What to expect:

OperationalError: no such table: reservations

no such table: reservations. Again, the engine objects. And again, the objection only helps you if you ran the SQL.

These hallucinations have a clear cause: the model doesn't know what your tables and columns are called unless you tell it. Giving it that information —serializing the schema as context— is exactly the topic of Module 2. Good schema context drastically reduces naming hallucinations; validation against the real schema (M4) catches what's left.

The two dangers, side by side

The moral that orders the rest of the guide: never trust a model's SQL without running it, and never trust a result without a way to measure whether it's correct. The first commandment is fulfilled by validation; the second, by evaluation.

Common mistakes

1. Showing the SQL to the user without running it. If your assistant shows the user "here's the query that answers your question" but never runs it, column hallucinations pass silently —the user believes there's a reservations table—. Running it is what turns the hallucination into a visible error.

2. Trusting that "ran without error" means "it's correct." The Focus case (9500 vs. 2000) proves otherwise. The absence of an error only rules out the second danger, not the first.

3. Assuming the model knows your schema. It doesn't, until you give it to it. Inventing b.total isn't a whim of the model: it's what happens when it guesses names. The fix is context (M2), not scolding it.

4. Suppressing the error instead of using it. no such column: b.total isn't a failure: it's extremely valuable information. Module 4 feeds it back to the model so it can correct itself. Catching the error and discarding it wastes the best signal you have.

Exercises

Exercise 1: Spotting the silent one (Easy)

A user asks "how many bookings did member Ana Torres make?". A model returns SELECT COUNT(*) FROM bookings WHERE member_id = 1. Does it run? Does it answer correctly? Run it and explain what assumption it hides.

import sqlite3
con = sqlite3.connect("reservo.db")
def show(sql):
    cur = con.execute(sql)
    print(" | ".join(d[0] for d in cur.description))
    for row in cur.fetchall():
        print(" | ".join(str(v) for v in row))
show("SELECT COUNT(*) AS n FROM bookings WHERE member_id = 1")
con.close()

n
4

Exercise 2: Trigger a hallucination (Medium)

Write and run SQL that queries an invented column of the rooms table (for example, rooms.price, which doesn't exist —the real column is hourly_cents—). Capture and print the error.

import sqlite3
con = sqlite3.connect("reservo.db")
try:
    con.execute("SELECT name, price FROM rooms").fetchall()
except sqlite3.Error as e:
    print(f"{type(e).__name__}: {e}")
con.close()

OperationalError: no such column: price

Exercise 3: The wrong JOIN (Hard)

A user asks "how many payments did each member make?". A model returns the SQL below. Run it and explain why the result is misleading, even though it produces no error.

SELECT m.name, COUNT(*) AS n_payments
FROM members m
JOIN payments p ON p.booking_id = m.id
GROUP BY m.name;

import sqlite3
con = sqlite3.connect("reservo.db")
def show(sql):
    cur = con.execute(sql)
    print(" | ".join(d[0] for d in cur.description))
    for row in cur.fetchall():
        print(" | ".join(str(v) for v in row))
show("""
SELECT m.name, COUNT(*) AS n_payments
FROM members m
JOIN payments p ON p.booking_id = m.id
GROUP BY m.name
""")
con.close()

name | n_payments
Ana Torres | 1
Carlos Vega | 1
Diego Salas | 1
Elena Cruz | 1
Luis Prado | 1
Marta Ruiz | 1
Pablo Nunez | 1
Sofia Marin | 1

Summary and next step

• Danger 1 — incorrect SQL that looks fine: runs without error and answers something else. The March revenue SQL without the status filter gave Focus = 9500; the correct one, 2000. The difference (one cancelled booking) is invisible without comparing against the expected result.
• Danger 2 — hallucinated column/table: the model invents b.total or reservations, and the engine objects with no such column/no such table —but only if you run the SQL—.
• The first is silent and is countered with validation and evaluation; the second is loud and is countered with good schema context and validation.
• Moral that orders the guide: never trust a model's SQL without running it, nor a result without a way to measure whether it's correct.

Next lesson: Destructive queries and injection — The two security dangers, run against disposable copies of Reservo. You'll see a generated DELETE actually erase rows, and a user question manipulate the SQL to empty out a table.

Additional resources

1. SQLite — Result and Error Codes — What errors like no such column, returned by the engine, mean.
2. Python — sqlite3 exceptions — The error hierarchy we catch with except sqlite3.Error.
3. BIRD: text-to-SQL focused on execution accuracy — Why the community measures "does it give the same result as the correct SQL?" instead of "does the text match?".
4. Spider: failure-mode analysis in text-to-SQL — The benchmark where errors like the wrong JOIN and the missing filter were cataloged.