Skip to content
v0.1.0latest release21 skills2 categories3 sub-categories22 tested scripts68 reference docsContact us
Vector Agentic Skills
Submit a skill

SQL Debugger Examples

Reference for sql-debugger · 573 lines

Programming / SQLv1.0.0Updated OCT 20 20261 reference1 script

Real-world debugging scenarios with step-by-step solutions.

Scenario 1: Query Returns No Results

Problem

sql
SELECT c.name, o.order_date, o.total
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'active'
  AND o.order_date = '2024-01-15';

User says: "I know there are orders on this date, but the query returns nothing."

Debugging Steps

Step 1: Check if customers exist

sql
SELECT COUNT(*) FROM customers WHERE status = 'active';

Step 2: Check if orders exist for that date

sql
SELECT COUNT(*) FROM orders WHERE order_date = '2024-01-15';

Step 3: Check data type of order_date

sql
SELECT order_date, pg_typeof(order_date) FROM orders LIMIT 1;

Step 4: Check actual values

sql
SELECT DISTINCT order_date::date FROM orders 
WHERE order_date >= '2024-01-15' AND order_date < '2024-01-16';

Root Cause

order_date is a TIMESTAMP, not DATE. Comparing '2024-01-15' only matches exactly midnight.

Solution

sql
SELECT c.name, o.order_date, o.total
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'active'
  AND o.order_date >= '2024-01-15'
  AND o.order_date < '2024-01-16';

Or:

sql
  AND DATE(o.order_date) = '2024-01-15';

Scenario 2: Aggregate Values Too High

Problem

sql
SELECT 
    c.id,
    c.name,
    COUNT(o.id) AS order_count,
    SUM(oi.quantity) AS total_items
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name;

User says: "The total_items count is way higher than it should be."

Debugging Steps

Step 1: Check a single customer manually

sql
SELECT c.id, c.name, o.id AS order_id, oi.id AS item_id, oi.quantity
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE c.id = 123;

Step 2: Count distinct values

sql
SELECT 
    c.id,
    COUNT(o.id) AS counted_orders,
    COUNT(DISTINCT o.id) AS distinct_orders
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE c.id = 123
GROUP BY c.id;

Root Cause

Each order with multiple items creates multiple rows, inflating the order count.

Solution

sql
SELECT 
    c.id,
    c.name,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity) AS total_items
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name;

Or use subqueries:

sql
SELECT 
    c.id,
    c.name,
    (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count,
    (SELECT SUM(oi.quantity) 
     FROM orders o 
     JOIN order_items oi ON o.id = oi.order_id 
     WHERE o.customer_id = c.id) AS total_items
FROM customers c;

Scenario 3: NULL Values Causing Issues

Problem

sql
SELECT 
    employee_id,
    base_salary + bonus + commission AS total_compensation
FROM employees;

User says: "Some employees show NULL for total_compensation even though they have a salary."

Debugging Steps

Step 1: Check for NULLs

sql
SELECT 
    employee_id,
    base_salary,
    bonus,
    commission,
    base_salary + bonus + commission AS total
FROM employees
WHERE base_salary + bonus + commission IS NULL
LIMIT 10;

Root Cause

Any arithmetic with NULL produces NULL. Some employees have NULL bonus or commission.

Solution

sql
SELECT 
    employee_id,
    COALESCE(base_salary, 0) + COALESCE(bonus, 0) + COALESCE(commission, 0) AS total_compensation
FROM employees;

Scenario 4: Division by Zero

Problem

sql
SELECT 
    product_id,
    total_sales / total_orders AS avg_sale
FROM product_stats;

Error: division by zero

Debugging Steps

Step 1: Find rows with zero orders

sql
SELECT * FROM product_stats WHERE total_orders = 0;

Solution

sql
SELECT 
    product_id,
    CASE 
        WHEN total_orders = 0 THEN 0
        ELSE total_sales / total_orders 
    END AS avg_sale
FROM product_stats;

Or:

sql
SELECT 
    product_id,
    total_sales / NULLIF(total_orders, 0) AS avg_sale
FROM product_stats;

Scenario 5: Missing Rows After LEFT JOIN

Problem

sql
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.name;

User says: "Customers with no orders are missing from results."

Debugging Steps

Step 1: Remove the WHERE and check

sql
SELECT c.name, o.id, o.status
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.id IN (SELECT id FROM customers WHERE id NOT IN (SELECT customer_id FROM orders))
LIMIT 10;

Root Cause

WHERE clause filters out NULL rows from LEFT JOIN. Customers without orders have o.status = NULL, which fails the = 'completed' check.

Solution

Move filter to JOIN condition:

sql
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed'
GROUP BY c.name;

Scenario 6: Duplicate Results

Problem

sql
SELECT u.id, u.email, r.name AS role_name
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.id = 123;

User says: "This returns 3 rows for one user. I expected 1 row."

Debugging Steps

Step 1: Check user_roles

sql
SELECT * FROM user_roles WHERE user_id = 123;

Root Cause

User has multiple roles assigned. The query correctly returns one row per role.

Solution (depends on requirement)

If you want one row with all roles:

sql
SELECT u.id, u.email, STRING_AGG(r.name, ', ') AS roles
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.id = 123
GROUP BY u.id, u.email;

If you want just the primary role:

sql
SELECT u.id, u.email, r.name AS role_name
FROM users u
JOIN user_roles ur ON u.id = ur.user_id AND ur.is_primary = true
JOIN roles r ON ur.role_id = r.id
WHERE u.id = 123;

Scenario 7: Incorrect String Matching

Problem

sql
SELECT * FROM products WHERE name = 'Widget';

User says: "Returns nothing, but I can see 'Widget' in the table."

Debugging Steps

Step 1: Check for whitespace or encoding

sql
SELECT 
    name,
    LENGTH(name) AS len,
    name = 'Widget' AS matches
FROM products
WHERE name LIKE '%Widget%';

Step 2: Check for invisible characters

sql
SELECT 
    name,
    encode(name::bytea, 'hex') AS hex_value
FROM products
WHERE name LIKE '%Widget%';

Root Cause

Leading/trailing whitespace or non-printable characters.

Solution

sql
SELECT * FROM products WHERE TRIM(name) = 'Widget';

Or fix the data:

sql
UPDATE products SET name = TRIM(name);

Scenario 8: Subquery Returns Multiple Rows

Problem

sql
SELECT 
    p.name,
    (SELECT c.name FROM categories c WHERE c.id = p.category_id) AS category_name
FROM products p;

Error: more than one row returned by a subquery used as an expression

Debugging Steps

Step 1: Find products with multiple category matches

sql
SELECT p.id, p.category_id, COUNT(*)
FROM products p
JOIN categories c ON c.id = p.category_id
GROUP BY p.id, p.category_id
HAVING COUNT(*) > 1;

Step 2: Check for duplicate category IDs

sql
SELECT id, COUNT(*) FROM categories GROUP BY id HAVING COUNT(*) > 1;

Root Cause

Duplicate rows in categories table (data integrity issue) or wrong comparison logic.

Solution

If duplicates are valid, limit to one:

sql
SELECT 
    p.name,
    (SELECT c.name FROM categories c WHERE c.id = p.category_id LIMIT 1) AS category_name
FROM products p;

Better - use JOIN:

sql
SELECT p.name, c.name AS category_name
FROM products p
LEFT JOIN categories c ON c.id = p.category_id;

Scenario 9: GROUP BY Error

Problem

sql
SELECT customer_id, name, SUM(total) AS total_spent
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY customer_id;

Error: column "c.name" must appear in GROUP BY clause

Root Cause

name is not in GROUP BY but also not aggregated.

Solution

sql
SELECT o.customer_id, c.name, SUM(o.total) AS total_spent
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY o.customer_id, c.name;

Or if customer_id determines name (functional dependency):

sql
SELECT o.customer_id, MAX(c.name) AS name, SUM(o.total) AS total_spent
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY o.customer_id;

Scenario 10: Date Comparison Issues

Problem

sql
SELECT * FROM events
WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31';

User says: "Missing events from January 31st."

Debugging Steps

Step 1: Check data type and values

sql
SELECT event_date, pg_typeof(event_date)
FROM events
WHERE event_date >= '2024-01-31'
ORDER BY event_date
LIMIT 5;

Root Cause

event_date is TIMESTAMP. '2024-01-31' is interpreted as '2024-01-31 00:00:00', excluding later times on Jan 31.

Solution

sql
SELECT * FROM events
WHERE event_date >= '2024-01-01'
  AND event_date < '2024-02-01';

Or be explicit:

sql
SELECT * FROM events
WHERE event_date BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';

Scenario 11: Case Sensitivity Issues

Problem

sql
SELECT * FROM users WHERE email = 'John@Example.com';

User says: "User exists but query returns nothing."

Debugging Steps

sql
SELECT email FROM users WHERE LOWER(email) = LOWER('John@Example.com');

Root Cause

PostgreSQL string comparison is case-sensitive by default.

Solution

sql
SELECT * FROM users WHERE LOWER(email) = LOWER('John@Example.com');

Or use case-insensitive comparison:

sql
SELECT * FROM users WHERE email ILIKE 'John@Example.com';

Or create a functional index:

sql
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Scenario 12: Circular Reference in CTE

Problem

sql
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.parent_id, c.name, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

Query runs forever or hits recursion limit.

Debugging Steps

Step 1: Check for circular references

sql
WITH RECURSIVE check_circular AS (
    SELECT id, parent_id, ARRAY[id] AS path, false AS is_cycle
    FROM categories
    
    UNION ALL
    
    SELECT c.id, c.parent_id, cc.path || c.id, c.id = ANY(cc.path)
    FROM categories c
    JOIN check_circular cc ON c.parent_id = cc.id
    WHERE NOT cc.is_cycle
)
SELECT * FROM check_circular WHERE is_cycle;

Root Cause

A category has itself as an ancestor (circular reference in data).

Solution

Add cycle detection:

sql
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 0 AS depth, ARRAY[id] AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || c.id
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
    WHERE NOT (c.id = ANY(t.path))
)
SELECT * FROM tree;

Or fix the data:

sql
UPDATE categories SET parent_id = NULL WHERE id = [circular_id];
More in SQL

sql-analytics

Analytical and aggregation SQL — GROUP BY, ROLLUP, CUBE, GROUPING SETS, pivots, cohorts, funnels, retention, percentiles and statistical functions.

sql-data-quality

Profile and validate data with SQL — NULL analysis, duplicate detection, referential integrity, range and format checks, outliers, constraints and cleansing queries.

sql-dialect-translator

Translate SQL between dialects — PostgreSQL, MySQL, SQL Server (T-SQL), Oracle, SQLite — covering data types, functions, pagination, date handling, upserts and identity columns.

sql-optimizer

Rewrite slow SQL queries for performance — read execution plans (EXPLAIN / EXPLAIN ANALYZE), remove anti-patterns, make predicates sargable, choose JOIN/EXISTS strategies and suggest indexes.

sql-performance-tuning

Database-level SQL performance tuning — index design and maintenance, query profiling, statistics, partitioning, schema design, locking and configuration bottlenecks.

sql-query-explainer

Explain what an existing SQL query does in plain language — logical execution order, step-by-step breakdown of joins, filters, grouping and window logic, and the shape of the result.