sql-debugging-and-repair

name

sql debugging and repair

description

Trigger this skill when the user provides broken or logically incorrect SQL requiring clause-level diagnosis and repair. It is essential when a query results in a syntax crash, row inflation (unwanted duplicates), or semantic nulls (correct syntax but no data returned). Layman triggers include requests like 'fix this query', 'why is my code returning nothing?', 'this script has an error on line 5', or 'adjust the WHERE clause to match the question better'. Use this to target specific parts of a query like the SELECT, WHERE, or JOIN clauses for fine-grained localized correction.

Skill: sql debugging and repair

1. Capability Definition & Real Case

• Professional Definition: The ability to perform clause-wise diagnosis and iterative refinement of SQL queries by localizing errors to specific components (SELECT, WHERE, JOIN, GROUP BY, etc.). This capability involves identifying syntax terminal failures, structural visibility issues (e.g., columns missing from CTE projections), and semantic inaccuracies (e.g., incorrect filters or aggregation levels), while simultaneously classifying whether a question is 'unanswerable' due to missing schema metadata or 'ambiguous' due to vague natural language terminology.
• Dimension Hierarchy: Robustness and Adaptation->Corrective Reasoning->sql debugging and repair

Real Case

[Case 1]

• Initial Environment: A product database contains a 'products' table with columns 'product_id', 'product_name', 'category', and 'price'. A user attempts to retrieve specific product names but provides a query that selects all columns.
• Real Question: List the product name whose product category is 'smartphone'.
• Real Trajectory: Analyze the predicted SQL: SELECT * FROM products WHERE category = 'smartphone'. Identify that the user intent explicitly asks for 'product name' (a specific attribute) while the SQL uses '*' (all attributes). Localize the error to the SELECT clause. Generate a corrected SELECT clause that projects only the 'product_name' column to align with the semantic constraint of the question.
• Real Answer: SELECT product_name FROM products WHERE category = 'smartphone';
• Why this demonstrates the capability: This demonstrates clause-wise semantic debugging. The agent identifies that while the WHERE clause and syntax are correct, the SELECT clause diverges from the specific intent of the natural language question. By precisely localizing the error to a single component (SELECT), the agent avoids unnecessary full-query regeneration and maintains structural stability.

---

[Case 2]

• Initial Environment: A complex database for an e-commerce platform uses Common Table Expressions (CTEs) to aggregate sales data. The developer's script fails due to a reference error in a downstream join.
• Real Question: Fix the error in this script: 'ERROR: column t1.customer_id does not exist' in the final join.
• Real Trajectory: Perform a structural audit of the preceding CTEs. Observe that 'customer_id' is present in the base table but was omitted from the projection list of the intermediate CTE named 't1'. Identify this as a visibility issue where a valid identifier was 'lost' during a sub-query projection. Add 'customer_id' to the SELECT list of the CTE 't1' to restore downstream visibility for the JOIN operation.
• Real Answer: In the CTE 't1', change the SELECT list from 'SELECT order_id, total' to 'SELECT customer_id, order_id, total'.
• Why this demonstrates the capability: This illustrates structural visibility repair in nested query environments. The agent traces the identifier provenance through multiple layers of aggregation to find the exact point of failure. It demonstrates the ability to perform minimal, surgical edits to a complex script rather than rewriting the entire logic from scratch.

---

[Case 3]

• Initial Environment: A clinical records database contains patient IDs and admission logs. The user asks for a count of people, but the schema allows for multiple admissions per single person.
• Real Question: How many people were admitted to the hospital last year?
• Real Trajectory: Inspect the candidate SQL: SELECT count(patient_id) FROM admissions WHERE admission_date >= '2023-01-01'. Realize that count(patient_id) will produce a 'row inflation' error because it counts every record, while the intent 'How many people' refers to unique individuals. Localize the semantic flaw to the aggregation logic within the SELECT clause. Inject the DISTINCT keyword to ensure unique cardinality in the final result.
• Real Answer: SELECT count(DISTINCT patient_id) FROM admissions WHERE admission_date >= '2023-01-01';
• Why this demonstrates the capability: This case demonstrates semantic debugging of row cardinality and aggregation levels. The agent correctly distinguishes between a syntactically valid query and a logically flawed one that would result in statistically inaccurate 'inflated' numbers. It proves the model can catch subtle grouping errors that do not cause execution crashes but produce wrong answers.

Pipeline Execution Instructions

To synthesize data for this capability, you must strictly follow a 3-phase pipeline. Do not hallucinate steps. Read the corresponding reference file for each phase sequentially:

1. Phase 1: Environment Exploration Read the exploration guidelines to discover raw knowledge seeds: references/EXPLORATION.md

2. Phase 2: Trajectory Selection Once Phase 1 is complete, read the selection criteria to evaluate the trajectory: references/SELECTION.md

3. Phase 3: Data Synthesis Once a trajectory passes Phase 2, read the synthesis instructions to generate the final data: references/SYNTHESIS.md