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

TL;DR

This paper introduces a novel pipeline for improving text-to-SQL translation using self-refinement and ensemble voting, achieving high accuracy on benchmarks and addressing real-world enterprise database challenges.

Contribution

It proposes the SSEV pipeline with self-refinement and weighted voting, and the ReCAPAgent-SQL framework with multiple agents for iterative SQL refinement in complex scenarios.

Findings

SSEV achieves 85.5% accuracy on Spider 1.0-Dev

SSEV attains 86.4% on Spider 1.0-Test

ReCAPAgent-SQL reaches 31% accuracy on Spider 2.0-Lite first 100 queries

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.