Apppublic

barathSV/sqldebug-env

sourceHugging Faceupdated 6mo agoView on Hugging Face

0likes

App README

🛠️ SQL Query Debugger — OpenEnv Environment

An agentic environment where AI models learn to diagnose and fix broken SQL queries through iterative feedback.

![OpenEnv](https://github.com/meta-pytorch/OpenEnv) ![HF Space](https://huggingface.co/spaces/barathSV/sqldebug-env) ![Docker](./Dockerfile)

🎯 Why SQL Debugging?

SQL query repair is a high-value, high-frequency real-world task:

—Data analysts and engineers debug queries every day in production systems
—It has clear, deterministic success/failure criteria (output matches or it doesn't)
—Partial progress is meaningful — a query that runs but returns wrong columns is closer to correct than one that errors
—Bug taxonomy maps naturally to difficulty levels: wrong joins → aggregation bugs → window functions
—Directly evaluates an agent's ability to reason about schemas, relational algebra, and query semantics

This environment is novel in the OpenEnv ecosystem and immediately useful for evaluating and training agents that assist data teams.

🌍 Environment Overview

The agent receives a broken SQL query along with:

—The full database schema (CREATE TABLE DDL)
—Expected output column names and row count
—A sample of the first 3 expected rows (on easy/medium tasks)
—Its last query's execution result and any SQL errors
—A human-readable hint after 3 failed attempts

The agent submits corrected SELECT queries. Each is executed against an in-memory SQLite database pre-seeded with realistic data. Reward is shaped to give partial credit for incremental progress toward the correct answer.

📐 Action Space

json

{
  "query": "SELECT ... (your corrected SQL)",
  "explanation": "optional string describing what you fixed"
}

—Must be a SELECT statement
—Write operations (DROP, DELETE, INSERT, etc.) return 0.0 reward with a penalty flag
—No length limit; must be valid SQLite syntax

👁️ Observation Space

json

{
  "task_id": "easy_wrong_join",
  "schema_ddl": "CREATE TABLE employees ...",
  "broken_query": "SELECT e.name, d.name FROM employees e, departments d ...",
  "expected_columns": ["name", "department"],
  "expected_row_count": 3,
  "expected_sample": [{"name": "Alice", "department": "Engineering"}, ...],
  "last_execution_result": {
    "columns": ["name", "department"],
    "rows": [["Alice", "Engineering"]],
    "total_rows": 3
  },
  "last_error": null,
  "step_number": 1,
  "max_steps": 10,
  "hint": null
}

🏆 Reward Function

Reward is shaped — partial credit accumulates across four components:

Component	Weight	Condition
`executes`	0.1	Query runs without SQL error
`columns_match`	0.2	Output column names match expected exactly
`row_count_match`	0.2	Number of rows matches expected
`data_match`	0.5	Full result set matches (order-independent)

Total range: [0.0, 1.0]

Example reward trajectory

Step 1 — Cartesian product bug:    reward = 0.30  (runs + right columns, wrong rows)
Step 2 — Wrong column alias:       reward = 0.80  (runs + right rows, wrong col name)
Step 3 — Correct fix:              reward = 1.00  ✓ done

📋 Tasks

Task 1 — `easy_wrong_join` 🟢 Easy

Bug: Missing JOIN condition causes a Cartesian product.

sql

-- ❌ Broken
SELECT e.name, d.name AS department
FROM employees e, departments d
WHERE e.salary > 70000;

-- ✅ Fixed
SELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 70000;

Schema: employees, departments (6 employees, 3 departments) Expected: 3 rows — high earners with their department name Broken query reward: 0.30 | Correct reward: 1.00

Task 2 — `medium_aggregation_bug` 🟡 Medium

Bugs (multiple):

1.Missing GROUP BY clause — SUM() has nothing to aggregate over
2.HAVING applied before any grouping exists
3.COUNT(o.id) missing an alias, causing column name mismatch

sql

-- ❌ Broken (3 bugs)
SELECT c.name, c.country, SUM(o.quantity * o.unit_price) AS revenue, COUNT(o.id)
FROM orders o JOIN customers c ON o.customer_id = c.id
HAVING revenue > 500
ORDER BY revenue DESC;

-- ✅ Fixed
SELECT c.name, c.country, SUM(o.quantity * o.unit_price) AS revenue, COUNT(o.id) AS order_count
FROM orders o JOIN customers c ON o.customer_id = c.id
GROUP BY c.id, c.name, c.country
HAVING revenue > 500
ORDER BY revenue DESC;

Schema: orders, customers, products (12 orders, 5 customers) Expected: 4 high-revenue customers Broken query reward: 0.10 | Correct reward: 1.00

Task 3 — `hard_window_cte` 🔴 Hard

Bugs: PARTITION BY and ORDER BY are swapped inside a window function CTE, causing every partition to have exactly 1 member, making the outer WHERE dept_rank > 1 return zero rows.

sql

-- ❌ Broken (swapped PARTITION/ORDER)
WITH ranked AS (
    SELECT e.name, e.department, e.salary, pr.score,
           ROW_NUMBER() OVER (
               PARTITION BY pr.score      -- ❌ should be e.department
               ORDER BY e.department      -- ❌ should be pr.score DESC
           ) AS dept_rank
    FROM employees e
    JOIN performance_reviews pr ON e.id = pr.employee_id
    WHERE pr.review_year = 2023
)
SELECT name, department, salary, score, dept_rank
FROM ranked WHERE dept_rank > 1 ORDER BY department, dept_rank;

-- ✅ Fixed
WITH ranked AS (
    SELECT e.name, e.department, e.salary, pr.score,
           ROW_NUMBER() OVER (
               PARTITION BY e.department
               ORDER BY pr.score DESC
           ) AS dept_rank
    ...

Schema: employees, performance_reviews (10 employees, 3 departments) Expected: 7 rows — non-top performers per department Broken query reward: 0.30 | Correct reward: 1.00

📊 Baseline Scores

Evaluated with Qwen/Qwen2.5-72B-Instruct via HuggingFace router (10 steps max):

Task	Difficulty	Score	Steps to Solve
`easy_wrong_join`	🟢 Easy	1.00	1
`medium_aggregation_bug`	🟡 Medium	1.00	2
`hard_window_cte`	🔴 Hard	0.80	4
Average		0.93

🚀 Setup & Usage

Run locally

bash

cd server
pip install -r requirements.txt
python app.py
# API available at http://localhost:7860

Run with Docker

bash

docker build -t sqldebug-env .
docker run -p 7860:7860 sqldebug-env

API Reference

bash

# List available tasks
curl http://localhost:7860/tasks

# Start an episode
curl -X POST http://localhost:7860/reset \
  -H "Content-Type: application/json" \
  -d '{"task_id": "easy_wrong_join"}'

# Submit a query
curl -X POST http://localhost:7860/step \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT e.name, d.name AS department FROM employees e JOIN departments d ON e.department_id = d.id WHERE e.salary > 70000"}'

# Check current state
curl http://localhost:7860/state

# Interactive API docs
open http://localhost:7860/docs

Run inference

bash

export API_KEY=your_key
export API_BASE_URL=https://router.huggingface.co/v1
export MODEL_NAME=Qwen/Qwen2.5-72B-Instruct
export SQLDEBUG_ENV_URL=http://localhost:7860

python inference.py

📁 Project Structure

sqldebug-env/
├── Dockerfile              # Docker build (python:3.11-slim, non-root)
├── openenv.yaml            # OpenEnv metadata spec
├── pyproject.toml          # Python package config with [project.scripts]
├── uv.lock                 # Locked dependencies
├── inference.py            # Baseline inference script
├── local_test.py           # 40-test validation suite
├── README.md               # This file
└── server/
    ├── app.py              # FastAPI server (reset/step/state/health)
    ├── environment.py      # Core env: models, grader, task definitions
    └── requirements.txt    # Server dependencies

✅ Validation

Run the local test suite (40 tests) before submitting:

bash

python local_test.py
# Results: 40/40 passed ✓

Built for the Meta PyTorch Hackathon x Scaler School of Technology — Round 1

🛠️ SQL Query Debugger — OpenEnv Environment

🎯 Why SQL Debugging?

🌍 Environment Overview

📐 Action Space

👁️ Observation Space

🏆 Reward Function

Example reward trajectory

📋 Tasks

Task 1 — easy_wrong_join 🟢 Easy

Task 2 — medium_aggregation_bug 🟡 Medium

Task 3 — hard_window_cte 🔴 Hard

📊 Baseline Scores

🚀 Setup & Usage

Run locally

Run with Docker

API Reference

Run inference

📁 Project Structure

✅ Validation

Task 1 — `easy_wrong_join` 🟢 Easy

Task 2 — `medium_aggregation_bug` 🟡 Medium

Task 3 — `hard_window_cte` 🔴 Hard