A comprehensive evaluation of ChatGPT's zero-shot Text-to-SQL capability

Aiwei Liu, Xuming Hu, Lijie Wen, Philip S. YuarXiv 2023 · DOI · arXiv

Introduction

The quest to bridge the gap between natural language and structured query language (SQL) has long been a focal point of database research. Traditionally, state-of-the-art (SOTA) performance in Text-to-SQL tasks has relied on specialized models fine-tuned on massive, hand-annotated datasets like Spider. However, these models often exhibit fragility when faced with real-world "distribution shifts"—scenarios where database schemas are messy, ambiguous, or intentionally misleading.

This paper presents the first systematic evaluation of ChatGPT’s zero-shot capabilities in this domain. While ChatGPT trails fine-tuned SOTA models by approximately 14% on standard benchmarks, it demonstrates a remarkable "robustness advantage," outperforming specialized models in adversarial environments where schemas are modified to confuse the generator.

Methodology: Zero-Shot Prompting

Unlike traditional models that require task-specific training, the researchers evaluated ChatGPT using a zero-shot approach. The model was provided with a standardized prompt containing the database schema (table names and column properties) and the natural language question, without any prior examples of SQL generation for that specific domain.

Examples of zero-shot prompting for single-turn and multi-turn SQL generation.

The evaluation prioritized Execution Accuracy (EX) over the more rigid "Exact Match" metric. This shift acknowledges that LLMs often generate valid, functional SQL queries that may differ syntactically from human-annotated "gold" standards but remain logically correct.

Performance Across Diverse Benchmarks

The study spanned 12 datasets to test various dimensions of SQL generation, including robustness, domain knowledge, and multi-turn conversation.

The Robustness and Adversarial Edge

A key finding of the research is ChatGPT's superior performance in adversarial scenarios. On the ADVETA (RPL) dataset, where column names are replaced with adversarial decoys, ChatGPT achieved an execution accuracy of 58.5%, surpassing the fine-tuned RESDSQL-3B model by 4.1%.

Performance comparison on Spider-DK and ADVETA datasets, highlighting ChatGPT's robustness in adversarial scenarios.

This robustness is attributed to ChatGPT's extensive "world knowledge." For instance, on the Spider-DK dataset—which requires external domain knowledge not explicitly present in the schema—ChatGPT's broad pre-training allows it to infer relationships and column meanings that specialized models, trained only on SQL pairs, fail to grasp.

Multi-turn and Multilingual Capabilities

In multi-turn scenarios (SParC and CoSQL), where the model must maintain context across a dialogue, ChatGPT remained competitive, achieving 60.7% accuracy on CoSQL. However, a significant performance gap was observed in multilingual tasks. On Chinese datasets like DuSQL, performance dropped to 53.7%, particularly when the database schema itself was non-English, indicating a limitation in cross-lingual schema mapping.

Discussion and Failure Modes

Despite its strengths, ChatGPT exhibits specific "failure modes":

  1. Schema Ambiguity: The model occasionally struggles to map natural language terms (e.g., "full name") to the correct column if the schema is not explicitly clear.
  2. Case Sensitivity: It sometimes fails to maintain the exact casing required for string values within the database.
  3. Join Preferences: ChatGPT shows a distinct preference for LEFT JOIN over standard JOIN. While often safer, this can lead to results that do not align with the simpler expectations of certain benchmark datasets.

Conclusion

ChatGPT represents a significant step toward general-purpose Text-to-SQL interfaces. Its ability to perform within 14% of SOTA models without any fine-tuning—and to exceed them in adversarial settings—suggests that broad world knowledge is a powerful asset for database interaction. Future improvements through in-context learning and automated error correction may soon bridge the remaining gap, paving the way for more resilient and accessible data exploration tools.

Summary updated

Abstract

This paper presents the first comprehensive analysis of ChatGPT's Text-to-SQL ability. Given the recent emergence of large-scale conversational language model ChatGPT and its impressive capabilities in both conversational abilities and code generation, we sought to evaluate its Text-to-SQL performance. We conducted experiments on 12 benchmark datasets with different languages, settings, or scenarios, and the results demonstrate that ChatGPT has strong text-to-SQL abilities. Although there is still a gap from the current state-of-the-art (SOTA) model performance, considering that the experiment was conducted in a zero-shot scenario, ChatGPT's performance is still impressive. Notably, in the ADVETA (RPL) scenario, the zero-shot ChatGPT even outperforms the SOTA model that requires fine-tuning on the Spider dataset by 4.1\%, demonstrating its potential for use in practical applications. To support further research in related fields, we have made the data generated by ChatGPT publicly available at https://github.com/THU-BPM/chatgpt-sql.
BibTeX
@misc{liu2023comprehensive,
  author = {Aiwei Liu and Xuming Hu and Lijie Wen and Philip S. Yu},
  title = {{A comprehensive evaluation of ChatGPT's zero-shot Text-to-SQL capability}},
  year = {2023},
  eprint = {2303.13547},
  archivePrefix = {arXiv},
  url = {https://arxiv.org/abs/2303.13547},
}
Figure