PaperlayerView exact revision on arXiv

Source-linked AI summary

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

Xiangjin Xie, Guangwei Xu, Lingyan Zhao, Ruijie Guo

arXiv:2502.14913v1cs.CLcs.AIcs.IR

TL;DR

Multi-agent Text-to-SQL systems can propagate hallucinated schema errors and fail to follow prompts. OpenSearch-SQL combines consistency alignment with dynamic few-shot SQL generation, and reports first-place BIRD rankings across three metrics.

  • Problem

    Multi-agent Text-to-SQL workflows can inherit hallucinated schema errors and produce outputs that fail to follow prompt instructions.

    Source: §2.3 · p. 3, §3.1 · p. 4

  • Method

    OpenSearch-SQL aligns each agent’s output with the upstream agent’s output and pairs dynamic Query-CoT-SQL few-shot examples with SQL-Like progressive generation.

    Source: §3.1 · p. 4, §3.5 · p. 6, §6 · p. 13

  • Results

    69.3% EX on BIRD validation, 72.28% EX on test, and 69.36% R-VES were reported, all ranking first at submission.

    Source: §1 · p. 2

  • Takeaways & Limitations

    The authors report that BIRD results demonstrate advantages in SQL accuracy and time efficiency.

    Source: §1 · p. 2

    OpenSearch-SQL has not fully optimized prompt details or precise column and value selection, and generation uses a single prompt for SQL candidates.

    Source: §3.8 · p. 7

Abstract

from arXiv · show

Although multi-agent collaborative Large Language Models (LLMs) have achieved significant breakthroughs in the Text-to-SQL task, their performance is still constrained by various factors. These factors include the incompleteness of the framework, failure to follow instructions, and model hallucination problems. To address these problems, we propose OpenSearch-SQL, which divides the Text-to-SQL task into four main modules: Preprocessing, Extraction, Generation, and Refinement, along with an Alignment module based on a consistency alignment mechanism. This architecture aligns the inputs and outputs of agents through the Alignment module, reducing failures in instruction following and hallucination. Additionally, we designed an intermediate language called SQL-Like and optimized the structured CoT based on SQL-Like. Meanwhile, we developed a dynamic few-shot strategy in the form of self-taught Query-CoT-SQL. These methods have significantly improved the performance of LLMs in the Text-to-SQL task. In terms of model selection, we directly applied the base LLMs without any post-training, thereby simplifying the task chain and enhancing the framework's portability. Experimental results show that OpenSearch-SQL achieves an execution accuracy(EX) of 69.3% on the BIRD development set, 72.28% on the test set, and a reward-based validity efficiency score (R-VES) of 69.36%, with all three metrics ranking first at the time of submission. These results demonstrate the comprehensive advantages of the proposed method in both effectiveness and efficiency.

1 INTRODUCTION

OpenSearch-SQL addresses gaps in LLM-driven Text-to-SQL with a four-stage framework and consistency alignment, plus Query-CoT-SQL few-shots and SQL-Like reasoning, without post-training.

Motivation

  • The motivating gaps were incomplete workflows, including absent database verification, error correction, or few-shot learning; weak agent coupling that accumulates hallucinations; and insufficient instruction design.The paper notes that later agents may ignore or only partly use earlier outputs, causing performance loss.Source: §1 · p. 1

Framework and alignment

  • The framework organizes Text-to-SQL into Preprocessing, Extraction, Generation, and Refine, with consistency alignment coordinating agents’ functions, inputs, and outputs to reduce hallucinations and information loss.Its stages follow the human process of understanding database structure, selecting query elements, constructing SQL, and refining it against execution results.Source: §1 · pp. 1–2

Few-shot and SQL-Like methods

  • OpenSearch-SQL adds self-taught Query-CoT-SQL few-shot examples and SQL-Like intermediate reasoning, which lets models draft an SQL backbone before filling in specific SQL details.The few-shot augmentation supplements query-SQL pairs with Chain-of-Thought information.Source: §1 · p. 2

Transferability

  • The method targets transferability across database query tasks without post-training, allowing agents and alignment methods to be modified for user needs without retraining.The authors report using a pretrained LLM without supervised fine-tuning or reinforcement learning.Source: §1 · p. 2

Results

  • OpenSearch-SQL achieved 69.3% EX on BIRD validation, 72.28% EX on test, and 69.36% R-VES, all ranking first at submission.The method was implemented without fine-tuning or specialized datasets.Source: §1 · p. 2

2 PRELIMINARY

The preliminary section defines Text-to-SQL as translating natural-language queries into SQL using database information, a model, and a prompt, and motivates OpenSearch-SQL’s alignment-based approach to hallucinations.

2.1 Large Language Models

  • Pre-trained LLMs acquire language and semantic knowledge from large-scale text, can perform downstream tasks through fine-tuning, and underpin current state-of-the-art Text-to-SQL work.Source: §2.1 · p. 2

2.2 Text-to-SQL

  • Text-to-SQL translates a natural-language query Q into an SQL query Y using database information S, a model F, and a prompt P.Source: §2.2 · p. 2
  • The framework groups Text-to-SQL methods into Preprocessing, Extraction, Generation, and Refinement, preparing database information, filtering query-specific aids, generating candidate SQLs, and optimizing them.Source: §2.2 · p. 3
  • OpenSearch-SQL uses dynamic few-shot prompting to strengthen its stages and an Alignment module to reduce hallucinations and simplify the multi-agent instruction system.Source: §2.2 · p. 3

2.3 Hallucination

  • In Text-to-SQL, hallucinations include nonexistent database information, instruction-following failures, and typographical or syntactic errors; the paper also treats randomness and minor prompt changes as sources of deviations.It identifies self-consistency and post-processing as architectural-level approaches to reducing hallucinations.Source: §2.3 · p. 3

3 METHODOLOGY · 3.1 Alignments · 3.2 Self-Taught Fewshot

OpenSearch-SQL pairs its operational stages with Alignment agents that reduce hallucination and instruction-following failures, while dynamic few-shots enrich selected examples with reasoning and error-specific correction guidance.

3.1 Alignments

  • Multi-agent errors can accumulate because downstream agents inherit hallucinated information, making total hallucination nearly monotonically non-decreasing.For example, an Extraction agent’s incorrect column names may persist into Generation, which lacks knowledge of the correct database structure.Source: §3.1 · p. 4
  • Alignment optimizes an agent’s input together with its output using a large model and rules, addressing differences attributed mainly to hallucinations.Its function is to align the current agent’s input information with its output result.Source: §3.1 · p. 4

3 METHODOLOGY · 3.1 Alignments

  • After each agent responds, an Alignment agent reconciles its output with the upstream agent’s output and passes the aligned result downstream.The methodology presents stages in operational order, pairing each with its corresponding Alignment.Source: §3.1 · p. 4, §3 · p. 4

3.1 Alignments

  • The consistency mechanism targets nonexistent or renamed columns, syntax errors, value-column mismatches, and failures to follow prompt rules, and is likened to residual connections [13].The paper says the mechanism extends the multi-agent collaboration chain while minimizing introduced hallucinations.Source: §3.1 · p. 4

3.2 Self-Taught Fewshot

  • OpenSearch-SQL uses Masked Question Similarity (MQs) [10] to select similar queries, then self-teaches Query-SQL examples into Query-CoT-SQL pairs with logical information.The authors describe the resulting few-shots as richer than simple Query-SQL pairs.Source: §3.2 · p. 4
  • For Refinement, the method prepares different few-shots for different error types, pairing Raw SQL errors with correction advice to guide SQL repair.The examples help LLMs understand how to correct SQL based on the specific error.Source: §3.2 · p. 4

3.3 Preprocessing

The preprocessing stage builds a database representation and retrieval resources to ground SQL generation, while also preparing dynamic Query-CoT-SQL few-shot examples. The process uses database information and training data and is automated by an agent without human intervention.

3.3 Preprocessing

  • Preprocessing reconstructs the database’s true structure and indexes only string values to reduce character-mismatch errors while saving retrieval-database space.The resulting schema and indexed values help generated SQL align with the actual database state.Source: §3.3 · p. 4
  • The stage creates dynamic few-shot examples that add CoT information and corrections tailored to different error types.Source: §3.3 · p. 5
  • Using database information and the training set, an agent automatically produces a vector database, Query-CoT-SQL few-shots, and the database schema without human intervention.Source: §3.3 · p. 5

3.4 Extraction

OpenSearch-SQL extracts entities, database values, and a preliminary schema subset from each NLQ, then aligns the extracted information with the input before passing it to Generation.

Extraction

  • Extraction prepares NLQ-specific schema links, stored database values, few-shot examples, and instructions, independently of the database query language.Its needs depend on data-storage format and auxiliary information for solving the problem.Source: §3.4 · p. 5
  • The pipeline extracts entities, retrieves values, filters columns, and uses Info Alignment to align extracted information with the input.Candidate columns and values are obtained through an LLM, while entities are extracted directly from the NLQ with a separate prompt.Source: §3.4 · p. 5

Entity Extraction

  • Entity extraction uses the NLQ and basic database information to identify potential entities, which are combined with predefined entity terms for retrieval and filtering.Source: §3.4 · p. 5

Values Retrieval

  • Vector retrieval finds database values similar to extracted entities, while split retrieval for phrases and longer texts helps avoid recall failures from storage-format differences.It filters the top K similar entities by a threshold and retains those meeting it; embedding similarity also helps prevent typo-related mismatches.Source: §3.4 · p. 5

Column Filtering

  • Column filtering combines LLM selection of NLQ-related tables and columns with vector retrieval of columns similar to NLQ entities above a threshold.The two-path recall forms a preliminary schema subset and is lighter and more streamlined, though it may be less precise.Source: §3.4 · p. 5

Info Alignment

  • Info Alignment matches NLQ phrases or clauses one-to-one with generated SELECT content to ensure its quantity and order, and adds primary keys and same-name columns to reduce misselection.The extracted information and alignment content are then passed to Generation.Source: §3.4 · p. 5

3.5 Generation

Generation uses progressive SQL-Like reasoning and dynamic few-shot examples to guide SQL production, while consistency alignments address discrepancies that can introduce errors and noise.

SQL Generation

  • Progressive generation asks the model to provide analysis, SELECT content, relevant columns and values, a SQL-Like query, and final SQL, making its reasoning structure easier to inspect.SQL-Like omits syntax-specific details such as JOINs and function formatting so the model can focus on SQL logic; multiple candidate queries may be generated for an NLQ.Source: §3.5 · p. 6
  • Few-shot examples are the Query-CoT-SQL forms of the top K_f most similar queries retrieved based on MQs.Inputs also include database rules and schema, relevant database values, and Info Alignment for matching the dataset’s SELECT style to the model.Source: §3.5 · p. 6

Alignments

  • Alignment addresses generation errors and sampling noise through agent, function, and style checks before SQL execution and refinement.Agent Alignment corrects column or value mismatches; Function Alignment standardizes aggregate functions and handles inappropriate expressions, nestings, and redundant JOINs; Style Alignment addresses dataset conventions such as IS NOT NULL and MAX versus LIMIT 1.Source: §3.5 · p. 6

3.6 Refinement

Refinement uses execution-based correction and consistency voting to select a final SQL, aiming to improve execution results and speed. The section also notes the cost of self-consistency and reports that OpenSearch-SQL v2 still ranks first on BIRD without it when Generation produces one SQL.

3.6 Refinement

  • Refinement aligns generated SQLs, corrects execution errors, then selects among viable queries by output consistency and, for tied answers, shortest execution time.Correction uses error-specific few-shot examples and instructions for issues such as syntax errors or empty results; unfixable queries and queries with empty answers are excluded.Source: §3.6 · p. 7
  • Despite the higher cost often associated with self-consistency, OpenSearch-SQL v2 still ranks first on BIRD when Generation produces one SQL and self-consistency is omitted.Source: §3.6 · p. 7

3.7 Algorithm

Algorithm 1 summarizes OpenSearch-SQL’s steps from preprocessing an NLQ to generating the final SQL. The pipeline builds query-specific schema and examples, generates and aligns SQL, then refines the answer.

3.7 Algorithm

  • Algorithm 1 covers each step from preprocessing an NLQ to generating its final SQL.The algorithm presents an overall summary of the OpenSearch-SQL framework.Source: §3.7 · p. 7
  • Preprocessing builds a value-and-column index and database schema, while training Query-SQL pairs are used to create a CoT-based few-shot set.For each NLQ, the system extracts entities and selects columns, retrieves similar values, expands database information, and uses Alignment to create a tailored schema. It retrieves relevant Query-CoT-SQL examples, generates SQL, aligns outputs, corrects execution errors, and selects the most consistent SQL with the shortest execution time.Source: §3.7 · p. 7

3.8 Optimization

Section 3.8 identifies prompt and selection refinements, SFT, broader few-shot strategies, and alignment research as opportunities to further improve OpenSearch-SQL.

3.8 Optimization

  • OpenSearch-SQL has not fully optimized prompts or column and value selection, and generation uses a single prompt to produce SQL candidates.Source: §3.8 · p. 7
  • Prior work on SFT models, including CHESS [34], MCS-SQL [18], and distillery [23], demonstrates potential to improve Text-to-SQL performance.Source: §3.8 · pp. 7–8
  • Few-shot methods could extend beyond Query-CoT-SQL pairs, while alignment research remains at an early stage with room for development.Source: §3.8 · p. 8

4 EXPERIMENTS

OpenSearch-SQL achieves leading BIRD and Spider results without fine-tuning, while experiments find benefits from its modules, Query-CoT-SQL few-shot strategy, self-consistency voting, and structured CoT.

4.1 Experimental Setup

  • BIRD has fewer database types but more complex structures and harder SQL on average than Spider; evaluation uses execution accuracy (EX) and reward-based valid efficiency score (R-VES).The Spider development set was used for tuning, with its test set treated as inaccessible.Source: §4.1 · p. 8

4.2 Main Result

  • At submission, OpenSearch-SQL v2 ranked first on BIRD with 69.3% EX on development and 72.28% EX plus 69.36% R-VES on holdout test; it achieved 87.1% EX on Spider test.The method did not involve fine-tuning to customize the LLM.Source: §4.2 · p. 9, Table 3 · p. 10

4.5 Self-Consistency & Vote

  • Self-Consistency & Vote improves difficult-problem performance by an absolute 7.64%; EX rises with more candidates for GPT-4o but peaks at 7 and 15 for GPT-4o-mini.The paper reports no notable voting difference for easy and medium problems and advises controlling candidate counts for smaller models.Source: Figure 3 · p. 9, §4.2 · p. 9, Figure 4 · p. 12

4.3 Modular Ablation

  • Ablations show SQL EX rising monotonically through the workflow; Few-shot helps Generation and Refinement, while Alignment improves individual SQL and Refinement adds 3.2% without CoT.Values retrieval plus column filtering improve single-SQL results by approximately as much as Extraction, suggesting their roles overlap little. Self-Consistency & Vote raises the performance ceiling, and Generation-stage Alignment helps Correction fix erroneous SQL.Source: §4.3 · p. 10

4.4 Few-shot

  • Query-CoT-SQL few-shot raises single-query generation EX by 3.0% over Query-SQL and 6.4% over no few-shot; its advantage narrows after refinement and voting.Few-shot still improves final EX after consistency voting, which the authors attribute to greater consistency among few-shot-driven SQL corrections.Source: §4.4 · pp. 10–11, Table 5 · p. 11

4.6 Execution Cost

  • Execution time primarily depends on LLM calls, retrieval, and SQL execution; generation takes longer than extraction because BeamSearch produces more tokens.HNSW can reduce retrieval latency; non-SELECT alignment steps usually activate only when SQL issues arise, while voting adds minimal time.Source: §4.6 · p. 12

4.7 CoT

  • Structured CoT improves single-SQL generation over unstructured or no CoT, and yields a larger relative gain when multiple answers are sampled for voting.The analysis isolates CoT by removing Generation-stage few-shot and compares single-SQL EXG with voted EXV.Source: Table 7 · p. 12, §4.7 · p. 12, Alignments · p. 12

5 RELATED WORK

Text-to-SQL remains challenging because SQL structure is closely tied to database structure and constrained by required output formats [17]. Related work spans classic SQL construction methods, advances in LLMs, and agent-based approaches to Text-to-SQL.

5.1 Classic Method

  • Classic methods assemble SQL clauses using sketches, dependency-aware prediction, graph representations, or intermediate languages; the authors say their theoretical value exceeds their practical value.Examples include SQLNET [42], RYANSQL [4], and SyntaxSQLNet [43].Source: §5.1 · p. 13

5.2 Large Language Models

  • LLMs progressed from GPT and BERT [5] to newer models including GPT-4 [26], LLAMA, and Qwenmax [1], with GPT associated with generation and BERT with language understanding.Source: §5.2 · p. 13

5.3 LLM-based Agents and RAG

  • LLM agents take on task-specific roles [39, 29], while RAG retrieves relevant documents [9]; Modular RAG [32, 45] and Advanced RAG [8, 22] add techniques such as reranking and rewriting.Source: §5.3 · p. 13

5.4 LLM for Text-to-SQL

  • LLM-based Text-to-SQL research uses chain-of-thought [33], zero-shot prompting (C3 [6]), difficulty-aware prompts (DIN-SQL [27]), retrieved few-shot examples (DAIL-SQL [7]), decomposition, and fine-tuning-plus-agent methods.MCS-SQL [18] votes across prompt sets, CHESS [34] filters columns, and Distillery [23] fine-tunes GPT-4O; benchmarks progressed from WiKiSQL [48] through Spider [44] to BIRD [20].Source: §5.4 · p. 13

6 CONCLUSION

OpenSearch-SQL combines dynamic Query-CoT-SQL few-shot construction with consistency alignment to improve Text-to-SQL. At submission, it ranked first on all three BIRD leaderboard metrics without relying on SFT.

6 CONCLUSION

  • The method extends examples with LLM-generated Chain-of-Thought information to form a Query-CoT-SQL few-shot configuration.The authors describe this as the first exploration of using LLMs to extend few-shot CoT content for Text-to-SQL.Source: §6 · p. 13
  • Consistency alignment mitigates hallucinations by reintegrating agents’ inputs and outputs to improve output quality.Source: §6 · p. 13
  • OpenSearch-SQL uses directly available LLMs and retrieval models without SFT, making it a pure architecture upgrade for Text-to-SQL.Source: §6 · p. 13
  • At submission, OpenSearch-SQL held the top position on all three BIRD leaderboard metrics.The authors report that these results demonstrate the approach’s advantages.Source: §6 · pp. 13–14

Related papers

Loading 2502.14913v1…