Source-linked AI summary
Exploring Chain-of-Thought Style Prompting for Text-to-SQL
Chang-You Tai, Ziru Chen, Tianshu Zhang, Xiang Deng, Huan Sun
TL;DR
Text-to-SQL prompting with LLMs still has room to improve, and the paper examines whether CoT-style reasoning can help. It proposes one-pass question decomposition, with schema cues in a variant, and reports gains over standard and least-to-most prompting on Spider datasets.
Problem
In-context learning has shown encouraging text-to-SQL results, but parsing still has room to improve, and the application of CoT-style prompting remains under-explored.
Source: §1 · p. 1Method
The paper adapts chain-of-thought and least-to-most prompting, then proposes one-pass question decomposition and a variant that incrementally includes relevant table and column names.
Source: §1 · p. 2Results
QDecomp+InterCOL gains 5.2 and 6.5 points over standard prompting on Spider development and Spider Realistic, respectively, and 2.4 and 1.5 points over least-to-most.
Source: §1 · p. 2, §5.1 · p. 6Takeaways & Limitations
The authors conclude that iterative prompting may be unnecessary for text-to-SQL, while detailed reasoning steps or intermediate SQL queries can aggravate error propagation.
Source: §6 · p. 9The study uses Codex; whether the prompting gains hold for more recent, more powerful LLMs remains for future research.
Source: Limitations · pp. 9–10
Abstract
from arXiv · showhide
In-context learning with large language models (LLMs) has recently caught increasing attention due to its superior few-shot performance on various tasks. However, its performance on text-to-SQL parsing still has much room for improvement. In this paper, we hypothesize that a crucial aspect of LLMs to improve for text-to-SQL parsing is their multi-step reasoning ability. Thus, we systematically study how to enhance LLMs' reasoning ability through chain of thought (CoT) style prompting, including the original chain-of-thought prompting (Wei et al., 2022b) and least-to-most prompting (Zhou et al., 2023). Our experiments demonstrate that iterative prompting as in Zhou et al. (2023) may be unnecessary for text-to-SQL parsing, and using detailed reasoning steps tends to have more error propagation issues. Based on these findings, we propose a new CoT-style prompting method for text-to-SQL parsing. It brings 5.2 and 6.5 point absolute gains on the Spider development set and the Spider Realistic set, respectively, compared to the standard prompting method without reasoning steps; 2.4 and 1.5 point absolute gains, compared to the least-to-most prompting method.
1 Introduction
The paper investigates how chain-of-thought-style prompting can support multi-step reasoning in text-to-SQL and proposes question-decomposition methods. QDecomp+InterCOL improves on standard and least-to-most prompting on Spider development and Spider Realistic.
1 Introduction
- Supervised text-to-SQL parsing requires considerable labeled data, while in-context learning uses a few examples but still has room to improve on this task.Annotating SQL queries is costly because it requires programming expertise.Source: §1 · p. 1
- The paper asks whether text-to-SQL reasoning works better in one pass or iteratively, and whether more detailed reasoning steps improve results.The motivation is that parsing requires schema grounding, inferring relations among schema items, and constructing syntactically correct SQL clauses.Source: §1 · p. 1
- The authors adapt chain-of-thought and least-to-most prompting, but report that both can introduce error propagation; least-to-most also incurs extra computation through iteration.Chain-of-thought uses natural-language execution steps, while least-to-most decomposes a question and solves its sub-questions iteratively.Source: §1 · p. 2
- QDecomp generates question-decomposition steps and the natural-language question in one pass; QDecomp+InterCOL also incrementally includes relevant table and column names.The latter is designed to help LLMs ground database schemas.Source: §1 · p. 2
- QDecomp+InterCOL gains 5.2 and 6.5 points over standard prompting on Spider development and Spider Realistic, respectively, and 2.4 and 1.5 points over least-to-most prompting.The paper also reports strong performance across three single-domain datasets and robustness analyses varying in-context examples.Source: §1 · pp. 2–3
2 Related Work
Prior work established chain-of-thought and least-to-most prompting for complex reasoning, but their application to text-to-SQL remained under-explored. This paper studies question decomposition alongside existing LLM and text-to-SQL approaches.
2 Related Work
- Chain-of-thought prompts elicit intermediate reasoning steps, while least-to-most prompting decomposes a problem and solves its sub-questions incrementally; text-to-SQL applications remained under-explored.The paper addresses this gap by systematically exploring CoT-style methods for the task.Source: §2 · p. 3
- The paper introduces question-decomposition prompting and reports that it outperforms chain-of-thought and least-to-most prompting on several text-to-SQL datasets.The experiments support the authors’ hypothesis that text-to-SQL requires multi-step reasoning and that carefully designed CoT prompting can improve parsing accuracy.Source: §2 · p. 3
- Question decomposition is presented as orthogonal to schema prompting and retrieval of similar examples, teaching an LLM multi-step reasoning without additional engineering.With a few in-context examples, the LLM can learn to decompose questions and predict table and column names incrementally.Source: §2 · p. 3
- On Spider development, the method performs comparably to RASAT+PICARD without relational structures or constrained decoding, but LEVER uses a full-training-set verifier and query execution results.The authors leave incorporating database contents beyond table and column names as future work.Source: §2 · p. 3
3 Prompting for Multi-Step Reasoning in Text-to-SQL
The paper adapts chain-of-thought and least-to-most prompting to text-to-SQL, then proposes one-pass question decomposition and a variant that adds table and column cues. Experiments use Codex with in-context examples.
3 Prompting for Multi-Step Reasoning in Text-to-SQL
- Experiments use Codex, code-davinci-002, with greedy decoding at temperature 0; the section introduces three CoT-style methods and the standard-prompting baseline.The API experiments ran between January and March 2023.Source: §3 · pp. 3–4
3.1 Chain-of-Thought Prompting
- Chain-of-thought prompting describes SQL clauses in natural language and orders the reasoning steps by SQL’s logical execution procedure.The example proceeds from FROM to WHERE to SELECT.Source: §3.1 · p. 4
3.2 Least-to-Most Prompting
- Least-to-most prompting first generates sub-questions, then solves them one at a time, building the final solution iteratively.The original question is segmented at sentence boundaries, conjunctions, and prepositions, while information that could leak into later questions is removed.Source: §3.2 · p. 4
- In the example, removing “female” from the first sub-question lets the model produce SELECT and FROM before adding the WHERE condition for the follow-up.This illustrates how the second query builds on the SQL generated for the first sub-question.Source: §3.2 · p. 4
3.3 Question Decomposition Prompting
- QDecomp generates decomposition steps and the final SQL in one pass, using question sub-questions rather than SQL execution order as its reasoning structure.The design tests whether question decomposition helps and whether least-to-most’s iterative process is necessary.Source: §3.3 · p. 4
- QDecomp+InterCOL prompts the model to identify table-column names for each sub-question, excluding pairs already annotated in earlier steps.For star operators, the annotations sample a column from a table in the query; examples illustrate identifying new columns at each step.Source: §3.3 · pp. 4–5
- Standard prompting serves as the baseline, mapping a natural-language question directly to SQL from question-SQL examples without intermediate reasoning.Source: §3.3 · p. 5
4 Experimental Setup
The experiments evaluate Codex prompting on cross-domain text-to-SQL datasets using varied in-context examples and schema formats. Test-suite accuracy compares predicted and gold query results across multiple databases.
4.1 Datasets
- Spider provides a cross-database benchmark requiring generalization to novel schemas; the study evaluates its development set because the test set was unavailable.The benchmark has 7,000 training pairs and 1,034 development pairs across 200 databases and 138 domains.Source: §4.1 · p. 5
- Spider Realistic modifies Spider development questions by removing or paraphrasing explicit column-name mentions, yielding 508 question-query pairs.It is intended to reflect scenarios where questions rarely name columns explicitly.Source: §4.1 · p. 5
4.2 In-context Example Selection
- The main results use randomly selected in-context examples; difficulty-based alternatives sample equal difficulty groups, hard and extra-hard examples, or extra-hard examples only.Random selection uniformly samples Spider training examples, and the selection strategies are compared for robustness.Source: §4.2 · p. 5
4.3 Prompt Formats
- API Docs represents schemas with table and column names in shorter prompts, while Create Table + Select 3 includes types, foreign keys, and sample rows but uses longer prompts.API Docs performs competitively, so the experiments primarily use it for efficiency.Source: §4.3 · p. 5
4.4 Evaluation Metric
- Test-suite accuracy compares predicted and gold SQL execution across many databases, reducing false positives that can occur when standard execution accuracy tests only one database.The paper reports that standard prompting trails least-to-most in test-suite accuracy even though their standard execution accuracy results are very close; Table 1 reports 8-shot results and Table 2 reports component matching accuracy.Source: §4.4 · pp. 5–6, Table 1 · p. 6, Table 2 · p. 6
5 Results and Analysis
QDecomp prompting improves text-to-SQL results while experiments examine reasoning style, error propagation, and robustness across prompt designs and datasets. The results suggest iterative prompting may be unnecessary, while detailed reasoning steps can introduce errors that propagate.
5.1 Main Results
- 68.4% test-suite accuracy on Spider development and 56.5% on Spider Realistic; QDecomp+InterCOL gains 5.2 and 6.5 point absolute points over standard prompting, and 2.4 and 1.5 over least-to-most.QDecomp generates sub-questions and SQL in one pass, leading the authors to conclude that iterative prompting may be unnecessary for text-to-SQL.Source: §5.1 · pp. 6–7
5.2 Error Analysis
- QDecomp and QDecomp+InterCOL outperform other CoT-style methods across five SQL components, while detailed CoT steps and intermediate SQL can propagate errors.Least-to-most can fail on JOIN, GROUP BY, and ORDER BY clauses; avoiding detailed reasoning steps or intermediate SQL reduces the possibility of accumulating mistakes.Source: §5.2 · pp. 7–8
5.3 Robustness to Prompt Design
- QDecomp+InterCOL performs best across example-selection settings; G1 examples favor easy and medium queries, while G3 favors hard and extra-hard queries.QDecomp is the most robust to example count; least-to-most can underperform standard prompting with fewer than eight examples, and adding foreign-key and database-content information brings negligible improvement or slight decreases in the tested format.Source: §5.3 · pp. 8–9
5.4 Results on Other Text-to-SQL Datasets
- On GeoQuery, IMDB, and Yelp, QDecomp and QDecomp+InterCOL consistently achieve the best performance among the compared methods.The authors suggest iterative prompting and error propagation may relate to least-to-most underperforming standard prompting on IMDB and Yelp; QDecomp generates SQL after all sub-questions are derived, maintaining a global view of the reasoning steps.Source: §5.4 · p. 9
6 Conclusion and Future Work
The paper studies CoT-style prompting for text-to-SQL and proposes question-decomposition prompting. Its experiments suggest iterative prompting may be unnecessary and detailed reasoning steps can aggravate error propagation.
6 Conclusion and Future Work
- The experiments suggest iterative prompting may be unnecessary for text-to-SQL, while detailed reasoning steps or intermediate SQL queries can aggravate error propagation.The authors propose question-decomposition prompting as a way to mitigate this issue.Source: §6 · p. 9
- The authors identify error propagation in multi-step reasoning as a future research direction and suggest interactive semantic parsing could let users correct natural-language sub-questions.They propose integrating question decomposition into interactive semantic parsing frameworks.Source: §6 · p. 9
Limitations
The study's limitations include evaluation with Codex and robustness tests focused mainly on prompt design. The authors call for testing other LLMs and broader perturbations.
Experiments on other large language models
- The experiments use Codex, leaving the consistency of the prompting improvements across other, more recent LLMs undetermined.The authors name GPT-4 as an example for future study, if budget allows.Source: Limitations · pp. 9–10
Experiments on robustness
- Robustness testing mainly varies prompt design, including example selection, example count, and example format.The authors propose testing different databases, natural-language questions, and SQL perturbations.Source: Limitations · p. 10
A Example Prompts
The appendix presents example prompts for several text-to-SQL formats and reasoning methods. Examples include question decomposition, with and without incremental schema information.
A Example Prompts
- The appendix shows examples of API Docs and Create Table + Select 3 formats, as well as a standard API Docs prompt.The figure captions identify these formats and a two-shot standard prompt.Source: Figure 2 · p. 13, Figure 3 · p. 13, Figure 4 · p. 14
- Example prompts illustrate chain-of-thought and least-to-most approaches, including problem reduction and iterative problem solving.The least-to-most problem-solving example reuses a prompt for the next sub-question after generating SQL for the first.Source: Figure 5 · p. 15, Figure 6 · p. 16, Figure 7 · p. 16
- A sample question is decomposed into progressively more constrained requests, with later steps adding grant and organization-type conditions.The example also associates sub-questions with relevant SQL tables and columns.Source: A Example Prompts · pp. 17–18
- The appendix includes examples of QDecomp and QDecomp+InterCOL prompts.The InterCOL example includes database table and column information alongside decomposed sub-questions.Source: Figure 8 · p. 17, Figure 9 · p. 18, A Example Prompts · p. 18