Archer: A Human-Labeled Text-to- SQL Dataset with Arithmetic, Commonsense and Hypothetical Reasoning (2024)

Abstract: We present Archer, a challenging bilingual text-to-SQL dataset specific to complex reasoning, including arithmetic, commonsense and hypothetical reasoning. It contains 1,042 English questions and 1,042 Chinese questions, along with 521 unique SQL queries, covering 20 English databases across 20 domains. Notably, this dataset demonstrates a significantly higher level of complexity compared to existing publicly available datasets. Our evaluation shows that Archer challenges the capabilities of current state-of-the-art models, with a high-ranked model on the Spider leaderboard achieving only 6.73% execution accuracy on Archer test set. Thus, Archer presents a significant challenge for future research in this field.

## introduction the text-to-sql task is an important nlp task, which maps input questions to meaningful and executable sql queries, enabling users to interact with databases in a more intuitive and userfriendly manner. state-of-the-art methods @xcite @xcite relying on large language models have achieved execution accuracy above 75% on the spider dataset @xcite , which encompasses complex sql grammar and cross-domain settings. recently, @xcite achieved remarkable results with an impressive 85.3% execution accuracy on the spider dataset, leveraging the enhanced capabilities of gpt-4. however, previous text-to-sql datasets @xcite @xcite @xcite @xcite @xcite , have limitations that prevent them from capturing complex reasoning effectively. for example, spider @xcite purposely excludes questions that arithmetic reasoning figure 1 : archer examples with three reasoning types: arithmetic, commonsense, and hypothetical reasoning. (see more examples in appendix d) would require external knowledge @xcite , like that from common-sense knowledge graphs or mathematical calculations. this exclusion limits spider's ability to properly test how well models can handle real-world scenarios, which often require a deeper level of reasoning capabilities. in this paper, we present archer, an innovative dataset designed to incorporate three distinct types of reasoning: arithmetic, commonsense, and hypothetical reasoning. by including such varied reasoning skills, archer seeks to challenge and expand the capabilities of text-to-sql models, equipping them to manage more intricate and nuanced queries. figure 1 showcases data examples from archer that demonstrate these three reasoning abilities. to evaluate the challenge posed by archer, we conducted experiments with both large language models (llms) and fine-tuned models. however, all models demonstrated inferior performance when dealing with archer. even the model that achieved a high place on the spider leaderboard managed only 6.73% execution accuracy on archer test sets. these findings highlight substantial potential for improvement, indicating that archer indeed provides a significant challenge to current models. ## reasoning types in this section, we present the three different types of reasoning in archer: arithmetic, commonsense, and hypothetical reasoning. arithmetic reasoning arithmetic reasoning pertains to the act of resolving mathematical problems through logical and analytical thought processes, involving datatype values @xcite and arithmetic operators. according to an analysis of sql queries from practical applications like the baidu search engine and customer service and data analysis robots by @xcite , mathematical calculations account for a significant portion across sql applications. however, previous high-quality datasets contain very few questions that involve calculations, and such questions are typically auto-generated with simple grammar. in contrast, all question-sql pairs included in archer necessitate arithmetic reasoning and are manually annotated to ensure high quality. commonsense reasoning commonsense reasoning refers to the capacity to make logical deductions based on implicit (and possibly uncertain) commonsense knowledge @xcite @xcite @xcite @xcite , including, e.g., a broad understanding of how things function in the world. commonsense knowledge can be useful for both zero-shot learning @xcite @xcite and model explanations @xcite . archer includes questions that necessitate models to comprehend the database, infer missing details, and generate logical inferences to create accurate sql queries. as illustrated in figure 1 , for the question "which 4-cylinder car needs the most fuel to drive 300 miles? list how many gallons it needs, and its make and model.", the database does not provide an explicit schema about the fuel used to travel 300 miles for each car. it only provides each car's fuel consumption in mpg. solving this question requires commonsense knowledge, specifically the understanding of "fuel used is calculated by dividing distance driven by fuel consumption" to derive the correct sql. hypothetical reasoning hypothetical reasoning takes the complexity a step further, requiring models to have counterfactual thinking ability, which is the ability to imagine and reason over unseen cases based on the seen facts and counterfactual assumptions. archer includes questions that involve hypothetical situations, requiring the model to understand and reason about conditional relationships. as illustrated in figure 1 , consider the hypothetical question "if all cars produced by the daimler benz company have 4-cylinders, then in all 4-cylinder cars, which one needs the most fuel to drive 300 miles? please list how many gallons it needs, along with its make and model.". in this question, the underlying assumption contradicts the factual information stored in the database. the model must comprehend this assumption and convert it into the sql condition d.@xmath0.@xmath1. ## corpus construction as illustrated in figure 2 , we create archer in the following six steps, spending around 300 hours of human labor in total: §3.1 database collection, §3.2 question annotation, §3.3 sql annotation, §3.4 sql review, §3.5 question review and paraphrase, §3.6 final review and process. ## dataset statistics and comparison in ## conclusion in this paper, we present archer, a complex bilingual text-to-sql dataset with three distinct reasoning types: arithmetic, commonsense, and hypothetical reasoning. experimental results on archer, obtained from both llms and fine-tuned models, suggest plenty of space for improvement. ethical approval. this exemption was based on the fact that all participants were employees of the university of edinburgh and were therefore protected by employment law. furthermore, participants received compensation at the standard hourly rate designated for tutors and demonstrators at the university. to promote academic usage, we intend to freely release the dataset online.