t2sql-schema-retrieval — When Does Schema Retrieval Help Text-to-SQL?

Code and reproducible artifacts for the paper "When Does Schema Retrieval Help Text-to-SQL? A Controlled Study of Lexical, Dense, and Graph Retrieval with Error-Transduction Analysis" (paper/main_retrieval.tex), targeting Knowledge-Based Systems.

This study adds a schema-retrieval layer to a fixed, aligned Text-to-SQL harness and compares five table retrievers under one execution-accuracy (EX) metric, three language models, and English and Chinese benchmarks. The harness (execution sandbox, aligned test-suite EX, cost ledger) is shared with the sibling paper "When Does Execution Feedback Help Text-to-SQL?" (t2sql-agent); this repository is self-contained and adds the retrieval layer on top.

Headline findings

• Controlled comparison (RQ1): on English schemas, lexical (BM25) ≈ dense (BGE-M3) ≈ full-schema ≫ graph ≫ random, stable across DeepSeek / Qwen / MiniMax; graph expansion is diluted at small budgets.
• Retrieval-error transduction (RQ2): a 10-point drop in table recall costs ≈6.6 EX points (n=1500); the full-schema arm has zero retrieval-induced misses (a validity control).
• Cost-accuracy crossover (RQ3): near ten tables — above it retrieval saves ≈39% of prompt tokens at no EX cost; below it retrieval is unnecessary.
• Multilingual: on Chinese schemas dense table recall is 2.2× BM25 (parity in English) — the best retriever is language-dependent; low-resource languages require dense retrieval.

Gold schema-linking is derived automatically from gold SQL (no human annotation); every reported number is produced by a script from run artifacts.

Layout

src/t2sql/retrieval.py       # 5 retrievers: bm25 / dense (BGE-M3) / graph / full / random
src/t2sql/schema_subset.py   # reconstruct a valid schema from a retrieved table subset
src/t2sql/gold_linking.py    # automatic gold table/column parsing from gold SQL (sqlglot)
src/t2sql/run_eval.py        # eval CLI with --retriever / --retrieve-budget / bucketing
scripts/analyze_retrieval.py # bucket metrics, transmission attribution, recall-EX elasticity
scripts/plot_retrieval_figures.py  # figures figB1–figB6 from run artifacts (no hand numbers)
scripts/reproduce_retrieval.sh     # one-command reproduction of the matrix + analysis + figures
paper/main_retrieval.tex     # this paper (Knowledge-Based Systems)
paper/main.tex               # sibling paper (execution feedback; IPM submission)

Reproduce

1. Install.

pip install -e '.[dev,figures,retrieval]'   # retrieval extra: rank_bm25, jieba, sqlglot, sentence-transformers

2. Data. Place the public benchmarks under data/ in the common Spider layout (dev.json, tables.json, database/<db>/<db>.sqlite): data/spider, data/bird, data/cspider, data/dusql. See scripts/download_*.py and scripts/build_*.py.

3. Dense weights (optional, for the dense arm). Provide a local BGE-M3 checkpoint and point --dense-model at it; loading is offline (local_files_only). No weights are downloaded at run time.

4. API keys. Copy .env.example to .env and set the OpenAI-compatible endpoint/model/key (T2SQL_BASE_URL / T2SQL_MODEL / T2SQL_API_KEY); .env is git-ignored.

5. Run.

bash scripts/reproduce_retrieval.sh        # matrix + budget sweep + Chinese + analysis + figures

Sanity checks that need no API key:

python -m t2sql.run_eval --dataset spider-dev --model gold-replay --retriever none   # frozen path, EX=1.00
pytest -q && ruff check .

Cost & reproducibility

The full experiment matrix in the paper cost ≈¥2.5 in API calls (three low-cost models). All retrievers are deterministic and offline; the accuracy metric uses the Spider test-suite comparison semantics with a bounded permutation guard.

Citation

Please cite the paper (see paper/main_retrieval.tex); the archived artifact DOI (Zenodo) will be added on release.

License

MIT (see LICENSE); the vendored test-suite evaluator retains its original Apache-2.0 license.