Refining Zero-Shot Text-to-SQL Benchmarks via Prompt Strategies with Large Language Models

Abstract

Text-to-SQL leverages large language models (LLMs) for natural language database queries, yet existing benchmarks like BIRD (12,751 question–SQL pairs, 95 databases) suffer from inconsistencies—e.g., 30% of queries misalign with SQL outputs—and ambiguities that impair LLM evaluation. This study refines such datasets by distilling logically sound question–SQL pairs and enhancing table schemas, yielding a benchmark of 146 high-complexity tasks across 11 domains. We assess GPT-4o, GPT-4o-Mini, Qwen-2.5-Instruct, llama 370b, DPSK-v3 and O1-Preview in zero-shot scenarios, achieving average accuracies of 51.23%, 41.65%, 44.25%, 47.80%, and 49.10% and a peak of 78.08% (O1-Preview), respectively. Prompt-based strategies improve performance by up to 4.78%, addressing issues like poor domain adaptability and inconsistent training data interpretation. Error-annotated datasets further reveal LLM limitations. This refined benchmark ensures robust evaluation of logical reasoning, supporting reliable NLP-driven database systems.

1. Introduction

The field of Text-to-SQL, which enables natural language querying of databases, has evolved significantly, propelled by neural architectures and large language models (LLMs). Early systems relied on inflexible rule-based or template-based methods, succeeded by neural approaches like Seq2SQL [1] and SQLNet [2], which introduced reinforcement learning and sequence-to-sequence models to improve SQL generation [3]. Modern solutions, such as DAIL-SQL [4] and SQL-PaLM [5], leverage LLMs with pretraining on vast text corpora and dynamic context learning, achieving high accuracy on benchmarks. While SQL’s structured format enhances LLM efficiency over unstructured data [6], challenges persist in handling complex queries, ensuring cross-domain generalization—where performance consistency falters across diverse domains—and maintaining accuracy amid ambiguous inputs [7].

Zero-shot prompting, where LLMs perform tasks without task-specific training or examples, has gained traction in Text-to-SQL. Brown et al. [8] demonstrated GPT-3’s capability, a method now vital for complex queries with models like GPT-4 [9] and LLaMA [10]. Studies like Liu et al. [11] further highlight LLMs’ zero-shot Text-to-SQL potential, though limitations in logical reasoning persist. However, no benchmark specifically targets zero-shot scenarios robustly. The BIRD dataset [12], with 12,751 question–SQL pairs across 95 databases and 37 domains, is a complex option, but approximately 30% of its queries suffer from logical misalignments (e.g., mismatched timestamps as shown in Figure 1), data inconsistencies, or grammatical errors [12], impairing evaluation. Such ‘dirty’ data—common in real-life benchmarks—hamper LLM reasoning evaluation, as logical clarity is critical in zero-shot settings where fine-tuning is absent [13]. Current Text-to-SQL benchmarks exhibit growing query ambiguity [14], rendering them less suitable for testing LLM logic. For instance, Spider’s [14] structured questions lack the complexity of real-world multi-table joins [14], a gap widened as LLMs master simpler datasets [15]. Conversational benchmarks like SParC [16] and CoSQL [17] focus on multi-turn interactions but neglect broader zero-shot reasoning assessment.

This study addresses the critical need for a reliable benchmark to evaluate LLM zero-shot reasoning in Text-to-SQL. We refine the BIRD dataset [12] by meticulously selecting 146 complex, logically sound question–SQL pairs across 11 domains. Our refinement process involves correcting SQL errors (e.g., fixing timestamp mismatches), aligning questions with SQL outputs, and enriching table schemas with contextual information (e.g., data ranges, typical values) to minimize ambiguity and reliance on external knowledge. This curated benchmark facilitates a rigorous assessment of LLM zero-shot reasoning on complex, real-world-like queries while eliminating the noise present in the original data. We evaluate six state-of-the-art LLMs (GPT-4o, GPT-4o-Mini, Qwen-2.5-Instruct, Llama 3 70b, DPSK-v3, O1-Preview) using this refined benchmark and specific prompt strategies, achieving average accuracies ranging from 41.65% to 78.08%. This work provides a robust foundation for evaluating and advancing reliable Text-to-SQL systems, supporting practical NLP-driven database applications.

2. Related Works

High-quality datasets are crucial for training and evaluating large language models (LLMs) [8]. Foundational datasets in the Text-to-SQL field include WikiSQL [1], ATIS [18], SParC [16], and CoSQL [17]. WikiSQL focuses on simple queries derived from Wikipedia tables [1], lacking complexity like JOINs [2]. ATIS offers examples in the air travel domain but its simple queries limit generalizability [18]. SParC emphasizes conversational parsing across topics [16], while CoSQL targets dialogue scenarios using Spider’s databases [17,19]. These datasets, while foundational, are increasingly inadequate for modern LLMs due to limited complexity and scope [7].

The advent of neural approaches marked a shift from rule-based systems. Seq2SQL [1] introduced reinforcement learning, followed by SQLNet [2], which improved efficiency without RL. SyntaxSQLNet [3] enhanced cross-domain performance using syntax tree networks. These methods laid the groundwork for LLM-based solutions like DAIL-SQL [4] and SQLPaLM [5], leveraging pretraining and dynamic context learning [6]. PICARD [20] introduced constrained decoding to refine autoregressive outputs.

Today, Spider [14] and BIRD [12] are prominent benchmarks. Spider targets complex, cross-domain parsing [14], but LLMs like LLaMA [10] and PaLM [21] have diminished this challenge. Zero-shot prompting, pioneered by GPT3 [8], has gained traction, with GPT-4 [9] and LLaMA excelling without training [11], though logical reasoning gaps persist [22]. Recent advancements include multi-agent frameworks like MAC-SQL [23] and SQLFixAgent [24], chain-of-thought prompting [25], and open-source efforts like CodeS [26]. Techniques such as SEA-SQL [27], QDA-SQL [28], OpenSearch-SQL [29], MCS-SQL [30], and Mag-SQL [31] enhance semantic accuracy, few-shot learning [32], and generative approaches. Surveys like [6,7,33] highlight trends toward real-world complexity, yet ambiguous ‘dirty’ data remain a challenge [23,34]. Our work refines BIRD into a clear, complex benchmark for zero-shot LLM evaluation, building on prompt design insights [35] and addressing data quality issues [9,36] to test reasoning robustly.

3. Challenges

While data from older datasets like Spider are currently too easy for LLMs [14], the more complex BIRD benchmark [12] is used here as a data source to ensure sufficient difficulty. Yet BIRD has various issues that harm its utility for assessing LLMs’ capabilities in SQL reasoning [22]. Key problems identified include

Logical and structural errors in SQL: Mismatches, such as omitting requested fields or providing overly complex SQL [12] (Figure A1), and ambiguities leading to misaligned logic [11] (Figure A2) are prevalent.

Inconsistent data representation: Discrepancies between database descriptions and actual data (e.g., ‘Y/N’ vs. ‘0/1’ in Figure A3) [12] or misleading naming conventions [7] can lead to incorrect reasoning.

Ambiguities and poorly defined questions: Vague questions leading to multiple interpretations [13] (Figure A4) or failure to define terms clearly [25] (Figure A5) cause issues.

Redundancy and unreasonable difficulty tags: The excessive repetition of specific test points [6] (Figure A6) or inaccurate difficulty labeling [12] (Figure A7) can skew evaluations.

Incorrect domain-specific knowledge assumptions: Problems requiring domain knowledge not provided or based on wrong assumptions [37] (Figure A8) render data unsuitable for reasoning assessment.

Evaluation challenges: Questions with multiple plausible answers or undefined criteria make validation difficult [34] (Figure A9).

These numerous issues highlight the necessity for significant refinement of the BIRD dataset to ensure valid benchmarking of LLM reasoning capabilities [35]. Rigorous alignment between questions and SQL, clearer question phrasing, and consistent data representation are crucial. Further examples illustrating these error types and their frequency are provided in Appendix A.

4. Methods

4.1. Dataset Creation

After identifying issues, the next step is to address these found issues. Each question is tested on various LLMs several times, and the generated SQL and original target SQL and their execution result are compared, to evaluate whether there are issues and adjust the queries and target SQL. For those data with serious problems that cannot be fixed, they are simply abandoned.

To create an ideal dataset for zero-shot prompt benchmarking, the following rules are applied in the data evaluation process:

• Avoid Mismatch Between Questions and SQL Outputs:

As presented before, SQL often omits fields explicitly requested in the question and sometimes diverges from the intent of the question. Such issues can be addressed by adding or removing the appropriate columns in the SQL and by adding or removing the query terms from the question (e.g., Question 586), as shown in Figure 2.

• Avoid Ambiguities in Questions Leading to Misaligned SQL:

Vague phrasing and ambiguous terms like percent or rate are sometimes inconsistently addressed in SQL and can eventually cause misinterpretations. To resolve the ambiguity, the description of data and the terms in question are transformed into more detailed forms (e.g., Question 1028), as shown in Figure 3.

• Avoid Overly Complex SQL for Simple Queries:

Although simpler answers exist, SQL queries can sometimes be unnecessarily complex. The solution is to compare the SQL generated by LLMs and check if they work better. If the SQL generated by LLMs works better in execution speed, etc., replace the original SQL with the generated SQL, as presented before.

• Avoid Redundancies or Missing Logic:

SQLs may contain redundant operations or omit key constraints, leading to incomplete or incorrect results (e.g., Question 835). Adding and removing appropriate terms from the SQL can solve such issues, as shown in Figure 4.

• Avoid Inconsistent Use of Data Representations:

SQL queries sometimes omit data inconsistencies or ambiguities, which may misalign with the questions’ intent, as in Question 83 (presented before). To solve this issue, the data description document is carefully checked to ensure no ambiguous, wrong, or missing data descriptions appear in the description documents, as shown in Figure 5.

• Avoid over-testing the same issue:

Original questions sometimes examine the same point too frequently. This problem can be solved by reducing the proportion of test questions with the same test point, i.e., by selecting only some of them to be added to the new data set.

• Adding Information about Data into Table Schema:

This benchmark focuses on the logic of the LLM, so ensuring the integrity of the data provided to the LLM before testing is critical. To this end, the data description file has been modified by adding two columns of data description, Data Range and Typical Data, to avoid problems caused by unknown data in the dataset to the LLM, as shown in Figure 6. Data ranges are obtained by capturing the maximum and minimum values in the corresponding columns to give the LLM an idea of the range of the data; typical data are obtained by randomly selecting 5 values in a field to simulate the behavior of a quick read of the database and to give the LLM a better understanding of the data.

• Assigning Difficulty Level Properly:

Most test questions were assigned the same difficulty as the original to show fidelity to the original data. Only those test questions that evidently contradicted their own difficulty labels were modified, as shown in Question 322 in the ‘Redundant and Unreasonable Difficulty Labels’ section.

4.2. Testing Method

The testing structure, as illustrated in Figure 7, was specifically designed to assess the reasoning and logic capabilities of the LLMs. During testing, SQL-structure-related exceptions, such as those observed in GPT-4o-Mini (e.g., incorrect marks leading to exceptions), were not regarded as critical for our evaluation. To minimize the impact of such exceptions on the analysis, we applied a secondary verification mechanism in the testing framework. In cases where an exception occurred during the initial attempt, the task will be evaluated again, and only if the exception remains in the second attempt, it will be recorded as a meaningful error and categorized alongside other incorrect cases, as multiple errors on the same task may reveal critical issues.

We conducted two types of experiments in this study. The first experiment involved running each model for five rounds on the entire dataset to calculate their average accuracy. This experiment was conducted on GPT-4o-Mini, GPT-4o, Qwen 2.5 Instruct, LLaMA-3-70B, DPSK-v3, and O1-Preview, with each model evaluated over five rounds on the entire dataset to calculate their average accuracy, aiming to assess their overall reasoning capabilities in zero-shot prompting scenarios in the Text-to-SQL domain.

The second experiment focused on repeated testing of the GPT-4o-Mini, GPT-4o and O1 on tasks that they never answered correctly, respectively. This experiment was designed to analyze the key challenges and limitations faced by the models in T2SQl field.

4.3. Prompt Generation

4.3.1. General Guidelines and Table Schema in Prompt

As shown in Figure 8, to ensure that the test structure functions correctly and allows for automated evaluation, the LLM was required to generate answers in a fixed format. This allows the SQL query within the answer to be easily extracted and executed to determine its correctness. Furthermore, to rigorously test only the reasoning ability, data integrity was prioritized. All necessary data, including relevant table schemas (enhanced as described in Section 4.1), were provided to the LLM via the prompt.

4.3.2. Extra Guidelines in Prompt

Based on common errors observed during initial testing iterations, we introduced additional guidelines into the prompt to mitigate these issues and better isolate core reasoning challenges in zero-shot prompting. These guidelines, detailed in Figure 9, Figure 10 and Figure 11, address specific problems like handling ties in ranking, data type precision, NULL values, and avoiding unfounded assumptions. They were designed based on common errors and paraphrased for clarity. It is important to note that while these guidelines offer general advice, they do not provide task-specific input–output examples related to the benchmark questions. For instance, advising on NULL or MAX/MIN handling is general SQL knowledge, not specific to any particular question in our dataset. Therefore, we maintain that this setup constitutes a zero-shot scenario, as the model does not learn from task-specific demonstrations but rather operates with enhanced general instructions and context, akin to how a developer might provide documentation or standard practices. Experiments were conducted both with and without these extra guidelines to assess their impact.

5. Results

5.1. Experimental Setup

All experiments were conducted on a high-performance computing platform using standardized software environments including Python 3.9.7, PyTorch 2.0.1, SQLite 3.36.0, pandas 1.5.3, OpenAI API 1.2.0, and Hugging Face Transformers 4.35.0. The refined dataset was hosted in SQLite databases. The overall testing framework is illustrated in Figure 7. This figure depicts the process using an example query, showing how initial errors might trigger re-evaluation before final validation. API calls were rate-limited with a timeout. This setup provided a robust environment for assessment.

5.2. Average Accuracy

The performance of six models was evaluated over five rounds on our refined dataset (146 pairs) using new (detailed guidelines) and old (basic guidelines) prompts.

Table 1. Average accuracy of six models across five test rounds on the refined dataset (new and old prompts) compared to the original bird subset. The refined dataset consistently outperforms BIRD, with new prompt accuracies ranging from 41.65% (GPT-4o-mini) to 78.22% (o1-preview), and old prompt accuracies ranging from 36.85% (GPT-4o-mini) to 75.89% (o1-preview).

Models		Refined Dataset (New Prompt)				Avg (New)		Refined Dataset (Old Prompt)				Avg (Old)	Original BIRD
	Test 1	Test 2	Test 3	Test 4	Test 5		Test 1	Test 2	Test 3	Test 4	Test 5		Avg (New)	Avg (Old)
GPT-4o-Mini	42.47%	46.58%	39.73%	41.78%	37.67%	41.65%	40.41%	32.88%	39.73%	34.93%	36.30%	36.85%	34.12%	31.25%
GPT-4o	47.94%	53.42%	51.36%	53.42%	50.00%	51.23%	50.00%	50.00%	52.05%	42.46%	52.74%	49.45%	43.68%	41.10%
Qwen-2.5-Instruct	46.58%	42.47%	45.21%	41.10%	45.89%	44.25%	43.84%	43.84%	44.52%	46.57%	36.30%	43.01%	38.90%	37.12%
LLaMA-3-70B	47.26%	50.00%	46.58%	45.21%	47.95%	47.80%	46.58%	47.95%	45.21%	44.52%	43.84%	45.62%	41.23%	39.80%
DPSK-v3	48.63%	50.00%	51.37%	47.26%	48.63%	49.10%	47.26%	48.63%	50.00%	46.58%	46.58%	47.80%	42.80%	41.50%
O1-Preview	77.40%	79.45%	76.03%	80.14%	78.08%	78.22%	75.34%	77.40%	74.66%	76.71%	75.34%	75.89%	69.50%	67.80%

presents accuracy results compared against a 146-pair subset of the original BIRD dataset. On the refined dataset with the new prompt, accuracies ranged from 41.65% (GPT-4o-Mini) to 78.22% (O1-Preview), outperforming the old prompt. Compared to the original BIRD subset, our dataset yielded gains of 5.35–8.72% (new prompt) and 5.60–8.09% (old prompt). These gains reflect the refinement’s mitigation of BIRD’s issues, while the prompt impact suggests data quality outweighs prompt details, supporting the hypothesis that clean data enhances zero-shot reasoning. This underscores the importance of data quality for reliable zero-shot evaluation, impacting assessments of model generalization and reliability, as discussed further below.

5.3. Ablation Studies

To quantify contributions of logical alignment, schema enhancement, and prompt guidelines, we conducted ablation studies across models and prompts on our refined dataset (146 pairs), measuring accuracy and error counts. Starting from full refinement, we incrementally removed components down to the original BIRD subset baseline.

Table 2. Ablation study combining accuracy across models and prompt types with error counts by type for gpt-4o (new prompt). T1–T7 denote error types (T1 Misinterpretation, T2 Forgetting, T3 Syntax, T4 Data Processing, T5 Query Logic, T6 Edge Cases, T7 False Assumptions).

Configuration		Accuracy (%)			Errors by Type (GPT-4o, New Prompt)
	GPT-4o (New)	GPT-4o (Old)	GPT-4o-Mini (New)	O1-Preview (New)	T1	T2	T3	T4	T5	T6	T7
Full Refinement	51.23	49.45	41.65	78.08	12	5	5	10	24	8	3
w/o Prompt Guidelines	49.80	47.67	40.00	76.85	12	6	6	10	25	9	3
w/o Schema Enhancement	48.12	46.30	38.90	74.52	14	8	5	13	26	8	3
w/o Logical Alignment	45.06	43.15	36.25	70.89	15	10	6	12	32	9	4
Original BIRD Subset	43.68	41.10	34.12	68.49	16	12	7	14	35	10	4
∆ from Full (GPT-4o, New)	-	−1.78	−9.58	+26.85	+4	+7	+2	+4	+11	+2	+1

consolidates accuracy and error distributions. Subsequent analyses explore impacts on reasoning, generalization, and error reduction.

• Impact of Logical Alignment: Removing logical alignment caused the largest accuracy drop across all models (−6.17% to −7.19%). For GPT-4o (new), Table 2 shows Type 5 errors (query logic) rising significantly, contributing ~82% of the total gain over BIRD. Errors increased substantially in domains like formula_1 and california_schools. This underscores how resolving logical misalignments [12] is pivotal for improving baseline reasoning accuracy, thus enhancing the model’s reliability and generalization potential on logically complex tasks.
• Impact of Schema Enhancement: Excluding schema enhancement reduced accuracy (−2.75% to −3.56%). For GPT-4o (new), Type 2 (forgetting requirements) and Type 4 (data handling) errors rose, reflecting reduced guesswork. Domain errors also increased. This supports the role of schema enhancement in reducing guesswork and improving cross-domain generalization by providing clearer data context, leading to more reliable outputs.
• Impact of Prompt Guidelines: Dropping prompt guidelines lowered accuracy moderately (−1.23% to −1.78%). For GPT-4o (new), Type 3 (syntax) and Type 6 (edge cases) errors increased slightly. Domain impacts were smaller. This indicates that while data quality is primary, prompt guidelines play a significant secondary role in fine-tuning reasoning and improving precision, contributing to overall model reliability across different models.

Overall Analysis: The total accuracy gain over BIRD (7.53–9.59%) matches the main experimental trends. (1) For GPT-4o (new), logical alignment drove ~82%, schema enhancement ~41%, and prompts ~19% of the uplift. (2) Errors increased upon removing refinements confirm BIRD’s ambiguities degrade performance. (3) O1-Preview’s higher baseline and resilience highlight its superior reasoning, while GPT-4o-Mini’s drops underscore sensitivity to data quality. (4) The dominant contribution of logical alignment confirms that clean, unambiguous data are the most critical factor for robust zero-shot performance, directly impacting model generalization and reliability.

5.4. Cross-Round Accuracy

Cross-round accuracy retested persistent errors, showing improved performance on our dataset versus BIRD. O1-Preview’s high accuracy and error drop reflect robust reasoning, bolstered by logical alignment. Figure 12, Figure 13 and Figure 14 provide further granularity on model performance across different domains, difficulty levels, and error types, respectively.

• Domain Analysis: Figure 12 shows O1-Preview’s edge, particularly in complex domains.
• Difficulty Analysis: Figure 13 highlights O1-Preview’s robustness on challenging tasks compared to others.
• Error Analysis: Figure 14 details seven error types (T1–T7). On BIRD, errors rose significantly. Type 5 (query logic) dominated but was cut by 33% with logical alignment. O1-Preview’s performance confirms refinement enhances reasoning. While this error typing provides more insight than a simple pass/fail metric, future work could explore even more granular partial correctness evaluations.

5.5. Case Studies

To illustrate effectiveness, we present three case studies comparing performance on original BIRD versus refined counterparts, highlighting how logical alignment, schema enhancement, and prompt guidelines improve accuracy.

• Case Study 1: Logical Alignment in formula_1 Domain (Question 880, Figure A4 vs. Figure 15): Refinement clarified ambiguity and aligned SQL with intent, improving accuracy (GPT-4o: 43.68% to 51.23%) and showcasing O1-Preview’s optimized solution, validating logical alignment’s gain.
• Case Study 2: Schema Enhancement in the california_schools Domain (Question 83, Figure A3 vs. Figure 16): Refining schema (Figure 5) resolved data inconsistencies, enabling correct interpretation and contributing to accuracy gains by reducing data handling errors.
• Case Study 3: Prompt Guidelines in the superhero Domain (Question 835, Figure 4 vs. Figure 17): Guidelines prompted the correct use of LEFT JOIN and NULL handling, correcting omissions and improving accuracy by reducing edge case errors.
• These case studies confirm that our method resolves BIRD’s issues, enhancing LLM reasoning and supporting accuracy uplift.
• Improvements in Runtime Performance: Refinements also enhance SQL execution efficiency (1). For instance, simplifying Question 477 (Figure A1) reduced execution time by 65.16% (2). We measured the times for 20 representative pairs across models (3). Figure 18 shows that refined SQL consistently reduces execution time (e.g., GPT-4o’s average drop of 46.12%) (4), particularly in complex domains (5). Improvements stem from logical alignment and prompt guidelines reducing computational overhead (6). Schema enhancements have a minimal direct runtime impact (7). This demonstrates practical runtime gains critical for real-world applications (8).

6. Conclusions

This study established a robust dataset by addressing flaws in BIRD [12], enabling a more accurate assessment of LLMs’ logical capabilities in zero-shot prompting. Performance aligned with model trajectories, validating the dataset. We identified common LLM issues and showed that prompt-based techniques offer mitigation, improving success rates and providing insights. Error-annotated datasets facilitate further analysis. These contributions enhance understanding and establish a foundation for ongoing improvements.

Despite progress, limitations remain: (1) Benchmarking is currently limited to SQLite; performance across other SQL dialects needs investigation. (2) Evaluations should encompass a broader range of LLMs. (3) Furthermore, future work could incorporate more granular evaluation metrics beyond execution accuracy, potentially exploring partial correctness scores, and employ rigorous statistical tests (e.g., ANOVA, t-tests) to validate performance differences. Finally, while our refined benchmark aims to reduce artifacts, the risk of overfitting exists; validation in diverse, real-world downstream applications, particularly critical decision-making systems, is essential to ensure true robustness and reliability.