Detecting Logical Errors in SQL Queries

Checking the consistency of query conditions is an old problem, and there are already many proposed solutions. Since the problem is in general undecidable, there is not a unique best one. However, for conjunctions of comparisons, it seems that the method of Guo, Sun, and Weiss (1996) is the state of the art. In this paper, we show how it can be extended to handle many cases of subqueries (and also null values). In this way, the consistency of a surprisingly large subset of SQL can be decided. We apply this consistency check to the task of finding semantic errors in SQL queries. In particular, we discuss possible runtime errors in SQL queries and show how a test for such errors can be reduced to a consistency check. We believe that future database management systems will perform such checks and that the generated warnings will help to develop code with fewer bugs in less time.

[1]  Qi Cheng,et al.  Implementation of Two Semantic Query Optimization Techniques in DB2 Universal Database , 1999, VLDB.

[2]  John Grant,et al.  Logic-based approach to semantic query optimization , 1990, TODS.

[3]  Richard C. T. Lee,et al.  Symbolic logic and mechanical theorem proving , 1973, Computer science classics.

[4]  Michael Minock.  Knowledge Representation using Schema Tuple Queries , 2003, KRDB.

[5]  Hua Yang,et al.  CoBase: A scalable and extensible cooperative information system , 1996, Journal of Intelligent Information Systems.

[6]  Antonija Mitrovic,et al.  Using Evaluation to Shape ITS Design: Results and Experiences with SQL-Tutor , 2002, User Modeling and User-Adapted Interaction.

[7]  Hendrik Decker,et al.  A Uniform Approach to Constraint Satisfaction and Constraint Satisfiability in Deductive Databases , 1988, EDBT.

[8]  Bernard C. Y. Tan,et al.  Three important determinants of user performance for database retrieval , 1999, Int. J. Hum. Comput. Stud..

[9]  Anthony C. Klug.  On conjunctive queries containing inequalities , 1988, JACM.

[10]  Wei Sun,et al.  Solving satisfiability and implication problems in database systems , 1996, TODS.

[11]  Stefan Brass,et al.  Semantic errors in SQL queries: a quite complete list , 2004 .

[12]  Wesley W. Chu,et al.  The design and implementation of CoBase , 1993, SIGMOD '93.

[13]  Charles Welty,et al.  Correcting User Errors in SQL , 1985, Int. J. Man Mach. Stud..

[14]  Parke Godfrey,et al.  An overview of cooperative answering , 1992, Journal of Intelligent Information Systems.

[15]  Hock Chuan Chan,et al.  The relationship between user query accuracy and lines of code , 1999, Int. J. Hum. Comput. Stud..

[16]  David A. Bradbard,et al.  The Effects of Relational and Entity-Relationship Data Models on Query Performance of End Users , 1989, Int. J. Man Mach. Stud..

[17]  Stefan Brass,et al.  Detecting Semantic Errors in SQL Queries , 2003 .

[18]  Sebastiano Bagnara,et al.  Human Error Detection Processes , 1987, Int. J. Man Mach. Stud..

[19]  Antonija Mitrovic,et al.  A Knowledge-Based Teaching System for SQL , 1998 .

[20]  Serge Abiteboul,et al.  Foundations of Databases , 1994 .

[21]  François Bry,et al.  Checking Consistency of Database Constraints: a Logical Basis , 1986, VLDB.

[22]  Alon Y. Halevy,et al.  Static analysis in datalog extensions , 2001, JACM.