Synthesizing highly expressive SQL queries from input-output examples

Abstract

SQL is the de facto language for manipulating relational data. Though powerful, SQL queries can be difficult to write due to their highly expressive constructs. Using the programming-by-example paradigm to help users write SQL queries presents an attractive proposition, as evidenced by online help forums such as Stack Overflow. However, developing techniques to synthesize SQL queries from input-output (I/O) examples has been difficult due to SQL's rich set of operators. In this paper, we present a new scalable and efficient algorithm to synthesize SQL queries from I/O examples. Our key innovation is the development of a language for abstract queries, i.e., queries with uninstantiated operators, that can express a large space of SQL queries efficiently. Using abstract queries to represent the search space nicely decomposes the synthesis problem into two tasks: (1) searching for abstract queries that can potentially satisfy the given I/O examples, and (2) instantiating the found abstract queries and ranking the results. We implemented the algorithm in a new tool, called SCYTHE, and evaluated it on 193 benchmarks collected from Stack Overflow. Our results showed that SCYTHE efficiently solved 74% of the benchmarks, most in just a few seconds. Queries synthesized by SCYTHE range from simple ones involving a single selection to complex ones with six levels of nested queries.

SQLizer: query synthesis from natural language

“…Related work in this area can be categorized into three classes, depending on the form of specifications provided by the user. In one line of work on query synthesis, users convey their intent to the system through the use of input-output examples [Tran et al 2009;Wang et al 2017;Zhang and Sun 2013;Zloof 1975]. Specifically, the input to the system is a miniature version of the database, and the output is the desired table that should be extracted from this database using the target query.…”

“…Although many end-users need to query data stored in some relational database, they typically lack the expertise to write complex queries in declarative query languages such as SQL. As a result, there has been a flurry of interest in automatically synthesizing SQL queries from informal specifications [Feng et al 2017a;Li and Jagadish 2014;Popescu et al 2003;Wang et al 2017;Zhang and Sun 2013].…”

“…Existing techniques for automatically synthesizing SQL queries fall into two different classes, namely example-based approaches and those based on natural language. Programming-by-example techniques, such as Scythe [Wang et al 2017] and SQLSynthesizer [Zhang and Sun 2013], require the user to present a miniature version of the database together with the expected output. A shortcoming of such example-directed techniques is that they require the user to be familiar with the database schema.…”

Cognification of Program Synthesis—A Systematic Feature-Oriented Analysis and Future Direction

“…4 [34][35][36][37][38][39][40][41][42][43][44][45][46][47][48] Debugging information, programming-by-example with input/output (I/O) examples for expressing user intent.…”

Synthesizing replacement classes

“…This strategy has been applied to synthesize programs in various domains [Barowy et al 2015;Cheung et al 2013;Drachsler-Cohen et al 2017;Peleg et al 2018;Polikarpova et al 2016;Schlaipfer et al 2017;Wang et al 2017a]. Cheung et al [Wang et al 2017a] use this technique to synthesize expressive SQL queries for databases. Schlaipfer et al [Schlaipfer et al 2017] analyze a given query to identify sub queries that can be optimized e ectively.…”