SQL-PaLM: Improved large language model adaptation for Text-to-SQL
Authors: Ruoxi Sun
Sercan O Arik
Alexandre Muzio
Lesly Miculicich
Satya Kesav Gundabathula
Pengcheng Yin
Hanjun Dai
Hootan Nakhost
Rajarishi Sinha
Zifeng Wang
Tomas Pfister
Venue: Accepted by TMLR ()

Abstract: Text-to-SQL, the process of translating natural language into Structured Query Language (SQL), represents a transformative application of large language models (LLMs), potentially revolutionizing how humans interact with data. This paper introduces the SQL-PaLM framework, a comprehensive solution for understanding and enhancing Text-to-SQL using LLMs, using in the learning regimes of few-shot prompting and instruction fine-tuning. With few-shot prompting, we explore the effectiveness of consistency decoding with execution-based error filtering. With instruction fine-tuning, we delve deep in understanding the critical paradigms that influence the performance of tuned LLMs. In particular, we investigate how performance can be improved through expanded training data coverage and diversity, synthetic data augmentation, and integrating query-specific database content. We propose a test-time selection method to further refine accuracy by integrating SQL outputs from multiple paradigms with execution feedback as guidance. Additionally, we tackle the practical challenge of navigating intricate databases with a significant number of tables and columns, proposing efficient techniques for accurately selecting relevant database elements to enhance Text-to-SQL performance. Our holistic approach yields substantial advancements in Text-to-SQL, as demonstrated on two key public benchmarks, Spider and BIRD. Through comprehensive ablations and error analyses, we shed light on the strengths and weaknesses of our framework, offering valuable insights into Text-to-SQL's future work.

Published in Transactions on Machine Learning Research (11/2024)
SQL-PaLM: Improved large language model adaptation for Text-to-SQL
Ruoxi Sun!
ruonis@google.com
Sercan Ö. Arik'
sourik@google.cm
Alex Muzio
amuzzo@@google.com
Lesly Miculicich
Imiculicich@goopee com
Satya Gundabathula
salyakesav@googlee.omm com
Pengcheng Yin
pcyin@google.coo com
Hanjun D
hadaitigoogle. com
Hootan 2
hootan@google com
Rajarishi Sinha
sinharaj@google.co com com
Zifeng Wang
zifeng@
Tomas Pfister!
tpfister@google.com
Cloud AI Research; Google Cloud; Google DeepMind
Reviewed on OpenReview: https: // openreview. net/ forum? id=rlloVZokri
Abstract
Text-to-SQU, the process of translating natural language into Structured Query Language (SQL), represents transformative application of large language models (LLMs), potentially revolutionizing how humans interact with data. This paper introduces the SQL-PaLM framework, comprehensive solution for understanding and enhancing Text-to-SQL using LLMs, using in the learning regimes of few-shot prompting and instruction fino-tuning. With few-shot prompting, we explore the effectiveness of consistency decoding with execution- based error filtering. With instruction fine-tuning, we delve deep in understanding the critical paradigms that influence the performance of tuned LLMs. In particular, we investigate how performance can be improved through expanded training data coverage and diversity, synthetie data augmentation, and integrating query-specífie database content. We propose test-time selection method to further refine accuracy by integrating SQL outputs from multiple paradignns with execution feedback guidance. Additionally, tackle the practical challenge of navigating intricate databases with significant number of tables and columns, proposing efficient. techniques for accurately selecting relevant. database elements to enhance Text-to-SQL performance. Our holistic approach yields substantial advancements Texl-lo-SLL, as demonstrated on two key public benchmarks, Spider and BIRD. Through comprehensive ablations and error analyses, we shed light on the strengths and weaknesses of our framework, offering valuable insights into Text-to-SQL's future work.

---

Published in Transactions on Machine Learning Research (11/2024)
1 Introduction
Text-to-SQI, aims automate the process of trans- lating natural language questions into SQL queries that can be executed directly on a database (An- droutsopoulos et al., 1995; Hristidis et al., 2003; Li & Jagadish, 2014; Wang et al., 2017). As illustrated in Fig. 1, Texl-to-SQL bridges the gap between the way humans naturally communicate, using language, and the way databases are structured, and has the potential to revolutionize how humans interact with data (Zhong et al., 2017; Yu et. al., 2018; Li et. al., 2023d). Making databases accessible to non-expert users through natural language, Text-toSSQL can empower humans to extract valuable information without needing specialized SQL knowledge (Wang et al., 2019; Scholak et al., 2021; Cai et al., 2021; Qi et al., 2022; Li et al., 2023b; Pourreza & Rafiei, 2023; Gao et al., 2023a; Sun et al., 2023; Chen et al., 2023). This not only enhances the efficiency of data analysis but also broadens the use of databases to a wider range of applications. Intelligent database services, platforms for automated data analytics, and more sophisticated conversational agents capable of understanding and responding to complex data-related questions can all be fueled by advancements in Text-to-SQL (Yu et al., 2019; Gu al., 2022; Pérez-Mercade et al., 2023; Xie et al., 2023).
Database Schema
Tade
Countrylanguage
Columne
CountryCode
Language
IsOfficial
Percentage
Table 2
Country
Coluris::
Code
Name
Continent
Region
Language
What sew the namee of tallene whene both English and Thench are ofkial languages?
SOL solution
sttect Neme "ROM country
counthylengggge
Laniggugee
"Engiar AND T2 Tatoo "T" IN INTEEETT SELECT Na FROM counyy AS JOEN countylrguuge A8 T2
T2 WHERE Languago "Frondy' AND
Figure 1: Text-to-SQL. systems are developed to trans- form queries expressed in natural language into Struc- tured Query Language (SQL) based on the information from databases.
At a high level, the Text-to-SQL is sequence-to-sequene modeling task (Sutskever et al., 2014), with both the database schema and natural language question being converted into a linear input sequence, and the SQL being the target output sequence. Farly works attempt to fine-tune domain-specific Transformer architectures or decoding approaches tailored for Text-to-SQL utilizing SQL syntax, semantics or the intricate relationship between questions and databases (Scholak et al., 2021; Qi et al., 2022; Li et al., 2023a; Wang et al., 2019; Bogin et al., 2019; Cai et al., 2021; Hui et al., 2022). Recent years have witnessed the burgeoning application of large language models (LLMs) to Text-to-SQL. (Rajkumar et al., 2022; Liu et al., 2023a; Gao et al., 2023a; An et al., 2023; Tai et al., 2023; Pourreza & Rafiei, 2023; Chen et al., 2023; Sun et al., 2023). Along this line, most of the research has focused on leveraging prompting to translate user utterances into SQL queries (Rajkumar et al., 2022; Liu et al., 2023a). More advanced prompting methods has domain-specific adoptions to improve understanding natural language questions and structured databaso schemas, such as selecting better few-shot exemplars based on question similarity (Gao al., 2023a; An et al., 2023), decomposing complex questions into sub-tasks (Tai et al., 2023; Pourreza & Rafiei, 2023), verifying the correctness of model-predicted SQL queries through exccution feedback (Chen et al., 2023; Sun et al., 2023; Pourreza & Rafiei, 2023), well as linking NL phrases (e.g. "nation" in the question in Fig. 1) to relevant database constructs (e.g. the Name column in the County table, see (Pourreza & Rafiei, 2023)).
While these few-shot prompting methods have significantly improved Text-to-SQL performance, it still remains unclear whether prompting alone is adequate to handle real-world challenges. As we elaborate in Sec. 2, real-world Text-to-SQI. exhibits a variety of challenges. Specifically, user's natural language questions are often ambiguous (e.g. "sales in California") ")) and can have multiple plausible interpretations (e.g. sales made by Californian businesses produces sold in the states). Those questions might also come with semantic constraints (e.g Who was the president before Joe Biden) that map to complex SQL queries (c.g. requires rcasoning steps that first retricve the beginning date of Biden's 's term and then identify the last record whose end date is before that). Moreover, real-world databases may contain large volumes of tables and columns, and the sheer content size would easily exceed the context limit of LLMs. Components in a schema could also have rich data types (c.g. strings or datetimes) with complex dependencies defined by primary-foreign key mappings, requiring non-trivial SQL queries to process such data. In addition those inherent challenges, collecting aligned examples of questions and SQL queries for learning also requires
2

---

Published in Transactions on Machine Learning Research (11/2024)
laborious annotation efforts by domain experts, impeding the process of scaling-up up data hungry LLMs for Text-to-SQI.
As a first step towards addressing those challenges, in this paper, we propose SQL-PaLM, a holistic framework that adapts a LLM, PaLM-2 (Amil et al., 2023) Unicorn variant, for Text-to-SQL tasks. We start with evaluating SQI.PPlLM's performance with prompting, and then we focus on SQI.-PaLMM's tuning as leads to better performance in challenging scenarios. Apart from existing work that mostly focus on few-shot prompting strategies tuning relatively smaller LLMs, SQL-PaLM focus tuning a large LLM. Larger models have different behaviors with their emergent abilities, a phenomenon of significantly improved understanding and reasoning performance compared to smaller LLMs (Wei et al., 2022a). We systematically explore large models' potential for Text-to-SQL and study the research topics along the key aspects presented in Sec. 4 Through extensive experiments and analyses, we unravel multiple key factors that influence the LLMs' performance when adapting to TeX.- First, diversity and coverage of train data, can be crucial we present ablation studies on training data mixture and provide takeaways across tasks and benchmarks. To improve training data coverage with low human cost, SQL-PaLLM also proposes augmenting with large-scale L.M--generaed synthetic data. Second, input representations study on fine-tuning Text-to-SQI. to better leverage different types of information-bearing content, such as database values, column descriptions, and hints. Next, scaling to real-world database sizes would be important for adoption. We present an efficient column selection approach that only encodes information from the subset of relevant database columns as inputs to LLMs. This approach significantly reduces the context size with negligible impact on performance. In particular, we propose a program-aided colurn selection and retrieval-based column selection approach, We study integration with both hard (i.e. with removal of unselccted columns) and soft (i.c. emphasizing selected columns) column selection approach into the overall Text-to-SQL pipeline. Finally, propose execution-based test-time refinement to integrate multiple training paradigms based on execution feedback.
greatly influence the overall quality. We present an in-depth
Our contributions can be summarized as follows:
We focus on large LLMs on Text-to-SQL and investigate from multiple important angles including learning perspectives (prompting tuning), task perspectives (judiciously selecting input components) and real-world scaling perspectives (e.g. column selection). Through thorough experiment and analysis, we systematically identify components in influencing performance.
We demonstrate that mixing training data with diverse sets, despite having various formats, benefit LLMs, indicating the potential of tuned LLMs for superior generalization ability. Complex SQL data particularly have been observed to be more beneficial as tuning data.
study effective methods to utilize dalabase content and auxiliary information, ion, such as descriptions. We show improvements with the relevant subset of them included in the prompt while irrelevant information can harm the performance.
For large-scale databases, we introduce a column selection approach that significantly reduces the length of the inputs going into the LLMs, while yielding negligible impact to performance (hard column selection). Among the two proposed approaches, program-aided column selection has higher column selection accuracy, whereas retrieval-based approach is more cost-effective and controllable. soft column selection, which doesn't exclude unselected columns, has a higher performance than hard column sclection, albeit it necossitates LLMs with longer context longth.
We propose a test-time execution-bart based selection approach to integrate multiple setups further improve performance.
Focusing on challenging SQLs that are close real-world use, we provide in-depth error analysis and case study to reveal the advantages and disadvant of the proposed approaches.
3

---

Published in Transactions on Machine Learning Research (11/2024)
2 Text-to-SQU Challenges
Real-world Text-to-SQL scenarios present a broad spectrum of challenges stemming from the complexity of natural language questions, database structures, inherent SQL intricacies, and data availability. These real-world challenges are often more severe than those encountered in academic benchmarks.
Natural language question phrasing: The ambiguity and complexity of natural language questions pose challenges. Input phrases may have multiple interpretations in natural language (e.g. "sales in California' could refer to sales made by people in California products sold to people in California). They might also come with complex sentence structures such subordinate clauses and relative clauses. The meaning may also depend on the surrounding context, making it difficult to derive correct interpretations. In addition, databases and applications come from a wide range of domains, and Text-to-SQL systems need to understand domain-specifie terminology, which can vary greatly depending on the use case. Specific rules, regulations, formulas or calculations related to a particular domain might need to be applied to generate the correct SQL. For example, the question "List disease names of patients with proteinuria levels above normal" requires LLMs to have domain knowledge "proleinuria level above normal means U-PRO >= 30" to solve the problem.
Sizes and diversities of databases: Real-world large-scale databases might contain numerous tables and columns. The sheer volume of columns can exceed prompt length limits, making including the entire dataset schema impossible. Furthermore, LLMs face difficulties in efficiently accessing and utilizing information within lengthy input contexts as discussed in the phenomenon of "lost in the middle" " (Liu et al., 2023b). Database schemas often have complex and various structures, including differences in table names, column names, and their relationships. The relationships between tables may not be explicitly defined in the schema, requiring the system to infer them (by understanding "foreign keys") Moreover, database schemas might contain ambiguities table and column names in a schema may not be informative (e.g. The column name "property" is vague about the feature it denotes, as opposed to "size" clearly specifies the type of property) or abbreviated in a way that is not easily understood (such as "cname "). Additionally, some tables might contain tens of similar columns ("shul", "sku2", "sku100"), potentially leading to confusion for the Text-to-SQL, system.
Besides ambiguities in schemas, database contents also have a wide variety of types and formats. The data stored in the database can also vary significantly in types and format, leading to lengthy and specifie clauses (e.g. regular expression, and type casting) to extract variable information. (1) Type: they in- clude scalars, arrays, nested arrays etc. (2) Format. For instance, the year of 2024 might be saved as a string: "2024", number: 2024, or in other forms such as "ycar2014", ", "2014-01-01". Extracting different formats of "year" requires different regular expressions. We show real-world example in Fig. 2, where extraction of the proper format of the column "revenue" requires using "CASE" statements, cast- ing string into numbers, schema interpretation, and regular expressions, because the values of revenue is stored in different string formals single values (i.e. i.e. "100") ranges (i.e. "100-200").
A challenging real-world example
Extract the "revemuc" valuos: cast to number if one number ('100"), take average a range ("100-200")
case
when regexp_containe (revenue, 1-1) then
(cast (regexp (revenue, r '((d+)) int64) cast exterpeextractiii (revenue, I ('d() ))) as int64)) / 2
else
cast (rwgexp_reppaeeeeeeeeeeeeeeeeeee (revenue, [-0-9]', ") as int64)
end
Inherent SQL complexity: Certain SQL queries are complex in nature, marked by the use of multiple SQL keywords, the inclusion of nested quu-que a variety of column selections or aggregations, the application of conditional statements, and the in- volvement of joins across multiple tables.
Figure 2: An example SQL sample corresponding to complex arithmetic operations and handcrafted rules on a complex database.
4

---

Published in Transactions on Machine Learning Research (11/2024)
Data-centric challenges: In general, paired (text, SQL) data can be very costly to obtain (Yu et al., 2018), so even the highly popular publicly-availabl dataset sizes are often much smaller than other text processing applications. Given the challenge of curating such datasets, it is not rare to observe inconsistent, incomplete, or incorrect ground truth data (e.g. human annotators making errors )), which might affect the quality of the models trained on them.
3
Related Work
3.1 Approaches with Deep Neural Networks
Sequuene-to-sequuence models Text-to-SQL, can be formulated as a sequence-to-seqqenee modeling prob- lem, with both the database schema and natural language question being converted into a linear input sequence, and the SQL being the target output sequence. Prior to the recent advances of large language models (LI.Ms), the approach of fine-tuning Transformer models, such as T5 (Raffel et al., 2020), with SQL-specific customizations had been the prevalent approach dominating the state-of PICARD (Scholak et al., 2021) introduces a technique that discards invalid beam search candidates during inference, improving the grammatical correctness of the SQL queries. RASAT (Qi al., 2022) augments transformer architecture with relation-aware self-attention which is efficient to incorporate a variety of relational structures while also leveraging a pretrained T5 model. RESDSQL(Li et al., 2023b) proposes ranking-enhanced encoding and skeleton-aware decoding framework to decouple the schema linking (e.g. table or column names) and the skeleton parsing (e.g. keywords).
Graph encoders for schema understanding Another line of work is based on employing graph encoders to explicitly model complex relationships within the database schemas and questions. RAT-SQL (Wang al., 2019) introduces schema encoding and linking, and models the schema and its relationships as a graph. Global-GNN (Bogin et al., 2019) further explores this concept of depicting the intricate structure of database schema with a graph. SADGA (Cai et al., 2021) employs both contextual and dependency structures for encoding the question-graph, and utilizes database schema relations in the construction of the schema graph. S2SQL (Hui et al., 2022) incorporates syntactic dependency information into the relational graph attention network.
3.2 Text-to-SQL with LLMs
Prompting LLMs Recent advances in LLMs have yielded groundbreaking capabilities (Chowdhery et al., 2022; Achiam al., 2023) - their ability to understand, generate, and reason unprecedented ways with prompting has amplified their penetration into many real-world tasks. Numerous advanced prompting techniques have further extended LLMs' capability, such as Chain-of-Thoughl (Wei et al., 2022b), Least- to-Most (Zhou et al., 2022), and others (Chen et al., 2022; Yao et al., 2023; Besta et al., 2023). However, these generic-purpose prompting approaches are observed to fall behind on Text-to-SQL, tasks compared to approaches tailored to Text-lo-SQLa (Rajkumar et al., 2022; Liu et al., 2023a; Li et al., 2023d).
To further improve ceneral-purpor prompting methods for Text-to-SQU specifically, advanced prompting approaches tailored to Text-to-SQL. task, have been proposod. DIN-SQL (Pourreza & Rafici, 2023) exemplifics this by breaking down the Text-lo-SQL lasks into sub-tasks: schema linking, classifying SQL by difficulty level, SQL generation based on SQL difficulty, and self-correction", and etc. CoT-style (Tai et al., 2023) also proposes decomposing Text-to-SQL into sub-problems and presents them all at once to LLMs instead of solving sub-problems iteratively. SQLPrompt (Sun et al., 2023) enhances LLMs with diverse representations of database schemas and questions as inputs to encourage diverse SQL generation to improve performance from execution-basse consistency decoding. Self-debugging (Chen et al., 2023) appends error messages to the prompt and performance multiple rounds of few-shot prompting to self-correct the errors. DAIL-SQL (Gao
Forr example, BIRD datasets Li et al. (2023d) contain errors, we described in error analysis in Sec. 8.7
FFor example, the standard single-pass prompting approach of LLMs, such as with PaLM-2 or GPT-4 on the development (dev) set of the Spider dataset underperforms finc-tuned smaller capacity models, such as Picard Scholak et al. (2021) and RESDSQL (Li et al., 2023a)
Notably, DIN the first few-shot prompting that can outperform strong tuning- based alternatives, such Picard Scholak et. al. (2021) and RESDSQL (I.i et al., 2023a)
5

---

Published in Transactions on Machine Learning Research (11/2024)
et al., 2023a) provides an investigation prompt designs, including question representations and example selection on few-shot prompting. In addition, another line of work is selecting similar few-shot demonstrations with the imput questions so that LLMs can follow the solution of a similar question. Among those, DAIL-SQL selects based on similarity of embodding of questions plus SQL queries, whereas SKILL-KNN(An et al., 2023) selects based on similarity of the required skills. Another line of research comes from the perspective of agent-based systems, such as MAC-SQL (Wang et al., 2024) and DIN-SQL. (Pourreza & Rafici, 2023), where function modules are framed in the context of an agent's 's operations. These agent modules typically include a selector (which reduces a large database into smaller sub-database), decomposer (which breaks down complex questions into simpler sub-queries), and a refiner (which utilizes SQL execution feedback to improve queries).
Fine-tuning LLMs Instruction tuning on coding tasks have achieved remarkable performance on different programming languages (e.g. Python and SQL) (Luo et al., 2023; Muennighoff al., 2023; Li et al., 2023e), indicating the immense potential of fine-tuning. However, regarding Text-to-SQL, compared to prompting approaches, luning approaches have been relatively under-explored, partially attributed to the prohibitively high computational cost. DAIL-SQL (Gao al., 2023a) has investigated fine-tuning open-source LLMs (e.g. LLaMA). Their results suggest that although fine-tuning yields significant enhancements, the performance of fine-tuning open-source LLMs, attributed to their smaller sizes, remains substantially lower than prompting larger models like PaLM-2 or GPT-4. Fncouragingly, concurrent work CodeS (Li et al., 2024) ("CodeS-15B") applies Text-to-SQL specific adaptation and achieves impressive results. Unlike existing work, we primarily focus on LLMs at larger scales, to investigate the potential of achieving significant gain with the increase of model size due to the emergent ability of large models (Wei et al., 2022a).
Schema linkage Schema linkage, which connects phrase in questions to those in the database schema, is often incorporated as a guidance module into Text-to-SQL. IRNet (Guo et al., 2019) performs schema linkage to generate custom type vectors to augments the embedding of questions and schema, that results in improvements in recognition of the columns and the tables mentioned in a question and superior generation of intermediate representations with abstract syntax tree decoder. RAT-SQL (Wang el al., 2019) integrates schema linkage information directly into the self-attention layers of its encoder, along with question and schema. With end-to-end training on Text-to-SQI, the encoder-decoder transformer learns to effectively utilize these linkage cues, enhancing SQL generation abilities. RESDSQL (Li et al., 2023a) employs ranking-enhance encoder to guild the model to prioritize the most pertinent elements for SQL generation. The ranking is obtained by training a cross-encoder is for schema linkage, classifying schema items based on their relevance to the question. Our proposed "column selection" approach is different from previous methods as we rely on LLM's intrinsic reasoning ability to infer relevant columns, whereas previous work relied on either pattern matching or learning trainable parameters through end-to-end Text-to-SQL. training. In addition, the purposes are different that ours is an independent module, which serves as prepossessing step to enable applying Text-to-SQL to large-scale datasets that exceed the prompt length, whereas others are proposed as parts of their Text-to-SQL. methods that are hard to decouple from the overall Text-to-SQI, pipcline.
Retrieval-based methods incorporate retrieval-augmennte generation and focus on real-world database content. Zhang et al. (2023) introduces a understanding of SQL by leveraging similar past queries to inform the generation process, addressing the gap between specific st sucturall knowledge and general knowledge. Nan et al. (2023) demonstrates the effectiveness of petrieval-based approaches in selecting diverse and relevant demonstrations through prompt design strategies. Wang et al. (2023) decouples the Text-to-SQI. process into schema routing and SQL generation, and employs a compact neural network-based router for effective navigation through large-scale schemas, complemented by LLMS for SQL generation.
approach that enhances the structural
Synthetic data methods Regarding synthetic data for Text-to-SQL task, GraPPa (Yu et al., 2020) and TaBERT (Yin et al., 2020) propose pretraining frameworks to jointly train structured schema of DB tables and questions. GraPPa proposes synchronous context-free grammar: extracts question-SQL. template in "context-free" manner from existing dataset, and applies them to other databases. Zhao et al. (2022) proposes synthetic framework with emphasis on "strong-typing" "key relations", only including "schema-distamm weighted" relevant columns. Wang et al. (2021), Wu et al. (2021) synthesize SQL using context-free grammar
6

---

Published in Transactions on Machine Learning Research (11/2024)
rules (abstract syntax tree grammar), and then generate the corresponding natural question based on the synthesized SQLs'. Unlike other work, this work focus on validity of the generated SQL to better benefit. from synthetic data.
4 Key Aspects of the SQL-PaLM Framework
We investigate multiple key aspects of building a Text-to-SQL framework in this paper. Through extensive experimental validation and in-depth analyses, we aim to systematically unravel the factors influencing Text-to-SQL. performance.
Learning perspective - pushing adaptation with prompting vs. tuning: Central to our investigation is understanding the intrinsic property of LLMs on tackling the Text-to-SQL task, whereby we explore, within the learning paradigms of few-shot prompting and tuning, how LLMs solve Text-to-SQL tasks differently under different learning scenarios, and what factors inffuence the final performance significantly. Compared with few-shot prompting approaches, tuning approaches have been relatively under-explored, partially due to the prohibitively high computational cost. In this paper, therefore, focus on some pivotal questions for tuming, such as: How does the performance of prompting strategies compare with that of tuning strategies? To what extent does the performance rely on the foundation models' capacity? How well do models generalize across different datascts, especially when faced with limited training data (considcring notable publicly-aaailabl datasets like Spider and BIRD are not significant for large model size)? What is the impact of parameter- efficient tuning techniques, like LoRA (Hu et al., 2021), on the training performance? How does tuning depend on different foundation models (e.g. PaLM vs. LLaMA)?
Task perspective judiciously selecting input components for Text-to-SQL: The Text-lo-SQL task contains a range of potentially-useful information that can be taken advantage of: (i) database schema: there are table & column names, descriptions (e.g. clarifications such as abbreviations), data types (e.g. string and integer), data formats (c.g. explanations of formats stored within the database such as with a raw value or an interval), and primary & foreign keys that describe how different tables are connected to each other; (ii) database content values, some database entry values are needed to solve the question"; ; (iii) natural language questions, whether there are associated hints or formulas, such as the domain knowledge. Each of the above information can be quite critical with some of them missing, LLMs cannot produce accurate SQL outputs. On the other hand, in some scenarios, they can add up to be lengthy and contain irrelevant details, wiht the potential to distract the LLMs from focus on relevant information and generate the correct SQL outputs. Therefore, within the limited input length, it is crucial to achieve a balance on not missing critical information, and not providing a significant amount of irrelevant information. This necessitates judicious selection of subset of features for Text-to-SQL model to produce correct SQLs. Some fundamental questions arise: How can we enable LLMs to elfectively utilize various forms of available information? Which information sources are most valuable? What are the linear formats to effectively represent the various inputs for LLMs?
Real-world scaling column selection: With the advances in LLMs, numerous challenges associated with Text-to-SQL can be addressed more effectively, bringing us closer resolving real-world database challenges. However, one remaining key obstacle is the potentially high number of columns. Real-world data such as those representing the full inventory of large-scale retailers, might contain a large number of attributes Directly processing this volume of columns is often infeasible, as the concatenation of column names would exceed the prompt length limits of LLMs. This highlights the need for column selection the process of identifying a relevant subset of columns from multiple tables. Column selection is related to the broader process of schema linking?, which maps phrases in the natural language question to corresponding columns
*For instance, Question: What is the revenue for shoes? In the database, the column "product' contains "Running shoes', ; Without providing database content, such as "column 'product' contains 'Running shoes", the output SQL is likely to be 'SELECT.... WHERE product='shoes", because "shoes' is mentioned in the question. However, the correct SQL is "SELECT... where product=
shoes"
FFor instance, public benchmarks, such Spider 6rhhe number of columns can be hundreds or thousands 7 Major schema linkage approaches (Gua et al., 2019; Wang et al., 2019; et al., 2023a) are discussed in Sec.
7

---

Published in Transactions on Machine Learning Research (11/2024)
and tables in the database schema. The difference is that column selection focuses solely on identifying the set of relevant columns from the schema, without linking.
5 Problem Formulation
Text-to-SQL systems transform queries expressed in natural language into SQL programs. Provided natural language query Q, and associated database D, SQL outputs are generated such that when executed against database D, would generate the answer to the original natural language query Q.
A database includes two primary components: the schema (including table and column names) and the contents (entry values) D. The schema, represented by S, outlines a database's structure and includes a set of table names T, and a set of column names C. The database content values are the data values that populate the entries of the tables, adhering to the attributes defined by the database schema.
The database schema S contains nr tables:
5= (5
s(2) ....(Tr)
(1)
with the k-th table schema ) consisting the table name ) and a collection columns C(k) where j-th column name is represented by c The k-th table contains nool number of columns:
(k)
s (T c(t) (*(k) = (c(h), (../%..).) "..
The database content values of the k-th table are (k), a nrow x noal matrix with each row being a data entry and each column being vector of values for an attribute:
(*) = {ul
(8)
(2)
90)
where os) are vectors length (k) encompassing the values of the attributes c.e) Usually, the number (k) of entries nrow significantly larger than the number of attributes (k)
(k)
not Primary keys K(A) E (kk) are the column(s) that contain values that uniquely identify each row in table Foreign keys K E ((k) are the column(s) in one table, referring to the primary key in another table. They are used to link multiple tables. Additionally, databases often include supplementary information for clarification (such as the detailed descriptions of columns) which help interpreting ambiguous or uninformative column names (as explained in Sec. 2). We use (C) to indicate the descriptions for column C. For k-th table, (ks refer to a collection of column deseription:
(k) (aaea(C j=1m
Lastly, there can be hints H user-specified aids for the question. They could contain definitions or formula used to construct SQL queries." 10
For example, the column "StudentIDs" of the "Student" table. Forr example, consider the table "Student" with primary key 'StudentID', and the table 'BookOrders' which has two columns: "OrderlD" and 'Buyer'. The 'Buyer' column contains series of StudentIDs representing the individuals who placed the book orders. In this scenario, 'BookOrders.b Buyer' is the foreign key which points to primary key each row in the 'BookOrders' table be associated with specific student from the student table.
This way,
Foror instance, for the query "List the phone numbers schools with the top SAT excellence rates", the hint is the definition of the excellence rate, the percentage of take takers with SAT score greater than 1500. "Excellence rate NumGE1500
Num Tht Takr", where column "NumGE1500" refers "Number of Test Takers Whose Total SAT Scores Are Greater or Equal 1500" and "NumTstTale Takaarr" refers "Number Test, Takers"
8

---

Published in Transactions on Machine Learning Research (11/2024)
Diversify Tuning Data Coverage
Test Time Execution-based Selection
Train
users greater than...
SQL solution
Dataset 1
Train
Executor
What the top
>
Dataßet
Result
k"
Resuit1
Count
Train
1
SQL solution
SQL solution
Aggregated Results Counts
Dataset
Dataset 2 Test
Test
Synthetic Data
SQL solution
Executor
What the top
solution
Dataset Train
LLM
Result
"5k"
Resull
Count
Dataset 2
How revenue..
Synthetic Train
Database Context Value
Aggregate
What is the revenue of phone?
Column "Product"
Don't match!
Executor
Table
"Smartphone"
SQL solution
</>
Resuit
"5k"
Column Selection
Selected Columns
What the revenue
Relevant Columns
Table,1
Embedding
Executor
Table,
Removed
Column Selection
Result
Inalevani Columis
n
Error
Program alded
LLM
Table,N
Select 'revane" from Table 1 where ProductF...
Error Filtering
Figure 3: Overview of the SQL-PaLM framework for fine-tuning. Our framework incorporates different submodules for superior Text-to-SQL performance: (i) diversifying training data coverage (Sec. 6.3.2), (ii) incorporating synthetic data (Sec. 6.3.3), (iii) including database content (Sec. 6.3.4& 6.3.5), (iv) test-time refinement mechanism (Sec. 6.3.6).

---

Published in Transactions on Machine Learning Research (11/2024)
6
Methods
This paper presents the SQL-PaLM framework (depicted in Fig. 3), a holistic approach to push Text-to- SQL capabilities using LLMs with both few-shot prompting and instruction-tuning We first describe the input representations (Sec. 6.1) used for both learning paradigms. For few-shot prompting, we propose a prompting approach leveraging cecuution-baseee croo-filtering-aidee consistency decoding (Sec. 6.2). For instruction-Luning, delve deeply into understanding the critical factors in influencing performance of tuning LLMs, including expanded training data coverage and diversity (Sec. 6.3.1 and Sec. 6.3.2), synthetic data augmentation (Sec. 6.3.3), and integrating query-specific database content (Sec. 6.3.4). Using the test-time selection approach (Sec. 6.3.6), we further enhance accuracy by integrating SQL outputs from varions paradigms, leveraging execution leedback for refinement. Furthermore, we address one of the real-world challenges of navigating complex databases with a significant number of tables and columns, presenting effective methods for procise selection of pertinent database components improve Text-to-SQL. performance (Sec. 6.3.5).
6.1 Input representation
The primary step of modeling with LLMs is providing judiciously-designed input representations. We start from the database schema S and primary and foreign keys. Following Shaw et al. (2020); Scholak et al. (2021); Sun et al. (2023), we serialize the database schema as:
X1 x1=1T00 C (c cl
C...) (d out n ool
( ). (d()) C....)
n (2) (2)
(2) (d
(4)
cot cot
where T denote the k-th table name represent the j-th column name of the k-th table, and de/) indicate its data type (such number string). We use the symbol 'I' to represent the boundaries between different tables in the schema. Within each table, we use ':' to separate the table name from its columns, and we indicate cach column via the delimiter ',. We integrate the primary keys (Kp) and foreign (Ky) keys to denote relationships between tables. We refer the above schema design as "concise prompt", as it concisely presents the table structure 11, See Fig. 4 as an input example for the question given in Fig. and a realistic example Sec. A.10.1 in Appendix.
Besides the database schema, other forms of database content can be beneficial. We use database content. values VQQ) to match with the tokens in question to clarify the database value (see Sec. 6.3.4). We also incorporate column descriptions (des) and hints (If), which provide additional clarification domain-specific knowledge when applicable. We concatenate them together as:
X₂ des; V.Q).H
(5)
Combining all, we form the overall input sequence by concatenating X1, X2, the natural language query Q, and a SQL initiation marker "[SQT]]":
X = Xx;
(6)
where of is the prompt design to connect the concatenated components (e.g. '(database schema] is ...; [Primary keys]: .!) and human instructions ('Convert text SQL.") explicitly in X. See Fig. as an example.
11 An alternative way descrihe the schema would be "verbose prompt", where we human language describe the schema verbosely. See example in A.10.2.
10

---

Published in Transactions on Machine Learning Research (11/2024)
Input: X (Eq. 6)
Convert text to SQL
Pew-shot examples
[Schema]: Countrylangunge: CountryCode (Number), Language (String), ... Country: Code (Number)) Name (String).
[Primary Keys]: Countrylanguage: CountryCade Country: Code ...
[Foreign Keys]:Country: Countrylaangee CountryCode is equivalent to Country: Code I...
[Detailed descriptions of tables and columns]: # column description
The column 'IsOfficial' in Table Countrylanguage has column descriptions of "whether language official language"
[Database values that related with questions]: database content
The colurn 'Language" in Table 'Contrylanguagg" has database values: ['English', "French"]
[Additional Info]: # hints, if applicable
[Q]: What are the names nations where both Euglish and French are official languages? [SQL]:
The overall imput representation from Eq. 6. The basic prompts are in black (X, in Eq. 4), and auxiliary information is gray (X2 in Eq. 5), if the datasets have the corresponding information.
Figure 4:
6.2 Prompting LLMs for few-shot learning
We first consider the use of LLMs for Text-to-SQL with prompting. Given an LLMg and a question Q, represented as a sequence of tokens, the prediction in zero-shot prompting can be formulated as:
Y arg max PLLM. (Y(X),
(7)
where Y are the inferred SQL outputs, X, as described in Sec. 6.1, are the input prompts including database schema, other auxiliary information (i.e. hints) if applicable, and the question Q. 0 are the parameters of the pretrained LLM. With few-shot prompting, the formulation is extended to LLMs generating a sequence of tokens conditioned on the provided demonstrations pairs demo [(X1, ()), (X2, Y2).
Y arg Y PLLM, |demo, X).
Essentially, in few-shot prompting, the prompts prepend the natural language queries Q with list of demonstrations (inputs, SQL) pairs, and the LLM follows the input to generate answers in an auto-regressive way.
To further enhance performance beyond the standard few-shot prompting, we adapt an execution-based consistency decoding method, following (Sun et al., 2023; Ni et al., 2023). This method leverages on the unique benefit of coding task, including Text-to-SQL task, where the SQL outputs are executable. This serves as a preliminary validation for the generated output, allowing us to identify invalid results more casily. Concretely, our approach involves the following steps:
Step 1:
Sample multiple SQL outputs from LLMs: Given input X, sample m SQL outputs from the LY using a sufficiently high temperature, i.e. l2 = {M)), ~ (Y|X)
Step 2: Verify with execution The generated SQL outputs, (Ý:), are subsequently executed using executor E(-), which yields the corresponding results denoted as e, = E(Y)).
Step 3: Aggregate Execution Result. Since multiple SQL.s are valid for the same question, we aggregate the SQL outputs in Ω that give the same execution result. The execution output with errors is guaranteed to be wrong, and the execution outputs with the most occurrences are more likely to be correct (Wang et al., 2022). We remove programs with invalid execution results to update the LLM generation probability with the verification probability, and marginalize over SQL outputs with the same execution results. We use this aggregated probability as the ranking score R:
R(X,8)= Σ PLAM, (f(x) ([8()) = E()) ([8)) e Φ).
YER
where T indicate the set of valid execution results without yielding errors (denoted as 'execution error filtering)). We choose the outputs Y* that exccute the most probable result
y = arg max R(X,Y) f).
(10)
Yea
11

---

Published in Transactions on Machine Learning Research (11/2024)
6.3 Model tuning
Despite the significant advances achieved with few-shot prompting of LLMs, it remains a formidable challenge for pretrained LLM to rely solely on its parametric knowledge and prompting to accurately process highly- complex SQL queries (Sec. 2). Such queries often involve sophisticatod semantic logic, complex database schemas, and database contents with numeric edge cases necessitating extensive clauses 12 for each case (See Fig. 2). Additionally, the SQL logic may require the use of clauses that were underrepresented in the pretraining corpus 13, To address these more intricate scenarios, this section focuses on tuning, wherein we refine the pretrained LLMs to better align with customized Text-to-SQL distribution. This paper delves into crucial training paradigms that influence the tuning efficacy of LLMs, including expanding the range and diversity of training data, leveraging synthetic data, integrating query-specific database content, and optimizing table and column selection. We then introduce a test-time selection approach that integrates these diverse training paradigms, aiming to enhance accuracy through the utilization of execution feedback.
6.3.1 Instruction tuning
To improve the SQL expertise of pretrained LLMg, we propose adapting pretrained LLM, to generate SQL from the imput sequences by tuning the model with Text-to-SQL datasets (Wei et al., 2021). The training data contain a collection of serialized inputs X & corresponding SQL. outputs Y pairs, sampled from the Text-to-SQL distribution derain- The training objective is based on maximizing the log probability of co-appearance of the training data (X, Y):
max (xdyen log PLLM, (YIX),
(11)
where X = f(S, Kp, Kp, H. H) 14 are the scrialized inputs as discussed in Sec. 6.1. The optimal H can be obtained by tuning of LLMs with conventional language modeling objectives:
ft = arg max
Σ (X,Y)
log PLLMA (Y
(12)
#
6.3.2 Diversifying tuning data coverage
Tuning LLMs would require sufficient amount of data given their large model size (Kaplan et al., 2020). This section focuses on enhancing model tuning through the use of diverse datasets. The rationale is that diverse datasets provide a more comprehensive coverage of SQL knowledge to enrich LLMs. However, a notable challenge of using more than one datascts is that they can be very different, which can lead to a decline in performance due to distribution shifts. This means that model trained on a particular dataset may not perform well another datasets common issue for machine learning even for the pre-LLM era. Concretely, Text-to-SQL datasets vary significantly from different perspectives: (i) they encompass wide range of topics, c.g. from healthcare, retail, and finance etc., each requiring specific knowledge; (ii) the clarify of the database schema varies, with some containing straight table or column names, while others are vague and demand further exploration or additional descriptions; (iii) the sizes of dataset range widely, setting different challenges on schema linkage challenges; and (iv) the quality of database content values might contain variations with some datasets having columns filled with NULL data that need filtering, while others not needing that. Given such diversity, it remains an open question how combining multiple training datasets improves tuning performance. Towards this end, extend Eq. 11 as:
max (xx log PLLM, (LLM, 8
(13)
where dmix is a mixture of |d] datasets: dmix {di)==njd and X Kr, whether the pretrained LLM, when tuned with a wide range of inputs from various datasets, can learn to understand these diverse inputs presented in different datasets rather than overfitting to specific patterns, and thus generalize better.
H) We investigate
12 An example is using CASE statements and regular expressions
13 An example is conditional expression (c.g., CASE, IFF, PARTITION clauses) and WINDOW functions. 1e discuss incorporation of database values V and column descriptions des in Sec. 6.3.4 illustrate the effect of the key factors one at a time.
12

---

Published in Transactions on Machine Learning Research (11/2024)
6.3.3 Augmentation with synthetic data
As previously described, introducing diverse datasets can improve tuning. We further extend this by using diverse synthetic SQL data to augment real datasets, especially considering the high cost to obtain real-world data. Since LLMs are pretrained with massive datasets, their prior knowledge can be utilized to create new information and augment training via synthetic SQL data.
For the same natural language questions, there are usually multiple SQLs that are correct with the same execution outputs'.. 15, Utilizing this, we focus on synthesizing data to incorporate the multiple ground truth SQLs. We start from a Text-to-SQL dataset, for each (natural language question, SQL)-pair in the dataset, we keep the database schema and natural language question unchanged, and generate new SQLs that are correct. but different from the ground truth SQLs. To achieve this, we query LLM with carefully-crafted prompts which include database schema, natural language question, and the ground truth SQL, and request LLMs to generate a SQL that is different from the ground truth and to output a similarity score. The similarity score indicates how similar the candidate is from the true SQL. Details of prompt design F are explained in Sec. A.5.1 in Appendix. Given the database D, question Q, and original ground truth query SQL", we query the LLM to generate a different SQL output, and estimate its similarity from SQL*, formulated as:
(SQ similarity (8) = (D."(P(
(14)
where SQL(S) is the generated SQL output, similarity( is the similarity score outputted simultaneously with SQL output and F is synthesis prompting design. LLM can be any LLMs, but idcally different from the LLMs that are used for tuning so that they can introduce new information.
Synthetic data generation comes with two challenges: accuracy and diversity. Accuracy refers to the generated SQLs are correct SQL for the natural language query. Diversity refers to the generated SQLs bring new information that is different from original data. To ensure the generated SQL outputs are correct, after the generation we evaluate the SQL 16 and keep the correct one. To ensure the diversity, we only keep the SQL with similarity score below a threshold.
After generating synthetic data, we augment real datasets with them, and the training objective becomes
max E () log PLLMA (X)X),
(15)
0
For this approach, we only synthesize target SQL without synthesizing new natural language questions databases, because want to ensure that the generated SQL, accuracy high" We leave for more open-ended synthetic data generation methods (e.g. altering questions, SQL, or data schema, etc) for future work.
6.3.4 Integration of query-specific database content
Incorporating database content can be crucial for Text-to-SQL performance, particularly when natural language questions refer to specific data values different from table or column names 18, In some scenarios, without access to the database content, it would be infeasible to formulate accurate SQL queries based solely on the database schema and question, even for 19 Furthermore, when multiple column names seem relevant to question, database values containing the keywords from the question can help identify the appropriate 20lumns 20, Overall, access to database content can be critical for improving Text-to-SQL.
For example, SQLI = "... order by score DESC LIMIT 1" and SQL2 = "... WHERE score=max(score)" are equivalent The result of the generated SQL match the result of the ground truth SQL
For example, modifying new natural language questions of database schema lead to the situation where original ground truth SQL cannot be used to evaluate syuthetic SQLs, as the natural questions have been changed
Foror example, a user might ask," 'What is the population in Santa Clara?" However, if the database only has an entry for 'Santa Clara County," 1 the correct SQL query should be SELECT WHERE county = "Santa Clara County', , not WHERE county *Santa Clara'.
As they would not know exact format used in the database. 2o example, if the question 'Please list schools in Fresno County Office of Education?', and the databasc has columns like "District', DDistrict Name", ; and "dname", knowing that only "District Name" has database values "Fresno County Office of Education" indicates that this Disttrict. Name" is the column use.
13

---

Published in Transactions on Machine Learning Research (11/2024)
Database content is often much larger, compared to the imput sequence (database schema, the question, and etc). It is not preferable to include all database values to the inputs, because firstly, total values could go beyond the limitation of the imput length. Secondly, the valuable information, such as database schema, can be lost in massive irrelovant values. To address this challenge, we propose inchuding only a limited number of entries ((Q) that are directly relevant the question.
We first describe the process of extracting the relevant database values relevant to the question, inspired by (Lin et al., 2020). Suppose natural language question Q is broken into words, and each word is considered as a key word: Q = [wi, W2, ..):: For each keyword wi, we conduct a matching against all entrics (u/g) in each attribute (columm) across all tables, and select the ones above the pre-defined threshold 8. To prevent the inclusion of extraneous irrelevant database content, we limit the selection to be topx values:
[[Fm (w,, (hl) > 0), Wk = :::n = (k) Vj = no ] topk],
V(w;,) = (1rf]) (1)
(16)
(17)
where (/4 is database content value of rth-row jth-column entry of kth-table and F,, is the matching algorithm. Here, we use Fm as the longest contiguous matching subsequence approach (Cormen et al., 2022),
as it allows us to accurately extract the exact values stored in the database. This precision is particularly important for some SQL queries"1. Finally, the total matches for the entire question Q is determined by aggregating all the matches of individual keywords:
V(Q) (V(w,)ii=1: QI
(18)
Additionally, Fm can also be instantiated using LLM inference (querying LLM with prompt and asking whether wi and (h) match") or embedding similarity (e.g. the cosine distance between the embedding of we and orj above a threshold is a match). We choose to use the above proposed fuzzy string matching approach as it is cost-effective and fast. We leave more investigations on F.. to future work.
With the proposed strategy of sclectively including relevant database content, we propose training with question-specifte database content via jointly modeling of the conditional probability:
max B(x,y) log PLAmo V(O)) 8
(19)
with the scrialized imput X X Kp, Kr, Kr, H, Q). See demo example in Fig. 4 (basic prompt + "[Database values that related with questions/")) and real example in Sec. A.11 in Appendix. V(Q) is database content relevant to Q. To enable effective training, we break down Eq. (19) into two stages. First, extracting relevant database content associated with the natural language question V(Q). Second, with the extracted database content, the LLMs are trained to generate output SQL programs Y using both the input sequence X and the relevant database content. This approach failors the training process to better reflect realistic scenarios when LLMs consider specific database entries relevant to the user's query. Consequently, the training objective is formulated as:
log PLLMO ((0))
0' = arg max
Σ (X.Y)
(20)
21 For example, being able distinguish subtle differences "Santa Clara" vs "Santa Clara County"; "apple" vs "apples" 22For databases with column names without parentheses like those in Spider dataset Yu et al. (2018), to incorporate database content into X, we append the identified database values to their respective column names in the input sequence's schema, separated by a delimiter "("" This approach aligus with (Qi et al., 2022; Xic et al., 2022). For instance, the database schema is represented Ti (V)),, C2, Ca(V)) .. and it indicates that only columns C1 and C3 contain relevant database content, while C2 docs not. For datasets with column names that include parentheses, like those in BIRD datasct (Li et al., 2023d), add the relevant datahase content separately, clearly specifying the values corresponding which columns in particular tables, to avoid confusion caused by the delimiter "()".
14

---

We start from inferring column selection. Text-to-SQL datasets typically do not explicitly provide the ground truth for the relevant columns (Q). We propose extraction of this information from the true SQL queries Published in Transactions on Machine Learning Research (11/2024)
6.3.5 Table and column selection
Handling real-world datasets poses a significant challenge to Text-to-SQL due to the large number of tables and columns. This challenges can arise when including the entire schema within the prompt limit is infeasible. Even when the schema fits, the increased number of columns adds complexity to the reasoning problem LLMs struggle in scenarios resembling "finding needle in haystack" (Liu et al., 2023b). Thus, careful selection of relevant tables and columns is crucial for improving Text-lo-SLL performance (Lei el al., 2020a).
Column selection is to select a subset of columns that are relevant for a given natural language question, so column selection (Q) is a function of question Q. To model Text-to-SQL with column selection, we formulate the problem as modeling the joint probability of the conditional probability of column selection se (Q) given the input sequence X, and the conditional probability of generated SQL program Y given the input sequence X and column selection (Qe:
max E log PLLMA (Y|X, Zoot (2))())) 0 (X.Y)
(21)
where 3 represents the parameters for the column selection model. Due to the prohibitive computational challenges of joint modeling of LLMs,, we instead digest the objective into two steps: first, modeling the selection of columns; and then, integrating these selected columns into the Text-to-SQL modeling process.
ya. The selected columns are the columns that are referenced in the true SQL query. Concretely, for the k-th table in the database, the selected columns are represented as:
Z (
(k) , iisj (1) (Q) = (c,*t) E E c.fT) ST
(22)
where c0 is j-th column of k-th table and ST is database schema. For the entire database schema, we aggregate the column selection of individual tables:
(k)
n1 U L (Q). 1=1
Z (Q)
(23)
Similarly, the selected tables are identified based on their presence in the ground-truth SQL.
W ( = (T< E Y
s ,1 S K <
(24)
where T/(h) is table name of k-th table. We consider two approaches for column selection:
Retrieval-basee column selection: Retrieval-augmented generation has proven to be an effective and efficient method for handling large contexts in generative tasks. We employ a similar approach for the Text-to-SQL task, utilizing a schema retriever based on ncarest neighbor scarch. Given natural language query, we identify the closest columns in the semantic space. We opt lo retrieve columns instead of tables to achieve a more refined and granular selection. Once the columns are identified, we group them according to their respective tables to construct the selected schema. The retrieval corpus is defined as the union of all table columns. The method initiates by calculating embedding representations for both the query and columns using pretrained embedding model denoted as F. Specifically, we represent. the query embedding as QE = E(Q) and the embedding of j-th column as Fj = E Subsequently, the column selection score 8 (k) is defined as the cosine similarity between the query and the column vector embedding. The topk columns closest to the query are then obtained based on this score:
CosineSSimilarity((pe. (th)) QE Bj
(25)
||
To generate column embeddings, we construct a sentence for each column by combining diverse pieces of information, including the column name, column type, column description, table name, and set of the most
15

---

Published in Transactions on Machine Learning Research (11/2024)
common distinct values. This text serves as the input to the embedding model. The specific templates and illustrative examples are presented in Appendix A.6.1.
beasee approach can be parallelized with tensor operations to efficiently scale to a large number of tables and columns with low cost and latency. It also offers controllability via the typer-parameter topk, which can adjust recall. It. can effectively reduce the risk of false negatives.
Program-aided column selection: Solving problems with LLM using coding representation has demon- strated impressive results variety of tasks (Gao et al., 2023b; Mishra et al., 2023). This is because coding offers greater precision than natural language descriptions and it bypasses the ambignities inherent in natural language. Program-aided column selection is an approach to infer column selection using preliminary SQLs. Specifically, we use initial LLMs (denoted as LLMpre) to generate a preliminary SQL query Y.
= LLM
(26)
Following the procedure used to infer ground truth col- umn selection from true SQL (Eq 23), we generate col- umn sclection from the preliminary SQL Y. The inferred column selection is determined as:
Program-aided column selection
Preliminary SQL generation:
12-7
Thy
SELECT 'FRPM Count (K-12) ''Enrolnentt (K-12)' FROM
7gen (Q)
gen (Q)
U k=1
(K) EP n moa) (27)
(k)
frpa WHERE 'County Name 'Alaneda" ORDER BY (CAST('
k=1
FRPM Count (X-12)' A8 REAL) / 'Earollnent (K-12)))
DESC LIMIT
The selected table name: Expe
To ease the matching process, both the preliminary SQL and schema are normalized to lowercase. In practice, we
pinpoint "selected tables" by identifying elements in the SQL following "FROM" or "JOIN" keywords that match table names in the scherna. "Selected columns" are then identified by locating column names that appear both in the SQL and the schema of the chosen tables. See the output of program-aided column selection in Fig. 5.
The selected column name:
FRPX Count (X-12), Enrollment (X-12), County Nane
Figure 5: Program-aided column selection.
The initial models used to generate preliminary SQLs can be standard Text-to-SQL framework to achieve better performance, or some less capable models due to various constraints. For example, when dealing with large datasets with many columns, in some scenarios, the prompt length limit can only fit column names, leaving no space for auxiliary information, such as data types and database content, or descriptions. Using these limited inputs (only the schema), we can generate preliminary SQL queries for column selection. Then the selected columns, we can apply complete prompt. (schema plus auxiliary information) to obtain more accurate SQL. Another scenario involves prioritizing the reduction of computational costs and latency, where smaller initial language model may be employed. Although preliminary SQLs generated from the initial model may not be highly accurate, they can be effective for generating column selection because column selection requires less details than Text-to-SQI. task.
Program-aided column selection has the following advantages: The number of columns selected by this approach is low as there are limited number of columns referenced in preliminary SQL queries. So this often leads to high precise of column selection. Additionally and importantly, program-aided column selection fosters mutually reinforcing cycle enhanced SQL accuracy improves column selection efficacy, which, in turn, increases the accuracy of future SQL. queries. This iterative enhancement process can lead to progressively higher levels of accuracy.
Integration of column selection to Text-to-SQL pipeline: We explore two approaches to incorporate column selection:
Soft column selection is the approach where, rather than removing the unselected columns from the database schema S, we emphasize the selected columns by adding their column descriptions to the prompt:
X= = (S, KF, H. Des (z,.H.Desszza.))))
(28)
16

---

Published in Transactions on Machine Learning Research (11/2024)
Soft column selection be considered a way to effectively incorporate column descriptions of relevant columns. This method's advantage lies in its resilience to errors in column selection; such errors have minimal impact on the Text-to-SQL task since the model continues to have access to the entire database schema in the prompt. See a example of inputs in Sec. A.11.2 in Appendix.
Hard column selection refers to that scenario that in the database schema S, only the chosen columns are included, while the non-selected columns are omitted:
x= f( Kp, KF, H, Q)
(29)
This approach has the advantage of considerably shortening the length of the data schema by removing irrelevant columns, which in turn increases the chance that LLMs concentrate on more critical information. Additionally, it also acts as an essential preproccssing stop that facilitates the application Text-to-SQL when the prompt length is insufficient to accommodate the full schemna However, inaccuracies in selecting columns can lead certain errors in the Text-to-SQL task.
Column selection can be utilized in both few-shot prompting and tuning setups. For prompting, we integrating column selection into the prompt and follow the procedures as outlined in Sec. 6.2; For tuning, column selection is applied to the inputs and follows procedures in Sec. 6.3.1.
6.3.6 Test-time refinement via execution-based selection
In previous sections (from Section 6.3.1 to Section 6.3.5), we have outlined various training paradigms. Each section focuses unique facet of Text-to-SQL, resulting in the generated SQL that exhibit distinct advantages. Through empirical analysis, observe that these produced SQL have diverse accuracy coverage - the questions correctly answered by one training paradigm often differ substantially from those addressed by others. This diversity suggests that selecting the appropriate SQL can be viable strategy for inlegration of multiple training paradigms. To this end, we introduce an approach called test-time refinement via enecution-bused selection to identify the correct SQL at test time by analyzing exccution outcomes. A fundamental advantage of Text-to-SQL is SQL programs are executable. If SQL program leads to an invalid execution, such error messages, the SQL can immediately be deemed incorrect. However, valid execution outcome does not guarantee the SQL is correct. To identify correct SQL, execute the generated SQL outputs for each question across multiple training paradigms and select the SQL that, while producing valid results, has the execution outcomes supported by the majority of the paradigms. This approach is detailed in Algorithm 1, providing a systematic method for integrating multiple training paradigms to improve SQL query generation accuracy. Similar with execution-based consistency decoding for prompting approach in Sec. 6.2, we consider majority of the execution outcome as a judgement for good SQL. The difference between the two is the candidates in Sec. 6.2 come from sampling from the same setup multiple times, whereas here candidates are come from diffcrent training paradigms.
An alternative approach involves combining different input configurations in previous sections into a single training experiment. This method entails integrating various factors, such as mixed training data, synthetic data, database content, and column selection, into the inputs for a single experiment. However, unfortu- nately, the results of such experiment reveal that merging these components does not result in performance improvements over using them individually. This suggests that LLMs may struggle effectively process and understand all the provided information simultancously during tuning.
7
Experimental Setup
7.1 Tasks and datasets
We consider publicly-availe large-scale Text-to-SQL benchmarks. Spider (Yu et al., 2018) contains 7000 training samples across 166 databases and 1034 evaluation samples ('Dev split') across databasos from a variety of domains. Spider-SYN (Gan al., 2021a) is a complex variant of the Spider dev split, created through the manual replacement of synonym substitutions in natural language queries. Spider-realistic
17

---

Published in Transactions on Machine Learning Research (11/2024)
Algorithm Test-time refinement via execution-based selection
Input: Database D. number of questions. {SQL,) from training paradigms. SQL exccutor E Output: outputs
for = to N do for j = to P do
5: exccutions = =]
6: indexes =
7:
e e= E D)
= valid then executions t indexes t-)
10:
11: end if
12: end for
outputs + (SQLIIE(SOLL,, arg max, (counts (exectttexett E indexes} > Among SQLs without exccution error, select the SQL that gives execution output with maximum number occurrences. end for
13:
14:
(Deng et al., 2020) samples 508 text-SQL pairs from Spider dev split removing explicit mentions of column names in natural language queries. Spider-DK (Gan et al., 2021b) samples 535 question-SQU. pairs on 10 databases from Spider dev split and incorporates domain knowledge to them. BIRD (Li et. al., 2023d) is a comprehensive dataset containing 9428 question-SQL pairs for train split and 1534 pairs for dev split, across databases totalling a size of 33.4 GB. covers a broad range of over 37 domains, including finance, sports, healthcare, and education. Uniquely, BIRD incorporates four lypes of external knowledge sources (numeric reasoning, domain-specific information, synonyms, and value illustration) to enhance the accuracy of SQL query generation. Compared with Spider, BIRD SQLs are typically more complex because of longer SQL, more keywords, more JOINs, and so on. BIRD also contains more challenging databases more database entries and larger number of tables and columns. Statistics of the number of tables and columns of BIRD are shown in Appendix A.7. Note that BIRD datasets remove the errors on September 2023, however we conducted all our experiments on previous BIRD version before the error correction. Our performance could be higher with the latest version.
7.2 Models
PaLM-2 is a Transformer-based model trained using a mixture of objectives similar to UL2 (Tay al., 2022), which an improved version of its predecessor PaLM (Chowdhery et al., 2022) by efficiently applying compute-optimal scaling, improved training dataset mixture, improved model architecture and objective. The PaLM-2 used here is Unicorn variant fine-tuned collection of improved datasets mixture phrased instructions following (Wei et al., 2021; Chung el. al., 2022).
7.3 Experiments
For few-shot prompting, temperature of 0.5. The inputs of the model includes database schema, data type, primary keys, foreign keys, database content, and the question. For fine-tuning, we choose more challenging datasct BIRD. The inputs are described in each experiment. We train until convergence, and the number of steps is no more than 10K steps.
Spider datasets. For each question, sample PaLM-2 32 times with
LoRA finetuning: Following Hu al. (2021), we incorporate trainable linear low-rank modules into the query and value projections of each aelenttton layer. We set the rank of LoRA to 32, learning rate 1c-4, and the model architecture Gecko PaLM model.
18

---

Published in Transactions on Machine Learning Research (11/2024)
7.4 Baselines
We list several relevant baseline methods in this section. Fine-tuning baselines: PICARD (Scholak et al., 2021) employs incremental parsing constrain auto-regressive decoding. RASAT (Qi al., 2022) is a transformer model that integrates relation-avee aware self-attention and constrained auto-regressive decoders. RESDSQL (Li et al., 2023a) decouples schema linking and skeleton parsing using ranking-enhanced encoding and skeleton-awarr decoding framework. In-context learning baselines: (Rajkumar et al., 2022) comprchensively evaluates the Text-to-SQL ability of CodeX and GPT3, while (Liu et al., 2023a) conducts a thorough evaluation on ChatGPT. DIN (Pourreza & Rafiei, 2023) decomposes the Text-to-SQL. tasks into sub-tasks: schema linking, query classification and decomposition, SQL generation, and self- correctit:;; then perform few-shot prompting with GPT-4. DIN only provides test-suite (TS) evaluation results, so we run execution accuracy (EX) evaluation with their provided SQL outputs. Self-dcbugging (Chen et al., 2023) appends error messages to the prompt and performance multiple rounds of few-shot prompting to self-correct the errors. Self-debugging only reports execution accuracy (EX). DAIL-SQL (Gao et al., 2023a) provides systematic investigation on prompt designs, including question representation and example selection on few-shot prompting and fine-tuning paradigm.
7.5 Evaluation
Text-to-SQL evaluation: We consider the two commonly-used evaluation metrics: ezecution accuracy (EX) and test-suite accuracy (TS) (Zhong et al., 2020). EX consists of one test - mcasuring whether SQL execution outcome matches that of ground-truth. TS consists of multiple EX tests - measuring whether the SQL passes all of the FX tests, generated by augmentation of the database. Since TS requires passing of more tests, we consider TS a more reliable evaluation metrie. Note that exact match evaluation is not performed, as multiple correct SQLs exist for single query. For Spider dataset, follow the official evaluation protocol of Spider For BIRD dataset, follow BIRD official evaluation BIRD does not have augmentation of test datasets, so BIRD does not have TS evaluation. Additionally, we provide Valid Efficiency Score (VES) (Li et al., 2023c), which is designed to measure the efficiency of valid SQLs generated by models for BIRD dataset. VES metric considers both the efficiency and accuracy of execution results.
Column selection evaluation: To evaluate the accuracy for retricval of columns and tables, we report recall, precision, and F1. We compule these metrics and report the averaged metries across all samples. recall is the proportion of relevant columns correctly identified, e.g. identified relevant columns /true relevant columns, whereas precision is the proportion of the selected columns that is relevant, e.g. identified relevant columns / identified columns. Finally, F1 is defined as precissioon - (eall)/(precision + recall).
8 Results
We present the performance of our proposed framework, SQL-PaLM, in both few-shot prompting and tuning settings. For few-shot prompting, we focus on the Spider benchmark, which recognized for its high-quality assossments, including both "crecution accuracy" and "test suite accuracy". For tuning, focus the BIRD benchnark, known for its complex SQL and sophisticated database schema, to better different iate various methods. Both benchmarks are assessed on their respective dev split, which are publicly accessible, in contrast to their private test 2 For intermediate results or ablation studios, we select representative and tigh-performin methods as baselines for comparison. BIRD and SPIDER prompt design are the same format.
htttps://yale-liiy http://............peeeeeeeeeeee
2hattps://bird-banch tit..b.ittttooo//oooooo
25rThse test. split are only available through evaluation servers hosted by the benchmarks' creators (refer (Yu al., 2018) and (In al., 2023d) for more details).
19

---

Published in Transactions on Machine Learning Research (11/2024)
8.1 Few-shot prompting setting
8.1.1 Ablation studies
Table 1 shows the elficacy Pew-shol SQL-PaLM on the Spider dev set. We utilize the concise prompt design with four demonstrations due the better performance compared against other prompt designs (Appendix Sec. A.1). We conduct ablation studies showing the roles of exccution-based consistency decoding and error filtering played in enhancing model performance. The results indicate omitting either component results in a performance degradation of 4.9% and 3.5% respectively, highlighting the substantial contributions to the overall performance.
Table 1: Few-shot prompting Spider dev split. We present both execution accuracy (EX) and test. suite accuracy (TS). Ablation studies provided removing execution-based consistency decoding error filtering respectively.
Execution accuracy (EX)
Test-suite accuracy (TS)
Few-shot SQL-PaLM
82.7%
77.3%
Ablation scenarios
Execution- based consistency
77.3%
72.4% (1 4.9%)
Error filtering
79.0%
73.8% (4 3.5%)
8.1.2 Performance for different SQL difficulty levels
In our analysis, we evaluate the efficacy of SQT--PaLM against a spectrum of SQL difficulty levels, which are categorized based on several factors, including the number of SQL keywords used, the presence of nested sub- queries, and the application of column selections aggregations. The results in Table 2 highlight SQL-PaLM performance in comparison with standard few-shot prompting approach using GPT-4 and Davinnii as well as the advanced prompting approach DIN-SQL (Pourreza & Rafici, 2023). Our findings reveal that SQL-PaLM consistently surpasses the alternative approaches across all evaluated difficulty levels.
Table 2:
Test-suite accuracy Spider dev split with SQL outputs being categorized by difficulty levels. The first four rows are taken from (Pourreza & Rafiei, 2023), and specifically the first two rows are based on standard few-shot prompting.
Methods
Model
Easy
Medium
Hard
Extra Hard All
Few-shot
CodeX-
84.7%
67.3%
47.1%
26.5%
61.5%
Few-shot
GPT-4
86.7%
73.1%
59.2%
31.9%
67.4%
DIN-SQL
CodeX-davinci
89.1%
75.6%
58.0%
38.6%
69.9%
DIN-SQL
GPT-4
91.1%
79.8%
64.9%
43.4%
74.2%
Few-shot SQL-PaLM
PaLM2
93.5%
84.8%
62.6%
48.2%
77.3%
8.1.3 Robustness evaluations
The Text-to-SQI. models frequently encounter challenges robustness, such as translating questions into SQL queries when the terminology differs from the database schema or when specialized domain knowledge is required. To address these, variants of the Spider dataset have been created, detailed in the Table 30 in Appendix. These include the 'Spider-Syn' and "Spider-Realisee Realistic variants, which alter natural language queries by substituting direct schema references with synonyms or by excluding explicit mentions altogether, respectively. Additionally, the "Spider-DK" variant incorporates domain-specific knowledge into the scherna. To determine if Few-shol SQL-Pal.M capable of overcoming such robustness challenges, we evaluate its performance on these Spider variants.
In Table 3, we compare Few-shot SQL-PaLM with previous Text-to-SQL methods. Among these, methods such T5-3B PICARD (Scholak et al., 2021), RASAT PICARD (Qi et al., 2022), and RESDSQL-3B
20

---

Published in Transactions on Machine Learning Research (11/2024)
+ NatSQL (Li et al., 2023a) rely on tuning-bass strategies In contrast, ChatGPT (Liu et al., 2023a) and SQI-PaT.M utilize few-shot prompting methods without further training. While LLMs naturally have the ability to perform reasoning, including understanding synonyms through extensive pretraining, prior evaluations with ChatGPT (Liu et al., 2023a) have demonstrated significantly lower effectiveness compared the training-basee methods. This altributed to the generation challenge of Text-to-SQI. However, Few-shot SQI-PaLM which also adopts a few-shot prompting strategy, has shown to achieve results on par with the best-performing training- based method (RESDSQL-3B + NatSQL), consistently outperforming other approaches. This outcome highlights the potential of Few-shot SQL-PaLM in addressing the robustness challenges.
Table 3: Evaluation of Few-shot SQL-PaLM on Spider variants: Spider-Syn, Spilsr-Realistic
and Spider-DK. Spider-DK does not contain augmented tests so test suite accuracy is not available.
Spider-Syn
Spiler-Realistic
Realistic
Spider-
EX
TS
EX
TS
EX
TS
T5-3B + PICARD (Scholak et al., 2021)
69.8
61.8
71.4
61.7
62.5
RASAT + PICARD (Qi et al., 2022)
70.7
62.4
71.9
62.6
63.9
RESDSQL-3B + NatSQT. (I. et al., 2023a)
76.9
66.8
81.9
70.1
66.0
ChatGPT (OpenAT defanlt Prompt) (Liu et al., 2023a)
58.6
48.5
63.4
49.2
62.6
Pew-shot SQL-Palm (Ours)
74.6
67.4
77.6
72.4
66.5
8.1.4 Improving few-shot prompting with column-selection
Table 4 demonstrates improved performance of SQL-PaLM on BIRD with column-selection enhanced few-shot prompting, which applies the soft column selection approach (Sec. 6.3.5) to the few-shot prompting (Sec. 6.2). We use the BIRD dataset instead of Spider, as it has larger database schema, where column selection can yield larger impact. We opt for the verbose prompt in our experiments due to its superior performance (Table A.2 in Appendix). The results show that compared with few-shot prompting baseline The proposed approach, column-selection enhanced few-shot prompting, improves performance 2%. To further understand the potential, we also investigate the upper-bond of the proposed method, where apply the ground truth column selection. In this setup, we observe an improvement of ~ 5.7%, which provides motivation for further improving column selection performance for better Text-to-SQL, performance.
Table 4: Evaluations of column-selection enhanced prompting BIRD dev split.
Methods
EX
Few-shot SQL-PaLM
43.02%
+ Soft-column selection (inferred) based description
45.05%(t 2.03%)
+ Soft-column selection (GT) based description
48.70%( 5.68%)
8.2
Tuning settings
In this section, present results for exploring the effect of various training paradigms that influence tuning performance of LLMs. We use the following experiments to answer questions proposed in Sec. 4.
8.2.1
Performance comparisons with few-shot prompting
"In what scenarios the improvements are observed to be more significant?" On more challenging datasets.
For instance, fino-tuning T5 model The preliminary SQLs used in column-sclection euhanced few-shot prompting is the baseline shown in the first line of Table The input sequences for all the experiments in this table formed of datahase schema, data type, primary keys, foreign keys, and the natural language question. Database content not included.
21

---

Published in Transactions on Machine Learning Research (11/2024)
We first explore the improvements with tuning compared to few-shot prompting. Tables and 6 show the comparisons on BIRD and Spider. For both, tuning demonstrates superior results, highlighting the LLMs proficiency to adapt to high-quality Text-to-SQL training data. Notably, tuning yields a larger improvement on BIRD, (~~ 8.5%), compared to Spider (~ 1%). This suggests that the benefits of tuning become increasingly important more complex Texl-to-SQL tasks. Given the significant improvements observed on BIRD, we conduct our tuning investigations primarily on it.
Table 5: Evaluations on BIRD dev split with few-shot prompting and tuning.
Adaplation approach
EX
Few-shot prompting
45.05%
Tuning
53.59% (1 8.51%)
Table 6: Evaluations on Spider dev split with few-shot prompting and tuning.
Adaptation approach
EX
TS
Few-shot prompting
82.7%
77.3 %
Tuning
82.8%
78.2 % (1 0.9%)
8.2.2 Scaling model size and different foundation models
"How about tuning with different foundation models: PaLM-2 vs LLaMA? Does foundation models' properties, such as parametric knowledge, motter?" Yes, stronger models help with tuning.
Another investigation is whether tuning performance increases with the model size. Table 7 shows results for tuning PaI.M-2 Gecko versus PaLM-2 Unicorn model BIRD dev split after training BIRD train split?s. Despite limited training samples, the larger model has significant improvement.
Table 7: Exccution accuracy on BIRD dev split using PaLM models of different sizes.
Model size
Gecko
Unicorn
EX
33.96%
55.8%
Table 31 in Appendix A.4 shows the results with tuning open-source models LLaMA7B, LLaMA13B, and LLaMA33B Spider using the best input reprosentation reported in Gao et al. (2023a) 29 Compared with PaLM-2 tuning results in Table 6, LLaMA's fine-tuning results are about 10% lower, that is attributed mainly to the capability of base foundation models. Overall, despite the tuning involving updating parameters, foundation models with larger sizes and better reasoning abilities are observed to be beneficial for tuning Text-to-SQL.
8.2.3 Comparisons with parameter efficient tuning
"How is Text-to-SQL performance with parameter efficient tuning compared with full supervised tuning (SFT))"
"Since train data is limited, is LoRA better than SFT?"
SFT is observed to be better even with limited tuning data.
Next question is whether tuning benefits from parameter efficient tuning such LoRA, as we do not have significant amount of training data. Table 8 presents results on tuning a PaLM-2 Gecko using full supervised tuning versus LoRA. The results reveal that full model tuning has clear advantages over LoRA even in the
28 inputs of the the same. Input sequence including database 29 Gao al. (2023a) mainly reports tuning results SPIDER datasets LLaMA, not BIRD
22

---

Published in Transactions on Machine Learning Research (11/2024)
limited data regimes that have considered with Spider and BIRD, suggesting that the customization for improved Text-to-SQL can benefit. from more learnable parameters.
Table 8: Evaluation of BIRD dev Split,, PaLM-2 Gecko
Model method Full Supervised Fine-tuning
LoRA
EX
33.96%
15.84%
8.2.4 The impact of training data diversity and generalization
"What kinds of tuning data might be more helpful?" Complex SQLs are observed lo be more useful.
Text-to-SQL benchmarks can be quite different from each other. We explore whether training on more datascts, despite of the diversity can help with tuning performance. We tune LLMs on combination of Spider and BIRD datasets, and evaluate their performance on each. Table 9 shows that when evaluating on the BIRD dev split, model trained on both BIRD and Spider outperforms model trained solely on BIRD. Similarly, Table 10 illustrates the improved performance on the Spider dev split when trained on both datasets, compared to training only Spider. The results suggest that tuning LLMs various datasets benefits tuming performance, and the model after tuning is more robust to distribution shifts, indicating the tuned models are not over-fitting on train dataset. BIRD contains more complex SQL queries compared to Spider. We observe a more significant performance improvement on Spider when BIRD data are incorporated, compared to the improvement seen on BIRD when Spider data are incorporated. This implies that introducing complex SQL queries into training can yield larger benefits compared with less complex SQL samples. BIRD and SPIDER prompt design are the same format.
Table 9: Evaluations BIRD Dev Split with different training data used for tuning.
Train data Execution accuracy
BIRD Only
53.59%
BIRD + Spider
55.15 (1 1.56%)
Table 10: Evaluations on Spider Dev Split with different training data used for tuning.
Train data
Execution accuracy Test-suite accuracy
Spider Only
82.8%
78.2 %
BIRD Spider
86.8% (f 4%)
82.8 (f 3.5%)
8.2.5 Incorporating database content
"How does the datobase content help tuning?" It clarifies mismatches in questions and database.
We explore whether introducing question-specifia database content benefits tuning performance. Table 11 presents the improvements achieved by incorporating database content The results indicate more than 3% accuracy improvement when testing the BIRD dev split, highlighting the positive impact of incorporating database content.
We further provide two case studies to illustrate why database content would help. One scenario arises when there is disparity between the words used in natural language query and the words used in the database, as exemplified in Fig. As another example, Fig. 7 illustrates how database content can serve as valuable
300ther than the difference SQLs database. The provided information be different. BIRD has hints, column descriptions, etc; Spider has none of them
W1We train both BIRD Spider datasets, as they bring good performance described in Sec. 8.2.4
23

---

Published in Transactions on Machine Learning Research (11/2024)
Table 11: Evaluations on BIRD Dev Split with and without database content.
Train data
EX
Without database content
55.15% 58.80% (1 3.65%)
With databasc content
cues for LLMss to identify relevant columns when multiple columns seem relevant for the question. This is situation when both LLMs and human experts have a difficult time to deciding which columns to use. The database values presented in Figs. 6 and 7 encompass all the keywords in the questions, not limited to the specific keywords discussed in this context.
Study
Question:
What is the highest eligible free rate for K-12 students in the schools in Alameda County?
SQL:
SELECT 'FRPM Count (K-12)' / 'Farollment (K-12)'
FROM frpm WHERE "County Name = 'Alameda'
ORDER BY (CAST(FRPM Count (K-12)' AS REAL) / 'Enrollment (K-12)") DESC LIMIT 1;
Without database content
Inferred SQL:
(K-12)' / "EErollmenn (K-12)' FROM frpm WHERE 'County Name' 'Alameda County'
'FRPM
ORDER BY (CAST(FRPM Count (K-12) AS REAL) / 'Eurollment (K-12)") DESC LIMIT 1:
Error reason:
Question AAlameda County',
'Alameda' (no "County")
With database content
Extracted database content values:
ftable: fcolmmn: [matched values]]]
Table 'frpm':
CCounty Name:: ['Alameda]]
Table 'satscores:
'ename': ['Alameda"].
Table 'schools':
"AdmFNamel': ['Rae"]
'AdmLNamell' [Free")
"City': ['Alameda",
"County:: [Alamedaa]]
"GSoffered"; [KK]
'GSserved'; [K-12).
"MailCity': ('Alameda)).
Inferred SQL:
SELECT 'FRPM Count (K-12)' / 'Enrollment (K-12)'
FROM frpm WHERE 'County Name" 'Alnmeda"
ORDER BY (CAST(FRPM Count (K-12)' AS REAL) 'Enrollment (K-12))) DESC LIMIT 1:
Terminology used in the question is different from that saved in the database. Consider the instance of the keyword "Alameda County' found in the natural language query: "What is the highest cligible free rate for K-12 students in the schools in Alameda County?" While the question uses *Alameda County", in the database, this information is stored as 'Alameda" without the word "County". If LLMs have access only to the original question without database content, the resulted SQL query is likely to contain the same keywords from the natural language, leading to an incorrect answer. Following Sec. 6.3.4, we extract database content that relevant to the question and the output is presented in Fig. 6 (c.g. the column "County Name containing 'Alameda').
Figure Case study 1:
8.3 Improving tuning with synthetic data
We present the impact synthetic data augmentation on the Text-to-SQL task. Table 12 presents the results of the inclusion of synthetic data into the original training data (Spider and BIRD), which leads the performance increase of 1.3% on the BIRD dev split. This improvement underscores the effectiveness of synthetic data in enhancing overall performance.
24

---

Published in Transactions on Machine Learning Research (11/2024)
Case Study 2
Question: Please list the zip code of all the charter schools in Fresno County Office of Education.
True SQL:
SELECT T2. FROM frpmn AS 'T INNER JOIN schools AS T2 ON '1. C1.CDeCode T2 CDSCude County Office Education AND T1:Charter
WHERE Tiitiiit Name!
(Y/N)'
Withont: database content
Inferred SQL:
SELECT T1.2h, Zip FROM schools AS T1 INNER JOIN frpm AS T2 ON T1. CDCCode = T2 CDCCode
WHERE '22. 122'Countty Name' 'Fresno County Office of Education' AND T2:Charter School (Y/N)'
Error Reason:
Multiple columns may contain keywords { ('Fresno County Office of Education");
LLMs don't know which columns
('District Name' 'County Name')
With database content
Extracted database content valuee: (table: fcolumn: |matched values]]]
Table 'frpm':
'County Name:: ['Presno',
"Distrrct Name" [FFrenno County Office Education")
'District Type': ('County Office of Education (COE)))
Table "satscores":
'ename': [Fresno'].
'dname:: ['Fresno County Office of Education').
Table 'schools':
'AdmL.Namel': [Cooe]]
"City:: [FFresno]]
"County': ['Fresno'],
'DOCType': ('County Office of Education (COE)), 'District': ['Fresno County Office of Education', CColusa County Office of Education").
'MailCiy': ['Fresmo']
Inferred SQL:
CI.CDCCddee T2 CDSCnde SELECT T2.Zip FROM frpm 'Fresno County Office of Education' AND T1:Charter School (Y/N)'
INNER schools
WHERE T1. Dlstrictt Name!
Figure 7: Case study 2: The association of keywords with a specific column is unclear without database content. Consider the keyword "Fresno County Office of Education" in the question 'Please list the zip code of all the charter schools in Fresno County Office of Education.". Without utilizing the database content, the LLMs might erroneously select the wrong column, such as "County name." However, with the inclusion of database content (assuming the column "District Name" contains the relevant keywords 'Fresno County Office of Education"), the LLMs learn 'District Name' is the columns use.
Table 12: Evaluations on BIRD Dev Split showing the impact of extra LLM-generated synthetic data. Execution accuracy
Tuning data
Spider + BIRD
55.15%
Spider + BIRD + LLM-generated synthetic data
56.45% (t 1.3%)
To encourage generation of a correct, distinct queries, prompt the LLM to generate up to three queries, followed by removing SQL that fails official evaluation or with a similarity score (Fq. 6.3.3) greater than 0.9. We choose to augment BIRD datasets, instead of Spider, because Spider contains simpler queries than BIRD, resulting in reduced flexibility in generating diverse queries from the original SQL. We use GPT-4 in synthetic generation, as selecting the LLMs for synthetic data differently from the LLMs used tuning potentially can bring now information.
We further examine the generated SQLs and their similarity score. Most of the generated SQL outputs do not deviate significantly from ground truth, indicated by the similarity score distribution (see statistics shown in Fig. 10 and Table 33 in Appendix). Among these generated SQL outputs, 81.4% are correct, validated by official evaluation. After removing similar SQLs (with similarity score > 0.9), 78.8% of the generated queries remains for training, which are considered as both diverse and precise, (Table 32 in Appendix). A few examples of the LLM generated synthetic SQL rewrites are provided in Table 13. can be observed that the LLM performs well at the given task by diversifying the ground truth queries and augmenting the dataset in useful way.
25

---

Published in Transactions on Machine Learning Research (11/2024)
Table 13: Examples of BIRD dataset queries generated.
Synthetle Data Examples
Example 1:
Question Ground-truth
Please uame any three restaurants that have unidentified region.
SELECT T2. 12.label FROM location AS T1 IMMER JOIN generalinfo AS T2 OK T1 rd_restaurant
12.1d restaurannt
JOIN goographic AS 12.city c3.city WHERE 13.region 'unknown'
Generated Query-1
SELECT gi-label FROM goneralinfo gi, geographic g WHERE gl.city g.city AND g. g-region 'unknown' LIMIT 3
Query-2
SELECT label FROM generalinto WHERE id, resattuuannt IN (SELECT id_ restaurannt FROM location WHERE city (SELECT city
geographic SELECT label FRON generalinfo WHERE city IN (SELECT city FROM geographic WHERE region 'unknoun')
region 'unknown') LIMIT
Query-3
LIMIT 3
Comment
The generated queries 1, 2. 3 have similarities 0.8, 0.7, 0.6 respectively, In this example, the LLM has also Identified redundant table usage from the ground truth and removed it from the generated query.
Example 2:
Question
Please give all the list prices of the product LL Fork. SELECT T2. ListPrice FRIM Product AS T1 INNER JOIN ProducttlistPriccalsttyy AS T2 OK ProductID T2. ProductID WHERE Ti.Name 'LL Fork'
Ground-truth
Generated Query-1
SELECT ProtuctListPricelistttrr// satt ListPrice FROM Product JOIN ProductlianPriicelssttrr Oll Product. ProducttI ProductlistPricctt story.tttr...... stary. ProductID WHERE Product. Nana 'LL Fork' SELECT plph. ListPrice FROM Product p, ProductlistPriceittttor Fork') LIMIT
Query-2
plph WHERE ProodductI plph. ProduccID
AND p.Name
Query-3
SELECT ListPrice FROM ProducttiatPPrioeHaatoo VHERE ProductID IN (SELECT ProductID FROM Product WHERE Name 'LL Fork')
Comment
The generated queries 2, have similarities 0,95, 0.85, 0.75 respectively. Query-1 1 merely alias change from the original query and hence queries with higher similarity (closer to 1) are not useful for augmenting the dataset.
8.4
Tuning with table and column selection
Table & column selection plays an important role for Text-to-SQL. scalability and accuracy, as covered in Sec. 6.3.5 - for database schema with high number of columns, it becomes vital to distill them down to a pertinent subset for Text-to-SQL especially when the schema size exceeds the LLMs' prompt length limit. This process is also crucial even for database schemas that can be represented within prompt limit, as it facilitates LLMs to focus on important information.
Regarding the selection of columns, high "recall" rate (the proportion of relevant columns correctly identified) is crucial, to ensure that no crucial columns missed. With "recall" meets a satisfactory level, we also aim to enhance the "precision" the proportion of the selected columns that is relevant, since it is beneficial to exclude numerous irrelevant columns.
In this section, we discuss the outcomes using retrieval-based and program-aided column selection techniques. We begin by evaluating the accuracy of column selection achieved by each method, then assess how these approaches influence the overall effectiveness of Text-to-SQL conversions. Lastly, we explore the advantages and limitations associated with both strategies.
Retrieval-based column selection: Table 14 shows the performance of retrieval-based column selection, with top 10 and 25 columns selected based on the retrieval ranking scores. The recall rates for selecting the top 10 and 25 columns are notably high at 81.52% and 92.93% respectively. Nonetheless, the precision is comparatively lower, suggesting that while retrieval-basee column selection has a good coverage of true columns, it also incorporates superfluous columns.
Table 15 presents the performance of end-to-end Text-to-SQL on the BIRD dev split, using both soft and hard column selection. The soft column selection method surpasses the baseline performance, which lacks column selection, demonstrating the effectiveness of soft column selection. On the other hand, hard column selection yields results worse than the baseline. This decrease in performance is likely due to hard column selection's incorrect exclusion of relevant columns from the database schema. For instance, with the top selection, approximately 20% of columns (1 - 81.52% are missing from the inputs, while soft column selection is more effective retaining the entire schema information while also enriching the selected columns with additional column description information.
26

---

Published in Transactions on Machine Learning Research (11/2024)
Table 14: Accuracy (%) of retrieval-based column selection when retrieving top 10 and 25
candidates on BIRD dev split.
Table selection
Column selection
Top 10
Top 25
Top 10
Top 25
Average counts
3.39
5.4
10
25
Recall
90.11
97.08
81.52
92.93
Precision
56.34
40.48
26.96
16.00
F1
38.95
54.05
38.95
26.39
Table 15: Execution accuracy of Text-to-SQL on BIRD dev split with retrieval-based column
selection incorporated into inputs. Bascline is from Table 11. Experiments are using the same setups as baseline.
Baseline Top 10
Top 25
Baseline
58.8%
+ Hard column selection
52.48%
56.39%
Soft column selection
58.93%
59.13%
Program-aided column selection: Table 16 presents the efficacy of the program-aided approach for column and lable selection. Overall, we observe impressive performance of this approach for selecting relevant. columns, with recall, precision, and Fl are all more than 90%. In comparison to the retrieval-base based column selection, the program-aided column selection has a substantially higher precision, indicating fewer irrelevant columns are selected. Additionally, program-aided method depends on preliminary SQL more accurate preliminary SQL prediction leads to more precise column and table choices, detailed in Table 34 in the Appendix.
Table 16: Accuracy (%) with program-aided column selection. The preliminary SQL used for the program-aided approach is the baseline SQL from Table 11 which has an accuracy of 58.8%.
Table selection Column selection
Average count.
1.88
5.24
Recall
94.64
90.66
Precision
96.14
92.60
F1
95.39
91.62
Table 17 illustrates the comprehensive impact of different ways to integrate chosen columns into model fine-tuning. We present. two approaches: hard column selection and soft column selection, to distinguish including only selected schema versus full schema into prompt. The way to identify the selected column is "program-aidedd" approach, as it gives optimal results. Hard column selection yields inferior outcomes compared to the baseline, likely because its recall rate is 90%~a seemingly high number that nonetheless results in the omission of 10% of columns. Conversely, soft column selection avoids this shortcoming and outperforms the baseline.
Table 17: Execution accuracy Text-to-SQL on BIRD dev split with program-aided column sclection incorporated into inputs. Bascline from Table 11. Experiments use the same sctups as baseline.
EX
Baseline
58.80%
+ Hard column selection
57.95%
+ Soft column selection
59.19%
27

---

Published in Transactions on Machine Learning Research (11/2024)
Furthermore, evaluate our column selection approaches against alternative baseline approaches, such using LLMs to directly identify relevant tables and columns through prompting (e.g. 'What are the relevant. tables or columns?") and other existing methods. Our methods demonstrably surpass such alternatives, as detailed in Table 35 within the Appendix.
What is the upper bound on the achievable performance incorporating the soft column selection approach? We conduct an ablation study using ground-truth column selection for soft column selection, which serves as upper bound of this approach.
Ablation studies and the upper bond:
Table 18 presents an ablation study on the soft column selection approach. We present two ways to identify the selected columns: 'retrieval-based" (3rd line) and 'program-aided' (4th line). Additionally, the table includes two further ablation studies: one on the impact of incorporating full column descriptions (2nd line) and another on using ground truth column selections (5th line). As Table 18 suggests, with ground truth column selection applicd as the oracle, the performance of Text-to-SQL reaches to 62.06%, which is 3% above the results using inferred column selection, indicating the potential of improving column selection. Additionally, since soft column selection incorporates the column description of subset of the columns, what would the performance be we incorporate the de- scriptions of all columns (2nd line)? We conduct an ablation study to include full column descriptions (See an input exam- ple 1.1...). For the prompts that ceed the input length, we cut them to fit the input length. The results reveal that introducing full column descriptions
Table 18: Ablation studies on soft column selection.
Method
EX
Baseline
58.80%
+ Full column descriptions
54.69%( 4.11%)
+ Soft column selection (retrieval-based)
59.13%
Soft column selection (program-aided)
59.19%
+ Ground truth column selection (oracle)
62.06%(1 3.26%)
actually decreases the performance compared with bascline by 4.11%. The reason might be due to full column descriptions yielding loo lengthy inputs that distract LLM from focusing on important information such as database schema and important information might get truncated.
Comparing retrieval-based and program-aided column selection: The program-aided approach outperforms retrieval-based approach in the accuracy of column selection, evidenced by its high Fl score and precision (Tablee 14 and Table 16). It achieves comparable recall with on average of 5 selected columns, unlike the retrieval-based approach that uses 25 to achieve 90% recall, demonstrating its efficiency. However, the overall Text-to-SQL performance of retrieval-basee based and program-aided column selection methods are comparable. Despite the program-aided approach showing significantly better performance in column selection, its end-to-end performance does not reflect a similar improvement. This seeming contradiction can be explained with the recall of the program-aided method being comparable to the top 25 retrieval approach (90 92) and the recall being more critical for Text-to-SQL performance. Additionally, there is mismatch between accuracy of column selection on train and dev splits. The program-aided approach, which trains an initial model to produce preliminary SQL outputs, results in higher accuracy in column selection of the train split compared to the dev split. This accuracy disparity could hinder the program- aided method from achieving its highest potential performance.
The retrieval-based method stands out for its computational efficiency and cost savings, as it avoids the need for querying expensive L.MSs. This approach also allows for easy adjustment. of recall by modifying the number of retrieved candidates ("topk")) Furthermore, in cases of extremely large datasets where the schema size makes generating even preliminary SQL infeasible due to prompt length constraints, the retrieval-basod approach is the only practical solution. While this scenario might not occur in academic benchmarks, it is a common challenge in real-world applications.
Comparing hard and soft column selection:
Compared with soft column selection, hard column selection leads to performance reduction. The reduction, 3% for retrieval-based approach (top 25) and 1% for program-aided approach, might be reasonable trado-off when we consider the amount of input information that has been reduced and the cost has been saved.
28

---

Published in Transactions on Machine Learning Research (11/2024)
From column level, BIRD dev split contains average of 76 columns (Table 37 in Appendix), and program-aided approach selects on average 5.24 columns, therefore, there are only 5.24/76 = 6.8% of the total columns used for program-aided column selection at the cost. of 1% of accu- racy loss. Similarly, for retrioval-base based approach, there are only 33% of total columns are used in inputs at the cost of 3% of accuracy. From token level, Table 19 demonstrates that the numbers of token after hard column selection are for only 26% or 42% compared to the full prompt for the two approaches. This suggests an equivalent proportion of cost savings, given that the cost associated with LLMs is directly proportional to the number of tokens. In some scenario, one may want to sacrifice some performance for the cost roduction.
Table 19: The number of input token saved due to hard column selection.
Tokens (counts)
Tokens remained
(percentage (%))
Bascline
1085.66
100%
Program-aided
286.31
26.37%
Retrieval- -basd
454.97
41.91%
Impact of the size of the database schema: Fig. 8 shows how the performance of the end-to-end Text-to-SQL process changes with different numbers of columns in the schema, using a soft column se- lection approach where the descriptions of selected columns are included in the prompt. The analysis reveals that as the number of columns increases, Text- to-SQL performance declines, indicating that column count is significant indicator for the difficulty of Text-to-SQL tasks. The program-aided colunn selec- tion method performs better than others when the column count is below 90, whereas the retricval-baat based approach excels when the columnn count exceeds 90, On average, the program-aided method selects about 5.24 columns per question, whereas the retrieval- based method extracts 25. When the total number of columns is below 90, including descriptions of 25 columns can overwhelm the prompt, leading to poorer performance. However, when the total number of columns is above 90, including 25 columns does not take a significant proportion of the schema, making the retrieval-based approach more effective.
Suxdine
Program-skdes
Potrioal
Execution Ascuraty
80.0
<45
45-90
>-90
Number of Columns Database
Figure 8: Text-to-SQL performance (y-axis) with re- spect to column numbers (x-axis). Both retrieval-basod and program-aided are based on soft-columu selection.
8.5 Improvements with test-time refinement via execution-based selection
Table 20 presents the effectiveness of the test-time refinement via secutiin-based selection approach, discussed in Section 6.3.6. Test-time refinement via execution- based selection approach integrates multiple predefined training paradigms introduced in previons sections, including mixing of training data, the integration of database content, the use synthetic data, and the implementation of both hard and soft column selection strategies. This combination method results in a performance improvement of 2.9% over individual training paradigms, such as the results in Table 11.
To assess the robustness of the test time execution selection to distribution shifts, we also apply the model, originally tailored for BIRD, to the Spider dataset. Despite Spider's 's distinct format differences from BIRD, such as lacking of column descriptions and hints or not employing column selection, Table 21 reveals that the method still cnhances performance on a diffcrent dataset, indicating its robustness to format variations.
Alternatively, we also explore another approach of combining different training paradigms into a single paradigm. This involves integrating various training components directly into the inputs of one single experiment. This method entails integrating various elements, such mixed training data, synthetic data, database content, and column selection, into the inputs for single experiment. Yet, outlined in Table 38 in the Appendix, this strategy does not yield additional accuracy gain, suggesting combining multiple information at once does not have superimposed positive effects. The reason may be LLMs cannot understand multiple information in the inputs simultancously effectively, highlighting the inherent difficuliees of incorporating various components of inputs.
29

---

Published in Transactions on Machine Learning Research (11/2024)
Table 20: Execution of text-time refinement via execution-based selection on BIRD dev split.
Decoding approach
Execution accuracy
Baseline
58.8%
+ Test-time execution-based selection
61.7% (1 2.9%)
Table 21: Evaluations of text-time refinement via execution-based selection on Spider dev split.
Train data Execution accuracy
Test-suite accuracy
Bascline
86.8% 87.3% (1 0.5%)
82.8%
Baseline
83.5% (1 0.7%)
8.6 Combining constituents SQL-PaLM
We consolidate our findings, as illustrated in Table 22. Our experimentation involves applying standard instruction tuning for an LLM which siguificantly outperforms in-context learning techniques. Utilizing combined training data BIRD and Spider for tuning led to improvement of 2%. Furthermore, the integration of synthetically generated data contributes to an additional 1% boost. Incorporating the database content results in a 3% increase, and further incorporating soft column selection into intputs adds another 1% Additionally, the implementation test time execution-basse selection, which combines the aforementioned training paradigms, provides an additional 3% improvement. Additionally, we present the results of execution- basod test-time selection on different difficulty levels in Table 23.
Table 22: Summary of each component's contribution in Text-to-SQL performance.
Run
Type
Train data
Method
Exccution accuracy
Tuning
BIRD
53.00%
Tuning
BIRD + Spider
55.15%
BIRD + Spider
Tuning
56.45%
+ Synthetic data
Tuning
BIRD Spider
+ Database content
58.80 88.80%
BIRD Spider
Tuning
+ Database content
58.35%
+ Synthetic data
+ Database content
Tuning
BIRD Spider
+ Soft column selection
59.13%
(Retrieval-base))
+ Database content
Tuning
BIRD + Spider
+ Soft column selection
59.19%
( (Program-aided)
Execution-based
Post-Tuning
61.70%
test-time selection
Table 23: Execution accuracy of execution-based test-time adaptation algorithm across various SQL complexity levels on the BIRD dev split.
Simple
Moderate
Challenging
Total
Count
933
459
142
1534
Execution-based test-time selection
68.92%
52.07%
47.89%
61.93%
30

---

Published in Transactions on Machine Learning Research (11/2024)
8.6.1 Overall Text-to-SQU performance comparison with other methods
Putting everything together, SQI.Pau.M demonstrates strong results on both the Spider and BIRD datasets. We present comparative analysis of methodology against leading methods from the BIRD (Table 24) and Spider (Table 25) leaderboards. SQL-PaLM has achieved notable results, with exccution accuracy of 87.3% Spider dev split and 61.7% BIRD dev split. These improvements diverse input components for tuning efficiently and adopting selective exccution approach. Additionally, BIRD provides Valid Elficiency Score (VES) to measure efficiency of valid SQL. Table 24 indicates SQL-PaLM significantly better performance for VES (10%+), indicates the SQL-PaLM can output efficient SQL.
made possible by effectively utilizing
In Table 24 and Table 25, we compare our approach with a variety of different methods for BIRD and SPIDER, respectively, since the top methods the different leaderboards vary. The fact that our single method competitive across different benchmarks and against diverse sets of methods further attests to the efficacy of our strategy.
Note that some leaderboard submissions are by anonymous contributors, lacking detailed documentation or code, and hindering a full comparison on dev splits (Leaderboard number is for test split, not dev split). such cases, we use to acknowledge their notable leaderboard performance, despite not being able consider them for direct evaluation.
Table 24: Evaluation on BIRD dev set with top-ranked methods.
Methods Datasots
EX
VES
Tuning
SFT CodeS-15B
58.47%
39.87%
Codex
25. 25.42%
43.41%
ChatGPT
37.22%
43.81 %
Few-shot prompting
GPT-4
46.35%
49.77%
DIN-SQL + GPT-4
50.72%
58.79%
DAIL-SQL GPTT4
54.56%
56.08%
MAC-SQL. GPT-4
59.39%
58. 76 %
Not available
Dubo-SQL
59.71%
66.01%
MCS-SQL. + GPT-4
64.82%
Peo- SQT. PaLM (Ours)
45.5%
45.11%
Pine-tuned SQL-PaLM (Ours)
61.7%
79.35%
Table 25: Evaluation on SPIDER dev set with top-ranked methods.
Methods/Datasett
EX
TS
T5-3B + PICARD
79.3%
69.4%
Fine-tuning
RASAT PICARD
80.5%
70.3%
RESDSQL-33 + NatSQL
84.1%
73.5%
CodeX davinci (0-shot)
67.0%
55.1%
CodeX davinci (few-shot)
71.0%
61.5%
ChatGPT
70.1%
60.1%
GPT-4 (Zero-shot)
72.9%
64.9%
GPT-4 (Few-shol)
76.8%
67.4%
Few-shot prompting
Self-Debug.
84.1%
DIN-SQL (w/ CodeX Davinci)
75.6%
69.9%
DIN-SQL (w/ GPT-4)
82.8%
74.2%
DAIL-SQII + GPT-4 + Self-Consistency
83.6%
72.8%
MiniSeck
MAC-SQL- + GPT4
86.75%
Few-ssho SQL-PaLM (Ours)
82.7%
77.3%
Fine-tuned SQL-PaLM (Ours)
87.3%
83.5%
31

---

Published in Transactions on Machine Learning Research (11/2024)
8.7 Error analyses
For few-shot prompting on Spider: In our detailed examination of the SQL queries produced by SQL- PaLM via few-shot prompting on the SPIDER dataset, we undertook an in-depth manual evaluation to assess the quality of the generated SQL. Our analysis indicates that the queries often exhibit creativity often deviating from the ground-truth truth by employing varied SQL clause. These queries are mostly free from syntactical errors and consistently display complex reasoning, such as the capability to join multiple tables. To provide more tangible insights, Table 26 shows two complex yet accurately generated queries. Further discussions and examples provided in Section A.9.1 in the Appendix.
Table 26: SQL examples generated by few-shot prompting of SQI-PaLM.
Question: What are the number of concerts that occurred in the stadium with the largest capacity ?
SQL:
SELECT count(*) FROM concert TI JOIN stadium AS T1. at.stadium_tt www.siaddnntt... WHRE T2. capacity ( SELECT nax capacity) FROM stadiun AS T3 >
Question: What are the id sand names of all countries that either have more than 3 car makers or produce fiat model
SELECT T1. countryd. Il. counttryye FROM countries AS T1 JOIN car_nakers AS T2 OX T1. countryy T2. country GROUP BY Ti. countrydd HAVING count(+) 3 UNION SELECT T1. countryid countryana FROM countries AB car makera 72 countryd 12.country JOIN model_list AS id maker WHERE mo.el "fiat"
To better understand the common error modes of the fine-tuned LLM, we randomly select 100 queries from different databases in the BirdSQL, dev split, where the execution results from generated queries don't match those of the ground-truth results. Table 27 shows high-level breakdown of accuracy on BIRD, categorized by query difficulty. As one would expect, the accuracy on harder examples is lower the accuracy on easier examples (68.92%) is significantly higher than that moderate examples (52.07%) which significantly higher than the challenging examples (47.89%). Additionally, we manually
For tuning on BIRD:
Table 27: SQL-PaLM error analysis statistics on BIRD dev split
Category
Number of Queries Percentage
Total
1533
Correct
950
61.97%
Incorrect
584
38.10%
Invalid
14
0.91%
Per difficulty
Number of queries
EX
Total Simple
933
60.86 60.86%
Total Moderate
459
29.94%
Total Challenging
142
9.26%
Correct Simple
290
68.92%
Correct Moderate
220
52.07%
Correct Challenging
74
47.89%
inspect these queries and categorize them according to the types of errors they produce. The error categories are shown in Figure 9 and will be described below. The representation of each category in the pie plot is proportional to its respective percentage.
We subdivide the errors into several categories: Schema Linking: Encompasses queries where the model was not able to select the relevant tables for the queries (e.g. failing to join tables). Misun- derstanding Database Content: The model fails to accurately interpret the data within the tables
321t, an evidence of memorization.
32

---

Published in Transactions on Machine Learning Research (11/2024)
(e.g. assumes incorrect date format for specific column). Misunderstanding Knowledge Ev-
idence: The model wasn't able to interpret the human-annolated evidence ignores it altogether.
Reasoning: The model fails to comprehend the question and the gencrated query docsn't contain the necessary reason- ing steps to generate the correct queries. Ryntax-Related Errors: The model produces SQL that are not runnable due to some syntactical mistake (e.g., missing backticks refer to column which has spaces). Finally, in red, we label additional error category, denoted Dataset related errors, which encompasses different errors due to questions, schema, evidence, inconsistencies belween the ground-truth SQL and the question, not because the outputted SQL. In the 100 queries that we analyzo, 31 of them present this error category that, if extrapolated to the full dataset, would upper bound the performance of BIRD dev set to 70%. We describe more of our finding data-quality in the Appendix A.9.2 and present more error examples of each category in Table 42 in Appendix.
Schea
Reasoning
Error Categories
Dangett relised enrors
Breienoe
Builon
Syran
Whorg
Figure Error categories for SQL-PaLM fine- tuned LLM on Bird dev set.
9 Conclusions
This paper presents the SQL-PaLM framework, our holistic approach to advancing Text-to-SQL capabilities. We provide insightful discussion for understanding koy factors in dociding Text-to-SQL, performance. We start with a comprehensive examination of few-shot prompting lo enhance Text-to-SQL, performance with LLMs. Then we present best practices for instruction fine-tuning, examining how performance can be improved through expanded data coverage and diversity, synthetic data augmentation and integrating query-specific database content. We introduce test-time refinement approach that leverages query execution feedback to bolster SQT. query accuracy. Additionally, we address some of the real-world challenges of navigating complex databases with many tables and columns, presenting effective methods for the precise selection of pertinent database components to improve Text-to-SQL performance. Our integrated approach demonstrates substantial improvements in Text-to-SQL SQL performance, demonstrated on two important public benchmarks.
10 Acknowledgement
We thank Sayna Ebrahimi and Slav Petrov for reviewing this paper.
References
Josh Achiam, Steven Adler, Sandhini Agarwal, Lama Ahmad, Tlge Akkaya, Florencia Leoni Aleman, Diogo Almeida, Janko Altenschmidt, Sam Altman, Shyamal Anadkat, et al. Gpt-4 technical report. arXiv preprint arXiv:2303.08771 2023.
Shengnan An, Bo Zhou, Zeqi Lin, Qiang Fu, Bei Chen, Nanning Zheng, Weizhu Chen, and Jian Guang Lou. Skill-based few-shot selection for in-context learning. arXiv preprint ar arXim:2305.142 14210, 2023.
Ion Androutsopoulos, Graeme D Ritchie, and Peter Thanisch. Natural language interfaces to databases -n introduction. Natural language engincering, 1(1):29 81, 1995.
Rohan Anil, Andrew M Dai, Orhan Firat, Melvin Johnson, Dmitry Lepikhin, Alexandre Passos, Siamak Shakeri, Emanuel Taropa, Paige Bailey, Zhifeng Chen, et al. Palm technical report. arXiv preprint arXiv:2305.10403, 2023.
Macicj Besta, Nils Blach, Ales Kubicck, Robert Gerstenberger, Lukas Gianinazzi, Joanna Gajda, Tomasz Lehmann, Michal Podstawski, Hubert Niewiadomski, Piotr Nyezyk, al. Graph of thoughts: Solving elaborate problems with large language models. arXio preprint ar Xrx22230 XrX.:23088.0667,, 2023.
33

---

Published in Transactions on Machine Learning Research (11/2024)
Ben Bogin, Matt Gardner, and Jonathan Berant. Global reasoning over database structures for text-to-sql parsing. XrXiv preprint Xie: 1 1908. 11214, 2019.
Ruichu Cai, Jinjie Yuan, Boyan Xu, and Zhifeng Hao. Sadga: Structure-awar dual graph aggregation network for text-to-sql. Advances in Neural Information Processing Systems, 34:7664-7676, 2021.
Wenhu Chen, Xueguang Ma, Xinyi Wang, and William W Cohen. Program of thoughts prompting: Dis- entangling computation from reasoning for numerical reasoning tasks, orXiv preprint arXio:2211.1258. 2022.
Xinyun Chen, Maxwell Lin, Nathanael Schärli, and Denny Zhou. Teaching large language models to self-debug. arviv preprint ar orXiv:2304.05122 2023.
Aakanksha Chowdhery, Sharan Narang, Jacob Devlin, Maarten Bosma, Gaurav Mishra, Adam Roberts, Paul Barham, Hyung Won Chung, Charles Sutton, Sebastian Gehrmann, et al. Palm: Scaling language modeling with pathways. arXie preprint arXiv:2204. 02311, 2022.
Hyung Won Chung, Le Hou, Shayne Longpre, Barret Zoph, Yi Tay, William Fedus, Eric Li, Xuezhi Wang, Mostafa Dehghani, Siddhartha Brahma, et al. Scaling mnstrcttion-finetunee language models. arXiv preprint arXtv:2810.11422 2022.
Thomas H Cormen, Charles E. Leiserson, Ronald I, Rivest, and Clifford Stein. Introduction to algorithms. MIT press, 2022.
Xiang Deng, Ahmed Hassan Awadallah, Christopher Mock, Olcksandr Polozov, Huan Sun, and Matthew Richardson. Strueture-grounded pretraining for lext-to-sql. arXiv preprinl urXiv:2010.122 12773, 2020.
Yujian Gan, Xinyun Chen, Qiuping Huang, Matthew Purver, John R Woodward, Jinxia Xie, and Peng- sheng Huang. Towards robustness of text-to-sql models against synonym substitution. ar preprint arXiv:2106. 200.000000006,,, 01065, 2021a.
Yujian Gan, Xinyun Chen, and Matthew Purver. Exploring underexplored limitations of cross-domain text-to-sql generalization. arXie preprint or arXiv:2109.05157, 2021b.
Dawei Gao, Haibin Wang, Yaliang Li, Xiuyu Sun, Yichen Qian, Bolin Ding, and Jingren Zhou. text-to-sql empowered by large language models: A benchmark evaluation. arXie preprint XXi::2008.1330 2023a.
Luyu Gao, Aman Madaan, Shuyan Zhou, Uri Alon, Pengfei Liu, Yiming Yang, Jamie Callan, and Graham Neubig. Pal: Program-aided language models. In International Conference on Machine Learning, pp. 10764-10799, PMLR, 2023b.
Yu Gu, Xiang Deng, and Yu Su. Don't generate, discriminate: A proposal for grounding language models to real-world environments. arXiv preprint arXiv:2212.09733, 2022.
Jiaqi Guo, Zechong Zhan, Yan Gao, Yan Xiao, Jian-Guang Lou, Ting Liu, and Dongmei Zhang. Towards com- plex text-to-sql in cross-domain database with intermediate representation. arXio preprind vrXi::195.0000 2019.
Vagelis Hristidis, Yannis Papakonstantinon, and Luis Gravano. Efficient ir-style keyword search over relational databases. In Proceedings 2003 VLDB Conference, pp. 850 861. Elsevier, 2003.
Edward J Hu, Yelong Shen, Phillip Wallis, Zeyuan Allen-Zhu, Yuanzhi Li, Shean Wang. Lu Wang, and Weizhu Chen. Lora: Low-rank adaptation of large language models. arXio preprinl arXiv: 06. 06.09685, 2021.
Binyuan Hui, Ruiying Geng, Lihan Wang, Bowen Qin, Bowen Li, Jian Sun, and Yongbin Li. S Injecting syntax to presstin-schemme interaction graph encoder for text-to-sql parsers. arXiv preprint ar arXiv22008.0055 2022.
34

---

Published in Transactions on Machine Learning Research (11/2024)
Jared Kaplan, Sam McCandlish, Tom Henighan, Tom B Brown, Benjamin Chess, Rewon Child, Scott Gray, Alec Radford, Jeffrey Wu, and Dario Amodei. Scaling laws for neural language models. arviv preprinl wwXiv:2001.08360 2020.
Wenqiang Lei, Weixin Wang, Zhixin Ma, Tian Gan, Wei Lu, Min- Yen Kan, and Tat-Seng Chua. Re- examining the role of schema linking in text-to-SQI. In Bonnic Webber, Trevor Cohn, Yulan Hc, and Yang Liu (eds.), Proceedings of the 2020 Conference on Empirical Methods in Natural Language Processing (FMNLP), pp. 6943 6954, Online, November 2020a. Association for Computat ional Linguisties. doi: 10.18653/ve 1122000.emmlppp main.564. URL https: : http//aaaaantoooy...ommmm org/2020 commp-mmain 564.
Wenqiang Lei, Weixin Wang, Zhixin Ma, Tian Gan, Wei Lu, Min- Yen Kan, and Tat-Seng Chua, Re-examining the rolc of schoma linking in text-to-sql. In Proceedings of the 2020 Conference on Empirical Methods in Natural Language Processing (EMNLP), pp. 6943 6954, 2020b.
Fei Li and Hosagrahar V Jagadish. Constructing an interactive natural language interface for relational databases. Proceedings of the VLDB Endowment, 8(1):73 84, 2014.
Haoyang Li, Jing Zhang, Cuiping Li, and Hong Chen. Decoupling the skeleton parsing and schema linking for text-lo-sql. arXiv preprinl or arXi::2302.0500 2023a.
Haoyang Li, Jing Zhang, Cuiping Li, and Hong Chen. Resdsql: Decoupling schema linking and skeleton parsing for text-to-sql. In Proceedings of the AAAI Conference on Arlificial Intelligence, volume 37, pp- 2023b.
Haoyang Li, Jing Zhang, Hanbing Liu, Ju Fan, Xiaokang Zhang, Jun Zhu, Renjie Wei, Hongyan Pan, Cuiping Li, and Hong Chen. Codes: Towards building open-source language models for text-to-sql. arXie preprint arXiv:2402.1000 16347, 2024.
Jinyang Li, Binyuan Hui, Ge Qu, Binhua Li, Jiaxi Yang, Bowen Li, Bailin Wang, Bowen Qin, Rongyu Cao, Ruiying Geng, Nan Huo, Chenhao Ma, Kevin C. C. Chang, Fei Huang, Reynold Cheng, and Yongbin Li. Can Ilm already serve as database interface? big bench for large-scale database grounded text-to-sqls, 2023c.
Jinyang Li, Binyuan Hui, Ge Qu, Binhua Li, Jiaxi Yang, Bowen Li, Bailin Wang, Bowen Qin, Rongyu Cao, Ruiying Geng, et al. Can Ilm already serve as a database interface? a big bench for large-scale database grounded text-to-sqls. arXiv preprint arXiv:2305.0811 2023d.
Raymond Li, Loubna Ben Allal, Yangtian Zi, Niklas Mucnnighoff, Denis Kocetkov, Chenghao Mou, Mare Marone, Christopher Akiki, Jia Li, Jenny Chim, et al. Starcoder: may the source be with you! arXiv preprinl Viv:2305.00110 2023e.
Xi Victoria Lin, Richard Socher, and Caiming Xiong. Bridging textual and tabular data for cross-domain text-to-sql semantic parsing. arXiv preprint arXi::2012.1900 2020.
Aiwei Liu, Xuming Hu, Lijie Wen, and Philip S Yu. A comprehensive evaluation of chatgpt's zero-shot text-to-sql capability. XrXiv preprint Nrv 13547, 2023a.
Nelson F. Liu, Kevin Lin, John Hewitt, Ashwin Paranjape, Michele Bevilacqua, Fabio Petroni, and Percy Liang. Lost in the middle: How language models use long contexts, 2023b.
Ziyang Luo, Can Xu, Pu Zhao, Qingfeng Sun, Xiubo Geng, Wenxiang Hu, Chongyang Tao, Jing Ma, Qingwei Lin, and Daxin Jiang. Wizardooder: Empowering code large language models with evol-instruct. arXiv preprint ar Xiv:23306. 08568,,8888818 2023.
Mayank Mishra, Prince Kumar, Riyaz Bhat, Rudra Murthy V, Danish Contractor, and Srikanth Tamilselvam. Prompting with pseudo-code instructions. arXiv preprint arXiv:2305.1 1 1790, 2023.
Niklas Mucnnighoff, Qian Liu, Armel Zebaze, Qinkai Zheng, Binyuan Hui, Terry Yue Zhuo, Swayam Singh, Xiangru Tang, Leandro Von Werra, and Shayne Longpre. Octopack: Instruction tuning code large language models. Xiv preprint or arXi::2308.0714 2023.
35

---

Published in Transactions on Machine Learning Research (11/2024)
Linyong Nan, Yilum Zhao, Weijin Zou, Narutatsu Ri, Jacsung Tae, Ellen Zhang, Arman Cohan, and Dragomir Radev. Enhancing text-to-SQL, capabilities of large language models: A study on prompt design strategies. In The 2023 Conference on Empirical Methods in Natural Language Processing, 2023.
Ansong Ni, Srini Tyer, Dragomir Radev, Veselin Stoyanov, Wen-tau Yih, Sida Wang, and Xi Victoria Lin. Lever: Learning to verify language-to-code generation with execution. In International Conference on Machine Learning, pp. 26106 261288...... PMLR, 2023.
Rubén Pérez-Mercado, Antonio Balderas, Andrés Muñoz, Juan Francisco Cabrera, Manuel Palomo-Duarle and Juan Manuel Dodero. Chatbotsql: Conversational agent to support relational database query language learning. SoftwareX, 22:101346, 2023.
Mohammadreza Pourreza and Davood Rafiei. Din-sql: Decomposed in-context learning of text-to-sql with self-correction. arXiv preprint arXiv:2304.1100 11015, 2023.
Jiexing Qi, Jingyao Tang, Ziwei He, Xiangpeng Wan, Chenghu Zhou, Xinbing Wang, Quanshi Zhang, and Zhouhan Lin. Rasat: Integrating relational structures into pretrained seq2seq model for text-to-sql. arXiv preprint Xrx::22055.0698. 2022.
Colin Raffel, Noam Shazeer, Adam Roberts, Katherine Lee, Sharan Narang, Michael Matena, Yanqi Zhou, Wei Li, and Peter J Liu. Exploring the limits of transfer learning with a unified text-to-text transformer. The Journal of Machine Learning Research, 21(1):5485-5555 5551, 2020.
Nilarshan Rajkumar, Raymond Li, and Damitry Bahdanau. Evaluating the text-lo-sql capabilities of large language models. arXiv preprint Xxxu:2204.00498 2022.
Torsten Scholak, Nathan Schucher, and Dzmitry Bahdanau. Picard: Parsing incrementally for constrained auto-regressive decoding from language models. arXiv preprint XXiu:2109.05099 2021.
Peter Shaw, Ming-Wer Wei Chang, Pamupong Pasupat, and Kristina Toutanova. Compositional gencralization and natural language variation: Can a semantic parsing approach handle both? arXiv preprind ar Xri::2010. 12725, 2020.
Ruoxi Sun, Sercan Ô Arik, Rajarishi Sinha, Hootan Nakhost, Hanjun Dai, Pengcheng Yin, and Tomas Pfister. Sqlprompt: In-context text-to-sql with minimal labeled data, arXiv preprint Xr (xXx::2311.0288,, 2023.
Tya Sutskever, Oriol Vinyals, and Quoc V Le. Sequence to sequence learning with neural networks. Advances in neural information processing systems, 27, 2014.
Chang-You You Tai, Ziru Chen, Tianshu Zhang, Xiang Deng, and Huan Sun. Exploring chain-of-thought style prompting for text-to-sql. arXiv preprint arXiv:2305. 14215, 2023.
Yi Tay, Mostafa Dehghani, Vinh Q Tran, Xavier Garcia, Jason Wei, Xuezhi Wang, Hyung Won Chung, Dara Bahri, Tal Schuster, Steven Zheng, et al. U12: Unifying language learning paradigms. In The Fleventh International Conference on Learning Representations, 2022.
Bailin Wang, Richard Shin, Xiaodong Liu, Oleksandr Polozov, and Matthew Richardson. Rat-sql: Relation- aware schema encoding and linking for text-to-sql parsers. arXiv preprint dxXiv:1911.04942, 2019.
Bailin Wang, Wenpeng Yin, Xi Victoria Lin, and Caiming Xiong. Learning to synthesize data for somantic parsing. arXiv preprint ar Xix::101.0005555
2021.
Bing Wang, Changyu Ren, Jian Yang, Xinnian Liang, Jiaqi Bai, Linzheng Chai, Zhao Yan, Qian- Wen Zhang, Di Yin, Xing Sun, et al. Mac-sql: A multi-agent collaborative framework for text-to-sql. or arXiv preprint arXiv:2312.11224 2024.
Chenglong Wang, Alvin Cheung, and Rastislav Bodik. Synthesizing highly expressive sql queries from input-output examples. In Proceedings of the 38th ACM SIGPLAN Conference on Programming Language Design and Implementation, pp. 452 466,, 2017.
36

---

Published in Transactions on Machine Learning Research (11/2024)
Tianshu Wang, Hongyu Lin, Xianpei Han, Le Sun, Xiaoyang Chen, Hao Wang, and Zhenyu Zeng. Dbcopilot:
Scaling natural language querying to massive databases. Preprint avoilable online, 2023.
Xuezhi Wang, Jason Wei, Dale Schuurmans, Quoe Le, Ed Chi, and Denny Zhou. Self-consistency improves chain of thought reasoning in language models. arXiv preprint arXiv:2203.11171 2022.
Jason Wei, Maarten Bosma, Vincent Y Zhao, Kelvin Guu, Adams Wei Yu, Brian Lester, Nan Du, Andrew M Dai, and Quoe V Le. Fineluned language models are zero-shot learners. arXiv preprinl or Xixx:21090016555 2021.
Jason Wei, Yi Tay, Rishi Bommasani, Colin Raffel, Barret Zoph, Sebastian Borgeaud, Dani Yogatama, Maarten Bosma, Demny Zhou, Donald Metzler, et al. Emergent abilities of large language models. arXiv preprint arXi2:2006. 0 0682,, 2022a.
Jason Wei, Xuezhi Wang, Dale Schurrmans, Maarten Bosma, Ed Chi, Quoc Le, and Denny Zhou. Chain of thought prompting elicits reasoning in large language models. arXiv preprint 0rXiv:2201.11900 2022b.
Kun Wu, Lijie Wang, Zhenghua Li, Ao Zhang, Xinyan Xiao, Hua Wu, Min Zhang, and Haifeng Wang. Data augmentation with hicrarchical sql-to-questin generation for cross-domain text-to-sql parsing. arXiv preprinl Xiv:21003. 02227, 2021.
Tianbao Xie, Chen Henry Wu, Peng Shi, Ruiqi Zhong, Torsten Scholak, Michihiro Yasunaga, Chien-Shens Wu, Ming Zhong, Pengcheng Yin, Sida I Wang, et al. Unifiedskg: Unifying and multi-tasking structured knowledge grounding with text-to-text language models. orXiv preprint (xxv:22201009666 2022.
Tianbao Xie, Fan Zhou, Zhoujun Cheng, Peng Shi, Luoxuan Weng, Yitao Liu, Toh Jing Hua, Junning Zhao, Qian Liu, Che Liu, et al. Openagents: An open platform for language agents in the wild. arXiv preprinl. 0rXiv:2310.10633 2023.
Shunyu Yao, Dian Yu, Jeffrey Zhao, Izhak Shafran, Thomas L Griffiths, Yuan Cao, and Karthik Narasimhan. Tree of thoughts: Deliberate problem solving with large language models. arXiv preprint xrXw:2235510601. 2023.
Pengcheng Yin, Graham Neubig, Wen-tau Yih, and Sebastian Riedel. Tabert: Pretraining for joint under- standing of textual and tabular data. arXio preprint or arx::22005.08: 2020.
Tao Yu, Rui Zhang, Kai Yang, Michihiro Yasunaga, Dongxu Wang, Zifan Li, James Ma, Irene Li, Qingning Yao, Shanclle Roman, et al. Spider: A large-scale human-labeled dataset for complex and cross-domain semantic parsing and text-to-sql task. arxie preprint 1rxii:1000 888.08887, 2018.
Tao Yu, Rui Zhang, He Yang Er, Suyi Li, Eric Xue, Bo Pang, Xi Victoria Lin, Yi Chern Tan, Tianze Shi, Zihan Li, et al. Cosql: A conversational text-to-sql challenge towards cross-domain natural language interfaces to databases. Xiv preprint ar Xiv: 053378 2019.
Tao Yu, Chien-Sheng WU, Xi Victoria Lin, Bailin Wang, Yi Chern Tan, Xinyi Yang, Dragomir Radev, Richard Socher, and Caiming Xiong. Grappa: Grammar-augmentel pre-training for table semantic parsing. arxiv preprint Xiv: 13845, 2020.
Kun Zhang, Xiexiong Lin, Yuanzhuo Wang, Xin Zhang, Fei Sun, Cen Jianhe, Hexiang Tan, Xuhui Jiang, and Huawei Shen. Refsql: A retrieval-angmennttiin framework for text-to-sq) generation. In Findings of the Association for Computational Linguistics: EMNLP 2023, 2023.
Yiyun Zhao, Jiarong Jiang, Yiqun Hu, Wuwei Lan, Henry Zhu, Anuj Chauhan, Alexander Li, Lin Pan, Jun Wang, Chung-Wa Wei Hang, et al. Importance of synthesizing high-quality data for text-to-sql parsing. arXio preprint 221iv:221..00 08785, 2022.
Ruiqi Zhong, Tao Yu, and Dan Klein. Semantic evaluation for text-to-sql with distilled test suites, arXiv preprinl Xiv:2000.. 02840, 2020.
37

---

Published in Transactions on Machine Learning Research (11/2024)
Victor Zhong, Caiming Xiong, and Richard Socher. Seq2sql: Generating structured queries from natural language using reinforcement learning. arXie preprint arXiv: 109.00000, 2017.
Denny Zhou, Nathanael Schärli, Le Hou, Jason Wei, Nathan Scales, Xuezhi Wang, Dale Schuurmans, Claire Cui, Olivier Bousquet, Quoc Le, et al. Least-to-most prompting enables complex reasoning in large language models. Xiv preprint or arXiv:2205. 10625, 2022.
38

---

Published in Transactions on Machine Learning Research (11/2024)
A Appendix
A.1 Prompt design and number of demonstrations for Spider
In Table 28, we analyze the performance of Few-shoo SQI-PPaLM method on different number demonstrations (zero- vs. few- shot) and queries with different prompt de- signs. Overall, few-shot prompting outper- forms zero-shot counterpart. We also ex- plore the effect of different prompt design approaches performance. For the LLM being queried (e.g. PaLM2), concise prompt is observed to be better. "Verbose" prompts are based on using natural language to describe database schema, which is closer to the way LLMs were trained, whereas "Concise" prompts use the symbols to describe the database schema, which has advantages of clearly presenting table structure. Examples of concise prompt and verbose prompt provided in Appendix A.10.1 and A.10.2.
Prompt design
Adaptation setting
EX
TS
Concise
0-shot
81.2
76.0
Concise
4-shot
82.7
77.3
Verbose
0-shot
78.5
70.9
Verbose
4-shot
81.3
73.7
Table 28: Test-suite accuracy for different prompt design ap- proaches zero- and few-shot set-up on Spider Dev.
A.2 Prompt design for BIRD via few-shot prompting
In Table 29, investigate various prompt design BIRD datasets. Unlike Spider datasets (Table 28) where concise prompt works better, for BIRD datasets verbose prompt works superior.
Table 29: Execution accuracy of Text-to-SQL with different prompt designs across different SQL difficulty levels on BIRD datasets
Simple
Moderate Challenging
Total
Count
933
459
142
1534
Concisc
49.95%
25.05%
19.01%
39.63%
Verbose 53.27%
29.85%
18.31%
43.02%
A.3 Descriptions of Robust Spider Datasets: Spider-SYN,
Spider-DK
Table 30: Information on different variants of Spider datasets with the purpose of evaluating robust ness.
Counts Moditiction Category
Source
Vindify Natural
Question?
Modlify Dotabase
Schema?
New
Tatabaen Schema?
Add
Examples
Spoder
Datalaan Schemn: concert singer
stuliim ID, Lotatiio,, Nams, Cagurity Highed,, Louodd, Average)
Masually modlfyynng unlund langrage questioes symonmm iittiittionss
singeriiingor ID, Namm, Counley, Song. Namo, Sing rulnse Age, le mule) conertfennerrt ID, conorrt Name, Theme, Stadinm, (D) Yoar)
Spicler- SYN
1051
Spider
No.
singer penmers[pomme Singer
Q: How many singers do lave?
Spider-YY
How vocaliste have?
Medily muliuni langunge questions remore explicitly muilimiin cxlumn
Sucder
Dataiuse Schoms: concert singer
Spidle- Realattie 208
Sulout of Spidks Dov
Yes
No
No
Q: How many cunsati los ane Uhose yeur 2014 2016
How
there 2014
Datahaan Schemn: concert singer
Modity database sthemo
Modity datehoss commmn "Age" Into "Batladas's
Spider DK
535
incrporate the domalla kaorloige
Subset of Spider Dox
Yes
Yes
Yes
Replace the valoos from 152" to 1993 02 oo 0000000
by singers above lage question
39

---

Published in Transactions on Machine Learning Research (11/2024)
A.4 Tuning performance with different foundation models
Table 31 shows the results of luming open-source models LLaMA7B, LLaMA13B, and LLaMA33B Spider using the best imput representation as reported in Gao et al. (2023a)
Table 31: Evaluations on Spider dev split using different foundation models.
Foundation model
TS
LLaMA-7B
66.7%
LLaMA-2-CHHIT-7
69.6%
LLaMA-13B
68.6%
LLAMA-2-CHAT-13B
65.1%
LLaMA-33B
69.1%
A.5 Synthetic data
A.5.1 Synthetic data prompt design
Synthetic Data Prompt Design
You will be provided with list of tables from SQL database followed by natural language query related to the database and the original SQL query answering the question. Your job is to understand the natural language queries and generate up to 3 different SQL queries using diverse commands from the original query while answering the question correctly. You need to make sure to use the same columns from the original query for the generated query. You will also generate a similarity score between the original and the generated query based on how closer they are syntactically.
Database tables schema are as follows:
CREATE TABLE customers customer_it int,
unique customer
name (archar(100)
name of the customer
@eail_addrees varchar (255), email address of the customer )
CREATE TABLE order order_ic int, customer_it int,
unique order id.
unique customer id. order_amount amount decimal (10, 2), amount spent by the customer the order );
Question: Find the email of the top spending customer?
Original SQL query:
SELECT customers. name
FROM customers
JOIN order ON customers. custooer GROUP BY customers. customer_it _id, customers. first_name name ORDER BY SUM(order. orderr owdeorrrddooee amount) DESC
_idddddd- id = order. customer_ittec... _i
LIMIT 1;
Output the generated queries and the similarity scores in json list as follows:
33mhee numbers taken from Gao al. (2023a);
40

---

Published in Transactions on Machine Learning Research (11/2024)
[
("sql":
// generated query-1,
"similarity": // similarity score (0. 00.0-1.0) for query-1 b, (...}
A.5.2
Synthetic data similarity score distribution
5000
4000
Number of samples
3000
2000
1000
0.5
0.7
0.8
0.9
Similarity Score
Figure 10: Histogram plot of synthetic data similarity scores
32 displays the statistics of the generated queries with correctness and similarity filters applicd. It is important note that lower similarity score indicates higher diversity.
Table 32: Synthetic data generation statistics for BIRD train split
Correct SQL Correct SQL Similarity <= 0.9
Samples with 1+ generated queries
81.4%
78.8%
The mean, median, standard deviation of the similarity scores of all generated queries is reported in Table 33 and the histogram is plotted in Figure 10 Appendix.
Table 33: Statistics of synthetic data similarity scores
Min
Max
Mean
Median
STD
0.5
1.0
0.85
0.85
0.07
A.6
Column Selection
A.6.1
Example of retrieval-based column selection
Template:
Column name [column, _ame] of type [column type] from the table [table name]. Description: [col- description]. Value examples: [common distinct values).
Example:
41

---

Published in Transactions on Machine Learning Research (11/2024)
Column name 'size' of type 'STRING' from the table 'package'. Description: 'package size dimensions'. Value examples: 'small", 'medium', 'long'.
A.6.2 Program-aides aided column selection ablation studies
The accuracy of program-aided column selection is directly related to the accuracy of the preliminary SQL. The higher the accuracy of these preliminary SQL queries, the better the column selections based on them will be.
Table 34: Higher accuracy in preliminary SQL leads to better column selection
Accuracy (%)
Table (%)
Column (%)
Preliminary SQL
Recall
Precision
F1
Recall
Precision
F1
43
92.96
91.15
92.04
84.75
86.62
85.67
50.2
94.10
94.41
94.25
87.97
90.87
89.40
93.76
95.12
94.44
89.62
91.69
90.64
58.8
94.64
96.14
95.39
90.66
92.60
91.62
A.6.3 Compare with other column selection methods
We presented other column selection methods: LLM base: prompting LLMs to request table and column selection (Prompt is in Sec. A.6.4). LLM CoT: Following Pourreza Rafici (2023), we add few-shot examples and use change-of-thought demonstrations to help the prompt (Prompt
Sec. A.6.5). The model is PaLM-2 tixt- Automatic Annotation (Lei al., 2020b) proposes pattern matching approach. The last two lines are taken from Table 16 and Table 14 (Top 10), rounded by two decimals. The results in Table 35 indicates that program-aided algorithm outperform the other methods with clear margin.
Table 35: Comparison of Table and Column selection
Table selection
Column selection
Methods
Recall
Precision
F1
Recall
Precision
Fl
LLM baseline
0.71
0.88
0.75
0.24
0.83
0.35
LLM Few-shot
0.85
0.82
0.82
0.81
0.64
0.70
Automatic Annotation
0.87
0.74
0.80
0.68
0.90
0.78
Retrieval-based (Ours)
0.90
0.56
0.39
0.82
0.27
0.39
Program-aided (Ours)
0.95
0.96
0.95
0.91
0.93
0.92
A.6.4 LLM base prompt design of column selection
We start with simple baseline which asks the model to select a schema in two steps. First, select tables and then columns. Table selection
Select tables from my database named [database_nd] [acaabaase__mee]_ to answer the given query. Tables:
CREATE TABLE (table_name, ()
CREATE TABLE [table_name 2] ()
Query: 2 'qquery}'
Only generate a list of comma separated table names without any spaces.
Column selection
Given a table definition and a natural language query, I interested in selecting columns related to the query:
42

---

Published in Transactions on Machine Learning Research (11/2024)
CREATE TABLE [table_narc name] (
[column name] [oolumn_type]]
);
Table Values:
[3 row examples with header]
The natural language query: {query)
Select all related column names, including ids, from the table.
Only generate list of comma separated column names values without any spaces.
A.6.5 LLM Few-shot with CoT for column selection
Following Pourreza & Rafici (2023), we add few-shot examples and use olange-of-thoughh demonstrations help the prompt.
You are an agent designed to find the schema_ links for generating SQL queries for each question based on the database schema and Foreign keys. Hint helps you to find the correct schema schema_links. links. ###
Few examples of this task are: ###
Schema of the database with sample rows and column descriptions:
#
CREATE TABLE users
user id INT,
);
Table Values: User_id id
001
Table users
User_ide id: id of the user
Question: Among the lists created by user 4208563...
Answer: Let's think step by step. In the question users. user_id.
asked:
"user" need column
"number of followers" so we need column [lists.list_] dist_followerss!
Schema links: flist..lsst followers,,sooo [ lists users. user subscriber, :chhmm_linkss: lists. user_user_iss id lists baats_user.. user_id, lists list_ id lists user list__istdd
lists lists_users users.eer....................... user id, 4208563,
###
Schema of the database with sample rows and column descriptions:
CREATE TABLE [table_name] (
[column name] [column_type],
);
Table Values:
[3 row examples with header]
Table [table name]
[column [column_name]: name] : [column_deacriptioon
Question: [question]
43

---

Published in Transactions on Machine Learning Research (11/2024)
Answer: Let's think step by step.
A.7 Column and table data statistics of BIRD dataset
Table 36 illustrates the distribution of data regarding the number of columns and tables per example in the testing sets. The ground-truth distribution is also provided. Notably, BIRD presents more challenging scenario compared to Spider, with an average of 73 total columns to be selected per example, of which only 3.7 columns are used in the ground truth. Similarly, the average number of tables 7, and 1.9 tables are selected in the ground truth. However, it's essential to acknowledge that these sets still deviate from real-world scenarios with thousands of columns. This direction should be further explored in the future.
Table 36: Statistics for table and columns in the data-sets
BIRD Test
Spider Test Max.
Mean
Min.
Max.
P75
P95
SDT
Mean
Min.
P75
P95
SDT
Tables Columns
3
8
11.5
2.7
2
8.3
2.2
73
11
192
91.5
155.5
48.8
20.8
7
59
24
50
13.5
Columns per table
10.4
2
115
9
42.4
16.8
5.2
2
26
6
11.2
3.6
Ground-trus truth Tables
1.9
1
2
0.7
1.5
1
0.6
Ground-truth columns
3.7
1
9
5
6
1.4
2.4
1
6
3
5
1.2
Table 37: Average of number of columns and tables for questions for BIRD datasots: we compute the average number of table and columns for each questions.
Avg. number
Avg. of
Median number
Min. of
Max. of
number of queries
of table
column counts
of tables
column counts
column counts
viRD-valid 1534
7.4
76.3
71
11
201
BIRD-train 9428
12.0
77.4
48
457
A.8
Exploration on combining submodules
An alternative approach involves combining different input configurations in previous sections into a single training experiment. This method entails integrating various elements, such as mixed training data, synthetic data, database content, and column selection, into the inputs for a single experiment. However, the outcomes of such experiments reveal that merging these components does not result in performance improvements over using them individually. This suggests that LLMs may struggle to effectively process and understand all the provided information simultaneously during tuning.
Type
Train data
Method
Accuracy
Tuning
BIRD + Spider
database content
58.80
BIRD + Spider
Tuning
Syuthetic data database content
58.35
BIRD + Spider
database content
Tuning
58.08
Synthetic data soft column selection
Table 38: The effect of inlegrating all components into one training paradigm
A.9 Case Study of SQL Generation and Error Analysis
A.9.1 SQL-PaLM with few-shot Prompting
We present case studies of Few-shot SQL-PoLM in Table 39 and 40 for "correct" and "wrong" SQL generatod by Few-shot SQL-PaLM based test-suite accuracy of Spider dataset. Surprisingly, the majority of examples
44

---

Published in Transactions on Machine Learning Research (11/2024)
classified as "errors" by Few-shot SQL-PaLM were actually correct when evaluated by human experts, indicating the scores of SQI.-PaLM might be significantly higher. The evaluation fails due to (1) ambiguous questions, exemplified by 1st example in Table 40, and (2) official test-suite evaluation struggles on evaluating creative solutions with output which deviate from that of the ground-truth. For instance, the 2nd example has multiple valid ground-truths; the 3rd example experiences type-related issues; the 4th example presents different formats (e.g. "full name" and "first name, second name" are equally semantically correct for the question. They both should be considered correct); and the 5th example is false negative due to the omission of the "distinct" keyword.
Regarding the 'real' mistakes made by Few-shot SQL-PaLMM, such as the sixth and seventh examples, we observed a departure from simple errors like syntax errors commonly found in other methods (Liu et al., 2023a). Instead, the mistakes made by Few-shot SQL-PaLM closely resemble those that human experts would make when developing the same solution, demonstrating its profound expertise SQL. Another source of errors the presence of a "confusing database schema,' where Few-shot SQL-PaLM encounters difficulties in selecting the appropriate table or column when multiple equivalent options contain similar content (as illustrated in the 5th example of Table 40).
Tables 39 and 40 show the capabilities of Tew-shot SQL-PaLM, demonstrating that it can efficiently handle complex SQL queries. It successfully deals with tasks such as joining multiple tables using various keywords (as observed in the 1st, 2nd, 4th, and 5th examples in Table 39 and all examples in Table 40), as well as employing nested SQL structures (as seen in the 3rd example of Table 39). Moreover, Peo-shot SQL-PaLM exhibits the ability to generate creative and diverse SQL outputs that differ from the ground-trute but remain equally correct. This suggests deep understanding of SQL content rather than mere memorization. Notable examples include the 3rd example in Table 39 and the 2nd, 3rd, 4th, and 5th examples in Table 40. Even in cases of errors, such as the 6th and 7th examples in Table 40, Few-shot SQL-PaLM presents alternative solutions distinct from the ground-truth. Furthermore, Few-shot SQL-PoLM demonstrates the ability to infer relevant SQL expression based on semantic meaning, i.c. 'French singers' and "country=France, as well as 'young to old' and 'OrderBy age ASC' (as evident. the 1st and 2nd examples). This capability is attributed to the large-scale pretraining of LLMs.
A.9.2 Fine-tuned SQL-PaLM
Next, we present our manual investigation of the generated queries on the BIRD dev set. To quantify the error cases for the queries generated from the fine-tuned Few-shot SQL-PaLM, we randomly select 100 samples from BIRD dev set and categorize the queries that we considered mistakes based on the BIRD evaluation procedure.
Table 41 shows breakdown of the different error types that we have identified and provided some examples in Table 42 for each category. We categorize the false positives as Wrong Ground-Truth (19%) which are examples from the dataset that do not correctly answer the original question. Wrong Evidence (5%) which denotes examples that have incorrect or misleading human-annotated evidences. Ambiguous (2%) which are examples where the question's meaning is ambiguous and open-ended (e.g., not clear what is expected from the question). Evaluation Procedure (5% denoting that the generated SQL query was in fact correct but was considered incorrect by the evaluation procedure (e.g., the generated queries contains additional selected columns). For comprehensive list of examples from the different error types, refer to Table 42.
This investigation demonstrates that from 100 randomly sampled incorrect queries (out of a total of 584 for fine-tuned Few-shot SQL-PaLM), 31% of them are not correctly evaluated by the BIRD evaluation suite and if this sample size is reprosentative of the full dev set, it shows that there is a performance upper bound of around 70%.
45

---

Published in Transactions on Machine Learning Research (11/2024)
Table 39: Few-shot SQL-PoLM examples that are correct according to test-suite accuracy.
Data Schema
Question
Ground-truth
SQI-PaLM
Comment
QI: stadium:
What is the aver- age; minimum, and maximum age of all singers from France?
Inference on
Stadium ID, Location, Name, Capacity, Highest, Lowest, Average
SELECT avg(aga) min (ag() FROM ainger
SELECT avg(age) min (age)) FROM ainger
the relation- ship between "French" Francee
country
WHERE country
singer: Singer Country, Song Name, Song release year, Age, Is_male
Name,
Inference based on understand- ing the age ranking "order by
Show name, country, age for all singers dered by age from the oldest the youngest.
concert: concert__DD concert Name, Theme,
SELECT country age FROM singer ORDER BY age DESC
SELECT country ago FROM singer ORDER BY age DESC
Stadium ID, Year
singer in concert:
concert ID, Singer ID
DESC":
Q2: city: ID, Name, Coun- tryCode, District, Popula- tion qqlite_sequence: name,
SELECT T1. Nama FROM country A8 T1 JOIN countrylanguage 12 oN T1 Code T2
SELECT Ti.nama FROM country AS T1 J0IN countrylanguage 12 ON code T2
country: Code, Name, Continent, Region, Sur- faceArea, IndepYear, Pop- ulation, LifeExpectancy, GNP, GNPOLd, Local-
What are the names of uations both English and French are official Name, GovernmentForun languages?
CountryCode WHERE T2
countrycode WHERE 12 language "English" AND 12, 12.isffficaal "T" INTERSECT SELECT Ti.nama
Language "Eaglish' AnD T2. 120fffffiaal *T"
Generate Cx- GT
INTERSECT SELECT T1.Nama
FROM country A8 T1 JOIK countrylanguage
FROM country AS T1 J0IN countrylanguage
HeadOfState, Capital, Code2
T1.Cade = 12. Countryddd
code = countryyoo
WHERE T2. Language
WHERE T2, la.ggnggge French" AND 12.isofficial "T*
countrylanguage: Coun-
French' AND T2. IeOfficial "T"
tryCode, Langunge, IsOmi.
cial, Percentage
Q3: stadium:
dium_ID, Location, Name, Capacity, Highest, Lowest, Average
SELECT count()) PRGH concert AS T1 JOIN
singer: Singer ID, Name, Country, Song Name,
What the ber of concerts that occurred in the stadium with the largest capacity
select count(4) fron concert where stadium_id (salect stadium, from atadiun order by capacity limit
Creative
stadium 0K
different from ground- truth
Song release year, Age, male
st.stadiun, T2. atadium,
T2.
but
concert: concert__Di ID, con- cert Name, Theme, Sta- dium ID, Year singer in concert: con- IDniin ID
capacity ( SELECT nax
73. capacity) FROM stadium T3
select t1 ti.fnane from student as t1 join ti.stuid t3.petid
SELECT frane FRCH student AB JOIN
has_pet
Ti.stuid r2uid JOIN pets petped petid WHERE T3 pettyee "cat" INTERSECT SELECT fi.inane FROM student 7I JoiN has patt AS OM 12.stuid pete AS T3 DK 12. T3.petid "dog"
has_pet
Q4: Student: StuID, LName. Fname, Age, Sex, code Has Pet: StulD, PetID Pets: PetID, PetType, pet_age, weight
t2.stuid join pets
Complex
Find the first name of studenta who have both cat and dog pets
Major, Advisor,
petid where pettype 'cat' intersect select t1. ti.fnane from student t1 join has as t2 on ti.etuid studd join pete as t3 on petid petid 'dog'
SQL contain- ing multiple
SQL ley- words.
t3. pettype
13.pettype
Q5: continents: ContId, Continent
select t1.countryid t1. countryuane fron countries
co.utryyy T1. co.uoturye FROH countries AS car makers A8 72 ON T1 ccuntrridd c2.country GROUP T1. countryid BAVING count(*)
countries: Countryld, CountryName, Continent car makers: Id, Maker,
join car, makers as t2 on t1 countryid t2. country group t1, countryid having count(*)
What are the ids and names of all countries that either have more than 3 makers pro- duce fiat model
FullName, Country model list:
Complex
Modelld, Maker, Model
SQL coutain-
union t1 countrynane from countries as join car nakera as t2 on t1. countryid 12.country join nodel_list as t3 +2. maker where t3 model 'fiat';
countryd
UNION countryannn FROM countries
countzyy
ing multiple key-
Makeld, Madel, Make
SQL words
cars_data:
data: Id. MPG, Cylinders, Edispl, Horse-
car makers AS 72 ON T1. countryid T2.country JOIN model_list AS T3 72 T3. T3.naker WHERE "fist"
power, Weight, Accelerate,
Year
46

---

Published in Transactions on Machine Learning Research (11/2024)
Table 40: Few-shot SQL-PaLM outputs that are incorrect according to test suite evaluation. Indeed, according to human experts, the majority of these "crror cases" are actually the correct SQL outputs.
Question 1:
What is the maximum capacity and the average of all stadiume ? databuse=coneert. singer)
(id=16,
Ground-truth:
select (ax average from stadiun SELECT max(capacity) aug(capacity) FROM stadiun
SQL-PALM:
Comment:
Ambiguous question. The Ground-truli -ruth query would give the average occupancy column of each stadium,
where as the SQL-PALM one would give the average of the capacity column of all stadiums. Without specific context, the SQI-PALM output matches the question better. Trme (Ground-truth issue)
Human Expert
Question 2: Ground-truth:
Which airline has most number of flights? (id=229, database-flight 2)
SELECT T1 Airrlieeee FROM AIRLINES AS TI JOIN FLIGHTS AS T2 DW T1.uid A2rAine GROUP BY Ti.Airline ORDER BY count(*) DESC LIMIT 1
SQI-PALM:
SELECT Ti-airline FROM airlines AS JOIN flights 48 T2 ON T1.uid 12.airline GROUP BY 12.airline URDER BY count(s) DESC LIMIT 1
Comment:
Both Ti.Atrline (the Airlines. kirline column, which contains names of airlines) and 12. airline (the rlights.airine column, which contains the uid of airlines) uniquely identify airlines, and grouping by either one gives the same results. The evaluation failed because the data contains 12 airlines each with 100 flights (i.e. every one is the maximum), and instability of the ordering produces different outcomes when both querles pick one of the maximums. True (Database
Human Expert
Eval isaue)
Question 3:
What are the Id, name and membership level of visitors who have spent the largest amount of money in total in all museum tickets? (id=120, database=museun visit)
Ground-truth:
SELECT t2.visitor__ id ti.nane t1. m.nnerrsbippppp FROM visitor AS :1 JOIN visit AS t2 ON ti.id (ttal
t2.riaitor, id GROUP BY t2.visitor_ DRDER (131, 'Arjen Robben', 1), where '3" is a string
DESC LIMIT
Excution result: SQL-PALM:
SELECT Ti.id. nene Ti.level_od oe_nebberhhip FROM visiter JOIN visit AS ON T1.id visioor id CROUP BY 11.1d URDER BY (T2. DESC LIMIT
Excution result:
(3. 'Aree Robben', 1), where 3 an integer
Comment:
The result is actually correct. 'The highlighted differences t2. visttor id and El.id equivalent because the two tables are joined on t1:1d - viaitor_id. Evaluation falled because of the field type differences,
ti.id int t2. visitor_idd text.
Human Expert
True (Database and Eval issuc)
Question 4: Ground-truth:
List the names of all winners who played in both 2013 and 2016. (id=447, database=wla, 1) SELECT winner _aae FROM natches WHERE YEAR 2013 INTERSECT SELECT simner_asn FROM natches WHERE 2016
Excution result: SQL-PALM:
(' ('Angelique Kerber'.,, J. ('Petra Kvitova',)
SELECT Ti.first, nane T1.last, nane FROM playars TI JOIN natches AS TI.player, id T2. 17.vinner_Bt Id WHERE year 2013 INTERSECT SELECT Ti.firat_naa _arrt_aae_____e Ti-last_paree FROM players AS Ti JOIX natchee AS T2 ON T1 Ti-player_ic T2, winner_id WHERE y2. 2016 ('Angelique', KKebber]. ('Petra'. "Kyitova'] The result is actually correct. The highlighhed differences, as both exccution results make sonse from semantic perspective of query. winner_nan name and T Ti.first_ name, Talastt name are equivalent for ropresentation of "name", Evaluation failed because of the differences in the output format type. If multiple ground-truths are provided considering different output formats, this is not an error. True (Eval issue)
Execution result:
CComentt
Human Expert
Question Ground-truth: SQL-PALM:
What the different template type codes? (id=322, database=cre Doc Template Mgt):
SELECT DISTINCT template_ pepilatte_yy___eee_eee__e sype_code FROM Templates
SELECT DISTINCT tomplate_tyya tttttt___cccee cada FROM Raf Taapaaaaa_aaaaeeeee
Comment:
The results actually correct. Pew-shot SQL-PaLM selects different table Ref Template Types, in- stoad of Templates. The same "template types" appear in multiple entries of Templates. The two SQL. outputs generate the execution result when evaluating with "DISTINCT", Evaluation failed because Spider official evaluation removes 'DISTINCT" during evaluation.
Human Expert
True (Eval issue)
Question 6:
Find the number of professionala who have not treated any database=dog kennels):
dogs,
(id=983,
Ground-truth:
SELECT count(*) FROM Professionals WHERE professional, id NOT IN ( SELECT professional, id FROM
Treatments
SQL-PALM:
SELECT count(*) FROM Professionals EXCEPT SELECT professional, id FROM Treatnents and Right sides "EXCEPT" need equivalent content. Corrected SQL SELECT count (+) FROM (SELECT
Comment:
professional_ id FROM Profesaionals EXCEPT SELECT profeeaional_ 1d FROM Treatnents) False (Wrong Use of keywords)
Human Expert
Question 7: Ground-truth:
Find the number of professionals who have not treated any dogs. (ld=754, watlb select ti.came from country as t1 join countzylanguage
12 tod countrycee wbere t2. language fenglish" and isofficial = Hell union select t1. tinnane from country as t1 join countrylanguage as t2 on to.code t2. countrycode where 12, language rautehit and isofficial men SELECT T1. Ti.mane FROM country AS T1 JQIN countrylanguage
SQL-PALM:
T2 ON code counttyyre WHERE T2, languag "English" OR 72.1anguage = "Dutch" AND T2. 12.isofficial Operator Precedence: ADD > OR. Need add parenthesis
*T"
Comment:
"OR". Corrected SQL is SELECT mane FROM country AS T1 JOIN countrylanguage AS T2 ON Ti.code - T2. countryyed WHERE languae "Eaglish" OR 12.langu ground-truth outpats to all lovercase for easier evaluation, but nismatch exists when referring databage content. Changes :aangee:english--EE enggishh dautliiitedttdDeeeeee 2->? False (Wrong operator precedence and eval issue)
"Datch" AND i2fioofficial "T*, Spider evaluation normalizes the
Human Expert
47

---

Published in Transactions on Machine Learning Research (11/2024)
Table 41: BIRD dev set errors from a sample of 100 queries denoted as "incorrect" " by the evaluation procedure.
Falsc-positive category Wrong Ground-Truth
Number of examples
19
Wrong Evidence
5
Ambiguous
2
Evaluation Procedure
5
48

---

Published in Transactions on Machine Learning Research (11/2024)
Table 42: These examples demonstrate the different categories of errors of SQL-PoLMM-fne-tune
Wrong Ground-Truth
Example 1:
Question
Write all comments made the post titled 'How does gentle boosting differ from AdaBoost?' (id=579, sttaaase==cddbaace community)
Ground-truth
SELECT Text FROM comments AS INKER JDIN posta 12 ON Ti.PostId 12.1d WHERE T2, Title doea gentle boosting differ FROM AdaBooat?',
'How
Comment:
ground-truth query has an upper case "FROM" instead "from" which what the question.
Example 2:
Question
What's the finish time for the driver who ranked second in 2008's Australian Grand Prix? 937, sattbbas==formll.
Ground-truth
SELECT timeine AND T2. name "Austrraiann GrAND Prix' AND year 2008
resulta AS INWER
T2 Ti.raceId raceId WHERE Ti.rank
Comment
Similarly the previous example, the ground-truth query string doesu't match one from the question. Likely this is due a data-clraning procedure in the BirdSQL, dev set.
Example 3:
Question
What race number has the most finishers? (id=979, catabase==fomulll 1) SELECT raceld FROM results
Ground-truth Comment
raceld By COUNT NOT NULL) The (tine IS NOT NULL) is somewhat inconvenionnal. Typically, cOUNT is used on a column name directly. However, here it counting whether time is null or not, since the expression tine IS NOT NULL always either true false, both of which are counted.
LINIT
boolean result tine I8 NUT NULL. This will count all rows, regardless of
Example 4:
Please provide top three football players' IDs who are among the lowest potential players and prefer to use the right foot when attacking. (Id=1135, detabase=eurppean (orrppean_football____ SELECT id FROH Player_Attribuse
Ground-truth Comment
WHERE preferred_foot 'right' ORDER BY potential DESC LIMIT The questions asles the 'lowest potential players' the ground-query should order by descending patential should not have DESC.
Wrong Evidence
Example 1:
Question
What is the eligible free rate of the 10th and 11th schools with the highest enrolment for students in grades 1 through 12?! (id=31, database-calforriia diaabaeecollforria__kooooi ) schools)
Ground-truth
SELECT EAST("Free Heal Count (K-12)* AB REAL) / 'Earolloent (K-12)' FROM frpm URDER BY 'Enrollnent (K-12) DESC
9, K-12 refers to students in grades through 12; Eligible free rate for K-12 'FRPM Count (K-12)' / 'Enrollment (K-12)'
Evidence
Comment:
The evidence suggests that the information can be found in columu 'FRPN Count (K-12)* but
that
in the ground-truth another column is actually chosen.
Example 2:
Question
Among chemical compounds that contain molocule TR047, identify the percent that double-bond? (id=287, database==toxioolggy)
Ground-truth
SELECT CAST(COUNT WHEN T. bondoottpp type THEY T. ELSE NULL END) AS REAL) 100 COUNT bond 14) FROM bond WHERE T.molacule_tt 'TRD47'. TR047 is the molecule id; double bond refers to bond bond_type = = percentage DI-
Evidence
VIDE(SUM(boud type 'TR047"
") COUNT(all bond id)) percent where molecule id
Comment:
evidence suggests that bond bond_typa has spares whereas ground-truth quory no spaces.
Ambiguous
Example 1:
Question
How many users database=codebase community) SELECT CIUNT(Id) FROM users WHERE date (LastaccessDate) 22014-09-01'. It is not clear from the question whether we should include that accessed the website exactly the day of 2014/09/01.
last
the website after 2014/9/12
(id=533,
Ground-truth Comment:
Evaluation Procedure
Example 1: Question
What is the height of the tallest player? database=europpann_ foot_fooohaal_____ SELECT player name FROM Player ORDER BY height DESC LINIT 1
Indicate
his name.
(id=1021,
Ground-truth SQI-PALM:
SELECT height, player, nana FROM Player ORDER haight LIMIT 1;
Comment:
The generated query selects additional column but is essentially equal to the ground-truth query.
Example 2:
Question
How many races were there in 2005? Name all the races in descending order. (1d=592, database=formule
Ground-truth SQL-PALM:
SELECT name FROM races WHERE year 2005 ORDER BY name DESC
SELECT CoUKT(racaId)
races WHERE year 2005 UNION ALL nane races year
ORDER BY zane DESC;
Comment:
The generated query selects contains the correct selection of races and also includes the count with UNION
ALL statement.
49

---

Published in Transactions on Machine Learning Research (11/2024)
A.10
Prompt examples
A.10.1
Concise Prompt Design: 4 shot
This is task converting text into SQL statement. We will first given the dataset schema and then ask question in text. You are asked to generate SQL statement.
Here is an example: Convert text to SQL:
[Schema [values)]: | farm city city_id official_nant name status area_km_2 population
census census_rankiin _rankingg farm farm_id year total_horsen working_horsee total_cattle oxen
bulls cows pigs sheep_and and__aadd farm farm_ competition competition year theme host_city_id hosts competition_reca cerettiiinrerrrd_ee competition_is _id farm_id rank;
city city_id (number) city official_name (text) city status (text) city area_ km (number) city population (number) | city census, massusrraniing ( text) farm farm_is id (number) farm year (number) | farm total_horses horses (mumber) farm brrking_horsee (number) farm total_cattle (number) | farm oxen (number) farm bulls (number) farm coss (number) farm pige (number) farm sheep_and_goate (number) farm_ farm_competition competition (number) @arm_competitine theme (text) camm_cometiiion hoat_city__i _id (number) camm_copetiiion hosts (text I campetition_record competition_id (number) competition_recom ceciitiiiiorerrrdeeo farm_id (number) competition_recom rank (number);
[Column names (type)];
year (number) farm_competitien
Primary Keys|
city city_id farm farm_ id
farm cirm__compeiiiio competition id
competition_reant recorrd competition_id.
[Foreign Keys]:
farm_ cemm_comeetiionn host_city: equals city city_id competition
farm_id equals farm farm_id competition_ recorrdrrrrrrree competition equals competitnttiionn competition_ic id
Q What are the themes of farm competitions sorted by year in ascending order?;
[QLE select theme from farm_competition order by year asc;
Here is an example: Convert text SQL:
[Schema (values)] farm city city_id afficial_name status area_km_2 population
census Baaeus_ranking_e farm farm_id year total_horses working horses cotal_cattle
pigs @ioep_and_ooate farm_competition competition_id year theme coot_citt_iis hosts coupetition_record competition_id id farm_id rank;
bulls
[Column names (type)]: city city id (number) city official Dericial_nameeeeee name (text) city status (text) city area_km 2m_ (number) city population (number) city census mansus_ranking ( text) farm farm_it (number) farm year (number) farm total_horses horses (number) farm working_horses (number) farm total_cattle (number) farm oxen (number) farm bulls (number) farm
(number) farm pigs (number) farm sheep_and_goat (number) farm competition competition_id (number) farm_competition year (number) farm_competition theme (text) carn_competition host_city (number) farm, competition hosts (text
I ceapetition_record competition_ia (number) caapetition_record farm_id (number) competition_reecrdd rank (number);
city city_id farm far=_id Sarm__ompetitiinn competition_5 competition_recort compstition_ia _i; [Foreign Keys] farm_competite host__ity__t equals city city_id competition_ record farm_id equals farm farm_id competition_record competition_ia id equals farm_coree competitieiino.... competition_ie
Primary Keys]:
Q What are the maximum and minimum number of cows across all farms.;
SQLE select max (cous), min(cows) from farm;
50

---

Published in Transactions on Machine Learning Research (11/2024)
Here is example: Convert text to SQL:
[Schema (values)] department, _aanggemennnnttttttt
| department department_ id name creation head head_id id name born_state age araporary_acting Yes );
ranking budget_ in_ billions num_ cim_emmloeees
management department_id id head_id id
department department_ (number) | department name (text) department creation (text) department ranking (number) department budget_in_ billions number) department _um__mmlmeeeeeeee employees (number) head head (number) head
[Column names (type)]:
num
(text) head
born_state (text) | head age (number) management department_ id (number) management
head_id id (number) management : cmmporaryactting (text);
Primary Keys] department : department_ id head : head id management : department_ice id;
Forcign Keys management : head_id id equals head head_ management department_ id equals
department department_id
[Q] Show the and number of employees for the departments managed by heads whose temporary acting value is YYe'??
[SQUE select t1.name, s1.num_employe from department as t1 join management as t2 on t1. department_ id deparrmtttt id where t2. ccntorary.acting "Yes";
Here is an example: Convert text SQL:
[Schema (values)]: farm city city_id, official _ame status area _m__km_2_ population census_ratie Bneuusranning farm farm_id year total_horsen working_horsee horses , lotal_cattle oxen bulls cows pigs sheep and _goats farm_ competition competition_ id year theme host_city_id hosts competition_record competition_id _ndd farm_id rank;
city : city_id (number) city : official_name (text) city status (text)) city area_km (number) city population (number) city : census_rankine ( text) farm farm_id (number) farm year (number) farm total_horses (number) farm working_horas horses (number) farm total_cattle (number) farm oxen (number) farm bulls (number) farm cous (number) | farm pigs (number) farm sheep_and_gosts (number) farm coopetiiiiiinnnn.. competition_is id (number) farm__ompetition year (number) farm_competitinn theme (text) farm_competition host_city_it (number) aarm_compttition hosts (text
[Column names (type)]i
) conpetition_receet competition_ia id (number) competition_recatt farm_ (number) competition_ recorrd rank (number);
PPrimary Keys]
city city_id farm farm_id come__oopetition competition
competition_reet record competition_id;
[Foreign Keys]: farm_comee competiiptii host_city_tt equals city sity_id_ competition cecciioonrrrrrr_//e
farm_id equals farm farm_id competition_recors competition id equals carm_comettiton competition_ic
[Q] Show the status of the city that has hoated the greatest number competitions.;
host_ host_city__t id group t2.host. 12.host_city_tte order count(*) desc limit 1; t1. ti.city_id t2.
[SQUE
select ti.status from city join farm_ carm_cometiiion
Here is the test question to be answered: Convert text to SQL:
[chemma (values)]:
concert_singer stadium stadium_id location name capacity highest lovest average singer singer_id name country Long_name
Long_release_yeee year age is_male concert concert_id id concert concert_name name theme stadium year singer_in_coneet inncccerrtttt....oo concert_id singer_id;
[Column names (type)]; stadium stadium_id (number) | stadium location (text) stadium
(text) stadium capacity (number) stadium highest (number) stadium lowest (number)
51

---

Published in Transactions on Machine Learning Research (11/2024)
stadium average (number) singer singer_ (number) singer (text) singer country (text) singer song_name (text) singer vong_release_y (text) singer age (number
I singer is_male (others) concert concert_id _id (number) concert concert_nan name (text) concert theme (text) concert stadium id (text) concert year (text) singer in concert concert_id (number) singer_in_ _nncerrt singer_id (text);
[Primary Keys]
stadium stadium id
singer singer_id concert concert_ic
singer_ singer_in_comee in_concertt concert_ide
Foreigg Keys| concert : stadium equals stadium stadium id itt_einnccoccrt///o singer_ia
equals singer: singer_id I cinger_in_concert concert_id equals concert : concert_id
[Q] How many singers do we have?;
[SQL]
A.10.2 Verbose Prompt Design: 4 shot
This is a task converting text into SQL statement. We will first given the dataset schema and then ask question in You asked generate SQL statement.
Here example: Let take question and turn it into SQL statement about database tables. There are tables
Their titles are: city, farm, competintt,,, conpetition_recors Table 1 is city, and its column names and types are: City_ID (Type is number), @fficial_Name (Type is text), Status (Type is text), Area_km _m__ (Type number), Population (Type is number), Census_Ranking (Type is text). Table is farm, and its column names and types are: Farm_ID (Type is number), Year (Type number), @otal_Horsed (Type is htth:err/ttpss///rraatt/ Working_Horsee
goog/
sroject/6590 (ooject//550ad0244225000 (198000222229009955999999 Total_Cattle (Type is number), Oxen (Type is number), Bulls (Type is number ) Cows (Type is number), Pigs (Type is number), Sheep_and_Goats (Type is number). Table 3 compettint,,, and its column names and types Competition_ID (Type is number), Year (Type is number), Theme (Type is text Host_city_ID (Type number), Hosts (Type text). Table is conpetition_recoras and its column names and types are: Competition_ ID Type number), Farm_ID (Type is number), Rank (Type is number).
(Type is number),
)
The primary keys ares city_ id from Table city, farm_id from Table farm, competition_id id from Table farm_competition, competition_id from Table conpetition_record
The foreiga keys are: host_city_1c from Table farm_competition is equivalent with city_id from Table city, farm_id from Table competition_recore is equivalent with farm_id id from Table farm, competition id from Table conpetition
is equivalent with competition_io id from Table car__competitinn Use foreign keys to join Tables. Let us take a text question and question is: What are the themes of farm competitions sorted by year in ascending order? The corresponding SQL farm_ comp_oomptii ORDER BY YEAR ASC;
it into SQL statement about database tables. The
SELECT Theme FROM
Here is an example: database tables. There are 4 tables
us take a question and turn it into a SQL statement about
coppetitioo_reecrrt Table 1 is city, and its column names and types are: City_ID (Type is number), Official_Name (Type is text), Status (Type is text), Area_km_2 (Type is number), Population (Type is number), Vensus_Rankin (Type is text). Table 2 is farm, and its column names and types are: Farm_ID (Type is number), Horses (Type is number), sarking_Horses (Type is number), Total_Cattle (Type is number), Dxen (Type is number), Bulls (
Their titles are: city, farm, farm_competition,
Year (Type number), Total_Horses
52

---

Published in Transactions on Machine Learning Research (11/2024)
Type number), Cows (Type number), Pigs (Type number),
Sheep_ and_ Goats (Type is number). Table is farm_ caa___oopttitonn and its column and types are: Competition ID (Type is number), Year (Type is number), Theme (Type is text), Host_city_ID (Type is number), Hosts (Type text). Table 4 is competition_ recroon_ and its column Competition_II (Type is number), Farm_ID (Type is number), Rank (Type is number).
and types
The primary keys are: city_id from Table city, farm_id from Table farm, competition_id id from Table farn_competition competition_ic from Table competition_recory
The foreign keys area host_city_ic from Table farm_competition is equivalent with city_id from Table city, farm_id from Table competition_recore is equivalent with farm_id id from Table farm, competition from Table conpetition_recome is equivalent with competition_id from Table farm corr__oopptiionn Use foreign keys to join Tables. Let us take a text question and turn it into question is: What are the maximum and minimum number of cows across all farms. The corresponding SQL is: SELECT (Cous) min(Cows) FROM farm; Here is example: Let us take question and turn it into a SQL statement about database tables. There are tables
statement about database tables. The
Their titles are: department, head, management. Table 1 is department, and its column names and types are: Department_ ID (Type number), Name ( Type is text), Creation (Type is text), Ranking (Type is number), Budget_in_Billle
(Type is number), Num Bum_oyoee (Type is number). Table is head, and its column names and types are: head_ID (Type is number), name (Type is text), born_state (Type is text), age (Type is number). Table is management, and its column is number), head_ID (Type is number), temporary_ acting (Type is text).
and types
department_ ID (Type
The primary keys are! department_id from Table department, head_id from Table head, department_id id from Table management.
The foreign keys are: head_id from Table management is equivalent with head_id from Table head, department_id from Table management is equivalent with department_ic from Table department. Use foreign keys to join Tables.
Columns with relevant values: Table management Column temporary_acting have values: Yes; Only use columns with relevant values to generate SQL. Let take text question and turn it into SQL statement about database tables. The question is: Show the name and number employees for the departments managed by heads whose temporary acting value is 'Yes The corresponding SQL is: SELECT name Ti,num employees FROM department AS Ti JOIN management AS ON T1. departtmmett semporary_acting
T2. deparrrteentt WHERE T2
'Yes';
Here is an example:
question and turn it database tables. There are 4 tables
us
SQL statement about
Their titles are: city, farm, competonpt,,,inn, conpetition_res record. Table 1 is city, and its column names and types are: City_ID (Type is number), Official_Name (Type is text), Status (Type is text), trea_km_2 (Type number), Population (Type is number), Lensus_Ranking (Type is text). Table is farm, and its column names and types are: Farm_ID (Type is number), Year (Type is number), Total_Horses (Type is number), borking_Horses (Type is number), Total_Cattll (Type is number), Dxen (Type is number), Bulls Type number), Cows (Type number), Pigs (Type number), seee_and_GGe
(Type is number). Table is farm_ farm_coapetition column names and types are: Competition_ (Type is number), Year (Type is number), Theme (Type is text), Host_city_ID (Type is number), Hosts (Type text). Table 4 Competition ID (Type is number), Farm_ID (Type is number), Rank (Type is number).
and its
ieceetttion_reordd and its column
and types
The primary keys are: city_id from Table city, farm from Table farm,
53

---

Published in Transactions on Machine Learning Research (11/2024)
competition_i0 from Table farm_ conpetiittt competition_id id from Table conpetition_recam record.
The foreigm keys host_city_id from Table farm_ competition is equivalent with city_id from Table city, farm_id from Table competition_recort is equivalent with farm_id from Table farm, competition_io from Table crapetition_recooe is equivalent with competition_id from Table farm_ compecooptt Use foreign keys to join Tables. Let take text question and turn it into a SQL statement about database tables. The question is: Show the status the city that has hosted the greatest number of competitions. The corresponding SQL is: SELECT т1 Status FROM city TI JOIN farm_ competittii AS T2 C1.City_ID GROUP BY T2. Rost_city_ID ORDER BY COUNT (+) DESC LIMIT 1;
т2. Host_city__n
Here is the test question to be answered: Let us take a question and turn it into a SQL statement about database tables.
There are 4 tablea. Their titles are: stadium, singer, concert,
singer singer_in_conee innnccnnetrt.....m Table 1 stadium, and its column Stadium_ID (Type is number), Location (Type is text), Name (Type is text) Capacity (Type number), Highest (Type is number), Lowest (Type number), Average (Type is number). Table 2 is singer, and its column names and types are: Singer_IJ (Type number), Name (Type text), Country Type is text), Somg_Name (Type is text), Song_release, year (Type is text), Age (Type
and types
concert, and its column names and types are: concert_ID (Type is number), concert Name (Type is text), Theme (Type is text), Stadium_I] (Type is text), Year Type is text). Table 4 is singer_in_concerv types are: concert_ID ID (Type is number), Singer_ID ID (Type is text).
number), Is_male (Type is others). Table
and its column names and
The primary keys are: stadium id from Table stadium, singer_ic id from Table singer, concert_ie _id from Table concert, concert_id from Table singer_in_concers
The foreign keys are! stadium_id id from Table concert is equivalent with concert is
stadium_id from Table stadium, singer_id from Table equivalent with singer_id from Table singer, concert_id from Table singer_in cencee_ concert is equivalent with concert from Table concert. Use foreign keys to join Tables. Let take text question and turn it into a SQL statement about database tables. The question is: How many singers do have? The corresponding SQL is:
A.11 Database content
See "[Database values that related with questions):" in red to show database content values.
Here is the test question to be anwered: Convert text to SQL:
(Schema (values)]: saloolsnia_schools frpm CDSCode , Academic Year
County Code District Code School Code County Name District Name School Name District Type School Type Educational Option Type NSLP
Provision Status, Charter School (Y/N) Charter School Number Charter
Funding Type IRC Low Grade High Grade Enrollment (K-12) Free Meal
Count (K-12) Percent (%) Eligible Free (X-12) FRPM Count (K-12)
Percent (%) Eligible FRPM (K-12) Enrollment (Ages 5-17) Free Meal Count
(Ages 5-17) Percent (%) Eligible Free (Ages 5-17) FRPM Count (Ages
5-17) Percent (%) Eligible FRPM (Ages 5-17) 2013-14 CALPADS Fall
Certification Status I satscores cds rtype sname dname cname
enroll12 NunTstTakr AvgScrRead AvgSorMath AvgScrWrite NumGE1500
schools CDSCode NCESDist NCESSchool StatusType County District
School Street StreetAbr City Zip State MailStreet
MailStrAbr Mailcity MailZip , MailState Phone Ext Website
OpenDate ClosedDate Charter CharterNum FundingType DOC DOCType
54

---

Published in Transactions on Machine Learning Research (11/2024)
SOC SOCType EdOpaCode EdOpsNane EILCode EILName GSoffered
GSserved Virtual Magnet Latitude Longitude AdmFNamei AdmLNamel
AdmEmail1 AdmFName2 AdmLName2 AdmEmai12 AdmFName3 AdmLName3
AdmEmail3 LastUpdate;
[Column names (type)] frpm edscode (text) frpm academic year (text) frpm county code (text) frpm district code (number) frpm school code (text) frpm county name (text) frpm district name (text)
frpm school name (text) frpm district type (text) I frpm school type (text) frpm educational option type (text) frpm nslp provision status (text) | frpm charter school (y/n) (number) I frpm charter school number (text) I frpm charter funding type (text) frpm irc number) I frpm low grade (text) frpm high grade (text) I frpm enrollment (k-12) (number) frpm free meal count (k-12) (number) frpm percent (X) eligible free (k-12) (number) | frpm frpm count (k-12)
number) frpm percent (%) eligible frpm (k-12) (number) I frpm enrollment (ages 5-17) (number) I frpm free meal count (ages 5-17) ( number) I frpm percent (%) eligible free (ages 5-17) (number) frpm frpm count (ages 5-17) (number) frpm percent (%) eligible frpm (ages 5-17) (number) I frpm 2013-14 calpads fall certification status (number satscores : (text) satscores rtype (text) satscores : sname text) satscores dname (text) I satscores chame (text) I satscores enroll12 (number) satacores numtattakr (number) satecores
avgacrread (number) | satscores avgecrmath (number) | satscores
avgscrwrite (number) I satscores : numge1500 (number) schools cdscode text) schools ncesdist (text) schools ncesschool (text) schools statustype (text) I schools county (text) schools district (text) schools school (text) schools street (text) schools : streetabr ( text) schools city (text) schools zip (text) schools state ( text) schools mailstreet (text) schools mailstrabr (text) I schools mailcity (text) I schools mailzip (text) schools mailstate (text) schools phone (text) schools ext (text) schools website (text) schools opendate (tine) schools closeddate (time) schools
charter (number) schools charternum (text) schools fundingtype text) schools doc (text) schools doctype (text) I schools : soc ( text) schools : soctype (text) I schools : edopscode (text) schools edopsname (text) schools eilcode (text) I schools eilname (text) schools gsoffered (text) I schools : gaserved (text) schools : virtual (text) schools magnet (number) schools latitude (number) | schools longitude (number) I schools : admfname1 (text) schools admlname1 ( text) achools admemail1 (text) schools admfname2 (text) schools admlname2 (tert) schools admemail2 (text) schools admfname3 (text)
schools admlname3 (text) schools admemail3 (text) schools
lastupdate (time);
[Priaary Keysl: frpm CDSCode satscores cds schools CDSCode;
[Foreign Keys] frp= CDSCode equals schools CDSCode I satscores cds
equals schools CDSCode;
[atabase values that related with questions]
The column "County Name Table 'frpm"
database values: Alameda
The column "cname" in Table 'satscores" has database values: Alameda
The column "County" in Table "schools' has database values: Alameda
The column 'City' in Table 'schools' has database values: Alameda
The column 'MailCity' in Table "schools' has database values: Alameda
The column 'CSoffered' in Table 'schools" has database values: K-12
The column ' in Table 'schools" has database values: K-12
The column 'AdmFName1' Table 'schoola" has database values: Rae
The column "AdmLNamee" in Table 'schools' has database values: Free
55

---

Published in Transactions on Machine Learning Research (11/2024)
[Additional Infol: Eligible free rate for K-12 'FRPX Count (K-12) Enrollment (K-12) bttps://braintes goog/ p wttpe//...002222222222222222222 [A]: What is the highest eligible free rate for K-12 students in the schools in Alameda County?;
[SQL]:
Here is example: Convert text to SQL:
A.11.1 Full column description
See "[detailed description of tables and columns]" in red to show entire column descriptions, and they very lengthy.
Here is the test question to be anwered: Convert text SQL
[Schema (values)]: I salifornia_schools frp= CDSCode Academic Year
County Code District Code School Code County Name District Name
School Name District Type School Type Educational Option Type NSLP
Provision Status Charter School (Y/N) Charter School Number Charter
Funding Type IRC Low Grade High Grade Enrollment (K-12) Free
Meal Count (K-12) Percent (%) Eligible Free (K-12) FRPM Count (K-12)
Percent (X) Eligible FRPM (K-12) Enrollment (Ages 5-17) Free Meal
Count (Ages 5-17) Percent (%) Eligible Free (Ages 5-17) FRPM Count (
Ages 5-17) Percent (%) Eligible FRPX (Ages 5-17) 2013-14 CALPADS Fall
Certification Status satscores eds rtype sname dname cname
enroll12 NunTstTakr AvgScrRead AvgSerMath AvgScrWrite NumGE1500 schools CDSCode NCESDist NCESSchool StatusType County
District School Street StreetAbr City Zip State MailStreet
MailStrAbr MailCity MailZip MailState Phone Ext Website
OpenDate ClosedDate Charter CharterNun FundingType DOC DOCType
S0C SOCType EdOpsCode EdOpsName EILCode EILName GSoffered
GSserved Virtual Magnet Latitude Longitude AdmFName1
AdmLName1 AdmEmail1 AdmFName2 AdmLName2 AdmEmail2 AdmFName3
AdmLName3 AdmEmai13 LastUpdate;
[Column names (type)]: frpm CDSCode (text) frpm Academic Year (text)
frpm County Code (text) frpm District Code (number) frpm : School
Code (text) frpm County Name (text) frpm District Name (text)
frpm School Name (text) frpm District Type (text) frpm School Type (text) frpm Educational Option Type (text) frpm NSLP
Provision Status (text) I frpm Charter School (Y/N) (number) frpm Charter School Number (text) frpm : Charter Funding Type (text) | frpm IRC (number) I frpm Low Grade (text) frpm High Grade (text) I frpm Enrollment (K-12) (number) frpm : Free Meal Count (K-12) (number) | frpm Percent (%) Eligible Free (K-12) (number) I frpm FRPM Count (K -12) (number) frpm Percent (%) Eligible FRPM (K-12) (number) I frpm Enrollment (Ages 5-17) (number) I frpm Free Meal Count (Ages 5-17) number) I frpm Percent (%) Eligible Free (Ages 5-17) (number) frpm FRPM Count (Ages 5-17) (number) I frp= Percent (%) Eligible FRPM (Ages 5-17) (number) frpm 2013-14 CALPADS Fall Certification Status number) I satscores eds (text) satscores rtype (text) | satacores sname (text) satscores : dname (text) satscores cname (text) satscores enroll12 (number) | satscores NunTstTakr (number) I satecores AvgScrRead (number) I satscores AvgSorMath (number) I satscores AvgScrWrite (number) satacores NumGE1500 (number)
schools CDSCode (text) I schools NCESDist (text) 1 schools
NCESSchool (text) schools StatusType (text) I schools County (text)
schools District (text) schools School (text) I schools Street ( text) schools StreetAbr (text) schools City (text) I schools Zip (text) | schools State (text) schools MailStreet (text) schools
56

---

Published in Transactions on Machine Learning Research (11/2024)
MailStrAbr (text) schools MailCity (text) schools MailZip (text)
schools MailState (text) schools Phone (text) | schools : Ext ( text) schools Website (text) schools OpenDate (time) schools ClosedDate (time) schools Charter (number) schools CharterNum ( text) schools FundingType (text) | schools DOC (text) schools DOcType (text) schools SOc (text) schools : SOcType (text) I schools EdOpsCode (text) schools EdOpsName (text) schools : EILCode (text ) schools EILName (text) schools GSoffered (text) schools
GSserved (text) schools Virtual (text) I schools : Magnet (number)
schools Latitude (number) schools Longitude (number) schools
AdmFName1 (text) schools AdmLName1 (text) echools AdmEmail1 (text)
schools AdmFName2 (text) schools AdmLName2 (text) I schools
AdmEmai12 (text) schools AdmFNaame3 (text) schools AdmLName3 (text)
schools AdmEmail3 (text) schools LastUpdate (time);
[Primary Keyal: frpm : CDSCode I satacores cds I schools CDSCode;
Foreigg Keyal frpm CDSCode equals schools CDSCode satscores cds equals schools : CDSCode;
[detailed description of tables and columns]
Column description of Table "frpm" have the following descriptions:
Column "County Name" of Table " "frpm", means "County Code"
Column "Charter School (Y/N)" of Table frpm has value descriptions "0: N;1: Y" Column "IRC" Table frpm has value descriptions "Not useful"
Column "Enrollment (K-12)" of Table frpm has value descriptions "commonsense evidence:K-12 1st grade 12nd grade"
Column "Free Meal Count (K-12)" " of Table frpm has value descriptions commonsense evidence: Column "FRPM Count (K-12)" of Table "frpm", means "Free Reduced Price Meal Count (K-12)", has value descriptions "commonsense evidence :vidence:eligiblee FRPM rate FRPM Enrollment"
free rate Free Meal Count / Enrollment*
Column "Free Meal Count (Ages 5-17) of Table frpm has value descriptions commonsense evidence:eligible free rate Free Meal Count Enrollment* Column description of Table "satscores" have the following descriptions:
Column "cds" Table "satscores", means "California Department Schools" Column "rtype" of Table satscores has value descriptions "unuseful" Column "sname" of Table "satscores", means "school name"
Column "dname" of Table "satscores", means "district segment", district name, Column "cname" Table "satscores", means "county name"
"enrollment (1st-12nd grade)" Column "NumTstTakr" of Table "satscores", means "Number of Test Takers in this
Column "enroll12" of Table "satscores",
school", Number Test Takers, has value descriptions "number of teat takera in each achool"
Column "AvgScrRead" Table "satscores", means "average scores in Reading" Column " "AvgScrMath" of Table "satscores", means "average scores in Math" Column "AvgScrWrite" Column "NumGE1500" of Table "satscores", means "Number of Test Takers Whose Total SAT Scores Are Greater or Equal to 1500", has value descriptions Number of Test Takers Whose Total SAT Scores Are Greater Equal to 1500 commonsense evidence: Excellence Rate NumGE1500 / NunTstTakr"
Table "satscores", means "average scores in writing"
Column description of Table "schools" have the following descriptions:
Column "NCESDist" of Table "schools", means "This field represents the 7-digit
National Center for Educational Statistics (NCES) school district identification number. The first 2 digits identify the state and the last digits identify the school district. Combined, they make unique 7-digit ID for each school district.", National Center for Educational Statistics school district identification number.
Column "NCESSchool" " of Table "schools", means "This field represents the 5- digit NCES school identification number. The NCESSchool combined with the
57

---

Published in Transactions on Machine Learning Research (11/2024)
NCESDist form unique 12-digit ID for each school.", National Center for Educational Statistics school identification number, Column "StatusType" of Table "schools", of the district.", has value descriptions "Definitions of the valid status Active: The district is in operation and Closed: The district is not in Merged: Pending: The district has not opened for operation and instructional services yet, but plans to open within the next 912 months."
"This field identifies the status
types liated below:
providing instructional services.
operation
no longer providing instructional services.
district has combined with another district districts.
Column "County" of Table "schools", means "County name"
Column "StreetAbr" of Table "schools", means "The abbreviated street address of
the school, district, or administrative authoritys physical location.", street address, has value descriptions "The abbreviated street address of the school, district, or administrative authoritys physical location. Note Soze records (primarily records closed retired schools) may have data in this field."
Column "MailStreet"
Table schools value descriptions "The unabbreviated mailing address of the school, district, administrative authority. Note: 1) Some entities (primarily closed or retired schools) may not have data in this field; 2) Many active entities have not provided mailing street address. For your convenience we have filled the unpopulated MailStreet cells with Street data."
Column "MailStrAbr" of Table "schools", means "mailing street address", has value descriptions "the abbreviated mailing street address of the school, district, or administrative authority. Note: Many active entities have not provided mailing street address. For your convenience have filled the unpopulated MailStrAbr cells with StreetAbr data."
Column "MailCity" of Table "schools", means "mailing city", has value descriptions "The city associated with the mailing address of the school, district, or administrative authority. Note: Many entities have not provided a mailing address city. For your convenience we have filled the unpopulated MailCity cells with City data."
Column "MailZip" of Table "schools", means "mailing zip", has value descriptions "The zip code associated with the mailing address of the school, district, provided a mailing address zip code. For your convenience we have filled the unpopulated MailZip cells with Zip data."
administrative authority. Note: Many entities have not
Column "MailState" of Table "schools", means "mailing state", has value deacriptions "The state within the mailing address. For your convenience have filled the unpopulated MailState cells with State data."
Column "Ext" Table "schools", means "The phone number extension school, district, or administrative authority.", extension, Column "Website" of Table "schools", means "The website address of the school, district, administrative authority."
Column "OpenDate" of Table "schools", means "The date the school opened." Column "ClosedDate" of Table "schools", means "The date the school closed." Column "Charter" of Table "schools", means "This field identifies charter
school.", has value descriptions "The field is coded follous: 1 The The school is a charter 0 The school is not a charter" Column " "CharterNun" Table "schools", value descriptions "4-digit number assigned to a charter school." Column "FundingType" of Table "schools", funding type", has value descriptions "Values are as follovs: Not in Cs ( California School) funding model Locally funded Directly funded" Column "DOC" of Table "schools", means "District Ownership Code", has value descriptions "The District Ownership Gode (DOC) is the numeric code used identify the category of the Administrative Authority.
"The charter school number,", has
"Indicates the charter school
00 County
58

---

Published in Transactions on Machine Learning Research (11/2024)
Office of Education Benefit Charter
02 State Board of Education
03 Statewide
31 State Special Schools
34 Non Non-school
Location 52 Elementary School District
54 Unified School
District
56 High School District
98 Regional Occupational
evidence: Only the California Education Authority has been included in the non non-school location category." Column "DOCType" of Table "schools", means
Center/Progran
District Ounership Code Type the text description of the DOC category.", The District Ownership Code Type, has value descriptions "(See text values in DOC field description above)
Column "SOC" Table "schools", means "The School Ovnership Code a numeric
code used to identify the type of school.", School Ownership Code, has value descriptions "08 Preschool Schools (Public)
09 Special Education 11 Youth Authority Facilities (CEA)
13
Opportunity Schools
14 Juvenile Court Schools
15 Other County
District Programs
State Special Schools
60 Elementary
School (Public)
61 Elementary School in 1 School District (Public)
Intermediate Middle Schools (Public)
63 Alternative
Schools of Choice
64 Junior High Schools (Public)
65 K-12
Schools (Public)
High Schools (Public)
High Schools in
School District (Public)
68 Continuation High Schools
69
District Community Day Schools Regional Decupational Center/Proget Column "SOCType" of Table "schools", means "The School Ownership Code Type is the text description of the type of school.", ", School Ownership Code Type, Column "Ed0paCode" of Table "schools", means "The Education Option Code is a the type of education offered." ", Education Option
70 Adult Education Centers (ROC/P)
98
short text description Code, has value descriptions "ALTSOC Alternative School of Choice COMM County Community School
COMMDAY Community Day School OPP
CON
Continuation School
JUV Juvenile Court School
Opportunity School
YTH Youth Authority School
s8S State
Special School
SPEC Special Education School
TRAD Traditional
номноS Home and Hospital SPECON District Consortia Special Education School"
ROP Regional Occupational Program
Column "EdOpsName" of Table "schools", means "Educational Option Name", has value descriptions "The Educational Option Name is the long text description the type of education being offered." Column "EILCode" of Table "schools",
"The Educational Instruction Level Code is a short text description of the institution's type relative to the grade range served." Educational Instruction Level Code, has value deacriptions "A Adult
ELEM Elementary High School
ELEMHIGH Elementary- INTHIDJR Internediate/Middle/ UG Ungraded"
High Combination
Junior High
PS Preschool
Column "EILName" of Table "schools", means "The Educational Instruction Level Name is the long text description of the institutions type relative to the grade range served.", Educational Instruction Level Name,
Column "GSoffered" of Table "schools", means "The grade span offered is the lovest grade and the highest grade offered or supported by the school, district, administrative authority. This field might differ from the grade span served as reported in the most recent certified California Longitudinal Pupil Achievement (CALPADS) Fall data collection.", grade span offered, has value descriptions "For example XYZ School might display the folloving Aata:GSofferes PAdultGSaerved Column "GSserved" of Table "schools", means "It is the lovest grade and the highest grade of student enrollment reported in the certified CALPADS Fall data collection. Only K12 enrollment is reported through CALPADS. This field may differ from grade span offered." grade span served., has value descriptions "commonsense evidence: Only K12
K12"
59

---

Published in Transactions on Machine Learning Research (11/2024)
enrollment reported through CALPADS2. Note: Special programs independent study, alternative education, and special sducation schools will often exceed the typical grade вpan for schools of that type" Column "Virtual" of Table "schools", means "This field identifies the type of virtual instruction offered by the school. Virtual instruction instruction in which students and teachers are separated by time and/or location, interaction occurs
computers and/or telecommuications technologies.", has value descriptions "The field is coded as follows: F Exclusively Virtual The school has no physical building where students meet with each other with teachers, all instruction is virtual. V Primarily Virtual The school focuses on a systematic program virtual instruction but includes some physical meetings among students with teachers. с Primarily Classroom The school offers virtual courses but virtual instruction is not the primary means of instruction. N lot Virtual The school does not offer any virtual instruction. P Partial Virtual The school offers some, but instruction. Note: This value was retired and replaced with the Primarily Virtual and Primarily Classroom values beginning with the 201617 school year."
all, instruction through virtual
Column "Magnet" of Table "schools", means "This field identifies whether school is magnet school and/or provides magnet program.", has value descriptions "The field is coded as follows: magnet school and/or offers magnet program = Not Magnet The school is not a magnet school and/or does not offer a magnet program.commone commonsense : Preschools and adult education centers do not contain magnet school indicator."
Magnet The school
Column "Latitude" of Table "schools", degrees) between the location of the school, district, or administrative authority and the equator measured north south."
"The angular distance (expressed
Column "Longitude" of Table "schools", means "The angular distance (expressed degrees) between the location the school, district, or administrative authority and the prime meridian (Creenwich, England) measured from west to east."
Column "AdmFNamei" of Table "schools", means "administrator's 's first name", has value descriptions "The superintendents or principals first name. commonsense evidence: Only active and pending districts and schools will display administrator information, if applicable."
Column "AdmLNamei" of Table "schools", means "administrator's 's last name", has value descriptions "The superintendents or principals last name. commonsense evidence:0nly active and pending districts and achools will display administrator information, if applicable."
Column "AdmEmaill" Table "schools", means "administrator" email address", has value descriptions "The superintendents or principals email address. commonsense evidence:0nly active display administrator information, if applicable." Column "AdmFName2" Table schools Column " "AdmFName3" of Table schools has value descriptions "not useful" Column "AdmLName3" Table schools has value descriptions "not useful" Column "AdmEmail3" of Table schools has value descriptions "not useful" Column "LastUpdate" of Table schools has value descriptions "when is this record updated last time"
pending districts and schools will
value descriptions "SAME 1"
[Database values that related
questions]
The column "County Name in Table 'frpm has database values: Alameda
The column 'cname' in Table 'satscores" has database values: Alameda
The column 'County' in Table 'schools" has database values: Alaneda The column "City" in Table 'schools" has database values: Alameda
The column "MailCity' in Table 'schools has database values: Alameda
60

---

Published in Transactions on Machine Learning Research (11/2024)
The column 'GSoffered' in Table 'schools' has database values: K-12
The column 'SSserved' in Table 'schools" has database values: K-12
The column 'AdmFName1' in Table 'schools' has database values: Rae
The column 'AdmLName1" in Table 'schools" has database values: Free
[Additional Infol: Eligible free rate for K-12 FRPM Count (K-12) Enrollment (K-12)
[q]: What is the highest eligible free rate for K-12 students in the schools Alameda County?;
[SQL]:
Here is an example: Convert text to SQL:
A.11.2 Inferred column selection
See "[dctailed description of tables and columns]" in red for soft column selection.
Here is the test question to be anwered: Convert text to SQL:
[Schema (valuea)]: california_schoos frpm CDSCode Academic Year
County Code District Code School Code County Name District Name
School Name District Type School Type Educational Option Type NSLP
Provision Status Charter School (Y/N) Charter School Number Charter
Funding Type IRC Low Grade High Grade Enrollment (K-12) Free Meal
Count (K-12) Percent (%) Eligible Free (K-12) FRPM Count (K-12)
Percent (%) Eligible FRPM (X-12) Enrollment (Ages 5-17) Free Meal Count
(Ages 5-17) Percent (X) Eligible Free (Ages 5-17) FRPM Count (Ages
5-17) Percent (%) Eligible FRPM (Ages 5-17) 2013-14 CALPADS Fall
Certification Status I satscores cds rtype sname dname cname
enroll12 MunIstTakr AvgScrRead AvgSorMath ArgScrWrite NumGE1500
schools CDSCode NCESDist NCESSchool StatusType County District
School Street StreetAbr City Zip State MailStreet
MailStrAbr MailCity MailZip MailState Phone Ext Website
OpenDate ClosedDate Charter CharterNum FundingType DOC DOCType
SOC SOCType Ed0psCode EdOpsName EILCode EILName GSoffered
GSserved Virtual Magnet Latitude Longitude AdmFName1 AdmLName1
AdmEmail1 AdmFName2 AdmLName2 AdmEmai12 AdmFName3 AdmLName3
AdmEmail3 LastUpdate;
[Column names (type)ll: frpm CDSCode (text) frpm Academic Year (text) frpm County Code (text) frpm District Code (number) I frpm School Code (text) frpm County Name (text) frpm District Name (text) frpm School Name (text) frpm District Type (text) frpm School Type (text frpm Educational Option Type (text) I frpm : NSLP Provision Status ( text) frp= Charter School (Y/N) (number) frpm Charter School Number (text) frpm : Charter Funding Type (text) frpm IRC (number) I frpm Low Grade (text) frpm : High Grade (text) frpm Enrollment (K-12)
number) I frpm Free Meal Count (K-12) (number) I frpm Percent (%)
gligible Free (K-12) (number) frpm : FRPM Count (K-12) (number) frpm Percent (%) Eligible FRPM (K-12) (number) I frpm Enrollment (Ages 5-17) ( number) frpm : Free Meal Count (Ages 5-17) (number) frpm Percent (1)
Eligible Free (Ages 5-17) (number) frpm FRPM Count (Ages 5-17) (number)
frpm Percent (2) Eligible FRPM (Ages 5-17) (number) frpm 2013-14
CALPADS Fall 1 Certification Status (number) satscores eds (text)
satscores : rtype (text) I satscores : sname (text) satscores dname (
text) aatacores cname (text) satscores enroll12 (number) satacorea MunTstTakr (number) | satacores AvgScrRead (number) |
satacores AvgScrMath (number) | satscores AvgScrWrite (number)
satscores NumGE1500 (number) | schools CDSCode (text) schools
61

---

Published in Transactions on Machine Learning Research (11/2024)
NCESDist (text) schools : NCESSchool (text) schools StatusType (text)
schools County (text) I schools District (text) | schools School text) schools Street (text) schools StreetAbr (text) schools City (text) schools Zip (text) schools State (text) I schools MailStreet (text) schools MailStrAbr (text) schools MailCity (text)
schools MailZip (text) schools MailState (text) schools Phone
(text) schools Ext (text) schools Website (text) schools
OpenDate (time) schools ClosedDate (time) schools Charter (number)
schools Charterlum (text) schools FundingType (text) | schools
DOC (text) schools DOCType (text) | schools SOC (text) | schools SOcType (text) I schools : EdOpsCode (text) schools EdOpsName (text)
schools EILCode (text) I schools EILName (text) schools GSoffered ( text) schools GSserved (text) schools Virtual (text) | schools Magnet (number) schools Latitude (number) schools Longitude (number schools AdmFName1 (text) schools AdmLName1 (text) schools
AdmEmail1 (text) schools AdmFName2 (text) achools AdmLName2 (text)
schools AdmEmai12 (text) schools AdmFName3 (text) schools
AdmLName3 (text) schools AdmEmail3 (text) schools LastUpdate (time)
[Primary Keys]: frpm CDSCode satscores cds schools CDSCode; [Foreign Keys]: frpm CDSCode equals schools CDSCode satscores cds equals schools CDSCode;
[detailed description of tables and columns]
Column description of Table "frpm" have the following descriptions:
Column "County Name" of Table "frp=", means "County Code
Column "Enrollment (K-12)" of Table frpm has value descriptions "commonsense
evidence:K-122 1st grade 12nd grade"
Column "FRPM Count (K-12)" of Table "frpm", means "Free or Reduced Price Meal Count (K-12)", has value descriptions "commonsense evidence eligible FRPM rate FRPM Enrollment"
[Database values that related with questions]
The column 'County Name in Table 'frpm' has database values: Alameda
The column 'cname' in Table 'satscores" has database values: Alameda
The column 'County' in Table 'schools' has database values: Alameda
The column 'City' in Table 'schools' has database values: Alameda
The column 'MailCity' in Table 'schools" has database values: Alameda
The column 'Gooffered' in Table 'schools' has database values: K-12
The column 'GSserved' in Table 'schools' has database values: X-12
The column 'AdmFNamee in Table 'schools' has database values: Rae
The column 'AdmLName1" in Table 'schools'
database values: Free
[Additional Infol; Eligible free rate for K-12 'FRPK Count (K-12) Enrollment (K-12) [Q]: What the highest eligible free rate for K-12 students the schools in Alameda County?;
[SQL]
62


Reviews:
[{"review_id": "mbPzEHRMG0", "reviewer": "Reviewer_tt9k", "summary": "", "questions": "", "limitations": "", "rating": null, "confidence": null, "soundness": null, "presentation": null, "contribution": null, "strengths": "", "weaknesses": "", "quality": null, "clarity": null, "significance": null, "originality": null, "strengths_and_weaknesses": "Strengths\n1. A performant text-to-SQL model\n2. Ablation on various components of the pipeline, showcasing the role of each component.\n3. Experiments and discussion around practical implications of several choices of implementations for components. \n\nWeaknesses\n1. Reliant on expensive, large-scale LLMs as opposed to efficient, specialised small LLMs (e.g, CodeS), especially when needing to fine-tune for each new set of databases.\n2. No evaluation on test data on the leaderboards. The test splits being hosted by benchmarks' creators is not a reason against evaluating on them.\n3. No evaluation on execution efficiency score for BIRD.\n4. Prompt design doesn't generalise across different datasets (Spider vs BIRD) and might not generalise across LLMs.\n5. Synthetic data augmentation relies on existing annotated samples. It's not clear how to extend to cases without manual annotations.\n6. Comparison against SOTA baseline uses both Spider + BIRD data for fine-tuning whereas baselines don't.\n7. Execution-based consistency decoding and test-time refinement via execution-based selection are costly as they require multiple inferences over multiple training paradigms and multiple executions on the database before selecting the correct SQL.", "was_revised": false, "final_justification": "", "rebuttal": "", "claims_and_evidence": "Yes", "methods_and_evaluation_criteria": "", "theoretical_claims": "", "experimental_designs_or_analyses": "", "relation_to_broader_scientific_literature": "", "essential_references_not_discussed": "", "other_strengths_and_weaknesses": "", "other_comments_or_suggestions": "", "questions_for_authors": "", "summary_of_paper": "", "summary_of_recommendation": "", "technical_quality": "", "clarity_of_presentation": "", "potential_impact": "", "robotics_focus": "", "quality_of_limitations": "", "questions_for_rebuttal": "", "reasons_to_accept": "", "reasons_to_reject": "", "questions_to_authors": "", "paper_topic_and_main_contributions": "", "excitement": null, "reproducibility": null, "ethical_concerns": "", "main_review": "", "summary_of_the_paper": "", "summary_of_the_review": "", "strength_and_weaknesses": "", "limitations_and_societal_impact": "", "review_text": "", "summary_of_contributions": "The paper proposes a framework for fine-tuning and prompting the PaLM LLM for a text-to-SQL task. The framework is composed of multiple components such as synthetic data augmentation, fine-tuning, prompt design, execution-based consistency decoding, table and column selection, query-specific database content integration, and a method for selecting the appropriate training paradigm at test time. The framework is evaluated on the dev sets of two prominent Text-to-SQL benchmarks, Spider (and its variants) and BIRD, and demonstrated competitive performance. In addition, the paper performs ablation studies to understand the impact each component has on the overall text-to-SQL performance.", "claims_and_evidence_yn": "Yes", "claims_explanation": "", "audience_yn": "Yes", "audience_explanation": "", "requested_changes": "1. Various typos throughout the document. Needs proof reading.\n2. Table 7 has results for Gecko as 15.84% and Table 8 suggests this might be for LoRA fine-tuning and that the results for Gecko in Table 7 should have been 33.96?\n3. It's not clear what baseline is used in Table 17 and 18.", "broader_impact_concerns": "The proposed framework is built around a closed PaLM model and will be difficult to reproduce.", "additional_comments": "", "extra_scores": {}, "extra_text": {}}, {"review_id": "ZIgdWClkS8", "reviewer": "Reviewer_AbU9", "summary": "", "questions": "", "limitations": "", "rating": null, "confidence": null, "soundness": null, "presentation": null, "contribution": null, "strengths": "", "weaknesses": "", "quality": null, "clarity": null, "significance": null, "originality": null, "strengths_and_weaknesses": "Strengths\n\n* This paper does a _major_ study in building production ready text-to-SQL systems with large LLMs and contain important insights that can be used by several practitioners\n* Although, the paper is too long, its straightforward to follow and is clearly written\n\nWeakness:\n\n* Not exactly a weakness, but the paper does not bring in a new technical novelty but instead chooses to test several configurations of building a text-to-sql model\n* In the fine-tuning section (Sec 6), I did not see any details of Lora fine-tuning. Please add details about the exact Lora experiments.", "was_revised": false, "final_justification": "", "rebuttal": "", "claims_and_evidence": "Yes", "methods_and_evaluation_criteria": "", "theoretical_claims": "", "experimental_designs_or_analyses": "", "relation_to_broader_scientific_literature": "", "essential_references_not_discussed": "", "other_strengths_and_weaknesses": "", "other_comments_or_suggestions": "", "questions_for_authors": "", "summary_of_paper": "", "summary_of_recommendation": "", "technical_quality": "", "clarity_of_presentation": "", "potential_impact": "", "robotics_focus": "", "quality_of_limitations": "", "questions_for_rebuttal": "", "reasons_to_accept": "", "reasons_to_reject": "", "questions_to_authors": "", "paper_topic_and_main_contributions": "", "excitement": null, "reproducibility": null, "ethical_concerns": "", "main_review": "", "summary_of_the_paper": "", "summary_of_the_review": "", "strength_and_weaknesses": "", "limitations_and_societal_impact": "", "review_text": "", "summary_of_contributions": "This paper does a major study into various aspects of building a practical and usable Text-to-SQL system using large LLMs and delves deeply into various critical aspects that a researcher/practitioner has to consider—learning (to ICL or to fine-tune), how does parameter-efficent fine-tuning help (to Lora or not?), how well do models generalize after being fine-tuned on smaller training sets, how to handle ambiguousness of real world queries, complex SQL queries requiring multi-step reasoning, whether to select specific columns relevant for the query, etc. The paper goes into great detail about every step of the system.\n\nA few notable results/findings from the paper - (a) Fine-tuning is more effective than prompting for more complex Text-to-SQL tasks, (b) Full fine-tuning is better than Lora (PEFT) even for few training examples, (c) column/table selection is important, (d) synthetic training data helps, but complex examples are more helpful, (e) given that SQL queries are executable, this is advantageous in program aided column selection as well as execution-based test-time selection.", "claims_and_evidence_yn": "Yes", "claims_explanation": "", "audience_yn": "Yes", "audience_explanation": "", "requested_changes": "* Please add details about Lora fine-tuning\n* In equation 14, It is not clear how the similarity score is computed. Is it also computed by the LLM?", "broader_impact_concerns": "None", "additional_comments": "", "extra_scores": {}, "extra_text": {}}, {"review_id": "VCUaY92PuY", "reviewer": "Reviewer_wZE4", "summary": "", "questions": "", "limitations": "", "rating": null, "confidence": null, "soundness": null, "presentation": null, "contribution": null, "strengths": "", "weaknesses": "", "quality": null, "clarity": null, "significance": null, "originality": null, "strengths_and_weaknesses": "## Strengths\nS1. The paper extensively experiments with the components of the SQL-PaLM framework on Text-to-SQL benchmarks (Spider and BIRD). Through ablation studies, the authors analyze the impact of factors like data diversity, input representations, column selection approaches, etc. The results offer insights into the proposed methods' strengths and limitations.\n\nS2.  The paper addresses challenges in applying Text-to-SQL to real-world scenarios, including large database schemas, ambiguous questions, and complex SQL queries. Tackling these practical issues enhances the potential real-world impact of the work. The authors identify and report errors present in the benchmarks mentioned earlier.\n\n## Weaknesses\nW1. It appears that this work lacks a timely discussion beyond few-shot prompting and instruction fine-tuning of LLMs. Recent solutions based on LLM-agents and RAG models have achieved state-of-the-art performance on official leaderboards, such as [1].\n\n[1] MAC-SQL: A Multi-Agent Collaborative Framework for Text-to-SQL\n\nW2. All scores are reported based on the dev set. It would be more credible to evaluate the proposed methods on the test set.\n\nW3. While I appreciate the effort put into this comprehensive work, it is worth noting that several of the components have already been explored in prior research and have not been sufficiently compared with in this study. For example, [2-7] are all works on synthesizing data for Text-to-SQL.\n\n[2] GraPPa: Grammar-Augmented Pre-Training for Table Semantic Parsing\n\n[3] TaBERT: Pretraining for Joint Understanding of Textual and Tabular Data\n\n[4] Importance of Synthesizing High-quality Data for Text-to-SQL Parsing\n\n[5] Learning to Synthesize Data for Semantic Parsing\n\n[6] Data Augmentation with Hierarchical SQL-to-Question Generation for Cross-domain Text-to-SQL Parsing\n\n[7] CodeS: Towards Building Open-source Language Models for Text-to-SQL", "was_revised": false, "final_justification": "", "rebuttal": "", "claims_and_evidence": "Yes", "methods_and_evaluation_criteria": "", "theoretical_claims": "", "experimental_designs_or_analyses": "", "relation_to_broader_scientific_literature": "", "essential_references_not_discussed": "", "other_strengths_and_weaknesses": "", "other_comments_or_suggestions": "", "questions_for_authors": "", "summary_of_paper": "", "summary_of_recommendation": "", "technical_quality": "", "clarity_of_presentation": "", "potential_impact": "", "robotics_focus": "", "quality_of_limitations": "", "questions_for_rebuttal": "", "reasons_to_accept": "", "reasons_to_reject": "", "questions_to_authors": "", "paper_topic_and_main_contributions": "", "excitement": null, "reproducibility": null, "ethical_concerns": "", "main_review": "", "summary_of_the_paper": "", "summary_of_the_review": "", "strength_and_weaknesses": "", "limitations_and_societal_impact": "", "review_text": "", "summary_of_contributions": "This paper presents SQL-PaLM, a comprehensive framework that enables LLMs to handle Text-to-SQL tasks. It explores two learning paradigms, few-shot prompting and instruction fine-tuning of LLMs, to assess their performance and effectiveness. Additionally, the paper analyzes the impact of factors such as input representation, database size, data diversity, coverage, and augmentation strategies, such as using LLMs to generate additional data. To evaluate the proposed SQL-PaLM framework, extensive experiments and analyses are conducted on public Text-to-SQL benchmarks like Spider and BIRD, aiming to gain insights into the framework's strengths, weaknesses, and the key factors influencing its performance.", "claims_and_evidence_yn": "Yes", "claims_explanation": "", "audience_yn": "Yes", "audience_explanation": "", "requested_changes": "See weaknesses", "broader_impact_concerns": "NA", "additional_comments": "", "extra_scores": {}, "extra_text": {}}]

Author rebuttal:

Meta-review: All reviewers found the work interesting and well-executed. The reviewers made suggestions to include additional metrics such as VES, a discussion on LoRA finetuning for their framework, and comparisons with additional prior works, which were addressed by the authors during the rebuttal and should be included in the final revision.
Decision: Accept (poster)