Semantic errors in SQL queries: a quite complete list

We investigate classes of SQL queries which are syntactically correct, but certainly not intended, no matter for which task the query was written. For instance, queries that are contradictory, i.e. always return the empty set, are obviously not intended. However, current database management systems execute such queries without any warning. We give an extensive list of conditions that are strong indications of semantic errors. Of course, questions like the satisfiability are in general undecidable, but a significant subset of SQL queries can actually be checked. We believe that future database management systems perform such checks and that the generated warnings help to develop code with fewer bugs in less time.

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

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

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

[4]  Alfred V. Aho,et al.  Efficient optimization of a class of relational expressions , 1978, SIGMOD Conference.

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

[6]  Joe Celko,et al.  Joe Celko's SQL for smarties: advanced SQL programming (2nd editor) , 1995 .

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

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

[9]  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..

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

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

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

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

[14]  Andrea Neufeld Dipl. Inform.,et al.  Generating consistent test data: Restricting the search space by a generator formula , 2005, The VLDB Journal.

[15]  Shing-Chi Cheung,et al.  Automatic generation of database instances for white-box testing , 2001, 25th Annual International Computer Software and Applications Conference. COMPSAC 2001.

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

[17]  Craig A. Knoblock,et al.  Using Inductive Learning To Generate Rules for Semantic Query Optimization , 1996, Advances in Knowledge Discovery and Data Mining.

[18]  Shing-Chi Cheung,et al.  Testing Database Applications with SQL Semantics , 1999, CODAS.

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

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

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

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

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

[24]  Stefan Brass,et al.  Detecting Logical Errors in SQL Queries , 2004, Grundlagen von Datenbanken.

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