Evaluating Cross-Domain Text-to- SQL Models and Benchmarks (2023)

Abstract: Text-to-SQL benchmarks play a crucial role in evaluating the progress made in the field and the ranking of different models. However, accurately matching a model-generated SQL query to a reference SQL query in a benchmark fails for various reasons, such as underspecified natural language queries, inherent assumptions in both model-generated and reference queries, and the non-deterministic nature of SQL output under certain conditions. In this paper, we conduct an extensive study of several prominent cross-domain text-to-SQL benchmarks and re-evaluate some of the top-performing models within these benchmarks, by both manually evaluating the SQL queries and rewriting them in equivalent expressions. Our evaluation reveals that attaining a perfect performance on these benchmarks is unfeasible due to the multiple interpretations that can be derived from the provided samples. Furthermore, we find that the true performance of the models is underestimated and their relative performance changes after a re-evaluation. Most notably, our evaluation reveals a surprising discovery: a recent GPT4-based model surpasses the gold standard reference queries in the Spider benchmark in our human evaluation. This finding highlights the importance of interpreting benchmark evaluations cautiously, while also acknowledging the critical role of additional independent evaluations in driving advancements in the field.

## introduction significant progress has been made in translating natural language text to sql statements over the past few years. the execution accuracy on the hold-out test of spider @xcite )-a large-scale cross-domain text-to-sql benchmarkhas improved from 53.5 in may, 2020 @xcite to 85.3 in march, 2023 @xcite . the exact set match accuracy, without considering database cell values, on the same benchmark and over the same period has im-proved from 65.6 @xcite to 74.0 @xcite . measuring such progress is hinged on reliable benchmarks and evaluation metrics. two standard metrics for evaluating the performance in this domain have been exact set match accuracy and execution accuracy. the former measures if a model-generated sql query lexically matches a reference sql query, whereas the latter measures if a model-generated sql query produces the same output as a reference query ( § 4). there is an ambiguity in schema mapping, with two different database columns describing the name. consider the example in figure 1 , which consists of a model-generated query (shown on the left) and a reference query (shown on the right). both sql queries return the id and name of makers that have more than 3 models. however, the model-generated query returns the column full-name, which gives the full name of a maker (e.g., "ford motor company"), whereas the reference query given in the benchmark returns the column maker, which gives the short common name of a maker (e.g., "ford"). the model-generated query fails an exact set match since the column names in the select clause are different. the query outputs are also different and the model-generated query fails the execution accuracy as well. the natural language utterance is not specific about the type of name to be returned, and a human evaluator tags both queries correct. as the models improve, these types of failures make up most of the errors, and the performance metrics become less relevant, as shown in our evaluation. in particular, we re-evaluated all development set queries of spider on which two top-performing models, one using a fine-tuned model @xcite and another using a large language model @xcite , failed. we found out that 25% of the queries generated by one model and 87% of the queries generated by the other model were indeed correct but were wrongly evaluated by the benchmark. for the same set of queries, our re-evaluation of the ground truth queries found 33% of the sql queries incorrect, which was more than the number of incorrect queries generated by one of the models. this evaluation places one of the models above the ground truth queries in this re-evaluation. we further re-evaluated two well-known benchmarks, spider @xcite and spider-dk @xcite , and a newly released benchmark, bird @xcite , and found similar problems in all three benchmarks that affect the evaluation. our evaluation reveals that 18% of the queries in the train sets and 20%-23% of the queries in the dev sets of these benchmarks are subject to ties in the dataset and which one of the tied rows are returned. this means a model-generated query will be deemed incorrect if it does not return the same row, among tied rows, as the ground truth query. this can severely impact the evaluation, especially when there is a tight race among models. considering these observations, it is crucial to emphasize the significance of additional independent evaluations when utilizing these benchmarks. to enhance the evaluation process further, a potential solution is to incorporate multiple sql queries as the ground truth, each representing a different interpretation that may be valid. our objective in this paper is to provide a comprehensive evaluation of existing text-to-sql benchmarks, underscoring the inherent issues they possess. we refrain from introducing a new dataset due to several considerations. first, addressing the identified issues by updating these benchmarks requires considerable human effort. additionally, benchmarks in the text-to-sql domain, like spider and bird, have holdout test sets used for official leaderboards and comparisons of text-to-sql methodologies. we only have access to the development and training sets of these benchmarks, which limits our capability to alter the test sets. as a result, making changes only to the development and training sets would not completely address the benchmark's inherent problems, given that final performance is gauged using the problematic test sets. ## related work limited research has been dedicated to assessing the reliability and effectiveness of text-to-sql benchmarks. the authors of sql-palm @xcite note in their qualitative analysis of their model that some queries, labelled as incorrect by execution accuracy, were considered correct by human annotators. similarly, @xcite conduct an analysis highlighting the discrepancy between automatic evaluations and human annotations. they emphasize that certain queries produced by the models were labeled as incorrect sql queries but human annotators labelled them as correct queries. generally, a query that is equivalent (but not identical) to ground truth may be mistakenly classified as incorrect by automated evaluation metrics. another study by @xcite identifies limitations within the spider benchmark, such as issues with ties and certain syntactic problems. their analysis is primarily focused on a subset of spider, without quantifying the extent or impact of these limitations or conducting an assessment of other benchmarks. ## text-to-sql benchmarks benchmarks have played a crucial role in advancing the field and providing a platform for evaluation. wikisql @xcite consists of over 24,000 tables from wikipedia with sql queries generated based on some predefined rules and templates. the queries in this dataset are considered easy since they are all single-table queries. spider, introduced by @xcite , consists of 200 database schemas of which 160 schemas are published as train and dev sets and 40 schemas are kept hidden for testing. the queries are written on those schemas by computer science students without using templates. this is considered a challenging dataset. some other benchmarks are developed based on spider, including spider-syn @xcite , which replaces schema-related words with synonyms and eliminates explicit mentions between nlq and schema, and spider-dk @xcite , which introduces rarely observed domain knowledge into the spider development set. other benchmarks include fiben @xcite , created for the financial domain and bird @xcite , which comprises 12,751 queries over 95 databases spanning 37 professional domains. our study in this paper focuses on cross-domain large-scale benchmark spider, its variants spider-dk and spider-syn, and a more recent crossdomain large-scale benchmark bird. the selection of these benchmarks stems from their resemblance to real-world datasets, which is a crucial factor in conducting comprehensive research and analysis. one notable advantage of these benchmarks is the availability of a large training set, which plays a pivotal role in training and fine-tuning large-scale models. the inclusion of a substantial amount of training data enables the development of more robust and powerful models that can better handle the complexities and nuances present in real-world databases. ## evaluation metrics the performance evaluation of text-to-sql systems involves comparing them to a reference system, typically a gold standard set of known correct sql queries. generating a reference can be challenging due to multiple interpretations of natural language questions, while sql queries are based on logic and tend to cover only one interpretation. even if an interpretation is fixed, detecting if a model-generated query is equivalent to a reference query is challenging, due to the halting problem which is undecidable @xcite . nonetheless, to assess progress, proxy measures of performance have been developed in the literature. as two such metrics, we review exact set match accuracy and execution accuracy in this paper. under exact set match accuracy, sql queries are evaluated by matching the query clauses and components independently, such as the select, where, having, group by, and order by clauses. the matching is based on comparing columns and predicates, disregarding the ordering of columns and predicates. an exact matching of literals can be challenging since predicates such as @xmath0. however, accurately generating those literals without accessing database content may not be possible. under exact set matching without values, which is used in spider @xcite , a matching of literals is not required. two equivalent sql queries can have different expressions and may not match under an exact set match. an alternative metric that can reduce the number of false negatives is the execution accuracy. under execution accuracy, the equivalence between a model-generated query and a reference query is established if they both produce the same results on all possible databases instances @xcite . while testing all instances is impractical, running queries on a subset of instances can help identify candidates that are not equivalent to the reference query. although execution accuracy can detect queries that are equivalent but not identical, it may mistakenly identify queries as equivalent if they produce the same result on tested instances. therefore, an effective execution-based evaluation requires finding instances that cover various edge cases and can detect queries that are not equivalent to the reference. test suite accuracy @xcite , which is simply referred to as execution accuracy in spider benchmark and in our work, aims to minimize false positives by evaluating queries on a carefully selected collection of database instances, known as a test suite. nevertheless, an execution-based accuracy cannot capture all correct sql queries, highlighting the limitations and the continued importance of human evaluation for reliable assessment. ## execution accuracy failures a model-generated query can be correct but still fail the execution accuracy. we classify these failures into three categories: (1) failures due to ties in output, (2) ambiguity in schema matching, (3) wrong assumptions made about database content. ## experiments to understand the extent at which the aforementioned problems affect the benchmarks, our evaluation and the ranking of the models, we conducted three types of evaluations on three benchmarks: spider, spider-dk, bird. our findings here apply to the spider-syn dataset as well, which employs the same sql queries as in the spider dataset. for the same reason, we did not conduct a separate analysis of that benchmark. ## discussion our analysis ( § 6.1) reveals the limitations of major text-to-sql benchmarks, highlighting the fact that even with a perfect model, achieving a perfect accuracy on these benchmarks is not possible. the accuracies presented in our human evaluation ( § 6.2) further supports our claim and provides more insight into the limitations within one of the benchmarks studied. the results in ## conclusions the reliance on standard text-to-sql evaluation metrics, namely exact set match accuracy and execution accuracy, has become less reliable as the model performance approaches human-level performance. our work is the first to systematically study the limitations of these metrics and benchmarks through both human evaluation and query rewriting. our re-evaluation of well-known benchmarks (spider, spider-dk, and bird) uncovers common systematic issues that affect the evaluation process and performance estimates, revealing that a significant portion of queries in the train and dev sets are impacted by these issues. incorporating multiple sql queries as the ground truth and representing different interpretations of queries offer a promising solution to enhance the evaluation process and achieve a more comprehensive and accurate assessment of text-to-sql models.