""" 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 . = . ...) # 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