Debugging queries

What should I do first when a query fails?

Read the message. It sounds obvious, yet the most common debugging mistake is to glance at the red text, guess, and start changing things. The Messages panel shows SQLite's own complaint in plain English, plus the line number where it noticed the problem. That line is where to start looking, though the actual mistake is often a line or two above it.

Next, change one thing at a time. If you edit three clauses at once and the query starts working, you won't know which edit fixed it, and one of the other two may have introduced a new problem that only shows up next week. Small edits, run, look, repeat. The query runs as you type here, so each step costs you nothing.

Finally, keep a known answer in mind. Before trusting a total, work out one row by hand from the Tables view. If the report says Gwen spent 580 pence in September and you can see two September orders for her, you already know something is wrong, even with no error on screen. Wrong answers with no error are the dangerous kind, and most of what follows is about them.

What do the common error messages mean?

SQLite has a small set of messages you will see over and over. Learn these and most errors become a two-second fix.

• no such column: p.price means no table behind that alias has a column with that name. Check the spelling and check the alias in the schema tab.
• no such table: order means the table name is wrong. Plural versus singular is the usual cause (order against orders).
• ambiguous column name: name means two joined tables both have that column. Put the alias in front: c.name.
• near "FROM": syntax error means SQLite got confused at that word. Look just before it for a trailing comma, a missing comma, or an unclosed bracket or quote.
• incomplete input means the statement ended early, usually because a quote or bracket was never closed.
• misuse of aggregate function SUM() means an aggregate sits somewhere it can't, most often in WHERE. Move that test to HAVING.
• FOREIGN KEY constraint failed and NOT NULL constraint failed come from INSERT and UPDATE: the data broke a rule on the table, and nothing was changed.

How do I check what is really in the tables?

Many bugs come from a wrong belief about the data: a column you thought was a number is text, a date has a time attached, a customer you expected has no orders. Before reasoning about the query, look at what it is reading. The Tables view shows every table and every row after your code runs, and the schema tab shows exactly how each table was created, including the column types.

You can also ask SQLite directly. pragma_table_info lists a table's columns with their declared types, which settles any argument about whether a column is called price or price_pence. The query below shows that loyalty_points was declared as TEXT, a detail that comes up again further down.

SELECT name, type, "notnull"
FROM pragma_table_info('customers');

How do I rebuild a query clause by clause?

When a long query gives a wrong answer and nothing in the error panel helps, take it apart. Start from the FROM table alone and look at the rows. Add the first JOIN and look again. Add the next JOIN. Then the WHERE. Then GROUP BY and the aggregates. The step where the rows stop matching what you expect is the step with the bug.

This mirrors the order the database itself works in: FROM and JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY and LIMIT. The Rows view follows the same order and shows which rows each step keeps or drops and why, so you can often skip the rebuilding and read the steps directly.

While rebuilding, select plain columns before aggregates. SELECT o.id, o.ordered_on, oi.qty shows you the actual rows a SUM will add up. Once those rows look right, swap in the SUM and the GROUP BY.

-- Step 2 of the rebuild: orders joined to their items, no totals yet
SELECT o.id, o.customer_id, o.ordered_on, oi.product_id, oi.qty
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.ordered_on >= '2026-09-01'
ORDER BY o.id;

Why does my join multiply rows?

A join produces one row for every matching pair. Join orders to order_items and an order with three items becomes three rows. That is exactly what you want when you sum items, and exactly wrong when you sum something that belongs to the order itself, like a delivery fee. Priya's single 150p fee turns into 450p.

The test is to count before and after. Run SELECT COUNT(*) on the table whose values you are adding up, then on the joined result. If the joined number is bigger, each value is being repeated. The fix is either to drop a join the report doesn't need, or to total the detail rows first (in a WITH step) and join the totals afterwards.

The Rows view makes this visible: after the JOIN step you will see the same order id several times. When an AI hands you a report full of joins, this is the first thing to check.

-- One order, three items, so the fee is counted three times
SELECT o.id, SUM(o.delivery_fee_pence) AS fee_counted, COUNT(*) AS joined_rows
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.id = 4
GROUP BY o.id;

Why do NULLs make rows vanish?

Any comparison with NULL gives NULL, and WHERE keeps only rows where the test is true. discount_code = NULL is never true, so it silently returns nothing. discount_code <> 'AUTUMN' also skips the rows where the code is NULL, which surprises people who expected 'every order that isn't AUTUMN'. Use IS NULL, IS NOT NULL, or add OR discount_code IS NULL explicitly.

NULL surprises show up in counts too. COUNT(*) counts rows and COUNT(discount_code) counts only non-NULL values, so the two numbers differ on purpose. NOT IN with a list that contains a NULL returns no rows at all. And a LEFT JOIN fills unmatched rows with NULLs, which a later WHERE on the right-hand table throws away without any warning.

When a filter returns fewer rows than you expect, select the column on its own and look for NULLs in the Tables view before you change anything else.

SELECT
  COUNT(*) AS all_orders,
  COUNT(discount_code) AS with_code,
  SUM(discount_code IS NULL) AS without_code
FROM orders;

Why does '10' compare differently from 10?

SQLite stores each value with its own type, and text compares letter by letter. As text, '85' is greater than '100' because '8' comes after '1', the same way 'banana' comes after 'apple'. The number 85 is less than the number 100. Whether you get the first comparison or the second depends on the types involved.

The loyalty_points column was declared TEXT because the data came from a spreadsheet. When you write loyalty_points >= 100, SQLite converts the 100 to text to match the column and compares as text. Tom's '85' passes. CAST(loyalty_points AS INTEGER) >= 100 forces a numeric comparison and gives the answer you meant. Sorting has the same trap: ORDER BY loyalty_points puts '9' after '300'.

The longer-term fix is a column with the right type. PostgreSQL and MySQL are stricter here: PostgreSQL refuses to compare text with a number at all, which turns this silent bug into a loud error.

SELECT name, loyalty_points,
  loyalty_points >= 100 AS text_test,
  CAST(loyalty_points AS INTEGER) >= 100 AS number_test
FROM customers
ORDER BY CAST(loyalty_points AS INTEGER);

Why is my date range off by one day?

Dates here are text in YYYY-MM-DD form, and they sort correctly as text. The trouble is at the edges. ordered_on < '2026-09-30' stops before the 30th, so the last day of the month falls out. That is the bug in the task above: Gwen's 30 September order vanished and her total dropped without any error.

BETWEEN '2026-09-01' AND '2026-09-30' includes both ends and works for plain dates. It breaks the day the column starts holding times as well: '2026-09-30 14:05' is greater than '2026-09-30' as text, so afternoon orders on the last day drop out. The pattern that works for both is a closed start and an open end: >= '2026-09-01' AND < '2026-10-01'.

Check edges deliberately. Look for rows on the first day, the last day, and the day after, and confirm each one lands on the side you intend.

SELECT id, ordered_on
FROM orders
WHERE ordered_on >= '2026-09-01'
  AND ordered_on < '2026-10-01'
ORDER BY ordered_on;

What does a calm debugging routine look like?

None of these steps needs cleverness. They replace guessing with looking, which is faster even when it feels slower. The same routine works on a query you wrote, one a colleague wrote, and one an AI tool produced.

• Read the error and its line number before touching anything.
• Check the schema tab or pragma_table_info for real table and column names and types.
• Work out one expected answer by hand from the Tables view.
• Rebuild the query one clause at a time, or step through it in Rows.
• Count rows before and after each join.
• Look for NULLs in every column you filter on.
• Check types when comparing or sorting numbers.
• Test the first day, last day and day after of every date range.

Field notes

• Read the error message carefully before changing anything.
• Look at the real rows in the tables, because many bugs come from a wrong belief about the data.
• Rebuild a long query one clause at a time and check the rows after each step.
• For a month of dates, use >= the first day and < the first day of the next month.

Easy to get wrong

A comparison with NULL is never true, so the first version returns no rows and gives no error.

Patching the end of a range with a late time still misses edge cases and is hard to read. An open upper bound on the next day covers dates and date-times alike.

Each extra item row repeats the order's fee. Join only the tables the answer needs, or total the detail table before joining it.

On a TEXT column the comparison is done as text, so '85' counts as greater than '100'.

Questions people ask

The error points at line 5, but line 5 looks fine. Why?

SQLite reports where it noticed the problem, which is often just after the real mistake. A missing comma at the end of line 4 is only detected when line 5 starts. Read the line above as well.

How do I debug a query that gives no error but the wrong answer?

Work out one row's correct answer by hand, then step through the query in Rows or rebuild it clause by clause until you find the step where that row goes wrong. Count rows after every join.

Should I paste the error into an AI tool?

You can, and include the query and the table definitions from the schema tab so it isn't guessing names. Then check its answer with the same steps here, because a fix that removes the error can still return wrong rows.

Why does my query run fine in SQLite but fail in PostgreSQL?

SQLite is forgiving: it compares text with numbers and allows bare columns next to aggregates. PostgreSQL rejects both. A query that only works because SQLite is lenient usually has a hidden bug anyway.

What does 'stopped after 2.5 seconds' mean?

The query ran too long and was stopped. On small lesson tables that almost always means a join with a missing or wrong ON condition, which pairs every row with every other row.