While recent Large Language Model (LLM)-based text-to-SQL systems achieve impressive performance on standard benchmarks, they struggle when user queries implicitly rely on domain-specific knowledge, such as business logic, data conventions, and analytical practices, that is neither captured by the schema nor explicitly stated in the natural language question. Historical SQL query logs offer a valuable source of such knowledge, yet existing benchmarks do not adequately support evaluation of history-driven approaches. To address this gap, we introduce BIRD-History, a benchmark consisting of 1,393 tasks across 11 databases, designed to evaluate text-to-SQL systems' ability to ground underspecified natural language questions using historical SQL scripts. Each task is annotated with ground-truth labels specifying which historical queries contain relevant knowledge and which SQL clauses encode it, enabling systematic evaluation of both retrieval effectiveness and knowledge utilization. Alongside the benchmark, we propose a plug-in retriever that extracts five types of external knowledge from historical SQL scripts, then retrieves and reranks relevant fragments for query generation. The retriever integrates seamlessly into existing few-shot text-to-SQL pipelines without requiring prompt modifications. Experiments demonstrate consistent improvements across four text-to-SQL systems, highlighting the value of leveraging historical query logs for handling underspecified queries. Dataset and code are open-sourced on https://github.com/zjuidg/BIRD-History.
Figures & tables
Figure 1: A text-to-SQL example from the BIRD dataset involving external knowledge.
Figure 2: The BIRD-History dataset construction pipeline. The process consists of two stages: (1) Automatic task generation, where a Task Generator parses historical SQL scripts into reusable components and creates new question-SQL pairs, a Knowledge Validator performs syntactic and semantic checks to ensure quality and proper knowledge reuse, and a Task Fixer iteratively refines candidates that fail validation; (2) Human validation and refinement, where domain experts cross-validate semantic consistency, task naturalness, and answer uniqueness to produce the final dataset.
Dataset
# Tasks
# Tok. / SQL
# Func. / SQL
# Subq. / SQL
JOIN Len. / SQL
Nest Depth / SQL
External Knowledge
Retrieval Annotations
WikiSQL
15,878
25.92
0.29
0
1
1
No
No
Spider
2,147
30.73
0.57
0.08
1.53
1.08
No
No
KaggleDBQA
185
27.37
0.73
0.03
1.21
1.02
Yes
No
SEDE
857
93.18
2.97
0.31
1.76
1.25
No
No
BIRD (dev)
1,534
53.38
1.74
0.10
1.92
1.09
Yes
No
Spider 2.0-lite
256
388.85
7.27
2.63
2.11
1.96
Yes
No
Table 1: Statistical comparison among BIRD-History and other text-to-SQL benchmarks, where Tok., Func., Subq., JOIN Len., and Nest Depth denote the average number of tokens, functions, subqueries, JOIN length, and nesting depth per SQL query, respectively.
Figure 3: The workflow of our baseline retriever. Historical SQL queries are decomposed into components with natural language descriptions and embeddings (top). For each natural language query, the retriever extracts keywords, retrieves candidates via semantic similarity at both query and component levels before integration into text-to-SQL pipelines (bottom).
Pipeline
Retriever
EX
DAIL-SQL
MQS
40.78
DAIL
38.77
Ours
51.33 ( ↑ 10.55)
OpenSearch-SQL
MQS
59.15
Ours
60.95 ( ↑ 1.80)
N-rep
MQS + Filter
54.49
Table 2: Execution accuracy on BIRD-History across different approaches.
Retriever
Precision
Recall
F1
MQS
21.11
48.84
29.48
DAIL
20.44
48.10
28.69
MQS + Filter
25.09
57.18
34.88
MQS + QS
24.38
52.47
33.29
Ours
35.53
83.07
49.77
Table 3: Retrieval results on BIRD-History across different retriever in existing Text-to-SQL pipelines.
Type
Precision
Recall
F1
Condition
36.70
76.67
49.64
Output
33.62
84.26
48.06
Dimension
30.24
79.19
43.77
Relation
25.75
78.09
38.73
Calculation
23.33
76.44
35.75
Table 4: Retrieval results for each knowledge type using OpenSearch-SQL pipeline with our retriever.
Max shot
Avg shot
EX
Precision
Recall
F1
1
1.000
58.36
57.95
42.78
49.23
2
1.999
59.30
48.37
62.13
54.39
3
2.967
59.58
42.55
73.14
53.80
4
3.744
61.31
38.75
79.33
52.07
5
4.229
60.01
36.68
81.60
50.61
Dynamic
4.572
60.95
35.53
83.07
49.77
Table 5: Execution accuracy and retrieval results of our proposed retriever with different max shots on the OpenSearch-SQL pipeline.
Similarity
EX
Precision
Recall
F1
0.5
58.79
35.58
82.52
49.72
0.6
60.73
35.78
81.33
49.70
0.7
60.66
35.17
74.42
47.77
0.8
59.15
34.19
54.94
42.15
0.9
56.57
33.48
32.68
33.07
Table 6: Execution accuracy and retrieval results of our retriever with different similarity thresholds on the OpenSearch-SQL pipeline.
Appendix figures & tables9 assets
Supplementary material from the paper’s appendix.
Appendix
Figure 4: A task example in BIRD-History.
Type
SQL Clause
Example SQL Components
Description
Calculation
SELECT , ORDER BY
CAST ( COUNT ( CASE WHEN T . element = ’cl’ THEN T . atom_id ELSE NULL END ) AS REAL ) * 100 / COUNT ( T . atom_id )
Chlorine atom percentage among all atoms in molecules
ORDER BY SUM ( T1 . home_team_goal + T1 . away_team_goal )
Total goals across all matches in a football team
Condition
WHERE , HAVING
SUBSTRING ( T1 . event_date ,1,4)= ’2019’
Filter the events in the year 2019
molecule . label = ’-’
Filter non-carcinogenic molecules
Relation
FROM , JOIN
FROM atom T1 INNER JOIN molecule T2 ON T1 . molecule_id = T2 . molecule_id
Should join “atom” and “molecule” tables to obtain labels for atoms
Dimension
GROUP BY
GROUP BY sname , charter_funding_type
Should consider both student and funding when aggregating average math scores
Appendix
Table 7: Categorization of external knowledge in SQL queries by grammar. These five types cover fundamental SQL clauses, and complex structures such as subqueries or common table expressions (CTEs) can be recursively parsed to extract the same knowledge types.
Knowledge Items
1
2
3
4
5
6
7
8
9
10
11
# Tasks
20
95
156
213
262
253
192
123
53
17
9
Appendix
Table 8: Distribution of knowledge item count per task.
Knowledge Types
1
2
3
4
5
6
# Tasks
100
320
568
274
127
4
Appendix
Table 9: Distribution of distinct knowledge type count per task.
Type
Total Items
Proportion (%)
Avg Items per Task
Relation
1,318
16.6
0.946
Condition
4,002
50.3
2.873
Dimension
244
3.1
0.175
Calculation
1,004
12.6
0.721
Output
1,354
17.0
0.972
Full SQL
29
0.4
0.021
Appendix
Table 10: Total knowledge items, proportions, and average knowledge items per task for each knowledge type.
Method
EX on BIRD-History
EX on BIRD Dev
Finetuned Models
XiYanSQL-32B
22.97
67.01
OmniSQL-32B
19.02
69.23
CscSQL-7B
26.49
69.19
SLM-SQL-1.5B
21.75
67.08
SQL-R1-14B
28.64
67.14
Appendix
Table 11: Performance of two additional categories of text-to-SQL pipelines on BIRD-History and BIRD development set. Here, “XiYanSQL-32B” refers to XiYanSQL-QwenCoder-32B-2504 , “CscSQL-7B” refers to CscSQL-Qwen2.5-Coder-7B-Instruct .
Model
BIRD-History
BIRD Dev
Qwen3
26.42
44.00
DeepSeek-V3.2
21.11
35.72
Llama3.3-70B-Instruct
16.65
35.07
GPT-5.3-Codex
28.93
41.26
Gemini-3-Flash
33.24
51.56
Appendix
Table 12: Execution accuracy comparison of different foundation models on BIRD-History and BIRD development benchmarks.
Level
EX
Precision
Recall
F1
Q+C
60.95
35.53
83.07
49.77
Q
59.44
34.61
69.45
46.19
C
60.73
33.91
79.29
47.50
None
54.27
0
0
0
Gold
67.34
100
100
100
Appendix
Table 13: Ablation studies across different retrieval levels on the best-performing few-shot pipeline, OpenSearch-SQL. “Q” denotes query-level retrieval, “C” denotes component-level retrieval, and “Q+C” refers to our proposed retriever that combines both levels. “None” indicates no retriever, while “Gold” represents oracle retrieval results.
Metric
Value per SQL
Offline Indexing
SQL Parsing Time
2.137 ms
Description Generation Time
5181 ms
Embedding Time
3.048 ms
Storage
20.62 KB
Online Retrieval
Appendix
Table 14: Efficiency analysis of our proposed retriever. Since our retriever consists of multiple stages, we report the efficiency of each step separately. The offline indexing phase is performed only once during knowledge base construction and is not repeated for each text-to-SQL task, so we report it separately from the online retrieval phase.