Benchmarking and Improving Text-to- SQL Generation under Ambiguity (2023)

Abstract: Research in Text-to-SQL conversion has been largely benchmarked against datasets where each text query corresponds to one correct SQL. However, natural language queries over real-life databases frequently involve significant ambiguity about the intended SQL due to overlapping schema names and multiple confusing relationship paths. To bridge this gap, we develop a novel benchmark called AmbiQT with over 3000 examples where each text is interpretable as two plausible SQLs due to lexical and/or structural ambiguity. When faced with ambiguity, an ideal top- k decoder should generate all valid interpretations for possible disambiguation by the user. We evaluate several Text-to-SQL systems and decoding algorithms, including those employing state-of-the-art LLMs, and find them to be far from this ideal. The primary reason is that the prevalent beam search algorithm and its variants, treat SQL queries as a string and produce unhelpful token-level diversity in the top- k . We propose LogicalBeam, a new decoding algorithm that navigates the SQL logic space using a blend of plan-based template generation and constrained infilling. Counterfactually generated plans diversify templates while in-filling with a beam-search that branches solely on schema names provides value diversity. LogicalBeam is up to 2.5 times more effective than state-of-the-art models at generating all candidate SQLs in the top- k ranked outputs. It also enhances the top-5 Exact and Execution Match Accuracies on SPIDER and Kaggle DBQA.

## introduction research on text-to-sql generation has focused on scenarios where each natural language question is associated with one correct sql (zelle and 1 we release ambiqt and logicalbeam's implementation publicly at https://github.com/testzer0/ambiqt . our first contribution is to bridge this gulf by developing a benchmark, ambiqt, that tests performance under ambiguity in the context of current models. ambiqt includes over 3000 examples, each associating a natural question on a database with two valid sqls. inspired by our experience with several real-world datasets, we target four types of ambiguity spanning both lexical (ambiguous column and table names) and structural (whether a join is necessary, and an aggregate is pre-computed) ambiguity. the benchmark is generated via a combination of @xcite based synonym generation and perturbation, and standard rule-based perturbation. when faced with ambiguity, an ideal text-to-sql system should incorporate all valid alternatives in their top-k sql outputs, for user resolution. we show that present approaches, ranging from t5-3b @xcite to sota models, fail to generate all ambiguous outputs with any decoding strategy, including beam search and diversity-promoting sampling methods such as nucleus @xcite and typical sampling @xcite . most outputs are small lexical tweaks of the top choice, bringing about little meaningful diversity in sql structures or schema alternatives. even sota llms like chat-gpt @xcite suffer from this issue. to remedy the lack of diversity, we propose a new decoding algorithm, logicalbeam, that allocates branching to explore underlying logical variants of the sql rather than the string form. we catalog the errors of t5-3b @xcite on the spider dev split and use our insights to encourage targeted types of diversity -the number of joins and selections, and table/column names. our main contributions are: • we develop ambiqt, the first benchmark that tests performance under four types of ambiguity over 3000+ examples. ## background and related work a text-to-sql model takes as input a question expressed as a natural language text x, and a database schema s comprising of table and column names, and outputs an sql program y which can be executed against the database to answer the user's question. figure 1 shows an example. the training data for the task comprises (text, schema, sql) triplets spanning multiple distinct databases. benchmarks. popular benchmarks for the textto-sql task are wikisql @xcite and spider @xcite . a few others have been proposed recently to capture real-world scenarios, such as kaggledbqa @xcite , sede @xcite , and ehrsql @xcite . they all attach one sql per text, though some of them mention the problem of ambiguity in real-world datasets. dr. spider @xcite , designed to test the robustness of existing models, perturbs either the text or schema of spider but still assigns one sql per text. ambiguity in sql although ambiguity has been studied in other fields of nlp @xcite @xcite , it has been unexplored in the context of semantic parsing. ambiguity in sql arising from related column names is discussed in @xcite , but they only consider column ambiguity. their method of recognizing ambiguous queries depends on labeling words of the text and does not generalize to other kinds of ambiguity. to the best of our discernment, ambiqt represents the first open benchmark for testing coverage of ambiguous alternatives. diverse decoding. prior work has critiqued the lack of meaningful diversity in beam-search outputs @xcite @xcite . in response, many fixes have been proposed. some proposals attempt to restrict the tokens sampled, using strategies like nucleus sampling @xcite , truncated sampling @xcite , and typical sampling @xcite , while some rely on template-based decoding @xcite @xcite @xcite . a third approach is to generate a prefix with high diversity first, then ## ambiqt: a benchmark of ambiguous text-to-sql conversion ambiqt is constructed so that each text query has two distinct valid sql interpretations. motivated by our experience working with real-life databases, we designed ambiqt to encompass four types of ambiguity. each entry is designed so that both alternatives have a similar relevance to the question, and a well-calibrated decoding method is expected to rank them close by in their outputs. we create ambiqt by modifying the spider @xcite dataset, and use chatgpt (ope-nai, 2022) to aid with the creation. in each case, we modify the schema instead of the text as that provides greater control over the modification process. we explain the kinds of ambiguity in ambiqt below and portray examples of each in column ambiguity (c). unlike the spider benchmark where column names usually appear verbatim in the question text (like born state for the column born_state), when users unaware of the schema pose a natural question, they introduce column ambiguity @xcite . for example, "what is the capacity of o2 arena?" could be ambiguous if the schema has separate columns for standing and seating capacity. likewise, a query on the number of under-nourished children is ambiguous if we have different columns for "under-weight children" and "stunted growth in children". to simulate column ambiguity, for each text x, schema s, and sql y in spider, we prompt chat-gpt to generate two synonyms for each column name of s in a one-shot manner. appendix a furnishes more details of the prompt. we then modify s by replacing c with two columns c 1 , c 2 , and we use y to generate two queries y 1 , y 2 where all mentions of c are replaced with c 1 in y 1 and with c 2 in y 2 . an example appears in the first row of ## our method: logicalbeam logicalbeam attempts to induce meaningful diversity, while steering clear of vacuous forms of diversity in the formatting of the sql. we first attempt to understand the type of logical diversity required by analyzing the errors of the top-1 output of t5-3b on the spider benchmark. the mistakes of the top-1 output are cataloged in error type contribution (%) correct output 70.31 wrong selection order 7.44 missing join(s) 6.09 missing column(s) 3.48 extra join(s) introduced 2.80 incorrect column or table names 2.51 where clause missing or incorrect 1.35 extra column introduced 1.06 order by clause missing or incorrect 0.48 group by clause missing or incorrect 0.39 wrong comparison 0.29 distinct missed or introduced 0.10 other 3.68 these observations drove us to design a twostage approach. in the first stage, we generate diverse sql skeletons (templates) to capture structural diversity, and in the second we fill in the template with schema-diverse alternatives. we illustrate our approach in figure 3 . ## experiments we present extensive comparisons of several stateof-the-art text-to-sql models and decoding methods on ambiqt in the following sections. we then show that logicalbeam can be helpful even t result: the next set of hypotheses with scores (y ′ 1 , s ′ 1 ), • • • , (y ′ k , s ′ k ) h ← ∅; u ← getpossiblefirsttoks(c) ∪ getpossiblefirsttoks(t ); for ( @xmath0; @xmath1; @xmath2, t) then /* if this hypothesis violates the template, don't extend it. */ continue; end ncls ← getnexttokclass(yi, t); /* check if we expect to start a column/table name next. */ if @xmath3, table} then /* allow branching, but restrict options to whitelist. */ hi ← getextensionswithscores(yi, si, u ); else /* disallow branching by only choosing the top-scoring extension in the absence of ambiguity. we also present a detailed ablation study of logicalbeam, and a discussion of its use-cases and shortcomings. ## conclusion in this work, we highlighted the lack of evaluation of text-to-sql models under ambiguity in contemporary literature. to address this, we developed ambiqt, a novel benchmark with 3000+ challenging examples that evaluates text-to-sql models on four kinds of ambiguity. we demonstrated that current methods fall short of acceptable performance under ambiguity. motivated by analyzing the errors of a t5-3b model on the spider dataset, we developed a two-step approach of generating and then filling in a template. to this end, we trained a model to predict the number of joins and selections as a plan before the template, and controlled template diversity by enforcing appropriate plans. beam search was modified to enforce template adherence during in-filling. our method aligns well with intuition and greatly improves a model's coverage under ambiguity, as measured on ambiqt. it also delivers improvements in the absence of ambiguity, on the spider and kaggle dbqa datasets. we hope our efforts inspire future work to study generation under ambiguity in more detail, both in the domain of text-to-sql conversion and beyond.