Download server/grader.py from anu151105/myenv: direct link, hf CLI and curl.
- Browser
- Download file 11.5 kB
-
https://huggingface.co/spaces/anu151105/myenv/resolve/main/server/grader.py
- Command line
-
hf download hf://spaces/anu151105/myenv/server/grader.py
-
curl -L -o grader.py https://huggingface.co/spaces/anu151105/myenv/resolve/main/server/grader.py
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 | |