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]  Qi Cheng,et al.  Implementation of Two Semantic Query Optimization Techniques in DB2 Universal Database , 1999, VLDB.

[2]  Alfred V. Aho,et al.  Efficient optimization of a class of relational expressions , 1979, TODS.

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

[4]  Guido Moerkotte,et al.  Generating consistent test data: Restricting the search space by a generator formula , 1993, VLDB 1993.

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

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

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

[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]  Antonija Mitrovic,et al.  A Knowledge-Based Teaching System for SQL , 1998 .

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

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

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

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

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

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

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

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

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

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

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

[22]  Alin Deutsch,et al.  A chase too far , 2000, SIGMOD 2000.

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

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

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

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

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