MCS-SQL: Leveraging Multiple Prompts and Multiple-Choice Selection For Text-to-SQL Generation

1 Introduction

• On BIRD, ICL-method accuracy had remained below 60%, compared with 93.0% for humans, underscoring the gap on complex schemas and queries.BIRD is characterized by complex database schemas and queries.Source: §1 · p. 1
• Text-to-SQL outputs vary with schema presentation and few-shot-example choice, reflecting LLM sensitivity to prompt structure and content.The paper reports these variations in its experiments.Source: §1 · p. 1
• MCS-SQL uses multiple prompts for schema linking and candidate SQL generation, then filters candidates by confidence and selects a query through LLM multiple-choice selection.The overview presents schema linking, multiple SQL generation, and selection as the three steps.Source: §1 · pp. 1–2, Figure 1 · p. 2
• BIRD EX reached 65.5% and VES 71.4%, surpassing the previous ICL SOTA by +5.9% and +3.7%, respectively; Spider EX reached 89.6%, 3.0% above its prior ICL SOTA.These results establish new SOTA performance on BIRD.Source: §1 · p. 2

Prompt Engineering

• CoT uses manually crafted examples to elicit intermediate reasoning steps (Wei et al., 2022), while LLMs can explain reasoning without human-annotated examples (Kojima et al., 2022).The paper describes prompt design as consequential for LLM performance across NLP tasks.Source: §2 · p. 2
• Self-consistency samples multiple answers and selects by majority vote; tree-of-thought and graph-of-thought extend exploration of reasoning paths (Wang et al., 2022; Wei et al., 2022; Besta et al., 2023).The paper contrasts these approaches' single-prompt reliance with its use of distinct prompts.Source: §2 · p. 2

ICL for Text-to-SQL

• Text-to-SQL prompting methods using CoT or least-to-most rely on fixed manually crafted examples, and performance can vary with example selection.The cited methods include Pourreza and Rafiei (2023a), Tai et al. (2023), and Wang et al. (2023a).Source: §2 · p. 2
• This work dynamically selects few-shot examples from training data and combines multiple selection strategies in parallel, rather than seeking one optimal strategy.The cited single-strategy studies are Guo et al. (2023), Nan et al. (2023), and Gao et al. (2023).Source: §2 · p. 3

3 Methodology

• Schema linking excludes tables and columns irrelevant to the question, reducing the schema used in later steps.This is the first of the method's three stages.Source: §3 · p. 3
• Multiple SQL generation produces several candidate queries based on various prompts and the linked schema.The candidates broaden the set considered by the selection stage.Source: §3 · p. 3
• Selection chooses the most accurate SQL query from among the candidates.This is the final stage of the methodology.Source: §3 · p. 3

3.1 Schema Linking

• Schema linking proceeds from table linking to column linking, using multiple prompts in both phases to aim for high recall.The SQL-generation steps use only tables and columns selected through schema linking, rather than the full schema.Source: §3.1 · p. 3, §3.1.2 · p. 3

3.1.1 Table Linking

• Table linking asks the LLM to identify needed tables and explain why each is necessary, then shuffles table order across prompts and unions responses to reduce omissions.The union is used because extra tables do not significantly affect later generation, whereas omitting a necessary table prevents the correct query.Source: §3.1.1 · p. 3

3.1.2 Column Linking

• Column linking prompts the LLM to select columns from linked tables, uses table-qualified column names, and unions responses across shuffled prompts.The output combines pc × n selected-column lists, one response per prompt sample.Source: §3.1.2 · p. 3

3.2 Multiple SQL Generation

• Multiple prompts vary few-shot example selection and order to explore a broader range of potential SQL queries.The approach is motivated by LLM output sensitivity to prompts and few-shot examples.Source: §3.2 · p. 4

3.2.1 Few-Shot Examples Selection

• Two example-selection strategies retrieve training questions by similarity to the test question, either before or after masking schema-specific tokens.Masking table names, column names, and values focuses similarity on question content relevant to generating similar queries.Source: §3.2.1 · p. 4

3.2.2 SQL Generation

• Each SQL-generation prompt includes few-shot examples, the linked tables and columns, sample table contents, and the test question.The target database schema is omitted from few-shot examples to conserve prompt length; multiple prompts and responses yield pq · n candidate queries.Source: §3.2.2 · p. 4

3.3 Selection

• The selection stage filters candidates by confidence before asking the LLM to select the most accurate query.The LLM makes its choice from the refined candidate pool.Source: §3.3 · p. 4

3.3.1 Candidate Filtering

• Candidate filtering removes syntax errors and timeouts, groups identical execution results, retains the fastest query per group, and excludes queries below confidence threshold T.Confidence is based on how many candidate queries produce the same execution result.Source: §3.3.1 · p. 5

3.3.2 Multiple-Choice Selection (MCS)

• Multiple-choice selection presents the filtered SQL candidates to the LLM in descending confidence order, requests a choice with reasons, and uses majority voting across n responses.Descending confidence order accounts for the reported tendency to favor earlier options.Source: §3.3.2 · p. 5

4.1 Datasets

• BIRD contains 12,751 question-SQL pairs across 95 real-world databases and includes complex queries and external-knowledge requirements.Spider contains 10,181 questions and 5,693 distinct queries across 200 databases, with test databases unseen during training.Source: §4.1 · p. 5

4.2 Evaluation Metrics

• Execution accuracy measures whether the generated SQL execution result matches the gold query, while BIRD's VES accounts for both accuracy and efficiency.VES assigns zero to queries whose execution result differs from the gold SQL and otherwise considers execution time.Source: §4.2 · pp. 5–6

4.3 Implementation Details

• Experiments use GPT-4 8K, three prompts each for table and column linking, five SQL-generation prompts, and 20 responses per API call.They also use 20 few-shot examples, a 180-second execution timeout, and confidence threshold T=0.2.Source: §4.3 · p. 6

4.4 Baselines

• The comparison includes GPT-4 zero-shot prompting, DIN-SQL, DAIL-SQL, and MAC-SQL as GPT-4-based in-context learning baselines.The baselines use distinct approaches, including question-complexity prompts, dynamic examples with self-consistency, and decomposed sub-questions.Source: §4.4 · p. 6

5.1 Main Results

• BIRD holdout-test EX 65.45% and VES 71.35% beat prior ICL SOTA by 5.86% and 3.67%; Spider dev EX 89.5 leads by +2.7%.The BIRD results set a new state of the art.Source: §5.1 · pp. 6–7

5.2 Ablation Study

• On BIRD dev, schema linking added 2.1%, table contents +2.4%, dynamic masked-question examples +4.8%, same-prompt MCS +2.1%, and multiple prompts +1.3%.On Spider dev, each component also contributed significant performance gains.Source: §5.2 · p. 7, Table 3 · p. 7, Table 4 · p. 7

5.3 Impact of Using Multiple Prompts in Schema Linking

• Schema-linking recall gains were +15.8% for BIRD and +4.7% for Spider with same-prompt sampling, then +12.7% and +2.7% with multiple prompts.The authors report that table and column order affects linking, and that multiple prompts mitigate this sensitivity; omitted schema elements can prevent accurate downstream SQL.Source: §5.3 · p. 8, Table 5 · p. 8

5.4 Impacts of Different Few-shot Selection Strategies

• Question-similarity selection over random selection increased EX by 2.3% on BIRD and 4.2% on Spider; masked-question similarity added 0.7% and 0.5%.The latter comparison is against selection based on the original question.Source: §5.4 · p. 8

5.5 Impact of MCS

• MCS outperformed majority vote by +0.6% on BIRD and +0.3% on Spider, while removing confidence-based filtering significantly reduced MCS efficacy.Confidence filtering narrows the candidate pool before selection.Source: §5.5 · p. 8, Table 7 · p. 8

• Distinct prompts support robust schema linking, and different few-shot selection strategies generate multiple candidate SQL queries.Candidates are filtered by confidence scores before the LLM selects the optimal query using MCS.Source: §6 · p. 8
• Evaluations on BIRD and Spider found that the approach significantly outperformed existing ICL-based approaches.The study reports a new state-of-the-art result on BIRD.Source: §6 · p. 8
• The study presents multiple prompts as a way to enhance the accuracy and robustness of ICL-based text-to-SQL generation.Source: §6 · p. 8

A Error Analysis

• The analysis examined a random sample of 100 examples from each dev set where predicted-query and gold-SQL execution results differed.Failure cases were grouped as incorrect gold, semantically correct, schema-linking error, or other error.Source: A Error Analysis · p. 11
• Manual classification found that 62% of BIRD and 73% of Spider failure cases involved a correct query being generated but still counted as failures owing to inaccuracies in the gold queries.The analysis used a random sample of 100 examples from each dev set where predicted-query and gold-SQL execution results differed.Source: A Error Analysis · p. 11
• Among the remaining errors, schema linking—selecting incorrect tables or columns—was most prevalent, at 20% for BIRD and 21% for Spider.Other errors accounted for 18% of BIRD errors and 6% of Spider errors.Source: A Error Analysis · p. 11
• Table 8 provides BIRD dev-set examples for the four failure categories.The categories distinguish incorrect annotations and semantically equivalent outputs from schema-linking and other prediction errors.Source: A Error Analysis · p. 11, Table 8 · p. 12

B.1.1 Prompt for Table Linking

• Table-linking prompts ask the LLM to select schema tables needed for the question and explain each choice in a specified JSON format.The prompt provides the schema, question, and knowledge evidence.Source: §B.1.1 · p. 13

B.1.2 Prompt for Column Linking

• Column-linking prompts similarly request relevant columns and reasons, returning selected columns in a specified JSON structure.The prompt includes a database schema, question, and knowledge evidence.Source: §B.1.2 · p. 13

B.2 Prompt for Question Masking

• Question-masking prompts replace table names, column names, and values with placeholders, as illustrated by masked questions.The examples show substitutions such as [TABLE], [COLUMN], and [VALUE].Source: §B.2 · pp. 13–14

B.3 Prompt for SQL Generation

• The generation examples pair questions and knowledge evidence with gold SQL, while the prompt requires a JSON response containing reasoning and SQL.The prompt asks for the correct SQLite query.Source: §B.3 · pp. 14–15
• SQL-generation prompts combine few-shot question–SQL examples, the linked schema, sample table contents, the question, and knowledge evidence.The prompt also requests reasoning steps and a final SQLite query; target-database schemas are omitted from few-shot examples to conserve prompt length.Source: §B.3 · p. 15

B.4 Prompt for SQL Selection

• SQL-selection prompts present candidate queries and ask the LLM to choose using checks for question fit, evidence use, SELECT columns and order, and operation types.Instructions evaluate candidates in sequence, choose the first one satisfying the checklist, and otherwise choose the first candidate.Source: §B.4 · pp. 15–17