Source-linked AI summary
DIN-SQL: Decomposed In-Context Learning of Text-to-SQL with Self-Correction
Mohammadreza Pourreza, Davood Rafiei
TL;DR
Fine-tuned models had outperformed prompting approaches on challenging text-to-SQL benchmarks, motivating a method that decomposes generation into smaller sub-tasks. DIN-SQL feeds intermediate sub-task solutions into LLM prompts and improves results across Spider and BIRD, reaching leading execution accuracy.
Problem
LLM prompting for text-to-SQL lagged behind fine-tuned models on challenging benchmarks such as Spider, particularly for medium and complex queries.
Method
DIN-SQL decomposes text-to-SQL into sub-tasks involving schema linking, query classification and decomposition, SQL generation, and self-correction.
Results
DIN-SQL achieves 85.3% Spider holdout-test execution accuracy with GPT-4 and 55.9% BIRD test-set execution accuracy, setting a new BIRD SOTA.
Takeaways & Limitations
The method substantially improves prompting across Spider and BIRD, producing results comparable or superior to state-of-the-art fine-tuned approaches.
Takeaways & Limitations
Manual demonstrations are fixed for each query class, while GPT-4 responses cost approximately $0.5 and take approximately 60 seconds per Spider question.
Abstract
from arXiv · showhide
There is currently a significant gap between the performance of fine-tuned models and prompting approaches using Large Language Models (LLMs) on the challenging task of text-to-SQL, as evaluated on datasets such as Spider. To improve the performance of LLMs in the reasoning process, we study how decomposing the task into smaller sub-tasks can be effective. In particular, we show that breaking down the generation problem into sub-problems and feeding the solutions of those sub-problems into LLMs can be an effective approach for significantly improving their performance. Our experiments with three LLMs show that this approach consistently improves their simple few-shot performance by roughly 10%, pushing the accuracy of LLMs towards SOTA or surpassing it. On the holdout test set of Spider, the SOTA, in terms of execution accuracy, was 79.9 and the new SOTA at the time of this writing using our approach is 85.3. Our approach with in-context learning beats many heavily fine-tuned models by at least 5%. Additionally, when evaluated on the BIRD benchmark, our approach achieved an execution accuracy of 55.9%, setting a new SOTA on its holdout test set.
1 Introduction
LLM prompting offers a resource-efficient alternative to fine-tuning, but lags behind fine-tuned models on difficult text-to-SQL benchmarks. DIN-SQL addresses this gap by decomposing text-to-SQL into sub-tasks and evaluating the approach on Spider and BIRD.
- LLMs provide strong few-shot baselines without fine-tuning but underperform well-designed fine-tuned models on Spider, especially for medium and complex queries.
- Prompting avoids the large task-specific datasets and machine resources often required for training or fine-tuning.
- DIN-SQL decomposes natural language text-to-SQL into multiple sub-tasks using few-shot prompting.
- On Spider, DIN-SQL reports 85.3% execution accuracy with GPT-4 and 78.2% with CodeX Davinci on the holdout test set.
- The approach combines task decomposition, adaptive prompting, schema linking, and LLM-based self-correction.
2 Related Work
Prior text-to-SQL research spans neural encoders, intermediate representations, sketch-based generation, and LLM prompting. These approaches differ in how they represent schemas and questions and how they construct SQL outputs.
- Sequence-to-sequence text-to-SQL models jointly encode the natural-language question and database schema before decoding SQL.
- Text-to-SQL encoders have used bidirectional LSTMs, convolutional networks, pretrained language models, and graph neural networks.
- Intermediate representations have been introduced to bridge natural-language questions and SQL statements.
- Sketch-based methods decompose SQL generation into slot-prediction sub-problems but may not generalize beyond predefined templates.
- Recent LLM studies evaluate zero-shot prompting for text-to-SQL, while prompting has also been applied to table understanding, reasoning, and table-to-text generation.
3 Few-shot Error Analysis
The error analysis examines 500 Spider training queries that failed under simple few-shot prompting and groups failures into six categories. Schema linking is the largest category, while JOIN and nesting errors are also prominent.
- 500 randomly sampled Spider training queries were manually examined and classified into six failure categories.
- Schema linking produced the largest number of failures, including incorrect column, table, or entity identification.
- JOIN failures occurred when the model missed required tables or selected incorrect foreign keys.
- GROUP BY failures involved missing the need for grouping or selecting incorrect grouping columns.
- Nesting and set-operation failures arose when the model missed nested structures or selected incorrect set operations.
- Other failures included invalid SQL syntax and miscellaneous predicate, DISTINCT, DESC, WHERE, or aggregation errors.
4 Methodology
The methodology decomposes text-to-SQL into smaller, prompt-based modules that address schema linking, query complexity, SQL generation, and correction. Different query classes receive different intermediate representations and prompting strategies.
- The approach breaks text-to-SQL into schema linking, query classification and decomposition, SQL generation, and self-correction modules.All four modules are implemented with prompting techniques.
- 4.1 Schema Linking Module: Schema linking identifies schema references and condition values, addressing the largest category of observed LLM failures.The prompt selects relevant columns and tables and extracts possible entities and cell values.
- 4.2 Query Classification and Decomposition Module: Queries are classified as easy, non-nested complex, or nested complex, with class-specific prompts for generation.Easy queries require neither joins nor nesting; non-nested queries require joins; nested queries may include joins, sub-queries, and set operations.
- 4.3 SQL Generation Module: NatSQL serves as an intermediate representation for non-nested complex queries because removing operators can ease the transition from natural language to SQL.This class particularly targets difficulties finding columns and foreign keys for joins.
- 4.3 SQL Generation Module: Nested complex queries are decomposed by solving generated sub-queries before producing the final answer.Their prompts combine schema links, sub-question and sub-query pairs, a NatSQL representation, and the final SQL answer.
- 4.4 Self-Correction Module: Self-correction asks the model to repair minor SQL errors such as missing or redundant keywords and aggregation functions.The authors note that these issues are less common in larger models but still occur.
5 Experiments
DIN-SQL is evaluated across Spider and BIRD using multiple LLMs, official accuracy and efficiency metrics, and comparisons with prompting baselines. The method achieves strong test-set results, consistently improves few-shot prompting, and benefits from its decomposition modules, though some module-specific limitations remain.
- Test set results: 55.9% execution accuracy on BIRD’s test set establishes a new SOTA for DIN-SQL with GPT-4.
- Test set results: DIN-SQL achieves the highest Spider holdout-test execution accuracy with GPT-4 and the third-highest with CodeX Davinci among officially published results.This result is achieved without using database content.
- Development set results: At least 10% execution-accuracy improvement over simple few-shot prompting occurs for every evaluated model, consistently across model sizes.The method also outperforms zero-shot prompting in both exact-set-match and execution accuracy.
- Development set results: 4% execution-accuracy and 9% valid-efficiency-score gains over a GPT-4 baseline on BIRD development establish a new SOTA.
- Development set results: DIN-SQL outperforms basic few-shot prompting across all Spider difficulty levels, with the largest gains on hard and extra-hard queries.On easy queries, the improvement is attributed to incorporating schema links in the prompt.
- Ablation study: Removing any of the four modules decreases execution accuracy, while schema linking can introduce redundant joins or output columns under ambiguity.Self-correction also depends on the model and prompt: generic correction helps CodeX Davinci but can hurt GPT-4, for which gentle correction performs better.
6 Conclusions
The paper presents decomposition as a way to improve LLM prompting for text-to-SQL, producing results comparable or superior to state-of-the-art fine-tuned approaches on Spider and BIRD.
- The method decomposes text-to-SQL to address challenges underlying the performance gap between prompting and fine-tuned models.
- Extensive experiments on Spider and BIRD show significant performance improvements across all query classes.
- The approach produces results comparable or superior to state-of-the-art fine-tuned approaches.
7 Limitations
The approach has fixed manually constructed demonstrations and incurs substantial cost and latency when responding with GPT-4.
- The manually constructed demonstrations are fixed for each query class, limiting their adaptivity.
- Approximately $0.5 cost and 60 seconds latency are incurred for a Spider question using GPT-4.
- Future work could generate demonstrations adaptively and automatically at finer granularities.
A Prompts
The appendix documents the prompts used across the proposed methodology and describes supporting few-shot examples for complex query classes.
- The appendix lists prompts for four methodology modules on GPT-4 and CodeX, including few-shot and zero-shot implementations.
- NatSQL intermediate representations from the NatSQL repository provide few-shot examples for Non-Nested Complex and Nested Complex queries.
A.1 Zero-shot prompting
The appendix presents an example of the zero-shot prompting scenario used in the method, drawing inspiration from prior work for ChatGPT.
- The zero-shot prompting scenario is inspired by Liu et al. and proposed for ChatGPT.
- Figure 5 demonstrates an example of the zero-shot prompting used in the work.
A.2 Few-shot prompting
The few-shot prompting examples pair natural-language questions with SQL queries, illustrating common operations over a relational schema. They cover filtering, grouping, aggregation, ordering, joins, subqueries, and set-based conditions.
- Example format: Few-shot examples map natural-language questions to executable SQL queries over tables such as student, course, department, and instructor.The examples use a university-style relational schema with tables, columns, and foreign-key relationships.
- Aggregation: Grouping examples use GROUP BY with aggregate functions and HAVING to compute department, building, course, or prerequisite statistics.The examples demonstrate COUNT, AVG, MAX, MIN, and SUM, including conditions on grouped averages and counts.
- Filtering: Filtering examples express conditions with WHERE, including thresholds, ranges, text matching, and specific semesters or years.Examples include capacity > 50, BETWEEN 50 AND 100, LIKE '%Soisalon%', and semester-year predicates.
- Ordering and ranking: Ordering and limiting examples retrieve highest, lowest, largest, or top-ranked results with ORDER BY and LIMIT.Examples select the department with the highest budget, the student with the most credits, and the top three departments by course count.
- Complex queries: More complex examples use joins, subqueries, NOT IN, EXCEPT, and DISTINCT to express relationships and set-based constraints.These patterns retrieve students, instructors, courses, and departments connected through foreign keys or excluded by prerequisite and time conditions.
A.3 Schema linking prompt
The schema-linking prompt identifies the database elements needed to answer a question, including columns, tables, foreign keys, and relevant cell values. Examples show this process across multiple database schemas and question types.
- Coverage: The schema-linking process is applied to questions involving counts, dates, attendance, and attributes across different domains.Examples include older department heads, departments managed by Alabama-born secretaries, students who never attend courses, and player attributes.
- Schema grounding: The prompt grounds links in database schemas by listing tables and their available columns.The examples include university, financial, agricultural, recruitment, and sports databases.
- Question interpretation: Question phrases are decomposed into requested columns and constraints, such as course title, building, semester, and year.The Chandler course example maps each phrase to course.title, SECTION.building, SECTION.semester, and a corresponding year condition.
- Relationship linking: Foreign-key relationships connect linked tables and support selecting the joins required by a question.The university examples enumerate relations among course, department, section, classroom, student, instructor, teaches, takes, advisor, and prereq.
A.4 Classification & decomposition prompt
The classification and decomposition prompt distinguishes EASY, NON-NESTED, and NESTED questions using their join requirements and need for nested queries. It also decomposes complex questions into answerable sub-questions.
- Decision procedure: The classifier applies a decision rule based on nested-query need first, then join need.If nested queries are needed, the label is NESTED; otherwise joins determine NON-NESTED versus EASY.
- Classification rules: Questions are classified as NESTED when they require nested-query operators such as INTERSECT, UNION, EXCEPT, IN, or NOT IN.Examples include finding courses offered by both departments, instructors absent from a later term, and courses without prerequisites.
- Classification rules: Questions requiring neither joins nor nested queries are classified as EASY.The department with the highest total credits uses only course.dept_name and course.credits.
- Classification rules: Questions requiring joins without nested queries are classified as NON-NESTED.The instructor-advisor query uses advisor, instructor, and student tables but no listed nested-query operator.
A.5 SQL generation
The SQL-generation prompt uses schema links, and for more complex cases an intermediate representation, to construct SQL queries. Examples span simple selections, joins, aggregation, ordering, and nested logic.
- Schema-link generation: Schema links directly specify the columns, tables, relationships, and values used to generate SQL.Examples map question terms to schema links before producing queries for students, departments, rooms, and instructors.
- Easy class: Easy examples generate SQL with single-table selection, filtering, grouping, aggregation, ordering, and limiting.Queries include DISTINCT building selection, capacity ranges, department averages, top departments, and highest-credit departments.
- Non-Nested Complex: Non-nested complex examples use an intermediate representation before constructing SQL involving joins and grouped conditions.The course-instructor example first represents the selected fields and relationships, then produces a three-table SQL query.
- Intermediate representation: The intermediate representation can encode aggregation, filtering, and joins before SQL realization.Examples cover summing department budgets, counting students and instructors by department, and counting prerequisites per course.
- Complex query construction: The generation prompt is applied to queries involving joins, temporal conditions, distinct results, and multiple relational constraints.The student-takes example represents year predicates and distinct names before producing the joined SQL query.
A.6 Self-correction prompts
The appendix presents two self-correction prompt variants: Generic, which assumes every query is buggy, and Gentle, which avoids that assumption and provides fixing instructions.
- A.6.1 Generic self-correction prompt: The Generic self-correction prompt is implemented in a zero-shot setting.
- A.6.1 Generic self-correction prompt: Generic self-correction treats all queries as “Buggy SQL.”
- A.6.1 Generic self-correction prompt: Figure 6 illustrates an example of the Generic self-correction prompt.
- A.6.2 Gentle self-correction prompt: The Gentle self-correction prompt is also implemented in a zero-shot setting.
- A.6.2 Gentle self-correction prompt: Gentle self-correction does not assume that queries are buggy.
- A.6.2 Gentle self-correction prompt: Gentle self-correction includes instructions for fixing SQL queries, as shown in Figure 7.