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

In plain words

Asking a large language model to write a database query from a plain question works only so-so. Simple questions go fine, but questions that need several tables joined or a query inside a query often come out wrong. The paper handles this by splitting the job into smaller jobs, each with its own short prompt. First the model lists which tables and columns the question touches. Then it decides how hard the question is. Then it writes the query using examples that match that difficulty. Last, it reads its own query again and fixes small slips. No model is retrained; everything happens through prompts. The authors report that this beats plain prompting by a wide margin and matches or beats several fine-tuned systems on two benchmarks.

TL;DR

• A four-module prompting pipeline for text-to-SQL: schema linking, query classification and decomposition, class-specific SQL generation, and self-correction §4.
• No fine-tuning and no database content on Spider. The models are GPT-4 and two CodeX variants, called through the OpenAI API §5.1 §5.5.1.
• Author-reported: 85.3% execution accuracy on the Spider holdout test with GPT-4 tab. 2; 55.9% execution accuracy on the BIRD holdout test tab. 3a.
• Dev-set numbers are not held-out: most development was done on Spider dev §5.5.2.
• Cost and latency are high: about $0.5 and about 60 seconds per question with GPT-4 §7.

Problem

Few-shot prompting of LLMs lagged behind fine-tuned text-to-SQL models on Spider, especially on medium and complex queries §1 tab. 1. In a manual error analysis of 500 sampled training queries, the largest failure category under plain few-shot prompting was schema linking, followed by missing or wrong JOINs §3 fig. 1. Other failure classes were GROUP BY, nesting and set operations, invalid SQL, and miscellaneous errors §3.

The authors' premise is that SQL is declarative, so there is no clear chain of procedural steps as in math word problems. They instead break the way a person writes a query into four parts: find the relevant tables and columns, identify the query structure, formulate sub-components, and write the final query §4.

Method

Overview. Four modules run in sequence, each implemented as a prompt to the same LLM. Demonstrations in the prompts come from the training split of the target benchmark §4 §4.1. Figure 2 shows the four-module flow fig. 2.

# DIN-SQL pipeline  [§4]  [fig. 2]
# Input:  question (text), schema (tables, columns, foreign keys)
# Output: one SQL query (string)

def din_sql(question, schema):
    # Find the schema links: tables, columns, join conditions and
    # literal values the question refers to  [§4.1]
    schema_links = llm(schema_linking_prompt(question, schema))
    # Decide EASY, NON-NESTED or NESTED, and list the tables to join
    # and any sub-questions  [§4.2]
    label, sub_questions = classify(question, schema, schema_links)
    # Generate SQL with the prompt for that class  [§4.3]
    if label == "EASY":
        sql = llm(easy_prompt(question, schema, schema_links))
    elif label == "NON-NESTED":
        sql = llm(medium_prompt(question, schema, schema_links))
    else:
        sql = llm(hard_prompt(question, schema, schema_links, sub_questions))
    # Zero-shot pass that fixes small errors in the generated SQL  [§4.4]
    corrected = llm(self_correction_prompt(question, schema, sql))
    return corrected

Schema linking §4.1. A prompt with ten demonstrations from the Spider training set. The model is asked to reason step by step, starting from the phrase "Let's think step by step" §4.1. For every column mention in the question it names the column and its table; it also lists the database entities and cell values it finds in the question §4.1. The output is a single Schema_links: list that can contain columns, join conditions written as table.column = table.column, and literal values DIN-SQL.py. The full prompt is in Appendix A.3 §A.3.

Classification and decomposition §4.2. The model assigns one of three labels:

• EASY: a single table, no join and no nesting §4.2.
• NON-NESTED: needs a join but no sub-queries §4.2.
• NESTED: may contain sub-queries, set operations (EXCEPT, UNION, INTERSECT) and joins §4.2.

For NON-NESTED and NESTED questions the same call also lists the tables to join and, for NESTED questions, the sub-queries §4.2. Labels and their definitions appear in the prompt DIN-SQL.py.

SQL generation §4.3. One few-shot prompt per class:

• EASY: plain few-shot examples of question, schema links and SQL; no intermediate steps §4.3.
• NON-NESTED: each example adds an intermediate representation, NatSQL, which removes JOIN ON, FROM and GROUP BY and merges HAVING into WHERE, and omits the set operators §4.3. The authors use NatSQL because it is reported to work well with other models §4.3.
• NESTED: the prompt first asks the model to answer the sub-questions with their sub-queries, then to write the final NatSQL and SQL §4.3. Examples use the format question, schema links, sub-question/sub-query pairs, intermediate representation, SQL §4.3.

Full prompts for all three classes are in Appendix A.4 §A.4. The authors state that all examples for the three classes come from one training database, the one chosen for the classification prompt §4.3.

Self-correction §4.4. A zero-shot pass where the model sees only the generated SQL and the schema, and is asked to fix mistakes. The authors describe two variants:

• Generic: the SQL is labelled as "BUGGY SQL" and the model is asked to find and correct errors §4.4.
• Gentle: no assumption that the SQL is buggy; the model is asked to check for possible issues, with hints about which clauses to check §4.4.

The default is gentle for GPT-4 and generic for CodeX §4.4. Both prompts are shown only as images, as examples in Appendix A.6 fig. 6 fig. 7. The prompts are described in words here, and the wording is not transcribed.

Few-shot baseline. For comparison the authors use the same examples all in one prompt with no decomposition. CodeX Cushman has a smaller context window, so its baseline uses two examples per class, six in total §5.5.2.

Hyperparameters

Values are as the paper states them. "Code" means the official repository at the commit in the front matter; where the code differs from the paper, see Paper vs code.

Results

Author-reported. The numbers below come from the paper's tables, which give execution accuracy (EX) and exact-set-match accuracy (EM). Spider test results come from the hidden evaluation server §5.5.1. Spider dev has 1,034 examples across 20 databases §5.3. The Spider holdout test has 2,147 examples across 34 databases §5.3. BIRD has 12,751 question-SQL pairs over 95 databases §5.3.

Spider holdout test tab. 2 (EX / EM). The DIN-SQL rows use no database content §5.5.1.

Spider dev, by method and model tab. 4a (EX / EM):

Spider dev by difficulty tab. 4b (EX, then EM):

BIRD holdout test tab. 3a (VES / EX). The VES column is the BIRD efficiency metric §5.4:

BIRD dev tab. 3b (VES / EX):

Ablation, Spider dev execution accuracy tab. 5:

The authors report that every removed module lowers overall execution accuracy for CodeX Davinci §5.6. Without classification, decomposed chain-of-thought does better on hard and extra-hard queries, and simple few-shot does better on easy ones §5.6. The GPT-4 rows show that the generic self-correction prompt can hurt GPT-4 §5.6.

Error analysis after DIN-SQL §5.5.3 fig. 4. The authors say DIN-SQL improves every failure category of the few-shot baseline, most for JOIN and nested queries. Schema linking still has the most failures §5.5.3.

Paper vs code

Repository searched: the paper's footnote links github.com/MohammadrezaPourreza/Few-shot-NL2SQL-with-prompting §1. The owner is the first author, so it is the official repository. The checked-out commit is 0474801616130413b024fd008b016a190684649e; its LICENSE is MIT. The repository has DIN-SQL.py (Spider), DIN-SQL_BIRD.py (BIRD), and result CSVs for GPT-4 and CodeX. The CodeX runs are not in the repository: only the CSV outputs are there, and DIN-SQL.py calls gpt-4 only [code: DIN-SQL.py#L573-L577].

• Self-correction variants. The paper uses a generic prompt for CodeX and a gentle prompt for GPT-4 §4.4. The Spider script runs one zero-shot debugger prompt on every output. It asks the model to fix the SQL when there are problems and to return it unchanged otherwise DIN-SQL.py. The generic prompt is not in the repository. The gentle prompt's exact wording is only in an image in the paper source, so this card cannot compare it word for word.
• Self-correction call and stop sequences. The paper gives 350 max tokens and stops at #, ; and a blank line §5.2. The code matches: 350 max tokens and stops at #, ; and \n\n DIN-SQL.py.
• Re-added SQL prefix. The debugger prompt ends with the word SELECT, and the code prepends SELECT to the model's output and removes newlines DIN-SQL.py DIN-SQL.py. The paper does not describe this.
• Demonstration database. Every Spider prompt also includes a fixed schema and foreign keys for the college_2 database DIN-SQL.py DIN-SQL.py DIN-SQL.py DIN-SQL.py. The paper's text does not mention it.
• Classification input. The code strips the first character of the schema links before passing them to classification DIN-SQL.py. The first character is [, so the test input loses it. The demonstrations keep it, so the two are inconsistent.
• Classification fallback. If the label cannot be parsed, the code treats the query as NESTED DIN-SQL.py. The paper does not state this.
• Sub-question parsing. The NESTED branch extracts sub-questions with a plain string split and no error handling DIN-SQL.py. A malformed classification output crashes the run.
• Decoding. Temperature 0, max tokens 600 and stop Q: for the generation modules match §5.2 DIN-SQL.py.
• API key. DIN-SQL.py has API_KEY = #key DIN-SQL.py, which is a syntax error until a key is filled in. DIN-SQL_BIRD.py sets the key to an empty string DIN-SQL_BIRD.py.
• BIRD model settings. The BIRD script calls gpt-4-32k through LangChain, with temperature 0 and max tokens 2000, and no stop sequences [code: DIN-SQL_BIRD.py#L21]. These are not the settings in §5.2. The paper's §5.2 describes only the Spider settings.
• BIRD schema and hints. Each table's schema includes three sample rows DIN-SQL_BIRD.py, and the BIRD evidence field is used as the hint DIN-SQL_BIRD.py. This matches §5.3.
• BIRD fallbacks. If the self-correction output has no Revised_SQL: line, the unrevised SQL is used. If no SQL was produced, the placeholder SELECT * FROM table is written DIN-SQL_BIRD.py. Neither case is in the paper.

Pitfalls

• Dev numbers are not held-out. The authors say most development was on Spider dev §5.5.2. Only the Spider test numbers come from a hidden server §5.5.1. Compare dev results with other dev results only.
• Cost and latency. The authors estimate about $0.5 and about 60 seconds per question with GPT-4 §7. Each question uses several LLM calls, so budget for that.
• Truncation. Generation is capped at 600 tokens and cut at Q: §5.2. Long nested queries can be cut short. If the output has no SQL: marker, the code falls back to the bare prefix SELECT DIN-SQL.py DIN-SQL.py. The paper does not discuss either case.
• Exact match versus execution. The authors attribute the gap between EX and EM to the few-shot in-context setting §1. EM compares against one reference query per question, while EX accepts any query with the same result §5.4.
• Fixed demonstrations. The authors say demonstrations are fixed per class and suggest automatic selection as future work §7. The number and choice of demonstrations are not stated for each class §4.3 §5.5.2.
• Decomposition is a prompt-level choice. The classification decides which prompt runs. A wrong label sends the query down the wrong branch. The paper does not report the classifier's accuracy on its own; it reports only end-to-end results and the ablation §5.6.
• Schema-linking errors propagate. Schema linking was the largest failure category even with a dedicated module §5.5.3. Everything downstream depends on its output.
• Figure and table numbers. The numbers in this card follow the order of figure and table environments in the LaTeX source. Check them against the compiled PDF before citing.
• No equations or algorithms. The paper has no numbered equations or algorithms. The pseudocode above is this card's own summary of the paper's procedure §4.

Lineage

• Builds on: Chain-of-Thought Prompting for step-by-step demonstrations §4 §4.1; least-to-most and decomposed prompting for splitting a task into sub-problems §4; the "Let's think step by step" phrase from Kojima et al. §4.1; NatSQL as the intermediate representation §4.3.
• Compared against: few-shot and zero-shot prompting of CodeX and GPT-4 tab. 4a; fine-tuned models on Spider, including RESDSQL, PICARD, Graphix and RATSQL tab. 2; a GPT-4 baseline on BIRD tab. 3a.
• Superseded by: not stated in the wiki.