Method, device and readable medium for detecting logical defects of database management system

Abstract

The application discloses a kind of detection method, device and readable medium of logical defect of database management system, by obtaining the table in the database randomly generated;According to table, first query statement is established, and the query based on first query statement is used as seed query;By approximate variation synthesis, a plurality of second query statements with approximate relationship with first query statement, and the query based on a plurality of second query statements is used as synthesis query;First query statement and a plurality of second query statements are input into database management system, and the result of seed query and the result of synthesis query are obtained;Whether the result of synthesis query and the result of seed query violate approximate relationship is judged, if yes, it is determined that database management system exists logical defect.Through synthesis seed query's over-approximation or under-approximation, its query result is the superset or subset of seed query result, whether database management system exists logical defect can be effectively judged, improve the accuracy and efficiency of detection.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of database management, and in particular to a method and device for detecting logical defects of a database management system and a readable medium. BACKGROUND

[0002] Database Management System (DBMS) is widely used as a key component of modern enterprise software, and its correctness and reliability are crucial to many enterprise applications, such as online banking, electronic shopping, electronic payment, etc. Therefore, DBMS testing has attracted considerable attention in industry and academia. For example, as a widely used testing technique, fuzz testing has been widely applied to DBMS and has shown its effectiveness in detecting crash defects. However, as another typical error, logical defects can cause DBMS to return incorrect results for a given query, which can easily be overlooked by developers because they do not have as obvious a manifestation as system crashes.

[0003] With the widespread use of DBMS in real-world industrial scenarios, its reliability and correctness have received increasing attention. As a complex software system, DBMS can have crashes or other unexpected conditions. Notably, logical defects are one of the most intractable errors in DBMS. The results of the execution of SQL queries on DBMS can be incorrect, which means that the semantics of the query have not been correctly implemented.

[0004] The main methods for detecting logical defects include various automated testing techniques. However, it is not easy to design effective automated testing techniques. One of the key challenges is how to automatically represent the expected results of a given query, which is also a classic problem in automated testing, the test oracle problem. To address this challenge, researchers have proposed various methods to construct test oracles. The first category is based on differential testing, which provides the same SQL query to different DBMS for execution and uses the query results to construct the test oracle. Specifically, inconsistencies between the results returned by different DBMS indicate potential logical defects. However, as existing studies have shown, differential testing cannot be used when the generated SQL query does not conform to the syntax of all selected DBMS, or the operations on different DBMS have different semantics. Although all DBMS support SQL core syntax, each DBMS provides various extensions and forms its own dialect, which greatly limits the universality of differential testing.

[0005] The second category is prophecy-guided query synthesis, which does not rely on multiple DBMSs and thus alleviates the limitations of differential testing. It first specifies a randomly chosen row in a database table, called pivot row, and then synthesizes a query whose result set should contain the pivot row. If the synthesized query cannot retrieve the pivot row in actual execution, it is considered that the DBMS has a logical flaw. However, this method only considers one record at a time and focuses on the generation of WHERE clause, so in many cases it will miss logical flaws. For example, the presence of duplicate rows in the query result or the execution of operator operations on the pivot row will affect the effectiveness of the method. In addition, as pointed out by some recent studies, synthesizing queries requires domain knowledge about the operators and operands supported by the database dialect. Therefore, the implementation cost of this method is high.

[0006] The third category is metamorphic testing-based methods. It first transforms a given query q into another query q' such that their query results satisfy a specific relationship, which is called metamorphic relationship. If the query results violate the metamorphic relationship, it indicates that the results of q or q' are incorrect. For example, TLP decomposes a query q into three partitioned sub-queries, each of which computes the result set of a Boolean predicate, which is evaluated as TRUE, FALSE and NULL, respectively, and then constructs an equivalent query q' by joining the three sub-queries. NOREC transforms an optimized query version into a non-optimized query by custom rules, for example, transforming "SELECT * FROM t WHERE p" into "SELECT (p ISTRUE) FROM t". Compared with the above two categories of methods, metamorphic testing-based methods are more lightweight to implement and have been proven to be more effective in detecting logical flaws. However, existing researches implement the metamorphic relationship as equivalence relationship, which is still insufficient to detect many deeply hidden errors. Due to the limited search space of transformation, a pair of equivalent queries can still have common erroneous operators or functions and eventually return the same result. In this case, the metamorphic relationship based on equivalent queries cannot detect logical flaws. SUMMARY

[0007] In view of the above technical problems, the embodiments of the present application aim to provide a method, device and readable medium for detecting logical flaws of a database management system.

[0008] In a first aspect, the present application provides a method for detecting logical flaws of a database management system, comprising the following steps:

[0009] S1, obtaining a table in a randomly generated database;

[0010] S2, establishing a first query statement according to the table, and taking the query based on the first query statement as a seed query; synthesizing a plurality of second query statements having an approximate relationship with the first query statement through approximate variation, and taking the query based on the plurality of second query statements as a synthetic query;

[0011] S3, inputting the first query statement and the plurality of second query statements into a database management system, and querying to obtain a result of the seed query and a result of the synthetic query;

[0012] S4, judging whether the result of the synthetic query and the result of the seed query violate the approximate relationship, and if yes, determining that the database management system has a logical defect.

[0013] As a preference, in step S2, the plurality of second query statements having an approximate relationship with the first query statement are synthesized through approximate variation, and the query based on the plurality of second query statements is taken as the synthetic query, which specifically comprises:

[0014] parsing the first query statement, determining a variable syntax structure, weakening or strengthening a set relationship, a predicate or a comparison operation in the seed query through an approximate variation operator, and constructing a super-approximation or an under-approximation of the seed query into the synthetic query, wherein the result of the synthetic query is a superset or a subset of the result of the seed query.

[0015] As a preference, the approximate variation operator comprises a set relationship variation operator, a predicate variation operator, a comparison expression variation operator and an extension variation operator.

[0016] As a preference, the set relationship variation operator comprises:

[0017] adding or modifying a relationship operator in the first query statement, wherein the relationship operator comprises DISTINCT, UNION ALL, UNION, INTERSECT and / or MINUS.

[0018] As a preference, the predicate variation operator comprises:

[0019] strengthening or weakening the constraint of a tuple in the relationship of the first query statement according to a logical implication relationship.

[0020] As a preference, the comparison expression variation operator comprises:

[0021] if there is a comparison expression as an atomic constraint in the first query statement, strengthening or weakening the constraint caused by the comparison expression by changing a comparison operator in the first query statement;

[0022] the extension variation operator comprises an operator supporting LIKE, REGEXP, IN and BETWEEN operators.

[0023] As preferred, step S4 further comprises: repeating steps S2-S4 until reaching a detection time.

[0024] In a second aspect, the present application provides a device for detecting logical defects of a database management system, comprising:

[0025] a data acquisition module configured to acquire tables in a randomly generated database;

[0026] a query generation module configured to establish a first query statement according to the tables, and to take a query based on the first query statement as a seed query; and to synthesize a plurality of second query statements having an approximate relationship with the first query statement through approximate mutation, and to take a query based on the plurality of second query statements as a synthesized query;

[0027] a query result module configured to input the first query statement and the plurality of second query statements into the database management system, and to query to obtain a result of the seed query and a result of the synthesized query;

[0028] a relationship judgment module configured to judge whether the result of the synthesized query and the result of the seed query violate the approximate relationship, and if so, to determine that the database management system has a logical defect.

[0029] In a third aspect, the present application provides an electronic device, comprising one or more processors; a storage device configured to store one or more programs, when the one or more programs are executed by the one or more processors, the one or more processors implement the method as described in any implementation manner of the first aspect.

[0030] In a fourth aspect, the present application provides a computer readable storage medium, having stored thereon a computer program, which, when executed by a processor, implements the method as described in any implementation manner of the first aspect.

[0031] Compared with the prior art, the present application has the following beneficial effects:

[0032] (1) The present application proposes the concept of approximate relationship and a series of approximate mutation operators to solve the test prediction problem in the test of DBMS logical defects.

[0033] (2) The present application uses approximate mutation operators to synthesize approximate queries of the seed query, and detects the logical defects in the DBMS according to the approximate relationship between the results of the seed query and the synthesized query, which is high in detection efficiency and accuracy.

[0034] (3) The present application uses four widely used DBMSs for system evaluation, and the evaluation results confirm the effectiveness of the present application in detecting logical defects. BRIEF DESCRIPTION OF DRAWINGS

[0035] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the following will briefly introduce the drawings needed to be used in the embodiments description. Obviously, the drawings in the following description are only some embodiments of the present application, and for those skilled in the art, other drawings can also be obtained from these drawings without any creative effort.

[0036] Figure 1 is an exemplary device architecture diagram to which an embodiment of the present application can be applied;

[0037] Figure 2 is a flowchart of a method for detecting logical defects of a database management system according to an embodiment of the present application;

[0038] Figure 3 is a workflow diagram of a method for detecting logical defects of a database management system according to an embodiment of the present application;

[0039] Figure 4 is a diagram of an abstract syntax tree of one of the synthetic queries according to a method for detecting logical defects of a database management system according to an embodiment of the present application;

[0040] Figure 5 is a comparison result diagram of PINOLO and benchmark tools according to a method for detecting logical defects of a database management system according to an embodiment of the present application;

[0041] Figure 6 is a result diagram of the code coverage of MySQL and MariaDB over time according to a method for detecting logical defects of a database management system according to an embodiment of the present application;

[0042] Figure 7 is a result diagram of the number of logical defects detected over time by five groups of random seed queries according to a method for detecting logical defects of a database management system according to an embodiment of the present application;

[0043] Figure 8 is a diagram of a device for detecting logical defects of a database management system according to an embodiment of the present application;

[0044] Figure 9 is a structural diagram of a computer device suitable for implementing an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

[0045] In order to make the objects, technical solutions and advantages of the present application clearer, the following further describes the present application with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present application, and not all the embodiments. Based on the embodiments of the present application, all other embodiments obtained by a person of ordinary skill in the art without creative effort belong to the protection scope of the present application.

[0046] Figure 1 An exemplary apparatus architecture 100 of a database management system logical defect detection method or a database management system logical defect detection apparatus to which embodiments of the present application can be applied is shown.

[0047] As shown in Figure 1 The apparatus architecture 100 can include terminal devices 101, 102, 103, a network 104 and a server 105. The network 104 is a medium to provide a communication link between the terminal devices 101, 102, 103 and the server 105. The network 104 can include various connection types, such as wired, wireless communication links or fiber optic cables, etc.

[0048] A user can use the terminal devices 101, 102, 103 to interact with the server 105 through the network 104 to receive or send messages, etc. The terminal devices 101, 102, 103 can be installed with various applications, such as data processing applications, file processing applications, etc.

[0049] The terminal devices 101, 102, 103 can be hardware or software. When the terminal devices 101, 102, 103 are hardware, they can be various electronic devices, including but not limited to smart phones, tablet computers, laptop computers and desktop computers, etc. When the terminal devices 101, 102, 103 are software, they can be installed in the above-mentioned listed electronic devices. They can be implemented as multiple software or software modules (such as software or software modules used to provide distributed services), or as a single software or software module. No specific limitation is made herein.

[0050] The server 105 can be a server providing various services, such as a background data processing server processing files or data uploaded by the terminal devices 101, 102, 103. The background data processing server can process the obtained files or data to generate processing results.

[0051] It should be noted that the database management system logical defect detection method provided by the embodiments of the present application can be executed by the server 105 or the terminal devices 101, 102, 103, and correspondingly, the database management system logical defect detection apparatus can be arranged in the server 105 or the terminal devices 101, 102, 103.

[0052] It should be understood that Figure 1 The number of terminal devices, networks and servers in the above-mentioned apparatus architecture is only illustrative. Any number of terminal devices, networks and servers can be provided according to implementation needs. In the case that the data to be processed does not need to be acquired from a remote location, the above-mentioned apparatus architecture can not include a network, but only a server or a terminal device.

[0053] Figure 2 A method for detecting logical defects of a database management system is shown, comprising the following steps:

[0054] S1, obtaining a table in a randomly generated database.

[0055] In a specific embodiment, the existing DBMS random testing technology is used to randomly generate the table in the database. Specifically, the existing tool GO-RANDGEN is used to randomly generate the table in the database. In order to effectively evaluate each query, the total number of records in the table is limited to no more than 30 in the embodiments of the present application. In addition, the random tables all have the same attributes, thereby ensuring the effectiveness of the connection operation. In particular, the embodiments of the present application avoid using null values in the table, because the test prediction requires non-null values as a prerequisite. The method proposed in the embodiments of the present application is referred to as PINOLO, and the specific process is referred to Figure 3 .

[0056] S2, establishing a first query statement according to the table, and taking the query based on the first query statement as a seed query; synthesizing a plurality of second query statements having an approximate relationship with the first query statement through approximate mutation, and taking the query based on the plurality of second query statements as a synthetic query.

[0057] In a specific embodiment, the plurality of second query statements having an approximate relationship with the first query statement are synthesized through approximate mutation in step S2, and the query based on the plurality of second query statements is taken as a synthetic query, specifically comprising:

[0058] Parsing the first query statement to determine the variable mutation syntax structure, weakening or strengthening the set relationship, predicate or comparison operation in the seed query through the approximate mutation operator, constructing the over-approximation or under-approximation of the seed query to become a synthetic query, and the result of the synthetic query is the superset or subset of the result of the seed query.

[0059] In a specific embodiment, the approximate mutation operator includes a set relationship mutation operator, a predicate mutation operator, a comparison expression mutation operator and an expansion mutation operator.

[0060] Specifically, the set relationship mutation operator includes:

[0061] adding or modifying a relational operator in the first query statement, the relational operator including DISTINCT, UNION ALL, UNION, INTERSECT, and / or MINUS.

[0062] The predicate variation operator includes:

[0063] The predicate variation operator includes:

[0064] The comparison expression variation operator includes:

[0065] If there is a comparison expression as an atomic constraint in the first query statement, the constraint caused by the comparison expression is strengthened or weakened by changing the comparison operator in the first query statement.

[0066] The extension variation operator includes: an operator supporting LIKE, REGEXP, IN, and BETWEEN operators.

[0067] Specifically, first, a syntactically valid SQL query is generated as a seed query, and then the seed query is parsed and its AST is traversed to determine the syntax structure that can be varied. By approximate variation, a plurality of second query statements are synthesized as synthesized queries, the results of the synthesized queries have an inclusion relationship with the results of the seed query, so as to construct the over-approximation or under-approximation of the seed query.

[0068] Specifically, the GO-RANDGEN is used to automatically generate the seed query. Specifically, an embodiment of the present application uses the universal grammar generator BISON to write the context-free grammar file of SQL, which describes a series of syntax rules. An embodiment of the present application provides this grammar file to GO-RANDGEN, so that it can generate queries by randomly searching each rule. In particular, GO-RANDGEN heuristically selects terminal and non-terminal to avoid exceeding the recursion limit. In addition, an embodiment of the present application allows users to write embedded LUA code blocks in the grammar file to further limit the format of the seed query to ensure the successful execution of the query, for example, the number of columns of the two queries of UNION should be equal. In order to perform the approximation transformation on the seed query, an embodiment of the present application uses another tool PINGCAP PARSER, which accepts the same context-free grammar as the seed query generator to generate the abstract syntax tree of the seed query for approximation transformation.

[0069] In general, a SQL query can be reduced to a select-from-where form, or the result of a subquery performing set operations. Logical connectives and arithmetic operators allow queries to support complex predicates. Without loss of generality, an arithmetic expression is described by an integer value, an attribute, an arithmetic operator, and a SQL built-in function. It is worth noting that the embodiments of the present application can also support comparisons between arithmetic expressions and numeric attributes using the keywords ALL and ANY. The syntax of a SQL query is shown as follows:

[0070]

[0071] To solve the test oracle problem in DBMS testing, the embodiments of the present application follow the concept of metamorphic testing, and propose the notion of approximate relations between SQL queries. Next, the concept of approximate relations is introduced, and then the form of queries with approximate relations is introduced.

[0072] Definition of approximate relation: Given a database D, q1 is an over-approximation of q2 on D if and only if holds. Here R(q i , D) is the execution result of query qi on database D, where i = 1 or 2, and is essentially a multiset. denotes the inclusion relation between two multisets. Similarly, q2 is an under-approximation of q1 on D if and only if holds.

[0073] Intuitively, the approximate relation between two SQL queries represents the inclusion relation between their query results. If a pair of SQL queries (q1, q2) is constructed such that or then the approximate relation can be used as an instantiation of the metamorphic relation, and thus the test oracle for DBMS testing can be constructed.

[0074] Example 1: Assume a database D = {t1}, where t1 has schema (c1) and t1 = {(-1), (0), (1)}. Consider the following three queries:

[0075] q1: SELECT c1 FROM t1 WHERE NOT(c1 > 0)

[0076] q2: SELECT c1 FROM t1 WHERE TRUE

[0077] q3: SELECT c1 FROM t1 WHERE NOT(c1 ≥ 0)

[0078] The first query q1 obtains all non-positive values of attribute c1 of t1, the second query q2 obtains all values of attribute c1, and the third query q3 obtains all values of attribute c1 that are not greater than 0, and the query results are marked as R(q1, D), R(q2, D), and R(q3, D), respectively. Obviously, the query results have the relationship of , which means or

[0079] Therefore, the syntax of the SQL query represents a search space for constructing an approximate query. Given a seed query that satisfies the syntax of the SQL query, by weakening / strengthening the set relationship, predicate, or comparison operation in the seed query, an over-approximation / under-approximation of the seed query can be constructed, and the result is a superset / subset of the result of the seed query. This means that it is feasible to automatically generate a query that has an approximate relationship with a specific query q. Therefore, the test prediction problem can be solved by synthesizing queries that have an approximate relationship.

[0080] Based on the above approximate relationship, a test prediction of a DBMS test can be further constructed. Specifically, by converting the set inclusion relationship or the predicate entailment relationship in the query, a query can always be converted into another query that has an approximate relationship. Based on this, an embodiment of the present application proposes the following concept of an approximate mutation operator, which is the basis for synthesizing approximate queries.

[0081] Definition of an approximate mutation operator: an approximate mutation operator is a mapping from a SQL query q1 to a query q2, such that or

[0082] In essence, the approximate mutation operator converts a SQL query into another query such that they have an over-approximation or under-approximation relationship. The approximate mutation operator changes the relationship, predicate, or comparison expression in the SQL query, which is usually an atomic constraint in the predicate, so that the query before and after the mutation has an approximate relationship. Each row in Table 1 represents two approximate mutation operators. Specifically, if the SQL syntax structure in the second column is replaced with the SQL syntax structure in the third column, an under-approximation of the original query can be obtained; if the query in the third column is replaced with the query in the second column, an over-approximation of the original query can be obtained. Next, each type of approximate mutation operator will be introduced in detail.

[0083] Table 1

[0084]

[0085] (1) Set relation variation: Using DISTINCT in select-from-where query can remove duplicate values in the query result, thus constructing under-approximation of the original query. Set operator UNION ALL can keep duplicate data, while operator UNION cannot, thus replacing the former with the latter can construct under-approximation of the original query. Other variations are similar.

[0086] (2) Predicate variation: According to logical implication relationship, for tuples in a relation, the constraint can be strengthened or weakened, thus constructing under-approximation or over-approximation of the original query. Specifically, for any predicate p, under-approximation of the original query can be constructed by changing it to FALSE, and over-approximation of the original query can be constructed by changing it to TRUE.

[0087] (3) Comparison expression variation: For each comparison expression as an atomic constraint, the constraint caused by the expression can be strengthened or weakened by changing its comparison operator. For example, replacing ≥ with = will make the new expression have stronger restriction than the original expression. In addition, changing keyword ANY to ALL will also introduce stronger predicate in the query.

[0088] Example 2: For the SQL query q1 in Example 1, it can be varied by replacing its WHERE predicate with TRUE or changing > to ≥, which will respectively produce two over-approximation queries q2 and q3 of q1. Further consider the following more complex SQL query.

[0089] q: SELECT 1 FROM t1 WHERE

[0090] (NOT(FROM_DAYS(1)) = ALL(SELECT c1 FROM t1))

[0091] The WHERE predicate in q can be changed to TRUE to construct over-approximation of q. In addition, changing ALL to ANY will weaken the comparison expression in the negation, thus strengthening the predicate in the WHERE clause, resulting in under-approximation query of q.

[0092] It is worth noting that the approximation variation operator proposed in the embodiments of the present application is a general concept. The operators shown in Table 1 are only a few examples, and more operators can be further defined to more flexibly synthesize queries with approximation relationships. In the process of developing PINOLO, more operators are actually instantiated.

[0093] Using the above approximation variation operators, the embodiments of the present application finally propose an approximation query synthesis algorithm, the purpose of which is to correctly maintain the change of approximation relationship brought by the approximation variation operators, thus constructing over-approximation and under-approximation of the seed query.

[0094] The technical details of the approximate synthesis algorithm will be shown below, and the correctness of the test predictions of the embodiments of the present application will be proved.

[0095]

[0096] Algorithm 1 shows a mutation-based query synthesis algorithm. Initially, it takes a seed query as input, parses the query, and generates an abstract syntax tree of the query (line 2). Next, the algorithm traverses the abstract syntax tree in a top-down manner, during which it identifies potential SQL structures and applies the approximate mutation operators described above (lines 3-4). Take the under-approximation of the synthesis of the seed query q as an example, in which kind will be set to UNDER (line 4). Specifically, it processes each SQL structure in two ways.

[0097] (1) When encountering the SQL structure (r1 MINUS r2), the algorithm attempts to maintain the relation r1 and strengthen the relation r2 (lines 12-15). Similarly, it also strengthens the predicate p of (NOT p) and (p IS NOT TRUE), as well as the relation r of the comparison expression e ⊙ ALL(r) (lines 16-24).

[0098] (2) For other SQL structures, the algorithm maintains each relation and strengthens the predicates and comparison expressions that appear in the structure. Finally, the algorithm combines each mutated structure together via the concat function to obtain the over-approximation or under-approximation of the seed query q.

[0099] By applying the approximate mutation operators during the abstract syntax tree traversal, Algorithm 1 eventually dynamically synthesizes two sets of queries, which are syntactically valid and have an approximate relationship with the seed query q.

[0100] Example 3: Take the query q in Example 2 as the seed query. The embodiments of the present application show how to synthesize the under-approximation of q. The abstract syntax tree is shown in Figure 4 After generating its abstract syntax tree, Algorithm 1 examines each SQL structure in a top-down manner. When encountering a predicate in the WHERE clause, the embodiments of the present application can either mutate the predicate to FALSE or strengthen the predicate in logical negation. For the latter case, the embodiments of the present application can further mutate the comparison expression in negation to TRUE, the comparison operator to ≥, or replace ALL with ANY, eventually weakening the logical negation. Finally, the embodiments of the present application can obtain four under-approximation queries of q. In particular, one of the synthesized queries is as follows:

[0101] q’: SELECT 1 FROM t1 WHERE

[0102] (NOT(FROM_DAYS(1))≥ALL(SELECT c1 FROM t1))

[0103] It is worth mentioning that the embodiments of this application found that R(q′,D) is not included in R(q,D), indicating that the query result of q or q′ is incorrect, which has been confirmed by the MySQL developers.

[0104] The embodiments of this application must limit the impact of null values. Any comparison between non-null and null values ​​may introduce an unknown value, denoted by NULL, into the comparison expression, which is logically less than TRUE but greater than FALSE. However, this application notes that when the predicate p is TRUE, NULL, or FALSE, the result of (p IS NULL) is FALSE, TRUE, or FALSE, respectively, indicating that strengthening or weakening the predicate p does not always strengthen or weaken the predicate p IS NULL. In this case, it cannot be ensured that Q over and Q under The seed query and the composite query in the model have the expected approximation relationship. In summary, the test oracle is formalized into a theorem.

[0105] Theorem: Provided that null values ​​have no effect on the approximation relation, using query q as the seed query, Algorithm 1 can always synthesize two sets of queries Q. over and Q under Make:

[0106] For any q′∈Q over ,have like

[0107] For any q′∈Q under ,have like

[0108] To solidify the theoretical foundation of the test prediction, this application briefly describes the proof of the theorem. First, this application ensures through preprocessing that null values ​​do not affect the approximation relation; for example, it prohibits NULL values ​​when generating tables and IS NULL values ​​when generating seed queries. Based on this, it is easy to prove that when kind is OVER, the approximation mutation operator applied to different structures will ultimately produce weaker predicates or stronger relations. A similar argument holds when kind is UNDER. Therefore, it can be inductively proven that each composite query satisfies the expected approximation relation with the seed query.

[0109] The embodiments of this application construct a series of approximate mutation operators for the relations, predicates, and comparison expressions in a given query. Each operator consists of two SQL syntax structures, representing over-approximation and under-approximation, respectively. To cover most features of the DBMS, the embodiments of this application construct a total of 25 approximate mutation operators. Among them, 5, 6, and 14 operators correspond to relations, predicates, and comparison expressions, respectively. In addition to the operators in Table 1, the embodiments of this application also construct 7 extended mutation operators supporting LIKE, REGEXP, IN, and BETWEEN. By adding more approximate mutation operators, this application can extend PINOLO to support more SQL features.

[0110] S3: Input the first query statement and several second query statements into the database management system to obtain the results of the seed query and the results of the combined query.

[0111] S4. Determine whether the result of the composite query violates the approximation relationship with the result of the seed query. If so, it is determined that there is a logical defect in the database management system.

[0112] Specifically, after synthesizing the query, PINOLO obtains the results of the seed query and the synthesized query by running the seed query and the synthesized query in the DBMS. It is worth noting that inconsistent query results often occur during testing. For example, during a 24-hour test, MySQL generated 46,772 inconsistent query pairs, making the process of identifying and fixing errors very lengthy. Therefore, embodiments of this application borrow the idea of ​​incremental debugging to locate the root cause of inconsistent query results and eliminate redundant logical defects. Specifically, each problematic query pair is associated with a release version of the tested DBMS codebase to find the version where the inconsistent query results first appeared. Embodiments of this application refer to such release versions as the versions that introduced the logical defects. If two versions that introduced logical defects are the same, they are considered identical. Based on feedback from DBMS developers, the error reports processed in the above way can facilitate developers in identifying the corresponding versions of the logical defects.

[0113] In a specific embodiment, step S4 is followed by repeating steps S2-S4 until the detection time is reached.

[0114] To evaluate the effectiveness of PINOLO in detecting logical defects in mainstream DBMSs, the following evaluation method was used.

[0115] The experimental environment consisted of a 104-core Intel(R) Xeon(R) Gold 6230R CPU @ 2.10GHz, 500GB of RAM, and Ubuntu 18.04. Additionally, four threads were allocated for each test in the experiment.

[0116] This paper tests four open-source and widely-used large-scale DBMSs, MySQL, MariaDB, TiDB, and OceanBase. There are two main reasons for choosing these projects. First, all the selected DBMSs are commonly used in previous research evaluations. Second, this paper chooses DBMSs with SQL syntax compatible with MySQL as evaluation objects to reduce the implementation overhead. This is because, although the method of this paper can be generalized to other DBMSs, the generation and parsing of seed queries require a grammar file to describe the SQL syntax of the tested DBMS. In order to obtain the feedback of the developers in time, this paper tests the latest release versions of the selected DBMSs, namely MySQL 8.0.31, MariaDB 10.11.1, TiDB 6.4.0, and OceanBase 4.0.0, and the specific parameters are shown in Table 2.

[0117] Table 2

[0118]

[0119] This paper compares PINOLO with three state-of-the-art logical defect detection techniques, namely PQS, NOREC, and TLP, corresponding to three test oracles. Similar to the method of this paper, these benchmarks also need to understand the SQL syntax of different DBMSs to generate and parse seed queries. Referring to Table 3, unfortunately, their implementations cannot support all selected DBMSs. This paper also tried to seek help from the authors of these tools, but so far it still cannot solve these problems. Therefore, this paper skips the evaluation of the DBMSs that are not supported. In addition, this paper tries to use the same random seed as the benchmark tool. However, the benchmark tool cannot export its random seed, nor can it import the random seed provided by the user. Therefore, PINOLO uses its own random seed generator. In order to understand the impact of the random seed on the results, this paper investigates the impact of the random seed on the robustness of PINOLO.

[0120] Table 3

[0121]

[0122] Embodiments of the present application use PINOLO to test the latest versions of MySQL, MariaDB, TiDB, and OceanBase for 24 hours. Table 4 summarizes the results of PINOLO. The second column shows the number of query pairs that have inconsistent results, where each pair of inconsistent queries represents a logical defect. From this medium, PINOLO discovers a large number of query pairs with problems, ranging from 4,675 to 46,772. However, there is a large amount of redundancy among these query pairs, that is, many query pairs can be attributed to the same defect. To alleviate the burden of developers checking for duplicate bugs, the present application utilizes bug introduction versions to de-duplicate the detected logical defects. The third column shows the number of logical defects after de-duplication, which can be seen to be significantly smaller than the values in the first column, ranging from 2 to 14. The de-duplicated logical defects are submitted to developers for confirmation. The fourth column shows the number of bug reports that have been verified by developers, ranging from 2 to 14.

[0123] PINOLO discovers a total of 41 different logical defects in these DBMS, of which 39 have been confirmed by developers. For MySQL, TiDB, and OceanBase, all detected logical defects have been confirmed by developers. For MariaDB, 12 of the 14 vulnerabilities have been confirmed, and the rest are still pending confirmation. A sample of bug reports submitted by other users of MariaDB is taken, and it is found that developers often take a long time to process these bugs. For the detected logical defects, a GitHub repository is maintained to facilitate tracking the status of bug reports, and thus it can be concluded that PINOLO discovers 41 different bugs on MySQL, MariaDB, TiDB, and OceanBase, of which 39 have been confirmed by DBMS developers.

[0124] Table 4

[0125]

[0126] The present application also compares PINOLO with three state-of-the-art benchmark tools, namely, PQS, NOREC, and TLP. Each tool is run for 24 hours. The comparison results are shown in Table 5. Figure 5 It is noted that the benchmark tools do not support all DBMS, so only comparisons between PINOLO and the runnable benchmark tools for each DBMS are focused on.

[0127] For MySQL, PINOLO detected 14 logical defects while TLP only found 2 logical defects. For MariaDB, PINOLO detected 14 logical defects and NOREC detected 10 logical defects. For TiDB, PINOLO detected 11 logical defects while TLP only detected 2 logical defects. For OceanBase, PINOLO can detect 2 logical defects while the benchmark tools cannot detect any logical defects. After manually verifying whether the logical defects detected by PINOLO overlap with the benchmark tools, for MariaDB, 4 out of the 10 logical defects detected by NOREC can also be detected by PINOLO. For TiDB, 1 out of the 2 logical defects detected by TLP can also be detected by PINOLO. PQS did not detect any logical defects.

[0128] Figure 5 The number of logical defects detected by PINOLO and the benchmark tools over time is also shown. It can be seen that PINOLO is more efficient in detecting logical defects compared to all the benchmark tools. Within one hour, PINOLO is able to detect 57.1% (8 / 14) of the errors on MySQL, 85.7% (12 / 14) of the errors on MariaDB, 63.6% (7 / 11) of the errors on TiDB, and 50% (1 / 2) of the errors on OceanBase.

[0129] To understand the reason why PINOLO can detect more logical defects, the present application uses GCOV to calculate the code coverage of PINOLO and other benchmark tools. It should be noted that the present application cannot provide the code coverage results of TiDB and OceanBase. This is because TiDB is developed in GO language and there is currently no code coverage calculation tool that supports program instrumentation or system testing for GO language. For OceanBase, it must be deployed by a specific tool OBDEPLOY to run its binary file, which makes GCOV unable to function normally.

[0130] Figure 6 The code coverage of MySQL and MariaDB over time is shown. The results show that the line coverage of PINOLO is higher than the other three benchmark tools. For MySQL, PINOLO improves 2.2% (3,008 lines) and 4.0% (5,316 lines) compared to PQS and TLP, respectively. For MariaDB, PINOLO improves 12.4% (2,835 lines) compared to NOREC. Therefore, it can be concluded that PINOLO can find more logical defects and achieve higher line coverage compared to the state-of-the-art techniques.

[0131] PINOLO uses GO-RANDGEN to generate seed queries. GO-RANDGEN requires a user to provide a random seed when generating seed queries. To understand whether randomness would affect the efficiency and effectiveness of PINOLO, we conduct experiments using 5 different random seeds, and randomly generate five groups of seed queries. We then run PINOLO under each group of seed queries and compare their performance in detecting logical defects.

[0132] Figure 7 The number of logical defects detected by PINOLO over time is shown. It can be found that under different random seeds, the growth trend of the number of logical defects over time is similar. Table 5 shows more detailed information about these logical defects. The last table shows the number of identical logical defects detected by the five groups of random seeds, and in MySQL, MariaDB, TiDB and OceanBase, the same logical defects account for an average of 68.4%, 72.9%, 82.9% and 100% of the total logical defects. This result shows that by providing different random seeds, PINOLO has the opportunity to find new logical defects. Therefore, it can be concluded that the randomness of the seed query has no significant impact on the efficiency and effectiveness of PINOLO. At the same time, different random seeds can help PINOLO detect new logical defects.

[0133] Table 5

[0134]

[0135] To understand the importance of the logical defects detected by PINOLO, we investigate the severity and duration of these errors. The results are shown in Table 6. The second table shows the severity of the errors marked by developers. The logical defects reported by this application are divided into two levels of S2 and S3, representing the second and third severity levels, respectively. Generally, the S2 level indicates a serious loss of service or loss of important functions, while the S3 level indicates a slight loss of service or causes inconvenience to use. Note that there is no identification of severity level in the error tracking system of OceanBase, so the discussion of OceanBase is skipped. Finally, PINOLO found 6, 9 and 8 S2 level logical defects in MySQL, MariaDB and TiDB, respectively, accounting for 62.2% of the total defects of the three DBMSs. S3 level errors are not as serious, but developers still consider them necessary to fix.

[0136] Table 6

[0137]

[0138] Table 6 also shows the duration of the logical defects. The duration refers to the interval between the time a logical defect is introduced by a version and the time it is detected. Surprisingly, there are 21 (53.8%) logical defects that last for 1-5 years, and 10 (25.6%) logical defects that last for 5-10 years. Specifically, two of the earliest logical defects in MySQL and MariaDB can be traced back to 2014. This result shows that logical defects usually lurk for a long time and are difficult to detect, which is consistent with previous research results.

[0139] Embodiments of the present application propose a detection of logical defects of a database management system, namely PINOLO, for detecting logical defects of a DBMS. The method is derived from the observation that a given query result is essentially a multiset. As the basis of set theory, the inclusion relationship between multisets is a good choice to describe the metamorphic relationship between two queries. Therefore, it is attempted to mutate a given seed query to obtain its over-approximation or under-approximation, whose query results are the superset or subset of the seed query, respectively. If the actual execution result violates the approximation relationship, it can reveal the logical defect of the DBMS. In order to systematically synthesize the two variants, a series of mutation operators are introduced, such as strengthening or weakening the predicates in the WHERE clause, and an approximation query synthesis algorithm is proposed to generate queries that have over-approximation or under-approximation relationship with the seed query. Thanks to the approximation relationship and flexible mutation operators of the present application, PINOLO can have more opportunities to reveal logical defects. Because it can make more aggressive mutations to the seed query (e.g., discard several functions), thus more thoroughly exploring the variants of the seed query. The present application also proves the correctness of the above test prediction to consolidate the theoretical basis of PINOLO.

[0140] The present application implements a DBMS test system according to the above idea, and evaluates it using four widely used DBMSs, MySQL, MariaDB, TiDB and OceanBase. Compared with the state-of-the-art methods, PINOLO is more effective in detecting logical defects. In 24 hours of running, PINOLO can find 41 different logical defects, while the three state-of-the-art methods can only find a total of 14 logical defects. In addition, 39 of the 41 logical defects have been confirmed by developers, showing the great impact of PINOLO on the four widely used DBMSs.

[0141] Further referring to Figure 8 , as an implementation of the method shown in the above figures, the present application provides an embodiment of a device for detecting logical defects of a database management system, which device embodiment corresponds to the method embodiment shown in Figure 2 , and the device can be specifically applied in various electronic devices.

[0142] The embodiment of the present application provides a kind of detection device of logical defect of database management system, comprising:

[0143] Data acquisition module 1 is configured to obtain the table in randomly generated database;

[0144] Query generation module 2 is configured to establish first query statement according to table, and the query based on first query statement is as seed query;By the approximation variation synthesis, several second query statements with approximate relationship with first query statement are synthesized, and the query based on several second query statements is as synthetic query;

[0145] Query result module 3 is configured to input first query statement and several second query statements in database management system, and the result of seed query and the result of synthetic query are inquired;

[0146] Relationship judging module 4 is configured to judge whether the result of synthetic query and the result of seed query violate approximate relationship, if yes, then determine that database management system exists logical defect.

[0147] The following refers to Figure 9 Fig. 1 shows a schematic diagram of a computer device 900 of an electronic device (e.g. Figure 1 a server or a terminal device) suitable for implementing the embodiments of the present application. Figure 9 The electronic device shown is merely an example, and should not impose any limitation on the functions and use range of the embodiments of the present application.

[0148] As shown in Figure 9 Fig. 1, the computer device 900 includes a central processing unit (CPU) 901 and a graphics processor (GPU) 902, which can perform various appropriate actions and processes according to programs stored in a read-only memory (ROM) 903 or programs loaded from a storage portion 909 into a random access memory (RAM) 904. In the RAM 904, various programs and data required for the operation of the device 900 are also stored. The CPU 901, the GPU 902, the ROM 903, and the RAM 904 are connected to each other through a bus 905. An input / output (I / O) interface 906 is also connected to the bus 905.

[0149] The following components are connected to the I / O interface 906: an input part 907 including a keyboard, a mouse, etc.; an output part 908 including a display such as a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage part 909 including a hard disk, etc.; and a communication part 910 including a network interface card such as a LAN card, a modem, etc. The communication part 910 performs communication processing via a network such as the Internet. A drive 911 can also be connected to the I / O interface 906 as necessary. A removable medium 912 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc. is attached to the drive 911 as necessary, so that a computer program read out therefrom is installed in the storage part 909 as necessary.

[0150] In particular, the processes described above with reference to the flowcharts can be implemented as a computer software program according to embodiments of the present disclosure. For example, embodiments of the present disclosure include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for executing the methods illustrated by the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network by the communication part 910, and / or installed from the removable medium 912. When the computer program is executed by the central processing unit (CPU) 901 and the graphics processor (GPU) 902, the above-described functions defined in the methods of the present application are performed.

[0151] Note that the computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium or any combination thereof. The computer-readable medium can be, for example but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing. More specific examples of a computer-readable medium can include, but are not limited to, an electrical connection having one or more wires, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the present context, a computer-readable medium can be any tangible medium that can contain or store a program for use by or in connection with an instruction execution system, apparatus, or device. In the present context, a computer-readable signal medium can include a computer-readable program code in a baseband or propagated as carrier waves in a propagated data signal associating with a carrier wave. Such a propagated signal can take a wide variety of forms including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer-readable signal medium can also be any computer-readable medium that can be used to carry or store a computer program for use by or in connection with an instruction execution system, apparatus, or device. The program contained in the computer-readable medium can be transmitted in any suitable format including, but not limited to, wireless, wireline, optical fiber cable, RF, or any suitable combination thereof.

[0152] Computer program code for carrying out operations of the present application can be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Smalltalk, C++ or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code can execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection can be made to an external computer (for example, through the Internet using an Internet Service Provider).

[0153] The flow and block diagrams in the drawings illustrate the architecture, functionality, and operation of possible implementations of apparatuses, methods, and computer program products according to various embodiments of the present application. In this regard, each block in the flow and block diagrams can represent a module, a segment, or a portion of code, which comprises one or more executable instructions for implementing the specified logical functions. It should also be noted that in some alternative implementations, the functions noted in the blocks can occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently or in the reverse order, depending on the functionality involved. It will also be noted that each block of the block diagrams and / or flowchart illustrations, and combinations thereof, can be implemented by special purpose hardware-based systems that perform the specified functions or operations, or combinations of special purpose hardware and computer instructions.

[0154] The modules involved in the embodiments of the present application can be implemented in software or in hardware. The modules described can be arranged in a processor.

[0155] As another aspect, the present application also provides a computer readable medium, which can be included in the electronic device described in the above embodiments, or can exist separately without being assembled into the electronic device. The computer readable medium carries one or more programs, which, when executed by the electronic device, cause the electronic device to: acquire a table in a randomly generated database; establish a first query statement according to the table, and take the query of the first query statement as a seed query; synthesize a plurality of second query statements having an approximate relationship with the first query statement through approximate variation, and take the query of the plurality of second query statements as synthetic queries; input the first query statement and the plurality of second query statements into a database management system, and query to obtain the result of the seed query and the result of the synthetic queries; and determine whether the result of the synthetic queries and the result of the seed query violate the approximate relationship, and if so, determine that the database management system has a logical defect.

[0156] The above description is merely the preferred embodiments of the present application and the explanation of the principles of the applied technology. It should be understood by those skilled in the art that the scope of the application involved in the present application is not limited to the technical solutions formed by the specific combinations of the above technical features, and should also cover other technical solutions formed by any combinations of the above technical features or equivalent features without departing from the above inventive concept. For example, the above features can be replaced with the technical features disclosed in the present application (but not limited to) having similar functions to form technical solutions.

Claims

1. A method of detecting a logical defect of a database management system, characterized by, The method comprises the following steps: S1, obtaining a table in a randomly generated database; S2, establishing a first query statement according to the table, and taking a query based on the first query statement as a seed query; synthesizing a plurality of second query statements having an approximate relationship with the first query statement through approximate variation, and taking a query based on the plurality of second query statements as a synthetic query, specifically comprising: parsing the first query statement, determining a variable syntax structure, weakening or strengthening a set relationship, a predicate or a comparison operation in the seed query through an approximate variation operator, constructing an over-approximation or under-approximation of the seed query into the synthetic query, and the result of the synthetic query being a superset or subset of the result of the seed query; S3, inputting the first query statement and the plurality of second query statements into the database management system to obtain the result of the seed query and the result of the synthetic query; S4, judging whether the result of the synthetic query and the result of the seed query violate an approximate relationship, and if so, determining that the database management system has a logical defect.

2. The method of claim 1, wherein the database management system is a relational database management system. The approximate variation operator comprises a set relationship variation operator, a predicate variation operator, a comparison expression variation operator and an expansion variation operator.

3. The method of claim 2, wherein the database management system is a relational database management system. The set relationship variation operator comprises: adding or modifying a relationship operator in the first query statement, and the relationship operator comprises DISTINCT, UNIONALL, UNION, INTERSECT and / or MINUS.

4. The method of claim 2, wherein the database management system is a relational database management system. The predicate variation operator comprises: strengthening or weakening the constraint of a tuple in the relationship of the first query statement according to a logical implication relationship.

5. The method of claim 2, wherein the database management system is a relational database management system. The comparison expression variation operator comprises: if there is a comparison expression as an atomic constraint in the first query statement, strengthening or weakening the constraint caused by the comparison expression by changing the comparison operator in the first query statement; The expansion variation operator comprises an operator supporting LIKE, REGEXP, IN and BETWEEN operators.

6. The method of claim 1, wherein the database management system is a relational database management system. The step S4 further comprises repeating steps S2-S4 until a detection time is reached.

7. A device for detecting logical defects in a database management system, characterized in that, Comprise: a data acquisition module configured to obtain a table in a randomly generated database; a query generation module configured to establish a first query statement according to the table, and take a query based on the first query statement as a seed query; synthesizing a plurality of second query statements having an approximate relationship with the first query statement through approximate variation, and taking a query based on the plurality of second query statements as a synthetic query, specifically comprising: parsing the first query statement, determining a variable syntax structure, weakening or strengthening a set relationship, a predicate or a comparison operation in the seed query through an approximate variation operator, constructing an over-approximation or under-approximation of the seed query into the synthetic query, and the result of the synthetic query being a superset or subset of the result of the seed query; a query result module configured to input the first query statement and the plurality of second query statements into the database management system to obtain the result of the seed query and the result of the synthetic query; The relationship judging module is configured to judge whether the result of the synthetic query and the result of the seed query violate an approximate relationship, and if so, determine that the database management system has a logical defect.

8. An electronic device, comprising: one or more processors; storage storing one or more programs, when the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1-6.

9. A computer-readable storage medium having stored thereon a computer program, characterized in that, The program is executed by the processor to implement the method according to any one of claims 1-6. The program is executed by the processor to implement the method according to any one of claims 1-6.

Images