Semantic Evaluation for Text-to- SQL with Distilled Test Suites (2020)

Abstract: We propose test suite accuracy to approximate semantic accuracy for Text-to-SQL models. Our method distills a small test suite of databases that achieves high code coverage for the gold query from a large number of randomly generated databases. At evaluation time, it computes the denotation accuracy of the predicted queries on the distilled test suite, hence calculating a tight upper-bound for semantic accuracy efficiently. We use our proposed method to evaluate 21 models submitted to the Spider leader board and manually verify that our method is always correct on 100 examples. In contrast, the current Spider metric leads to a 2.5% false negative rate on average and 8.1% in the worst case, indicating that test suite accuracy is needed. Our implementation, along with distilled test suites for eleven Text-to-SQL datasets, is publicly available.

## introduction a text-to-sql model translates natural language instructions to sql queries that can be executed on databases and bridges the gap between expert programmers and non-experts. accordingly, researchers have built a diversity of datasets @xcite @xcite and improved model performances @xcite @xcite @xcite . however, evaluating the semantic accuracy of a text-to-sql model is a long-standing problem: we want to know whether the predicted sql query has the same denotation as the gold for every possible database. "single" denotation evaluation executes the predicted sql query on one database and compares its denotation with that of the gold. it might create false positives, where a semantically different 1 metric implementation and test suites available here, for datasets: spider, cosql, sparc, academic, advising, atis, geography, imdb, restaurants, scholar and yelp. query: "who is above 34 years old?" ## problem statement let @xmath0, and q w be the denotation of q on w, 4 where w/q is the space of all databases/sql queries. two queries q 1 and q 2 are semantically equivalent if their denotations are the same for all possible databases, i.e. we refer to the ground truth query as g and the model-predicted query to be evaluated as q. ideally, we want to evaluate whether q is semantically equivalent to g (abbreviated as semantic accuracy), which is unfortunately undecidable in general @xcite . traditionally, people evaluate a model-predicted query q by either exact string match or compare denotations on a single database w (abbreviated as single denotation accuracy). exact string match is too strict, as two different strings can have the same semantics. single denotation evaluation is too loose, as the denotations of g and q might be different on another database w. we use test suite to refer to a set of databases. a database w distinguishes two sql queries g, q if g w q w , and a test suite s distinguishes them if one of the databases @xmath1: for convenience, we define the indicator function: we use the test suite s to evaluate a modelpredicted query q: q is correct iff d s (g, q) = 0; i.e., g and q have the same denotations on all databases in s . to summarize, if m 1 ⇒ m 2 means that "correctness under m 1 implies correctness under m 2 ", exact match ⇒ semantic accuracy ⇒ test suite accuracy ⇒ single denotation accuracy. our goal is to construct a test suite of databases s to obtain a tight upper bound on semantic accuracy with test suite accuracy reliably and efficiently. ## desiderata since we want to construct a test suite s of databases for each gold query g, we use s g to denote the target test suite. before describing how to generate s g , we first list two criteria of a desirable test suite. later we construct s g by optimizing over these two criteria. computational efficiency. we minimize the size of s g to speed up test suite evaluations. gold g select name from people where @xmath2 figure 2: automatically generating a set of neighbor queries n g . we apply one type of modification to the original gold query g at a time. the modified queries are likely to be semantically close but inequivalent to the gold. code coverage. the test suite needs to cover every branch and clause of the gold query such that it can test the use of every crucial clause, variable, and constant. for example, database 1 in figure 1 alone does not have a row where "@xmath3. ## evaluation setup we introduce the dataset and the model-predicted queries we use to study our test suite evaluation. we also adapt our test suite evaluation and the official spider metric to make a fair comparison. ## results we first show that our test suite evaluation is reliable by verifying that the test suite distinguishes most neighbor queries, and always makes the correct judgement on 100 model-predicted queries we manually examined (section 6.1). then we use test suite accuracy as a proxy for semantic accuracy to calculate the error rate of the existing commonly used metrics (section 6.2) and their correlations (section 6.3). at last we discuss the computational efficiency of test suite evaluation (section 6.4). ## metrics comparison and analysis we explain how esm and test suite accuracy differ and provide representative examples (figure 8 ). false positives although standard esm is strict, the adapted esm (section 5.3) can introduce false positives because it ignores table variable names. see figure 8 row 1 for an example. false negatives row 2-4 shows that slightly complicated queries usually have semantically equivalent variants, and it is nontrivial to tell whether they are semantically equivalent unless we execute them on a test suite or manually verify. nevertheless, even though test suite accuracy reliably approximates semantic accuracy according to our observation, researchers might also care about other aspects of a model-predicted query. semantic accuracy is only concerned with what are the denotations of a query, but not how it calcu-lates them. for example, figure 8 row 5 represents one of the most common types of false negatives, where the model-predicted query chooses to join other tables even though it is unnecessary. while semantically correct, the model-predicted query increases running time. figure 8 row 7 exhibits a similar but more complicated and rare example. inserting gold values into model-predicted queries as described in section 5 might also unexpectedly loosen the semantic accuracy metric. for example, in figure 8 row 6, the model-predicted query uses the "like" keyword rather than the "=" operator. by sql style conventions, "like" usually precedes a value of the form "%[name]%" and corresponds to natural language query "contains [name]" rather than "matches [name]"; it seems plausible that the model does not understand the natural language query. however, if we replace the wrong value "%[name]%" with the gold value "[name]" after the "like" operator, the predicate becomes semantically equivalent to "= [value]" and hence makes the query semantically correct. value prediction is a crucial part of evaluating textto-sql models. ## discussion and conclusion semantic evaluation via test suites we propose test suite accuracy to approximate the semantic accuracy of a text-to-sql model, by automatically distilling a small test suite with comprehensive code coverage from a large number of random inputs. we assure test suite quality by requiring it to distinguish neighbor queries and manually examining its judgments on model-predicted queries. our test suites will be released for eleven datasets so that future works can conveniently evaluate test suite accuracy. this metric better reflects semantic accuracy, and we hope that it can inspire novel model designs and training objectives. our framework for creating test suites is general and only has two requirements: (1) the input is strongly typed so that the fuzzing distribution i g can be defined and the sample input can be meaningfully executed, and (2) there exist neighbor queries n g that are semantically close but different from the gold g. since these two conditions hold in many execution environments, our framework might potentially be applied to other logical forms, such as λ-dcs @xcite , knowledge graphs @xcite , and python code snippets @xcite if variable types can figure 8 : representative examples where the exact set match (esm) metric is different from test suite accuracy. false positives happen when esm judges a model-predicted query to be correct but test suite accuracy judges it to be wrong. false negatives happen when the reverse takes place. be heuristically extracted. we hope to see more future work that evaluates approximate semantic accuracy on the existing benchmarks and formulates new tasks amenable to test suite evaluation. we do not attempt to solve sql equivalence testing in general. while our test suite achieves comprehensive code coverage of the gold query, it might not cover all the branches of model-predicted queries. adversarially, we can always construct a query that differs from the gold only under extreme cases and fools our metric. fortunately, we never observe models making such pathological mistakes. however, it is necessary to revisit and verify this hypothesis some time later due to goodhardt's law, since researchers will optimize over our metric. beyond semantic evaluation although test suite evaluation provably never creates false negatives in a strict programming language sense, it might still consider "acceptable answers" to be wrong and result in false negatives in a broader sense. for example, in a database of basketball game results, the predicate "a wins" is equivalent to "@xmath4. however, such a relation is not explicitly reflected in the database schema, and our procedure might generate an "unnatural" database where "@xmath5, hence dis-tinguishing the model-predicted query from the gold. fortunately, this issue is mitigated by current models. if "a wins" is mentioned in the text, the model would prefer predicting "a wins" rather than "@xmath6. nevertheless, to completely solve this issue, we recommend future dataset builders to explicitly define the database generation procedure. automatic constraint induction from database content and schema descriptions might also be possible, which is on its own an open research problem. additionally, some answers might be pragmatically acceptable but semantically wrong. for example, if a user asks "who is the oldest person?", the correct answer is a person's name. however, it also makes sense to return both the name and age columns, with the age column sorted in descending order. collecting multiple gold sql query references for evaluation (like machine translation) might be a potential solution. finally, as discussed in section 7, there might be other crucial aspects of a model-predicted query beyond semantic correctness. depending on the goal of the evaluation, other metrics such as memory/time efficiency and readability are also desirable and complementary to test suite accuracy. spider, cosql and sparc have approximately the same statistics, since they share the same database schema and annotation convention. the other eight datasets have significantly longer queries with much more "join" and "where" operations. hence, there are more spans to drop and more neighbors are generated per query. reliability we say that a datapoint can be reliably evaluated if all of its undistinguished neighbors do not fall into the above two categories; then we estimate the fraction of datapoints that can be reliably evaluated for each dataset in