Retrieval and Augmentation of Domain Knowledge for Text-to-SQL Semantic Parsing
Extracted source text · arXiv 2510.02394
\documentclass[conference]{IEEEtran}
%\documentclass[peerreview, 10pt]{IEEEtran}
\IEEEoverridecommandlockouts
% The preceding line is only needed to identify funding in the first footnote. If that is unneeded, please comment it out.
\usepackage{cite}
\usepackage{amsmath,amssymb,amsfonts}
\usepackage{algorithmic}
\usepackage{graphicx}
\usepackage{multirow}
\usepackage{times}
\usepackage{latexsym}
\usepackage{url}
\usepackage{graphicx}
\usepackage[table,xcdraw]{xcolor}
\usepackage{colortbl}
\usepackage[normalem]{ulem}
\usepackage{amsmath}
%\useunder{\uline}{\ul}{}
\usepackage{ulem}
\usepackage{soul}
\usepackage{makecell}
\usepackage{multirow}
\usepackage{pgfplots}
\newcommand{\dNL}{d_\text{NL}}
\usepackage{comment}
\RequirePackage[inline,shortlabels]{enumitem}
\usepackage{textcomp}
\usepackage{xcolor}
\def\BibTeX{{\rm B\kern-.05em{\sc i\kern-.025em b}\kern-.08em
T\kern-.1667em\lower.7ex\hbox{E}\kern-.125emX}}
\pgfplotsset{compat=1.18}
\begin{document}
\title{Retrieval and Augmentation of Domain Knowledge for Text-to-SQL Semantic Parsing\\
% {\footnotesize \textperscript{*}Note: Sub-titles are not captured in Xplore and
% should not be used}
% \thanks{Identify applicable funding agency here. If none, delete this.}
}
% \author{\IEEEauthorblockN{Anonymous Authors}}
\author{
\IEEEauthorblockN{Manasi Patwardhan}
\IEEEauthorblockA{
\textit{TCS Research}\\
Pune, India \\
manasi.patwardhan@tcs.com}
\hspace{15em}
\and
\IEEEauthorblockN{Ayush Agarwal}
\IEEEauthorblockA{
\textit{TCS Research}\\
Pune, India \\
ayush.agarwal10@tcs.com}
\hspace{16em}
\and
\IEEEauthorblockN{Shabbirhussain Bhaisaheb}
\IEEEauthorblockA{
\textit{TCS Research}\\
Pune, India \\
shabbirhussainb@gmail.com}
\hspace{17em}
\and
\IEEEauthorblockN{Aseem Arora}
\IEEEauthorblockA{
\textit{TCS Research}\\
Pune, India \\
aseem\_1911mc02@iitp.ac.in}
\hspace{16em}
\and
\IEEEauthorblockN{Lovekesh Vig}
\IEEEauthorblockA{
\textit{TCS Research}\\
Delhi, India \\
lovekesh.vig@tcs.com}
\hspace{16em}
\and
\IEEEauthorblockN{Sunita Sarawagi}
\IEEEauthorblockA{
\textit{IIT Bombay}\\
Mumbai, India \\
sunita@iitb.ac.in}
\hspace{16em}
}
\maketitle
%\IEEEpeerreviewmaketitle
\begin{figure*}[!h]
\begin{center}
\includegraphics[width=\textwidth]%%{latex/TP_examples.drawio.png}
{Updated_DB_Process_V3.pdf}
\caption{ Framework for Enterprise NL-SQL Semantic Parsing. Mentions of same entities across DB Schema, NL and Structured Domain Statements, NL Query and Ground Truth SQL, are coded with the same color.
}
\label{fig:example}
\end{center}
\end{figure*}
\begin{abstract}
The performance of Large Language Models (LLMs) for translating Natural Language (NL) queries into SQL varies significantly across databases (DBs). NL queries are often expressed using a domain specific vocabulary, and mapping these to the correct SQL requires an understanding of the embedded domain expressions, their relationship to the DB schema structure. Existing benchmarks rely on unrealistic, ad-hoc query specific textual hints for expressing domain knowledge. In this paper, we propose a systematic framework for associating structured domain statements at the database level. We present retrieval of relevant structured domain statements given a user query using sub-string level match. We evaluate on eleven realistic DB schemas covering diverse domains across five open-source and proprietary LLMs and demonstrate that (1) DB level structured domain statements are more practical and accurate than existing ad-hoc query specific textual domain statements, and (2) Our sub-string match based retrieval of relevant domain statements provides significantly higher accuracy than other retrieval approaches.
\end{abstract}
\begin{IEEEkeywords}
Knowledge Augmented Text to SQL, Efficient Knowledge Retrieval,
Question Answering on Databases\end{IEEEkeywords}
\section{Introduction}
%%
\begin{table*}[t]
\centering
\caption{Four Categories of Domain Statements illustrating types of domain understanding required for DBs.}
\footnotesize
\resizebox{\textwidth}{!}{
\begin{tabular}{|l|l|l|l|}
\hline
\textbf{Category} &
\textbf{Database} &
\textbf{Natural Language Domain Statement} &
\textbf{Structured Domain Statement} \\
\hline
\multirow{2}{*}{
\begin{tabular}[c]{@{}l@{}}
\textbf{Description } \\
\textbf{of Cryptic} \\
\textbf{Schema Names }
\end{tabular}
}
&
Financial
&
\begin{tabular}[c]{@{}l@{}}
A12 indicates the unemployment \\
ratio for year 1995.
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
`unemployment ratio of year 1995' \\
refers to district.A12
\end{tabular}
\\
\cline{2-4}
&
Formula 1
&
\begin{tabular}[c]{@{}l@{}}
Location coordinates of the circuit of the race \\
are given be a pair of latitude and longitude
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
`location coordinates of the circuit of the race'\\
refers to circuits.lat, circuits.lng
\end{tabular}
\\
%\cline{2-4}
%&
%\begin{tabular}[c]{@{}l@{}}
%Thrombosis \\
%Prediction
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%ALB denotes the Albumin Level\\
%in the laboratory experiments
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%`albumin level'
%refers to Laboratory.ALB
%\end{tabular}
%\\
\hline
\multirow{2}{*}{
\begin{tabular}[c]{@{}l@{}}
\textbf{Description} \\
\textbf{Categorical} \\
\textbf{Column Values}
\end{tabular}
}
&
\begin{tabular}[c]{@{}l@{}}
Calif. Schools
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
Amador is a school county
in California
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
`Amador' refers to
schools.County = `Amador'
\end{tabular}
\\
%\cline{2-4}
%&
%\begin{tabular}[c]{@{}l@{}}
%Codebase\\
%Community
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%`Eliciting prior from experts' is a type of \\
%the title of a post in coding community
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%the post `eliciting prior from experts' refers to \\
%posts.Title = `Eliciting prior from experts'
%\end{tabular}
%\\
\cline{2-4}
&
\begin{tabular}[c]{@{}l@{}}
Toxicology
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
Non-carcinogenic molecules are labelled as ‘-’
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
`non-carcinogenic molecules' refers to \\molecule.label = ‘-’
\end{tabular}
\\
%\cline{2-4}
%&
%\begin{tabular}[c]{@{}l@{}}
%Student\\
%Club
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%Postcards, Posters is a type of\\
%expense description made by students
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%`Post Cards, Posters' refers to expense.\\
%expense\_description = `Post Cards, Posters'
%\end{tabular}
%\\
\hline
\multirow{3}{*}{
\begin{tabular}[c]{@{}l@{}}
\textbf{Handling } \\
\textbf{Format} \\
\textbf{Mismatches}
\end{tabular}
}
&
\begin{tabular}[c]{@{}l@{}}
Debit Card\\
Specializing
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
September 2013 is represented as\\
the date format year month = `201309'
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
`September 1000' refers to
yearmonth.Date \\= `100009'
\end{tabular}
\\
\cline{2-4}
&
\begin{tabular}[c]{@{}l@{}}
Codebase\\
Community
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
Last accessed by user after the date 2014/9/1 \\
means the Last Access Date $>$`2014-09-01'
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
`last accessed after date 1000/10/10' refers to \\ date(users.LastAccessDate) $>$`1000-10-10'
\end{tabular}
\\
%\cline{2-4}
%&
%\begin{tabular}[c]{@{}l@{}}
%Student \\
%Club
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%Events conducted between November 2019 \\
%\& March 2020 suggests range of event\_date \\
%BETWEEN `2019-01-11' and `2020-03-31'
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%`Events conducted from November 1000 to March \\1000'
%refers to date(SUBSTR(event.event\_date, \\1, 10))
%BETWEEN `1000-01-11' AND `1000-03-31'
%\end{tabular}
%\\
\hline
\multirow{2}{*}{
\begin{tabular}[c]{@{}l@{}}
\textbf{Converting} \\
\textbf{NL Expression}\\
\textbf{to Formula} % 21
\end{tabular}
}
&
\begin{tabular}[c]{@{}l@{}}
European\\
Football2
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
Highest potential score is an attribute player \\
calculated by taking taking maximum of potential
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
`highest potential score' refers to ORDER \\
BY Player\_Attributes.potential DESC LIMIT 1
\end{tabular}
\\
\cline{2-4}
&
\begin{tabular}[c]{@{}l@{}}
Thrombosis \\
Prediction
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
Normal level of complement 3 \\
suggests value of C3 $>$ 35
\end{tabular}
&
\begin{tabular}[c]{@{}l@{}}
‘normal level of complement 3’ \\
refers to Laboratory.C3 $>$ 35
\end{tabular}
\\
%\cline{2-4}
%&
%\begin{tabular}[c]{@{}l@{}}
%California \\
%Schools
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%Eligible free rate for K-12 students is\\
%the ratio of `Free or Reduced-Price Meals \\
%Count (K-12)' to `Enrollment (K-12)'
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%`Eligible free rate for K-12 students' \\
%refers to frpm.`FRPM Count (K-12)' /\\
%frpm.`Enrollment (K-12)'
%\end{tabular}
%\\
%\cline{2-4}
%&
%\begin{tabular}[c]{@{}l@{}}
%Toxicology
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%Percentage of carbon is calculated by \\
%DIVIDE(COUNT(carbon atom elements) *100,\\
%COUNT(atom ids))
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%`percentage of carbon' refers to CAST(COUNT\\
%(DISTINCT CASE WHEN atom.element = `c' \\
%THEN atom.atom\_id ELSE NULL END) AS REAL)\\
%* 100 / COUNT(DISTINCT atom.atom_id)
%\end{tabular}
%\\
%\cline{2-4}
%&
%\begin{tabular}[c]{@{}l@{}}
%Financial
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%Difference of highest average \\
%salary and lowest average salary \\
%indicates the gap between them
%\end{tabular}
%&
%\begin{tabular}[c]{@{}l@{}}
%`Gap between the highest average salary and\\
%the lowest average salary' refers to \\
%MAX(account.A11) - MIN(account.A11)
%\end{tabular}
%\\
\hline
\end{tabular}
}
\label{tab:Examples}
\end{table*}
%problem motivation
The impressive natural language understanding and code generation capabilities of modern LLMs has led to significantly improved performance on NL-SQL semantic parsing \cite{Liu2024ASO,Li2024TheDO}. %\footnote{\url{https://yale-lily.github.io/spider}, \url{https://bird-bench.github.io/}}
However, their accuracy %while parsing an NL query to SQL
varies widely with the database queried \cite{Lan2023UNITEAU}. DBs in WikiSQL\cite{Zhong2017Seq2SQLGS} or Spider\cite{yu2018spider} contain semantically meaningful table/column names and cell values making it easier for LLMs to accurately link domain expressions in the NL query with the DB schema/cell elements. For more challenging DBs with semantically meaningless entities such as the DBs in KaggleDBQA\cite{lee-2021-kaggle-dbqa} or XSP\cite{suhr-etal-2020-exploring}, average performance is significantly worse\footnote{KaggleDBQA: \url{https://paperswithcode.com/sota/text-to-sql-on-kaggledbqa}}. This variation in performance often stems from the domain-specific vocabulary used to frame the NL queries and providing LLMs with additional context information in the form of relevant Domain Statements (DSs) may help address this problem.
The large performance gap on the recent BirdSQL benchmark \cite{li2024can} between approaches with and without the use of NL query-specific oracle domain knowledge\footnote{\label{fn:bird}BirdSQL: \url{https://bird-bench.github.io}} indicates an indispensable need for domain knowledge augmentation for NL query resolution. Mapping the NL queries to their correct SQL requires an understanding of (i) the embedded domain expressions, and (ii) their relationship to the schema elements and structure of the DB. BirdSQL fails to suitably capture this dichotomy, and relies on unrealistic, ad-hoc query-specific textual hints for expressing domain knowledge. In this paper, we propose a systematic framework for soliciting DSs at the DB level, as opposed to the NL query level, in a format that optimizes LLM performance on downstream real-world enterprise semantic parsing.
Figure \ref{fig:example} presents the outline of the framework. Over the life-cycle of an enterprise DB, DSs are solicited from domain experts, such as bankers and doctors, incrementally as and when they identify domain expressions for which additional information is needed (\S\ref{sec:sol}). The DSs are expected to be in NL form $d_{NL}$, as domain experts may not be familiar with the DB structure or SQL, but would likely be adept at providing the information required to understand domain expressions in NL form. % Considering the $\sim$10K token length of the set-of DSs solicited for one of the DBs (Card Games) in BirdSQL, consisting of 6 tables and 168 columns (detailed in Section \ref{sec:dataset}),
For larger enterprise DBs, as the DSs get accumulated, they may be too numerous for LLMs with smaller context to accommodate for LLM driven NL-to-SQL semantic parsing. Also, including irrelevant statements in the context can confuse the LLM \cite{Shi2023LargeLM}. Thus, there is a need to retrieve relevant DSs for the NL query. Aiming to facilitate the retrieval and downstream semantic parsing, these $d_{NL}$, are to be translated into a unified format that lends itself to optimized retrieval and LLM driven semantic parsing. For this, we define our own structured format for domain expressions ($d$) comprising of NL expressions mapped to SQL code snippets. Whenever a $d_{NL}$, is received from the domain expert, it is translated to $d$ via LLM based few shot in-context learning and any errors in the generated $d$ may optionally be corrected by the Database Administrator (DBA) (\S\ref{sec:tras}). The end user application then retrieves the appropriate structured DSs for the NL query (\S\ref{sec:ret}). Thus, the process of DS structuring is automated and is executed when domain repositories get updated. Also, the retrieval of DSs is automated and is triggered when an end-user poses a query on the DB.%, to retrieve relevant set-of $d$ from the current domain repository.
Figure \ref{fig:example} illustrates example %NL-queries posed on a
NL DSs $d_{NL}$, solicited for a `Thrombosis Prediction' DB %and the relevant DSs
. They provide information about DB column (2,9,10), cell values (3,4,6,8,11) and derived entities (1,5,7). As observed, each NL-query ($t$) may require multiple DSs to parse (DS 5, 6 for query 1 and 1, 3, 9 for query 2) and each relevant $d_{NL}$, has a semantic match with only a partial NL-query. %Our initial experiments indicate that decomposing the NL query and using the individual components for retrieving relevant DSs yields superior results when compared to the retrieval performed by the original non-decomposed query (Elaborated in the Appendix \ref{sec:ques_decomp}, Table \ref{tab:question_decompose}). Taking a cue from this result, and
Noting that a $d_{NL}$, relevant to a query should act as a knowledge `link' between the NL query and its SQL counterpart, we design structured DSs ($d$) with two components. The `text' component helps the LLM interpret the NL query, and the `SQL snippet' provides hints for generating the desired SQL query. For example, for the structured DS 2 in Figure \ref{fig:example}, \textit{Patient came to the hospital first time after year 1992} depicts the `text' and \textit{$STRFTIME$(`$\%Y$', Patient.`First Date') $>$ 1992} depicts the `SQL snippet', with both parts connected by the `refers to' phrase. %For retrieval of statements only the `text' component is utilized.
We further define a strategy for semantic matching of query sub-strings with the `text' component of $d$ to rank and retrieve top-K DSs. Along with the DB schema, these retrieved DSs are included in the LLM context for SQL generation.
%We simulate the real-life scenario of necessitating NL DSs %$d_{NL}$
%to understand NL queries and link them to relevant DB entities,
%using
Current State-of-the-art NL-to-SQL approaches \cite{Gao2024APO, Pourreza2024CHASESQLMR, li2024codes, pourreza2024dts} for BirdSQL, utilize query specific oracle DSs provided in the dataset. Soliciting DSs from domain experts for each new query posed by the end-user on the DB in real-time is unrealistic. Hence, we release a more realistic version of this dataset where the DSs are solicited at the DB schema level, structured with clearly demarcated NL domain expression and SQL snippets (\S\ref{sec:dataset}). For each query posed on the DBs of the BirdSQL Dev set, we retrieve relevant structured DSs from the DB level repository using our retrieval technique. We demonstrate that, by augmenting these retrieved structured DSs in the LLM context we obtain higher accuracy for the downstream SQL generation task, as opposed to augmenting the context with query specific oracle DSs available in the original BirdSQL.
%\footnotemark[\ref{fn:bird}].
%To simulate realistic scenarios without query specific DSs, we randomly split the queries in the DBs of the Dev Set of BirdSQL %based on their compositions into IN and OUT splits. We assume the availability of the DSs designed only for the IN split queries together to form the repository, and discard DSs for the OUT split queries. We use our approach, to translate these DSs to the structured form mentioned above.
Our main contributions are:
\begin{itemize}
\item We outline a framework for practical NL-to-SQL for enterprise DBs requiring domain knowledge for NL query understanding. As the part of the framework, we define a new structure for DS Structuring that helps to link an NL query to its equivalent SQL (\S\ref{sec:tras}).
\item We extend the BirdSQL Dev set to benchmark solutions utilising query-agnostic DSs for NL-SQL task (\S\ref{sec:dataset}).
\item We devise a technique to retrieve structured DSs relevant to an NL query, based on a semantic match of query sub-strings and the textual `link' of structured DSs (\S\ref{sec:ret}).
\item We perform exhaustive experiments to evaluate our pipeline demonstrating the value of different components including: (i) DS inclusion, (ii) structuring of NL DSs (iii) DBA correction and (iv) sub-string based retrieval, for eventual SQL generation (\S\ref{sec:results}). Our approach outperforms approaches with none or all DSs as well as retrieved DSs with other baselines, across LLMs, highlighting the impact of the robust retrieval mechanisms.
\end{itemize}
\vspace{-1mm}
\begin{figure*}[!h]
\begin{center}
\includegraphics[width=\textwidth]%%{latex/TP_examples.drawio.png}
{retriever_final6.pdf}
\caption{ Illustrative Example for Sub-String based Ranking (SbR) Mechanism
}
\label{fig:example1}
\end{center}
\end{figure*}
%TDS Prompt
\begin{table}[!t]
\centering
\small
%\sffamily
\caption{LLM Prompt for Domain Statements translation}
\label{tab:TDSprompt}
\resizebox{\columnwidth}{!}{
\begin{tabular}{l}
\hline
\textbf{System Prompt:} \\
\hline
\begin{tabular}{l} %[c]{@{}l@{}}
You are a database administrator. You have been given a few examples of \\domain statement and
their corresponding templatized DSs. The DSs are \\ for the database $<$DB name$>$ whose schema is provided below:\\
CREATE TABLE Patient ( \\
ID INTEGER PRIMARY KEY, -- identification of the patient;\\
SEX TEXT , -- Sex; \\
Birthday DATE, --Format Year-Month-Date;\\
% Description DATE , -- the first date when a patient data\\ \hspace{1cm} was recorded;\\
% `First Date' DATE , -- the date when a patient came to the hospital;\\
% Admission TEXT , -- patient was admitted to the hospital (+) \\ \hspace{1cm} or followed at the outpatient clinic (-);\\
Diagnosis TEXT -- disease names;)
...\\
\hline
\end{tabular} \\
\textbf{ User Prompt:} \\ \hline
\begin{tabular}{l} %[c]{@{}l@{}}
For the given domain statement, you need to create a templatized statement.\\
Provide the statement in one sentence only. Generate the templatized \\
domain statement in the following form:`Relevant text component' refers \\
to `corresponding SQL syntax' for each sample.\\
\#\#\# Few Shot Exemplars\\
Example:\\
Domain Statement: female is also represented as `F'\\
Templatized Domain Statement:`female' refers to Patient.SEX = `F'\\
Example:\\
Domain Statement: patient born between Year 1930 to 1940 represents \\Birthday BETWEEN the dates `1930-12-31' AND `1940-01-01'\\
Templatized Domain Statement: `patient born between year 100 and 100' \\ refers to STRFTIME(`\%Y', Patient.Birthday) BETWEEN `100' AND `100'\\
% ...\\
% Example:\\
% Domain Statement: the youngest patient can be determined by \\taking MAX(YEAR(Birthday))\\
% Templatized Domain Statement:\\
...\\
\hline
\end{tabular}
%\hline
\end{tabular}
}
\end{table}
%SQL Generation Prompt
\begin{table}[!t]
\centering
\caption{LLM Prompt for SQL Generation }
\label{tab:SQLprompt}
\small
%\sffamily
\resizebox{\columnwidth}{!}{%
\begin{tabular}{l}
\hline
\textbf{ System Prompt:} \\ \hline
\begin{tabular}{l} %[c]{@{}l@{}}
You are a database administrator. You have designed the following\\
database for $<$DB name$>$ whose schema is represented as:\\
CREATE TABLE Examination (\\
ID INTEGER PRIMARY KEY, -- identification of the patient\\
`Examination Date` DATE, --Format Year-Month-Date;\\
`aCL IgG` REAL, -- anti-Cardiolipin antibody (IgG) concentration\\
% `aCL IgM` REAL, -- anti-Cardiolipin antibody (IgM) concentration\\
ANA INTEGER , -- anti-nucleus antibody concentration\\
% `ANA Pattern` TEXT , -- pattern observed in the sheet of \\
% \hspace{1cm} ANA examination\\
% `aCL IgA` INTEGER , -- anti-Cardiolipin antibody\\
% \hspace{1cm} (IgA) concentration\\
Diagnosis TEXT , -- disease names\\
% KCT TEXT , -- measure of degree of coagulation\\
% RVVT TEXT , --measure of degree of coagulation;\\
% LAC TEXT , --measure of degree of coagulation\\
% Symptoms TEXT , -- other symptoms observed;\\
Thrombosis INTEGER , --degree of thrombosis
); ...
\end{tabular} \\
\hline
\textbf{ User Prompt:} \\
\hline
\begin{tabular}{l} %[c]{@{}l@{}}
Query: $<$User Query$>$\\
Domain Knowledge statements, some of which might or \\
might not be useful to generate the query:$<$A list of DSs$>$ \\
Generate a single SQL in SQLite format for the above query. Do not \\include any extra text, tag or information other than the SQL query itself.\\
SQL:
\end{tabular} \\
\hline
\end{tabular}
}
\end{table}
\section{Related Works}
Existing approaches for NL-to-SQL semantic parsing \cite{rai2023improving, li2023graphix, gao2023text, pourreza2024din} assume DBs with semantically meaningful table, column names such as Spider\cite{yu2018spider}, WikiSQL \cite{zhongSeq2SQL2017}, or GeoQuery \cite{Zelle:1996:LPD:1864519.1864543}.
Results on these DBs often fail to translate to enterprise DBs with semantically opaque schema elements and missing domain information. Synthetically created datasets incorporate domain knowledge (SpiderDK \cite{Gan2021ExploringUL}), replace schema-related words by synonyms (Spider-Syn \cite{Gan2021TowardsRO}) or paraphrase words (DBPal \cite{Utama2018AnEN}) in queries, allowing for increased difficulty for query understanding. However, for enterprise DBs, the required domain knowledge can be beyond column, value descriptions, including knowledge of entities derived with mathematical or string operations.
The BirdSQL \cite{li2024can} dataset contains realistic DBs from multiple domains with entities requiring additional information for query understanding, provided in the form of the oracle query specific domain knowledge (evidences). %The top performing approaches \cite{Gao2024APO,Pourreza2024CHASESQLMR,li2024codes, pourreza2024dts, wang2023mac} on this dataset assume availability of this query-specific domain statements, during inference. As opposed to this unrealistic assumption, for each database, we assume availability of a domain statement repository, but the domain statements are mapped to queries dynamically at test time via a retrieval process.
Queries in recently published Spider 2.0 dataset \cite{lei2024spider} have a similar requirement. However, we observe that the dataset does not explicitly provide such domain knowledge and thus does not facilitate the simulation of our framework (\S\ref{sec:approach}).
%Some approaches retrieve similar few-shots for text-to-SQL using text based \cite{poesia2022synchromesh, an2023skill, li2023resdsql} or composition based \cite{rajkumar2022evaluating, qiu2022evaluating, hosseini2022compositional} similarity. \cite{rubin-etal-2022-learning} generates and scores few-shots to create contrastive pairs for training a retriever, which is further used to rank them for a test NL query.
As opposed to existing few-shot retrieval techniques \cite{an2023skill, li2023resdsql,rubin-etal-2022-learning} , our focus is on retrieval of domain statements. %which describe relevant entities for NL query understanding
Existing approaches perform retrieval \cite{dou2023towards} or generation \cite{Hong2024KnowledgetoSQLES} of domain knowledge for the downstream text-to-SQL task. They assume the availability of parallel data in the form of a query and a DS pair to fine-tune the base model. As opposed to these, we assume no availability of such data for a given DB. Moreover, in an enterprise setting, applicability of cross-domain DB knowledge is infeasible. This is also validated by our observation with distinct DBs within BirdSQL, where a match of domain statements across DBs is incidental and rare and possible only in case of standard public knowledge (genders, date formats, etc). By definition the DSs are needed when DBs have their own traits. Hence, we assume an in-domain (single DB) setting where there would be availability of DB level domain knowledge, as opposed to cross-domain setting assumed in \cite{Hong2024KnowledgetoSQLES}. We find it surprising that the DS generator in \cite{Hong2024KnowledgetoSQLES} would generalize across schema. Subsequently, we also observe our gains (15.3\%) are much more over \cite{Hong2024KnowledgetoSQLES} (5.7\%), primarily because we lean on DSs provided by domain experts, as opposed to generating them.
\section{Framework for NL-SQL Semantic Parsing}\label{sec:approach}
In our proposed framework, a domain expert can provide domain statements $d_{NL}$ in natural language at any time during the usage of a database (\S~\ref{sec:sol}). We translate each $d_{NL}$ into a structured form $d$ that clearly demarcates the mapping between a natural language phrase and an SQL snippet (\S\ref{sec:tras}). The structured DS $d$ are stored and indexed as part of other meta data associated with the DB.
%using an LLM and further optionally rectified by DBA %as a one time task
%We embed and index the `text' ($d.x$) part of $d$.
Any subsequent user query $t$, will first retrieve a subset of the relevant DSs $d_t \subseteq d$ (\S~\ref{sec:ret}) to augment for the final text-to-sql generation. % In Section \ref{sec:ret}, we elaborate on how we match queries to stored structured DSs.
\subsection{Natural Language Domain Statements}\label{sec:sol}
%We are given a DB with its schema $S$, including the names of tables, columns, their types, and representative column values %strings corresponding to entities stored in each column.
%Figure~\ref{fig:example}, shows an example schema of `Thrombosis' DB comprising of three tables. The schema itself provides limited knowledge for converting NL queries on the DB to SQL. Hence, we support %expert customization with DSs as a task
%solicitation of DSs from a domain expert
%, executed once at the time of DB creation, which is a human intensive process in an enterprise setting \cite{helskyaho2023towards}.
% \cite{wingate2023fatalities}.
%, at any time during the enterprise DB life-cycle can provide one or more domain statements
Enterprises invest in one-time manual effort to build domain knowledge for their long lived DBs for gains in their reliability and accuracy. Domain experts may not necessarily be fluent in SQL, and specify domain knowledge in NL statements. % form to bridge the semantic gap between database schema elements and their usage in the real world.
%A domain expert first provides a list of DSs in NL denoted as $\{\dNL\}$.
%Some examples appear in Figure~\ref{fig:example} and Table~\ref{tab:Examples} third column.
We identify four broad categories of such statements (Table \ref{tab:Examples}): (i) description of cryptic schema names, %such as aCL (IgG) column in Thrombosis DB
(Figure~\ref{fig:example} DS 9), (ii) description of string values of categorical columns%, such as `+' or `-' values of `Admission' column of patient table indicating `inpatients' and `outpatients' OR `+' value of `RVVT' column of Examination table indicating positive degree of coagulation
(Figure \ref{fig:example} DSs 3, 4 and 8), (iii) Handling format mismatches %by replacing the non-domain specific
especially for numerical and date values. %with fixed integer placeholder value.
%For example, the value for `First Date' in the patient's table
(Figure \ref{fig:example} DS 2), and (iv) Converting NL expression to formula
for specifying domain-specific predicates such as age, ratio of in and out patients, etc% most common signs, normal range of LDH and Rheumatoid Factor
(Figure \ref{fig:example} DS 1, 5, 7, 10, 11). We believe that such DSs by definition are very specific and private to enterprise DBs and cannot be conjured from LLMs trained on public data. Thus their solicitation from domain experts is unavoidable.
\begin{table}[h]
\centering
\caption{BirdSQL \cite{li2024can} Dev Split DBs. \textbf{DS}: Union of Domain Statements (Evidences) of IN Set Questions}
\label{tab:db_info}
\footnotesize
%\resizebox{\columnwidth}{!}{%
\begin{tabular}{lcccc}
\hline
\textbf{Database} & \textbf{\#Tables} & \textbf{\#Columns} & \textbf{\#Questions} & \textbf{\#DS} \\ \hline
Thrombosis Pred. & 3 & 72 & 163 & 188 \\
Calif. School & 3 & 89 & 89 & 52 \\
Card Games & 6 & 168 & 191 & 185 \\
Debit Card Spec. & 5 & 21 & 64 & 33 \\
Toxicology & 4 & 11 & 145 & 300 \\
Financial & 8 & 49 & 106 & 66 \\
Codebase Comm. & 8 & 71 & 186 & 136 \\
Euro. Football2 & 7 & 199 & 129 & 124 \\
Formula 1 & 13 & 94 & 175 & 102 \\
Student Club & 8 & 48 & 158 & 127 \\
Superhero & 10 & 31 & 129 & 130 \\ \hline
Average & 6.8 & 77.5 & 139.5 & 131.2 \\ \hline
\end{tabular}%
%}
\end{table}
\subsection{Domain Statement Structuring}\label{sec:tras}
We translate $d_{NL}$ into a structured form $d$ that differs from the original $d_{NL}$ in two ways: (1) $d$ is of the form "$d.x$ refers to $d.q$" where $d.x$ denotes a NL domain expression, and $d.q$ denotes a SQL snippet to which $d.x$ maps.
Such a representation of $d$ allows to better match domain specific constructs in the user query, and lookup the attached SQL snippet. (2) Constants such as names and dates in $d_{NL}$ are converted to a fixed canonical form to allow better match with user queries. We expect the structuring to bring the NL DSs into a unified format, leading to more generalizable downstream retrieval. Figure~\ref{fig:example} and the fourth column of Table~\ref{tab:Examples} provide examples of structured DSs for all the four categories, discussed in section \ref{sec:sol}.
%\textcolor{green}{Reconsider details: 2; DB names; percent overlap}
We harness LLMs for translating $\{d_{NL}\}$ to $\{d\}$. For each DB, the DBA defines few-shot exemplars consisting of pairs of $<d_{NL},d>$ covering all the FOUR categories ($\sim$2 per category).
Given a DB schema $S$, the few-shots $\{<d_{NL}, d>\}$ provided by the DBA and $d_{NL}$ provided by the domain expert, with a proper instruction, the LLM generates a $d$ for each $d_{NL}$ (Table \ref{tab:TDSprompt}). We compare $d$ generated by the LLM with the ones manually translated by DBA, for three DBs (Thrombosis Prediction, California Schools and Card Games). We observe to have on an average 77.67\% lexical (word) and 88.82\% semantic overlap, computed using BERT embeddings \cite{reimers2019sentence}. This demonstrates the ability of the LLM to generate $d$ from $d_{NL}$ and also the amount of noise in the LLM generated $d$.
To ensure correctness, given $S$, $d_{NL}$, and $d$ generated by the LLM, the DBA \emph{optionally} validates $d$ and rectifies errors, if any. For this process the DBA is NOT provided with any information about NL-SQL query pairs to which the DSs are aligned. % This optional DBA intervention is needed only when a new DB is designed or existing DB schema is updated, which is less frequent in the lifetime of an enterprise \cite{curino2013automating, 10.1145/1595808.1595817}. The more frequent task of SQL generation from a query by retrieving relevant DSs (Section \ref{sec:ret}) is completely automated. % \textcolor{red}{Moreover, we propose LLM generated Structuring for full automation in case of DBs with evolving domain repositories and/or large schema. In Section \ref{sec:results}, we showcase that LLM generated structured DSs serve as the better context for NL query resolution than the NL DSs.}
Once the $d$ are created, we compute and index the embeddings of their text components ($d.x$).
\subsection{Structured Domain Statement Retrieval}\label{sec:ret}
%As the last stage, g
Given a user query $t$, an LLM generates an SQL $q$, when instructed with a prompt comprising of the query $t$, the DB schema $S$ including column name and value descriptions, augmented with relevant DSs (Table \ref{tab:SQLprompt}). %We do not assume availability of ground truth structured DSs $\{d_t\}$ for $t$.
We devise a retriever $R$, which retrieves a relevant subset $\{\hat{d_t}\} \subseteq \{d\}$ of the DSs given $t$ using $d.x$. A standard method of retrieval augmentation is to match the query embedding $t$ to the embeddings of each stored $d$. This approach is inadequate for our case, as a $d$ may be relevant only to a part of a query, and multiple DSs may map to different parts of $t$. For example, different parts of the user query 2 in Figure~\ref{fig:example} matches the `text' ($d.x$) of three relevant DSs. %If we compute the embedding of the whole query $t$ to match the relevant $d$, the resulting similarity may be too small (illustrated in Table \ref{tab:question_decompose} of Appendix \ref{sec:ques_decomp}).
This motivates the need for our query decomposition-based retrieval procedure, described below.
%We pre-compute and store embeddings of the text component ($d.x$) of each $d$ as we templatize it.
Given a user query $t$, we convert it into a generic form by replacing numerical values and dates to the same fixed integer placeholder value used during DS Structuring for better matching. The next step is to compute the embeddings of the contiguous sub-strings of the query $t$ and use cosine similarity (SIM) to compute their matches with the stored embeddings of $d$.
The complexity of the embedding and similarity computation increases exponentially with the number of sub-strings of $t$ and thus is of the order of $2^{|t|}$, where $|t|$ is the query length. We observe that for a $d$, only those contiguous sub-stings of $t$, having lengths in the range of $(\lfloor |d.x| - n \rfloor , \lceil |d.x| + n \rceil)$, $n$ being the threshold, yields the best match. Considering only these subset of sub-strings, the complexity is the order of $2^{|d.x|}$. $|d.x|$ (average 4.28 words for all DBs in the dataset) is typically much smaller (4 times) than $|t|$ (average 14.55 words), leading to efficiency gains for computations of embeddings as well as matching. %The efficiency is further further improved by computing the embeddings of all the considered sub-strings in a batch and computing their similarity with all the domain statements in parallel using vectorization.
%With this observation, for better efficiency, for each new query $t$, we consider the embeddings of only those contiguous sub-strings of $t$ having lengths in the range $r$ to compute the similarity with a $d$.
For a new DB, we assume the availability of a few NL-SQL pairs to tune the threshold $n$. We set it to a value which yields maximum execution accuracy for downstream SQL generation for these available NLs, when augmented with the retrieved $d_t$s.
For each new query $t$ posed on a db, we pre-compute the embeddings of only those contiguous sub-strings within the range of ($\lfloor min(|d.x|) -n \rfloor$, $\lceil max(|d.x|) + n \rceil$) using batch processing and cache them. Here $min(|d.x|)$ and $max(|d.x|)$ are the minimum and maximum lengths of $d.x$ components of all $d$s available in the repository for that DB. We use the cached embeddings of these query sub-strings to compute their similarity with all $d$s in the repository in parallel using vectorization.
We define the similarity between $t$, and a $d$ as follows:
Let $t_1, ...., t_m$ denote the ordered list of the words in the query. As depicted in Equation \ref{eq:subseq}, the score between a query $t$ and a structured DS $d$, is computed as the maximum of the similarity of $d.x$ with the considered sub-strings of $t$, determining it's best match.
\begin{equation}
\label{eq:subseq}
Score(t, d) = \max_{ |d.x| - n \le i \le j \le |d.x| + n }\text{SIM}(d.x, [t_i,t_j])
\end{equation}
%We further weigh the score for a $d$ with the length of the sub-string with which it has the best match and then normalize the scores across all the domain statements.
For a query, we rank the $d$s using this scoring mechanism and augment top-K to the prompt for SQL generation. We reduce the time taken to perform retrieval for an average length query from few minutes to few milliseconds. For large DS repositories we envision retrieval of candidate DSs by using ColBERT \cite{santhanam-etal-2022-colbertv2} style index, followed by re-ranking with our method. An illustration of this efficient sub-string based retrieval method is provided (Figure \ref{fig:example1}). Following this, we perform SQL generation via LLM with the retrieved relevant top-k DS in context (Table \ref{tab:SQLprompt}).
\begin{table*}[!ht]
\centering
\footnotesize
\caption{Examples of Missing and Erroneous Oracle Domain Statements in BirdSQL indicated by \textcolor{red}{Red} % indicates possible missing relevant statements and errors in the existing oracle statements.
}
\resizebox{\textwidth}{!}{%
\begin{tabular}{|llll|}
\hline
\multicolumn{1}{|l|}{\textbf{\begin{tabular}[c]{@{}l@{}}\#\end{tabular}}} &
\multicolumn{1}{l|}{\textbf{Database}} &
\multicolumn{1}{l|}{\textbf{Question}} &
\textbf{Natural Language Domain Statements} \\ \hline
\multicolumn{4}{|c|}{\textbf{Missing Domain Statements}} \\ \hline
\multicolumn{1}{|l|}{1.} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}Euro. Football2\end{tabular}} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}What's the heading accuracy of Ariel Borysiuk?\end{tabular}} &
\begin{tabular}[c]{@{}l@{}}\textcolor{red}{Ariel Borysiuk is player name}\end{tabular} \\ \hline
\multicolumn{1}{|l|}{2.} &
\multicolumn{1}{l|}{Formula 1} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}Name the top 3 drivers and the points they scored \\in the 2017 Chinese Grand Prix.\end{tabular}} &
\begin{tabular}[c]{@{}l@{}}\textcolor{red}{Name of the drivers consists of their forename} \\ \textcolor{red}{and surname.}\end{tabular} \\ \hline
\multicolumn{1}{|l|}{3.} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}Debit Card Spec.\end{tabular}} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}Which year recorded the most gas use paid in EUR?\end{tabular}} &
\begin{tabular}[c]{@{}l@{}}\textcolor{red}{Extract Year from Date by considering last 4 digits}\end{tabular} \\ \hline
%\multicolumn{1}{|l|}{4.} &
% \multicolumn{1}{l|}{Student Club} &
% \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}How many of the members' hometowns are from Maryland?\end{tabular}} &
% \begin{tabular}[c]{@{}l@{}}\textcolor{red}{Maryland refers to name of the state}\end{tabular} \\ \hline
\multicolumn{4}{|c|}{\textbf{Erroneous Domain Statements}} \\ \hline
\multicolumn{1}{|l|}{4.} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}Thrombosis \\ Prediction\end{tabular}} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}What was the age of the youngest patient when they \\initially arrived at the hospital?\end{tabular}} &
\begin{tabular}[c]{@{}l@{}}the youngest patient is the one which has \\\textcolor{red}{MIN}(YEAR(Birthday))\end{tabular} \\ \hline
\multicolumn{1}{|l|}{5.} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}Card Games\end{tabular}} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}What percentage of cards without power are in French?\end{tabular}} &
\begin{tabular}[c]{@{}l@{}}cards without power indicates value of \textcolor{red}{power = `*'}\end{tabular} \\ \hline
%\multicolumn{1}{|l|}{7.} &
% \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}Toxicology\end{tabular}} &
% \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}How many double bonds does TR006 have and is it carcinogenic?\end{tabular}} &
%\begin{tabular}[c]{@{}l@{}}`non-carcinogenic molecule' is represented by label = \textcolor{red}{'-'}\end{tabular} \\ \hline
\multicolumn{1}{|l|}{6.} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}California \\Schools\end{tabular}} &
\multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}What is the phone number of the school that has the highest \\number of test takers with an SAT score of over 1500?\end{tabular}} &
\begin{tabular}[c]{@{}l@{}}\textcolor{red}{`False'}\end{tabular} \\ \hline
\end{tabular}
}
\label{tab:Error-DS}
\end{table*}
\section{Experimentation and Results}
\subsection{Dataset}\label{sec:dataset}
We consider the BirdSQL Dev set, which is the standard dataset used for NL-to-SQL task. The DBs are from real platforms such as Kaggle, Relation.vit, etc, spanning over diverse domains including medical, finance and sports.
Each NL query posed on the DBs require domain understanding of one or more of the four categories ( \S\ref{sec:sol}), for which the dataset provides domain knowledge in terms of 1 to 3 evidences in natural language (Figure \ref{fig:example}, Table \ref{tab:Examples}).
%We take 11 Dev set DBs into consideration, each belonging to a different domain,
%The 11 DBs have a total of 1534 parallel NL-SQL pairs.% (Details in Table \ref{tab:db_info} in Appendix \ref{sec:dataset_stat}).
% For each NL query, the dataset provides domain knowledge in terms of 1 to 3 evidences, required to resolve the query.
% This knowledge is in the form of natural language (English) DSs as illustrated by Figure \ref{fig:example} and Table \ref{tab:Examples}.
There are variations in the format in which the $d_{NL}$ are represented across the DBs. For example, the math formulae are specified as equations such as `\textit{Gap = highest average salary - lowest average salary}' (DB: Financial) or in text `\textit{Average score for all subjects can be computed by addition of AvgScrMath, AvgScrRead and AvgScrWrite}' (DB: California Schools). Column values are described by various formats such as `\textit{`Chinese Simplified' is the language} (DB: Card Games) or `\textit{label = `+' means molecules are carcinogenic}' (DB: Toxicology). Our automated approach of LLM based Structuring shall translate $d_{NL}$ in variety of different forms into the pre-specified structured form $d$, demonstrating its effectiveness (\S\ref{sec:tras}).
% (Examples in Appendix \ref{ds-variations}).
Additionally, the top performing approaches on BirdSQL\cite{Gao2024APO,Pourreza2024CHASESQLMR,li2024codes, pourreza2024dts, wang2023mac} assume availability of these oracle query-specific DSs for SQL generation. In real-world enterprise settings, for each new query posed on a DB, such statements will not be available. %As opposed to this unrealistic assumption, for each database, we assume availability of a domain statement repository, but the domain statements are mapped to queries dynamically at test time via a retrieval process.
%Existing approaches on BirdSQL \cite{li2024codes, pourreza2024dts} use these oracle query specific DSs for SQL generation. In real-world enterprise settings, for each new query posed on an enterprise DB, such statements will not be available.
For the 11 DBs in Dev split of BirdSQL, we simulate the scenario of solicitation of DB level domain knowledge from domain experts. % by taking a union of query-specific domain knowledge across the databases in BirdSQL to form a database level repository.
We form 50-50\% random splits of the queries in each DB.%, maximizing the \emph{Tree Edit Distance}\footnote{\scriptsize\url{https://pypi.org/project/zss/1.1.4/}} between the SQLs of the splits, with the \emph{max-cut} algorithm\footnote{\scriptsize\url{https://en.wikipedia.org/wiki/Maximum_cut}}. This leads to maximum divergence in the compositional distributions of the splits. We henceforth refer to these splits as the IN and OUT Sets.
The queries in one of the splits are assumed to be the part of regular DB workload and the union of DSs of the queries in this split are assumed to be provided by the domain expert (\S\ref{sec:sol}), to form the domain repository for retrieval. Henceforth, we refer to this split as the IN Set. Note, we do not assume the availability of the mapping between the DSs and queries. The DSs for the other split (OUT Set), are not included in this repository. We find that $\sim77\%$ of queries in the OUT Set of all the DBs have no oracle DS overlap with the repository, forming an OUT-NO Set %whereas, the remaining queries have partial overlap
. %with the queries in the IN set.
Thus, we simulate the realistic scenario of domain repository construction with complete (IN Set), partial (OUT Set) and no (OUT-NO Set) knowledge of the queries that may get posed on the DB. We expect an approach to be able to retrieve the right set-of DSs for the IN Set queries leading to good performance on the downstream SQL generation task, comparable to when the oracle DSs are in-context. Whereas, we do not expect a comparable performance for the other sets.
%This allows us to simulate the realistic scenario of domain repository construction without knowledge of the queries that may get posed on the DB. More importantly, we do not assume the availability of the mapping between DSs and queries.
Table \ref{tab:db_info} %in Appendix \ref{sec:dataset_stat}
provides the dataset statistics, which we release at
%the names of all the DBs in the Dev Splits of BirdSQL dataset, the number of tables, columns, total queries per DB and the number of DSs in the repository of each DB, synthesized taking the union the IN split query evidences, removing redundancy. We release this extended dataset
\footnote{\scriptsize\url{https://anonymous.4open.science/status/RAG-of-Domain-Knowledge-for-Text-to-SQL}}.%\textcolor{red}{We further observe that for this dataset the length of the `text' component of the generated structured DS ($|d.x|$) is an average 4.28 words, which is 4 times smaller than the average query length ($|t|$) of 14.55 words, leading to the required efficiency gains, discussed in section \ref{sec:ret}.}
\subsection{Models and hyper-parameter Setting}\label{sec:hyper}
%We use GPT-3.5 Turbo for generation of structured DSs from NL statements (Prompt illustrated in Table \ref{tab:TDSprompt}).
We use the following LLMs to generate SQL: \begin{enumerate*}
\item GPT-3.5-turbo%with 16K context
\footnote{\scriptsize\url{https://platform.openai.com/docs/models/gpt-3-5-turbo}}
\item Mixtral-8X7B% with 32K context
%\cite{Jiang2024MixtralOE}
\footnote{\scriptsize\url{huggingface.co/mistralai/Mixtral-8x7B-Instruct-v0.1}}
\item Llama3-70B%with 262K context
%\cite{Touvron2023LLaMAOA}
\footnote{\scriptsize\url{https://huggingface.co/meta-llama/Meta-Llama-3-70B}}
\item sqlcoder-8B%with 16K context
\footnote{\scriptsize\url{https://github.com/defog-ai/sqlcoder}}
\item Gemini-1.5 Flash%with 128K context
\footnote{\scriptsize\url{https://github.com/google-gemini/cookbook}}.
\end{enumerate*}
For reproducibility, we ensure deterministic outcomes by setting t = 0. We require better precision for IN Set queries with guaranteed availability of the required domain knowledge and better recall for OUT Set queries, which may have only partial domain knowledge available. Hence, for the retriever, we set lower value of K = 4 for the IN Set and higher value of K = 10 for the OUT Set.
We compute embeddings with best performing\footnote{\scriptsize \url{https://www.sbert.net/docs/pretrained_models.html}} \textit{all-mpnet-base-v2} BERT based model\footnote{\scriptsize \url{https://huggingface.co/sentence-transformers/all-mpnet-base-v2}} \cite{reimers2019sentence}. We get the best performance of the sub-string based matching technique at the threshold $n$ (\S\ref{sec:ret}) ranging from 2 to 4 for the DBs in the BirdSQL Dev split.
%\vspace{-1mm}
\subsection{ Evaluation Metric}\label{sec:eval}
We use `Execution Accuracy' of the generated SQLs with DSs in context as the evaluation metric, which is invariant to the syntactic variations in the generated and gold SQLs. %We execute the ground truth SQL $q$ against the DB and treat the resulting answer as the ground truth answer. If the answer produced by predicted SQL matches the ground truth answer, the execution accuracy for that sample is 1.
Analyzing the dataset, we observe that there are 20.17\% queries with missing (examples 1-3) and 6.72\% with erroneous (examples 4-6) oracle DSs (Table \ref{tab:Error-DS}). Hence, we treat evidence F1 as a secondary metric to analyze the retrieval performance. Assuming the availability of only IN Set DSs, we compute evidence F1 only for the \textbf{IN Set}, by considering top-K retrieved DSs per query, where K is the number of ground-truth DSs for that query.
%Assuming the availability of only IN Set DSs, we compute evidence F1 only for the \textbf{IN Set}, by considering top-K retrieved DSs per query %\textcolor{red}{\sout{, where K is the number of ground-truth DSs for that query}}
%. The Evidence F1 scores for all the \emph{Whole} as well as \emph{decomposed} query based retrieval mechanisms with Templatized DSs (Str) are as follows: (i) BM25: 0.35 (ii) BE: 0.33 (iii) MS-M: 0.34 (iv) STSb: 0.37 (v) sBSR: 0.35 (vi) SbR: 0.39. Thus, evidence F1 for SbR is the best. Note that though overall evidence F1 scores are low, for all LLMs the execution accuracy for SbR-Str for IN split is greater than or comparable with QS (Table \ref{tab:main_results_IN}). This is because, though for computation of F1 we consider top-K retrieved DSs, where K is the number of oracle DSs for each query; for SQL generation, we consider top-4 retrieved DSs, which can be higher than the number of ground truth DSs. This allows us to cover missing oracle DSs in context for SQL generation. Also, with DBA scanning we observe erroneous oracle DSs getting rectified leading to good results.
% Results Comparison
\begin{table*}[]
\centering
\caption{Execution Accuracy averaged over ALL \textbf{IN Set} of Dev Set DBs of BirdSQL; NLQ: NL Query; NL: NL Domain Statements (DS); S$\mathrm{tr}$: DBA Screened Structured DS;
QS, No DS, All-DS, BM25, BE, MS-M, STS$\mathrm{b}$, $\mathrm{s}$BSR : Baselines (\S\ref{sec:base}); S$\mathrm{b}$R: Sub-string based Retrieval (\S\ref{sec:approach}); Retrievals with K = 4; \textbf{Bold}: Best performance.}
\footnotesize
\resizebox{\textwidth}{!}{%
\begin{tabular}{l||l||lll|llllllll|llll}
\hline
\multirow{3}{*}{\textbf{LLM}} &
\multicolumn{1}{c||}{\multirow{3}{*}{\textbf{QS}}} &
\multicolumn{3}{c|}{\textbf{No Retrieval}} &
\multicolumn{8}{c|}{\textbf{Retrieval: \emph{Whole} NLQ}} &
\multicolumn{4}{l}{\textbf{\begin{tabular}[c]{@{}l@{}}\textbf{Retrieval: \emph{Decomposed} NLQ}\end{tabular}}} \\ \cline{3-17}
&
\multicolumn{1}{c||}{} &
\multicolumn{1}{l|}{\textbf{No}} &
\multicolumn{2}{c|}{\textbf{All DS}} &
\multicolumn{2}{c|}{\textbf{BM25}} &
\multicolumn{2}{c|}{\textbf{BE}} &
\multicolumn{2}{c|}{\textbf{MS-M}} &
\multicolumn{2}{c|}{\textbf{STSb}} &
\multicolumn{2}{c|}{\textbf{sBSR}} &
\multicolumn{2}{c}{\textbf{SbR}} \\ \cline{4-17}
&
\multicolumn{1}{c||}{} &
\multicolumn{1}{l|}{\textbf{DS}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\textbf{Str} \\ \hline
\textbf{\begin{tabular}[c]{@{}l@{}}Mixtral (8X7B)\end{tabular}} &
20.2 &
\multicolumn{1}{l|}{10.3} &
\multicolumn{1}{l|}{11.4} &
13.1 &
\multicolumn{1}{l|}{12.5} &
\multicolumn{1}{l|}{13.0} &
\multicolumn{1}{l|}{13.0} &
\multicolumn{1}{l|}{15.6} &
\multicolumn{1}{l|}{11.9} &
\multicolumn{1}{l|}{13.5} &
\multicolumn{1}{l|}{13.9} &
16.4 &
\multicolumn{1}{l|}{13.9} &
\multicolumn{1}{l|}{14.3} &
\multicolumn{1}{l|}{14.6} &
\textbf{17.2} \\ %\hline
\textbf{\begin{tabular}[c]{@{}l@{}}Llama 3 (70B)\end{tabular}} &
23.5 &
\multicolumn{1}{l|}{12.2} &
\multicolumn{1}{l|}{14.4} &
17.0 &
\multicolumn{1}{l|}{16.0} &
\multicolumn{1}{l|}{16.9} &
\multicolumn{1}{l|}{16.0} &
\multicolumn{1}{l|}{21.6} &
\multicolumn{1}{l|}{15.1} &
\multicolumn{1}{l|}{18.0} &
\multicolumn{1}{l|}{19.2} &
22.3 &
\multicolumn{1}{l|}{17.2} &
\multicolumn{1}{l|}{19.3} &
\multicolumn{1}{l|}{21.1} &
\textbf{25.2} \\ %\hline
\textbf{\begin{tabular}[c]{@{}l@{}}SQL Coder (8B)\end{tabular}} &
25.3 &
\multicolumn{1}{l|}{15.3} &
\multicolumn{1}{l|}{15.8} &
18.8 &
\multicolumn{1}{l|}{18.3} &
\multicolumn{1}{l|}{19.1} &
\multicolumn{1}{l|}{18.2} &
\multicolumn{1}{l|}{23.0} &
\multicolumn{1}{l|}{17.4} &
\multicolumn{1}{l|}{19.4} &
\multicolumn{1}{l|}{20.9} &
24.4 &
\multicolumn{1}{l|}{19.4} &
\multicolumn{1}{l|}{20.6} &
\multicolumn{1}{l|}{24.2} &
\textbf{30.1} \\ %\hline
\textbf{\begin{tabular}[c]{@{}l@{}}Gemini 1.5 Flash\end{tabular}} &
40.8 &
\multicolumn{1}{l|}{30.4} &
\multicolumn{1}{l|}{34.1} &
35.2 &
\multicolumn{1}{l|}{31.6} &
\multicolumn{1}{l|}{32.1} &
\multicolumn{1}{l|}{31.0} &
\multicolumn{1}{l|}{33.9} &
\multicolumn{1}{l|}{31.5} &
\multicolumn{1}{l|}{32.5} &
\multicolumn{1}{l|}{35.5} &
36.8 &
\multicolumn{1}{l|}{32.0} &
\multicolumn{1}{l|}{33.0} &
\multicolumn{1}{l|}{35.9} &
\textbf{39.6} \\ %\hline
\textbf{\begin{tabular}[c]{@{}l@{}}GPT 3.5 Turbo \end{tabular}} &
42.1 &
\multicolumn{1}{l|}{32.2} &
\multicolumn{1}{l|}{35.1} &
39.0 &
\multicolumn{1}{l|}{35.3} &
\multicolumn{1}{l|}{41.2} &
\multicolumn{1}{l|}{33.0} &
\multicolumn{1}{l|}{42.3} &
\multicolumn{1}{l|}{31.6} &
\multicolumn{1}{l|}{40.8} &
\multicolumn{1}{l|}{37.5} &
45.8 &
\multicolumn{1}{l|}{35.3} &
\multicolumn{1}{l|}{41.4} &
\multicolumn{1}{l|}{38.3} &
\textbf{47.5} \\ \hline
\end{tabular}%
}
\label{tab:main_results_IN}
\end{table*}
\subsection{Baselines}\label{sec:base}
We compare the following methods:
\begin{enumerate*}[label=(\arabic*)]
\item \textbf{No Retrieval:}
\begin{enumerate*}[label=(\alph*)]
\item \textbf{QS}: Query-Specific oracle DSs provided in the dataset, serve as an upper-bound for the OUT set,
\item \textbf{No-DS}: zero-shot with no DSs,
\item \textbf{All-DS-NL}: All NL Domain Statements ($\{d_{NL}\}$) provided by domain experts for the IN Set,
\item \textbf{All-DS-Str}:All DBA screened Structured Domain Statements ($\{d\}$) for the IN set.
\end{enumerate*}
\item \textbf{Retrieval:} Retrieval of top-K NL DSs ($\{\hat{d}_{NL_t}\} \subseteq \{d_{NL}\}$), where similarity is computed with the complete statement $d_{NL}$ and LLM generated Structured (L-Str) and DBA screened Structured (Str) DSs ($\{\hat{d}_{t}\} \subseteq \{d\}$), where the similarity is computed with the `text' ($d.x$) part of $d$.
\begin{enumerate*}[label=(\alph*)]
\item \textbf{Similarity with \emph{Whole} NL query $t$}:
\begin{enumerate*}[label=(\roman*)]
\item \textbf{BM25}:
Okapi variant \cite{robertson1994okapi} of BM25 from the $rank\_bm25$\footnote{\scriptsize \url{https://github.com/dorianbrown/rank_bm25}} library,
\item \textbf{BE}:
cosine similarity using BERT embedding,
\item \textbf{MS-M}: on the lines of \cite{dou2023towards} we use the best performing\footnote{\scriptsize ckpt ms-marco-MiniLM-L-12-v2: \url{https://www.sbert.net/docs/pretrained-models/ce-msmarco.html}} dense retriever trained with MS MARCO dataset \cite{lei-etal-2023-unsupervised},
\item \textbf{STSb}: the best performing\footnote{\scriptsize ckpt stsb-roberta-base: \url{https://sbert.net/docs/cross_encoder/pretrained_models.html}} dense retriever trained with STS dataset \cite{cer-etal-2017-semeval},
\end{enumerate*}
\item \textbf{Similarity with \emph{Decomposed} NL query}:
\textbf{(sBSR)}: The method of set selection with BERT Score Recall \cite{gupta-etal-2023-coverage}. Here, we take the contextual representation of each word in the query seperately and perform retrieval of DSs to cover all words.
\end{enumerate*}
\end{enumerate*}
% in db splits
\begin{table}[]
\caption{ Execution accuracy of \textbf{IN Set} of Dev Set DBs by \textbf{GPT-3.5-Turbo}; NL: NL Domain Statements (DS); L-S$\mathrm{tr}$/ S$\mathrm{tr}$: LLM generated; DBA Screened Structured DS;
QS, No DS, All-DS: Baselines (\S\ref{sec:base});
S$\mathrm{b}$R: Our Sub-string based Retrieval (\S\ref{sec:approach}); Retrievals with K=4; \textbf{\underline{Bold Underline}}: Best performance; \textbf{Bold}: Second Best.
}
\label{tab:gpt-in-results}
\centering
\small
\resizebox{\columnwidth}{!}{%
\begin{tabular}{l||l||l|l|l|lll}
\hline
&
&
\multicolumn{1}{c|}{\textbf{No}}&
\multicolumn{2}{c|}{\textbf{All DS}} &
\multicolumn{3}{c}{\textbf{SbR}} \\ \cline{4-8}
\multirow{-2}{*}{\textbf{Database}} &
\multirow{-2}{*}{\textbf{QS}} &
\multicolumn{1}{c|}{\textbf{DS}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\textbf{Str} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{L-Str}} &
\textbf{Str} \\ \hline
\textbf{Thrombosis Pred.} &
30.9 &
18.5 &
\multicolumn{1}{l|}{20.7} &
13.4 &
\multicolumn{1}{l|}{{24.6}} &
\multicolumn{1}{l|}{{\textbf{35.8}}} &
\multicolumn{1}{l}{{\textbf{\underline{37.0}}}}
\\
%\hline
\textbf{California Schools} &
25.0 &
11.4 &
\multicolumn{1}{l|}{11.1} &
{20.0} &
\multicolumn{1}{l|}{{15.9}} &
\multicolumn{1}{l|}{\textbf{20.5}} &
\multicolumn{1}{l}{{\textbf{\underline{29.6}}}}
\\
% \hline
\textbf{Card Games} &
32.6 &
22.1 &
\multicolumn{1}{l|}{26.0} &
{\textbf{32.6}} &
\multicolumn{1}{l|}{{29.4}} &
\multicolumn{1}{l|}{\textbf{32.6}} &
\multicolumn{1}{l}{{ \underline{\textbf{47.4}}}}
\\
%\hline
\textbf{Debit Card Spec.} &
21.9 &
15.6 &
\multicolumn{1}{l|}{30.3} &
\textbf{33.3} &
\multicolumn{1}{l|}{{28.1}} &
\multicolumn{1}{l|}{{{31.2}}} &
\multicolumn{1}{l}{{\textbf{\underline{34.4}}}}
\\
%\hline
\multirow{1}{*}{\textbf{Toxicology}} &
47.2 &
41.7 &
\multicolumn{1}{l|}{31.5} &
{\textbf{46.6}} &
\multicolumn{1}{l|}{{45.8}} &
\multicolumn{1}{l|}{\textbf{\underline{48.6}}} &
\multicolumn{1}{l}{43.1}
\\
%\hline
\multirow{1}{*}{\textbf{Financial}} &
43.4 &
34.0 &
\multicolumn{1}{l|}{35.2} &
33.3 &
\multicolumn{1}{l|}{{32.0}} &
\multicolumn{1}{l|}{{\textbf{35.8}}} &
\multicolumn{1}{l}{{\textbf{\underline{41.5}}}}
\\
%\hline
\textbf{Codebase Comm.} &
57.0 &
47.3 &
\multicolumn{1}{l|}{47.3} &
54.3 &
\multicolumn{1}{l|}{\textbf{55.9}} &
\multicolumn{1}{l|}{{50.5}} &
\multicolumn{1}{l}{{\textbf{\underline{59.1}}}}
\\
% \hline
\textbf{Euro. Football2} &
35.9 &
31.3 &
\multicolumn{1}{l|}{38.5} &
33.9 &
\multicolumn{1}{l|}{{35.9}} &
\multicolumn{1}{l|}{{\textbf{45.3}}} &
\multicolumn{1}{l}{{\textbf{\underline{53.1}}}}
\\
% \hline
\multirow{1}{*}{\textbf{Formula 1}} &
31.0 &
28.7 &
\multicolumn{1}{l|}{27.6} &
33.0&
\multicolumn{1}{l|}{{27.5}} &
\multicolumn{1}{l|}{\textbf{{33.3}}} &
\multicolumn{1}{l}{{\textbf{\underline{36.8}}}}
\\
%\hline
\textbf{Student Club} &
50.6 &
39.2 &
\multicolumn{1}{l|}{40.0} &
{46.3} &
\multicolumn{1}{l|}{{48.1}} &
\multicolumn{1}{l|}{\textbf{\underline{55.6}}} &
\multicolumn{1}{l}{\textbf{{53.2}}}
\\
%\hline
\multirow{1}{*}{\textbf{Superhero}} &
75.0 &
50.0 &
\multicolumn{1}{l|}{72.3} &
{75.4} &
\multicolumn{1}{l|}{{65.6}} &
\multicolumn{1}{l|}{\textbf{\underline{79.6}}} &
\multicolumn{1}{l}{{\textbf{76.6}}}
\\
\hline %\hline
\textbf{Average} &
42.1 &
32.2 &
\multicolumn{1}{l|}{35.1} &
39.0 &
\multicolumn{1}{l|}{{38.3}} &
\multicolumn{1}{l|}{{\textbf{43.6}}} &
\multicolumn{1}{l}{{\underline{\textbf{47.5}}} }
\\ \hline
\end{tabular}%
}
\end{table}
\begin{table*}[!ht]
\centering
\caption{Execution Accuracy averaged over ALL \textbf{OUT Set} of Dev Set DBs of BirdSQL;
Refer Table \ref{tab:main_results_IN} for abbreviations;
QS:\textbf{Upper-bound}; Retrievals with K = 10.}
\footnotesize
\resizebox{\textwidth}{!}{%
\begin{tabular}{l||l||lll|llllllll|llll}
\hline
\multirow{3}{*}{\textbf{LLM}} &
\multicolumn{1}{c||}{\multirow{3}{*}{\textbf{QS}}} &
\multicolumn{3}{c|}{\textbf{No Retrieval}} &
\multicolumn{8}{c|}{\textbf{ Retrieval: \emph{Whole} NLQ}} &
\multicolumn{4}{l}{\textbf{\begin{tabular}[c]{@{}l@{}}Retrieval: \emph{Decomposed} NLQ\end{tabular}}} \\ \cline{3-17}
&
\multicolumn{1}{c||}{} &
\multicolumn{1}{l|}{\textbf{No}} &
\multicolumn{2}{c|}{\textbf{All DS}} &
\multicolumn{2}{c|}{\textbf{BM25}} &
\multicolumn{2}{c|}{\textbf{BE}} &
\multicolumn{2}{c|}{\textbf{MS-M}} &
\multicolumn{2}{c|}{\textbf{STSb}} &
\multicolumn{2}{c|}{\textbf{sBSR}} &
\multicolumn{2}{c}{\textbf{SbR}} \\ \cline{4-17}
&
\multicolumn{1}{c||}{} &
\multicolumn{1}{l|}{\textbf{DS}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{Str}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\textbf{Str} \\ \hline
\textbf{\begin{tabular}[c]{@{}l@{}}Mixtral (8X7B)\end{tabular}} &
15.9 &
\multicolumn{1}{l|}{8.0} &
\multicolumn{1}{l|}{9.1} &
11.2 &
\multicolumn{1}{l|}{10.0} &
\multicolumn{1}{l|}{10.5} &
\multicolumn{1}{l|}{9.9} &
\multicolumn{1}{l|}{13.0} &
\multicolumn{1}{l|}{09.5} &
\multicolumn{1}{l|}{11.0} &
\multicolumn{1}{l|}{11.1} &
13.0 &
\multicolumn{1}{l|}{10.5} &
\multicolumn{1}{l|}{11.4} &
\multicolumn{1}{l|}{12.1} &
\textbf{14.7} \\% \hline
\textbf{\begin{tabular}[c]{@{}l@{}}Llama 3 (70B)\end{tabular}} &
23.3 &
\multicolumn{1}{l|}{10.9} &
\multicolumn{1}{l|}{13.5} &
15.4 &
\multicolumn{1}{l|}{14.6} &
\multicolumn{1}{l|}{15.3} &
\multicolumn{1}{l|}{17.5} &
\multicolumn{1}{l|}{19.6} &
\multicolumn{1}{l|}{13.8} &
\multicolumn{1}{l|}{16.6} &
\multicolumn{1}{l|}{19.3} &
21.1 &
\multicolumn{1}{l|}{15.4} &
\multicolumn{1}{l|}{17.0} &
\multicolumn{1}{l|}{20.5} &
\textbf{25.6} \\ %\hline
\textbf{\begin{tabular}[c]{@{}l@{}}SQL Coder (8B)\end{tabular}} &
24.0 &
\multicolumn{1}{l|}{13.0} &
\multicolumn{1}{l|}{14.6} &
16.7 &
\multicolumn{1}{l|}{16.2} &
\multicolumn{1}{l|}{17.0} &
\multicolumn{1}{l|}{18.3} &
\multicolumn{1}{l|}{21.0} &
\multicolumn{1}{l|}{15.4} &
\multicolumn{1}{l|}{17.8} &
\multicolumn{1}{l|}{19.3} &
23.2 &
\multicolumn{1}{l|}{17.9} &
\multicolumn{1}{l|}{18.8} &
\multicolumn{1}{l|}{22.2} &
\textbf{26.0} \\% \hline
\textbf{\begin{tabular}[c]{@{}l@{}}Gemini 1.5 Flash\end{tabular}} &
40.3 &
\multicolumn{1}{l|}{28.2} &
\multicolumn{1}{l|}{34.3} &
32.1 &
\multicolumn{1}{l|}{29.9} &
\multicolumn{1}{l|}{30.6} &
\multicolumn{1}{l|}{29.2} &
\multicolumn{1}{l|}{33.0} &
\multicolumn{1}{l|}{30.4} &
\multicolumn{1}{l|}{31.1} &
\multicolumn{1}{l|}{33.8} &
35.4 &
\multicolumn{1}{l|}{30.7} &
\multicolumn{1}{l|}{32.1} &
\multicolumn{1}{l|}{34.6} &
\textbf{38.7} \\ %\hline
\textbf{\begin{tabular}[c]{@{}l@{}}GPT 3.5 Turbo \end{tabular}} &
41.2 &
\multicolumn{1}{l|}{29.7} &
\multicolumn{1}{l|}{36.6} &
34.2 &
\multicolumn{1}{l|}{28.9} &
\multicolumn{1}{l|}{29.2} &
\multicolumn{1}{l|}{30.1} &
\multicolumn{1}{l|}{36.1} &
\multicolumn{1}{l|}{34.2} &
\multicolumn{1}{l|}{32.3} &
\multicolumn{1}{l|}{33.3} &
32.9 &
\multicolumn{1}{l|}{35.1} &
\multicolumn{1}{l|}{34.7} &
\multicolumn{1}{l|}{34.8} &
\textbf{39.5} \\ \hline
\end{tabular}%
}
\label{tab:main_results_OUT}
\end{table*}
% Database wise Result
\begin{table}[!ht]
\centering
\caption{Execution accuracy of \textbf{OUT Set} of Dev Set DBs BirdSQL by \textbf{GPT-3.5-Turbo}; Refer Table \ref{tab:gpt-in-results} for abbreviations; QS: \textbf{Upper-bound}; Retrievals with K=10.
}
\footnotesize
\resizebox{\columnwidth}{!}{%
\begin{tabular}{l||l||l|l|l|lll}
\hline
&
&
\multicolumn{1}{c|}{\textbf{No}}&
\multicolumn{2}{c|}{\textbf{All DS}} &
\multicolumn{3}{c}{\textbf{SbR}} \\ \cline{4-8}
\multirow{-2}{*}{\textbf{Database}} &
\multirow{-2}{*}{\textbf{QS}} &
\multicolumn{1}{c|}{\textbf{DS}} &
\multicolumn{1}{c|}{\textbf{NL}} &
\textbf{Str} &
\multicolumn{1}{c|}{\textbf{NL}} &
\multicolumn{1}{c|}{\textbf{L-Str}} &
\textbf{Str} \\ \hline
\textbf{Thrombosis Pred.} &
32.9 &
{20.7} &
\multicolumn{1}{l|}{19.3} &
10.8 &
\multicolumn{1}{l|}{{\textbf{24.3}}} &
\multicolumn{1}{l|}{{\textbf{\underline{25.6}}}} &
\multicolumn{1}{l}{{\textbf{\underline{25.6}}}}
\\
%\hline
\textbf{California Schools} &
24.4 &
11.1 &
\multicolumn{1}{l|}{13.0} &
{\underline{\textbf{21.7}}} &
\multicolumn{1}{l|}{{\textbf{{20.0}}}} &
\multicolumn{1}{l|}{{13.3}} &
\multicolumn{1}{l}{{\textbf{{20.0}}}}
\\
% \hline
\textbf{Card Games}&
27.5 &
14.6 &
\multicolumn{1}{l|}{19.6} &
\textbf{20.6} &
\multicolumn{1}{l|}{{17.7}} &
\multicolumn{1}{l|}{{19.7}} &
\multicolumn{1}{l}{{\textbf{\underline{26.0}}}}
\\
% \hline
\textbf{Debit Card Spec.}&
37.5 &
15.6 &
\multicolumn{1}{l|}{27.3} &
{\textbf{33.3}} &
\multicolumn{1}{l|}{{{28.1}}} &
\multicolumn{1}{l|}{15.6} &
\multicolumn{1}{l}{\textbf{\underline{34.4}}}
\\
% \hline
\textbf{Toxicology} &
32.9 &
27.0 &
\multicolumn{1}{l|}{36.5} &
{\textbf{\underline{40.5}}} &
\multicolumn{1}{l|}{{{36.9}}} &
\multicolumn{1}{l|}{35.6} &
\multicolumn{1}{l}{{{\textbf{39.7}}}}
\\
%\hline
\textbf{Financial} &
37.7 &
\textbf{26.4} &
\multicolumn{1}{l|}{25.9} &
22.2 &
\multicolumn{1}{l|}{{22.6}} &
\multicolumn{1}{l|}{{23.5}} &
\multicolumn{1}{l}{\textbf{\underline{30.2}}}
\\
% \hline
\textbf{Codebase Comm.}&
55.9 &
45.2 &
\multicolumn{1}{l|}{\textbf{\underline{48.4}}} &
45.7 &
\multicolumn{1}{l|}{{46.2}} &
\multicolumn{1}{l|}{45.1} &
\multicolumn{1}{l}{\textbf{47.3}}
\\
% \hline
\textbf{Euro. Football2}&
43.0 &
35.4 &
\multicolumn{1}{l|}{\textbf{{43.9}}} &
28.8 &
\multicolumn{1}{l|}{{35.3}} &
\multicolumn{1}{l|}{35.3} &
\multicolumn{1}{l}{{\textbf{\underline{46.2}}}}
\\
%\hline
\textbf{Formula 1} &
36.8 &
{35.6} &
\multicolumn{1}{l|}{{{{\textbf{36.8}}}}} &
{\underline{\textbf{37.5}}} &
\multicolumn{1}{l|}{{28.7}} &
\multicolumn{1}{l|}{31.0} &
\multicolumn{1}{l}{{{35.6}}}
\\
%\hline
\textbf{Student Club} &
49.4 &
43.0 &
\multicolumn{1}{l|}{\textbf{{55.0}}} &
48.8 &
\multicolumn{1}{l|}{{48.1}} &
\multicolumn{1}{l|}{43.0} &
\multicolumn{1}{l}{{\textbf{\underline{55.7}}}}
\\
%\hline
\textbf{Superhero} &
70.8 &
38.5 &
\multicolumn{1}{l|}{66.7} &
62.1 &
\multicolumn{1}{l|}{{\textbf{\underline{69.2}}}} &
\multicolumn{1}{l|}{52.3} &
\multicolumn{1}{l}{\textbf{67.7}}
\\
\hline% \hline
\multirow{1}{*}{\textbf{Average}}&
41.2 &
29.7 &
\multicolumn{1}{l|}{\textbf{36.6}} &
34.2 &
\multicolumn{1}{l|}{{34.8}} &
\multicolumn{1}{l|}{32.4} &
\multicolumn{1}{l}{\underline{\textbf{39.5}} }
\\ \hline
\end{tabular}%
}
\label{tab:gpt-out-results}
\end{table}
\begin{table}[t]
\centering
\footnotesize
\caption{Execution Accuracy of GPT-3.5-Turbo with variations in IN-OUT Sets. \textbf{DS}: Number of Union of IN Set Domain Statements averaged over Databases.}
\small
%\resizebox{\columnwidth}{!}{%
\begin{tabular}{cllll}
\hline
\textbf{\% IN-OUT Set}
&
\multicolumn{1}{c}{\textbf{50-50}} &
\multicolumn{1}{c}{\textbf{40-60}} &
\multicolumn{1}{c}{\textbf{30-70}} &
\multicolumn{1}{c}{\textbf{20-80}}
% \multicolumn{1}{c}{\textbf{10-90}}
\\ \hline
\textbf{DS}
&
\multicolumn{1}{c}{128.53} &
\multicolumn{1}{c}{108.63} &
\multicolumn{1}{c}{77.00} &
\multicolumn{1}{c}{53.54}
% \multicolumn{1}{c}{24.91}
\\ \hline
IN &
\multicolumn{1}{l}{47.51} &
\multicolumn{1}{l}{44.65} &
\multicolumn{1}{l}{46.72} &
\multicolumn{1}{l}{46.81}
%48.41
\\% \hline
OUT &
\multicolumn{1}{l}{39.48} &
\multicolumn{1}{l}{33.18} &
\multicolumn{1}{l}{32.57} &
\multicolumn{1}{l}{34.43}
% 34.80
\\ \hline
All &
\multicolumn{1}{l}{43.48} &
\multicolumn{1}{l}{37.73} &
\multicolumn{1}{l}{36.76} &
\multicolumn{1}{l}{36.86}
% 36.83
\\
\hline
\end{tabular}%
%}\
\label{tab:in-out-ratio}
\end{table}
% \begin{table*}[!ht]
% \centering
% \footnotesize
% \caption{Examples of Error Analysis. GT: Oracle DSs. The retrieved DSs, matching with the oracle DSs are coded with same color. Parts of generated SQLs are color coded in \textcolor{brown}{brown} and \textcolor{red}{red} to indicate the parts that are correct and incorrect, respectively.}
% \resizebox{\textwidth}{!}{%
% \begin{tabular}{|l|l|l|}
% \hline
% \textbf{Approach} & \textbf{Structured Domain Statements} & \textbf{SQL} \\ \hline
% %\multicolumn{3}{|l|}{\textbf{Error Category: Correct retrieval but incorrect SQLs generated}} \\ \hline
% \multicolumn{3}{|l|}{\textbf{Example 1} \hspace{15pt} \textbf{DB:} Formula 1.\hspace{15}{\textbf{Question:} Calculate the average number of oxygen atoms in single-bonded molecules.(Toxic IID Br vs SbR)}} \\ \hline
% GT &
% \begin{tabular}[c]{@{}l@{}} 1. \textcolor{blue}{`single-bonded molecules' refers to} \textcolor{blue}{bond\_type = `-'} \\ 2. \textcolor{teal}{average number of oxygen atom}
% \textcolor{teal}{= AVG(element = `o')}\end{tabular} &
% \begin{tabular}[c]{@{}l@{}} SELECT CAST(COUNT(DISTINCT CASE WHEN \\ \textcolor{brown}{T1.element = 'o'} THEN T1. molecule\_id ELSE NULL END)\\ AS REAL) / COUNT(DISTINCT T1.molecule\_id)\textcolor{brown}{FROM} \\\textcolor{brown}{atom AS T1 INNER JOIN bond AS T2} ON T1.molecule\_id \\= T2.molecule\_id WHERE \textcolor{brown}{T2.bond\_type = `-'} \end{tabular} \\ \hline
% BE-Str &
% \begin{tabular}[c]{@{}l@{}}1. \textcolor{teal}{`average number of oxygen atoms in single-bonded} \\\textcolor{teal}{ molecules' refers} \textcolor{teal}{to CAST(COUNT(DISTINCT CASE WHEN} \\\textcolor{teal}{ atom.element = `o' THEN} \textcolor{teal}{atom.molecule\_id ELSE NULL} \\ \textcolor{teal}{END) AS REAL) / COUNT(DISTINCT atom.molecule\_id)} \\ 2. \textcolor{red}{`average number of bonds the atoms have'} refers to \textcolor{red}{CAST(}\\\textcolor{red}{ COUNT(connected.bond\_id) AS REAL) / COUNT(atom.atom\_id)} \\
% 3. \textcolor{red}{`percentage of carbon in double-bond molecules' refers to} \textcolor{red}{CAST}\\ \textcolor{red}{(COUNT(DISTINCT CASE WHEN atom.element} = \textcolor{red}{'c' THEN}\\ \textcolor{red}{atom.atom\_id ELSE NULL END) AS REAL)}\\ \textcolor{red}{* 100 / COUNT} \textcolor{red}{(DISTINCT atom.atom\_id} \end{tabular} &
% \begin{tabular}[c]{@{}l@{}}SELECT CAST(COUNT(DISTINCT CASE WHEN \\ \textcolor{brown}{atom.element = 'o' AND bond.bond\_type = `-'} THEN \\ atom.molecule\_id ELSE NULL END) AS REAL) / \\ COUNT (DISTINCT atom.molecule\_id) AS \\ average\_oxygen\_atoms FROM \textcolor{brown}{atom JOIN bond} ON \\ atom.molecule\_id = bond.molecule\_id WHERE \\ \textcolor{brown}{bond.bond\_type = `-'}; \end{tabular} \\ \hline
% SbR-Str &
% \begin{tabular}[c]{@{}l@{}}1. \textcolor{teal}{`average number of oxygen atoms in single-bonded molecules'} \\ \textcolor{teal}{refers} \textcolor{teal}{to CAST(COUNT(DISTINCT CASE WHEN atom.element} \\ \textcolor{teal}{ = 'o' THEN} \textcolor{teal}{atom.molecule\_id ELSE NULL END) AS REAL)} \\ \textcolor{teal}{ / COUNT(DISTINCT atom.molecule\_id)} \\ 2. \textcolor{blue}{`single-bonded molecules' refers to bond.bond\_type = `-'} \\ 3. \textcolor{teal}{`oxygen' refers to atom.element = `o'} \end{tabular} &
% \begin{tabular}[c]{@{}l@{}}SELECT CAST(COUNT(DISTINCT CASE WHEN \\ \textcolor{brown}{atom.element = `o'} THEN atom.molecule\_id ELSE NULL \\END) AS REAL) / COUNT\ (DISTINCT atom.molecule\_id) \\FROM \textcolor{red}{atom JOIN molecule ON} \textcolor{red}{ atom.molecule\_id = molecule.} \\ \textcolor{red}{molecule\_id JOIN bond ON} atom.molecule\_id = bond.molecule \\ \_id WHERE \textcolor{brown}{bond.bond\_type = `-'} AND \textcolor{brown}{atom.element = `o'}; \end{tabular} \\ \hline
% \multicolumn{3}{|l|}{\textbf{Example 2} \hspace{15pt} \textbf{DB:} Formula 1.\hspace{15}{\textbf{Question:} List circuits which host 4 f1 races from year 990 to 2000.}} \\ \hline
% GT &
% \begin{tabular}[c]{@{}l@{}} 1. \textcolor{teal}{`from year 1990 to 2000 refers to year(date)} \\ \textcolor{teal}{between 1990 and 2000'}\\ 2. \textcolor{blue}{`did not host any f1 races refers to COUNT} \textcolor{blue}{(raceId) = 0'}\end{tabular} &
% \begin{tabular}[c]{@{}l@{}}SELECT DISTINCT T1.name FROM circuits AS T1 INNER \\ JOIN races AS T2 ON T2.circuitID = T1.circuitId WHERE \\ \textcolor{brown}{STRFTIME('\%Y', T2.date) BETWEEN '1990' AND '2000'} \\ GROUP BY T1.name HAVING \textcolor{brown}{ COUNT(T2.raceId) = 4}\end{tabular} \\ \hline
% SbR-Str &
% \begin{tabular}[c]{@{}l@{}}1. \textcolor{red}{`In which Formula\_1 race' refers to }\textcolor{red}{DISTINCT races.name} \\2. \textcolor{teal}{`From (100) to (100)' refers to race.year }\\ \textcolor{teal}{BETWEEN 100 AND 100} \\ \\\end{tabular} &
% \begin{tabular}[c]{@{}l@{}}
% SELECT circuits.name FROM circuits JOIN races ON circuits.\\
% circuitId=races.circuitId JOIN seasons ON races.year=seasons.\\year
% WHERE \textcolor{brown}{seasons.year} \textcolor{brown}{BETWEEN 1990 AND 2000} GROUP\\
% BY circuits.name HAVING \textcolor{red}{COUNT(DISTINCT} \textcolor{red}{races.name) = 4}\end{tabular} \\ \hline
% %\multicolumn{3}{|l|}{\textbf{Error Category: ALL-DS-Str and ALL-DS-NL generates SQLs which lead to correct output but are semantically incorrect}} \\ \hline
% \multicolumn{3}{|l|}{\textbf{Example 3} \hspace{15pt} \textbf{DB:} Toxicology.\hspace{15}{\textbf{Question:} Enumerate the bond ID of triple bond type molecules and tell me if they are carcinogenic or not.}} \\ \hline
% GT &
% \begin{tabular}[c]{@{}l@{}}
% 1. \textcolor{teal}{triple bond refers to bond\_type = `\#'} \\ 2. \textcolor{blue}{label = '+' mean molecules are carcinogenic} \\ 3. \textcolor{red}{label = '-' means molecules are non-carcinogenic} \end{tabular} &
% \begin{tabular}[c]{@{}l@{}} \textcolor{brown}{SELECT DISTINCT T1.bond\_id} , T2.label FROM bond \\ AS T1 INNER JOIN molecule AS T2 ON T1.molecule\_id \\ = T2.molecule\_id WHERE T1.bond\_type = `\#' \end{tabular} \\ \hline
% All-DS-Str&
% \begin{tabular}[c]{@{}l@{}} Not Applicable \end{tabular} &
% \begin{tabular}[c]{@{}l@{}}\textcolor{red}{SELECT bond.bond\_id}, molecule.label FROM bond JOIN \\ molecule ON bond.molecule\_id = molecule.molecule\_id \\ WHERE bond.bond\_type = `\#'; \end{tabular} \\ \hline
% SbR-Str &
% \begin{tabular}[c]{@{}l@{}} 1. \textcolor{teal}{`triple bond' refers to bond.bond\_type = `\#'} \\ 2. \textcolor{blue}{'carcinogenic' refers to molecule.label = `+'} \\ 3. \textcolor{teal}{`molecules with a triple bond type' refers to } \textcolor{teal}{bond.bond\_type = '\#'} \\ %4. \textcolor{teal}{`molecules with triple bonds' refers} \\ \textcolor{teal}{to bond.bond\_type = `\#'} \\
% 4. \textcolor{blue}{`carcinogenic-type molecules' refers to } \\ \textcolor{blue}{molecules with molecule.label = `+'} %\\ 6. \textcolor{blue}{`molecules containing carcinogenic} \\ \textcolor{blue}{compounds' refers to molecules with} \\ \textcolor{blue}{molecule.label = `+'}
% \end{tabular} &
% \begin{tabular}[c]{@{}l@{}}\textcolor{red}{SELECT bond\_id}, molecule.label FROM bond JOIN molecule \\ ON bond.molecule\_id = molecule.molecule\_id WHERE \\ bond.bond\_type = `\#' AND \textcolor{red}{molecule.label = `+'}; \end{tabular}\\ \hline
% \end{tabular}
% }
% \label{tab:error_analysis}
% \end{table*}
% Add this to your document preamble if you haven't already:
% \usepackage{makecell}
\begin{table*}[!ht]
\centering
\footnotesize
\caption{Examples of Error Analysis. GT: Oracle DSs. The retrieved DSs, matching with the oracle DSs are coded with same color. Parts of generated SQLs are color coded in \textcolor{brown}{brown} and \textcolor{red}{red} to indicate the parts that are correct and incorrect, respectively.}
\resizebox{\textwidth}{!}{%
\begin{tabular}{|l|l|l|}
\hline
\textbf{Approach} & \textbf{Structured Domain Statements} & \textbf{SQL} \\ \hline
\multicolumn{3}{|l|}{\textbf{Example 1} \hspace{15pt} \textbf{DB:} Toxicology.\hspace{15pt}{\textbf{Question:} Calculate the average number of oxygen atoms in single-bonded molecules.(Toxic IID Br vs SbR)}} \\ \hline
GT &
\makecell[l]{1. \textcolor{blue}{`single-bonded molecules' refers to} \textcolor{blue}{bond\_type = `-'} \\ 2. \textcolor{teal}{average number of oxygen atom}
\textcolor{teal}{= AVG(element = `o')}} &
\makecell[l]{SELECT CAST(COUNT(DISTINCT CASE WHEN \\ \textcolor{brown}{T1.element = 'o'} THEN T1. molecule\_id ELSE NULL END)\\ AS REAL) / COUNT(DISTINCT T1.molecule\_id)\textcolor{brown}{FROM} \\\textcolor{brown}{atom AS T1 INNER JOIN bond AS T2} ON T1.molecule\_id \\= T2.molecule\_id WHERE \textcolor{brown}{T2.bond\_type = `-'} } \\ \hline
BE-Str &
\makecell[l]{1. \textcolor{teal}{`average number of oxygen atoms in single-bonded} \\\textcolor{teal}{ molecules' refers} \textcolor{teal}{to CAST(COUNT(DISTINCT CASE WHEN} \\\textcolor{teal}{ atom.element = `o' THEN} \textcolor{teal}{atom.molecule\_id ELSE NULL} \\ \textcolor{teal}{END) AS REAL) / COUNT(DISTINCT atom.molecule\_id)} \\ 2. \textcolor{red}{`average number of bonds the atoms have'} refers to \textcolor{red}{CAST(}\\\textcolor{red}{ COUNT(connected.bond\_id) AS REAL) / COUNT(atom.atom\_id)} \\
3. \textcolor{red}{`percentage of carbon in double-bond molecules' refers to} \textcolor{red}{CAST}\\ \textcolor{red}{(COUNT(DISTINCT CASE WHEN atom.element} = \textcolor{red}{'c' THEN}\\ \textcolor{red}{atom.atom\_id ELSE NULL END) AS REAL)}\\ \textcolor{red}{* 100 / COUNT} \textcolor{red}{(DISTINCT atom.atom\_id}} &
\makecell[l]{SELECT CAST(COUNT(DISTINCT CASE WHEN \\ \textcolor{brown}{atom.element = 'o' AND bond.bond\_type = `-'} THEN \\ atom.molecule\_id ELSE NULL END) AS REAL) / \\ COUNT (DISTINCT atom.molecule\_id) AS \\ average\_oxygen\_atoms FROM \textcolor{brown}{atom JOIN bond} ON \\ atom.molecule\_id = bond.molecule\_id WHERE \\ \textcolor{brown}{bond.bond\_type = `-'};} \\ \hline
SbR-Str &
\makecell[l]{1. \textcolor{teal}{`average number of oxygen atoms in single-bonded molecules'} \\ \textcolor{teal}{refers} \textcolor{teal}{to CAST(COUNT(DISTINCT CASE WHEN atom.element} \\ \textcolor{teal}{ = 'o' THEN} \textcolor{teal}{atom.molecule\_id ELSE NULL END) AS REAL)} \\ \textcolor{teal}{ / COUNT(DISTINCT atom.molecule\_id)} \\ 2. \textcolor{blue}{`single-bonded molecules' refers to bond.bond\_type = `-'} \\ 3. \textcolor{teal}{`oxygen' refers to atom.element = `o'}} &
\makecell[l]{SELECT CAST(COUNT(DISTINCT CASE WHEN \\ \textcolor{brown}{atom.element = `o'} THEN atom.molecule\_id ELSE NULL \\END) AS REAL) / COUNT\ (DISTINCT atom.molecule\_id) \\FROM \textcolor{red}{atom JOIN molecule ON} \textcolor{red}{ atom.molecule\_id = molecule.} \\ \textcolor{red}{molecule\_id JOIN bond ON} atom.molecule\_id = bond.molecule \\ \_id WHERE \textcolor{brown}{bond.bond\_type = `-'} AND \textcolor{brown}{atom.element = `o'};} \\ \hline
\multicolumn{3}{|l|}{\textbf{Example 2} \hspace{15pt} \textbf{DB:} Formula 1.\hspace{15pt}{\textbf{Question:} List circuits which host 4 f1 races from year 990 to 2000.}} \\ \hline
GT &
\makecell[l]{1. \textcolor{teal}{`from year 1990 to 2000 refers to year(date)} \\ \textcolor{teal}{between 1990 and 2000'}\\ 2. \textcolor{blue}{`did not host any f1 races refers to COUNT} \textcolor{blue}{(raceId) = 0'}} &
\makecell[l]{SELECT DISTINCT T1.name FROM circuits AS T1 INNER \\ JOIN races AS T2 ON T2.circuitID = T1.circuitId WHERE \\ \textcolor{brown}{STRFTIME('\%Y', T2.date) BETWEEN '1990' AND '2000'} \\ GROUP BY T1.name HAVING \textcolor{brown}{ COUNT(T2.raceId) = 4}} \\ \hline
SbR-Str &
\makecell[l]{1. \textcolor{red}{`In which Formula\_1 race' refers to }\textcolor{red}{DISTINCT races.name} \\2. \textcolor{teal}{`From (100) to (100)' refers to race.year }\\ \textcolor{teal}{BETWEEN 100 AND 100} \\} &
\makecell[l]{
SELECT circuits.name FROM circuits JOIN races ON circuits.\\
circuitId=races.circuitId JOIN seasons ON races.year=seasons.\\year
WHERE \textcolor{brown}{seasons.year} \textcolor{brown}{BETWEEN 1990 AND 2000} GROUP\\
BY circuits.name HAVING \textcolor{red}{COUNT(DISTINCT} \textcolor{red}{races.name) = 4}} \\ \hline
\multicolumn{3}{|l|}{\textbf{Example 3} \hspace{15pt} \textbf{DB:} Toxicology.\hspace{15pt}{\textbf{Question:} Enumerate the bond ID of triple bond type molecules and tell me if they are carcinogenic or not.}} \\ \hline
GT &
\makecell[l]{
1. \textcolor{teal}{triple bond refers to bond\_type = `\#'} \\ 2. \textcolor{blue}{label = '+' mean molecules are carcinogenic} \\ 3. \textcolor{red}{label = '-' means molecules are non-carcinogenic}} &
\makecell[l]{ \textcolor{brown}{SELECT DISTINCT T1.bond\_id} , T2.label FROM bond \\ AS T1 INNER JOIN molecule AS T2 ON T1.molecule\_id \\ = T2.molecule\_id WHERE T1.bond\_type = `\#'} \\ \hline
All-DS-Str&
\makecell[l]{ Not Applicable } &
\makecell[l]{\textcolor{red}{SELECT bond.bond\_id}, molecule.label FROM bond JOIN \\ molecule ON bond.molecule\_id = molecule.molecule\_id \\ WHERE bond.bond\_type = `\#';} \\ \hline
SbR-Str &
\makecell[l]{ 1. \textcolor{teal}{`triple bond' refers to bond.bond\_type = `\#'} \\ 2. \textcolor{blue}{'carcinogenic' refers to molecule.label = `+'} \\ 3. \textcolor{teal}{`molecules with a triple bond type' refers to } \textcolor{teal}{bond.bond\_type = '\#'} \\
4. \textcolor{blue}{`carcinogenic-type molecules' refers to } \\ \textcolor{blue}{molecules with molecule.label = `+'}} &
\makecell[l]{\textcolor{red}{SELECT bond\_id}, molecule.label FROM bond JOIN molecule \\ ON bond.molecule\_id = molecule.molecule\_id WHERE \\ bond.bond\_type = `\#' AND \textcolor{red}{molecule.label = `+'};} \\ \hline
\end{tabular}%
}
\label{tab:error_analysis}
\end{table*}
\subsection{Results}\label{sec:results}
We call our Sub-string based Retrieval as \textbf{SbR}.
We answer the following research questions (RQ):
\\
\textbf{RQ1: How effective are DSs for the NL-to-SQL task?}
Comparing No DS to QS, across LLMs, Sets and DBs (Tables \ref{tab:gpt-in-results} and \ref{tab:gpt-out-results}) we show the benefit for having DSs relevant to the query in context for SQL generation. %For the IN Split, we observe larger increments in the average accuracy from No DS to QS across all LLMs (Table \ref{tab:main_results_IN}) and DBs (Table \ref{tab:gpt-in-results}). This is expected, because the DS repository is derived from IN split DS. We \textcolor{red}{also}\sout{further} experiment with different proportions of queries in the IN and OUT splits (Details in Appendix \ref{sec:in-out}), leading to variations in the size of the DS repository, with the inclusion of only IN split DSs. \textcolor{red}{Finally, we compute results for the OUT-NO set with our best performing model, GPT 3.5 Turbo, leading to an increase from 30.1\%(No DS) to 36.6\% (SbR-Str). In all scenarios, we consistently} observe a drop in the overall accuracy with fewer DSs in the repository, thus demonstrating their need.
\\
\textbf{RQ2: Does our retrieval based approach yield comparable performance to when we have oracle DSs for a query? }
For the IN Set, with availability of the required DSs, our SbR-Str approach performs better than QS, for 9 out of 11 DBs (Table \ref{tab:gpt-in-results}) and 3 out of 5 LLMs (Table \ref{tab:main_results_IN}). For the remaining DBs and LLMs our approach's performance is comparable. The better performance is the result of: (i) DBA rectification of erroneous LLM structured DSs, % we observe that erroneous oracle statements
(examples in Table \ref{tab:Error-DS}), %are rectified
and (ii) retrieval of non oracle yet relevant top-4 DSs, boosting the performance for queries with missing and insufficient oracle DSs. As expected, for the OUT Set, with only partial availability of required DSs, our SbR-Str performance is inferior to QS for 3 out of 5 LLMs (Table \ref{tab:main_results_OUT}) and 8 out of 11 DBs (Table \ref{tab:gpt-out-results}). Here, QS acts as the upper-bound. For the OUT-NO Set our best performing GPT-3.5-Turbo model yields 36.6\% accuracy with SbR-Str, 30.1\% with No DS and 41.7\% with QS. With no availability of DSs the drop in the accuracy of SbR-Str over QS for OUT-NO Set is more (5.1\%) as compared to the OUT Set with partial availability of DSs (1.7\%). Thus, the proposed method (SbR-Str) is designed to harness existing DSs in the repository, relevant to user queries (IN set), but is not intended to aid queries with no relevant DSs in the repository (OUT sets). However, non-trivial performance gains of SbR-Str over QS for the IN set establishes the efficacy of our core novelty in terms of (i) DS Structuring, and (ii) sub-string based retrieval.\\
\textbf{RQ3: Is retrieving relevant domain statements better than providing all DSs?}
For both the IN and OUT Sets and all LLMs, QS and our SbR-Str performance is consistently better than All-DS/NL and All-DS/Str (Tables \ref{tab:main_results_IN} and \ref{tab:main_results_OUT}).
For the IN Set, SbR-Str performs better than All-DS/NL and ALL-DS/Str across almost all DBs (Table \ref{tab:gpt-in-results}). This shows that even if the prompt size is not a concern, having relevant DSs in-context is important. Extensive manual inspection for the incorrect samples resulting from the SQLs generated with SbR-Str retrieved DSs in-context, but correct answers with All-DS/Str demonstrates that most of the failure cases fall in one of the three categories: (i) Retrieved DSs with SbR-Str are relevant to the NL query, however the LLM generates incorrect SQLs with these correct DSs in the context. For e.g., the SQL generated for Example 3 (Table \ref{tab:error_analysis}) with SbR-Str is incorrect though the retrievals are correct. It uses an additional constraint of molecule.label = `+' instead of projecting the labels of the molecule as the part of SELECT clause. (ii) The generated SQLs with All-DS-Str are semantically wrong, but coincidentally lead to the correct answer. For the same example, the All-DS-Str approach leads to incorrect SQL without the use of the keyword DISTINCT, but coincidentally gives the same answer as ground truth SQL, (iii) The generated SQLs by SbR-Str are incorrect due to wrong retrievals. In Example 2(Table \ref{tab:error_analysis}) our SbR-Str approach retrieves an incorrect structured DS due to the high similarity of its `text' part (`In which formula\_1 race') with the query sub-string (`which host 4 f1 races'). This leads to the generation of incorrect SQL wrongly clued to utilize the `races.name' from the retrieved DS in the HAVING clause, as opposed to `races.raceId'.
As expected, for the OUT Set, we observe that All-DS/Str and All-DS/NL performs better than SbR-Str for 3 (California School, Toxicology, Formula 1)
and 2 (Codebase Community, Formula 1)
out of 11 DBs, respectively. However, For the other DBs, better performance of SbR-Str is due to retrieval of the relevant DSs with higher recall (K=10), for the queries having partial DS overlap, and missing or in-correct DSs, which get overlooked during overlap computations (\S~\ref{sec:dataset}).
\\
\textbf{RQ4: What is the benefit of structuring DSs?}
For the IN Set, with all LLMs, all the methods perform better with DBA scanned structured (Str) DSs, as opposed to when NL DSs in context (Table \ref{tab:main_results_IN}).
We observe the same for the OUT Set, except for GPT-3.5-turbo LLM, for 3 retrieval methods (Table \ref{tab:main_results_OUT}).
For the IN Set, SbR-L-Str is inferior to SbR-Str due to error propagation because of noisy LLM generated structured DSs. However, for 10 out of 11 DBs, it performs better than SbR-NL (Table \ref{tab:gpt-in-results}). This demonstrates the effectiveness of LLM based Structuring, allowing complete automation and handling variations in the NL DSs across DBs.
On the other hand, for the OUT Set, we observe inferior performance of SbR-L-Str over SbR-NL for 5 out of 11 DBs. However, after DBA screening, SbR-Str demonstrates better performance than SbR-NL for 10 out of 11 DBs (Table \ref{tab:gpt-out-results}). This demonstrates that the queries with unavailability of all the required DSs, need non-erroneous, DBA scanned structured DSs in context, to achieve performance comparable to the queries with availability of DSs.
\\
\textbf{RQ5: How important is it to decompose a user query into sub-strings when matching with DSs?}
Our SbR-Str approach, which retrieves using semantic match with NL query sub-strings performs better than all the four baselines which use the \emph{whole} query for matching, across LLMs and Sets (Tables \ref{tab:main_results_IN} and \ref{tab:main_results_OUT}). We observe similar trend with Evidence F1 scores with structured DSs (Str) (BM25: 0.35, BE: 0.33, MS-M: 0.34, STSb: 0.37, sBSR: 0.35, SbR: 0.39). At inference time, we can not differentiate between IN and OUT queries. The performance of SbR-Str with DSs retrieved using consistent K = 10 across the sets (42.6), is the best surpassing the baselines (QS: 41.7, NO-DS: 30.9, All-DS-NL: 35.8, All-DS-Str: 36.6, BM25-Str: 37.3, MSM-Str: 38.4, sBSR-Str: 38.0, BE-Str: 31.4, STSb-Str: 38.4, SbR-NL: 36.6 and SbR-L-Str: 36.5). This shows the importance of query decomposition. Superior performance of SbR-Str approach to sBSR, treating each word of the query as its decomposition, demonstrates efficacy of our sub-strings based query decomposition strategy.
%Overall evidence F1 scores are low, because, though for computation of F1 we consider top-K retrieved DSs, where K is the number of oracle DSs for each query; for SQL generation, we consider top-4 retrieved DSs, which can be higher than the number of ground truth DSs. This allows us to cover missing oracle DSs in context for SQL generation. Also, with DBA scanning we observe erroneous oracle DSs getting rectified leading to good results.
For IN Sets of 4 DBs (Debit Card Specializing, Toxicology, Formula 1 and Student Club) and OUT Sets of 3 DBs (Card Games, Codebase Community and Formula 1) one of the other retrieval baselines (mostly BE-Str) performs better than SbR-Str. We perform an extensive analysis for the samples for which we do not get correct answers from the SQLs generated using our approach (SbR-Str). We find that the errors belongs to the 3 categories explained as the part of discussion of \textbf{RQ3}. For e.g., the structured DSs retrieved with our SbR-Str based approach of the Example 1(Table \ref{tab:error_analysis}), match with the oracle DSs, yet the SQL generated has a unnecessary JOIN on `molecule' table, which makes the SQL query incorrect. Note that the retrieval done using SbR-Str is better than that with the embedding base (BE-Str) approach, where some of the retrieved DSs do not match with oracle DSs. However, in this case, the SQL generated with BE-Str is semantically wrong but coincidentally leads to the correct answer. \\
\textbf{RQ6: How does the size of the DS repository affects the performance.}
As discussed in Section \ref{sec:dataset}, we assume 50-50 IN-OUT \% splits. However, to check the effect of availability of distinct size of the domain repository, we create distinct IN-OUT \% splits. %To create these splits, we remove IN Set queries and add them to OUT Set, maintaining the distinction in the compositional distributions. We consider availability of DSs belonging to newly generated IN Set queries only.
Table \ref{tab:in-out-ratio} demonstrates the performance of our SbR-Str approach with distinct IN-OUT Sets with the best performing GPT-3.5-Turbo LLM. The number of DSs in the repository of a DB decreases with the decreases in the \% IN Set. As expected this negatively affects the overall performance, especially due to decrease in the performance of the OUT Set, with fewer available DS in the repository. However, IN Set performance remains consistent, due to availability of DSs for all the queries in that set. This also underscores the effectiveness of DSs for the NL-to-SQL task. %The drop in the performance is more when the IN split is reduced to 40\% from 50\%, however later performance almost remains consistent.
% \begin{table*}[!ht]
% \centering
% \resizebox{\textwidth}{!}{%
% \begin{tabular}{|llllll|}
% \hline
% \multicolumn{1}{|l|}{\begin{tabular}[c]{@{}l@{}} \textbf{Sr.}\\ \textbf{No.}\end{tabular}} &
% \multicolumn{1}{|l|}{\begin{tabular}[c]{@{}l@{}} \textbf{DB}\end{tabular}} &
% \multicolumn{1}{l|}{\textbf{Question}} &
% \multicolumn{1}{|l|}{\begin{tabular}[c]{@{}l@{}} \textbf{Appr-}\\ \textbf{-oach}\end{tabular}} &
% \multicolumn{1}{l|}{\textbf{Templatized Domain Statements}} &
% \textbf{SQL} \\ \hline
% \multicolumn{6}{|l|}{\textbf{Error Category: Correct retrieval but incorrect SQLs generated}} \\ \hline
% \multicolumn{6}{|l|}{\textbf{DB:} Formula 1.\hspace{20}{\textbf{Question:} Calculate the average number of oxygen atoms in single-bonded molecules.(Toxic IID Br vs SbR)}} \\ \hline
% \multicolumn{1}{|l|}{\multirow{20}{*}{1}} &
% \multicolumn{1}{|l|}{\multirow{20}{*}{\begin{turn}{}Formula 1\end{turn}}} &
% \multicolumn{1}{l|}{\multirow{20}{*}{\begin{tabular}[c]{@{}l@{}}Calculate the \\ average number \\ of oxygen \\ atoms in \\ single-bonded\\ molecules. \\ (Toxic \\ IID Br vs SbR) \end{tabular}}} &
% \multicolumn{1}{l|}{GT} &
% \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}} 1. \textcolor{blue}{`single-bonded molecules' refers to bond\_type = `-'} \\ 2. \textcolor{teal}{average number of oxygen atom = AVG(element = `o');} \end{tabular}} &
% \begin{tabular}[c]{@{}l@{}} SELECT CAST(COUNT(DISTINCT \\ CASE WHEN \textcolor{brown}{T1.element = 'o'} \\ THEN T1.molecule\_id ELSE NULL \\ END) AS REAL) / COUNT(DISTINCT \\ T1.molecule\_id) \textcolor{brown}{FROM atom AS T1} \\ \textcolor{brown}{INNER JOIN bond AS T2} ON \\ T1.molecule\_id = T2.molecule\_id \\ WHERE \textcolor{brown}{T2.bond\_type = `-'}\end{tabular} \\ \cline{4-6}
% \multicolumn{1}{|l|}{} &
% \multicolumn{1}{|l|}{} &
% \multicolumn{1}{l|}{} &
% \multicolumn{1}{|l|}{\begin{tabular}[c]{@{}l@{}} BE-Str \end{tabular}} &
% \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}1. \textcolor{teal}{`average number of oxygen atoms in single-bonded} \\ \textcolor{teal}{molecules' refers} \textcolor{teal}{to CAST(COUNT(DISTINCT CASE} \\ \textcolor{teal}{WHEN atom.element = `o' THEN} \textcolor{teal}{atom.molecule\_id ELSE} \\ \textcolor{teal}{NULL END) AS REAL) / COUNT(DISTINCT} \\ \textcolor{teal}{atom.molecule\_id)} \\ 2. \textcolor{red}{`average number of bonds the atoms have'} refers to \\ \textcolor{red}{CAST(COUNT} \textcolor{red}{ (connected.bond\_id) AS REAL) / COUNT}\\ \textcolor{red}{(atom.atom\_id)} \\
% 3. \textcolor{red}{`percentage of carbon in double-bond molecules' refers} \\ \textcolor{red}{to CAST} \textcolor{red}{(COUNT(DISTINCT CASE WHEN} \\ \textcolor{red}{atom.element} = \textcolor{red}{'c' THEN} \textcolor{red}{atom.atom\_id ELSE} \\ \textcolor{red}{NULL END) AS REAL) * 100 / COUNT} \textcolor{red}{(DISTINCT} \\ \textcolor{red}{atom.atom\_id)} \\\end{tabular}} &
% \begin{tabular}[c]{@{}l@{}}SELECT CAST(COUNT(DISTINCT \\ CASE WHEN \textcolor{brown}{atom.element = 'o' AND} \\ \textcolor{brown}{bond.bond\_type = `-'} THEN \\ atom.molecule\_id ELSE NULL END) \\ AS REAL) / COUNT (DISTINCT \\ atom.molecule\_id) AS \\ average\_oxygen\_atoms FROM \textcolor{brown}{atom \\ JOIN bond} ON atom.molecule\_id = \\ bond.molecule\_id WHERE \\ \textcolor{brown}{bond.bond\_type = `-'}; \end{tabular} \\ \cline{4-6}
% \multicolumn{1}{|l|}{} &
% \multicolumn{1}{l|}{} &
% \multicolumn{1}{|l|}{} &
% \multicolumn{1}{l|}{SbR-Str} &
% \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}1. \textcolor{teal}{`average number of oxygen atoms in single-bonded} \\ \textcolor{teal}{molecules' refers} \textcolor{teal}{to CAST(COUNT(DISTINCT CASE} \\ \textcolor{teal}{WHEN atom.element = 'o' THEN} \textcolor{teal}{atom.molecule\_id} \\ \textcolor{teal}{ELSE NULL END) AS REAL) / COUNT(DISTINCT} \\ \textcolor{teal}{atom.molecule\_id)} \\ 2. \textcolor{blue}{`single-bonded molecules' refers to bond.bond\_type = `-'} \\ 3. \textcolor{teal}{`oxygen' refers to atom.element = `o'} \\
% \end{tabular}} &
% \begin{tabular}[c]{@{}l@{}}SELECT CAST(COUNT(DISTINCT \\ CASE WHEN \textcolor{brown}{atom.element = `o'} \\ THEN atom.molecule\_id ELSE \\ NULL END) AS REAL) / COUNT\\\ (DISTINCT atom.molecule\_id) \\ FROM \textcolor{red}{atom JOIN molecule ON} \\ \textcolor{red}{ atom.molecule\_id =} \\ \textcolor{red}{molecule.molecule\_id JOIN bond} \\ ON atom.molecule\_id = \\ bond.molecule\_id WHERE \\ \textcolor{brown}{bond.bond\_type = `-'} AND \\ \textcolor{brown}{atom.element = `o'}; \end{tabular} \\ \hline
% % \multicolumn{1}{|l|}{\multirow{6}{*}{2}} &
% % \multicolumn{1}{|l|}{\multirow{6}{*}{\begin{turn}{90}Toxicology\end{turn}}} &
% % \multicolumn{1}{l|}{\multirow{6}{*}{\begin{tabular}[c]{@{}l@{}}Give me \\ the molecule\\ ID of the \\ double bond \\ type. \end{tabular}}} &
% % \multicolumn{1}{l|}{GT} &
% % \multicolumn{1}{l|}{1. \textcolor{teal}{`double bond' refers to bond\_type = '='} } &
% % \begin{tabular}[c]{@{}l@{}}\textcolor{brown}{SELECT DISTINCT Str.molecule\_id} \\ FROM bond AS Str WHERE \\ \textcolor{brown}{Str.bond\_type = `='} \end{tabular} \\ \cline{4-6}
% % \multicolumn{1}{|l|}{} &
% % \multicolumn{1}{l|}{} &
% % \multicolumn{1}{|l|}{} &
% % \multicolumn{1}{l|}{All-DS-Str} &
% % \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}} Not Applicable \end{tabular}} &
% % \begin{tabular}[c]{@{}l@{}}\textcolor{brown}{SELECT DISTINCT molecule\_id} \\ FROM bond WHERE \\ \textcolor{brown}{bond\_type = `='}; \end{tabular} \\ \cline{4-6}
% % \multicolumn{1}{|l|}{} &
% % \multicolumn{1}{|l|}{} &
% % \multicolumn{1}{l|}{} &
% % \multicolumn{1}{l|}{SbR-Str} &
% % \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}1. \textcolor{teal}{`double bond’ refers to bond.bond\_type = `='} \\
% % 2. \textcolor{teal}{`not having double bond' refers to bond\_type <> `='} \\ 3. \textcolor{teal}{`type of bond' refers to bond.bond\_type} \end{tabular}} &
% % \begin{tabular}[c]{@{}l@{}} \textcolor{red}{SELECT molecule\_id} FROM bond \\ WHERE \textcolor{brown}{bond\_type = `='}; \end{tabular} \\ \hline
% % \multicolumn{1}{|l|}{\multirow{12}{*}{3}} &
% % \multicolumn{1}{|l|}{\multirow{12}{*}{\begin{turn}{90}Codebase Community\end{turn}}} &
% % \multicolumn{1}{l|}{\multirow{12}{*}{\begin{tabular}[c]{@{}l@{}}Which user \\ has a \\ higher \\ reputation, \\ Harlan or\\ Jarrod Dixon?\end{tabular}}} &
% % \multicolumn{1}{l|}{GT} &
% % \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}1. \textcolor{teal}{`Harlan' and `Jarrod Dixon' are both DisplayName'}\\ 2. \textcolor{blue}{`highest reputation refers to Max(Reputation)'}\end{tabular}} &
% % \begin{tabular}[c]{@{}l@{}}SELECT DisplayName FROM \\ users WHERE \textcolor{brown}{DisplayName} \\ \textcolor{brown}{IN (`Harlan', `Jarrod Dixon')} \\ AND Reputation = ( SELECT \\ \textcolor{brown}{MAX(Reputation)} FROM \\ users WHERE \textcolor{brown}{DisplayName IN} \\ \textcolor{brown}{('Harlan', 'Jarrod Dixon'))}\end{tabular} \\ \cline{4-6}
% % \multicolumn{1}{|l|}{} &
% % \multicolumn{1}{l|}{} &
% % \multicolumn{1}{|l|}{} &
% % \multicolumn{1}{|l|}{\begin{tabular}[c]{@{}l@{}} All-DS-NL\end{tabular}} &
% % \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}} Not Applicable \end{tabular}} &
% % \begin{tabular}[c]{@{}l@{}}SELECT DisplayName FROM \\users WHERE \textcolor{brown}{DisplayName} \\ \textcolor{brown}{= `Harlan' OR DisplayName}\\ \textcolor{brown}{= `Jarrod Dixon'} \textcolor{brown}{ORDER BY} \\ \textcolor{brown}{Reputation DESC LIMIT 1};\end{tabular} \\ \cline{4-6}
% % \multicolumn{1}{|l|}{} &
% % \multicolumn{1}{l|}{} &
% % \multicolumn{1}{|l|}{} &
% % \multicolumn{1}{l|}{SbR-Str} &
% % \multicolumn{1}{l|}{\begin{tabular}[c]{@{}l@{}}
% % 1. \textcolor{blue}{`Who has the highest reputation' refers to ORDER BY} \\ \textcolor{blue}{users.Reputation DESC LIMIT 1} \\ 3. \textcolor{teal}{`the user csgillespie owns' refers to users.DisplayName =} \\ \textcolor{teal}{`csgillespie'} \\ 4. \textcolor{teal}{`votes made by Harlan' refers to votes.DisplayName =} \\ \textcolor{teal}{`Harlan'} \\ 5. \textcolor{teal}{`name of users' refers to users.DisplayName} \\
% % 6. \textcolor{teal}{`the user Samuel' refers to users.UserDisplayName = \\`Samuel'} \\
% % ...\\
% % \end{tabular}} &
% % \begin{tabular}[c]{@{}l@{}}SELECT DisplayName
% % FROM \\ users WHERE \textcolor{red}{[DisplayName} \\ \textcolor{red}{IN (`Harlan', `Jarrod Dixon')} \\ \textcolor{red}{AND]} Reputation = (SELECT \\ \textcolor{brown}{MAX(Reputation)} FROM users \\ WHERE \textcolor{brown}{DisplayName = `Harlan'} \\ \textcolor{brown}{OR DisplayName = `Jarrod Dixon'})\end{tabular} \\ \hline
% \end{tabular}%
% }
% \caption{Error Analysis Part 1: Categories of errors where our approach (SbR-Str) generates SQLs which give incorrect answers and All-DS-Str or NL or other retrieval technique such as BE-Str generates SQL which gives correct answer. GT: Ground Truth / oracle DSs.
% The retrieved DSs, matching with the oracle DSs are coded with same color. Parts of generated SQLs are color coded in \textcolor{brown}{brown} and \textcolor{red}{red} to indicate the parts that are correct and incorrect, respectively. Parts of SQLs inside \textcolor{red}{[ ]} indicate necessary missing parts.}
% \label{tab:error_analysis}
% \end{table*}
%\section{Detailed Error Analysis}\label{sec:err_analysis}
%\textcolor{red}{\sout{As discussed in Research Question \textbf{RQ3} in Section \ref{sec:results} for OUT Set of some DBs we observe that All-DS-NL and Str performs better than our SbR-Str. Moreover, for IN Sets of 5 DBs (Debit Card Specializing, Toxicology, Codebase Community Formula 1 and Student Club) (Table \ref{tab:gpt-results_IN}) and OUT Sets of 3 DBs (Card Games, Codebase Community and Formula 1) (Table \ref{tab:gpt-results_OUT}) one of the other retrieval baselines performs better than SbR-Str.}}
%Also, with our best performing LLM (GPT-3.5-Turbo), SbR-Str performs better than all the four baseline methods which use the \emph{whole} query for matching for 8 out of 11 DBs for IN (Table \ref{tab:gpt-results_IN}) and OUT (Table \ref{tab:gpt-results_OUT}) splits, respectively. As discussed in Research Question \textbf{RQ5}, this shows the importance of query decomposition. However, For IN Sets of 3 DBs (Debit Card Specializing, Toxicology, and Formula 1) and OUT Sets of 3 DBs (Card Games, Codebase Community and Formula 1) one of the other retrieval baselines (mostly BE-Str) performs better than SbR-Str.
%We perform an extensive analysis for the samples for which we do not get correct answers from the SQLs generated using our approach (SbR-Str). We find that the errors get categorized into three broad categories, which are explained in detail below with examples illustrated in Table \ref{tab:error_analysis} and \ref{tab:error_analysis_part_2}.
% \begin{table}[!ht]
% \centering
% \resizebox{\columnwidth}{!}{%
% \begin{tabular}{l|lllll}
% \hline
% \textbf{Database} &
% \textbf{Mixtral} &
% \textbf{\begin{tabular}[c]{@{}l@{}}Llamma\\ 3\end{tabular}} &
% \textbf{\begin{tabular}[c]{@{}l@{}}SQL \\ Coder\end{tabular}} &
% \textbf{\begin{tabular}[c]{@{}l@{}}Gemini\\ 1.5 Flash\end{tabular}} &
% \textbf{GPT 3.5} \\ \hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Thrombosis Pred.\end{tabular}} & 07.4 & 21.0 & \textbf{26.2} & 23.5 & {\ul \textbf{37.0}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Calif. Schools\end{tabular}} & 11.4 & 15.9 & 19.9 & \textbf{22.7} & {\ul \textbf{29.6}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Card Games\end{tabular}} & 10.5 & 23.2 & 24.2 & \textbf{29.5} & {\ul \textbf{47.4}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Debit Card Spec.\end{tabular}} & 18.8 & 28.1 & 28.1 & {\ul \textbf{43.8}} & \textbf{34.4} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Toxicology\end{tabular}} & 16.7 & 25.0 & 31.3 & 38.9 & {\ul \textbf{43.0}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Financial DB\end{tabular}} & 17.0 & 28.3 & 30.2 & \textbf{37.7} & {\ul \textbf{41.5}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Codebase Comm.\end{tabular}} & 19.4 & 28.0 & 35.0 & \textbf{54.8} & {\ul \textbf{59.1}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}European Football2\end{tabular}} & 20.3 & 25.4 & 31.7 & \textbf{32.8} & {\ul \textbf{53.1}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Formula 1\end{tabular}} & 19.5 & 20.7 & 24.1 & \textbf{26.4} & {\ul \textbf{36.8}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Student Club\end{tabular}} & 21.5 & 26.9 & 33.6 & {\ul \textbf{55.7}} & \textbf{53.2} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Super hero\end{tabular}} & 28.1 & 36.2 & 45.2 & \textbf{69.2} & {\ul \textbf{75.0}} \\ \hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Average \end{tabular}} & 17.2 & 25.2 & 30.1 & \textbf{39.6} & {\ul \textbf{47.5}} \\ \hline
% \end{tabular}%
% }
% \caption{Database wise Execution Accuracy of Generated SQLs on Dev Set \textbf{IN Set} DBs of BirdSQL \cite{li2024can} by All LLMs using our query Sub-string based Retrieval approach with DBA scanned Templatized Domain statements (SbR-Str) (Section \ref{sec:approach}) with Templatized Domain Statements. IN, OUT Set as explained in Section \ref{sec:dataset},
% All retrievals are with Top-K Statements, where K = 4 \textbf{\underline {Bold and Underline}}: Best performance across LLM. \textbf{Bold}: Second best performance across LLM.
% }
% \label{tab:DBwiseSR_IN}
% \end{table}
% % OUT
% \begin{table}[!ht]
% \centering
% \resizebox{\columnwidth}{!}{%
% \begin{tabular}{l|lllll}
% \hline
% \textbf{Database} &
% \textbf{Mixtral} &
% \textbf{\begin{tabular}[c]{@{}l@{}}Llamma\\ 3\end{tabular}} &
% \textbf{\begin{tabular}[c]{@{}l@{}}SQL \\ Coder\end{tabular}} &
% \textbf{\begin{tabular}[c]{@{}l@{}}Gemini\\ 1.5 Flash\end{tabular}} &
% \textbf{GPT 3.5} \\ \hline %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Thrombosis Pred.\end{tabular}} & 08.5 & 18.3 & 22.9 & \textbf{24.4} & {\ul \textbf{25.6}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Calif. Schools\end{tabular}} & 06.7 & 12.2 & 15.3 & {\ul \textbf{22.2}} & \textbf{20.0} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Card Games\end{tabular}} & 05.2 & 25.0 & \textbf{25.0} & 21.9 & {\ul \textbf{26.0}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Debit Card Spec.\end{tabular}} & 18.8 & 28.1 & 28.1 & {\ul \textbf{43.8}} & \textbf{34.4} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Toxicology\end{tabular}} & 13.7 & 20.6 & 24.7 & {\ul \textbf{42.5}} & \textbf{39.7} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Financial DB\end{tabular}} & 18.9 & 28.3 & 26.4 & \textbf{26.4} & {\ul \textbf{30.2}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Codebase Comm.\end{tabular}} & 17.2 & 20.7 & 30.1 & {\ul \textbf{49.5}} & \textbf{47.3} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}European Football2\end{tabular}} & 20.0 & 26.9 & 27.7 & \textbf{33.9} & {\ul \textbf{46.2}} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Formula 1\end{tabular}} & 10.3 & 24.1 & 24.1 & {\ul \textbf{37.9}} & \textbf{35.6} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Student Club\end{tabular}} & 22.8 & 33.2 & 41.5 & {\ul \textbf{58.2}} & \textbf{55.7} \\ %\hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Super hero\end{tabular}} & 24.6 & 30.8 & 38.5 & \textbf{62.5} & {\ul \textbf{67.7}} \\ \hline
% \textbf{\begin{tabular}[c]{@{}l@{}}Average \end{tabular}} & 14.7 & 25.6 & 28.0 & \textbf{38.7} & {\ul \textbf{39.5}} \\ \hline
% \end{tabular}%
% }
% \caption{Database wise Execution Accuracy of Generated SQLs on Dev Set \textbf{OUT Set} DBs of BirdSQL \cite{li2024can} by All LLMs using our query Sub-string based Retrieval approach with DBA scanned Templatized Domain statements (SbR-Str) (Section \ref{sec:approach}) with Templatized Domain Statements. IN, OUT Set as explained in Section \ref{sec:dataset},
% All retrievals are with Top-K Statements, where K=10 \textbf{\underline {Bold and Underline}}: Best performance across LLM. \textbf{Bold}: Second best performance across LLM.
% }
% \label{tab:DBwiseSR_OUT}
% \end{table}
%Our retrieved DSs with SbR-Str are relevant to the NL query, however the LLM generates incorrect SQLs with these correct DSs in the context (Example 2 and 3). Example 2 has correct set-of templatized DSs retrieved with our (SbR-Str) approach, but the SQL query is incorrect as it does not use the DISTINCT keyword, which is present in the ground truth SQL. Example 3 is another such example where even though the retrieval is correct, the generated SQL is incorrect, with an important constraint, \textit{"DisplayName IN (`Harlan', `Jarod Dixon')"} is missing in the generated SQL query.
%The generated SQLs by SbR-Str are incorrect due to wrong retrievals (Examples 4 and 5). Example 4 is a query part of OUT set. Here an incorrect templatized DS gets retrieved having its `text' part (`In which formula\_1 race') very similar to the query sub-string (`which host 4 f1 races'), leading to erroneous sub-string matching with our SbR-Str approach. The generated SQL query is affected by this incorrect retrieval leading to the use of `races.name' from the retrieved DS in the HAVING clause, as opposed to `races.raceId'. On the similar lines, for example 5 an incorrect templatized DS (index 3) is retrieved by our approach (SbR-Str). It gets retrieved because the `text' part `bond\_id TR001\_3\_4' of the statement matches with the `bond TR001\_2\_4' sub-string of the NL query. LLM gets mislead by the incorrect hint provided by this irrelevant domains statement leading to an unnecessary join on the table `connected' in the generated SQL.
\section{Conclusion}
% \cite{curino2013automating}
%This paper presents the framework for LLM driven NL-to-SQL for queries on enterprise databases that require an understanding of the domain expressions embedded in the NL query. A novel Structuring structure is proposed for encoding domain statements and the resulting format is proved useful for downstream retrieval augmented generation of SQLs.
This paper presents a framework for expressing, representing, indexing, and retrieving domain knowledge required to convert domain-specific NL questions to SQL. For enterprise databases, it provides a significantly more practical approach of providing database-level domain statements, than existing NL hints attached to each query.
Results on a dataset derived from BirdSQL demonstrates the value of the domain statement construction, structuring \& retrieval based augmentation for downstream SQL generation.
Our novel retrieval strategy outperforms all the baseline approaches. In future, for new NL user queries we plan to automatically identify unresolved domain-specific expressions and route to domain experts for disambiguation. Finally, to handle enterprise DBs that get frequently updated, we plan to extend our pipeline to accommodate real-time updates of the domain repository.
%In the future, we plan to solicit NL domain statements for the IN split queries from pretrained LLMs, to be screened by domain experts to further alleviate the manual intervention required for the current framework.
% \section{GenAI Usage Disclosure}
% We DO NOT use GenAI tools for any stage of our research including coding, paper writing. %All the stages are executed manually by the authors.
\begin{comment}
\section{Ease of Use}
\subsection{Maintaining the Integrity of the Specifications}
The IEEEtran class file is used to format your paper and style the text. All margins,
column widths, line spaces, and text fonts are prescribed; please do not
alter them. You may note peculiarities. For example, the head margin
measures proportionately more than is customary. This measurement
and others are deliberate, using specifications that anticipate your paper
as one part of the entire proceedings, and not as an independent document.
Please do not revise any of the current designations.
\section{Prepare Your Paper Before Styling}
Before you begin to format your paper, first write and save the content as a
separate text file. Complete all content and organizational editing before
formatting. Please note sections \ref{AA}--\ref{SCM} below for more information on
proofreading, spelling and grammar.
Keep your text and graphic files separate until after the text has been
formatted and styled. Do not number text heads---{\LaTeX} will do that
for you.
\subsection{Abbreviations and Acronyms}\label{AA}
Define abbreviations and acronyms the first time they are used in the text,
even after they have been defined in the abstract. Abbreviations such as
IEEE, SI, MKS, CGS, ac, dc, and rms do not have to be defined. Do not use
abbreviations in the title or heads unless they are unavoidable.
\subsection{Units}
\begin{itemize}
\item Use either SI (MKS) or CGS as primary units. (SI units are encouraged.) English units may be used as secondary units (in parentheses). An exception would be the use of English units as identifiers in trade, such as ``3.5-inch disk drive''.
\item Avoid combining SI and CGS units, such as current in amperes and magnetic field in oersteds. This often leads to confusion because equations do not balance dimensionally. If you must use mixed units, clearly state the units for each quantity that you use in an equation.
\item Do not mix complete spellings and abbreviations of units: ``Wb/m\textsuperscript{2}'' or ``webers per square meter'', not ``webers/m\textsuperscript{2}''. Spell out units when they appear in text: ``. . . a few henries'', not ``. . . a few H''.
\item Use a zero before decimal points: ``0.25'', not ``.25''. Use ``cm\textsuperscript{3}'', not ``cc''.)
\end{itemize}
\subsection{Equations}
Number equations consecutively. To make your
equations more compact, you may use the solidus (~/~), the exp function, or
appropriate exponents. Italicize Roman symbols for quantities and variables,
but not Greek symbols. Use a long dash rather than a hyphen for a minus
sign. Punctuate equations with commas or periods when they are part of a
sentence, as in:
\begin{equation}
a+b=\gamma\label{eq}
\end{equation}
Be sure that the
symbols in your equation have been defined before or immediately following
the equation. Use ``\eqref{eq}'', not ``Eq.~\eqref{eq}'' or ``equation \eqref{eq}'', except at
the beginning of a sentence: ``Equation \eqref{eq} is . . .''
\subsection{\LaTeX-Specific Advice}
Please use ``soft'' (e.g., \verb|\eqref{Eq}|) cross references instead
of ``hard'' references (e.g., \verb|(1)|). That will make it possible
to combine sections, add equations, or change the order of figures or
citations without having to go through the file line by line.
Please don't use the \verb|{eqnarray}| equation environment. Use
\verb|{align}| or \verb|{IEEEeqnarray}| instead. The \verb|{eqnarray}|
environment leaves unsightly spaces around relation symbols.
Please note that the \verb|{subequations}| environment in {\LaTeX}
will increment the main equation counter even when there are no
equation numbers displayed. If you forget that, you might write an
article in which the equation numbers skip from (17) to (20), causing
the copy editors to wonder if you've discovered a new method of
counting.
{\BibTeX} does not work by magic. It doesn't get the bibliographic
data from thin air but from .bib files. If you use {\BibTeX} to produce a
bibliography you must send the .bib files.
{\LaTeX} can't read your mind. If you assign the same label to a
subsubsection and a table, you might find that Table I has been cross
referenced as Table IV-B3.
{\LaTeX} does not have precognitive abilities. If you put a
\verb|\label| command before the command that updates the counter it's
supposed to be using, the label will pick up the last counter to be
cross referenced instead. In particular, a \verb|\label| command
should not go before the caption of a figure or a table.
Do not use \verb|\nonumber| inside the \verb|{array}| environment. It
will not stop equation numbers inside \verb|{array}| (there won't be
any anyway) and it might stop a wanted equation number in the
surrounding equation.
\subsection{Some Common Mistakes}\label{SCM}
\begin{itemize}
\item The word ``data'' is plural, not singular.
\item The subscript for the permeability of vacuum $\mu_{0}$, and other common scientific constants, is zero with subscript formatting, not a lowercase letter ``o''.
\item In American English, commas, semicolons, periods, question and exclamation marks are located within quotation marks only when a complete thought or name is cited, such as a title or full quotation. When quotation marks are used, instead of a bold or italic typeface, to highlight a word or phrase, punctuation should appear outside of the quotation marks. A parenthetical phrase or statement at the end of a sentence is punctuated outside of the closing parenthesis (like this). (A parenthetical sentence is punctuated within the parentheses.)
\item A graph within a graph is an ``inset'', not an ``insert''. The word alternatively is preferred to the word ``alternately'' (unless you really mean something that alternates).
\item Do not use the word ``essentially'' to mean ``approximately'' or ``effectively''.
\item In your paper title, if the words ``that uses'' can accurately replace the word ``using'', capitalize the ``u''; if not, keep using lower-cased.
\item Be aware of the different meanings of the homophones ``affect'' and ``effect'', ``complement'' and ``compliment'', ``discreet'' and ``discrete'', ``principal'' and ``principle''.
\item Do not confuse ``imply'' and ``infer''.
\item The prefix ``non'' is not a word; it should be joined to the word it modifies, usually without a hyphen.
\item There is no period after the ``et'' in the Latin abbreviation ``et al.''.
\item The abbreviation ``i.e.'' means ``that is'', and the abbreviation ``e.g.'' means ``for example''.
\end{itemize}
An excellent style manual for science writers is \cite{b7}.
\subsection{Authors and Affiliations}
\textbf{The class file is designed for, but not limited to, six authors.} A
minimum of one author is required for all conference articles. Author names
should be listed starting from left to right and then moving down to the
next line. This is the author sequence that will be used in future citations
and by indexing services. Names should not be listed in columns nor group by
affiliation. Please keep your affiliations as succinct as possible (for
example, do not differentiate among departments of the same organization).
\subsection{Identify the Headings}
Headings, or heads, are organizational devices that guide the reader through
your paper. There are two types: component heads and text heads.
Component heads identify the different components of your paper and are not
topically subordinate to each other. Examples include Acknowledgments and
References and, for these, the correct style to use is ``Heading 5''. Use
``figure caption'' for your Figure captions, and ``table head'' for your
table title. Run-in heads, such as ``Abstract'', will require you to apply a
style (in this case, italic) in addition to the style provided by the drop
down menu to differentiate the head from the text.
Text heads organize the topics on a relational, hierarchical basis. For
example, the paper title is the primary text head because all subsequent
material relates and elaborates on this one topic. If there are two or more
sub-topics, the next level head (uppercase Roman numerals) should be used
and, conversely, if there are not at least two sub-topics, then no subheads
should be introduced.
\subsection{Figures and Tables}
\paragraph{Positioning Figures and Tables} Place figures and tables at the top and
bottom of columns. Avoid placing them in the middle of columns. Large
figures and tables may span across both columns. Figure captions should be
below the figures; table heads should appear above the tables. Insert
figures and tables after they are cited in the text. Use the abbreviation
``Fig.~\ref{fig}'', even at the beginning of a sentence.
\begin{table}[htbp]
\caption{Table Type Styles}
\begin{center}
\begin{tabular}{|c|c|c|c|}
\hline
\textbf{Table}&\multicolumn{3}{|c|}{\textbf{Table Column Head}} \\
\cline{2-4}
\textbf{Head} & \textbf{\textit{Table column subhead}}& \textbf{\textit{Subhead}}& \textbf{\textit{Subhead}} \\
\hline
copy& More table copy$^{\mathrm{a}}$& & \\
\hline
\multicolumn{4}{l}{$^{\mathrm{a}}$Sample of a Table footnote.}
\end{tabular}
\label{tab1}
\end{center}
\end{table}
\begin{figure}[htbp]
\centerline{\includegraphics{fig1.png}}
\caption{Example of a figure caption.}
\label{fig}
\end{figure}
Figure Labels: Use 8 point Times New Roman for Figure labels. Use words
rather than symbols or abbreviations when writing Figure axis labels to
avoid confusing the reader. As an example, write the quantity
``Magnetization'', or ``Magnetization, M'', not just ``M''. If including
units in the label, present them within parentheses. Do not label axes only
with units. In the example, write ``Magnetization (A/m)'' or ``Magnetization
\{A[m(1)]\}'', not just ``A/m''. Do not label axes with a ratio of
quantities and units. For example, write ``Temperature (K)'', not
``Temperature/K''.
\section*{Acknowledgment}
The preferred spelling of the word ``acknowledgment'' in America is without
an ``e'' after the ``g''. Avoid the stilted expression ``one of us (R. B.
G.) thanks $\ldots$''. Instead, try ``R. B. G. thanks$\ldots$''. Put sponsor
acknowledgments in the unnumbered footnote on the first page.
\section*{References}
Please number citations consecutively within brackets \cite{b1}. The
sentence punctuation follows the bracket \cite{b2}. Refer simply to the reference
number, as in \cite{b3}---do not use ``Ref. \cite{b3}'' or ``reference \cite{b3}'' except at
the beginning of a sentence: ``Reference \cite{b3} was the first $\ldots$''
Number footnotes separately in superscripts. Place the actual footnote at
the bottom of the column in which it was cited. Do not put footnotes in the
abstract or reference list. Use letters for table footnotes.
Unless there are six authors or more give all authors' names; do not use
``et al.''. Papers that have not been published, even if they have been
submitted for publication, should be cited as ``unpublished'' \cite{b4}. Papers
that have been accepted for publication should be cited as ``in press'' \cite{b5}.
Capitalize only the first word in a paper title, except for proper nouns and
element symbols.
For papers published in translation journals, please give the English
citation first, followed by the original foreign-language citation \cite{b6}.
\begin{thebibliography}{00}
\bibitem{b1} G. Eason, B. Noble, and I. N. Sneddon, ``On certain integrals of Lipschitz-Hankel type involving products of Bessel functions,'' Phil. Trans. Roy. Soc. London, vol. A247, pp. 529--551, April 1955.
\bibitem{b2} J. Clerk Maxwell, A Treatise on Electricity and Magnetism, 3rd ed., vol. 2. Oxford: Clarendon, 1892, pp.68--73.
\bibitem{b3} I. S. Jacobs and C. P. Bean, ``Fine particles, thin films and exchange anisotropy,'' in Magnetism, vol. III, G. T. Rado and H. Suhl, Eds. New York: Academic, 1963, pp. 271--350.
\bibitem{b4} K. Elissa, ``Title of paper if known,'' unpublished.
\bibitem{b5} R. Nicole, ``Title of paper with only first word capitalized,'' J. Name Stand. Abbrev., in press.
\bibitem{b6} Y. Yorozu, M. Hirano, K. Oka, and Y. Tagawa, ``Electron spectroscopy studies on magneto-optical media and plastic substrate interface,'' IEEE Transl. J. Magn. Japan, vol. 2, pp. 740--741, August 1987 [Digests 9th Annual Conf. Magnetics Japan, p. 301, 1982].
\bibitem{b7} M. Young, The Technical Writer's Handbook. Mill Valley, CA: University Science, 1989.
\end{thebibliography}
\vspace{12pt}
\color{red}
IEEE conference templates contain guidance text for composing and formatting conference papers. Please ensure that all template text is removed from your conference paper prior to submission to the conference. Failure to remove the template text from your paper may result in your paper not being published.
\end{comment}
\bibliographystyle{./IEEEtran}
\bibliography{./main}
\end{document}
% Generated by IEEEtran.bst, version: 1.12 (2007/01/11)
\begin{thebibliography}{10}
\providecommand{\url}[1]{#1}
\csname url@samestyle\endcsname
\providecommand{\newblock}{\relax}
\providecommand{\bibinfo}[2]{#2}
\providecommand{\BIBentrySTDinterwordspacing}{\spaceskip=0pt\relax}
\providecommand{\BIBentryALTinterwordstretchfactor}{4}
\providecommand{\BIBentryALTinterwordspacing}{\spaceskip=\fontdimen2\font plus
\BIBentryALTinterwordstretchfactor\fontdimen3\font minus \fontdimen4\font\relax}
\providecommand{\BIBforeignlanguage}[2]{{%
\expandafter\ifx\csname l@#1\endcsname\relax
\typeout{** WARNING: IEEEtran.bst: No hyphenation pattern has been}%
\typeout{** loaded for the language `#1'. Using the pattern for}%
\typeout{** the default language instead.}%
\else
\language=\csname l@#1\endcsname
\fi
#2}}
\providecommand{\BIBdecl}{\relax}
\BIBdecl
\bibitem{Liu2024ASO}
\BIBentryALTinterwordspacing
X.~Liu, S.~Shen, B.~Li, P.~Ma, R.~Jiang, Y.~Luo, Y.~Zhang, J.~Fan, G.~Li, and N.~Tang, ``A survey of nl2sql with large language models: Where are we, and where are we going?'' \emph{ArXiv}, vol. abs/2408.05109, 2024. [Online]. Available: \url{https://api.semanticscholar.org/CorpusID:271843296}
\BIBentrySTDinterwordspacing
\bibitem{Li2024TheDO}
\BIBentryALTinterwordspacing
B.~Li, Y.~Luo, C.~Chai, G.~Li, and N.~Tang, ``The dawn of natural language to sql: Are we fully ready?'' \emph{ArXiv}, vol. abs/2406.01265, 2024. [Online]. Available: \url{https://api.semanticscholar.org/CorpusID:270214429}
\BIBentrySTDinterwordspacing
\bibitem{Lan2023UNITEAU}
\BIBentryALTinterwordspacing
W.~Lan, Z.~Wang, A.~Chauhan, H.~Zhu, A.~H. Li, J.~Guo, S.~Zhang, C.-W. Hang, J.~Lilien, Y.~Hu, L.~Pan, M.~Dong, J.~Wang, J.~Jiang, S.~M. Ash, V.~Castelli, P.~Ng, and B.~Xiang, ``Unite: A unified benchmark for text-to-sql evaluation,'' \emph{ArXiv}, vol. abs/2305.16265, 2023. [Online]. Available: \url{https://api.semanticscholar.org/CorpusID:258888094}
\BIBentrySTDinterwordspacing
\bibitem{Zhong2017Seq2SQLGS}
\BIBentryALTinterwordspacing
V.~Zhong, C.~Xiong, and R.~Socher, ``Seq2sql: Generating structured queries from natural language using reinforcement learning,'' \emph{ArXiv}, vol. abs/1709.00103, 2017. [Online]. Available: \url{https://api.semanticscholar.org/CorpusID:25156106}
\BIBentrySTDinterwordspacing
\bibitem{yu2018spider}
T.~Yu, R.~Zhang, K.~Yang, M.~Yasunaga, D.~Wang, Z.~Li, J.~Ma, I.~Li, Q.~Yao, S.~Roman \emph{et~al.}, ``Spider: A large-scale human-labeled dataset for complex and cross-domain semantic parsing and text-to-sql task,'' \emph{arXiv preprint arXiv:1809.08887}, 2018.
\bibitem{lee-2021-kaggle-dbqa}
\BIBentryALTinterwordspacing
C.-H. Lee, O.~Polozov, and M.~Richardson, ``{KaggleDBQA}: Realistic evaluation of text-to-{SQL} parsers,'' in \emph{Proceedings of the 59th Annual Meeting of the Association for Computational Linguistics and the 11th International Joint Conference on Natural Language Processing (Volume 1: Long Papers)}.\hskip 1em plus 0.5em minus 0.4em\relax Online: Association for Computational Linguistics, Aug. 2021, pp. 2261--2273. [Online]. Available: \url{https://aclanthology.org/2021.acl-long.176}
\BIBentrySTDinterwordspacing
\bibitem{suhr-etal-2020-exploring}
\BIBentryALTinterwordspacing
A.~Suhr, M.-W. Chang, P.~Shaw, and K.~Lee, ``Exploring unexplored generalization challenges for cross-database semantic parsing,'' in \emph{Proceedings of the 58th Annual Meeting of the Association for Computational Linguistics}, D.~Jurafsky, J.~Chai, N.~Schluter, and J.~Tetreault, Eds.\hskip 1em plus 0.5em minus 0.4em\relax Online: Association for Computational Linguistics, Jul. 2020, pp. 8372--8388. [Online]. Available: \url{https://aclanthology.org/2020.acl-main.742}
\BIBentrySTDinterwordspacing
\bibitem{li2024can}
J.~Li, B.~Hui, G.~Qu, J.~Yang, B.~Li, B.~Li, B.~Wang, B.~Qin, R.~Geng, N.~Huo \emph{et~al.}, ``Can llm already serve as a database interface? a big bench for large-scale database grounded text-to-sqls,'' \emph{Advances in Neural Information Processing Systems}, vol.~36, 2024.
\bibitem{Shi2023LargeLM}
\BIBentryALTinterwordspacing
F.~Shi, X.~Chen, K.~Misra, N.~Scales, D.~Dohan, E.~H. hsin Chi, N.~Scharli, and D.~Zhou, ``Large language models can be easily distracted by irrelevant context,'' in \emph{International Conference on Machine Learning}, 2023. [Online]. Available: \url{https://api.semanticscholar.org/CorpusID:256459776}
\BIBentrySTDinterwordspacing
\bibitem{Gao2024APO}
Y.~Gao, Y.~Liu, X.~Li, X.~Shi, Y.~Zhu, Y.~Wang, S.~Li, W.~Li, Y.~Hong, Z.~Luo, J.~Gao, L.~Mou, and Y.~Li, ``A preview of xiyan-sql: A multi-generator ensemble framework for text-to-sql,'' in \emph{Proceedings of the The 2024 Joint International Conference on Computational Linguistics, Language resources and Evaluation}, 2024.
\bibitem{Pourreza2024CHASESQLMR}
\BIBentryALTinterwordspacing
M.~Pourreza, H.~Li, R.~Sun, Y.~Chung, S.~Talaei, G.~T. Kakkar, Y.~Gan, A.~Saberi, F.~Ozcan, and S.~{\"O}. Arik, ``Chase-sql: Multi-path reasoning and preference optimized candidate selection in text-to-sql,'' \emph{ArXiv}, vol. abs/2410.01943, 2024. [Online]. Available: \url{https://api.semanticscholar.org/CorpusID:273098638}
\BIBentrySTDinterwordspacing
\bibitem{li2024codes}
H.~Li, J.~Zhang, H.~Liu, J.~Fan, X.~Zhang, J.~Zhu, R.~Wei, H.~Pan, C.~Li, and H.~Chen, ``Codes: Towards building open-source language models for text-to-sql,'' \emph{arXiv preprint arXiv:2402.16347}, 2024.
\bibitem{pourreza2024dts}
M.~Pourreza and D.~Rafiei, ``Dts-sql: Decomposed text-to-sql with small large language models,'' \emph{arXiv preprint arXiv:2402.01117}, 2024.
\bibitem{rai2023improving}
D.~Rai, B.~Wang, Y.~Zhou, and Z.~Yao, ``Improving generalization in language model-based text-to-sql semantic parsing: Two simple semantic boundary-based techniques,'' \emph{arXiv preprint arXiv:2305.17378}, 2023.
\bibitem{li2023graphix}
J.~Li, B.~Hui, R.~Cheng, B.~Qin, C.~Ma, N.~Huo, F.~Huang, W.~Du, L.~Si, and Y.~Li, ``Graphix-t5: Mixing pre-trained transformers with graph-aware layers for text-to-sql parsing,'' in \emph{Proceedings of the AAAI Conference on Artificial Intelligence}, vol.~37, no.~11, 2023, pp. 13\,076--13\,084.
\bibitem{gao2023text}
D.~Gao, H.~Wang, Y.~Li, X.~Sun, Y.~Qian, B.~Ding, and J.~Zhou, ``Text-to-sql empowered by large language models: A benchmark evaluation,'' \emph{arXiv preprint arXiv:2308.15363}, 2023.
\bibitem{pourreza2024din}
M.~Pourreza and D.~Rafiei, ``Din-sql: Decomposed in-context learning of text-to-sql with self-correction,'' \emph{Advances in Neural Information Processing Systems}, vol.~36, 2024.
\bibitem{zhongSeq2SQL2017}
V.~Zhong, C.~Xiong, and R.~Socher, ``Seq2sql: Generating structured queries from natural language using reinforcement learning,'' \emph{CoRR}, vol. abs/1709.00103, 2017.
\bibitem{Zelle:1996:LPD:1864519.1864543}
\BIBentryALTinterwordspacing
J.~M. Zelle and R.~J. Mooney, ``Learning to parse database queries using inductive logic programming,'' in \emph{Proceedings of the Thirteenth National Conference on Artificial Intelligence - Volume 2}, ser. AAAI'96.\hskip 1em plus 0.5em minus 0.4em\relax AAAI Press, 1996, pp. 1050--1055. [Online]. Available: \url{http://dl.acm.org/citation.cfm?id=1864519.1864543}
\BIBentrySTDinterwordspacing
\bibitem{Gan2021ExploringUL}
\BIBentryALTinterwordspacing
Y.~Gan, X.~Chen, and M.~Purver, ``Exploring underexplored limitations of cross-domain text-to-sql generalization,'' in \emph{Conference on Empirical Methods in Natural Language Processing}, 2021. [Online]. Available: \url{https://api.semanticscholar.org/CorpusID:237491444}
\BIBentrySTDinterwordspacing
\bibitem{Gan2021TowardsRO}
\BIBentryALTinterwordspacing
Y.~Gan, X.~Chen, Q.~Huang, M.~Purver, J.~R. Woodward, J.~Xie, and P.~Huang, ``Towards robustness of text-to-sql models against synonym substitution,'' in \emph{Annual Meeting of the Association for Computational Linguistics}, 2021. [Online]. Available: \url{https://api.semanticscholar.org/CorpusID:235293739}
\BIBentrySTDinterwordspacing
\bibitem{Utama2018AnEN}
\BIBentryALTinterwordspacing
P.~A. Utama, N.~Weir, F.~Basık, C.~Binnig, U.~Çetintemel, B.~H{\"a}ttasch, A.~Ilkhechi, S.~Ramaswamy, and A.~Usta, ``An end-to-end neural natural language interface for databases,'' \emph{ArXiv}, vol. abs/1804.00401, 2018. [Online]. Available: \url{https://api.semanticscholar.org/CorpusID:4569924}
\BIBentrySTDinterwordspacing
\bibitem{lei2024spider}
F.~Lei, J.~Chen, Y.~Ye, R.~Cao, D.~Shin, H.~Su, Z.~Suo, H.~Gao, W.~Hu, P.~Yin \emph{et~al.}, ``Spider 2.0: Evaluating language models on real-world enterprise text-to-sql workflows,'' \emph{arXiv preprint arXiv:2411.07763}, 2024.
\bibitem{an2023skill}
S.~An, B.~Zhou, Z.~Lin, Q.~Fu, B.~Chen, N.~Zheng, W.~Chen, and J.-G. Lou, ``Skill-based few-shot selection for in-context learning,'' \emph{arXiv preprint arXiv:2305.14210}, 2023.
\bibitem{li2023resdsql}
H.~Li, J.~Zhang, C.~Li, and H.~Chen, ``Resdsql: Decoupling schema linking and skeleton parsing for text-to-sql,'' in \emph{Proceedings of the AAAI Conference on Artificial Intelligence}, vol.~37, no.~11, 2023, pp. 13\,067--13\,075.
\bibitem{rubin-etal-2022-learning}
\BIBentryALTinterwordspacing
O.~Rubin, J.~Herzig, and J.~Berant, ``Learning to retrieve prompts for in-context learning,'' in \emph{Proceedings of the 2022 Conference of the North American Chapter of the Association for Computational Linguistics: Human Language Technologies}, M.~Carpuat, M.-C. de~Marneffe, and I.~V. Meza~Ruiz, Eds.\hskip 1em plus 0.5em minus 0.4em\relax Seattle, United States: Association for Computational Linguistics, Jul. 2022, pp. 2655--2671. [Online]. Available: \url{https://aclanthology.org/2022.naacl-main.191}
\BIBentrySTDinterwordspacing
\bibitem{dou2023towards}
L.~Dou, Y.~Gao, X.~Liu, M.~Pan, D.~Wang, W.~Che, D.~Zhan, M.-Y. Kan, and J.-G. Lou, ``Towards knowledge-intensive text-to-sql semantic parsing with formulaic knowledge,'' \emph{arXiv preprint arXiv:2301.01067}, 2023.
\bibitem{Hong2024KnowledgetoSQLES}
\BIBentryALTinterwordspacing
Z.~Hong, Z.~Yuan, H.~Chen, Q.~Zhang, F.~Huang, and X.~Huang, ``Knowledge-to-sql: Enhancing sql generation with data expert llm,'' \emph{ArXiv}, vol. abs/2402.11517, 2024. [Online]. Available: \url{https://api.semanticscholar.org/CorpusID:267751230}
\BIBentrySTDinterwordspacing
\bibitem{reimers2019sentence}
N.~Reimers and I.~Gurevych, ``Sentence-bert: Sentence embeddings using siamese bert-networks,'' \emph{arXiv preprint arXiv:1908.10084}, 2019.
\bibitem{santhanam-etal-2022-colbertv2}
K.~Santhanam, O.~Khattab, J.~Saad-Falcon, C.~Potts, and M.~Zaharia, ``{C}ol{BERT}v2: Effective and efficient retrieval via lightweight late interaction,'' in \emph{Proceedings of the 2022 Conference of the North American Chapter of the Association for Computational Linguistics: Human Language Technologies}, M.~Carpuat, M.-C. de~Marneffe, and I.~V. Meza~Ruiz, Eds.\hskip 1em plus 0.5em minus 0.4em\relax Seattle, United States: Association for Computational Linguistics, Jul. 2022, pp. 3715--3734.
\bibitem{wang2023mac}
B.~Wang, C.~Ren, J.~Yang, X.~Liang, J.~Bai, Q.-W. Zhang, Z.~Yan, and Z.~Li, ``Mac-sql: Multi-agent collaboration for text-to-sql,'' \emph{arXiv preprint arXiv:2312.11242}, 2023.
\bibitem{robertson1994okapi}
S.~E. Robertson and S.~Walker, ``Some simple effective approximations to the 2-poisson model for probabilistic weighted retrieval,'' in \emph{Proceedings of the 17th annual international ACM SIGIR conference on Research and development in information retrieval}.\hskip 1em plus 0.5em minus 0.4em\relax Springer-Verlag New York, Inc., 1994, pp. 232--241.
\bibitem{lei-etal-2023-unsupervised}
\BIBentryALTinterwordspacing
Y.~Lei, L.~Ding, Y.~Cao, C.~Zan, A.~Yates, and D.~Tao, ``Unsupervised dense retrieval with relevance-aware contrastive pre-training,'' in \emph{Findings of the Association for Computational Linguistics: ACL 2023}, A.~Rogers, J.~Boyd-Graber, and N.~Okazaki, Eds.\hskip 1em plus 0.5em minus 0.4em\relax Toronto, Canada: Association for Computational Linguistics, Jul. 2023, pp. 10\,932--10\,940. [Online]. Available: \url{https://aclanthology.org/2023.findings-acl.695}
\BIBentrySTDinterwordspacing
\bibitem{cer-etal-2017-semeval}
\BIBentryALTinterwordspacing
D.~Cer, M.~Diab, E.~Agirre, I.~Lopez-Gazpio, and L.~Specia, ``{S}em{E}val-2017 task 1: Semantic textual similarity multilingual and crosslingual focused evaluation,'' in \emph{Proceedings of the 11th International Workshop on Semantic Evaluation ({S}em{E}val-2017)}, S.~Bethard, M.~Carpuat, M.~Apidianaki, S.~M. Mohammad, D.~Cer, and D.~Jurgens, Eds.\hskip 1em plus 0.5em minus 0.4em\relax Vancouver, Canada: Association for Computational Linguistics, Aug. 2017, pp. 1--14. [Online]. Available: \url{https://aclanthology.org/S17-2001}
\BIBentrySTDinterwordspacing
\bibitem{gupta-etal-2023-coverage}
\BIBentryALTinterwordspacing
S.~Gupta, M.~Gardner, and S.~Singh, ``Coverage-based example selection for in-context learning,'' in \emph{Findings of the Association for Computational Linguistics: EMNLP 2023}, H.~Bouamor, J.~Pino, and K.~Bali, Eds.\hskip 1em plus 0.5em minus 0.4em\relax Singapore: Association for Computational Linguistics, Dec. 2023, pp. 13\,924--13\,950. [Online]. Available: \url{https://aclanthology.org/2023.findings-emnlp.930}
\BIBentrySTDinterwordspacing
\end{thebibliography}