sql-debugging-and-repair

# 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`