PaperlayerView exact revision on arXiv

Source-linked AI summary

ACT-SQL: In-Context Learning for Text-to-SQL with Automatically-Generated Chain-of-Thought

Hanchong Zhang, Ruisheng Cao, Lu Chen, Hongshen Xu, Kai Yu

arXiv:2310.17342v1cs.CL

TL;DR

Text-to-SQL in-context methods can have weaker performance or require multiple API calls, while CoT prompts lack an automated generation approach. ACT-SQL automatically generates CoT exemplars and extends the approach to multi-turn text-to-SQL; it reaches state-of-the-art among in-context learning methods on Spider dev and comparable accuracy to finetuned models on SParC and CoSQL.

  • Problem

    Text-to-SQL in-context approaches may perform worse than finetuned models or require multiple LLM API calls, and CoT methods require manual labeling without an automated generation approach.

    Source: §1 · pp. 1–2

  • Method

    ACT-SQL automatically generates CoT exemplars for in-context text-to-SQL, and the authors extend their approach to multi-turn datasets.

    Source: §1 · p. 2, §5 · p. 9

  • Results

    ACT-SQL achieves state-of-the-art performance among in-context learning methods on Spider dev and comparable accuracy to finetuned models on SParC and CoSQL.

    Source: §1 · p. 2

  • Takeaways & Limitations

    The authors report that ACT-SQL uses only one LLM API call to generate a SQL query and is relatively fast and cheap.

    Source: §1 · p. 2, §5 · p. 9

    The hybrid exemplar-selection strategy has manually set static and dynamic exemplar counts, and the authors report relatively poor scores on some Spider robustness variants and multi-turn datasets.

    Source: Limitations · p. 9

Abstract

from arXiv · show

Recently Large Language Models (LLMs) have been proven to have strong abilities in various domains and tasks. We study the problem of prompt designing in the text-to-SQL task and attempt to improve the LLMs' reasoning ability when generating SQL queries. Besides the trivial few-shot in-context learning setting, we design our chain-of-thought (CoT) prompt with a similar method to schema linking. We provide a method named ACT-SQL to automatically generate auto-CoT exemplars and thus the whole process doesn't need manual labeling. Our approach is cost-saving since we only use the LLMs' API call once when generating one SQL query. Furthermore, we extend our in-context learning method to the multi-turn text-to-SQL task. The experiment results show that the LLMs' performance can benefit from our ACT-SQL approach. Our approach achieves SOTA performance on the Spider dev set among existing in-context learning approaches.

1 Introduction

The paper addresses the cost and complexity of text-to-SQL systems and gaps in in-context learning prompts, including the lack of an automated way to generate CoT sequences. It introduces ACT-SQL and extends its in-context learning approach to multi-turn tasks.

1 Introduction

  • Existing text-to-SQL models require many high-quality training examples and substantial finetuning time, and their complex structures can make deployment challenging.LLMs’ zero- or few-shot in-context learning can avoid extensive finetuning on abundant examples.Source: §1 · p. 1
  • Prior text-to-SQL in-context approaches either perform relatively poorly or use multiple LLM API calls, while existing CoT methods require time-consuming example selection and manual labeling.The cited work also had not extended in-context learning methods to multi-turn datasets such as SParC, CoSQL, and DIR.Source: §1 · pp. 1–2
  • The paper studies database-schema prompt formats and hybrid exemplar selection alongside automatically generated CoT prompts for text-to-SQL.Source: §1 · p. 2
  • ACT-SQL achieves state-of-the-art performance on the Spider dev set among existing in-context learning methods while avoiding extra LLM API calls.The authors also describe the method as time-saving and cost-saving.Source: §1 · p. 2
  • The authors extend their approach to SParC and CoSQL, reporting performance comparable to finetuned models.Source: §1 · p. 2

2 Related Work

Related work contrasts specialized text-to-SQL models with in-context learning approaches. The former can perform well but demand substantial training resources; the latter vary in performance and inference cost.

Text-to-SQL models

  • RATSQL and LGESQL encode and decode SQL abstract syntax trees using predefined grammar, but AST-based approaches are generally complex to deploy.PICARD instead rejects invalid tokens during decoding to constrain outputs to a specified space.Source: §2 · p. 2
  • Specialized text-to-SQL models need abundant high-quality training examples, whose construction and labeling consume time and resources, as does model training and finetuning.Source: §2 · p. 2

In-context learning for text-to-SQL

  • Rajkumar et al. (2022) evaluate LLM text-to-SQL prompting but report performance behind finetuned models, while Nan et al. (2023) require a difficulty predictor and repeated API calls for majority voting.Source: §2 · pp. 2–3
  • DIN-SQL decomposes text-to-SQL into simpler subproblems and achieves the best Spider test-set score with GPT-4, but its multiple API calls make query generation slow and expensive.Source: §2 · p. 3

3 Methodology

The methodology frames text-to-SQL generation as an LLM prompt containing instructions, a database schema, a question, and optional exemplars. It studies schema formatting and exemplar selection before introducing ACT-SQL.

3 Methodology

  • SQL generation is formulated as S = LLM(I, D, Q, E), with performance influenced by database prompt style, exemplar selection, and exemplar prompt design.I is the instruction; D the database schema; Q the question; E the list of exemplars, each pairing a schema, question, and answer prompt containing the correct SQL.Source: §3 · p. 3

3.1 Database Prompt Style

  • Under zero-shot prompting, the schema and question are inputs, so the authors study how database-schema formatting influences LLM performance.Source: §3.1 · p. 3
  • The five schema styles include Table(Column), which lists each table with its columns, and Table(Column)(PF), which adds primary and foreign keys.The paper says Table(Column) follows OpenAI’s official document.Source: §3.1 · p. 3

3. Create(NoPF)

The section describes schema formatting, hybrid exemplar selection, automatic CoT construction, and a conversion step for multi-turn questions. Together these methods define how the paper builds prompts for single-turn and multi-turn text-to-SQL.

3. Create(NoPF)

  • Create(NoPF) describes tables and columns with SQL CREATE TABLE statements, including column types but omitting primary- and foreign-key information.Create(EoC) adds those keys after the corresponding columns, while Create(EoT) adds them at the end of each table; database contents may also be appended as example rows.Source: §3 · p. 3

3.2 Exemplar Selection

  • The hybrid strategy combines random static exemplars used for every test case with dynamic exemplars selected for each case by question similarity.The authors state exemplar selection can affect LLM performance; dynamic examples are the top-nd training questions by similarity.Source: §3.2 · pp. 3–4

3.3 Chain-of-Thought Prompt Design

  • ACT-SQL automatically generates CoT prompts for training examples by linking SQL schema items to relevant question slices and including query values.It identifies column slices by similarity, ignores GROUP BY columns, and links relevant slices for tables appearing in the FROM clause; this addresses the time cost and static-only nature of manual CoT labeling.Source: §3.3 · pp. 4–5

3.4 Extension for Multi-turn Text-to-SQL

  • For multi-turn tasks, the authors use LLMs to rewrite context-dependent questions into independent examples, then apply the single-turn in-context method.Because auto-CoT relies on question slices and multi-turn information may span context-dependent sentences, that method is disabled in this setting; rewriting quality affects performance, so the authors manually label 10 rewriting examples per dataset.Source: §3.4 · p. 5

4 Experiments

Experiments evaluate ACT-SQL across single-turn Spider and related variants, as well as multi-turn SParC and CoSQL. ACT-SQL achieves strong Spider results, while its multi-turn performance is less effective and remains comparable to a finetuned baseline.

4.1 Experiment Setup

  • Experiments mainly use GPT-3.5-turbo on Spider dev and related variants, plus SParC and CoSQL, with EM, EX, TS, QM, and IM metrics.GPT-4 is also used to evaluate auto-CoT on Spider.Source: §4.1 · pp. 5–6

4.2 Zero-shot Results

  • With Table(Column) prompts, three database-content rows yield the best zero-shot score; no content gives the lowest score, and additional content does not help.The authors describe database contents as useful clues for value-sensitive questions and SQL structure.Source: Figure 3 · p. 6, §4.2 · p. 6

4.3 Few-shot Results

  • With GPT-4, ACT-SQL uses one API call per SQL query, attains the highest EM, EX, and TS scores among in-context methods, and is comparable to finetuned models.The comparison is on Spider dev and uses the Create(EoT) database style with ns = nd = 2.Source: Table 2 · p. 7, §4.3 · p. 7

4.2 Zero-shot Results

  • In zero-shot experiments, Table(Column) and Table(Column)(PF) score higher than the other three database styles.Create(EoC) and Create(EoT) outperform Create(NoPF) on EX and TS; the authors point to primary and foreign keys as useful information.Source: §4.2 · pp. 6–7, Table 3 · p. 7

4.3 Few-shot Results

  • Few-shot exemplars increase Spider EM scores, while TS scores improve by 1%-3% except with Table(Column), where primary and foreign keys become more important.The authors attribute the EM gains to shared SQL grammar and structure between examples from the same dataset.Source: §4.3 · p. 7
  • With GPT-3.5-turbo and Create(EoT), ACT-SQL achieves the best Spider dev EM score of 62.7% and TS score of 71.4%.Its generated reasoning clarifies relevant tables and columns, avoiding a redundant schema item in the cited case study.Source: §4.3 · p. 7

4.4 Multi-turn Datasets Results

  • On SParC and CoSQL, ACT-SQL is comparable to GAZP + BERT, but the authors report weaker effectiveness when question rewriting impairs schema linking.One cited rewritten example retains context dependency, which carries into a later question.Source: Table 8 · p. 8, Table 9 · p. 8, §4.4 · p. 8

5 Conclusion

The paper proposes ACT-SQL, which automatically generates chain-of-thought prompts for text-to-SQL and uses one LLM API call per SQL query. It reports state-of-the-art performance on Spider dev among existing in-context learning approaches and extends the method to multi-turn datasets.

5 Conclusion

  • ACT-SQL achieves state-of-the-art performance on the Spider dev set among existing in-context learning approaches.The approach uses one LLM API call to generate each SQL query.Source: §5 · p. 9
  • The paper proposes automatically generated chain-of-thought prompts through its ACT-SQL approach.The approach requires no manual labeling of the auto-CoT exemplars.Source: §5 · p. 9
  • The authors extend their in-context learning approach to multi-turn text-to-SQL datasets.Source: §5 · p. 9

Limitations

The paper identifies manual exemplar-count tuning and limited performance on some robustness variants and multi-turn datasets as limitations. It names exploration of these datasets as future work.

Limitations

  • The numbers of static and dynamic exemplars are hyperparameters that still require manual determination.Source: Limitations · p. 9
  • The hybrid exemplar-selection strategy is relatively simple and still needs improvement.Source: Limitations · p. 9
  • The approach achieves relatively poor scores on some Spider robustness variants and some multi-turn text-to-SQL datasets.Source: Limitations · p. 9
  • The authors identify exploration of these datasets as future work.Source: Limitations · p. 9

A Detailed Experiment Results

The appendix tables describe few-shot and ACT-SQL performance evaluations on the Spider dev set.

A Detailed Experiment Results

  • Table 11 covers GPT-3.5-turbo few-shot performance on Spider dev across database styles and numbers of static and dynamic exemplars.Source: Table 11 · p. 12
  • The supplied table descriptions identify evaluation scope but do not state comparative scores.Source: Table 11 · p. 12, Table 12 · p. 12
  • Table 12 presents detailed ACT-SQL performance results on Spider dev.Source: Table 12 · p. 12

B Auto-CoT by LLM

The appendix includes an ACT-SQL case study whose entire output is generated by the LLM.

B Auto-CoT by LLM

  • Table 13 is a case study for the ACT-SQL method.Source: Table 13 · p. 12
  • The table's case-study output is generated entirely by the LLM using ACT-SQL.Source: Table 13 · p. 12
  • The table description identifies the example as an ACT-SQL output, without reporting a performance comparison.Source: Table 13 · p. 12

C Prompts

This appendix presents the prompts used in the experiments, spanning schema formats, database contents, and zero-shot, few-shot, and Auto-CoT examples, including illustrations of how schema cues connect questions to generated SQL.

C.1.1 Table(Column) / C.1.2 Table(Column)(PF)

  • The schema examples list the same four tables and their columns; the second additionally specifies primary keys for all four tables and foreign-key relationships linking concerts to stadiums and singers in concerts to singers and concerts.Source: §C.1.1 · p. 13, §C.1.2 · p. 13

C.1.3 Create(NoPF)

  • The Create(NoPF) examples express schemas as table definitions without explicit key constraints.The fragments define columns and types for stadium, singer, concert, and singer_in_concert.Source: §C.1.3 · p. 13

C.1.4 Create(EoC)

  • Create(EoC) examples include primary keys and references inline in the table definitions.The supplied fragments show keys and foreign-key references for the stadium, singer, concert, and singer_in_concert tables.Source: §C.1.4 · pp. 13–14

C.1.5 Create(EoT)

  • Create(EoT) examples encode keys and references as table-level constraints.The fragments show primary-key and foreign-key declarations across the example tables.Source: §C.1.5 · pp. 14–15

C.2 Database Contents / C.2.1 Table(Column) / C.2.2 Create(EoT)

  • The database-content examples use three rows and select Table(Column) and Create(EoT) styles for the prompt examples.The appendix says the other database styles are similar and illustrates the selected styles with stadium, singer, concert, and singer_in_concert rows.Source: §C.2 · p. 15, §C.2.1 · p. 15, §C.2.2 · pp. 16–17

C.3.2 Few-shot

  • Few-shot prompts use four shots, with the first two static and the last two dynamic, and show SQL exemplar answers.The examples include queries for account balances, flight destinations, female professors, and party counts.Source: §C.3 · p. 17, §C.3.2 · pp. 19, 21, 22, 24

C.3 In-context Learning Prompts / C.3.1 Zero-shot

  • The zero-shot example supplies a schema with three sample rows and asks, “How many singers do we have?”Its system instruction is to translate the question into an SQL query.Source: §C.3.1 · pp. 17–18

C.3.3 Auto-CoT

  • The Auto-CoT exemplar links question phrases to schema columns and values before giving the final SQL query.For the savings-balance question, it identifies SAVINGS.balance, ACCOUNTS.name, and value [3] before producing the query.Source: §C.3.3 · p. 26
  • For the flight question, the exemplar identifies `flight.destination` and values `[1]`, then groups destinations and orders by flight count to return the least frequent one.SQL: SELECT destination FROM Flight GROUP BY destination ORDER BY count(*) LIMIT 1Source: §C.3.3 · p. 28
  • For counting dorms with amenities, the exemplar selects `Has_amenity.dormid` and counts distinct dorm IDs.SQL: SELECT count(DISTINCT dormid) FROM has_amenitySource: §C.3.3 · p. 29
  • For counting different dorm amenities, the exemplar identifies `Dorm_amenity` as the relevant table and counts its rows.SQL: SELECT count(*) FROM dorm_amenitySource: §C.3.3 · p. 31
Loading 2310.17342v1…