DIN-SQL: Decomposed In-Context Learning of Text-to-SQL with Self-Correction

1 Introduction

• Prompted LLMs lag fine-tuned methods on Spider, especially for medium and complex queries [Scholak et al., 2021, Li et al., 2023a].Table 1 compares zero-shot and few-shot prompting with fine-tuned approaches on Spider’s development set. The paper investigates where LLMs fail and whether mitigating those failures can reach or surpass fine-tuned SOTA.Source: §1 · pp. 1–2, Table 1 · p. 2
• Prompting avoids requiring large task-specific training datasets, motivating an alternative to resource-intensive pretraining or fine-tuning.Training and fine-tuning can require substantial training samples and machine resources that may be unavailable.Source: §1 · p. 2
• SQL decomposition is challenging because the language is declarative and its query clauses have complex relationships.The approach builds on multistep reasoning techniques that reuse intermediate results: chain-of-thought [Wei et al., 2022b], least-to-most [Zhou et al., 2022], and decomposed prompting [Khot et al., 2022].Source: §1 · p. 2
• 85.3% execution accuracy on Spider’s holdout test set with GPT-4 exceeds the previous 79.9% SOTA [Li et al., 2023a].CodeX Davinci achieves 78.2% execution accuracy; exact set match accuracy is 60% with GPT-4 and 57% with CodeX Davinci, versus the previous 74% SOTA [Li et al., 2023b]. The paper attributes the execution–exact-match gap to in-context prompting, contrasting it with trained models’ exposure to queries sharing the test set’s composition style.Source: §1 · p. 2
• 55.9% execution accuracy establishes a new BIRD holdout-test SOTA with GPT-4, which also achieves 50.72% development execution accuracy.The method also improves the development-set valid efficiency score by 9% over a GPT-4 baseline.Source: §1 · p. 2
• DIN-SQL decomposes text-to-SQL into subtasks, adapts prompting to task complexity, addresses schema linking, and uses LLM self-correction.The method substantially outperforms ordinary few-shot prompting and is evaluated using CodeX Davinci, CodeX Cushman, and GPT-4.Source: §1 · p. 2

• Sequence-to-sequence models [Sutskever et al., 2014] jointly encode a natural-language question and database schema, then use a decoder to predict SQL.Source: §2 · p. 3
• Encoder approaches represent questions and schemas using bidirectional LSTMs, convolutional networks, pretrained language models, or graph neural networks.Gan et al. [2021] introduce an intermediate representation bridging natural language and SQL. TaBERT [Yin et al., 2020], TaPas [Herzig et al., 2020], and Grappa [Yu et al., 2020] encode both tables and text.Source: §2 · p. 3
• Decoder methods divide into sketch-based slot filling and generation-based approaches [Qin et al., 2022].Sketch-based methods aggregate slot predictions but cannot generalize beyond predefined templates [Hwang et al., 2019, Xu et al., 2017, Hui et al., 2021]. Generation-based methods decode SQL as an abstract syntax tree [Guo et al., 2019, Wang et al., 2019, Cao et al., 2021, Huang et al., 2021].Source: §2 · p. 3
• Rajkumar et al. [2022] and Liu et al. [2023a] evaluate zero-shot LLM prompting for text-to-SQL on Spider using different prompts.Prompting also supports table understanding, table reasoning, and table-to-text generation, with notable results from small numbers of prompt examples [Guo et al., 2023, Chen, 2022].Source: §2 · p. 3

JOIN

• JOIN errors formed the second-largest category, arising when the model failed to identify all required tables or the correct foreign keys.Source: §3 · p. 3

GROUP BY

• GROUP BY failures involved either overlooking the need for grouping or selecting incorrect grouping columns.Source: §3 · p. 3

Schema linking

• Schema linking was the largest failure category, involving missed column names, table names, or entities, and confusion between column names and required aggregation functions.The analysis excluded databases used in prompts and manually classified queries whose execution results differed from gold-query results.Source: Figure 1 · p. 3, §3 · p. 3

Queries with nesting and set operations

• Nesting and set-operation failures involved missing a required nested structure or choosing an incorrect nesting or set operation.Source: §3 · p. 4

Invalid SQL

• Invalid SQL accounted for a small set of generated statements with syntax errors that prevented execution.Source: §3 · p. 4

Miscellaneous

• Miscellaneous failures included extra or missing predicates, missing or redundant DISTINCT or DESC keywords, absent WHERE clauses, and redundant aggregation functions.Source: §3 · p. 4

4 Methodology

• Four prompt-based modules structure DIN-SQL: schema linking, query classification and decomposition, SQL generation, and self-correction.Solutions to sub-problems support construction of the final query. Few-shot demonstrations come from the respective benchmarks’ training sets.Source: §4 · p. 4

4.1 Schema Linking Module

• Schema linking identifies relevant columns, their tables, entities, and cell values, addressing the largest category of observed LLM failures.Its step-by-step prompt uses ten randomly selected Spider training examples.Source: §4.1 · p. 5

4.2 Classification & Decomposition Module

• Classification assigns queries to easy, non-nested complex, or nested complex classes, selects generation prompts, and identifies join tables and nested sub-queries.Easy queries require neither joins nor nesting; non-nested complex queries require joins without sub-queries. Nested complex queries can contain joins, sub-queries, and set operations.Source: §4.2 · p. 5

4.3 SQL Generation Module

• SQL generation uses direct few-shot prompting for easy queries and NatSQL [Gan et al., 2021] intermediate representations for non-nested complex queries requiring joins.NatSQL [Gan et al., 2021] builds on SemQL [Guo et al., 2019], which removes JOIN ON, FROM, and GROUP BY and merges HAVING with WHERE. NatSQL [Gan et al., 2021] additionally removes set operators, simplifying the transition from natural language to SQL.Source: §4.3 · p. 6
• Nested-query generation first solves sub-queries identified during decomposition, then uses their answers, schema links, and a NatSQL intermediate representation to generate final SQL.This class accommodates multiple joins, nesting, and set operations such as EXCEPT, UNION, and INTERSECT.Source: §4.3 · p. 6

4.4 Self-correction Module

• Zero-shot self-correction repairs generated SQL using gentle prompts for GPT-4 and generic prompts for CodeX by default.It targets missing or redundant keywords and aggregation functions using only the generated SQL as input. Generic prompts label SQL buggy, whereas gentle prompts check potential issues and provide clause hints; evaluations favor generic for CodeX and gentle for GPT-4.Source: §4.4 · p. 6

5.1 Models

• Experiments evaluated CodeX Davinci, CodeX Cushman, and GPT-4.Few-shot comparisons incorporated examples from all three difficulty classes; Cushman’s smaller context window limited its prompt to two examples per class, six total.Source: §5.1 · p. 6, §5.5.2 · p. 8

5.2 Hyperparameter

• CodeX Davinci, CodeX Cushman, and GPT-4 were accessed through the OpenAI API with greedy decoding at temperature zero.Maximum output lengths were 350 tokens for self-correction and 600 for other modules; stopping sequences were “#;\n \n” and “Q:”, respectively.Source: §5.1 · p. 6, §5.2 · p. 7

5.3 Dataset

• Evaluation used cross-domain Spider and BIRD datasets, with BIRD prompts incorporating table sample rows and external-knowledge hints.Spider’s training, development, and test databases were nonoverlapping. BIRD’s more intricate queries, sample rows, external knowledge, and limited context windows required fewer prompt demonstrations.Source: §5.3 · p. 7

5.5.1 Test set results

• 55.9% test execution accuracy established a new BIRD SOTA with GPT-4; DIN-SQL also achieved the highest then-published Spider test execution accuracy.On Spider, CodeX Davinci ranked third in execution accuracy; these results used no database content, and exact-set-match accuracy was comparable to previous methods without database content.Source: Table 2 · p. 7, §5.5.1 · p. 8, Table 3 · p. 9

5.4 Metrics

• Spider evaluation used exact-set-match accuracy and execution accuracy; BIRD used execution accuracy and valid efficiency score.Exact-set-match compares query components against one reference without considering values, whereas execution accuracy compares outputs and accommodates alternative valid SQL queries. Valid efficiency score combines correctness with execution-time efficiency.Source: §5.4 · pp. 7–8

5.5.3 Error improvements

• 500 shared training queries showed improvements across all error categories, largest for JOIN and Nested errors, while schema linking remained the largest failure category.The comparison reran DIN-SQL on the same randomly selected queries previously analyzed using basic few-shot prompting.Source: Figure 4 · p. 8, §5.5.3 · p. 9

5.5.2 Development set results

• At least 10% execution-accuracy improvement over few-shot prompting held across all evaluated models on Spider’s development set.DIN-SQL outperformed zero-shot and few-shot prompting on both Spider metrics across models, with the largest difficulty-level gains on hard and extra-hard queries; BIRD development gains over the GPT-4 baseline Li et al. [2023c] were 4% in execution accuracy and 9% in valid efficiency score, establishing a new SOTA. Test performance exceeded development performance on both datasets, with reduced database-content dependence and schema ambiguity offered only as possible explanations.Source: §5.5.2 · p. 8, Table 4 · p. 9, Table 3 · p. 9

5.6 Ablation study

• Removing any of DIN-SQL’s four modules reduced CodeX Davinci’s development execution accuracy.Schema linking helped every class, least for hard queries, explained easy-class gains, and sometimes introduced redundant joins or columns; removing classification retained schema linking, self-correction, and NatSQL, with fixed decomposed chain-of-thought favoring hard/extra-hard queries and simple few-shot favoring easy queries. Generic self-correction helped Davinci across classes more than gentle correction, but could hurt GPT-4, whose gentle correction improved every class except easy.Source: §5.6 · pp. 9–10, §5.5.2 · p. 9, Table 5 · p. 10

• Across Spider and BIRD, decomposition significantly improves prompting across all query classes, producing results comparable or superior to state-of-the-art fine-tuned approaches.Source: §6 · p. 10
• The proposed decomposition technique addresses challenges underlying the performance gap between prompted LLMs and models fine-tuned for text-to-SQL.Source: §6 · p. 10
• Prompting enables strong performance across NLP domains without large training sets, but previously underperformed task-specific fine-tuning for text-to-SQL.Source: §6 · p. 10

7 Limitations

• Approximately $0.5 and 60 seconds are required per Spider natural-language question using GPT-4 with the decomposed, step-by-step approach, as reported at writing.The authors anticipate that advancing LLMs will lower these costs and latencies, while identifying cost reduction as a research direction.Source: §7 · p. 10
• Manually constructed demonstrations are fixed for each query class.This limits the granularity at which demonstrations are tailored.Source: §7 · p. 10
• Future research could explore adaptive, automated demonstration generation at finer granularities to improve performance.Source: §7 · p. 10

A Prompts

• Prompts are provided for all four methodology modules on both GPT-4 and CodeX.The module prompts are documented in detail to support replication and understanding of the approach.Source: A Prompts · p. 15
• Few-shot and zero-shot implementation prompts are also included.Few-shot examples for Non-Nested Complex and Nested Complex queries use NatSQL intermediate representations from the NatSQL Github repository 2, which provides representations for all Spider training queries.Source: A Prompts · p. 15
• Detailed prompt documentation is intended to make the approach easier to replicate and understand.Source: A Prompts · p. 15

A.1 Zero-shot prompting

• The zero-shot prompt draws inspiration from Liu et al. [2023a].Source: §A.1 · p. 16
• The prompting approach of Liu et al. [2023a] was proposed for ChatGPT.Source: §A.1 · p. 16
• Figure 5 presents an example of the zero-shot prompting used in this work.Source: §A.1 · p. 16, Figure 5 · p. 16

A.2 Few-shot prompting

• The prompt lists university database tables and their columns, then pairs natural-language questions with SQL answers.The schema includes students, instructors, departments, courses, classrooms, prerequisites, and teaching and enrollment records.Source: §A.2 · pp. 16–17
• Filtering demonstrations use BETWEEN for room capacities, LIKE for instructor-name matching, and semester/year conditions with COUNT(DISTINCT ID) for instructor counts.Examples specify capacities from 50 to 100, names containing Soisalon, and instructors teaching in Spring 2010.Source: §A.2 · pp. 16–17
• Ordering demonstrations cover ascending student credits, highest-credit students, highest-budget departments, and the three departments offering the most courses.The examples combine ORDER BY with descending order and LIMIT for maximum and top-three selections.Source: §A.2 · pp. 16–17
• Grouped aggregation demonstrations use GROUP BY with COUNT, MAX, AVG, and HAVING to summarize buildings and filter departments.Examples select departments with average salaries above 42000 and count rooms with capacity above 50 in each building.Source: §A.2 · p. 17
• Join demonstrations retrieve prerequisite-constrained courses, pair course titles with instructor names, and locate courses taught in the largest-capacity classroom.The prerequisite example uses HAVING count(*) = 2; the classroom example joins on both building and room number and uses a maximum-capacity subquery.Source: §A.2 · pp. 17–18
• Advanced demonstrations combine nested aggregate comparisons, NOT IN exclusion, and EXCEPT to express department salary conditions, prerequisite absence, and teaching-period differences.One query returns departmental minimum salaries where departmental average salaries exceed the overall instructor average; another excludes Spring 2010 instructors from Fall 2009 instructors.Source: §A.2 · p. 18

A.3 Schema linking prompt

• The competition-count example selects city.Status, farm_competition.*, and the host-city join to represent the question about the city hosting the most competitions.The join is farm_competition.Host_city_ID = city.City_ID; the possible cell-value set is empty.Source: §A.3 · p. 19
• Each demonstration identifies columns from question phrases, determines required foreign-key links and possible cell values, and combines them into Schema_links.The course example links the requested title, building, semester, and year to schema columns before assembling the output.Source: §A.3 · pp. 19, 21
• Literal extraction includes location and temporal values, including both periods in a question asking who taught in one semester but not another.The course example extracts Chandler, Fall, and 2010; the instructor example extracts Fall, 2009, Spring, and 2010 alongside teaches.id, teaches.semester, and teaches.year.Source: §A.3 · pp. 21–22
• Schema_links can contain only columns when the example requires neither foreign-key links nor literal values.The accounts example returns Accounts.account_id, Accounts.account_name, Accounts.other_account_details, and Accounts.date_account_opened, with both intermediate sets empty.Source: §A.3 · p. 22

A.4 Classification & decomposition prompt

• EASY requires neither JOINs nor nesting; NON-NESTED requires JOINs without nesting; NESTED applies whenever nested queries are required.Nesting determines the NESTED label regardless of whether a JOIN is needed.Source: §A.4 · pp. 23, 25, 26
• NESTED examples extract explicit subquestions even for single-table requests involving departmental overlap, semester exclusion, or comparison with an average.Extracted subquestions ask for Psychology course titles, instructors who taught in Spring 2010, or the average departmental budget.Source: §A.4 · pp. 24–26
• Step-by-step examples use schema links to identify required tables, assess JOIN and nesting needs, and emit a classification label.The instructor-advising example identifies advisor, instructor, and student as required tables and receives the NON-NESTED label.Source: §A.4 · p. 25

A.5.1 Easy Class

• Easy-class prompts generate SQL directly from schema links, with examples covering filtering, sorting, grouped aggregation, and top-k selection.Examples use DISTINCT for buildings, ORDER BY for student credits, and GROUP BY with HAVING for departmental average salaries. Top departments are selected by summed credits or course counts.Source: §A.5.1 · pp. 26–27

A.5.2 Non-Nested Complex

• Non-nested-complex prompts identify tables to join, create an intermediate representation, and translate it into SQL using schema links.The prompt supplies table schemas and foreign keys. Examples translate intermediate representations into explicit joins, distinct counts, grouped HAVING conditions, and ordered results.Source: §A.5.2 · pp. 27–29

A.5.3 Nested Complex

• Nested-complex prompts explicitly solve a sub-question in SQL before presenting an intermediate representation and the final query.Examples embed subqueries in scalar comparisons, IN/NOT IN predicates, and HAVING conditions. Final queries can combine these subqueries with joins, as illustrated by courses taught in the largest classroom.Source: §A.5.3 · pp. 30–32

A.6.1 Generic self-correction prompt

• Generic self-correction assumes every query is “Buggy SQL.”Source: §A.6.1 · p. 33
• Generic self-correction is implemented in a zero-shot setting.Source: §A.6.1 · p. 33

A.6 Self-correction prompts

• Figures 6 and 7 illustrate examples of the Generic and Gentle self-correction prompts, respectively.Source: Figure 6 · p. 33, Figure 7 · p. 34

A.6.2 Gentle self-correction prompt

• Gentle self-correction does not assume that SQL queries are buggy.Source: §A.6.2 · p. 34
• Gentle self-correction includes instructions for fixing SQL queries.Source: §A.6.2 · p. 34
• Gentle self-correction is implemented in a zero-shot setting.Source: §A.6.2 · p. 34