sql-repair-env / README.md
Savita Srivastava
readme harshit ne hi udaya tha
19e8836
|
Raw History Blame Contribute Delete
7.62 kB
metadata
title: SQL Repair Env
colorFrom: blue
colorTo: green
sdk: docker
app_port: 7860

Interactive SQL Query Repair Environment

An OpenEnv-compatible reinforcement learning environment where an agent learns to fix broken SQL queries through multi-turn interaction.


Overview

The agent receives a broken SQL query, a plain-English intent, and a compact schema hint. Over multiple turns it can:

  • Run partial queries β€” test a fix and see whether SQLite errors
  • Ask clarification questions β€” probe the schema or intent for hints
  • Submit a final query β€” evaluated against ground-truth SQLite execution

Grading is fully objective: does the repaired query return the correct rows?


Environment Details

Action Space

Action Fields Effect
submit_query query: str Finalises the episode; scored by Jaccard similarity
run_partial query: str Tests a query; returns error message or OK
ask_clarification question: str Returns a schema/intent hint if available

Observation Space

Field Type Description
task_id str Unique task identifier
difficulty easy|medium|hard Difficulty level
intent str Plain-English goal for the query
schema_hint str Compact table signatures
broken_query str The query to fix (unchanged across turns)
turn int Current turn number
max_turns int Maximum turns for this difficulty
error_message str|null SQLite error from last run_partial / failed submit
clarification_response str|null Answer to last ask_clarification
last_action_type str|null Previous action type

Reward Function

submit_query:
  jaccard == 1.0  β†’  +1.0   (perfect)
  jaccard >= 0.8  β†’  +0.5   (close)
  jaccard >= 0.5  β†’  +0.2   (partial)
  jaccard <  0.5  β†’  -0.5   (wrong)
  minus turn_penalty = 0.05 Γ— (turns_used - 1)
  clamped to [-1.0, 1.0]

run_partial:
  no SQL error  β†’  +0.05
  SQL error     β†’  -0.10

ask_clarification:
  answer found   β†’   0.00
  no answer      β†’  -0.05

Grader Formula

score = 0.60 Γ— correctness
      + 0.25 Γ— turn_efficiency
      + 0.15 Γ— clarification_quality

correctness           = final Jaccard score
turn_efficiency       = 1 - (turns_used / max_turns)
clarification_quality = useful_hits / total_asks  (1.0 if no asks)

All scores are deterministic and in [0.0, 1.0].


Tasks

Easy (15 tasks, max 3 turns)

  • Single table (users)
  • Error types: syntax errors, wrong operators, missing keywords
  • Examples: COUNTS(*) β†’ COUNT(*), ORDER age β†’ ORDER BY age

Medium (15 tasks, max 5 turns)

  • Two tables (users + orders or orders + products)
  • Error types: wrong join keys, WHERE vs HAVING, missing LEFT JOIN
  • Examples: join on orders.id instead of orders.user_id

Hard (10 tasks, max 8 turns)

  • Three to five tables with CTEs and subqueries
  • Error types: nested aggregates, correlated subqueries, multi-table joins
  • Designed so naive first-attempt strategies score ≀ 0.40
  • Examples: AVG(SUM(...)) requires CTE, tag intersection requires self-join

API Reference

Endpoint Method Description
/health GET Liveness probe
/tasks GET List all 40 tasks
/reset POST Start a new episode
/step POST Take one action
/state GET Current episode state
/grader GET Final score for current episode

POST /reset

{
  "task_id": "medium_001",
  "difficulty": "medium"
}

POST /step β€” submit_query

{
  "type": "submit_query",
  "query": "SELECT DISTINCT u.name FROM users u JOIN orders o ON u.id = o.user_id"
}

POST /step β€” run_partial

{
  "type": "run_partial",
  "query": "SELECT * FROM users WHERE age > 30"
}

POST /step β€” ask_clarification

{
  "type": "ask_clarification",
  "question": "what column in orders links to users?"
}

Setup & Running

Local (Python)

pip install -r requirements.txt
uvicorn server.app:app --host 0.0.0.0 --port 7860

Docker

docker build -t sql-repair-env .
docker run -p 8000:8000 sql-repair-env

Test it:

curl http://localhost:7860/health
curl http://localhost:7860/tasks
curl -X POST http://localhost:7860/reset \
  -H "Content-Type: application/json" \
  -d '{"task_id": "easy_001", "difficulty": "easy"}'

Running the Baseline

export API_BASE_URL="https://router.huggingface.co/v1"
export MODEL_NAME="Qwen/Qwen2.5-72B-Instruct"
export HF_TOKEN="your-token-here"
export ENV_SERVER_URL="http://localhost:7860"

python inference.py

Expected baseline scores (approximate):

Difficulty Score range
Easy 0.55 – 0.80
Medium 0.30 – 0.55
Hard 0.10 – 0.30

Project Structure

sql-repair-env/
β”œβ”€β”€ app/
β”‚   β”œβ”€β”€ models.py        # All Pydantic types
β”‚   β”œβ”€β”€ env.py           # Core environment logic
β”‚   β”œβ”€β”€ executor.py      # SQLite execution layer
β”‚   β”œβ”€β”€ grader.py        # Deterministic scoring
β”‚   └── data_loader.py   # Dataset loader (module singleton)
β”œβ”€β”€ data/
β”‚   β”œβ”€β”€ easy.json        # 15 easy tasks
β”‚   β”œβ”€β”€ medium.json      # 15 medium tasks
β”‚   └── hard.json        # 10 hard tasks
β”œβ”€β”€ db/
β”‚   └── fixtures.sql     # SQLite seed data
β”œβ”€β”€ server/
β”‚   └── app.py           # FastAPI server
β”œβ”€β”€ inference.py         # Baseline inference script
β”œβ”€β”€ openenv.yaml         # OpenEnv spec
β”œβ”€β”€ Dockerfile
β”œβ”€β”€ requirements.txt
└── README.md

Design Decisions

Why SQLite in-process? Zero external infrastructure β€” the DB seeds from fixtures.sql on every reset(), guaranteeing a clean deterministic state per episode.

Why Jaccard similarity? Row order is undefined in SQL; Jaccard treats results as sets, making grading robust to ORDER BY differences between the agent's query and the reference.

Why deny actual row data to the agent? Showing only error messages forces the agent to reason about the query structure, not memorise the dataset. This better simulates real-world debugging where production data isn't available.

Why three action types? run_partial + ask_clarification before submit_query models real developer behaviour: check syntax, check schema, then commit. The turn penalty in the reward function ensures the agent learns to use these efficiently rather than exhausting the budget.