Exploring Chain of Thought Style Prompting for Text-to- SQL (2023)

Abstract: 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 and least-to-most prompting. Our experiments demonstrate that iterative prompting as in least-to-most prompting 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.

## introduction text-to-sql parsing, the task of mapping a natural language utterance to a sql query, has found wide applications in building language agents for databases and piqued significant research interest in recent years @xcite @xcite @xcite . to develop a text-to-sql parser, a prevalent approach is to collect labeled data and train a model via supervised learning @xcite . while effective, this approach necessitates a considerable amount of training data, which is costly to obtain because annotating sql queries requires programming expertise. as an alternative to supervised learning, incontext learning @xcite , an emergent capability of large language models (llms), alleviates the need for large-scale data. with only a few examples, in-context learning enables llms to demonstrate performance comparable to or even better than fully supervised models on many nlp tasks, such as question answering, machine translation, and natural language inference @xcite @xcite . when applied to text-to-sql parsing, in-context learning has also shown encouraging results @xcite @xcite , but there is still much room for improvement. we hypothesize that a crucial aspect of llms to improve for text-to-sql parsing is their multistep reasoning ability. even for a seemingly simple question, such as "what is the id of kyle," a model has to ground it to the given database schema, infer the relational algebra among schema items, and construct syntactically correct sql clauses. to enhance llms' reasoning capabilities, chain of thought (cot) style prompting methods @xcite are proposed and have shown promising results. however, how to apply cot-style prompting to text-to-sql parsing remains under-explored, and we fill this gap by systematically exploring cot-style prompting for textto-sql parsing. specifically, we seek to answer two research questions: (rq1) which prompting style is better, generating all reasoning steps in one pass, or iterative prompting and problem solving? (rq2) do more detailed reasoning steps lead to better results for text-to-sql parsing? to address these questions, we adapt two widely used prompting methods for text-to-sql parsing. as the first method, we apply chain-of-thought prompting @xcite by drawing an analogy between its problem-solving process and the execution procedure of a sql query (figure 1a ). referring to the logical execution order of sql clauses @xcite , we compose the intermediate execution steps in natural language and prompt llms to derive them before generating the sql query. for the second method, we follow @xcite to apply least-to-most prompting in two stages: (1) problem reduction: generate a series of sub-questions from the original question and (2) problem solving: iteratively translate each subquestion into its corresponding sql query, with the original question as the last sub-question, as shown in figure 1b . with a careful analysis (section 5.2), we find that directly applying these two methods for text-to-sql parsing tends to introduce error propagation issues frequently. also, the iterative process in least-to-most prompting incurs more computational costs to generate each sql query. therefore, we propose a new cot-style prompting method called question-decomposition prompting (qdecomp, figure 1c ). similar to chainof-thought prompting, qdecomp generates a sequence of reasoning steps followed by the natural language question in one pass. instead of generating the intermediate execution steps, we instruct llms to decompose the original complex question, akin to the problem reduction stage in least-to-most prompting. furthermore, to help llms ground database schemas, we design a variant of question decomposition prompting (qdecomp+intercol, figure 1d ) by incrementally including the table and column names involved in each sub-question. we conduct comprehensive evaluations on two cross-domain text-to-sql datasets, spider @xcite and spider realistic @xcite . compared to the standard prompting method without reasoning steps, qdecomp + in-tercol brings 5.2 and 6.5 point absolute gains on the spider development set and the spider realistic set, respectively. it also brings 2.4 and 1.5 point absolute gains compared to least-to-most prompting. our results suggest that it may be unnecessary to perform iterative prompting, which is also computationally costly (rq1). besides, our analysis shows that our qdecomp+intercol method reduces the chance of error propagation by providing less detailed reasoning steps and generating the sql query in one pass (rq2). meanwhile, it includes key schema information in reasoning, which is still beneficial to database grounding. further, we evaluate the robustness of our proposed methods by varying the number, selection, and format of in-context examples, providing useful guidelines for designing text-to-sql prompting strategies. we also extend our evaluation to three single-domain datasets @xcite @xcite and show our proposed method can achieve strong performance consistently across different datasets. the method first prompts llms to generate a list of sub-questions as a decomposition of the given problem. then, it uses the sub-questions to guide llms to incrementally solve each of them and derive a correct final answer. however, how to apply these two cot-style prompting methods to text-to-sql parsing remains under-explored. we fill this gap by systematically exploring several cot-style prompting methods for the task. in particular, we propose a new cot-style prompting method that guides llms to perform reasoning via question decomposition. question decomposition is a method that converts a complex problem into a sequence of simpler sub-questions @xcite . our work refers to existing question decomposition methods for textto-sql parsing @xcite and presents a novel cot-style prompting method to improve llms' performance. we conduct comprehensive experiments and show that our question decomposition prompting outperforms chain-of-thought prompting and least-to-most prompting on several text-to-sql datasets. our experiments validate our hypothesis that text-to-sql parsing indeed requires multi-step reasoning, and carefully designed cot-style prompting can help llms achieve higher parsing accuracy. text-to-sql semantic parsing. text-to-sql semantic parsing has long been studied to build language agents for database applications @xcite . since the release of spider @xcite , a cross-database text-to-sql benchmark, many parsers have been developed on top of language models to better understand various database schemas @xcite @xcite . recent work starts to explore the potential of llms, such as codex @xcite , in text-to-sql parsing by including database schemas in the prompts @xcite or retrieving similar questions as few-shot examples @xcite . orthogonal to these methods, our question decomposition prompting teaches llm to perform multistep reasoning for text-to-sql parsing without additional engineering efforts. with a few in-context examples, an llm, such as codex in our experiments, can learn to decompose natural language questions and predict table and column names (section 3) incrementally in each step. our method demonstrates comparable performance to rasat+picard @xcite , a fine-tuned text-to-sql parser, on the spider development set without using relational structures or constrained decoding. compared to other llmbased methods, it achieves better execution accuracy than din-sql @xcite on the spider development set in a single pass, while din-sql requires iterative prompting. although our method shows lower execution accuracy than lever @xcite , we note that lever's verifier model is fine-tuned on the full spider training set, which may have extra advantages over our method. also, lever uses the execution results of sql queries, which provides extra information for better database grounding. we leave the incorporation of database contents beyond table and column names into our method as future work. ## prompting for multi-step reasoning in text-to-sql in this section, we outline three cot-style prompting methods that teach an llm to perform multi-step reasoning. we first describe how we adapt chain-of-thought and least-to-most prompting for text-to-sql parsing. then, we propose a novel prompting method, question decomposition prompting (qdecomp), and its variant qdecomp+intercol. figure 1 demonstrates different prompting methods, and we provide more examples in appendix a. for all experiments, we use codex @xcite , code-davinci-002, as the llm. the experiments were conducted between january and march 2023 through openai api, using greedy decoding with temperature 0. ## results and analysis by analyzing our experimental results, we seek to answer the following two research questions: