【正文】 專業(yè)外文翻譯 題 目 Semantic errors in SQL queries: A quite plete list 系 （院） 計算機科學(xué)技術(shù)系 專 業(yè) 計算機科學(xué)與技術(shù) 班 級 學(xué)生姓名 學(xué) 號 指導(dǎo)教師 職 稱 副教授 畢業(yè)設(shè)計 (專業(yè)外文翻 譯 ) 1 Semantic errors in SQL queries: A quite plete list Abstract 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, . always return the empty set, are obviously not intended. However, current database management systems (DBMS) execute such queries without any warning. In this paper, 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 DBMS will perform such checks and that the generated warnings will help to develop application programs with fewer bugs in less time. Keywords: Databases。 SQL。 Queries。 Bugs。 Errors。 Semantic errors。 Logical errors。 1. Introduction SQL is today the standard language for relational and objectrelational databases. Application programs typically contain a relatively large number of SQL queries and updates, which are sent to the DBMS for execution. As any program code, SQL queries can contain errors (Updates are not considered in this paper, but they are usually much simpler than queries.). Errors in SQL queries can be classified into syntactic errors and semantic errors. A syntactic error means that the entered character string is not valid SQL. Then any DBMS will print an error message because it cannot execute the query. Thus, the error is certainly detected and usually easy to correct. A semantic error means that a legal SQL query was entered, but the query does not or not always produce the intended results, and is therefore incorrect for the given task. Semantic errors can be further classified into cases where the task must be known in order to detect that the query is incorrect, and cases where there is sufficient evidence that the 畢業(yè)設(shè)計 (專業(yè)外文翻 譯 ) 2 query is incorrect no matter what the task is. Our focus in this paper is on this latter class, since there is often no independent specification of the goal of the query. For instance, consider this query: SELECT * FROM EMP WHERE JOB = ‘CLERK’ AND JOB = ‘MANAGER’ This is a legal SQL query, and it is executed . in Oracle 9i and DB2 without any warning. But the condition is inconsistent: The query result will be always empty. Since nobody would use a database in order to geta certainly empty result, we can state that this query is incorrect without actually knowing the task of the query. Such cases do happen, . in one exam exercise that we analyzed, 10 out of 70 students wrote an inconsistent condition. It is well known that the consistency of formulas is undecidable, and that this applies also to database queries. However, although the task is in general undecidable, many cases that occur in practice can be detected with relatively simple algorithms. Our work is also inspired by the program lint, which is or was a semantic checker for the ‘‘C’’ programming language. Today C pilers do most of the checks that lint was developed for, but in earlier times, C pilers checked just enough so that theycould generate machine code. We are still at this development stage with SQL today. Printing warnings for strange SQL queries is very unmon in current database management systems. We currently develop a similar tool for SQL queries (called sqllint). We believe that such a tool would be useful not only in teaching, but also in application software development. At least, a good error message could speed up the debugging process. Furthermore, runtime errors are possible in SQL, ., in some contexts, SQL queries or subqueries must return not more than one row. The occurrence of this error depends on the database state (the data), therefore it is not necessarily found during testing. Certainly it would be good to prove that all queries in a program can never violate this condition. Our tool does not depend on the data, it only takes the schema information (including 畢業(yè)設(shè)計 (專業(yè)外文翻 譯 ) 3 constraints) and an SQL query as input. Therefore, it is not necessary to check the queries in each execution. Furthermore, we do not need test data: All input to our tool is (part of) the program code that is anywaywritten during software development. The only exception is the specification of ‘‘soft keys’’ (see Section 5)which would be useful for some of our checks. While the title of this paper is ‘‘Semantic Errors’’, people from piler construction have advised us that what we pute are really ‘‘warnings’’, since the queries are still executable, andin some cases only ‘‘notices’’ about bad style. Nevertheless, this information would help to improve the software quality of database application programs, which are a significant subset of the software developed today. The main contribution of this paper is a list of semantic errors that represents years of experience while correctin hundreds of exams that contained SQL queries. However, we have also tried to explain the general principles from which these errors can be derived (as far as possible). Therefore, it is not simply by chance whether an error appears on our list, but the list has a certain degree of pleteness (except possibly in Section 4). While our experience so far has only been with errors made by students, not professional programmers, most of the students will bee programmers, and they will not immediately make fewer errors. In the exam solutions that we analyzed, 24% contained a syntax error, 18% contained a semantic error of the type