Advances in OpenSearch-SQL

• OpenSearch-SQL is an advanced NL2SQL framework that combines schema filtering, multi-generator ensembles, and learned LL-based selection to enhance query translation.
• The approach uses a three-stage pipeline—schema filtering, diverse SQL candidate generation, and execution-based candidate reorganization—to balance precision, diversity, and controllability.
• Empirical results demonstrate improved execution accuracy across benchmarks, validating the effectiveness of integrating multi-task fine-tuning and LLM-driven techniques.

OpenSearch-SQL refers to advanced frameworks designed for natural language to SQL (NL2SQL) translation, leveraging ensemble architectures and modern LLMs for robust, high-accuracy semantic parsing. The recent XiYan-SQL paradigm epitomizes the state-of-the-art in OpenSearch-SQL by integrating schema filtering, multi-generator ensembles, fine-tuned selection mechanisms, and multi-format augmentation to achieve both quality and diversity in SQL output, delivering superior execution accuracy across cross-domain and dialectal benchmarks (Gao et al., 2024, Liu et al., 7 Jul 2025).

1. Architecture and Workflow

XiYan-SQL organizes the NL2SQL process into a three-stage pipeline: schema filtering, candidate generation via ensemble models, and learned candidate selection. This division addresses the key weaknesses of single-generator (prompt-based or supervised fine-tuned) approaches, namely their trade-off between precision, diversity, and controllability. The major pipeline steps are:

1. Schema Filtering: The full database schema is pruned to minimal, relevant sub-schemas using multi-path retrieval and iterative, LLM-driven column selection.
2. Candidate Generation (Multi-Generator Ensemble): Multiple SQL generators (each fine-tuned on distinct objectives or formats) and in-context-learning (ICL) models produce diverse candidate queries, with failed generations passed to an on-the-fly “refiner.”
3. Selection Module with Candidate Reorganization: Candidates are clustered by execution semantics (output equivalence), reorganized by cluster size and generator trust, and finally ranked via a compact, fine-tuned LLM scorer.

The following table summarizes the major pipeline modules and their salient techniques:

Stage	Key Techniques	Main Function
Schema Filtering	Multi-path similarity, LLM selection	Prune schema to relevant subspaces
Candidate Generation	Multi-task SFT, ICL, Refiner	Diverse, style-variant SQL synthesis
Selection & Reorg	Execution clustering, learned LLM rank	Best-candidate selection beyond voting

Each of these stages is critical for robustness and state-of-the-art accuracy (Liu et al., 7 Jul 2025).

2. Schema Filtering and M-Schema Representation

The schema filtering component solves the high noise and recall-precision dilemma inherent in large, normalized database schemas. XiYan-SQL’s retrieval mechanism first extracts keywords and literals from the question and auxiliary evidence using GPT-4o, computes similarity-based scores at the column and value levels, and applies edit-distance plus embedding similarity for value-level pruning. Subsequent iterative column selection, repeated $p_s=2$ times, uses LLM prompting to select columns most relevant to the question, always preserving implied primary/foreign keys.

Filtered sub-schemas are encoded using the M-Schema format. For a schema $D = \{T_1, ..., T_n\}$, each table $T_i = (name_i, C_i, PK_i, FK_i)$ is represented in a semi-structured prompt:

[DB_ID: "db1"] Columns: (c_{ij}, data_type, description, isPK, {examples}) [Foreign Keys: (c_{ij} → T_k.c_{kl}), …]

This encoding ensures explicit display of types, primary keys, and 2–3 real example values, improving both compactness and context-awareness compared to raw DDL or prior formats (e.g., MAC-SQL schema) (Gao et al., 2024).

3. Multi-Generator Ensemble for SQL Candidate Synthesis

The multi-generator ensemble consists of both supervised fine-tuned (SFT) models and ICL-based generators.

Supervised Fine-Tuned Generators

• Each model is trained via a two-stage multi-task curriculum:
  • Basic-Syntax: train on large, dialect-agnostic SQL corpora.
  • Generation-Enhance: incorporate NL→SQL, SQL→NL (reverse), SQL discrimination and repair, evidence selection, and question paraphrasing via LLM data augmentation.
• Joint training objective: $L = \sum_t w_t \cdot L_t$ with tasks $t$ weighted to contribute to the total.

To induce diversity, alternate SQL format augmentations are employed (chunked, standardized, mixed).

ICL Generators

• Few-shot, prompt-based LLMs (e.g. GPT-4o), with context examples retrieved using a named-entity skeleton strategy—entities in natural language $q$ and training data $q_i$ are replaced by placeholders, embeddings compared by cosine similarity, and top-$K$ diverse matches selected. In multi-table settings, only examples referencing multiple tables are kept.

Refiner

• Takes any failed candidate (syntax/runtime error), prompts a LLM with the schema, SQL, and execution feedback for correction—allowing an effective cross-entropy update at inference (Gao et al., 2024, Liu et al., 7 Jul 2025).

The combination of these generators (four supervised, one ICL by default) is critical for both precision (SFT, standardized styles) and complementary coverage (diverse and complex constructs through ICL and stylistic augmentation).

4. Learned Selection and Candidate Reorganization

After candidate SQL generation, robust selection is essential to avoid failure modes of self-consistency or naive majority voting.

Execution-based Clustering: All candidate SQLs are executed, and grouped into clusters $C_1, ..., C_m$ by identical result output.

Candidate Reorganization:

• Inter-group: clusters sorted by descending size.
• Intra-group: candidates within clusters sorted by generator trust.
• Final candidate list $L'$ is constructed either by concatenation (if a majority exists) or by sampling shortest SQL from each cluster.

Selection Model:

• A compact LLM (Qwen2.5-Coder-7B) is fine-tuned on synthetic positive/negative SQL contrasts to output the single-best SQL $l^*$. Model capacity ablation shows a 69.56% accuracy (EX) for the fine-tuned model, outperforming GPT-4o and Gemini-1.5-pro on forced-choice tasks (Liu et al., 7 Jul 2025).

5. Empirical Results and Benchmarks

XiYan-SQL establishes state-of-the-art performance on multiple NL2SQL benchmarks:

Dataset	Metric	XiYan-SQL Performance	Comparison
BIRD	EX (test)	75.63%	CHASE-SQL+Gemini: 74.79%; GPT-4o: 58.47%
Spider	EX (test)	89.65%	MCS-SQL+GPT-4: 89.60%; CHASE-SQL+Gemini: 87.60%
SQL-Eval	EX (test)	69.86%	GPT-4o: 64.64%; SQL-Coder-8B: 60.20%
NL2GQL	EX (test)	41.20%	DeepSeek: 18.06%; GPT-4o: 4.86%

Ablation confirms each pipeline component adds 1–4% absolute execution accuracy. Removal of schema filtering (–1.24%), reduction to a single SFT generator (–4.04%), or replacing learned selection with majority voting (–3.13%) all degrade performance (Liu et al., 7 Jul 2025, Gao et al., 2024).

6. Analysis of Component and Generator Diversity

Schema Filtering demonstrates high value-recall (≥90%) and manages a precision/recall trade-off between iteration $S_1$ (high precision) and $S_2$ (high recall).

Multi-Task SFT Gains: Multi-task fine-tuning adds 2–4% on base SFT, with reverse question inference providing the largest individual gain.

Generator Diversity: With $p_\ell=10$ candidates (five models × two schema slices), the upper-bound (best possible pick) reaches 82.2% EX on BIRD. Fine-tuned generator ensembles (Multi-FT) yield higher top-1 accuracy and diversity than temperature or prompt-variant approaches alone.

Selection and Reorganization: Learned selection surpasses majority voting and naïve LLM scoring by 2–3%. The main SFT generator supplies the majority of correct picks (51.9% contribution), with secondary format-diverse and ICL generators filling important coverage niches (Liu et al., 7 Jul 2025).

7. Limitations and Future Directions

OpenSearch-SQL frameworks exemplified by XiYan-SQL demonstrate strengths including high single-model accuracy, robust diversity via format/style augmentation, effective candidate ranking, and scalability through schema filtering. Nonetheless, diversity upper bounds plateau (~82% for 10 candidates on BIRD), suggesting the need for more radical diversity methods (e.g., mixture-of-experts, retrieval-augmented SQL) for further gains. Extension of the paradigm to all-in-one models, encompassing filtering, generation, and selection in a unified prompt, or to related modalities (e.g., Text-to-Visual SQL, Text-to-NoSQL), remains an active research direction (Liu et al., 7 Jul 2025).

The rigorously orchestrated, multi-component architecture of OpenSearch-SQL sets a new benchmark for robust, high-quality, and diverse text-to-SQL translation and serves as a blueprint for future advances in semantic parsing frameworks.