Axiomatic foundations and algorithms for deciding semantic equivalences of SQL queries

Chu, Shumo; Murphy, Brendan; Roesch, Jared; Cheung, Alvin; Suciu, Dan

doi:10.14778/3236187.3236200

Cited by 41 publications

(43 citation statements)

References 38 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…Since our goal is to synthesize a new version of the program that is equivalent to a previous version, this paper is particularly related to verification techniques for checking Synthesizing Database Programs for Schema Refactoring PL, 2019, USA equivalence [11-13, 52, 54]. Most of these papers focus on equivalence between individual SQL queries [11][12][13]52]. The only work that addresses the problem of verifying equivalence between entire database programs is Mediator, which automatically infers a bisimulation invariant between the two programs [54].…”

Section: Related Workmentioning

confidence: 99%

Synthesizing database programs for schema refactoring

Wang

Dong

Shah

et al. 2019

Proceedings of the 40th ACM SIGPLAN Conference on Programming Language Design and Implementation

View full text Add to dashboard Cite

Many programs that interact with a database need to undergo schema refactoring several times during their life cycle. Since this process typically requires making significant changes to the program's implementation, schema refactoring is often non-trivial and error-prone. Motivated by this problem, we propose a new technique for automatically synthesizing a new version of a database program given its original version and the source and target schemas. Our method does not require manual user guidance and ensures that the synthesized program is equivalent to the original one. Furthermore, our method is quite efficient and can synthesize new versions of database programs (containing up to 263 functions) that are extracted from real-world web applications with an average synthesis time of 69.4 seconds.CCS Concepts • Software and its engineering → Programming by example; Automatic programming; • Information systems → Database utilities and tools.

show abstract

Section: Related Workmentioning

confidence: 99%

Synthesizing database programs for schema refactoring

Wang

Dong

Shah

et al. 2019

Proceedings of the 40th ACM SIGPLAN Conference on Programming Language Design and Implementation

View full text Add to dashboard Cite

show abstract

“…State-of-the-art automated tools for determining QE adopt algebraic approaches [51,26]. While COSETTE uses K-relations for representing SQL queries, UDP leverages U-semirings.…”

Section: Challenges For Algebraic Approachesmentioning

confidence: 99%

“…Lastly, they cannot model the semantics of certain SQL operators (e.g., aggregate functions and different types of OUTER JOIN). These limitations restrict the set of queries that can be supported by UDP's proof assistant [26].…”

Section: Challenges For Algebraic Approachesmentioning

confidence: 99%

See 1 more Smart Citation

Automated verification of query equivalence using satisfiability modulo theories

et al. 2019

View full text Add to dashboard Cite

Database-as-a-service offerings enable users to quickly create and deploy complex data processing pipelines. In practice, these pipelines often exhibit significant overlap of computation due to redundant execution of certain sub-queries. It is challenging for developers and database administrators to manually detect overlap across queries since they may be distributed across teams, organization roles, and geographic locations. Thus, we require automated cloud-scale tools for identifying equivalent queries to minimize computation overlap. State-of-the-art algebraic approaches to automated verification of query equivalence suffer from two limitations. First, they are unable to model the semantics of widely-used SQL features, such as complex query predicates and three-valued logic. Second, they have a computationally intensive verification procedure. These limitations restrict their efficacy and efficiency in cloud-scale database-as-aservice offerings. This paper makes the case for an alternate approach to determining query equivalence based on symbolic representation. The key idea is to effectively transform a wide range of SQL queries into first order logic formulae and then use satisfiability modulo theories to efficiently verify their equivalence. We have implemented this symbolic representation-based approach in EQUITAS. Our evaluation shows that EQUITAS proves the semantic equivalence of a larger set of query pairs compared to algebraic approaches and reduces the verification time by 27×. We also demonstrate that on a set of 17,461 real-world SQL queries, it automatically identifies redundant execution across 11% of the queries. Our symbolic-representation based technique is currently deployed on Alibaba's MaxCompute database-as-a-service platform.

show abstract

“…However, if we first inline the WITH clause and then find edits, two edits would be required. 4 Hence when applying edits, we should deduct marks based on the edits that would have been required on the original student query. In the space of edits we consider, other syntactic canonicalizations do not increase the edit distance.…”

Section: Shortest Path Algorithmmentioning

confidence: 99%

Automated Grading of SQL Queries

Chandra

Banerjee

Hazra

et al. 2019

2019 IEEE 35th International Conference on Data Engineering (ICDE)

View full text Add to dashboard Cite

Grading student SQL queries manually is a tedious and errorprone process. Earlier work on testing correctness of student SQL queries, such as the XData system, can be used to test correctness of a student query. However, in case a student query is found to be incorrect there is currently no way to automatically assign partial marks. Partial marking is important so that small errors are penalized less than large errors. Manually awarding partial marks is not scalable for classes with large number of students, especially MOOCs, and is also prone to human errors.In this paper, we discuss techniques to find a minimum cost set of edits to a student query that would make it correct, which can help assign partial marks, and to help students understand exactly where they went wrong. Given the limitations of current formal methods for checking equivalence, our approach is based on finding nearest query, from a set of instructor provided correct queries, that is found to be equivalent based on query canonicalization. We show that exhaustive techniques are expensive, and propose a greedy heuristic approach that works well both in terms of runtime and accuracy on queries in real-world datasets. Our system can also be used in a learning mode where query edits can be suggested as feedback to students to guide them towards a correct query. Our partial marking system has been successfully used in courses at IIT Bombay and IIT Dharwad.

show abstract

Axiomatic foundations and algorithms for deciding semantic equivalences of SQL queries

Cited by 41 publications

References 38 publications

Synthesizing database programs for schema refactoring

Synthesizing database programs for schema refactoring

Automated verification of query equivalence using satisfiability modulo theories

Automated Grading of SQL Queries

Contact Info

Product

Resources

About