PaperlayerView exact revision on arXiv

Source-linked AI summary

BIRD-History: A Benchmark for History-Driven Text-to-SQL with Fine-Grained Knowledge Annotations

Yunfan Zhou, Qiming Shi, Yizhou Yang, Di Weng, Yingcai Wu

arXiv:2608.29345v1cs.AIcs.CL

TL;DR

Text-to-SQL systems struggle with domain-specific knowledge that schemas and questions omit, while existing benchmarks do not adequately evaluate historical-query grounding. BIRD-History supplies an annotated benchmark and a plug-in retriever that extracts and reranks fine-grained SQL knowledge, with consistent improvements across four systems. Its controlled use of successful fixed-set queries enables systematic evaluation but does not fully represent noisy, evolving real-world logs.

  • Problem

    Text-to-SQL systems struggle with domain-specific knowledge absent from schemas and question wording, while existing benchmarks inadequately evaluate grounding in historical SQL.

  • Method

    BIRD-History provides 1,393 annotated tasks across 11 databases, alongside a plug-in retriever that extracts five knowledge types and retrieves and reranks relevant fragments.

  • Results

    Experiments demonstrate consistent improvements across four existing text-to-SQL systems, including a +10.55% gain for DAIL-SQL over MQS.

  • Takeaways & Limitations

    Historical query logs provide useful external knowledge for handling underspecified text-to-SQL queries within existing few-shot pipelines.

  • Takeaways & Limitations

    The benchmark retains only final successful queries from a fixed development set, unlike noisy, continuously evolving real-world session logs.

Abstract

from arXiv · show

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.

1 Introduction

BIRD-History addresses the difficulty of translating underspecified questions that depend on domain-specific knowledge absent from schemas and wording. It introduces an annotated benchmark and plug-in retriever for grounding such questions in historical SQL.

  • Motivation: Schema linking often misses business logic, data conventions, and analytical practices implicit in underspecified questions.These forms of knowledge may be neither represented in the schema nor explicitly stated in natural language.
  • Motivation: Historical SQL queries encode domain-specific information such as common join paths and column semantics that schemas alone do not reveal.
  • Benchmark: BIRD-History contains 1,393 tasks across 11 databases with labels identifying relevant historical queries and the SQL clauses carrying their knowledge.The annotations support separate evaluation of retrieval effectiveness and knowledge utilization.
  • Retriever: The proposed retriever extracts five types of fine-grained knowledge from historical SQL queries for text-to-SQL generation.
  • Results: Experiments show consistent improvements across four existing text-to-SQL systems, and the retriever integrates without prompt-template modifications.

2 Related Work

Prior text-to-SQL benchmarks mainly provide natural-language documentation or syntactically similar examples, leaving dataset-specific conventions in historical query logs underused. BIRD-History evaluates systems that retrieve and apply such historical knowledge.

  • Text-to-SQL Approaches: Earlier text-to-SQL methods use structured decoding, schema-aware encoding, in-context learning, task decomposition, and schema-linking preprocessing.
  • Retrieval Methods: OpenSearch-SQL combines question similarity with SQL skeleton matching for retrieval.
  • Text-to-SQL Datasets: Existing benchmarks add external knowledge or robustness settings, while newer datasets address SQL translation and issue resolution.
  • Research Gap: Existing benchmarks generally provide natural-language documentation, whereas real-world query logs often lack annotations linking queries to relevant knowledge.
  • Research Gap: BIRD-History evaluates use of historical query logs and supplies a plug-in baseline retriever for existing few-shot pipelines.

3 BIRD-History Dataset

BIRD-History formalizes history-grounded text-to-SQL as retrieval followed by generation, then constructs controlled tasks from successful BIRD development queries. Automatic validation, fixing, and expert review produce a curated benchmark.

  • Task Definition: Given a question Q, database D, and historical queries H, the system must generate an executable and correct SQL query Y.
  • Task Definition: A retrieval step selects a relevant subset H′ ⊂ H using the question and schema before SQL generation, reducing irrelevant context.
  • Dataset Construction: BIRD-History treats successful BIRD development-set SQL scripts as historical queries and generates tasks on the same databases.The development set averages 139 historical queries per database and spans large, cross-domain schemas.
  • Automatic Task Generation: Automatic generation parses SQL into clause-level components, combines related components, and synthesizes new logic for question-SQL pairs.
  • Automatic Task Generation: Validation checks executability, non-empty results, nontrivial outputs, and faithful knowledge reuse; a fixer revises failed candidates.
  • Human Validation and Refinement: Human annotators verify semantic consistency and naturalness, remove non-unique answers, and achieve Gwet’s AC1 = 0.82.
  • Dataset Statistics: 1,393 evaluation tasks reuse an average of 5.3 historical fragments, and generated SQL averages 181.70 tokens versus 53.38 for BIRD queries.

4 Baseline Approach

The baseline retriever decomposes historical SQL into categorized knowledge components, represents them textually and geometrically, and retrieves and reranks relevant query or component candidates for integration.

  • Overview: The retriever is designed as a plug-in for existing text-to-SQL pipelines.
  • Knowledge Categorization: Historical SQL knowledge is categorized into calculation, condition, relation, dimension, and output types.
  • Knowledge Base Construction: AST parsing extracts expressions or complete clauses, which an LLM converts into query-level summaries and component-level explanations with dense embeddings.
  • Retrieval and Integration: For each natural-language question, the retriever searches query-level and component-level embeddings, merges source queries, then uses an LLM to filter and rerank candidates.

5 Experiments

Experiments evaluate BIRD-History through execution accuracy, retrieval quality, ablations, and knowledge-type analyses across existing text-to-SQL pipelines. The results show consistent gains from the proposed retriever, while noise, false positives, and retrieval errors remain important constraints.

  • Experimental Setup: BIRD-History uniquely evaluates both historical-query retrieval and SQL generation rather than providing gold context directly.The benchmark uses historical queries as retrieval targets, enabling joint assessment of retrieval quality and generation accuracy.
  • Experimental Setup: Four representative few-shot systems—DAIL-SQL, OpenSearch-SQL, N-rep, and CodeS—serve as the main evaluation pipelines.Their retrieval variants differ in masked-question similarity, SQL skeleton filtering, quality filtering, and combined similarity strategies.
  • Main Results: +10.55% execution accuracy is the largest reported gain, achieved by integrating the proposed retriever into DAIL-SQL; CodeS (7B) improves by 6.03%.The retriever achieves the highest F1 and recall among compared retrievers, but its precision is 35.53%, leaving irrelevant knowledge as a constraint.
  • Ablation Studies: Disabling retrieval causes a substantial performance drop, while gold historical-knowledge annotations produce significant gains.These ablations jointly support the necessity of accurate historical knowledge for BIRD-History.
  • Retrieval Analysis: Retrieval quality varies by knowledge type: output knowledge reaches 84.26% recall, whereas relation and calculation knowledge each have precision below 30%.Condition knowledge achieves the highest precision and F1 because filtering predicates are typically explicit in questions.

6 Conclusion

BIRD-History benchmarks history-aware text-to-SQL generation and validates a plug-in retriever that improves standard pipelines, aiming to bridge benchmark evaluation with real-world data querying.

  • BIRD-History benchmarks history-aware text-to-SQL generation and validates its utility through a plug-in retriever.
  • The benchmark targets the gap between benchmark evaluation and real-world data querying scenarios.

Limitations

The benchmark uses a controlled history setting containing only final successful SQL queries, while the retriever can introduce irrelevant knowledge because of modest precision.

  • The benchmark retains only final, successful SQL queries from past sessions rather than failed attempts, syntax errors, or iterative refinements.This controlled design supports systematic evaluation without malformed-query confounds but may not reflect evolving real-world logs.
  • The benchmark may not capture how expanding retrieval pools and shifting knowledge patterns affect retrieval and generation over time.The authors suggest incorporating more realistic, noisy query logs in future work.
  • The retriever’s modest precision can introduce irrelevant knowledge alongside useful historical patterns, contributing to moderate execution accuracy.

Ethical Consideration

BIRD-History documents task structure and fine-grained historical knowledge annotations while categorizing reusable SQL knowledge across fundamental query constructs, including nested structures.

  • Each task includes question_id, db_id, question, SQL, and history_knowledge fields.The first four fields follow the BIRD dataset convention, while history_knowledge supports retrieval evaluation.
  • The history_knowledge field maps masked SQL knowledge skeletons to historical question IDs containing those fragments.This links each external knowledge item required for a target query to its historical sources.
  • Retrieval metrics count covered knowledge items rather than retrieved scripts because scripts may contain multiple relevant fragments.The example achieves 3/4 knowledge-item recall from two retrieved scripts.
  • External knowledge is categorized as calculation, condition, relation, dimension, and output based on SQL grammar positions.The parser recursively applies these categories to nested subqueries, CTEs, and window functions.

A.3 Dataset Analysis

BIRD-History is substantially more complex than standard BIRD evaluation because tasks compose multiple historical knowledge items and types, while systems perform markedly worse without effective retrieval.

  • Benchmark comparison: BIRD-History averages 2.57 joined tables, 2.06 nesting layers, and 5.3 knowledge items per task, with complexity arising from knowledge composition.It is the only compared benchmark providing knowledge-retrieval annotations.
  • Knowledge item count: 1,278 tasks (91.7%) require at least three knowledge items, and the distribution peaks at five items with 262 tasks (18.8%).Tasks requiring 8–11 items form a challenging long tail involving synthesis across numerous historical queries.
  • Knowledge type diversity: 973 tasks (69.8%) require at least three distinct knowledge types, with the distribution peaking at three types in 568 tasks (40.8%).The types include relation, condition, dimension, calculation, output, and full SQL.
  • Knowledge type distribution: Condition knowledge dominates with 4,002 items (50.3%), while full SQL accounts for only 29 items (0.4%).Most tasks therefore require composing specific patterns rather than directly reusing complete historical queries.
  • Performance comparison: Finetuned models score 19.02%–28.64% on BIRD-History versus 67.01%–69.23% on BIRD Dev, while foundation models also show substantial gaps.Llama3.3-70B-Instruct reaches 16.65% on BIRD-History versus 35.07% on BIRD Dev.
  • Retrieval analysis: Combining query-level and component-level retrieval performs best, disabling retrieval causes a significant drop, and gold annotations improve performance.These results identify retrieval and annotation quality as important factors for solving BIRD-History tasks.

B.3 Efficiency Analysis

The retriever separates one-time indexing costs from per-query retrieval costs. Embedding retrieval is fast, while LLM-based online stages dominate latency, and the reported setup is not throughput-optimized.

  • Efficiency Analysis: The efficiency analysis reports offline indexing separately from online retrieval because indexing is not repeated for each task.Table 14 analyzes the efficiency of the retriever’s multiple stages under the stated experimental environment.
  • Offline Indexing: Offline indexing is performed once, so LLM-based description generation does not affect inference latency.The indexing stage includes SQL parsing, description generation, and embedding computation; description generation dominates its cost.
  • Online Retrieval: About 54 ms is required for embedding-based retrieval, while LLM-based filtering and reranking account for most online latency.Online retrieval also includes keyword extraction.
  • Storage Cost: Each SQL query requires approximately 20.62 KB to store structured components, descriptions, and embeddings.The resulting storage overhead is described as moderate.
  • Experimental Setting: The reported latency uses serial qwen-plus-2025-07-28 API calls rather than a throughput-optimized local deployment.Batching or local serving with frameworks such as vLLM could accelerate offline indexing in practical deployments.

B.4 Case Studies

The case studies show that retrieval recall alone does not ensure correct SQL generation. High precision can expose non-obvious join knowledge, whereas competing retrieved patterns can lead the LLM to select an incorrect result semantics.

  • Overview: 100% recall produced different outcomes across two representative cases because retrieval precision and LLM behavior differed.The cases were designed to examine whether historical knowledge helps and how retrieval noise affects performance.
  • Success Case: The success case asks for a secretary’s identity and hometown while requiring the highest total club income.Its ground-truth query includes a zip-code join for city, county, and state retrieval.
  • Success Case: 90.0% precision, 100% recall, and 94.7% F1 accompanied successful retrieval of the non-obvious member.zip → zip_code.zip_code relationship.The relationship supported a hometown lookup that could not be inferred from column names alone.
  • Failure Case: The failure case asks for male Marvel superheroes with maximum strength, ordered by height from tallest to shortest.The retrieved oversimplified pattern instead returns only one maximum-strength result.
  • Failure Case: 28.6% precision, 100% recall, and 44.4% F1 accompanied a failure in which the LLM selected an oversimplified top-1 pattern.The selected ORDER BY attribute_value DESC LIMIT 1 returns one result instead of all heroes tied at the maximum value.
  • Failure Case: The failure case contrasts a maximum-value subquery with an ORDER BY ... LIMIT 1 pattern that changes tie-handling semantics.Both patterns appeared among retrieved examples, but only the subquery returns all heroes tied at the maximum strength value.

C Prompt Templates

The appendix describes prompt templates for generating, validating, fixing, extracting, and reranking SQL knowledge. These templates constrain reuse to preserve executable, meaningful domain logic while structuring knowledge for retrieval.

  • C.1 Prompt Templates for Dataset Construction: The dataset-construction pipeline contains task generator, knowledge validator, and task fixer components.The appendix presents prompt templates for each component.
  • C.1.1 Task Generator: The task generator selects 2–6 original SQLs, reuses interesting components, tweaks values, and creates harder matching descriptions and queries.It targets new queries that reuse underlying knowledge or logic rather than merely combining tokens.
  • C.1.1 Task Generator: Generation rules prohibit introducing extra joined tables, arbitrary JOIN-mode changes, and chains of four or more consecutive JOIN clauses.Allowed constructions include subsets of original join structures.
  • C.1.1 Task Generator: The generator prompt supplies schemas, original SQL scripts, natural-language explanations, evidence, and structured JSON output fields.The output includes used original SQL indices, task description, evidence, and SQL.
  • C.1.2 Knowledge Validator: Validation prompts combine original SQL descriptions and scripts with candidate task details, execution feedback, automatic diagnostics, and checklist-based decisions.Diagnostics include execution success, result validity, SQL length, and join-chain constraints.
  • C.1.2 Knowledge Validator: The validator checks execution, non-empty and reasonable results, purposeful clauses, join limits, function consistency, aggregation semantics, and meaningful knowledge reuse.It also preserves CASE WHEN intent and avoids rewriting COUNT(DISTINCT ...) into COUNT(...).
  • C.1.3 Task Fixer: The task fixer revises descriptions, evidence, and SQL to correct execution errors, empty or unreasonable results, redundancy, and inappropriate difficulty.It preserves prior steps, schemas, join limits, and JOIN modes unless changes are supported by the originals.
  • C.2 Prompt Templates for Our Baseline Approach: Knowledge-extraction templates organize historical SQL into relation, calculation, condition, dimension, and output concepts with searchable names, aliases, and descriptions.The retrieval pipeline comprises knowledge extraction, keyword extraction, and filter-and-rerank stages.
Loading 2608.29345v1…