Source-linked AI summary
A comprehensive evaluation of ChatGPT's zero-shot Text-to-SQL capability
Aiwei Liu, Xuming Hu, Lijie Wen, Philip S. Yu
TL;DR
The paper addresses the limited evidence on ChatGPT’s zero-shot ability for the challenging Text-to-SQL task. It evaluates ChatGPT across 12 benchmark datasets spanning languages, settings, and scenarios, finding strong overall performance and robustness despite a remaining SOTA gap, with a 4.1% advantage in ADVETA. The authors conclude that zero-shot ChatGPT is particularly strong in robustness and compositional generalization settings.
Problem
The paper investigates ChatGPT’s zero-shot performance on Text-to-SQL, a challenging code-generation task requiring conversion of user input into executable SQL.
Source: §1 · p. 1Method
The study evaluates ChatGPT on 12 Text-to-SQL benchmark datasets using standardized prompts and execution-based evaluation against established baselines.
Source: §3.1 · p. 3Results
ChatGPT shows strong Text-to-SQL performance and robustness, with a 4.1% advantage over SOTA models in ADVETA despite a remaining overall performance gap.
Source: §1 · p. 2Takeaways & Limitations
Zero-shot ChatGPT performs relatively well on robustness and compositional generalization benchmarks and can surpass SOTA models on ADVETA.
Source: §1 · p. 2, §3.2 · p. 4, §5 · p. 6The study uses only a common prompt and identifies non-executable SQL generation as an issue to address through future multi-turn interaction and additional in-context examples.
Source: §6 · p. 6
Abstract
from arXiv · showhide
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.
1 Introduction
This paper evaluates ChatGPT’s zero-shot Text-to-SQL capability across diverse datasets and scenarios. ChatGPT demonstrates strong performance and robustness, including outperforming SOTA models in ADVETA, while remaining behind SOTA overall.
Motivation
- The paper conducts a comprehensive evaluation of ChatGPT’s zero-shot performance on the challenging Text-to-SQL task.Text-to-SQL converts user input into executable SQL and requires handling varied databases, questions, and database structures.Source: §1 · pp. 1–2
Main findings
- ChatGPT performs 14% worse than the current SOTA model using complete training data, despite using no task-specific training data.The result demonstrates strong zero-shot Text-to-SQL conversion ability.Source: §1 · p. 2
- A 7.8% performance gap separates ChatGPT from SOTA models on some Spider robustness settings, compared with 14% on standard Spider.The smaller gap indicates stronger relative performance under these robustness settings.Source: §1 · p. 2
- 4.1% higher performance allows ChatGPT to surpass current SOTA models in the ADVETA scenario with adversarially modified database column names.This scenario evaluates robustness to adversarial changes in database information.Source: §1 · p. 2
Evaluation
- The study mainly uses execution accuracy because exact-match scores are very low when different SQL expressions produce the same result.Execution accuracy compares query execution results with the standard SQL results.Source: §1 · p. 2
Conclusion
- Overall, ChatGPT shows strong Text-to-SQL capabilities and robustness, outperforming SOTA models in certain scenarios.The paper’s introduction presents this as the overall experimental conclusion.Source: §1 · p. 2
2 Method
The method uses standardized prompts to evaluate ChatGPT in single-turn and multi-turn Text-to-SQL scenarios. The single-turn prompt includes database and question information, while later multi-turn interactions provide only new questions.
Prompt design
- The evaluation directly adopts the Text-to-SQL prompt from the OpenAI demo without further prompt exploration.This choice is intended to provide a fair demonstration of ChatGPT’s Text-to-SQL capabilities.Source: §2 · p. 2
Single-turn scenario
- In the single-turn scenario, the prompt requires only the database and question information.The prompt also emphasizes that generated SQL statements can be executed in SQLite.Source: §2 · p. 2
Prompt design
- Figure 1 contrasts the single-turn prompt with the multi-turn prompt structure used for subsequent interactions.The upper prompt represents single-turn use, while the lower prompt represents multi-turn use.Source: Figure 1 · p. 2
Multi-turn scenario
- In the multi-turn scenario, the first interaction uses the single-turn prompt and subsequent interactions require only new questions.This structure supports evaluations involving multiple interactions.Source: §2 · p. 2
3 Experiment
The experiments evaluate ChatGPT across twelve Text-to-SQL benchmarks spanning multiple datasets, languages, robustness settings, compositional generalization, and interaction scenarios. ChatGPT shows strong zero-shot performance overall, with particularly small gaps or advantages in several robustness and generalization settings, while Chinese schema-and-question settings remain weaker.
Experiment Setup
- 12 public benchmark datasets span cross-domain, synonym, realistic, knowledge, adversarial, compositional, multi-turn, and Chinese Text-to-SQL scenarios.The evaluation includes Spider and its variants, ADVETA, CSpider, DuSQL, SParC, and CoSQL.Source: §3.1 · pp. 2–3, Table 4 · p. 4, Table 5 · p. 4
- Execution accuracy is emphasized because equivalent SQL queries can differ syntactically, making exact match unsuitable for zero-shot ChatGPT evaluation.The study also reports valid SQL and test-suite accuracy among its main metrics.Source: §3.1 · p. 3
Main Results
- ChatGPT performs 14% worse than fully trained SOTA models overall, despite using no task-specific training data.On the standard Spider comparison, the reported gap is 14% in execution accuracy and 13.4% in test-suite accuracy.Source: §3.2 · pp. 3–4
Robustness
- 7.8% is the performance gap on some Spider robustness settings, compared with 14% on standard Spider.The results characterize ChatGPT as robust when database-column names or related inputs are modified.Source: §3.2 · p. 4
- 4.1% is ChatGPT’s advantage over current SOTA models in the ADVETA (RPL) scenario with adversarially modified column names.ChatGPT also performs exceptionally well on Spider-DK, ADVETA (RPL), and ADVETA (ADD), with very small gaps to SOTA models.Source: §3.2 · p. 4
Generalization and Language
- ChatGPT shows strong performance in compositional and multi-turn settings, but Chinese Text-to-SQL performance declines when schema names and questions are both Chinese.It outperforms the original Spider dev set on Spider-CG (SUB), has a relatively smaller SOTA gap on Spider-CG (APP), and performs better on CoSQL with more average interactions.Source: §3.2 · pp. 4–5
4 Related Work
Text-to-SQL converts natural-language questions into executable SQL, but its complexity requires robustness across varied database structures and input scenarios. Prior work evaluates these challenges through specialized datasets and models, including ChatGPT-based zero-shot approaches.
- Text-to-SQL converts natural-language questions into executable SQL statements for database access.Source: §4 · p. 5
- Spider development errors include overly fine-grained JOINs, database-structure confusion, incorrect nested-query semantics, and value-copying mistakes.Source: Table 6 · p. 5
- Robustness is evaluated under synonym substitutions, domain knowledge, omitted schema mentions, compositional generalization, and adversarial database modifications.Source: §4 · pp. 5–6
- PICARD and RASAT use constrained decoding with T5-3B but require training-data fine-tuning, whereas prior work also examined GPT-3 in zero-shot Text-to-SQL.Source: §4 · p. 6
5 Conclusion
The paper comprehensively evaluates ChatGPT’s zero-shot Text-to-SQL ability and finds strong performance despite a remaining gap from current SOTA models. It also reports strong robustness, including performance that surpasses SOTA on ADVETA.
- ChatGPT demonstrates strong Text-to-SQL ability without using training data, although it remains behind current SOTA models.Source: §5 · p. 6
- ChatGPT performs relatively better on most robustness benchmarks and surpasses current SOTA models on the ADVETA benchmark.Source: §5 · p. 6
- The evaluation uses a common prompt, while future work could investigate whether better prompts improve ChatGPT’s Text-to-SQL ability.Source: §5 · p. 6
6 Future work
Future work targets improving ChatGPT’s Text-to-SQL reliability and contextual generation through multi-turn interaction and more correlated in-context examples.