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.
Method
The study evaluates ChatGPT on 12 Text-to-SQL benchmark datasets using standardized prompts and execution-based evaluation against established baselines.
Results
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.
Takeaways & Limitations
Zero-shot ChatGPT performs relatively well on robustness and compositional generalization benchmarks and can surpass SOTA models on ADVETA.
Takeaways & Limitations
The 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.
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.
- 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.
- Main findings: 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.
- Main findings: 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.
- 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.
- 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.
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.
- 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.
- 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.
- 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.
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.
- Experiment Setup: 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.
- 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.
- 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.
- Robustness: 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.
- 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.
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.
- Spider development errors include overly fine-grained JOINs, database-structure confusion, incorrect nested-query semantics, and value-copying mistakes.
- Robustness is evaluated under synonym substitutions, domain knowledge, omitted schema mentions, compositional generalization, and adversarial database modifications.
- 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.
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.
- ChatGPT performs relatively better on most robustness benchmarks and surpasses current SOTA models on the ADVETA benchmark.
- The evaluation uses a common prompt, while future work could investigate whether better prompts improve ChatGPT’s Text-to-SQL ability.
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.
- Multi-turn dialogues incorporating database error messages are proposed to reduce non-executable SQL statements and improve generated-query validity.
- Adding more highly correlated in-context examples to prompts is proposed to enhance Text-to-SQL generation.