Source-linked AI summary
CHASE-SQL: Multi-Path Reasoning and Preference Optimized Candidate Selection in Text-to-SQL
Mohammadreza Pourreza, Hailong Li, Ruoxi Sun, Yeounoh Chung, Shayan Talaei, Gaurav Tarlok Kakkar, Yu Gan, Amin Saberi, Fatma Ozcan, Sercan O. Arik
TL;DR
Text-to-SQL systems need reliable ways to generate and select SQL when single-prompt diversity and self-consistency are insufficient. CHASE-SQL uses multiple reasoning-based generators, synthetic demonstrations, query fixing, and a pairwise fine-tuned selection agent. It achieves 73.01% execution accuracy on BIRD development and 73.0% on the holdout test set, setting state-of-the-art performance in the reported evaluation.
Problem
Single prompt designs may not fully use LLM Text-to-SQL knowledge, and self-consistency can select incorrect answers despite agreement.
Method
CHASE-SQL generates diverse candidates with divide-and-conquer, query-plan, and online synthetic-example strategies, then uses pairwise selection and query fixing.
Results
73.01% development-set and 73.0% holdout-test execution accuracy on BIRD set a new state-of-the-art performance.
Takeaways & Limitations
CHASE-SQL demonstrates the reported effectiveness of test-time computation for generating diverse SQL queries and selecting accurate responses.
Abstract
from arXiv · showhide
In tackling the challenges of large language model (LLM) performance for Text-to-SQL tasks, we introduce CHASE-SQL, a new framework that employs innovative strategies, using test-time compute in multi-agent modeling to improve candidate generation and selection. CHASE-SQL leverages LLMs' intrinsic knowledge to generate diverse and high-quality SQL candidates using different LLM generators with: (1) a divide-and-conquer method that decomposes complex queries into manageable sub-queries in a single LLM call; (2) chain-of-thought reasoning based on query execution plans, reflecting the steps a database engine takes during execution; and (3) a unique instance-aware synthetic example generation technique, which offers specific few-shot demonstrations tailored to test questions.To identify the best candidate, a selection agent is employed to rank the candidates through pairwise comparisons with a fine-tuned binary-candidates selection LLM. This selection approach has been demonstrated to be more robust over alternatives. The proposed generators-selector framework not only enhances the quality and diversity of SQL queries but also outperforms previous methods. Overall, our proposed CHASE-SQL achieves the state-of-the-art execution accuracy of 73.0% and 73.01% on the test set and development set of the notable BIRD Text-to-SQL dataset benchmark, rendering CHASE-SQL the top submission of the leaderboard (at the time of paper submission).
1 Introduction
CHASE-SQL addresses limitations of single-prompt generation and self-consistency in Text-to-SQL by using diverse candidate generators and selection. It reports 73.01% development-set and 73.0% test-set execution accuracy on BIRD.
- Text-to-SQL converts natural-language questions into executable SQL, enabling database use without requiring users to know SQL.
- Self-consistency can select incorrect answers because the most consistent responses are not always correct.
- CHASE-SQL uses test-time computation in an agentic framework to generate diverse, high-quality candidate responses.
- Its generators use divide-and-conquer decomposition, query-plan reasoning, and online synthetic examples to guide candidate generation.
- 73.01% development-set and 73.0% test-set execution accuracy were achieved on the BIRD benchmark.
2 Related Work
Text-to-SQL research progressed from neural sequence-to-sequence and tabular language models toward LLM-based methods. More recent work has focused on optimizing prompts and related LLM techniques.
- Early Text-to-SQL systems used GNNs, RNNs, LSTMs, and pretrained transformer encoders with slot-filling or autoregressive decoding.
- Tabular language models such as TaBERT, TaPas, and Grappa encoded tables together with textual data.
- LLMs have largely replaced earlier approaches because of their superior Text-to-SQL performance.
- Initial LLM-based efforts concentrated on prompt-design optimization.
3 Methods
CHASE-SQL combines value retrieval, multi-path candidate generation, query fixing, and pairwise selection. Its generators create varied SQL candidates, while a trained selector chooses among them.
- Overall Framework: The framework retrieves relevant database values before supplying values, metadata, and schema to an LLM for candidate generation.
- Candidate Generation and Selection: CHASE-SQL prioritizes candidate diversity because consistency among execution results can select sub-optimal answers.
- Multi-path Candidate Generation: Chain-of-thought generation uses divide-and-conquer decomposition, query-execution-plan reasoning, and online synthetic examples.
- Divide and Conquer CoT: Divide-and-conquer CoT decomposes a question into sub-questions, generates partial SQL queries, and assembles a final query.
- Query Plan CoT: Query Plan CoT converts database EXPLAIN output into human-readable execution steps to guide SQL construction.
- Online Synthetic Example Generation: Online synthetic examples are generated per question to tailor few-shot demonstrations while reducing misleading example reuse.
- Query Fixer: An LLM-based fixer iteratively corrects syntactically incorrect queries using syntax errors or empty result sets as feedback.
- Selection Agent: The selection agent compares two candidates at a time with a classification objective, rather than ranking larger groups simultaneously.
4 Experiments
CHASE-SQL is evaluated on BIRD and Spider through benchmark comparisons, ablations, generator analyses, and selection-agent studies. Results show state-of-the-art BIRD performance, strong Spider generalization, complementary generators, and advantages for trained selection over consistency-based picking.
- BIRD results: 73.01% accuracy on BIRD development and 73.0% on the holdout test set establish CHASE-SQL as state of the art.The Gemini 1.5 pro configuration outperforms previous works on both reported BIRD splits.
- Spider results: 87.6% execution accuracy on Spider test demonstrates generalization without target-distribution prompt changes or a newly trained selection model.This places CHASE-SQL second among methods specifically trained or prompt-optimized for Spider.
- Ablations: Ablations evaluate candidate generators against the original BIRD prompt with zero-shot chain-of-thought and assess the contribution of the query fixer and other framework components.The reported analyses include single-candidate generation, 21-candidate selection, and binary selection-model comparisons.
- Candidate generation analysis: Upper-bound performance increases with candidate count, but gains plateau earlier for moderate and challenging BIRD questions than for simple questions.The analysis combines candidates from all three generation methods across human-assigned difficulty levels.
- Generator analysis: The three generators provide complementary correct answers, with divide-and-conquer strongest on challenging questions and query-plan reasoning strongest on moderately difficult queries.Online synthetic examples increase diversity and produce more correct answers overall across databases.
- Selection analysis: Selection-agent performance exceeds self-consistency by roughly 6% on candidate pools sampled at temperatures 0.5 and 1.8.Higher temperature raises the upper bound but lowers the lower bound, while the trained selector is less temperature-sensitive.
5 Conclusion
CHASE-SQL is an agentic framework that uses test-time computation to generate diverse, high-quality SQL candidates and select the most accurate response. Its generator-selection design addresses query diversity and selection optimization in complex Text-to-SQL tasks.
- CHASE-SQL uses test-time compute to generate diverse, high-quality SQL queries and select the correct one.
- The framework combines multiple chain-of-thought prompting methods with online synthetic example generation.
- Its query-selection mechanism scores candidates through pairwise comparisons.
- CHASE-SQL sets a state-of-the-art result on the public Text-to-SQL leaderboard at submission time.
- The framework targets query diversity and selection optimization in complex real-world Text-to-SQL reasoning.
A.1 Related works
Self-consistency generates multiple SQL responses, groups them by execution results, and selects a query from the largest group. This provides a consistency-based approach for Text-to-SQL answer selection.
- Self-consistency samples multiple responses from an LLM and selects the most consistent answer by majority vote.
- In Text-to-SQL, self-consistency generates multiple SQL queries for one question.
- The generated queries are grouped according to their execution results.
- A query is selected from the group with the largest membership.
A.2 Models
The experiments use Gemini and Claude models selected for their ability to handle long contexts, which is relevant to Text-to-SQL over large databases. Different models are reported for generation-related components and query picking.
- Experiments use models from the Gemini and Claude families.
- These models are described as capable of handling long contextual information needed for queries over large databases.
- Gemini 1.5 Pro and Claude-3.5-Sonnet are used for candidate generation and several supporting components.
- The query-picking model is trained using Gemini 1.5 Flash.
A.3 Performance Based On Database
This section analyzes candidate-generator performance across databases in the BIRD development set. Figure 4 reports the number of correct queries achieved by each method for different databases.
- The analysis evaluates each candidate-generator method on BIRD development-set databases.
- Figure 4 compares methods across different BIRD development-set databases.
- The figure uses the number of correct queries as its comparison measure.
A.4 Error Analysis
The error analysis separates final-answer outcomes from candidate availability and selection, and compares these correctness measures across databases. Most responses are correct, but selection errors remain a notable source of failures.
- Performance breakdown: 72.9% of cases produced a correct final answer, while 10.4% contained a correct candidate that the system did not choose.The remaining categories were wrong generations or no correct candidate (6.7%) and wrong golden queries (10.0%).
- Database comparison: Figure 6 compares database-level percentages for correct queries existing among candidates and correct queries chosen by the selection agent.Databases or categories appear on the x-axis, and percentage performance appears on the y-axis.
- Database comparison: The database comparison distinguishes candidate-generation coverage from selection success rather than treating correctness as a single measure.This separation shows whether errors arise before selection or when the agent chooses among candidates.
A.4.1 Selection Agent Error Analysis
Selection-agent failures are analyzed when a correct candidate exists but another candidate receives the highest score. The errors are grouped into vague questions, wrong picking, data integrity errors, and incorrect gold queries.
- Error categories: Selection errors are categorized as vague questions, wrong picking, data integrity errors, and incorrect gold queries.The analysis concerns cases where at least one generated candidate matches the ground truth but the selector chooses another candidate.
- Wrong picking: Wrong picking is the largest error category, often involving a selected candidate that omits a required column, table, or SQL clause.The analysis found no specific recurring mistake pattern across instances; one example uses * instead of returning only the required id.
- Incorrect golden query: Incorrect golden queries form the second-largest category when the ground-truth SQL is wrong and a generated candidate reproduces that mistake.In the example, the gold query includes an extra molecule_ID column not specified by the question, while another candidate answers correctly.
- Vague questions: Vague questions can make a selector’s choice appear correct even when it differs from an unambiguous reference query.Figure 10 illustrates an error case attributed to question vagueness.
- Data integrity: Data integrity errors are the smallest category and arise when supposedly consistent columns contain missing values.One example concerns missing values in either the School or School Name column.
A.4.2 Error Analyses
The error analysis groups failures into schema linking, incorrect logic, SQL functions, JOINs, and ignored evidence, with missing required columns or tables the most common pattern. The appendix also illustrates the proposed divide-and-conquer and query-plan reasoning prompts, query fixing, selection-agent prompting, and synthetic-example generation.
- Error categories: Schema linking errors most commonly occur when candidate queries fail to use the columns or tables required by the question.Ambiguous or confusing column names can cause the model to select the wrong fields.
- Error categories: Wrong-logic errors include missing DISTINCT, NOT NULL conditions, SELECT columns, or WHERE and HAVING conditions.The analysis identifies this as the second-largest error category.
- Error categories: SQL-function errors involve incorrect or missing functions such as COUNT(), CAST(), AVG(), and ROUND().One example required ROUND(), but none of the candidate queries used it.
- Error categories: JOIN errors arise when predicted SQL queries omit one or more tables from their JOIN clauses.Ignoring explicit evidence is another documented error category, though it represents the smallest portion of cases.
- Prompt examples: The divide-and-conquer CoT method decomposes questions into pseudo-SQL subproblems, aggregates their solutions, and optimizes the resulting query.An example shows a query solved only by this generator.
- Prompt examples: The query-plan CoT method mirrors database execution steps, while the selection-agent prompt compares two candidate queries using their answers and differences.The appendix also presents query-fixing prompts and instance-aware synthetic examples whose SQL-feature distribution closely follows the ground-truth distribution except for CASE statements.