Advanced SQL Reasoning and Debugging

Expert SQL work is disciplined reasoning, not memorizing more syntax. Start from the required output grain and invariants, construct a minimal adversarial dataset, verify each relational step, compare expected with actual results, and only then inspect execution plans. Debug correctness before performance because a fast wrong query is a more efficient defect.

What will you be able to do?

• Turn a vague question into grain, inputs, invariants, and edge cases.
• Reduce a failing query to the smallest counterexample.
• Debug joins, nulls, time boundaries, grouping, and concurrency assumptions.
• Separate semantic diagnosis from performance diagnosis.

Prerequisites and mental model

Use the GRAIN checklist:

• Grain: what does one output row mean?
• Rules: which invariants must always hold?
• Adversarial rows: duplicates, nulls, ties, boundaries, wrong tenants.
• Intermediates: what should each step return?
• Negative proof: what must never appear?

How do you debug a wrong report?

Suppose “open ticket count” is too high:

1. Restate: one row per organization, count tickets whose current status is open.
2. Remove extra output columns and joins.
3. Compare ticket IDs before and after each join.
4. Group by ticket ID and find multiplicity:

5. Replace a detail join with EXISTS or pre-aggregate.
6. Reconcile final IDs with a trusted simple query using EXCEPT both ways.

How do you create adversarial fixtures?

Include:

• two tenants with overlapping local-looking data;
• a parent with zero, one, and multiple children;
• equal timestamps and values;
• null optional fields;
• boundary times exactly at interval start/end;
• duplicate-looking labels with distinct IDs;
• stale and current versions.

A fixture that contains only the happy path cannot disprove a flawed query.

How do you debug errors?

Read the complete PostgreSQL message: SQLSTATE, primary message, detail, hint, constraint, and position. Preserve the first error; cascaded “transaction is aborted” messages are symptoms. Use \errverbose in psql after an error when helpful.

For performance, capture a safe plan after correctness:

ANALYZE executes the query. Do not use it casually on writes; use plain EXPLAIN or an isolated rollbackable test with full awareness of side effects.

Why does this matter in AI-native systems?

Models can generate syntactically polished SQL that answers a subtly different question. Require the model to declare grain, assumptions, sensitive fields, and test fixtures before SQL. Retrieval and evaluation queries need negative proofs: no cross-tenant IDs, no stale document versions, no missing failed attempts, and no duplicated candidates.

Prove it worked

Take one earlier query and deliberately introduce three defects: remove a tenant join condition, remove an order tie-breaker, and replace IS NULL with = NULL. Create a fixture that exposes each defect, then show the repaired query passes both positive and negative assertions.

Production judgment

• Preserve representative distributions; tiny fixtures prove semantics, not performance.
• Sanitize production samples and respect retention/access controls.
• Record query text fingerprint, parameters shape, schema version, plan, and server settings for reproducible incidents.
• Do not “fix” planner estimates by disabling plan types globally.
• Make correctness checks executable in tests and monitoring where possible.

Common mistakes

• Random query edits: no falsifiable hypothesis → change one cause and predict the result.
• DISTINCT makes test green: multiplication hidden → locate cardinality defect.
• EXPLAIN first: semantics never defined → prove expected IDs/results first.
• AI answer accepted because it parses: no adversarial test → require a counterexample fixture.

AI Pair-Programmer Prompt

Hands-on exercise

Write a query returning tickets with no agent-authored message. Produce two correct versions (NOT EXISTS and left anti-join), then build a null/adversarial fixture and prove the results match using EXCEPT in both directions.

Acceptance criterion: both difference queries return zero rows, and cross-tenant message IDs cannot change the result.

Knowledge check

1. What should be defined before a complex query?
2. Why use EXCEPT both ways?
3. What does EXPLAIN ANALYZE do that plain EXPLAIN does not?
4. What is a negative proof in tenant reporting?

---

Answers

1. Output grain, invariants, inputs, scope, and edge cases.
2. To prove neither result contains rows absent from the other.
3. It actually executes and measures the statement.
4. Evidence that no row belonging to another tenant can appear or influence the result.

Completion checklist

• I use GRAIN before debugging.
• My fixtures contain adversarial rows.
• Correctness is proven before tuning.
• Repaired behavior is captured as regression tests.

Primary references

• PostgreSQL error fields
• psql error verbosity
• Using EXPLAIN
• Set operations