docs/p3f_design.md

# P3.F — JOIN-path schema-linker: design analysis & realistic ceiling

> Status: analysis complete, code deferred. Written 2026-05-18 after llama70b
> TPD-reset retry sanity check (`v11_saturation_evidence.md` § day-3).

## Why P3.F exists

The v11 residue is 38 cases. The biggest single bucket is `row_count_off`
(20 cases), and `feedback_bird_ceiling_physics` + memory suggested P3.F
(custom JOIN-path schema-linker) could lift it +5–10pp by addressing
"`row_count_off` is structural unanimous failure across all Mistral models".

## Bucket sub-classification (script-derived, n=20)

Run `python -c` snippet on `eval/reports/2026-05-17/…-v11.json` with table-set
diffing + DISTINCT diffing gave:

| Sub-bucket | Count | Description |
|---|---:|---|
| same_tables_diff_join_cols_or_filter | 10 | Pred picks same tables as gold, but wrong JOIN ON column, wrong WHERE column, or wrong projection |
| missing_table_in_pred | 5 | Pred substitutes wrong table or omits a required one |
| distinct_diff_only | 4 | Bidirectional: 3 cases gold-has-DISTINCT/pred-doesn't, 1 case pred-has-DISTINCT/gold-doesn't |
| extra_table_in_pred | 1 | Pred joined extra table that changes row count |

## Per-qid audit of the "same_tables_diff_join_cols_or_filter" bucket

This is the supposed P3.F target. Reading each gold ↔ pred pair:

| qid | diff | Real root cause | Solvable by JOIN-path linker? |
|---:|---|---|---|
| 77 | mod | Pred filters on `frpm.CountyName + Low/High Grade`, gold on `schools.County + GSserved='K-9'`. Wrong **filter-column source-table**. | partially — needs column-to-table grounding heuristic |
| 207 | chal | Pred joins `connected.bond_id`, gold joins `connected.atom_id`. Wrong **FK choice** between same tables. | **yes — classic JOIN-path** |
| 484 | mod | Pred adds `LIMIT 1`, gold doesn't (returns all 155 cards tied at top mana cost). **Query-structure mis-interpretation**. | no |
| 518 | mod | Gold uses WITH-clause to find max format then selects all matching cards. Pred just GROUP BY + LIMIT 1. **Query-structure mis-interpretation**. | no |
| 930 | simple | Gold uses subquery IN (returns 37 races where Hamilton ranked 1). Pred uses JOIN + ORDER BY ASC + LIMIT 1 (returns single best race). **Semantic mis-interpretation of "highest rank"**. | no |
| 990 | chal | Pred missed `WHERE results.time LIKE '_:%:__.___'` filter from gold. **WHERE clause omission**. | partially — needs evidence-grounded WHERE |
| 1144 | simple | Pred uses JOIN, gold uses subquery + LIMIT 1. Pred returns 38 rows (Player_Attributes has 38 rows per player). **Subquery-vs-JOIN issue**. | no |
| 1205 | mod | Pred has `LIMIT 1`, gold doesn't. Gold returns 67 lab records for patient 57266; pred truncates to 1. **LIMIT mis-interpretation**. | no |
| 1399 | mod | Gold returns 14 rows (one per attendance match) via CASE WHEN. Pred returns single COUNT > 0 boolean. **Query-structure interpretation** ("Did X attend Y?" → BIRD wants per-attendance-row not single bool). | no |
| 1404 | mod | Pred groups by `expense.expense_description`, gold groups by `event.type`. Wrong **GROUP BY column source-table**. | **yes — schema linking** |

**Solvable-by-JOIN-path-linker count: ~2** (qid 207, 1404), maybe 2 more partial
(qid 77, 990 if linker also handles WHERE-column source).

## Realistic ceiling revision

Earlier memory: «P3.F +5–10pp ceiling lift, дни-недели работы».
Reality after audit: **+1–2pp on residue = +0.5–1pp on n=200 EA.** Most of the
20 row_count_off cases are query-structure mis-interpretations (LIMIT/subquery/CASE
shape), not JOIN-path choice errors. A schema-linker addresses 2–4 cases out
of 38 residue.

Combined with other buckets:
- `distinct_diff_only` (4): would need a bidirectional DISTINCT-rule, but it's
  bidirectional — same prompt rule would regress qid 407 (where gold lacks
  DISTINCT but pred adds it).
- `set_mismatch` (10), `col_projection_off` (7): not addressed by JOIN-path linker.

**Total realistic chrome-free $0-budget headroom past v11 81.0%:** ≤+2.5pp.
This matches the upper bound from `v11_saturation_evidence.md` § lower bound
estimate (binomial CI ≤5% rescue rate across all attempted free-tier voting).

## Design (sketch only, not implemented)

If we did build P3.F:

1. **Foreign-key candidate enumeration.** For each pair of tables (T1, T2)
   in retrieved set, collect ALL FK paths via SQLite `pragma foreign_key_list`
   and via heuristic `T1.X_id ↔ T2.id` matches. Each path has score.
2. **Question-token grounding.** Map question entities to columns via
   embedding similarity against `column_name + column_description` (already
   in chunker). Drop FK paths whose entity-mapped columns are not on the
   path.
3. **Re-prompt with candidate JOIN paths as hint.** "For tables {T1, T2, T3},
   the candidate JOIN paths are: (a) T1.X = T2.X via FK; (b) T1.Y = T3.Y +
   T3.Z = T2.Z indirect. Question 'X' suggests path (a). Use it unless the
   evidence forces (b)."

This is research-grade work. Memory `feedback_no_redraft_after_approval` +
the realistic +0.5–1pp ceiling argue against starting it without explicit
user mandate.

## Recommendation

**Don't build P3.F speculatively.** The headline 81.0% v11 + 67.34% corrected-gold
triplet is portfolio-ready. The marginal +0.5–1pp from a JOIN-path linker
costs days of work for a number that won't change the narrative.

If user wants past 81% chrome-free, the cheaper paths are:
1. **Wait for daily quotas to fully reset** (24h+) and re-run llama70b on
   the 21 unattempted qids — expected ≤1 rescue but $0 cost.
2. **Try `gemini-2.5-pro` (RPD ≥100, 5× higher than 2.5-flash)** via Google
   AI Studio. New provider on residue, ortogonal model family.
3. **OpenRouter paid $1 top-up** unlocks 1000 free-model requests/day —
   not paid model usage, just lifts free-tier cap. Could re-run nemotron and
   other free OpenRouter models with no daily cap.

If user wants past 81% with $1–3 budget: paid Anthropic API Sonnet sweep on
the 38 residue. Memory marks this deprecated, but it's the highest $/pp.

If user wants research-grade improvement: P3.F design above + custom corrective
self-consistency (CSC-SQL technique from `docs/bird_sota_research.md`). Multi-day
work, expected +2–4pp combined.