1、 专业外文翻译 题 目 Semantic errors in SQL queries: A quite complete list 系 （院） 计算机科学技术系 专 业 计算机科学与技术 班 级 学生姓名 学 号 指导教师 职 称 副教授 毕业设计 (专业外文翻 译 ) 1 Semantic errors in SQL queries: A quite complete list Abstract We investigate classes of SQL queries which are syntactically correct, but certainly not intended,
2、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 (DBMS) execute such queries without any warning. In this paper, we give an extensive list of condit
3、ions 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 app
4、lication 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 object-relational databases. Application programs typically contain a relatively large number of SQL
5、 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 syntac
6、tic 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 n
7、ot 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 毕业设计 (专业外文翻 译 ) 2 query is in
8、correct 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 e.g.
9、 in Oracle 9i and DB2 V8.1 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
10、 happen, e.g. 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
11、 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 compilers do most of the checks that lint was developed for, but in earlier times, C compilers checked just enough so
12、 that theycould generate machine code. We are still at this development stage with SQL today. Printing warnings for strange SQL queries is very uncommon in current database management systems. We currently develop a similar tool for SQL queries (called sqllint). We believe that such a tool would be
13、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, e.g., 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
