SQL Debugging and Troubleshooting

Concept

SQL errors fall into two groups:

Errors the database reports — syntax errors, missing objects, constraint violations, type errors, permission errors.

Silent logic errors — the query runs but returns wrong results (wrong join type, fan-out, NULL handling, wrong date boundary). These are more dangerous.

Why It Matters

Knowing what each error message means and having a systematic debugging method saves hours and prevents wrong data from reaching reports and customers.

Syntax

Where to find error details:

Error code format SQLSTATE (e.g., 23505) DETAIL, HINT, CONTEXT lines

Error number + SQLSTATE (e.g., 1062 (23000)) SHOW WARNINGS;

Msg number, level, state, line (e.g., Msg 2627) ERROR_MESSAGE(), ERROR_LINE() in CATCH Result code (SQLITE_CONSTRAINT) Extended result codes

Standard SQLSTATE classes: 23xxx integrity constraint violation, 42xxx syntax/access rule violation, 22xxx data exception, 40xxx transaction rollback (serialization failure 40001, deadlock 40P01 in PostgreSQL), 28xxx invalid authorization.

Example

45.1 Syntax errors Cause: missing BY. Other frequent causes: missing commas between columns, a trailing comma before FROM, unbalanced parentheses/quotes, reserved words as identifiers (SELECT order FROM...), clauses in the wrong order (WHERE after GROUP BY), dialect syntax on the wrong DBMS (TOP in PostgreSQL, LIMIT in SQL Server).

Fix: read the position given by the error; check the token just before it.

45.2 Unknown column Check columns:

45.3 Table doesn't exist Causes: wrong name/plural; wrong database/schema (search_path in PostgreSQL, USE in MySQL/SQL Server, schema owner in Oracle); case sensitivity (MySQL on Linux); missing privileges (Oracle reports ORA-00942 also when you lack access); temp table from another session.

45.4 Duplicate key Causes: explicit IDs colliding with existing data; sequence behind after a bulk import (Module 29); retried request without idempotency; duplicates in the source of an INSERT... SELECT.

Debug: find the conflicting row; for bulk loads, find duplicates in the source first (GROUP BY key HAVING COUNT(*) > 1); use upsert when appropriate.

45.5 Foreign key constraint failure Fix: insert parents before children; delete children before parents (or use a deliberate ON DELETE action); check for orphans during migrations:

45.6 NOT NULL violation Causes: omitted required column; explicit NULL overriding a default; INSERT... SELECT from a source with NULLs; an UPDATE setting a column from a subquery that returned no row (→ NULL).

45.7 Data type mismatch Fix: use ISO literals (DATE '2026-01-05'), explicit CAST, matching parameter types in application code. Watch for silent implicit conversion in MySQL ('12abc' = 12 is true with a warning) — check SHOW WARNINGS.

45.8 Ambiguous column Fix: qualify every column in multi-table queries (e.dept_id).

45.9 GROUP BY errors Fix: aggregate the column (MAX(first_name), STRING_AGG), add it to GROUP BY, or use a window function; move aggregate filters to HAVING.

45.10 Subquery errors Fix: use IN/ANY/ALL or an aggregate; alias derived tables and computed columns.

45.11 Permission errors Check the current user (SELECT CURRENT_USER;), role memberships, schema USAGE privileges (PostgreSQL), and whether access should go through a view or procedure. Request the minimum privilege needed — do not ask for admin rights.

45.12 Other common runtime errors Division by zero NULLIF(denominator, 0) Numeric overflow / value too long

Value exceeds DECIMAL precision or VARCHAR length Widen type, validate input Consistent lock order, retry Lock wait timeout Long transaction holding locks

Find blocker (pg_stat_activity, sys.dm_exec_requests, SHOW ENGINE INNODB STATUS) Could not serialize access SERIALIZABLE/REPEATABLE READ conflict

Current transaction is aborted (PostgreSQL) Earlier error in the same transaction ROLLBACK (or use savepoints) Safe update mode (MySQL Workbench) UPDATE/DELETE without key in WHERE Add a key-based WHERE 45.13 Systematic debugging method

Debugging silent logic errors — row-count checks at each join:

If a count changes unexpectedly after a join, you have found the fan-out (or a missing match for INNER JOINs).

SELECT first_name, last_name
FROM employees
WHERE dept_id = 1
ORDER salary DESC;
-- PostgreSQL: ERROR: syntax error at or near "salary"

SELECT emp_name FROM employees;
-- PostgreSQL: ERROR: column "emp_name" does not exist
-- MySQL:      ERROR 1054 (42S22): Unknown column 'emp_name' in 'field list'

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'employees';
-- MySQL: DESCRIBE employees;  SQL Server: EXEC sp_help 'employees';  psql: \d employees

SELECT * FROM employee;
-- PostgreSQL: ERROR: relation "employee" does not exist
-- MySQL: ERROR 1146 (42S02): Table 'shop.employee' doesn't exist
-- SQL Server: Invalid object name 'employee'.  Oracle: ORA-00942: table or view does not exist

INSERT INTO customers (customer_id, name, city, country) VALUES (1, 'Test', 'Pune', 'India');
-- PostgreSQL: ERROR: duplicate key value violates unique constraint "customers_pkey"  DETAIL: Key (customer_id)=(1) already exists.
-- MySQL: ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
-- SQL Server: Msg 2627 Violation of PRIMARY KEY constraint

INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
VALUES (2002, 99, DATE '2026-04-01', 'PENDING', 100);
-- ERROR: insert or update on table "orders" violates foreign key constraint ... Key (customer_id)=(99) is not present in table "customers".

DELETE FROM customers WHERE customer_id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint ... on table "orders"

SELECT o.* FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL;

INSERT INTO products (product_id, product_name, price) VALUES (120, 'Cable', 300);
-- ERROR: null value in column "category" of relation "products" violates not-null constraint

SELECT * FROM orders WHERE order_date = '05/01/2026';        -- ambiguous format; may error or mean May 1 vs Jan 5
SELECT * FROM products WHERE price = 'cheap';                 -- invalid input syntax for type numeric
INSERT INTO employees (..., salary, ...) VALUES (..., '95,000', ...);   -- comma not valid in numeric
SELECT 'Total: ' + 5;                                          -- SQL Server: conversion error on 'Total: '

SELECT dept_id, dept_name, first_name
FROM employees e JOIN departments d ON e.dept_id = d.dept_id;
-- ERROR: column reference "dept_id" is ambiguous

Expected Result

After fixing the ambiguous-column query:

SELECT e.dept_id, d.dept_name, e.first_name
FROM employees e JOIN departments d ON e.dept_id = d.dept_id
WHERE e.dept_id = 3;

Common Mistakes

Reading only the first line of an error.

Changing several things at once while debugging.

Assuming "no error" means "correct result".

Testing only on happy-path data without NULLs and edge cases.

Best Practices

Build queries incrementally and check row counts.

Keep tiny, hand-verified test datasets.

Log SQLSTATE codes in applications and handle retryable ones (40001, deadlocks).

Use a linter/formatter (sqlfluff, SQL Prompt) and code review.

Interview Relevance

Medium-high: "How do you debug a query returning wrong results?", "What does 'must appear in GROUP BY' mean?", "How do you find what is blocking a query?".

Practice Questions

Fix: SELECT name, COUNT(*) FROM customers c JOIN orders o USING (customer_id);

Explain three possible causes of "table does not exist" when the table clearly exists.

A report's revenue doubled after adding a join to order_items. Explain and fix.

What does PostgreSQL's "current transaction is aborted" mean and how do you recover?

Write a query to find orphan order_items rows.