File size: 6,719 Bytes
4e1037f | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 | """Archive sweep: scan eval/reports/**/*.json for stale pred_sql that match
gold under the *current* corrected runner, for any qid currently missing in a
baseline report.
Use this after a runner-level fix (e.g. the day-5 bind-bug fix in
`db/connection.py`) or a scoring-methodology change (e.g. Counter → set in
`compare_results`): pred_sqls that were written long ago may have become
correct because the gold side stopped silently dropping rows or because the
matcher is no longer over-strict. Each rescue is a *re-verification*, not a
fresh model call — strictly $0 budget and offline.
Audit discipline: every candidate is re-executed live; the script never trusts
a stored `match` flag from the source report. Audit it afterwards via
`scripts/audit_rescore.py`.
Example:
uv run python scripts/archive_sweep.py \
--baseline eval/reports/2026-05-23/v24-v23-plus-archive-rescore-959-merged.json \
--out eval/reports/2026-05-23/archive-sweep-v24-candidates.json
"""
from __future__ import annotations
import argparse
import glob
import json
from pathlib import Path
from typing import Any
from nl_sql.db import DatabaseSpec
from nl_sql.db.connection import execute_readonly, sqlite_url_readonly
from nl_sql.eval.metrics.execution_accuracy import compare_results
from nl_sql.eval.runner import _execute_gold
def main() -> int:
p = argparse.ArgumentParser(description=__doc__)
p.add_argument("--baseline", type=Path, required=True)
p.add_argument("--reports-root", type=Path, default=Path("eval/reports"))
p.add_argument("--out", type=Path, required=True)
p.add_argument(
"--data-root",
type=Path,
default=Path("data/bird_mini_dev/MINIDEV/dev_databases"),
)
p.add_argument(
"--only-qids",
type=str,
default=None,
help="Optional comma-separated qids to restrict sweep to.",
)
args = p.parse_args()
baseline = json.loads(args.baseline.read_text(encoding="utf-8"))
misses = [r for r in baseline["records"] if not r.get("match")]
if args.only_qids:
wanted = {int(x.strip()) for x in args.only_qids.split(",") if x.strip()}
misses = [r for r in misses if r.get("question_id") in wanted]
miss_index = {r["question_id"]: r for r in misses}
print(f"baseline: {args.baseline}")
print(f" misses: {len(misses)} (qids: {sorted(miss_index)})")
candidates: dict[int, set[str]] = {q: set() for q in miss_index}
for rp in glob.glob(str(args.reports_root / "**" / "*.json"), recursive=True):
rp_path = Path(rp)
if rp_path.resolve() == args.baseline.resolve():
continue
try:
d = json.loads(rp_path.read_text(encoding="utf-8"))
except Exception:
continue
recs = d.get("records", []) if isinstance(d, dict) else []
for r in recs:
if not isinstance(r, dict):
continue
qid_raw = r.get("question_id") or r.get("qid")
if not isinstance(qid_raw, int) or qid_raw not in miss_index:
continue
qid = qid_raw
for key in ("pred_sql", "alt_pred"):
pred = r.get(key) or ""
if isinstance(pred, str) and pred.strip():
candidates[qid].add(pred.strip())
total_cands = sum(len(v) for v in candidates.values())
print(f" unique candidate pred_sqls: {total_cands}")
rescues: list[dict[str, Any]] = []
examined: list[dict[str, Any]] = []
for qid in sorted(miss_index):
miss = miss_index[qid]
db_id = miss["db_id"]
gold_sql = miss["gold_sql"]
db_path = args.data_root / db_id / f"{db_id}.sqlite"
spec = DatabaseSpec(id=db_id, dialect="sqlite", url=sqlite_url_readonly(db_path))
engine = spec.make_engine()
try:
try:
gold_rows, _ = _execute_gold(
engine, gold_sql, statement_timeout_ms=30_000, row_cap=10_000
)
except Exception as exc:
print(f" qid={qid}: gold failed: {exc!r}")
gold_rows = []
found = False
for pred in sorted(candidates[qid]):
try:
with execute_readonly(
engine, pred, statement_timeout_ms=30_000, row_cap=10_000
) as result:
pred_rows = list(result.rows)
except Exception:
pred_rows = []
continue
cmp = compare_results(gold_rows, pred_rows, gold_sql=gold_sql)
if cmp.match:
rescues.append(
{
"question_id": qid,
"difficulty": miss.get("difficulty"),
"db_id": db_id,
"alt_pred": pred,
"alt_match": True,
"alt_rows": len(pred_rows),
"gold_rows": len(gold_rows),
"baseline_match": False,
}
)
print(
f" qid={qid} {miss.get('difficulty'):>11} db={db_id}: RESCUE "
f"(alt_rows={len(pred_rows)}, gold_rows={len(gold_rows)})"
)
found = True
break
examined.append(
{
"question_id": qid,
"difficulty": miss.get("difficulty"),
"db_id": db_id,
"candidates": len(candidates[qid]),
"rescued": found,
}
)
if not found:
print(
f" qid={qid} {miss.get('difficulty'):>11} db={db_id}: no archive rescue "
f"({len(candidates[qid])} cand)"
)
finally:
engine.dispose()
out = {
"alt_model": "archive-sweep",
"baseline": str(args.baseline).replace("\\", "/"),
"summary": {
"voted_better": len(rescues),
"voted_worse": 0,
"voted_same": 0,
"examined_qids": len(miss_index),
"total_candidates": total_cands,
},
"examined": examined,
"records": rescues,
}
args.out.parent.mkdir(parents=True, exist_ok=True)
args.out.write_text(json.dumps(out, indent=2), encoding="utf-8")
print(f"wrote: {args.out}")
print(f" rescues: {len(rescues)} / {len(miss_index)} misses")
return 0
if __name__ == "__main__":
raise SystemExit(main())
|