PaperlayerView exact revision on arXiv

Source-linked AI summary

Semantic Evaluation for Text-to-SQL with Distilled Test Suites

Ruiqi Zhong, Tao Yu, Dan Klein

arXiv:2010.02840v1cs.CLcs.AI

TL;DR

Text-to-SQL evaluation must distinguish semantic equivalence without relying on either exact strings or a single database. The paper distills compact, high-coverage test suites from random databases and evaluates predicted-query denotations on them. On Spider, it verified all 100 sampled judgments and found substantial false negatives in the current metric, while releasing suites for eleven datasets.

  • Problem

    Exact string match is too strict and single-database denotation evaluation is too loose for determining whether predicted and gold SQL queries are semantically equivalent.

  • Method

    The method generates random databases, retains a compact subset that distinguishes neighbor queries from the gold query, and checks predicted-query denotations on that distilled suite.

  • Results

    2.5% false negatives occurred on average and 8.1% in the worst case for ESM, while the proposed method was correct on all 100 manually examined cases.

  • Takeaways & Limitations

    Test suite accuracy better reflects semantic accuracy for Text-to-SQL evaluation, with released test suites supporting eleven popular datasets.

  • Takeaways & Limitations

    The test suite covers the gold query comprehensively but may miss branches of predicted queries, allowing adversarial pathological mistakes to fool the metric.

Abstract

from arXiv · show

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.

1 Introduction

The paper introduces test suite accuracy, which uses compact, high-coverage database suites to approximate Text-to-SQL semantic accuracy efficiently. It shows that existing evaluation metrics can misjudge semantic correctness and reports reliable evaluation across Spider submissions and eleven datasets.

  • Motivation: Semantic evaluation is difficult because exact string match rejects semantically equivalent queries, while single-database denotation checks can accept incorrect queries.Exact string match produces false negatives; single-database evaluation can produce false positives when queries differ on another database.
  • Method: Test suite accuracy compares predicted and gold-query denotations on a compact database suite with high code coverage.The method aims to approximate semantic accuracy without evaluating on a large number of random databases each time.
  • Method: Neighbor queries modify one aspect of the gold query, allowing the search objective to retain databases that distinguish likely semantic errors.Modifications include changing constants, strings, comparison operators, column names, or removable query spans.
  • Method: The method generates many random databases and retains a small fraction that distinguish neighbor queries from the gold query.During evaluation, predicted queries are checked only on the distilled test suite.
  • Evaluation: More than 99% of neighbor queries were distinguished using 1000 random databases for Spider, and the method was correct on all 100 manually examined disagreements.The test suite was also used to evaluate 21 Spider leaderboard submissions.
  • Evaluation: ESM had a 2.5% false negative rate on average and 8.1% in the worst case, while undervaluing one 61% semantic-accuracy submission by 8%.The analysis reports that ESM increasingly diverges from semantic accuracy on more complex queries.
  • Contributions: The authors provide software and distilled test suites for eleven popular Text-to-SQL datasets.The listed datasets include Spider, CoSQL, SParC, Academic, Advising, ATIS, Geography, IMDB, Restaurants, Scholar, and Yelp.

2 Problem Statement

The paper formalizes semantic equivalence as equality of query denotations on every possible database. Because exact match and single-database evaluation are respectively too strict and too loose, it defines test-suite-based evaluation as an intermediate approximation.

  • Semantic Equivalence: Two SQL queries are semantically equivalent when they produce the same denotation on every possible database.The database space and query space are denoted by W and Q, respectively.
  • Existing Metrics: Exact string match is too strict because different query strings can have the same semantics.This creates false negatives for semantically equivalent predictions.
  • Existing Metrics: Single denotation accuracy is too loose because two queries matching on one database may differ on another database.A query can therefore appear correct under a particular database while remaining semantically different from the gold.
  • Test-Suite Evaluation: A test suite distinguishes two queries when at least one database in the suite gives them different denotations.The indicator function D_S(g, q) records whether the suite distinguishes the gold query g from predicted query q.
  • Test-Suite Evaluation: Under test-suite evaluation, a predicted query is correct when the suite does not distinguish it from the gold query.Equivalently, the queries have matching denotations on every database in the suite.
  • Evaluation Objective: The paper positions the metrics in the implication chain exact match ⇒ semantic accuracy ⇒ test suite accuracy ⇒ single denotation accuracy.Its objective is to construct a small suite that provides a tight upper bound for semantic accuracy efficiently.

3 Desiderata

The paper defines desirable test suites by balancing computational efficiency with comprehensive code coverage. Coverage is operationalized through distinguishing semantically different neighbor queries generated by localized modifications to the gold query.

  • Computational Efficiency: A desirable test suite S_g should be small to speed up test suite evaluations.
  • Code Coverage: Comprehensive code coverage requires testing every branch and clause of the gold query, including crucial clauses, variables, and constants.A single database can miss relevant cases, such as rows below or equal to a comparison threshold.
  • Code Coverage: Neighbor queries are close to the gold query in surface form but likely semantically different, making them probes for code coverage.
  • Neighbor Queries: The method generates neighbors by replacing constants, strings, comparison operators, or column names, and by dropping semantically nonessential query spans.Modified queries that cannot execute are removed, and only one aspect is changed at a time.
  • Optimization Objective: A test suite has high quality when it distinguishes the gold query from all its neighbor queries.For threshold comparisons, this requires databases containing values above, below, and equal to the threshold.

4 Fuzzing

The paper uses fuzzing to sample constrained random databases and greedily distill a compact test suite. Generated databases preserve schema, type, and foreign-key requirements while varying values to expose query differences.

  • Fuzzing: Fuzzing generates many random inputs to test whether a program satisfies a target property such as SQL equivalence.
  • Fuzzing: The method keeps a small fraction of randomly sampled databases to distill the test suite S_g.
  • Sampling Databases: Random databases must satisfy the gold query’s table names, column names, foreign-key structure, and data types.
  • Sampling Databases: Foreign-key dependencies determine generation order: referenced tables are produced first using a topological sort.If c1 refers to c2, every value in c1 must belong to c2.
  • Sampling Databases: Column values are sampled from type-appropriate random distributions, while gold-query constants and close variants are added to increase potential code coverage.
  • Test Suite Construction: The greedy procedure adds a sampled database when it distinguishes a neighbor query not distinguished by the current suite.The implementation shares random databases across gold queries associated with the same schema to save disk space.

5 Evaluation Setup

The evaluation studies test suite evaluation on Spider development-set predictions from 21 diverse model submissions. It adapts both the test-suite procedure and Spider’s official metric to support a fair comparison.

  • Dataset: The study uses Spider’s development set, containing 1034 English utterances and SQL queries across 20 database schemata.Spider groups examples into easy, medium, hard, and extrahard categories by gold SQL complexity.
  • Models: The evaluation uses model-predicted queries from 21 submissions spanning diverse architectures, decoding strategies, and pre-training methods.Their accuracy ranges from below 40% to above 70%.
  • Official Metric: The official Spider metric is exact set match, which compares whether parsed queries contain the same set of clauses.It avoids false negatives from semantically equivalent clause reordering but remains strict and can produce false negatives.
  • Metric Adaptation: The adapted evaluation enumerates gold-constant replacements because the Spider metric does not check constant prediction correctness.
  • Metric Adaptation: The adapted comparison accounts for column-order equivalence, repairs Spider’s ignored-join-predicate bug, and retains its table-variable-name feature.Retaining table-variable-name equivalence can introduce false positives.

6 Results

The distilled test suite reliably distinguishes most neighbor queries and matched manual semantic judgments on all 100 examined model predictions. Compared with traditional metrics, it reveals substantial errors and weaker agreement on harder queries, while remaining computationally feasible.

  • 6.1 Reliability: > 99% of neighbor queries were distinguished using 1000 random databases, whereas single-database checking missed 5%.The fuzzing curve stopped decreasing after around 600 random databases, and constructing the suite took around a week on 16 CPUs.
  • 6.1 Reliability: 1% of neighbor queries remained indistinguishable across sampled databases; most were semantically equivalent to the gold.The approach can struggle with queries differing only at floating-point precision, although manual evaluation found no resulting false positive.
  • 6.1 Reliability: 100 of 100 model-predicted queries judged correct by test suite evaluation but incorrect by ESM were manually verified as semantically equivalent to the gold.The examined queries came from 21 Spider leaderboard submissions.
  • 6.2 Errors of Traditional Metrics: 8% was the worst-case false positive rate for single denotation accuracy on the entire development set, increasing by 4% on the extrahard fraction.Single denotation accuracy checks predictions only on the databases originally released with Spider.
  • 6.3 Correlation with Existing Metrics: 91.4% was the aggregate Kendall τ correlation between ESM and test suite accuracy, falling to 74.1% on the hard fraction.Five of 21 submissions on the hard fraction had more than four submissions with lower test suite accuracy but higher ESM scores.
  • 6.4 Computational Efficiency: 695 databases occupied 3.27GB, and running all gold queries took 75.3 minutes on one CPU.The test suites averaged 42 databases per query, shared suites across queries with the same schema, and remained parallelizable and affordable by most researchers.

7 Metrics Comparison and Analysis

The section examines disagreements between exact set match and test suite accuracy, including false positives, false negatives, and limitations beyond semantic correctness.

  • False Positives: Adapted ESM can produce false positives because it ignores table variable names.Figure 8 row 1 provides an example.
  • False Negatives: Slightly complicated queries often have semantically equivalent variants that are difficult to identify without test-suite execution or manual verification.Figure 8 rows 2–4 illustrate these false negatives.
  • Beyond semantic accuracy: Semantic accuracy evaluates denotations but not how a query computes them.The authors distinguish semantic correctness from other evaluation concerns.
  • Beyond semantic accuracy: Semantically correct queries may still be undesirable when they calculate results inefficiently, such as by joining unnecessary tables.Figure 8 rows 5 and 7 show this pattern.
  • Value prediction: Replacing predicted values with gold values can loosen the metric: a wrong LIKE pattern may become semantically equivalent to equality.The example uses LIKE with “%[name]%” versus the gold value “[name]”.

8 Discussion and Conclusion

The paper presents test suite accuracy as a practical semantic-evaluation framework while identifying scope boundaries, pathological cases, and broader notions of acceptable answers and query quality.

  • Conclusion: Test suite accuracy distills compact, high-coverage suites from random inputs, checks neighbor queries, and is released for eleven datasets.The authors position it as a metric that better reflects semantic accuracy.
  • Scope: The framework requires strongly typed inputs and semantically close but distinct neighbor queries, potentially extending beyond SQL to other logical forms.These are the framework’s two stated requirements.
  • Metric comparison: Exact set match can miss semantic equivalence, including EXCEPT and NOT IN variants.Figure 8 also illustrates false positives involving table variable names.
  • Future work: The authors recommend future work on approximate semantic accuracy for existing benchmarks and on tasks suited to test-suite evaluation.This is stated as a future research direction.
  • Limitations: The method does not solve SQL equivalence testing in general and may be fooled by queries differing only under extreme cases.The authors report not observing such pathological mistakes but recommend revisiting the assumption as researchers optimize the metric.
  • Broader evaluation: Some pragmatically acceptable answers may be semantically wrong, motivating multiple gold SQL references as a potential solution.The example returns both a person’s name and age for an oldest-person question.
  • Broader evaluation: Memory/time efficiency and readability are complementary evaluation dimensions beyond semantic correctness.The paper identifies these metrics as desirable depending on evaluation goals.

A.1 Algorithmic Description of Section 4.2

Algorithm 1 distills a test suite by sampling database inputs and retaining those that distinguish neighbor queries from the gold query.

  • Initialization: The algorithm initializes an empty suite S_g and sets N to the neighbor-query set N_g.I_g is the distribution of database inputs used for sampling.
  • Sampling: It iterates through 1000 sampled inputs from the fuzzing process.Each iteration evaluates the sampled database against the current neighbor set.
  • Selection: The algorithm adds a selected input w_t to S_g and removes the distinguished query q from N.It returns S_g as the distilled test suite.

A.2 Test Suite for Other Datasets

The appendix describes released suites and their statistics across eleven datasets, noting shared suites, dataset differences, and reliability limits of random fuzzing.

  • Dataset coverage: Test suites are distilled for eleven datasets, with the test split used when defined and the entire dataset otherwise.The listed datasets include Academic, Advising, ATIS, Geography, IMDB, Restaurants, Scholar, and Yelp.
  • Dataset coverage: Spider, CoSQL, and SParC share one development-set test suite because they use the same 20 database schemata.The remaining datasets have distinct suite construction settings.
  • Statistics: Table 4 reports query counts, execution time, suite size, original size, average neighbor counts, and undistinguished fractions.The table indicates whether smaller or larger values are preferred for these statistics.
  • Statistics: The other eight datasets contain longer queries with more JOIN and WHERE operations, generating more dropped spans and neighbors.Spider, CoSQL, and SParC have approximately the same statistics because they share schema and annotation conventions.
  • Reliability: Random fuzzing cannot distinguish a non-trivial fraction of neighbors for some datasets, including cases caused by complex WHERE structures and high count thresholds.These cases require unusually specific database contents.
  • Reliability: Most queries can be reliably evaluated, but ATIS and Advising may need hand-crafted suites for greater reliability.A datapoint is reliable when its undistinguished neighbors avoid the two identified difficult categories.
  • Denotations: At least one suite database produces a non-empty gold denotation for every datapoint across all eleven datasets.This contrasts with evaluation restricted to datapoints whose gold denotation is non-empty.

A.3 Evaluation on WikiSQL

On WikiSQL, the test-suite evaluation strategy was tested against real model predictions and generally aligned with semantic distinctions, but one uncovered clause caused an error and the authors caution against using it for WikiSQL.

  • Model-Predicted Queries: The evaluation used real predictions from eight models, drawn from six prior works, after converting compatible outputs into SQL queries.The cited models include MQAN unordered, X-SQL, HydraNet, IncSQL, and SQLova variants.
  • Test Suite Generation: The fuzzing procedure generated a WikiSQL test suite that could distinguish all neighboring queries because WikiSQL queries contain only a simple WHERE clause with aggregation.The most complex WikiSQL query consists of a single WHERE clause with an aggregation operation.
  • Metric Difference: After filtering the count-any-column equivalence pattern, test-suite accuracy still found one prediction that ESM marked wrong but the test suite marked correct.Counting any column in a table produces the same denotation, despite differing surface forms.
  • Metric Difference: Only one model-predicted query produced an error: an extra WHERE col2 > 10 clause was not covered by the test suite.The prediction was not semantically equivalent to the gold query, so the test suite incorrectly accepted it.
  • Metric Difference: The authors do not recommend test-suite accuracy for WikiSQL because its only semantically equivalent gold-query variant changes the counted column, which may matter for code readability.The test suite can miss distinctions that researchers may still want to inspect for readability.
  • Metric Difference: The study also compared test-suite accuracy with adapted exact set match, official Spider exact set match, and single denotation accuracy across difficulty splits.These comparisons correspond to Figures 9, 10, and 11, respectively.
Loading 2010.02840v1…