Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

LLM-Based SQL Generation: Prompting, Self-Refinement, and Adaptive Weighted Majority Voting

Yu-Jie Yang, Hung-Fu Chang, Po-An Chen

TL;DR

The paper advances Text-to-SQL by introducing SSEV, a single-agent self-refinement pipeline with ensemble voting, and extends it to ReCAPAgent-SQL, a multi-agent framework for enterprise-scale SQL generation. It demonstrates that execution-guided refinement, schema grounding, and adaptive voting markedly improve accuracy across Spider 1.0, BIRD, and Spider 2.0-lite benchmarks, with WMA often outperforming RWMA in stability and overall performance. The approach leverages PET-SQL-inspired two-stage generation, external knowledge retrieval, and dynamic schema reasoning to address ambiguity, dialect diversity, and real-world data challenges. The work showcases practical deployment potential by achieving strong results on real-world datasets and outlining a concrete path toward scalable, robust Text-to-SQL systems for data-driven decision making.

Abstract

Text-to-SQL has emerged as a prominent research area, particularly with the rapid advancement of large language models (LLMs). By enabling users to query databases through natural language rather than SQL, this technology significantly lowers the barrier to data analysis. However, generating accurate SQL from natural language remains challenging due to ambiguity in user queries, the complexity of schema linking, limited generalization across SQL dialects, and the need for domain-specific understanding. In this study, we propose a Single-Agent Self-Refinement with Ensemble Voting (SSEV) pipeline built on PET-SQL that operates without ground-truth data, integrating self-refinement with Weighted Majority Voting (WMV) and its randomized variant (RWMA). Experimental results show that the SSEV achieves competitive performance across multiple benchmarks, attaining execution accuracies of 85.5% on Spider 1.0-Dev, 86.4% on Spider 1.0-Test, and 66.3% on BIRD-Dev. Building on insights from the SSEV pipeline, we further propose ReCAPAgent-SQL (Refinement-Critique-Act-Plan agent-based SQL framework) to address the growing complexity of enterprise databases and real-world Text-to-SQL tasks. The framework integrates multiple specialized agents for planning, external knowledge retrieval, critique, action generation, self-refinement, schema linking, and result validation, enabling iterative refinement of SQL predictions through agent collaboration. ReCAPAgent-SQL's WMA results achieve 31% execution accuracy on the first 100 queries of Spider 2.0-Lite, demonstrating significant improvements in handling real-world enterprise scenarios. Overall, our work facilitates the deployment of scalable Text-to-SQL systems in practical settings, supporting better data-driven decision-making at lower cost and with greater efficiency.

LLM-Based SQL Generation: Prompting, Self-Refinement, and Adaptive Weighted Majority Voting

TL;DR

The paper advances Text-to-SQL by introducing SSEV, a single-agent self-refinement pipeline with ensemble voting, and extends it to ReCAPAgent-SQL, a multi-agent framework for enterprise-scale SQL generation. It demonstrates that execution-guided refinement, schema grounding, and adaptive voting markedly improve accuracy across Spider 1.0, BIRD, and Spider 2.0-lite benchmarks, with WMA often outperforming RWMA in stability and overall performance. The approach leverages PET-SQL-inspired two-stage generation, external knowledge retrieval, and dynamic schema reasoning to address ambiguity, dialect diversity, and real-world data challenges. The work showcases practical deployment potential by achieving strong results on real-world datasets and outlining a concrete path toward scalable, robust Text-to-SQL systems for data-driven decision making.

Abstract

Text-to-SQL has emerged as a prominent research area, particularly with the rapid advancement of large language models (LLMs). By enabling users to query databases through natural language rather than SQL, this technology significantly lowers the barrier to data analysis. However, generating accurate SQL from natural language remains challenging due to ambiguity in user queries, the complexity of schema linking, limited generalization across SQL dialects, and the need for domain-specific understanding. In this study, we propose a Single-Agent Self-Refinement with Ensemble Voting (SSEV) pipeline built on PET-SQL that operates without ground-truth data, integrating self-refinement with Weighted Majority Voting (WMV) and its randomized variant (RWMA). Experimental results show that the SSEV achieves competitive performance across multiple benchmarks, attaining execution accuracies of 85.5% on Spider 1.0-Dev, 86.4% on Spider 1.0-Test, and 66.3% on BIRD-Dev. Building on insights from the SSEV pipeline, we further propose ReCAPAgent-SQL (Refinement-Critique-Act-Plan agent-based SQL framework) to address the growing complexity of enterprise databases and real-world Text-to-SQL tasks. The framework integrates multiple specialized agents for planning, external knowledge retrieval, critique, action generation, self-refinement, schema linking, and result validation, enabling iterative refinement of SQL predictions through agent collaboration. ReCAPAgent-SQL's WMA results achieve 31% execution accuracy on the first 100 queries of Spider 2.0-Lite, demonstrating significant improvements in handling real-world enterprise scenarios. Overall, our work facilitates the deployment of scalable Text-to-SQL systems in practical settings, supporting better data-driven decision-making at lower cost and with greater efficiency.
Paper Structure (37 sections, 2 equations, 17 figures, 10 tables, 2 algorithms)

This paper contains 37 sections, 2 equations, 17 figures, 10 tables, 2 algorithms.

Table of Contents

  1. Introduction
  2. Related Work
  3. LLM-based Text-to-SQL Overview
  4. Prompting Strategies and Two-Stage SQL Generation
  5. Self-Consistency and Cross-Consistency
  6. Agent-based Reasoning in Text-to-SQL Systems
  7. Retrieval-Augmented Generation (RAG) for Text-to-SQL and Knowledge Grounding
  8. Self-Refinement in Text-to-SQL Systems
  9. Methodology
  10. Common Techniques in SSEV pipeline and ReCAPAgent-SQL
  11. PreSQL, Schema Linking, and PostSQL
  12. Weighted Majority Algorithm Voting Mechanisms
  13. Execution-Guided Self-Refinement
  14. SSEV pipeline
  15. ReCAPAgent-SQL
  16. ...and 22 more sections

Figures (17)

  • Figure 5: Architecture of the two-stage Text-to-SQL system: PreSQL (blue) and PostSQL (yellow) pipelines. Both stages involve prompting, schema linking, generation, voting (WMA, RWMA, or Naïve), and execution-guided Self-refinement.
  • Figure 6: Comparison of PreSQL and PostSQL prompt structure. The left side illustrates the PreSQL prompt, which includes complete schema DDL, full table cell references, and few-shot examples to guide initial SQL generation. The right side shows the PostSQL prompt, where the schema has been pruned to only include entities linked in the PreSQL output, enabling more focused and efficient SQL generation.
  • Figure 7: Refinement prompt example
  • Figure 8: Architecture of ReCAPAgent-SQL with core agent modules.
  • Figure 9: Generate reference plan prompt
  • ...and 12 more figures