Vaishnavi-279/sql-debugger-env

1"""2SQL Debugger — Task definitions (easy / medium / hard).3 4Each task has a broken query the agent must fix.5Grading is fully deterministic: compare actual vs expected rows.6"""7 8from dataclasses import dataclass9from typing import Any, Dict, List, Optional10 11 12@dataclass13class Task:14    id: str15    difficulty: str16    description: str17    schema_ddl: str18    broken_query: str19    expected_result: List[Dict[str, Any]]20    hint: Optional[str] = None21    bug_type: str = ""22 23 24# ── EASY: three keyword typos ─────────────────────────────────────────────────25TASK_EASY = Task(26    id="task_easy_syntax",27    difficulty="easy",28    description=(29        "Return the name and salary of every employee in the 'Engineering' department, "30        "ordered by salary descending. Fix the query — it has three keyword typos."31    ),32    schema_ddl="""33CREATE TABLE employees (34    id      INTEGER PRIMARY KEY,35    name    TEXT    NOT NULL,36    dept    TEXT    NOT NULL,37    salary  REAL    NOT NULL38);39INSERT INTO employees VALUES (1, 'Alice',   'Engineering', 95000);40INSERT INTO employees VALUES (2, 'Bob',     'Engineering', 88000);41INSERT INTO employees VALUES (3, 'Carol',   'Marketing',   72000);42INSERT INTO employees VALUES (4, 'Dave',    'Engineering', 102000);43INSERT INTO employees VALUES (5, 'Eve',     'HR',          65000);44""",45    broken_query="SELEC name, salary FORM employees WHERE dept = 'Engineering' ORDER BY salary DESK;",46    expected_result=[47        {"name": "Dave",  "salary": 102000.0},48        {"name": "Alice", "salary": 95000.0},49        {"name": "Bob",   "salary": 88000.0},50    ],51    hint="Three SQL keywords are misspelled: SELEC, FORM, DESK.",52    bug_type="syntax_typo",53)54 55# ── MEDIUM: wrong JOIN column + wrong aggregation ─────────────────────────────56TASK_MEDIUM = Task(57    id="task_medium_join",58    difficulty="medium",59    description=(60        "Return each customer's name and the total value of their orders "61        "(quantity × unit_price), ordered by total_value descending. "62        "The query has a wrong JOIN condition and a wrong aggregation expression."63    ),64    schema_ddl="""65CREATE TABLE customers (66    id    INTEGER PRIMARY KEY,67    name  TEXT    NOT NULL68);69CREATE TABLE orders (70    id           INTEGER PRIMARY KEY,71    customer_id  INTEGER NOT NULL,72    quantity     INTEGER NOT NULL,73    unit_price   REAL    NOT NULL74);75INSERT INTO customers VALUES (1, 'Acme Corp');76INSERT INTO customers VALUES (2, 'Globex');77INSERT INTO customers VALUES (3, 'Initech');78INSERT INTO orders VALUES (1, 1, 3, 250.0);79INSERT INTO orders VALUES (2, 1, 1, 500.0);80INSERT INTO orders VALUES (3, 2, 5, 100.0);81INSERT INTO orders VALUES (4, 3, 2, 750.0);82""",83    broken_query=(84        "SELECT c.name, SUM(o.quantity) AS total_value "85        "FROM customers c "86        "JOIN orders o ON c.id = o.id "87        "GROUP BY c.name "88        "ORDER BY total_value DESC;"89    ),90    expected_result=[91        {"name": "Initech",   "total_value": 1500.0},92        {"name": "Acme Corp", "total_value": 1250.0},93        {"name": "Globex",    "total_value": 500.0},94    ],95    hint="Fix the JOIN column (o.id → o.customer_id) and the aggregation (SUM quantity → SUM quantity*unit_price).",96    bug_type="wrong_join_wrong_aggregation",97)98 99# ── HARD: NULL handling + HAVING + ORDER BY direction ────────────────────────100TASK_HARD = Task(101    id="task_hard_complex",102    difficulty="hard",103    description=(104        "Return the name and average review score for every product whose average score "105        "is strictly above the overall average score across all products. "106        "NULL scores must be ignored. Results ordered by avg_score descending. "107        "There are three simultaneous bugs — find and fix all of them."108    ),109    schema_ddl="""110CREATE TABLE products (111    id    INTEGER PRIMARY KEY,112    name  TEXT    NOT NULL113);114CREATE TABLE reviews (115    id         INTEGER PRIMARY KEY,116    product_id INTEGER NOT NULL,117    score      REAL             -- nullable118);119INSERT INTO products VALUES (1, 'Widget A');120INSERT INTO products VALUES (2, 'Widget B');121INSERT INTO products VALUES (3, 'Widget C');122INSERT INTO products VALUES (4, 'Widget D');123INSERT INTO reviews VALUES (1,  1, 4.5);124INSERT INTO reviews VALUES (2,  1, 5.0);125INSERT INTO reviews VALUES (3,  2, 3.0);126INSERT INTO reviews VALUES (4,  2, 3.5);127INSERT INTO reviews VALUES (5,  3, 4.0);128INSERT INTO reviews VALUES (6,  3, NULL);129INSERT INTO reviews VALUES (7,  4, 2.0);130INSERT INTO reviews VALUES (8,  4, 2.5);131""",132    broken_query=(133        "SELECT p.name, AVG(r.score) AS avg_score "134        "FROM products p "135        "LEFT JOIN reviews r ON p.id = r.product_id "136        "GROUP BY p.name "137        "HAVING AVG(r.score) > (SELECT AVG(score) FROM reviews) "138        "ORDER BY avg_score ASC;"139    ),140    expected_result=[141        {"name": "Widget A", "avg_score": 4.75},142        {"name": "Widget C", "avg_score": 4.0},143    ],144    hint=(145        "Three bugs: (1) subquery must exclude NULLs with WHERE score IS NOT NULL, "146        "(2) use INNER JOIN instead of LEFT JOIN so NULL scores don't skew group averages, "147        "(3) ORDER BY should be DESC not ASC."148    ),149    bug_type="null_join_order",150)151 152ALL_TASKS: Dict[str, Task] = {153    TASK_EASY.id:   TASK_EASY,154    TASK_MEDIUM.id: TASK_MEDIUM,155    TASK_HARD.id:   TASK_HARD,156}157 158# For convenient iteration in difficulty order159TASK_ORDER = [TASK_EASY.id, TASK_MEDIUM.id, TASK_HARD.id]160