TS-SQL: Test-driven Self-refinement for Text-to-SQL

Wenbo Xu, Haifeng Zhu, Liang Yan, Chuanyi Liu, Peiyi Han, Shaoming Duan, Jeff Z. Pan2025 · DOI

TL;DR

Large Language Models (LLMs) struggle with semantic errors in Text-to-SQL generation due to their inherent biases. This paper introduces TS-SQL, a novel framework that leverages automatically generated test cases (data and code) to provide precise feedback, enabling LLMs to self-refine and correct these semantic errors, significantly improving SQL accuracy.

What Problem Does This Paper Address?

Text-to-SQL, the task of converting natural language questions into executable SQL queries, is crucial for data analysis. While LLMs have shown promise, they often produce SQL queries with semantic errors, such as missing conditions or misinterpretations of requirements. These errors stem from LLMs' susceptibility to "hallucination-induced biases" in understanding natural language, making it difficult for them to self-correct. Existing self-refinement methods rely on the LLM's semantic understanding, which is precisely where the problem lies. The authors argue that a more robust approach is needed, one that doesn't solely depend on the LLM's internal understanding but uses external validation.

Methods

TS-SQL tackles this problem through a two-phase approach:

  1. Test Case Generation Phase: This phase employs a collaborative framework of three LLM-based agents to automatically generate high-quality test cases.
  2. Data Generation Agent: Creates synthetic test data relevant to the question and database schema. This involves selecting relevant columns and content values from the database.
  3. Code Generation Agent: Generates Python code (using libraries like Pandas) to process the generated test data and produce expected results.
  4. Code Inspection Agent: Validates the generated test code for correctness and maintainability, ensuring it can be executed and understood. This phase involves iterative generation and inspection.
  5. Feedback-driven SQL Generation Phase: This phase uses the generated test cases to refine the LLM's SQL output.
  6. The LLM first generates an initial SQL query.
  7. This query is then executed against the generated test data, and its output is compared with the expected output from the test code.
  8. If discrepancies arise, the test case (including the test code, its execution result, and the generated SQL's execution result) is fed back to the LLM as feedback.
  9. The LLM then revises its SQL query based on this feedback, aiming to align it with the test case's logic and expected outcome. This process is repeated until the SQL passes the test case or a maximum number of revisions is reached.

Datasets

The paper evaluates TS-SQL on two prominent Text-to-SQL benchmarks:

  • Spider: A large-scale, complex, and cross-domain dataset with 10,181 questions and 5,693 SQL queries across 200 databases.
  • BIRD: A newer, cross-domain dataset designed to examine the impact of extensive database contents on Text-to-SQL. It features 12,751 Text-to-SQL pairs and 95 databases, introducing challenges like noisy data and external knowledge grounding.

The authors also created a modified Spider-dev set by correcting identified gold SQL errors to provide a more rigorous benchmark for evaluating SQL correctness.

Key Insights and Ideas

  • Test Cases as Semantic Validators: The core idea is that generated test cases, even if not perfectly reflecting the original question's nuances, can act as crucial external validators for SQL semantic correctness. This bypasses the LLM's inherent semantic understanding limitations.
  • Decomposition of Test Case Generation: Breaking down test case generation into distinct data and code generation steps, managed by specialized LLM agents, allows for more robust and high-quality test cases.
  • Iterative Refinement with Test Feedback: The feedback loop, where SQL execution results are compared against test case expectations, provides concrete, actionable information for the LLM to correct its semantic errors.
  • Benchmarking Rigor: The study highlights that even established benchmarks like Spider can contain incorrect gold SQL queries, underscoring the need for robust validation methods like TS-SQL.

Key Results

  • Spider Benchmark: TS-SQL identified 131 (12.67%) incorrect gold SQL queries in the Spider development set, demonstrating its error-checking capability. On the modified Spider-dev set, TS-SQL achieved an Execution Accuracy (EX) of 88.88%, outperforming baselines like DIN-SQL and MAC-SQL.
  • BIRD Benchmark: TS-SQL achieved an EX score of 70.93% on the BIRD development set and 69.20% on the BIRD test set, competitive with state-of-the-art methods.
  • Self-Refinement Improvement: TS-SQL improved the performance of other Text-to-SQL methods by at least 6% compared to existing self-refinement techniques.
  • Agent Contributions: Ablation studies showed that each agent (data generation, code generation, code inspection) contributes significantly to TS-SQL's effectiveness, with the code generation agent having the largest impact (41.5% decrease in EX when removed).
  • Token Efficiency: TS-SQL demonstrated higher efficiency in token usage, with an average of 2,369 tokens per query, lower than comparable methods like E-SQL (3,237) and CHESS (2,672).

Limitations and Future Work

  • LLM Performance Ceiling: The accuracy of TS-SQL's generated test cases is inherently limited by the performance of the LLMs used for generation. This also caps the overall performance gains achievable by TS-SQL.
  • Gap Between Generated and Manual Test Cases: Despite improvements, a significant gap remains between LLM-generated and human-written test cases.
  • Performance Issues Not Considered: The current TS-SQL method does not consider performance aspects like execution time for the generated SQL.

Future work suggested includes:

  • Exploring different LLMs for test case generation and selection.
  • Investigating methods to further reduce the gap between generated and manual test cases.
  • Incorporating performance assessment into the test case generation process.
  • Developing more sophisticated voting mechanisms for test case selection.

Summary updated

BibTeX
@misc{xu2025ts,
  author = {Wenbo Xu and Haifeng Zhu and Liang Yan and Chuanyi Liu and Peiyi Han and Shaoming Duan and Jeff Z. Pan},
  title = {{TS-SQL: Test-driven Self-refinement for Text-to-SQL}},
  year = {2025},
  doi = {10.18653/v1/2025.findings-emnlp.156},
}
Figure