docs/v18_residue_patterns.md

# v18 residue patterns — что осталось после 86.5% EA

> Written 2026-05-19 night. Audit of the 27 fails in
> `eval/reports/2026-05-18b/v18-gpt52-pro-merged.json` (n=200 BIRD original gold,
> v18 = 173/200 = 86.5% EA).
>
> Цель: найти overlap-паттерны для prompt patch v19 + честная оценка
> headroom + risk assessment regression'ов.

## Spread

| Метрика | Значение |
|---|---|
| Total fails | 27 |
| simple | 5 |
| moderate | 16 |
| challenging | 6 |
| DBs covered | 11 (max 6 в thrombosis_prediction, 4 в formula_1) |

## Pattern classification (per-qid)

| qid | diff | db | pattern | gold-arguably-wrong? |
|---:|:---:|---|---|:---:|
| 25 | mod | california_schools | C: WHERE-source (`District Name LIKE 'Riverside%'` vs `City='Riverside'`) | no |
| 37 | mod | california_schools | C: ORDER BY scope (outer vs subquery; tied values) | no |
| 125 | cha | financial | D: extra-table JOIN (pred adds spurious `client` → row explosion 45→5817) | no |
| 207 | cha | toxicology | B: JOIN-FK choice (`connected.atom_id` vs `connected.bond_id`) | partial |
| 349 | mod | card_games | A: gold nested-subquery for "most" — query structure | partial (Arcwise territory) |
| 408 | mod | card_games | C: missing JOIN to `rulings` (`COUNT(DISTINCT id)` через JOIN) | no — pred bug |
| 484 | mod | card_games | **A1: LIMIT mis-interp** (gold no LIMIT, pred `LIMIT 1`) | no |
| 584 | mod | codebase_community | C: WHERE-source (`postHistory.Comment` vs `comments.Text`) | no |
| 595 | mod | codebase_community | C: GROUP BY granularity (`UserId` vs `UserId,PostId`) | no |
| 694 | mod | codebase_community | C: ORDER BY column (`users.CreationDate` vs `comments.CreationDate`) | partial |
| 743 | cha | superhero | C: WHERE-filter + INNER vs LEFT JOIN + percentage form | no |
| 894 | mod | formula_1 | A2: column projection (gold возвращает `milliseconds`, pred — нет) | no |
| 902 | sim | formula_1 | B: JOIN-table choice (`driverStandings` vs `results`) | no |
| 930 | sim | formula_1 | **A1: LIMIT mis-interp** ("ranked highest" → gold returns all rank=1 races, pred `LIMIT 1`) | no |
| 959 | sim | formula_1 | C: time-format LIKE filter missing (`_:%:__.___`) | no |
| 1029 | mod | european_football_2 | **E: gold wrong** (gold uses `ASC` for "highest", pred uses `DESC`) | **YES** |
| 1094 | cha | european_football_2 | C: aggregation form (`SUM(CASE)` vs `MAX(CASE)`) | partial |
| 1144 | sim | european_football_2 | **A1: LIMIT mis-interp** (gold subquery+LIMIT 1, pred JOIN no-LIMIT → 38 rows) | no |
| 1168 | cha | thrombosis_prediction | A2: column projection (gold +Birthday col) | partial (Arcwise territory) |
| 1205 | mod | thrombosis_prediction | **A1: LIMIT mis-interp** (gold no LIMIT 67 lab records, pred `LIMIT 1`) | no |
| 1247 | cha | thrombosis_prediction | **E: gold wrong** (op precedence: gold `OR FG≥450 AND WBC>3.5 AND ...` without parens) | **YES** |
| 1251 | sim | thrombosis_prediction | F: spurious `Examination` JOIN (gold) | partial — pred natural |
| 1254 | mod | thrombosis_prediction | C: bounds form (`BETWEEN` vs `>`/`<`) + date format | partial |
| 1275 | mod | thrombosis_prediction | C: wrong source table (`Laboratory.CENTROMEA` vs `Examination.CENTROMEA`) | no — pred bug |
| 1399 | mod | student_club | A3: query-structure ("Did X attend Y?" → gold per-row CASE, pred boolean COUNT>0) | partial |
| 1404 | mod | student_club | C: GROUP BY column (`event.type` vs `expense.expense_description`) | no |
| 1531 | mod | debit_card_specializing | C: aggregation form (`SUM(P/A)` vs `SUM(P)/SUM(A)`) | partial |

## Pattern families collapsed

| Family | Count | Notes |
|---|---:|---|
| **A1 — LIMIT mis-interpretation** | 4 (484, 930, 1144, 1205) | Gold uses subquery / no-LIMIT for "highest/lowest/best" when ties exist; pred adds `LIMIT 1` |
| A2 — Column projection (gold +1 col) | 2 (894, 1168) | Gold returns extra grouping col not in question |
| A3 — Query structure | 1 (1399) | "Did X attend Y?" → BIRD wants per-attendance-row CASE |
| **B — JOIN-path / FK / source-table choice** | 4 (207, 902, 959, 1275) | driverStandings/results, results.fastestLap, Examination/Laboratory |
| **C — WHERE/filter/GROUP-BY semantics** | 11 (25, 37, 125, 408, 584, 595, 694, 743, 1094, 1254, 1404, 1531) | Heterogeneous — каждый case уникален |
| D — Extra-table JOIN expansion | 1 (125) | Spurious `client` → 5817 rows |
| **E — Gold itself wrong (Arcwise catch territory)** | 2 (1029, 1247) | Confirmed Arcwise-style: ASC-for-highest, op-precedence bug |
| F — Spurious JOIN in gold | 1 (1251) | Examination INNER drops valid patients |

## Realistic v19 prompt-patch headroom

### Patch P1 — LIMIT discipline (A1 family, 4 cases) — **CLOSED 2026-05-19 night: NEGATIVE**

**Experiment** (config C codestral baseline, n=200, seed 0):

| Run | simple | moderate | challenging | overall |
|---|---:|---:|---:|---:|
| P2+P3 only (baseline) | 71.6% | 50.5% | 41.2% | **56.0% (112/200)** |
| P1+P2+P3 | 68.7% | 50.5% | 41.2% | **55.0% (110/200)** |
| Delta | **−2.9pp** | 0 | 0 | **−1.0pp (−2 cases)** |

Per-qid:
- P1 wins (was FAIL, now PASS): 6 cases (118, 168, 327, 909, 1340, 1390)
- P1 regressions (was PASS, now FAIL): 8 cases (98, 99, 189, 707, 865, 1281, 1500, 1528)
- **Target qids (484, 930, 1144, 1205): 0/4 rescued** — все остались FAIL обоих runs.

**Verdict:** P1 net-regressive at codestral baseline layer. The intended 4 targets (LIMIT mis-interp on v18 voting-survived residue) are **deep hard cases** the prompt patch alone cannot flip. Meanwhile the patch causes scattered regressions on simple-tier cases that previously chose correct `LIMIT 1`.

P1 **reverted** from working tree. Не возвращаться без orthogonal mechanism (e.g., row-count-aware repair pass that catches tied-rows truncation).

**Orthogonal mechanism attempt CLOSED 2026-05-19 night: NEGATIVE.** Codex implemented `row_count_repair` node (AST-level LIMIT 1 detection + tie-prone question regex + re-execute without LIMIT + column-shape acceptance). Tests 4/4 pass, gate green. Empirical n=200 config C codestral: P2+P3 baseline 56.0% → +rcrepair 55.5% (**−1 case, qid 1157 regression, 0 rescues**). Of 23 cases eligible (LIMIT 1 + tie-prone + pred_row_count=1), zero actually got repaired in the final state — pred_sql unchanged. Likely state-update propagation issue in langgraph wiring or run-to-run variance in codestral generation. Reverted. Artefacts: `eval/reports/2026-05-19/C_dense_cards-rcrepair.json`.

**Vendor: the 4 target qids (484, 930, 1144, 1205) are truly hard.** Neither prompt patch nor execute-feedback heuristic at codestral baseline layer flips them. They sit in v18 86.5% residue precisely because the full voting stack (gpt-5.2 Pro, sonnet-thinking, grok, kimi) also couldn't rescue. Past 86.5% won't come from baseline-layer tooling — only from new voting-layer additions (cooldown-gated) or paid escalation.

### Patch P4 — CSC merge-revision (arXiv:2505.13271) — **CLOSED 2026-05-19 morning: NULL**

Two independent research sources (r1.md, r2.md в корне репо) сошлись на CSC-SQL merge-revision как самом сильном free-tier lever (+2-4pp за счёт top-2 cluster judge между disagreeing самплов). Реализовал поверх `eval/self_consistency.py` (новая функция `vote_with_csc_merge` + prompt-шаблон) + флаг `--enable-csc-merge` в `scripts/eval_baseline.py`.

**Experiment** (config F = codestral self-consistency × 4 temperatures [0.2,0.4,0.6,0.8], n=200, seed 0):

| Run | simple | moderate | challenging | overall | wall |
|---|---:|---:|---:|---:|---:|
| F baseline (plain vote) | 71.6% | 56.6% | 47.1% | **60.0% (120/200)** | 29.5 min |
| F + CSC merge-revision | 71.6% | 56.6% | 47.1% | **60.0% (120/200)** | 2.6 min (cache) |
| Delta | 0 | 0 | 0 | **+0 cases (+0.00pp)** | — |

Per-qid: 0 wins, 0 regressions. CSC merge-revision triggered on **6/200 = 3% cases** (qid 159, 407, 414, 1037, 1205, 1531 — pred_sql changed). None of the 6 flipped the match flag: на 5 случаях both candidates были одинаково wrong vs gold; на qid 414 both — semantically equivalent SQL, both PASS.

**Target qids:** 484, 930, 1144 — top-1 cluster unanimous (codestral 4 temps все согласны на wrong LIMIT 1 SQL), CSC даже не fire'нул. qid 1205 — fired, но альтернативный candidate тоже неправ.

**Verdict:** CSC null on this setup. Why:
1. **Codestral self-consistency homogeneous** — 4 temperatures sample from one model with same biases → 97% questions имеют top-1 strictly majority (>50%) → CSC threshold не пробивается.
2. **Judge LLM = generator LLM** — даже когда candidates disagree, codestral как judge не имеет independent ground truth (same training, same blind spots).
3. **Hard targets unanimous** — все 4 temps выдают одну и ту же неправильную SQL для LIMIT-mis-interp cases.

**Когда CSC мог бы помочь:** N-rep (different schema representations per candidate) + diverse base models (codestral + Qwen + OmniSQL). На single-model homogeneous self-consistency lift = 0.

Implementation reverted. Artefacts: `eval/reports/2026-05-19/F_self_consistency-{F_baseline_v2,F_csc_v2}.json`.

Artefacts: `eval/reports/2026-05-19/C_dense_cards-p23_baseline.json`, `C_dense_cards-p1p23.json`.

### Patch P1 ORIGINAL proposal (для истории)

**Proposed addition to system prompt:**

> При вопросах формата "highest/lowest/best/most X" или "the player/card/team with the most/least Y":
> если результат может содержать ties (несколько строк с одинаковым экстремальным значением),
> верни все tied rows — используй subquery `WHERE col = (SELECT MAX(col) FROM ...)` либо
> `ORDER BY col DESC` без `LIMIT 1`. Добавляй `LIMIT 1` **только** когда вопрос явно
> требует одну запись ("the single", "the top one", "first" с явным указанием на одну).

**Expected:** +2-4 cases on residue (484, 930, 1144, 1205 — all 4 are LIMIT-discipline).
**Risk:** regression on legit `LIMIT 1` cases (e.g., qid 37 already removes LIMIT 1 правильно через subquery — но какой-то simple "the school with the lowest score" case в текущем passing-set может ослабнуть). Нужно прогнать на full n=200 чтобы померить regression cost.

### Patch P2 — driverStandings vs results disambiguation (B family, 1 case)

**Proposed schema-doc addition (db_id=formula_1):**

> `driverStandings.position` = season standings rank (per race snapshot of overall standings).
> `results.position` / `results.positionOrder` = race finish position (per race).
> "track number" / "in track number less than 20" → `driverStandings.position` (standings rank).
> "finished in position N" / "Nth place in the race" → `results.position`.

**Expected:** +1 case (902).
**Risk:** low — schema clarification, не behavioral nudge.

### Patch P3 — postHistory vs comments disambiguation (C/B family, 1 case)

**Proposed schema-doc addition (db_id=codebase_community):**

> `postHistory.Comment` = the edit comment left by an editor.
> `comments.Text` = a reader's comment on the post.
> "comments left by users who edited" → `postHistory.Comment` (the edit message).
> "comments to the post" / "comments under" → `comments.Text`.

**Expected:** +1 case (584).
**Risk:** low.

### Combined ceiling

| Scenario | Best case | Worst case (regression) |
|---|---:|---:|
| P1 only | +4 cases (+2.0pp) | +0 cases (if regression equals gain) |
| P2 + P3 only | +2 cases (+1.0pp) | +2 cases (low regression risk) |
| P1+P2+P3 | +6 cases (+3.0pp) | +2 cases (P1 regression cancels) |

**Headline target:** v19 = 87.5-89.5% EA (175-179/200), if P1 has zero regression.
**Realistic:** v19 = 87.0-87.5% EA (174-175/200), expecting some P1 regression.

## What can't be patched cheaply

- **Family A2/A3 (column projection, query structure)** — gold's choices for which columns to project or whether to return per-row vs aggregate are not derivable from question text alone. Would need example-driven few-shot patches per pattern. Marginal cost.
- **Family C (heterogeneous)** — 11 unique semantics, each needs own example. Diminishing returns.
- **Family D/F (extra JOIN, spurious JOIN)** — P3.F-style schema linker. Multi-day. p3f_design.md says don't speculate.
- **Family E (gold wrong)** — Arcwise catches. Already credited in 72.36% Arcwise-Plat number. No v19 patch needed.

## Recommended action

Apply P2 + P3 only (low-risk schema-doc patches). **Defer P1** until evidence that LIMIT-discipline patch на n=200 не регрессит. Запустить experimental v19 build with P2+P3 + run full n=200 eval — expected +1pp without regression.

P1 экспериментально гонять на v18-passing subset (173 cases) и измерять regression rate напрямую. Если ≤+0 regression, добавлять; иначе skip.

## How to verify regression for P1

```bash
# 1. Apply P1 prompt patch
# 2. Re-run full n=200 eval
make eval ARGS="--limit 200"
# 3. Compare per-qid match flags v18 baseline vs v19
python scripts/audit_rescore.py \
  --baseline eval/reports/2026-05-18b/v18-gpt52-pro-merged.json \
  --candidate eval/reports/<date>/v19-with-P1.json
# 4. Count regressions (passing in v18, failing in v19)
```

If regression count > P1 gain count, **revert P1**.