Mahathi4554/sql-query-debugging

Check out the configuration reference at https://huggingface.co/docs/hub/spaces-config-reference

SQL Query Debugging — OpenEnv Environment

A real-world OpenEnv environment where AI agents learn to debug and optimize SQL queries. Agents interact through a standard step() / reset() / state() API, receiving partial reward signals at every step.

Motivation

SQL debugging is a daily task for data engineers, analysts, and backend developers. Unlike toy environments, this environment:

• —Uses a real SQLite database engine — errors are genuine, results are verifiable
• —Graders are fully deterministic — run the query, check the output
• —Reward is partial at every step — agents get signal even for partially correct attempts
• —Tasks range from trivially broken to genuinely challenging optimization problems

This environment fills a gap in the OpenEnv ecosystem: a real code-execution environment that isn't just another HumanEval variant.

Environment Overview

Action:       { "sql_query": "<SQL string>" }
Observation:  schema, broken query, error message, result preview, hint
Reward:       0.0 → 1.0  (partial progress at every step)
Done:         True when correct solution found OR max_attempts reached

Reward Breakdown

Tasks

Task 1 — Syntax Error Fix (Easy)

ID: task_syntax_fix | Max attempts: 5

A data analyst wrote a query to list Engineering employees by salary but made a typo in a SQL keyword. The agent must fix the syntax error and return the correct 4-row result.

• —Difficulty: Easy — one keyword typo, clear error message
• —Grader: deterministic — execute + check exact result set
• —Baseline score (gpt-4o-mini): 0.68

Task 2 — Logic Bug Fix (Medium)

ID: task_logic_bug | Max attempts: 7

Find customers with completed orders over $500 on or after 2024-10-01. The broken query has three logic bugs: wrong JOIN type, wrong amount threshold, and a reversed date comparison.

• —Difficulty: Medium — multiple bugs, partial credit for each fix
• —Grader: deterministic — execute + check exact result set
• —Baseline score (gpt-4o-mini): 0.52

Task 3 — Query Optimization (Hard)

ID: task_optimization | Max attempts: 7

A production query uses correlated subqueries (runs once per row — O(n²) at scale). The agent must rewrite it using JOIN + GROUP BY + HAVING to be efficient while returning the same results.

• —Difficulty: Hard — must both correct AND optimize
• —Grader: correctness check + query plan cost (EXPLAIN QUERY PLAN scan count)
• —Baseline score (gpt-4o-mini): 0.71

API Reference

Core OpenEnv Endpoints

# Start a new episode (random task)
POST /reset
{}

# Start a specific task
POST /reset
{"task_id": "task_syntax_fix"}

# Submit a SQL query
POST /step
{"sql_query": "SELECT name, salary FROM employees WHERE department='Engineering' ORDER BY salary DESC;"}

# Get current state
GET /state

Judging Endpoints

# List all tasks and action schema
GET /tasks

# Grade a query standalone (no episode needed)
POST /grader
{"task_id": "task_syntax_fix", "sql_query": "SELECT name FROM employees;"}

# Run baseline inference (requires OPENAI_API_KEY)
GET /baseline

Example Session

import httpx

base = "http://localhost:7860"

# Start episode
obs = httpx.post(f"{base}/reset", json={"task_id": "task_syntax_fix"}).json()
print(obs["broken_query"])
# → "SELECT name, salary\nFORM employees\nWHERE department = 'Engineering'\nORDER BY salary DESC;"

# Submit fix
result = httpx.post(f"{base}/step", json={
    "sql_query": "SELECT name, salary FROM employees WHERE department='Engineering' ORDER BY salary DESC;"
}).json()
print(result["reward"]["value"])   # → 0.68
print(result["done"])              # → True

Setup & Running Locally

With Docker (recommended)

git clone https://huggingface.co/spaces/YOUR_USERNAME/sql-query-debugging
cd sql-query-debugging
docker build -t sql-env .
docker run -p 7860:7860 -e OPENAI_API_KEY=sk-... sql-env

Without Docker

pip install -r requirements.txt
python server.py
# Server running at http://localhost:7860

Run Baseline

OPENAI_API_KEY=sk-... python baseline.py

Run Tests

python tests/test_env.py
# Expected: 37 passed, 0 failed

Baseline Scores (gpt-4o-mini, temperature=0)

Project Structure

sql-query-debugging/
├── environment.py      # Core SQLEnv class, Pydantic models, reward engine
├── tasks.py            # 3 task definitions with schemas, broken queries, graders
├── server.py           # FastAPI app with all endpoints
├── baseline.py         # OpenAI API inference script
├── openenv.yaml        # OpenEnv spec metadata
├── Dockerfile          # HF Spaces container
├── requirements.txt
├── README.md
└── tests/
    └── test_env.py     # Full test suite (37 tests)

License

MIT