OpenSearch-SQL: Enhancing Text-to-SQL with Dynamic Few-shot and Consistency Alignment

Xiangjin Xie, Guangwei Xu, Lingyan Zhao, Ruijie Guo

Abstract

Converting natural language questions into executable SQL queries (Text-to-SQL) remains a significant challenge in database research, particularly as schema complexity and database scale increase. This paper introduces OpenSearch-SQL, a multi-agent framework designed to bridge the gap between Large Language Models (LLMs) and complex database systems without the need for expensive supervised fine-tuning (SFT). By implementing a novel consistency alignment mechanism and a simplified intermediate language called SQL-Like, OpenSearch-SQL addresses common pitfalls such as multi-agent hallucinations and instruction complexity. At the time of evaluation, the framework achieved state-of-the-art results on the BIRD leaderboard, demonstrating that architectural innovation can outperform specialized model training.

Introduction

Text-to-SQL systems serve as a critical bridge for non-technical users to interact with large-scale data. While modern LLMs show promise in code generation, they frequently struggle with real-world database tasks due to three primary hurdles:

  1. Framework Gaps (L1): Systems often lack the ability to verify if queried data actually exists or fail to correct errors dynamically.
  2. Multi-Agent Hallucinations (L2): In multi-step pipelines, errors in column selection or schema interpretation often cascade, leading to "accumulated hallucinations" where the final query is logically detached from the database reality.
  3. Instruction Complexity (L3): Generating syntactically perfect SQL in a single pass is cognitively demanding, often causing models to lose track of core query logic while managing complex JOIN conditions and formatting.

OpenSearch-SQL addresses these challenges through a modular, four-stage pipeline: Preprocessing, Extraction, Generation, and Refinement.

Methodology

The core innovation of OpenSearch-SQL lies in its ability to maintain information integrity across agents and simplify the reasoning process for the LLM.

Consistency Alignment

To mitigate the L2 problem of cascading errors, the framework introduces an Alignment module. This module acts as a "sanity check" between agents, utilizing sub-agents for Agent, Function, and Style Alignment. By ensuring that the output of one stage (e.g., a list of identified columns) perfectly matches the schema requirements of the next, the system prevents the model from hallucinating non-existent tables or using incompatible aggregate functions. This acts similarly to a residual connection in neural networks, preserving the "signal" of the database schema throughout the generation process.

SQL-Like Intermediate Language

To solve the L3 problem of instruction complexity, the authors developed SQL-Like, a simplified intermediate representation. Instead of forcing the LLM to produce production-ready SQL immediately, the model first generates a version that ignores complex JOIN syntax and specific formatting. This allows the LLM to focus exclusively on the query logic. Once the logic is established, a subsequent agent converts the SQL-Like code into valid, executable SQL.

Self-Taught Dynamic Few-Shot

Rather than relying on static examples, the system employs a Self-Taught Dynamic Few-Shot approach. It uses an LLM to generate its own Chain-of-Thought (CoT) reasoning steps from existing question-SQL pairs. These high-quality, self-generated reasoning paths are then used as dynamic prompts, providing the model with relevant context for new, unseen queries.

Experimental Results

The researchers evaluated OpenSearch-SQL on two primary benchmarks: Spider, a cross-domain semantic parsing dataset, and BIRD (Big Bench for Large-scale Database Grounded Text-to-SQL), which is currently the most rigorous test for these systems due to its massive scale (33.4 GB) and complexity.

BIRD Leaderboard Performance

OpenSearch-SQL reached the top of the BIRD leaderboard, achieving superior results across all primary metrics:

  • Execution Accuracy (EX): 69.3% on the development set and 72.28% on the test set.
  • Efficiency (R-VES): 69.36%, proving that the framework generates queries that are not only correct but also computationally optimized.

Spider Performance

The framework achieved an execution accuracy of 87.1% on Spider, notably outperforming several models that had undergone specific supervised fine-tuning for the task.

Discussion and Key Insights

Several critical insights emerged from the development of OpenSearch-SQL:

  • Architecture vs. Fine-Tuning: The success of OpenSearch-SQL using "off-the-shelf" models like GPT-4o suggests that sophisticated multi-agent orchestration can be more effective and portable than expensive fine-tuning on specific datasets.
  • Logic-First Approach: Decoupling logic from syntax via the SQL-Like language significantly reduces the cognitive load on the LLM, leading to higher logical accuracy.
  • The Value of Self-Consistency: The "Refinement" stage utilizes a voting mechanism (Self-Consistency). While this adds marginal value for simple queries, it provides a 7.64% performance boost for "challenging" problems where models are most prone to stochastic errors.

Limitations and Future Work

Despite its performance, the framework faces challenges regarding cost and latency. The Self-Consistency mechanism requires generating multiple SQL candidates (up to 21 in some configurations), which increases API usage and processing time. Furthermore, while the system is robust, it remains sensitive to the quality of initial prompts.

Future research will focus on more precise column and value selection methods. Additionally, the team plans to investigate the potential of combining this multi-agent architecture with a fine-tuned SFT model to further push the performance ceiling of Text-to-SQL systems.

Summary updated

BibTeX
@article{xie2025opensearch,
  author = {Xiangjin Xie and Guangwei Xu and Lingyan Zhao and Ruijie Guo},
  title = {{OpenSearch-SQL: Enhancing Text-to-SQL with Dynamic Few-shot and Consistency Alignment}},
  journal = {Proc. ACM Manag. Data},
  year = {2025},
  doi = {10.1145/3725331},
}
Figure