Reading Errors and Debugging a Query

Every SQL writer sees error messages all day, beginners and seniors alike. The difference is that experienced people read the message, find the exact spot in seconds and move on. Database errors are short but precise: they name the word where parsing broke, the table or column that does not exist, the rule a row broke.

The harder bugs are the ones with no message at all: the query runs, returns a tidy table, and the numbers are wrong. This page covers both. It matters even more now that LLMs write SQL: a text-to-SQL system is only as good as its ability to notice an error, read it and repair the query, and to check a result that looks fine.

What you will learn

• Read the common errors: syntax, no such table or column, ambiguous column, constraint failures and type mismatches, in SQLite, PostgreSQL and MySQL.
• Look up a table's real columns when a name is wrong.
• Catch database errors in Python and use the message.
• Recognise logical bugs that produce no error.
• Follow a debugging routine: shrink the query, run the pieces, check row counts, and SELECT before you UPDATE or DELETE.

Syntax errors: read the word it points at

A syntax error means the database could not even understand the statement. The message names the place where it got confused:

SELECT name, FROM products;

Error: near "FROM": syntax error

near "FROM" does not mean FROM is wrong. It means the parser was fine up to there and then met a word that could not come next. The real mistake is usually just before the word it names: here, the comma after name promises another column, and FROM is not one. The same error, three ways:

-- PostgreSQL
SELECT name, FROM products;

Error: syntax error at or near "FROM"

-- MySQL
SELECT name, FROM products;

Error: ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM products' at line 2

MySQL quotes the text from the confusing point onward (near 'FROM products') and gives a line number; the line counts the -- MySQL comment too. Clauses in the wrong order give the same kind of error, because the order is fixed: SELECT … FROM … WHERE … ORDER BY … LIMIT.

SELECT name FROM products ORDER BY price DESC LIMIT 3 WHERE stock > 0;

Error: near "WHERE": syntax error

Other usual suspects: a missing closing quote (everything after it becomes "text"), a missing closing bracket, a keyword used as a name, and a stray comma before FROM or after the last column.

No such table, no such column

The statement is valid SQL, but it names something that does not exist. Nearly always a typo, a singular/plural slip, or a column that lives in a different table:

SELECT * FROM product;

Error: no such table: product

SELECT name, status FROM products;

Error: no such column: status

status exists, but in orders. Do not guess: look the columns up. In SQLite, PRAGMA table_info lists them with their declared types:

PRAGMA table_info(products);

+-----+-------------+--------------+---------+------------+----+
| cid | name        | type         | notnull | dflt_value | pk |
+-----+-------------+--------------+---------+------------+----+
|   0 | product_id  | INTEGER      |       0 | NULL       |  1 |
|   1 | name        | VARCHAR(100) |       1 | NULL       |  0 |
|   2 | category_id | INTEGER      |       0 | NULL       |  0 |
|   3 | price       | INTEGER      |       1 | NULL       |  0 |
|   4 | stock       | INTEGER      |       0 | NULL       |  0 |
+-----+-------------+--------------+---------+------------+----+

The equivalents are \d products in PostgreSQL's psql shell, DESCRIBE products; in MySQL, and .schema products in the sqlite3 shell. DB Browser for SQLite shows the same in its Database Structure tab. PostgreSQL words these errors as relation "product" does not exist (a relation is a table or view) and column "prise" does not exist; MySQL as ERROR 1146: Table … doesn't exist and ERROR 1054: Unknown column 'prise' in 'field list'.

Double quotes: works in SQLite, fails elsewhere

In standard SQL, 'single quotes' are text and "double quotes" are names of columns or tables. SQLite forgives "Gift Card": when no column has that name, it treats it as text. PostgreSQL does not:

-- PostgreSQL
SELECT name FROM products WHERE name = "Gift Card";

Error: column "Gift Card" does not exist

When an error says a column does not exist and the "column" is obviously a value, check your quotes.

Ambiguous column

When a query reads from two tables that share a column name, the database cannot know which one you mean. You will join tables later in this tutorial; here is the error you will meet then:

SELECT order_id, product_id, status
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id;

Error: ambiguous column name: order_id

Both tables have order_id. The fix is to say which one: SELECT orders.order_id, …. PostgreSQL says column reference "order_id" is ambiguous; MySQL says Column 'order_id' in field list is ambiguous. More in Joins: INNER JOIN and Table Aliases.

Constraint failures

A constraint is a rule the table enforces on every row (you will create them in CREATE, ALTER, DROP and Constraints). When an INSERT or UPDATE breaks one, the database refuses the whole statement. This Python loop tries five bad inserts and prints each error:

import sqlite3
from sqlhelp import con

attempts = {
    "duplicate email":  "INSERT INTO customers VALUES (9, 'Test User', 'nadia@example.com', NULL, '2026-07-01')",
    "missing name":     "INSERT INTO customers VALUES (9, NULL, 'test@example.com', NULL, '2026-07-01')",
    "free product":     "INSERT INTO products VALUES (12, 'Free Sticker', 4, 0, 100)",
    "unknown customer": "INSERT INTO orders VALUES (15, 99, '2026-07-01', 'pending')",
    "unknown status":   "INSERT INTO orders VALUES (15, 1, '2026-07-01', 'lost')",
}
for label, sql in attempts.items():
    try:
        con.execute(sql)
    except sqlite3.IntegrityError as error:
        print(f"{label:17} {error}")
con.rollback()

duplicate email   UNIQUE constraint failed: customers.email
missing name      NOT NULL constraint failed: customers.name
free product      CHECK constraint failed: price > 0
unknown customer  FOREIGN KEY constraint failed
unknown status    CHECK constraint failed: status IN ('pending', 'shipped', 'delivered', 'cancelled')

SQLite names the table and column for UNIQUE and NOT NULL, and prints the rule for CHECK, but says nothing about which foreign key failed. PostgreSQL is more helpful:

-- PostgreSQL
INSERT INTO orders VALUES (15, 99, '2026-07-01', 'pending');

Error: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"

Constraint errors are good news: the database stopped bad data at the door. In a data pipeline, catch them, log the offending row, and carry on with the rest, rather than letting one bad row crash the whole load.

A non-failure you might not expect: MySQL 8.0 silently ignores a REFERENCES written inside a column definition (customer_id INTEGER REFERENCES customers (customer_id)). A table written that way has no foreign key on MySQL, and an order for a non-existent customer 99 would be stored without a word. That is why shop.sql writes every foreign key as a separate FOREIGN KEY (customer_id) REFERENCES customers (customer_id) clause, which all three databases enforce. More in CREATE, ALTER, DROP and Constraints.

Type mismatches

What happens when you put text into a number column depends heavily on the database. SQLite is the most forgiving, sometimes too forgiving:

INSERT INTO products VALUES (12, 'Sticker', 4, 'cheap', 100);
SELECT product_id, name, price, typeof(price) AS stored_as
FROM products
WHERE product_id = 12;
DELETE FROM products WHERE product_id = 12;

+------------+---------+-------+-----------+
| product_id | name    | price | stored_as |
+------------+---------+-------+-----------+
|         12 | Sticker | cheap | text      |
+------------+---------+-------+-----------+

No error. SQLite stored the word cheap in an INTEGER column, and even the CHECK (price > 0) passed, because in SQLite any text sorts above any number. (The next page explains SQLite's type rules and the STRICT tables that close this hole.) The servers refuse:

-- PostgreSQL
INSERT INTO products VALUES (12, 'Sticker', 4, 'cheap', 100);

Error: invalid input syntax for type integer: "cheap"

-- MySQL
INSERT INTO products VALUES (12, 'Sticker', 4, 'cheap', 100);

Error: ERROR 1366: Incorrect integer value: 'cheap' for column 'price' at row 1

PostgreSQL is also strict about comparing different types: WHERE order_date > 5 fails with operator does not exist: date > integer. MySQL rejects the insert only because the server runs in strict mode (STRICT_TRANS_TABLES, the default since 5.7); without it, MySQL would turn 'cheap' into 0 with only a warning that nobody reads. Here the CHECK would then reject the 0, but a column without such a check would quietly store it.

Catching errors in Python

From Python, every sqlite3 error is a subclass of sqlite3.Error. The two you will see most are OperationalError (syntax, missing table or column) and IntegrityError (constraints). The message string is exactly the text you have been reading, which makes it useful to log, or to give back to an LLM that wrote the query:

import sqlite3
from sqlhelp import con

def try_query(sql):
    """Run a query; return its rows, or the error as text."""
    try:
        return con.execute(sql).fetchall()
    except sqlite3.Error as error:
        return f"{type(error).__name__}: {error}"

# what a model might write for "how many products cost more than 5000?"
print(try_query("SELECT COUNT(*) FROM product WHERE price > 5000"))
print(try_query("SELECT COUNT(*) FROM products WHERE price > 5000"))

OperationalError: no such table: product
[(3,)]

A text-to-SQL agent does exactly this in a loop: run the query, and if it fails, send the error text back with the table list so the model can fix the name. Error messages are written for humans, and LLMs read them well too.

Bugs that give no error

The database only checks that a query is valid, not that it answers your question. These run happily and return wrong results:

SELECT name price          -- missing comma: "price" became an alias for name
FROM products
ORDER BY product_id
LIMIT 3;

+---------------------------+
| price                     |
+---------------------------+
| Python Crash Course       |
| Hands-On Machine Learning |
| Mechanical Keyboard       |
+---------------------------+

SELECT 7 / 2   AS whole_numbers,     -- integer division drops the .5
       7 / 2.0 AS one_decimal;

+---------------+-------------+
| whole_numbers | one_decimal |
+---------------+-------------+
|             3 |         3.5 |
+---------------+-------------+

Integer division is a dialect trap too: SQLite and PostgreSQL give 3, MySQL's / gives 3.5000. The most common silent bugs, most of them met on the last three pages:

A debugging routine

When a query errors, or returns something that looks off, work through it step by step instead of editing at random.

1. Read the whole message. Find the word or name it points at, and look just before it.
2. Shrink the query. Comment out lines until it runs, then add them back one at a time. The line that breaks it, or changes the result unexpectedly, is your bug.
3. Run the pieces and count rows. Check how many rows each step keeps. A number that is surprisingly large or small points at the wrong step.
4. Look at the real values. SELECT DISTINCT status FROM orders shows whether it is 'delivered' or 'Delivered'.
5. Test the edges. NULLs, ties, the first and last day of a range, a row you know should (or should not) be in the result.

Here it is on a real bug. The task: "Electronics or Accessories products with fewer than 10 in stock, so we can reorder." The first attempt:

SELECT name, category_id, stock
FROM products
WHERE stock < 10 AND category_id = 2 OR category_id = 4
ORDER BY product_id;

+-----------------------------+-------------+-------+
| name                        | category_id | stock |
+-----------------------------+-------------+-------+
| 27-inch Monitor             |           2 |     5 |
| Laptop Stand                |           4 |    25 |
| USB-C Hub                   |           4 |     0 |
| Noise-Cancelling Headphones |           2 |     8 |
+-----------------------------+-------------+-------+

Edge check: the Laptop Stand has 25 in stock, so it should not be there. Shrink the query to the first condition only:

SELECT name, category_id, stock
FROM products
WHERE stock < 10
--  AND category_id = 2 OR category_id = 4
ORDER BY product_id;

+-----------------------------+-------------+-------+
| name                        | category_id | stock |
+-----------------------------+-------------+-------+
| 27-inch Monitor             |           2 |     5 |
| USB-C Hub                   |           4 |     0 |
| Noise-Cancelling Headphones |           2 |     8 |
+-----------------------------+-------------+-------+

Three rows, and all of them are Electronics or Accessories anyway. So the first condition is fine, and the bug is in the line we switched off: AND binds before OR, so OR category_id = 4 brought in every accessory. Fix it and the result matches the step above:

SELECT name, category_id, stock
FROM products
WHERE stock < 10
  AND category_id IN (2, 4)
ORDER BY product_id;

+-----------------------------+-------------+-------+
| name                        | category_id | stock |
+-----------------------------+-------------+-------+
| 27-inch Monitor             |           2 |     5 |
| USB-C Hub                   |           4 |     0 |
| Noise-Cancelling Headphones |           2 |     8 |
+-----------------------------+-------------+-------+

SELECT before you UPDATE or DELETE

For changes, the routine has one more rule, and it is the one that saves careers: run the WHERE as a SELECT first. If the SELECT returns the rows you expect, swap SELECT … for DELETE and keep the same WHERE. Inside a transaction you also get a last chance to check the number of rows changed:

from sqlhelp import con

# 1. Look first: which rows does the WHERE match?
print(con.execute("SELECT order_id, product_id FROM order_items WHERE order_id = 5").fetchall())

# 2. Change with the same WHERE, then check the count before committing
cur = con.execute("DELETE FROM order_items WHERE order_id = 5")
print("rows deleted:", cur.rowcount)

# 3. This is only a demo, so undo it (in real work: con.commit() if the count is right)
con.rollback()
print("rows left:", con.execute("SELECT COUNT(*) FROM order_items").fetchone()[0])

[(5, 5)]
rows deleted: 1
rows left: 20

If rowcount had said 20 instead of 1, you would roll back and look again, not explain to your team why the order history is gone. INSERT, UPDATE, DELETE and Upserts, Safely builds this into a full routine.

Common mistakes

1. Fixing the word the error names. near "FROM": syntax error usually means the problem is just before FROM, such as a trailing comma.
2. Treating "no error" as "correct". Check the header row, the row count and one row you can verify by hand.
3. Changing several things at once. Then you do not know which change fixed it, or which one broke something else. Change one thing, run, look.
4. DELETE or UPDATE without a SELECT first. Run SELECT … WHERE <same condition>, check the rows, then change them.
5. Trusting SQLite's leniency. Double-quoted strings, aliases in WHERE and text in number columns all "work" in SQLite and fail (or are refused) on PostgreSQL. Write standard SQL even when practising on SQLite.

Try it yourself

1. Easy: This query fails: SELECT name, price, FROM products ORDER BY price;. Read the error, then fix it.
2. Medium: SELECT name FROM customers WHERE city = NULL; returns nothing and no error. Explain why, and fix it.
3. Medium: Write a Python function columns(table) that returns the list of column names of a table using PRAGMA table_info, and print the columns of orders.
4. Hard: The task is "names and prices of Books (category 1) or Courses (category 3) under 3000 taka, cheapest first". This query has three bugs and no error: SELECT name price FROM products WHERE category_id = 1 OR category_id = 3 AND price < 3000 ORDER BY price DESC;. Find all three and fix them.

Answers

-- 1: the error is near "FROM"; the stray comma before it is the bug
SELECT name, price FROM products ORDER BY price;

-- 2: "= NULL" is unknown for every row; test for NULL with IS
SELECT name FROM customers WHERE city IS NULL;

from sqlhelp import con

# 3: PRAGMA table_info returns one row per column; the name is the second field
def columns(table):
    return [row[1] for row in con.execute(f"PRAGMA table_info({table})")]

print(columns("orders"))

-- 4: missing comma, AND/OR precedence, DESC instead of ascending
SELECT name, price
FROM products
WHERE category_id IN (1, 3)
  AND price < 3000
ORDER BY price, product_id;

For 4, the fixed query returns Python Crash Course (1200) and Hands-On Machine Learning (2500). SQL Masterclass costs exactly 3000, so "under 3000" excludes it; check the task wording when a boundary row is in doubt.

Summary

• Syntax errors point at the word where parsing failed; the mistake is usually just before it.
• "No such table/column" is a name problem: look up the real names with PRAGMA table_info, \d or DESCRIBE, and check your quotes.
• Constraint failures (UNIQUE, NOT NULL, CHECK, FOREIGN KEY) are the database protecting your data. SQLite is lenient about types; PostgreSQL and strict-mode MySQL are not.
• In Python, catch sqlite3.Error and keep the message: it is useful for logs and for LLM repair loops.
• The worst bugs give no error. Shrink the query, run the pieces, count rows, test edges, and SELECT before every UPDATE or DELETE.

Next: Data Types, NULL and Choosing the Right Type. Several bugs on this page came from types: text stored in a number column, integer division, dates as text. Next you will see exactly what types each database offers and how to choose them.