Text-to- SQL in the Wild: A Naturally-Occurring Dataset Based on Stack Exchange Data (2021)

Abstract: Most available semantic parsing datasets, comprising of pairs of natural utterances and logical forms, were collected solely for the purpose of training and evaluation of natural language understanding systems. As a result, they do not contain any of the richness and variety of natural-occurring utterances, where humans ask about data they need or are curious about. In this work, we release SEDE, a dataset with 12,023 pairs of utterances and SQL queries collected from real usage on the Stack Exchange website. We show that these pairs contain a variety of real-world challenges which were rarely reflected so far in any other semantic parsing dataset, propose an evaluation metric based on comparison of partial query clauses that is more suitable for real-world queries, and conduct experiments with strong baselines, showing a large gap between the performance on SEDE compared to other common datasets.

## introduction semantic parsing, the task of mapping natural language into logical forms that can be executed on a database or knowledge graph, has been studied mostly on academic datasets, where both the utterances and the queries were written as part of a dataset collection process @xcite @xcite , and not in a natural process where users ask questions about data they need or are curious about. as a result, these datasets generally do not contain any of the richness and diversity of natural-occurring utterances, even if the data on which the questions are asked about is collected from a real-world source. recent methods @xcite @xcite have significantly improved results on such academic datasets: state-ofthe-art models have yield impressive results of over title: questions which attract bad answers description: posts which have attracted significantly more controversial or bad answers than good ones in this work, we take a significant step towards evaluation of text-to-sql models in a real-world setting, by releasing sede: a dataset comprised of 12,023 complex and diverse sql queries and their natural language titles and descriptions, written by real users of the stack exchange data explorer out of a natural interaction. in compared to other text-to-sql datasets, we show that sede contains at least 10 times more sql queries templates (queries after canonization and anonymization of values) than other datasets, and has the most diverse set of utterances and sql queries (in terms of 3-grams) out of all singledomain datasets. we manually analyze a sample of examples from the dataset and list the introduced challenges, such as under-specification, usage of parameters in queries, dates manipulation and more. we also address the challenging problem of evaluating naturally-occurring text-to-sql datasets. in academic datasets, standard evaluation metrics such as denotation accuracy and exact comparison of sql components can often be used with relative success, but we found this to be a greater challenge in sede. denotation accuracy is inaccurate for under-specified utterances, where any single clause not mentioned in the question could entirely change execution results, while exact match comparison of sql components (e.g. comparing all select, where, group by and order by clauses) are often too strict when queries are highly complex. while solving these issues still remains an open problem, to at least partially address them we propose to measure a softer version of the exact match metric, pcm-f1, based on partially extracted queries components, and show that this metric gives a better indication of models' performance than common metrics, which yield a score that is close to 0. finally, we test strong baselines on our dataset, and show that even models that get strong results on spider's development set (63.2% exact-match, 86.3% pcm-f1), perform poorly on our dataset, with a pcm-f1 value of 50.6%. we hope that the unique and challenging properties exhibited in sede 1 will pave a path for future work on gen-1 our dataset and code to run all experiments and metrics is eralization of text-to-sql models in real world setups. ## background in the past decades, a broad selection of datasets have been used as benchmarks for semantic parsing: atis @xcite , geoquery @xcite , @xcite , scholar @xcite , academic @xcite , yelp and imdb @xcite , advising @xcite , wikisql @xcite , spider @xcite , wikitablequestions (pasupat and liang, 2015), overnight @xcite and more. however, the utterances and queries in all of these academic datasets, to the best of our knowledge, were collected explicitly for the purpose of evaluating semantic parsing models, usually with the help of crowd-sourcing (even though in most cases questions are asked about real data). as such, these academic datasets were generated in an artificial process, which often introduces various simplifications and artifacts which are not seen in real-life. utterance-query alignment one arising issue with this artificial process is that utterances are often aligned to their sql queries counterparts, such that the columns and the required computations are explicitly mentioned @xcite . in contrast, natural utterances often do not explicitly mention these, since the schema of the database is not necessarily known to the asking user (for example, the question from spider "titles of films that include 'deleted scenes' in their special feature section" might have been more naturally phrased as "films with deleted scenes" in a real-world setting). well-specified utterances furthermore, the utterances in academic datasets are mostly wellspecified, whereas in contrast, natural utterances are often under-specified or ambiguous; they could be interpreted in different ways and in turn be mapped to different sql queries. consider the example in scope last, in academic datasets the utterances are usually written by crowd-sourced workers, asked to provide utterances on various data domains which they do not necessarily need or are interested with. as a result, the utterances and queries are often not very diverse or realistic, are inherently limited in scope, and might not reflect real-world utterances. ## stack exchange data explorer to introduce a realistic text-to-sql benchmark, we gather sql queries together with their titles and descriptions from a naturally occurring dataset: the stack exchange data explorer. stack exchange is an online question & answers community, with over 3 million questions asked. the data explorer foot_1 allows any user to query the database of stack exchange with t-sql (a sql variant) to answer any question they are curious about. the database schema foot_2 is spread across 29 tables and 211 columns. common utterance topics are published posts, comments, votes, tags, awards, etc. any query that users run in the data explorer is logged, and users are able to save the queries with a title and description for future use by the public. all of these logs are available online, and stack exchange have agreed to release these queries, together with their title, description and other metadata. we publish our clean version of this log, which contains 12,023 samples, of which a subset of 1,714 examples is verified by humans to be correct and is used for validation and test. in this section, we explain the cleaning process, analyze the characteristics of the dataset and compare it to other semantic parsing datasets. ## evaluation semantic parsing models are usually evaluated in two different forms: execution accuracy and logical forms accuracy. in this section, we show why using any of these metrics is difficult with complex queries such as those in sede, and propose a more loose metric for evaluation of models. execution accuracy this metric is measured by executing both the predicted and gold query against a dataset, and considers the query to be correct if the two output results are the same (or similar enough). while this metric appears to be exactly what we want to optimize (yielding a query the outputs a correct output), it does not necessarily cope well with two challenges: spurious queries and under-specified questions. spurious queries are incorrect queries (with respect to the given question) that happen to result in a correct answer, thus leading to a false-positive count. the problem of spuriousness can be addressed by executing the predicted query on modified versions of the dataset, as proposed in @xcite . the second challenge, evaluating under-specification, is arguably harder to address, as mentioned in subsection 3.2. for example, consider a question that asks for "the top 1% active users". this question does not specify which columns should be returned, how the rows should be ordered, and how does one measure "being active". as such, a query could be correct with respect to some interpretation, yet its execution result might be different than the execution result of the given gold query. logical form accuracy instead of comparing execution results, another frequent approach is to simply perform a textual comparison between the predicted and gold queries. when comparing sql queries, it is common to perform a more loose comparison that does not consider the order of appearances of different clauses (e.g. it shouldn't matter which where expression is written first), as performed in spider @xcite . however, as discussed in @xcite , even this looser metric leads to false-negative measures, since multiple queries can all be correct with respect to an utterance, but written in various different manners. due to the richness of sql queries in sede, its extended scope and the fact that queries are written by many different authors, in our case this problem deteriorates: queries can be written in a substantial number of ways. for example, a query that contains a with statement could yield exactly the same result without it, by including a nested from clause instead. ## experiments in this section, we describe our experimental setup, test how strong baselines perform on sede, and analyze their errors. ## conclusion in this work, we take a significant step towards improving and evaluating text-to-sql models in a real world setting, by releasing sede, a dataset comprised of real-world complex and diverse sql queries with their utterances, naturally written by real users. we show that there's a large gap between the performance of strong text-to-sql baselines on sede compared to the commonly studied dataset spider, and hope that the release of this challenging dataset will encourage research on improving generalization for real-world sql prediction.