myenv / server /grader.py
prem1505
Initialize space with OpenEnv SQL Optimizer
1e97968
Raw History Blame Contribute Delete
11.5 kB
"""
grader.py β€” Deterministic, reproducible grader for SQL query submissions.
Scoring formula (all sub-scores 0.0–1.0):
total = 0.50 * correctness + 0.30 * performance + 0.20 * style
Correctness β€” Structural equivalence heuristic via sqlglot AST comparison.
Performance β€” Estimated cost reduction based on anti-pattern elimination.
Style β€” Absence of forbidden patterns + presence of expected improvements.
"""
from __future__ import annotations
import re
from typing import Any
import sqlglot
import sqlglot.errors
from .tasks import Task
# Weights
W_CORRECTNESS = 0.50
W_PERFORMANCE = 0.30
W_STYLE = 0.20
def grade_submission(
task: Task,
optimized_query: str,
explanation: str,
) -> tuple[Any, dict[str, Any]]:
"""
Grade the agent's optimized_query against task's reference.
Returns (Reward, info_dict).
"""
# Import here to avoid circular import
from .main import Reward # noqa: PLC0415
correctness, correctness_info = _grade_correctness(task, optimized_query)
performance, performance_info = _grade_performance(task, optimized_query)
style, style_info = _grade_style(task, optimized_query, explanation)
total = round(
W_CORRECTNESS * correctness
+ W_PERFORMANCE * performance
+ W_STYLE * style,
4,
)
feedback_parts = []
if correctness >= 0.9:
feedback_parts.append("βœ… Query appears structurally correct.")
elif correctness >= 0.5:
feedback_parts.append("⚠️ Query may be partially correct β€” check result equivalence.")
else:
feedback_parts.append("❌ Query likely changes the result set or has syntax errors.")
if performance >= 0.8:
feedback_parts.append("βœ… Good performance improvements detected.")
elif performance >= 0.4:
feedback_parts.append("⚠️ Some performance gains but key anti-patterns remain.")
else:
feedback_parts.append("❌ Little or no performance improvement detected.")
if style >= 0.8:
feedback_parts.append("βœ… Clean, idiomatic SQL with no anti-patterns.")
else:
feedback_parts.append(f"⚠️ Style issues: {style_info.get('issues', [])}")
feedback = " | ".join(feedback_parts)
reward = Reward(
total=total,
correctness=round(correctness, 4),
performance=round(performance, 4),
style=round(style, 4),
feedback=feedback,
)
info = {
"correctness_detail": correctness_info,
"performance_detail": performance_info,
"style_detail": style_info,
}
return reward, info
# ---------------------------------------------------------------------------
# Sub-graders
# ---------------------------------------------------------------------------
def _grade_correctness(task: Task, query: str) -> tuple[float, dict]:
"""
Heuristic structural correctness via sqlglot AST.
Full points if:
1. Query parses without error.
2. All base tables from the slow query still appear (CTE aliases excluded).
3. SELECT column semantics are preserved.
"""
info: dict[str, Any] = {}
# 1. Parse check
try:
sqlglot.parse_one(query, dialect="postgres")
info["parse"] = "ok"
except sqlglot.errors.ParseError as e:
info["parse"] = f"error: {e}"
return 0.0, info
# 2. Same base tables referenced?
# Extract CTE names so we don't mistake them for missing real tables
cte_names = _extract_cte_names(query)
ref_tables = _extract_tables(task.slow_query)
agent_tables = _extract_tables(query) | cte_names # CTEs count as "present"
missing_tables = ref_tables - agent_tables
if missing_tables:
info["missing_tables"] = list(missing_tables)
if not _extract_tables(query): # truly no tables (e.g. SELECT 1)
return 0.1, info
return 0.3, info
info["tables"] = "ok"
# 3. Check SELECT column preservation
slow_cols = _extract_select_columns(task.slow_query)
agent_cols = _extract_select_columns(query)
# SELECT * replaced with explicit columns β†’ full credit
if re.search(r"SELECT\s+\*", task.slow_query, re.IGNORECASE) and len(agent_cols) >= 1:
info["select"] = "select_star_replaced"
return 1.0, info
# CTE-based rewrite typically restructures column names; give benefit of doubt
if "WITH " in query.upper() and len(agent_cols) >= 1:
info["select"] = "cte_rewrite_assumed_ok"
return 0.9, info
# Check overlap ratio for non-CTE rewrites
if slow_cols and agent_cols:
overlap = len(slow_cols & agent_cols) / len(slow_cols)
info["select"] = f"overlap={overlap:.2f}"
if overlap >= 0.6:
return 0.85 + 0.15 * overlap, info
return 0.5, info
info["select"] = "ok_assumed"
return 0.9, info
def _grade_performance(task: Task, query: str) -> tuple[float, dict]:
"""
Score based on:
1. Forbidden anti-patterns eliminated.
2. Expected improvement patterns present.
3. Estimated cost reduction (proxy via heuristics).
"""
info: dict[str, Any] = {}
query_upper = query.upper()
query_norm = " ".join(query.split())
# Anti-pattern elimination (each elimination worth points)
anti_patterns_found = []
for pattern in task.forbidden_patterns:
if re.search(pattern, query, re.IGNORECASE | re.DOTALL):
anti_patterns_found.append(pattern)
info["anti_patterns_remaining"] = anti_patterns_found
elimination_score = 1.0 - (len(anti_patterns_found) / max(len(task.forbidden_patterns), 1))
# Expected improvements present
improvements_found = []
improvement_checks = {
"specific columns instead of *": lambda q: "SELECT *" not in q.upper(),
"index hint or note": lambda q: "INDEX" in q.upper() or "/*+" in q,
"CTE or derived table for regional_avg": lambda q: "WITH" in q.upper() and "AVG" in q.upper(),
"JOIN instead of correlated subquery": _no_correlated_subquery,
"CTE to pre-join events once": lambda q: "WITH" in q.upper() and "JOIN" in q.upper(),
"FILTER aggregate or CASE WHEN instead of correlated subqueries":
lambda q: "FILTER" in q.upper() or "CASE WHEN" in q.upper(),
"single GROUP BY at cohort level": lambda q: q.upper().count("GROUP BY") <= 2,
}
expected = task.expected_improvements
for imp in expected:
checker = improvement_checks.get(imp)
if checker and checker(query):
improvements_found.append(imp)
improvement_score = len(improvements_found) / max(len(expected), 1)
info["improvements_found"] = improvements_found
# CTE / JOIN bonus
has_cte = "WITH " in query.upper()
has_join = re.search(r"\bJOIN\b", query, re.IGNORECASE) is not None
structural_bonus = 0.0
if has_cte:
structural_bonus += 0.1
if has_join and task.difficulty in ("medium", "hard"):
structural_bonus += 0.1
structural_bonus = min(structural_bonus, 0.2)
perf_score = min(1.0, 0.5 * elimination_score + 0.4 * improvement_score + structural_bonus)
info["elimination_score"] = round(elimination_score, 3)
info["improvement_score"] = round(improvement_score, 3)
info["structural_bonus"] = round(structural_bonus, 3)
return round(perf_score, 4), info
def _grade_style(task: Task, query: str, explanation: str) -> tuple[float, dict]:
"""
Style checks:
- No SELECT * remaining
- Explanation provided (shows reasoning)
- No obviously repeated identical sub-expressions
- Consistent casing / formatting heuristic
"""
issues = []
bonuses = []
if "SELECT *" in query.upper():
issues.append("SELECT * still present")
# Reward explanation
if len(explanation.strip()) > 20:
bonuses.append("explanation_provided")
# Reward CTE usage (idiomatic SQL)
if re.search(r"\bWITH\b", query, re.IGNORECASE):
bonuses.append("uses_cte")
# Penalize repeated identical large subqueries (copy-paste)
subqueries = re.findall(r"\(SELECT[^)]{30,}\)", query, re.IGNORECASE | re.DOTALL)
unique_subqs = set(s.strip().upper() for s in subqueries)
if len(subqueries) > len(unique_subqs):
issues.append("repeated_identical_subqueries")
# LIMIT sanity (for hard tasks, not stripping LIMIT)
if task.difficulty == "hard" and "LIMIT" in task.slow_query.upper():
if "LIMIT" not in query.upper():
issues.append("LIMIT removed β€” may return too many rows")
score = 1.0 - 0.25 * len(issues) + 0.05 * len(bonuses)
score = max(0.0, min(1.0, score))
return round(score, 4), {"issues": issues, "bonuses": bonuses}
# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------
def _extract_cte_names(sql: str) -> set[str]:
"""Extract CTE alias names defined in WITH clauses."""
names: set[str] = set()
for m in re.finditer(r"(?:WITH|,)\s+([a-zA-Z_][a-zA-Z0-9_]*)\s+AS\s*\(", sql, re.IGNORECASE):
names.add(m.group(1).lower())
return names
def _extract_tables(sql: str) -> set[str]:
"""Extract table names referenced in FROM / JOIN clauses."""
# Remove CTEs first (they introduce aliases not real tables)
sql_no_cte = re.sub(r"\bWITH\b.*?\bSELECT\b", "SELECT", sql, flags=re.IGNORECASE | re.DOTALL)
pattern = r"\b(?:FROM|JOIN)\s+([a-zA-Z_][a-zA-Z0-9_]*)"
return {m.group(1).lower() for m in re.finditer(pattern, sql_no_cte, re.IGNORECASE)}
def _extract_select_columns(sql: str) -> set[str]:
"""Very rough extraction of column names from SELECT list."""
m = re.search(r"\bSELECT\b(.*?)\bFROM\b", sql, re.IGNORECASE | re.DOTALL)
if not m:
return set()
cols_raw = m.group(1)
# Extract identifiers
tokens = re.findall(r"[a-zA-Z_][a-zA-Z0-9_]*", cols_raw)
# Filter SQL keywords
keywords = {"as", "distinct", "all", "top", "from", "where", "and", "or",
"not", "null", "is", "in", "like", "between", "case", "when",
"then", "else", "end", "coalesce", "sum", "count", "avg",
"max", "min", "date_trunc"}
return {t.lower() for t in tokens if t.lower() not in keywords}
def _no_correlated_subquery(query: str) -> bool:
"""
Return True (improvement present) if the query does NOT contain
a correlated subquery. A correlated subquery is detected when:
- There is a subquery (parenthesised SELECT) in a WHERE/SELECT clause
- AND that subquery's WHERE references an alias defined outside it
e.g. WHERE o2.region = o.region where o is an outer alias
Heuristic: look for the pattern of an inner SELECT whose WHERE clause
references two different table aliases separated by '='.
"""
# Pattern: (SELECT ... WHERE <alias1>.<col> = <alias2>.<col> ...)
# where the two aliases are different β†’ correlated
inner_pattern = re.compile(
r"\(\s*SELECT\b.*?\bWHERE\b.*?(\b\w+)\.\w+\s*=\s*(\b\w+)\.\w+",
re.IGNORECASE | re.DOTALL,
)
for m in inner_pattern.finditer(query):
a1, a2 = m.group(1).lower(), m.group(2).lower()
if a1 != a2:
return False # correlated subquery found β†’ improvement NOT present
return True # no correlated subquery found β†’ improvement IS present