A METHOD OF SOFTWARE DESIGN ACCORDING TO THE ANALYSIS OF SQL QUERY ERRORS

Abstract

The article presents the results of the analysis of methods for detecting semantic errors for a declarative programming language - the results of the analysis by Brass and Goldberg, who performed the analysis and discovered that there are not only syntactic, but also semantic errors that affect the operation of the program. A list of frequently encountered semantic errors during query generation is provided, a set of 191,834 of the collected queries was used to analyze semantic errors, more than 36,000 contained an error. For each error, a description of the problem is made, an example of a typical error is given, and ways to solve it, a possible option for implementing a tool for their detection for further use. Analyzing the prevalence of semantic errors in SQL queries, the most common errors were found to be missing join predicates, followed by persistent source column errors, and unnecessary counter arguments. It was also found that the co-occurrence of semantic problems in SQL queries for the entire data set is quite low, indicating that queries rarely contain more than one semantic error. The highest similarity between the two problems is 20% for an unnecessary count argument and an unnecessary grouping by attribute. It was also found that more complex queries in terms of the number of compounds, predicates and functions used tended to suffer from more semantic errors, an interesting finding that could be used in the future as a metric to early predict whether a query might contain semantic errors or not. Today, there are many resources on the Internet that contain many queries with problems of this type. Therefore, developers who read this study and the problems described above will appreciate the problem and will allocate more time to identify these problems in order to eliminate them immediately after they are discovered, and not during the operation of the program.