[Paper Review] CHASE-SQL: Multi-Path Reasoning and Preference Optimized Candidate Selection in Text-to-SQL

Mohammadreza Pourreza, Hailong Li+8 more|arXiv (Cornell University)|Oct 2, 2024

Advanced Database Systems and Queries4 citations

TL;DR

CHASE-SQL proposes a test-time compute framework that enhances Text-to-SQL performance through multi-path reasoning and preference-optimized candidate selection. By combining divide-and-conquer, execution-plan-based chain-of-thought, and instance-aware synthetic example generation, it produces diverse, high-quality SQL candidates, which are then ranked via a fine-tuned binary selection LLM, achieving state-of-the-art execution accuracy of 73.01% on the BIRD development set.

ABSTRACT

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).

Motivation & Objective

• Address the limitations of existing Text-to-SQL methods that rely on single-prompt generation and self-consistency, which fail to fully exploit LLMs’ intrinsic knowledge.
• Improve candidate diversity and quality in SQL generation without sacrificing accuracy, especially under high-temperature sampling or complex query conditions.
• Develop a robust, learnable selection mechanism that outperforms self-consistency and ranking-based methods by leveraging pairwise comparisons.
• Optimize test-time computation through multi-agent orchestration, integrating reasoning strategies, synthetic data, and value retrieval for better schema understanding.
• Achieve state-of-the-art execution accuracy on the BIRD benchmark by combining diverse generation techniques with a trained selection agent.

Proposed method

• Employ a divide-and-conquer strategy that decomposes complex natural language questions into sub-queries using a single LLM call to improve reasoning over complex schemas.
• Implement query execution-plan-based chain-of-thought (CoT) prompting, where reasoning steps mirror the actual steps a database engine takes during query execution.
• Introduce instance-aware synthetic example generation to produce few-shot demonstrations tailored to the specific schema and values of each test question.
• Use a selection agent trained via binary classification to score candidates through pairwise comparisons, aggregating results into a cumulative ranking for final selection.
• Integrate a locality-sensitive hashing (LSH) module for efficient value retrieval and a query fixer to correct syntactic errors in generated SQL.
• Combine three distinct candidate generators (divide-and-conquer CoT, execution-plan CoT, and synthetic example CoT) to maximize diversity and quality in the candidate pool.

Experimental results

Research questions

• RQ1 Can multi-path reasoning strategies—such as divide-and-conquer and execution-plan-based chain-of-thought—improve the quality and diversity of generated SQL queries in complex Text-to-SQL tasks?
• RQ2 How effective is instance-aware synthetic example generation in enhancing LLMs’ schema understanding and improving few-shot generalization for unseen test questions?
• RQ3 Does a learnable, pairwise comparison-based selection agent outperform traditional self-consistency and ranker-based methods in selecting the correct SQL query from diverse candidates?
• RQ4 To what extent does test-time compute, when strategically applied across generation and selection, improve execution accuracy on challenging benchmarks like BIRD?
• RQ5 How do individual components—such as LSH, query fixer, and reasoning strategies—affect the overall performance of the framework in ablation studies?

Key findings

• CHASE-SQL achieves a new state-of-the-art execution accuracy of 73.01% on the BIRD development set and 73.0% on the test set, outperforming prior methods at the time of submission.
• The proposed selection agent improves performance by approximately 6 percentage points over self-consistency, even under high-temperature sampling, demonstrating robustness to candidate diversity.
• The ablation study shows that removing any of the core components—LSH, query fixer, or reasoning strategies—leads to a measurable drop in accuracy, with the largest drop (7.5%) when replacing the selection agent with a ranker agent.
• The selection agent maintains or improves performance with higher temperature sampling (T=1.8), while self-consistency performance degrades, indicating the agent’s resilience to noisy or diverse candidates.
• The combination of three distinct reasoning strategies (divide-and-conquer, execution-plan CoT, and synthetic examples) yields higher diversity and better selection outcomes than any single method alone.
• The upper bound on performance (82.79%) is significantly higher than self-consistency (68.84%), highlighting the untapped potential of better selection mechanisms—precisely what CHASE-SQL’s agent addresses.