Detection method for database and related equipment

Abstract

The embodiment of the invention provides a database detection method and related equipment, and the method comprises the steps: obtaining a reference database corresponding to a to-be-detected database, and the reference database is different from the to-be-detected database in database architecture; respectively sending the generated query statements to the reference database and the to-be-tested database; obtaining a test execution result obtained by executing the query statement by the to-be-tested database, and obtaining a reference execution result obtained by executing the query statement by the reference database; performing verification conversion on the test execution result and the reference execution result to obtain verification information of the test execution result and verification information of the reference execution result; and according to the verification information of the test execution result and the verification information of the reference execution result, performing logic defect detection on the to-be-detected database to obtain a detection result of the to-be-detected database, so that the accuracy of database detection can be improved.

Description

Technical Field

[0001] This application relates to the field of Internet technology, specifically to a method and related equipment for detecting databases. Background Technology

[0002] With the development of internet technology and the explosive growth of data volume, distributed databases with data sharding capabilities are becoming increasingly mature. To ensure that distributed databases can provide stable services, they need to be tested.

[0003] However, the current main method for detecting distributed databases is to execute the same query on multiple nodes corresponding to the distributed database, obtain the execution results of each node, and then select a suitable reference node for any given node, comparing the execution results of that node with the reference node to determine whether there are problems with the distributed database. However, when nodes in a distributed database execute query statements, because the logic is relatively fixed, they may produce the same errors, thus failing to detect defects in the distributed database, resulting in inaccurate detection. Summary of the Invention

[0004] This application provides a method and related equipment for detecting databases, which can improve the accuracy of database detection.

[0005] On the one hand, embodiments of this application provide a detection method for a database, the method comprising:

[0006] Obtain the reference database corresponding to the database under test. The database architecture of the reference database is different from that of the database under test.

[0007] Send the generated query statements to both the reference database and the database to be tested;

[0008] Obtain the test execution results of the query statement executed on the database under test, and obtain the reference execution results of the query statement executed on the reference database;

[0009] The test execution results and the reference execution results are verified and transformed to obtain the verification information of the test execution results and the verification information of the reference execution results respectively;

[0010] Based on the verification information of the test execution results and the verification information of the reference execution results, logical defect detection is performed on the database under test to obtain the detection results of the database under test.

[0011] On one hand, embodiments of this application provide a detection device for a database, the device comprising:

[0012] The processing unit is used to obtain the reference database corresponding to the database under test. The database architecture of the reference database is different from that of the database under test.

[0013] The sending unit is used to send the generated query statements to the reference database and the database under test, respectively.

[0014] The processing unit is also used to obtain the test execution result obtained by executing the query statement in the database under test, and to obtain the reference execution result obtained by executing the query statement in the reference database;

[0015] The processing unit is also used to perform verification conversion on the test execution results and the reference execution results respectively, so as to obtain the verification information of the test execution results and the verification information of the reference execution results;

[0016] The processing unit is also used to perform logical defect detection on the database under test based on the verification information of the test execution results and the verification information of the reference execution results, and to obtain the detection results of the database under test.

[0017] Among them, the differences in database architecture between the reference database and the database under test include at least one of the following:

[0018] The number of data node groups included in the reference database is different from the number of data node groups included in the database under test;

[0019] The number of coordinating nodes included in the reference database differs from the number of coordinating nodes included in the database under test.

[0020] The number of backup nodes included in the data node group in the reference database is different from the number of backup nodes included in the data node group in the database under test;

[0021] The reference database is of a different type than the database under test.

[0022] The basic database state of the reference database is consistent with that of the database under test. The processing unit is also used for:

[0023] Generate the database state creation statement and send it to the reference database;

[0024] Query the basic database status established based on the database status creation statement in the reference database;

[0025] Based on the queried basic database status, determine the database complexity of the reference database;

[0026] If the database complexity of the reference database meets the complexity condition, then the basic database state for the reference database is established.

[0027] The processing unit is also used for:

[0028] If the database complexity of the reference database does not meet the complexity condition, a new database state creation statement will be generated.

[0029] The new database state creation statement is sent to the reference database so that the reference database can create a basic database state based on the new database state creation statement, thus completing the creation of the basic database state for the reference database.

[0030] Specifically, when generating database state establishment statements, the processing unit can be used for:

[0031] Generate initial database state establishment statements according to the syntax characteristics of the database under test;

[0032] Obtain the syntax supported by the reference database;

[0033] Based on the syntax supported by the reference database, the initial database state creation statement is syntactically adapted to obtain the database state creation statement for the reference database.

[0034] Specifically, when the processing unit performs syntax adaptation on the initial database state establishment statement according to the syntax supported by the reference database to obtain the database state establishment statement of the reference database, it can be used for:

[0035] Based on the syntax supported by the reference database, determine the statements that need to be replaced in the initial database state creation statement;

[0036] By using the syntax supported by the reference database, the statements that need to be replaced in the initial database state creation statement are replaced to obtain the database state creation statement of the reference database.

[0037] Specifically, when the processing unit performs syntax adaptation on the initial database state establishment statement according to the syntax supported by the reference database to obtain the database state establishment statement of the reference database, it can be used for:

[0038] If the initial database state creation statement contains a target statement that does not belong to the syntax supported by the reference database, and removing the target statement does not affect the execution result of the initial database state creation statement, then the target statement is removed from the initial database state creation statement to obtain the database state creation statement of the reference database.

[0039] The reference database has the same basic database state as the database under test, and the basic database state includes data tables; the processing unit is also used for:

[0040] Obtain the constraints;

[0041] Add constraints to the query statement to be generated, and then generate the query statement.

[0042] The constraints include at least one or more of the following:

[0043] Limit the number of nested trigonometric functions in the query statement to be generated;

[0044] If the execution result of the query statement to be generated includes floating-point numbers, then the modulo operation of floating-point numbers is restricted.

[0045] If the execution result of the query statement to be generated includes floating-point numbers, then binary bit operations on floating-point numbers are restricted.

[0046] In the query statement to be generated, restrict the use of grouping clauses on the target data columns included in the data table;

[0047] In the query statement to be generated, the deduplication operation is restricted to the target data column included in the data table. The target data column refers to the floating-point data column in the data table.

[0048] The query statement to be generated contains a clause limiting the number of execution results. The processing unit, when retrieving constraints, specifically uses the following:

[0049] If the query to be generated does not contain a sort clause, the constraints include using a sort clause in the query and increasing the number of columns to be sorted in the sort clause; or,

[0050] If the query statement to be generated uses a sort clause, and the sorted data columns contain the same data, then increase the number of sorted data columns in the sort clause.

[0051] The data table includes data rows and corresponding data columns; the query statement to be generated uses a window function that depends on the relative position of the data rows within the segmented window, and the window function is used to segment the data in the data table into windows; the processing unit, when obtaining constraints, is specifically used for:

[0052] If no sort clause is used in the split window corresponding to the window function, the constraints include: using a sort clause in the split window corresponding to the window function, and increasing the number of columns to be sorted in the sort clause; or,

[0053] If a sort clause is used in the split window corresponding to the window function, and the sorted data columns in the sort clause contain the same data, then the constraint includes: increasing the number of sorted data columns in the sort clause.

[0054] The data table includes data rows; the query statement to be generated uses window functions that depend on the relative position of the data rows within the segmented window, and there are multiple window functions; the processing unit, when obtaining constraints, is specifically used for:

[0055] If multiple window functions are used to partition a data table, and the data rows are partitioned and sorted differently within the partition windows corresponding to each window function, then the constraints include: retaining one window function from among the multiple window functions, and increasing the number of data columns to be sorted in the sorting clauses included in the partition windows corresponding to the retained window function.

[0056] The query statement to be generated uses a grouping clause, and the return value of the aggregate function used in the query statement depends on the order of the data rows within the group defined by the grouping clause. The processing unit, when obtaining constraints, is specifically used for:

[0057] If the aggregate function allows sorting of rows within a group, the constraints include adding a parameter to the statement to be generated that indicates sorting of rows within the group.

[0058] If aggregate functions are not allowed to sort rows within a group, then the constraint includes: removing aggregate functions from the statement to be generated.

[0059] The number of reference databases can be one or more. When the processing unit performs logical defect detection on the database under test based on the verification information of the test execution results and the verification information of the reference execution results, and obtains the detection results of the database under test, it can be specifically used for:

[0060] Compare the verification information of each reference execution result with the verification information of the test execution result;

[0061] If there are inconsistencies between the verification information of each reference execution result, or between the verification information of each reference execution result and the verification information of the test execution result, the detection result of logical defects in the database under test will be output, and a logical defect detection report will be generated.

[0062] The reference execution result includes a data row and N data columns corresponding to the data row, where N is a positive integer. When the processing unit verifies and transforms the reference execution result to obtain verification information, it can specifically use it for:

[0063] The data columns are traversed sequentially according to a preset order. The data column traversed for the i-th time is obtained, and the data type of the data column traversed for the i-th time is determined.

[0064] According to the data type, the hash seed and the hash value corresponding to the data column traversed in the (i-1)th traversal are used to perform hash calculation on the data in the data column traversed in the i-th traversal, so as to obtain the hash value corresponding to the data column traversed in the i-th traversal.

[0065] When i = N, the verification information of the reference execution result is determined based on the hash value corresponding to the data column traversed in the Nth iteration;

[0066] When i is less than N, continue to traverse each data column in the preset order to determine the verification information of the reference execution result.

[0067] Specifically, when the processing unit performs hash calculations on the data in the i-th traversed data column according to the data type, using the hash seed and the hash value corresponding to the data column traversed in the (i-1)-th traversal, to obtain the hash value corresponding to the data column traversed in the i-th traversal, it can be used for:

[0068] If the data type belongs to the first subtype of the first type, then according to the first parameter, the hash seed and the hash value corresponding to the data column traversed in the (i-1)th traversal, the data in the data column traversed in the i-th traversal are hashed to obtain the hash value corresponding to the data column traversed in the i-th traversal.

[0069] If the data type belongs to the second subtype of the first type, then according to the second parameter, the hash seed and the hash value corresponding to the data column traversed in the (i-1)th traversal, the data in the data column traversed in the i-th traversal are hashed to obtain the hash value corresponding to the data column traversed in the i-th traversal.

[0070] If the data type belongs to the third subtype of the first type, then the data in the data column traversed in the i-th iteration will be converted to the second type to obtain the conversion result; if the conversion result indicates that the data failed to be converted to the second type, then according to the second parameter, the hash seed and the hash value corresponding to the data column traversed in the (i-1)-th iteration, the data in the data column traversed in the i-th iteration will be hashed to obtain the hash value corresponding to the data column traversed in the i-th iteration.

[0071] The processing unit is also used for:

[0072] If the data type is of type 2, or the conversion result indicates that the data has been successfully converted to type 2, then the data in the data column traversed in the i-th iteration is truncated to obtain the truncated data corresponding to the data column traversed in the i-th iteration.

[0073] Based on the second parameter, the hash seed, and the hash value corresponding to the data column traversed in the (i-1)th iteration, perform hash calculation on the truncated data to obtain the hash value corresponding to the truncated data;

[0074] The hash value corresponding to the truncated data is determined as the hash value corresponding to the data column traversed in the i-th iteration.

[0075] On one hand, embodiments of this application provide a computer device, the computer device comprising:

[0076] A processor is used to execute computer programs;

[0077] A computer-readable storage medium storing a computer program, which, when executed by a processor, implements the database detection method described above.

[0078] On one hand, embodiments of this application provide a computer-readable storage medium storing a computer program that is loaded by a processor and executed as described above for the database detection method.

[0079] On the one hand, embodiments of this application provide a computer program product, which includes a computer program or computer instructions, and when the computer program or computer instructions are executed by a processor, they implement the above-described database detection method.

[0080] In this embodiment, a reference database corresponding to the database under test is obtained. The reference database has a different database architecture than the database under test. Generated query statements are sent to both the reference database and the database under test. The test execution results of the query statements in the database under test and the reference execution results of the query statements in the reference database are obtained. The test execution results and the reference execution results are verified and converted to obtain verification information for the test execution results and the reference execution results, respectively. Based on the verification information of the test execution results and the reference execution results, logical defect detection is performed on the database under test to obtain the detection results. It is evident that by introducing a reference database with a different architecture than the database under test, the execution results of the database under test and the reference database with different architectures can be directly compared. This avoids the problem of databases with the same deployment architecture generating the same erroneous logic, thus failing to detect defects in the database under test and improving the accuracy of database detection. Attached Figure Description

[0081] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0082] Figure 1An architecture diagram of a database provided for an embodiment of this application;

[0083] Figure 2 An architecture diagram of a database detection system provided in this application embodiment;

[0084] Figure 3 A schematic diagram of a database detection process provided for an embodiment of this application;

[0085] Figure 4a A schematic diagram of another database detection process provided for an embodiment of this application;

[0086] Figure 4b A schematic diagram of a hash calculation process is provided for an embodiment of this application;

[0087] Figure 5 A schematic flowchart illustrating a database detection method provided in this application embodiment;

[0088] Figure 6 This is a schematic diagram of a reference database deployment process provided in an embodiment of this application;

[0089] Figure 7 A schematic diagram illustrating a process for establishing a basic database state for a database, provided as an embodiment of this application;

[0090] Figure 8 A schematic diagram illustrating the addition of constraints to output stable execution results, as provided in an embodiment of this application;

[0091] Figure 9 A schematic diagram illustrating the addition of constraints to output stable execution results, as provided in an embodiment of this application;

[0092] Figure 10 A schematic diagram illustrating the addition of constraints to output stable execution results, as provided in an embodiment of this application;

[0093] Figure 11 A schematic diagram illustrating the addition of constraints to output stable execution results, as provided in an embodiment of this application;

[0094] Figure 12 A schematic flowchart illustrating a database detection method provided in this application embodiment;

[0095] Figure 13 A schematic diagram of floating-point truncation provided in an embodiment of this application;

[0096] Figure 14 A schematic diagram of a database detection device provided in an embodiment of this application;

[0097] Figure 15This is a schematic diagram of the structure of a computer device provided in an embodiment of this application. Detailed Implementation

[0098] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of this application.

[0099] First, the technical terms provided in the embodiments of this application will be explained.

[0100] I. Database

[0101] A database, simply put, can be viewed as an electronic filing cabinet—a place to store electronic files. Objects (such as users) can perform operations such as adding, querying, updating, and deleting data in the database. A database is a collection of data stored together in a certain way, which can be shared by multiple users, has minimal redundancy, and is independent of application programs. In one implementation, the data stored in the database can be stored in the form of data tables. Data tables include data rows and corresponding data columns. It should be understood that each data row can correspond to at least one data column.

[0102] Please see Figure 1 This is an architecture diagram of a database provided in an embodiment of this application. The database may include at least one data node (DN), which can be used for storing data and executing query statements to retrieve data, etc. The data node may include a primary data node or a standby data node (which may be simply referred to as a standby node). Figure 1 In this database, there are multiple primary data nodes (i.e., primary data node 1, primary data node 2, and primary data node 3), and each primary data node can correspond to at least one backup data node, such as... Figure 1 In this architecture, each primary data node can correspond to two backup data nodes. The primary data node is used for storing data and executing queries, while the backup data nodes are used to back up the data stored on the primary data node. When the primary data node fails, the backup data node can be activated.

[0103] It should be understood that the primary data node and its corresponding backup data node constitute a data node group, such as... Figure 1 In this system, primary data node 1 and its corresponding backup data node 1 and backup data node 2 constitute a data node group, and primary data node 2 and its corresponding backup data node 1 and backup data node 2 constitute another data node group.

[0104] Optionally, the database may also include at least one coordinating node (CN). The coordinating node serves as the external entry point for the database service, responsible for distributing data to data nodes for storage and planning data queries, among other things. The coordinating node and data nodes communicate via a cluster data exchange bus.

[0105] II. Database Status and Basic Database Status

[0106] In this application embodiment, database status refers to the collection of the following information in the database: the structure of the data table, the data type corresponding to each data column (such as floating-point type, integer type, etc.), primary key constraints, unique constraints, and other descriptions of the data table itself; the collection of data entries actually stored in the data table; the optimization data table statistics used by the optimizer; and other additional database status such as triggers and functions.

[0107] Basic database status refers to the collection of the following information in a database: the basic structure of the data tables, the types of each column, and the collection of actual data entries stored in the data tables.

[0108] It should be understood that, in the embodiments of this application, the database under test and the reference database need to have the same basic database state or the same database state, so as to effectively ensure the accuracy of subsequent logical defect testing of the database under test through the reference database.

[0109] III. SQL (Structured Query Language) Statements

[0110] Structured Query Language (SQL) is a database query and programming language used to access, query, update, and manage data in a database. SQL statements can include: DDL (Data Definition Language) statements, DML (Data Manipulation Language) statements, and DQL (Data Query Language) statements. Among them:

[0111] DDL statements are used to define the structure of database objects such as databases, tables, views, and indexes. DDL statements include statements for creating, modifying, and deleting database objects; the database state can be changed through these DDL statements.

[0112] DML statements are used to manipulate data in a database, including but not limited to operations such as inserting, updating, and deleting data in tables. DML statements can also change the state of a database.

[0113] DQL statements are used to retrieve data from a database, i.e., to perform query operations. DQL statements do not change the database state.

[0114] The database state creation statements involved in the embodiments of this application may include one or more of DML statements and DQL statements, and the query statements may include DQL statements.

[0115] This application provides a database detection scheme. The general principle of the database detection scheme is as follows: For the database under test, firstly, a reference database with a different database architecture can be deployed. This difference in data architecture may include, but is not limited to: different numbers of nodes (e.g., the database under test includes two nodes, while the reference database includes a single node), different node types (e.g., the database under test includes both coordinating nodes and data nodes, while the reference database includes only data nodes), and different database types (e.g., the database under test is MySQL, and the reference database is PostgreSQL). This application does not impose any limitations on these aspects. Furthermore, a basic database state consistent with the database under test can be established for the reference database. This ensures that the execution results of the reference database and the database under test are compared under the same basic database state, improving the detection accuracy of the database under test.

[0116] After deploying a reference database to the database under test, a query statement can be generated and sent to both the reference database and the database under test, causing both databases to execute the query. In one implementation, constraints can be added during query generation to ensure that the final query outputs stable (i.e., uniquely ordered) results after execution by both the reference and databases under test. This avoids false positives due to unstable results and allows for better detection of potential semantic or logical defects in the database. For clarity, the execution result obtained by executing the query statement on the database under test is called the test execution result, and the execution result obtained by executing the query statement on the reference database is called the reference execution result.

[0117] Furthermore, the test execution results of the query statements executed on the database under test and the reference execution results of the query statements executed on the reference database are obtained. The test execution results and the reference execution results are then verified and converted to obtain verification information for the test execution results and the reference execution results, respectively. Based on the verification information of the test execution results and the reference execution results, logical defect detection is performed on the database under test to obtain the detection results. It is evident that by verifying and converting the test execution results and the reference execution results, false alarms caused by sorting errors and floating-point operation errors are effectively reduced, further improving the detection accuracy of the database under test.

[0118] The following section will elaborate on the database detection system provided in the embodiments of this application.

[0119] Please see Figure 2 This is an architecture diagram of a database detection system provided in an embodiment of this application. The database detection system may include: a database to be tested 201, at least one reference database 202, and a computer device 203. The computer device 203 can communicate with both the database to be tested 201 and the reference database 202.

[0120] The database under test 201 refers to the database to be tested. Database under test 201 can include, but is not limited to, distributed databases such as OceanBase, TiDB, CockroachDB, MySQL, etc. The database architecture of database under test 201 can be as follows: Figure 1 As shown.

[0121] Reference database 202 is used to detect logical defects in the database under test. Reference database 202 has a different database architecture than the database under test 201. In this embodiment, reference database 202 can be of the same type as the database under test 201, but it is a database instance built by randomly changing the number of data node groups, the number of coordinating nodes, and the number of backup nodes (i.e., backup data nodes) in the data node groups of the database under test 201. Of course, reference database 202 can also be another database of a different type than the database under test; for example, if the database under test 201 is MySQL, reference database 202 could be TiDB. This embodiment does not impose any limitations on this.

[0122] Computer device 203 can be the device used by the test object (such as testers, developers, etc.). Computer device 203 can have the following functions: ① Establish a basic database state consistent with the database under test 201 for the reference database 202; ② Generate query statements through an SQL generator and send the query statements to the reference database 202 and the database under test 201; ③ Compare the execution results of the query statements in the database under test 201 and the reference database 202 to perform logical defect detection on the database under test 201.

[0123] It should be understood that computer device 203 can be either a terminal device or a server. Terminal devices can include, but are not limited to, smartphones, tablets, smart wearable devices, smart voice interaction devices, smart home appliances, personal computers, in-vehicle terminals, smart cameras, virtual reality devices (such as AR (Augmented Reality) devices), etc., and this application does not impose any restrictions on them. Servers can be independent physical servers, server clusters or distributed systems composed of multiple physical servers, or cloud servers that provide basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDN (Content Delivery Network), and big data and artificial intelligence platforms.

[0124] Next, to facilitate understanding of the interaction process between computer device 203, database under test 201, and reference database 202, please refer to [link / reference]. Figure 3 This is a schematic diagram of a database detection process provided in an embodiment of this application. The database detection process can be divided into the following seven steps: (1) randomized deployment of the reference database; (2) random establishment of the basic database state of the reference database; (3) verification of the basic database state; (4) generation and adaptation of query statements; (5) execution of query statements and collection of execution results; (6) comparison of the consistency of execution results; and (7) reporting of logical defects. Each step will be described in detail below:

[0125] (1) Reference database is deployed randomly.

[0126] Computer device 203 can randomly deploy reference databases for the database under test 201. It should be understood that, in this embodiment, one or more reference databases can be randomly deployed for the database under test 201. In one implementation, such as... Figure 4a The diagram shown illustrates another database testing process provided in this embodiment of the application. The computer device 203 can deploy one or more reference databases for the database under test 201 using an instance deployer, such as... Figure 4aIn this example, the instance deployer can deploy reference databases 1 through n to the database under test (201). Each reference database has a different database architecture than the database under test.

[0127] (2) Randomly establish the basic database state of the reference database

[0128] In one implementation, computer device 203 can generate a basic database state for a reference database. For example... Figure 4a In this context, computer device 203 can generate a basic database state for the reference database using a state generator and verifier. Specifically, it can randomly generate database state creation statements and send them to the reference database 202, which can then execute these statements to establish the basic database state. It should be understood that database state creation statements include both DDL and DML statements.

[0129] (3) Basic database status verification

[0130] To ensure the accuracy of subsequent execution results, when there are one or more reference databases, in this embodiment of the application, before generating the query statement, the computer device 203 can obtain the basic database status of each reference database through the status generator and verifier, and verify whether the basic database status of each reference database is consistent with the basic database status of the database under test 201. If they are consistent, step (4) can be executed; if there is a reference database whose basic database status is inconsistent with that of other reference databases, the basic database status of the reference database needs to be reconstructed so that the basic database status of all reference databases is consistent with the basic database status of the database under test 201.

[0131] (4) Generation and adaptation of query statements: such as Figure 4a As shown, computer device 203 randomly generates query statements using an SQL generator.

[0132] (5) Execute the query statement and collect the execution results:

[0133] Computer device 203 can send query statements to reference database 202 and database under test 201. Database under test 201 can execute the query statements to obtain test execution results, and reference database 202 can execute the query statements to obtain reference execution results. For example... Figure 4a In the process, the query statements were executed for each of the reference databases 1 through n, so n reference execution results can be obtained respectively.

[0134] After the query statement is executed in both the database under test and the reference database, the computer device 203 can obtain the test execution result obtained by executing the query statement in the database under test 201 and the reference execution result obtained by executing the query statement in the reference database 202.

[0135] Optionally, if it is detected that the execution time of the query statement in the database (i.e., the reference database and the database under test) exceeds the preset time, a record of the timeout of the database query statement execution needs to be generated for later review.

[0136] (6) Compare the consistency of execution results:

[0137] Computer device 203 can compare the test execution results of the database under test 201 with the reference execution results of the reference database 202 to see if they are consistent. As one implementation method, a result comparator can be used to directly compare the test execution results of the database under test 201 with the reference execution results of the reference database 202. If they are consistent, the test result is output that the database under test 201 does not have logical defects; if they are inconsistent, the test result is output that the database under test 201 has logical defects, and step (7) is executed. As another implementation, since the test execution results and reference execution results may include floating-point data, in order to prevent the possibility of false detection, this embodiment of the application can verify and convert the test execution results of the database under test 201 and the reference execution results of the reference database 202 to obtain the verification information of the execution results of the database under test 201 and the verification information of the reference execution results. This can overcome the false detection of the database caused by the difference brought by the floating-point data to a certain extent. Then, the verification information of the test execution results of the database under test 201 and the verification information of the reference execution results are compared by the result comparator. If they are consistent, the detection result that the database under test 201 does not have logical defects is output. If they are inconsistent, the detection result that the database under test 201 has logical defects is output, and step (7) is executed.

[0138] The verification transformation may include hash calculations, meaning that hash calculations can be performed on the reference execution result and the test execution result to obtain the verification information for both. Please refer to [link to relevant documentation]. Figure 4b This is a schematic diagram of a hash calculation process provided in an embodiment of this application. For ease of understanding, a reference execution result is used as an example. The reference execution result includes data rows and the corresponding data columns; that is, the reference execution result includes one row of data. Figure 4bIn this process, the reference execution result is input into the hash calculator. The hash calculator obtains the data type of the data in the data column. If the data type is a precise data type, such as an integer or text string, then the data in the data column is determined to be an integer or text string. In this case, a hash method can be used to perform a hash calculation on the data in the data column. For example, the hash method can be linear transformation and addition (if the data after linear transformation is too large, the hash method needs to perform a linear transformation, modulo operation, and then addition). If not, then the data in the data column is determined to be a floating-point number or a large integer represented as a string. In this case, the data in the data column can be normalized to a floating-point number and truncated (e.g., mantissa truncation) to obtain truncated data. Then, a hash method is used to perform a hash calculation on the truncated data to obtain the hash value of the reference execution result. The hash value of the reference execution result is used as the verification information of the reference execution result.

[0139] It should be noted that since a data row corresponds to multiple data columns, the data type of each data column needs to be determined, and then processed accordingly before hash calculation. Finally, the multiple data columns corresponding to a data row are converted into a single hash value (that is, the data of a row is converted into a single hash value) as verification information for the reference execution result. This part can be explained in the subsequent embodiments and will not be repeated here.

[0140] (7) Reporting logical flaws.

[0141] After determining that the database under test has logical defects, a logical defect detection report can be generated. This report can record the entire process of logical defect detection in the database under test, including the process of establishing the basic database state of the reference database, the reference execution results obtained by executing query statements in the reference database, the test execution results obtained by executing query statements in the database under test, and so on. This makes it easier for subsequent developers to completely reproduce the problem based on the logical defect detection process of the database under test and locate the code with logical defects from the configuration file template corresponding to the database under test.

[0142] It should be understood that, due to the different types of query statements, this application embodiment needs to repeatedly execute steps (4)-(7) so that logical defects can be detected in the database under test from different query statements, so as to effectively ensure the performance of the database under test.

[0143] In summary, in this embodiment, by deploying reference databases with different database architectures for the database under test and establishing a basic database state consistent with that of the database under test for the reference databases, it is possible to ensure that the semantics of the same query statement are completely consistent between the reference database and the database under test under the same basic database state. This allows for the detection of potential semantic defects in the database under test by comparing the execution results of the query statements executed in the reference database and the database under test, thus improving the accuracy of database detection. Furthermore, by comparing the execution results of two databases under test and reference databases with different database architectures, the problem of failing to detect database defects due to identical architecture deployments can be avoided, further improving the accuracy of database detection.

[0144] The following section will describe the database detection method provided in the embodiments of this application.

[0145] Please see Figure 5 This is a flowchart illustrating a database detection method provided in an embodiment of this application. The database detection method can be executed by the aforementioned computer device 203, and may include the following steps S501-S505:

[0146] S501. Obtain the reference database corresponding to the database under test. The database architecture of the reference database is different from that of the database under test.

[0147] The basic database state of the reference database is consistent with that of the database under test. By configuring the database under test and the reference database to be in the same basic database state, it can be effectively ensured that the database under test and the reference database execute query statements in the same basic database state and obtain the corresponding execution results. This can avoid the problem of abnormal results caused by executing query statements in different database states, improve the accuracy of subsequent result comparison, and thus improve the accuracy of database detection.

[0148] It should be understood that there can be one or more reference databases. Obtaining the reference database corresponding to the database under test can specifically involve randomly deploying one or more reference databases to the database under test. In this embodiment, by changing the number of data node groups, the number of coordinating nodes, and the number of spare nodes in each data node group, etc., multiple architectures of reference databases can be deployed for the database under test. This allows for the detection of logical defects in the database under test when affected by different database architecture deployments. It should be understood that the type of reference database deployed by changing the number of data node groups, the number of coordinating nodes, and the number of spare nodes in each data node group, etc., is the same as the type of the database under test.

[0149] Please see Figure 6 This is a schematic diagram of a reference database deployment process provided in an embodiment of this application. Deploying one or more reference databases to a database under test includes: ① obtaining a configuration file template for the database under test, wherein the configuration file template includes, but is not limited to: the number of data node groups, the number of coordinating nodes, the number of standby nodes in the data node groups (i.e., the standby data nodes mentioned above), etc.; ② adjusting the file configuration of the configuration file template through a mutant to obtain one or more configuration files, each configuration file being used to deploy a reference database, for example... Figure 6 In the process, the configuration file template is adjusted using a mutant to obtain configuration file 1 through configuration file n. ③ The instance deployer deploys the corresponding reference database based on each configuration file, such as... Figure 6 In this process, the instance deployer deploys reference databases 1 through 2 based on configuration files 1 through 2. That is, configuration file 1 deploys reference database 1, configuration file 2 deploys reference database 2, and so on, with configuration file 2 deploying reference database 2.

[0150] Furthermore, embodiments of this application can perform a minimal deployment of the database under test, resulting in a minimal reference database. A minimal deployment means the reference database includes only a single node. Thus, when comparing the database under test and the reference database, the semantics of the query statement require consistency in execution results between multiple nodes and a single node. Therefore, the execution result of the reference database obtained from the minimal deployment should also be consistent with the execution results of the database under test or other reference databases. Specifically, as... Figure 6 In the process, a minimal configuration file can be obtained, and a minimal reference database can be deployed based on the minimal configuration file through the instance deployer.

[0151] Optionally, the database under test includes multiple nodes (such as data nodes). Different nodes share some code logic. When there is a logical defect in this part, different reference database deployments implemented by changing the number of data node groups, the number of coordinating nodes, and the number of spare nodes in each data node group may also produce erroneous results and fail to detect the logical problems in the database under test. Therefore, in this embodiment, other types of databases with the same syntax (such as SQL syntax) framework as the database under test can also be deployed to detect logical vulnerabilities in such code. Specifically, such as... Figure 6 In the process, you can obtain other database configurations and deploy reference databases of different types from the database under test using the instance deployer based on the other database configurations.

[0152] In summary, the reference database can include at least one of the following: a database deployed via a configuration file (also known as a database instance), a database deployed with a minimized configuration file, or a database of a different type than the database under test, thereby covering as many database architectures as possible. Differences in database architecture between the reference database and the database under test include at least one of the following: ① The number of data node groups in the reference database differs from the number of data node groups in the database under test; ② The number of coordinating nodes in the reference database differs from the number of coordinating nodes in the database under test; ③ The number of standby nodes in the data node groups in the reference database differs from the number of standby nodes in the data node groups in the database under test; ④ The type of the reference database differs from the type of the database under test.

[0153] S502. Send the generated query statements to both the reference database and the database under test. Accordingly, the database under test can execute the generated query statements to obtain execution results, and the reference database can execute the generated query statements to obtain reference execution results.

[0154] Specifically, query statements can be generated and sent to both the reference database and the database under test. The generated query statements can be DQL statements within the SQL statement set.

[0155] S503. Obtain the test execution result of the query statement executed on the database under test, and obtain the reference execution result of the query statement executed on the reference database.

[0156] Specifically, test execution results obtained from executing query statements can be obtained from the database under test, as well as reference execution results obtained from executing query statements from the reference database.

[0157] S504. Verify and convert the test execution results and the reference execution results respectively to obtain the verification information of the test execution results and the verification information of the reference execution results.

[0158] In one implementation, a hash method can be used to perform hash calculations on the test execution result and the reference execution result to obtain the verification information of the test execution result and the verification information of the reference execution result. How to perform hash calculations will be described in subsequent embodiments and will not be repeated here.

[0159] S505. Based on the verification information of the test execution results and the verification information of the reference execution results, perform logical defect detection on the database under test to obtain the detection results of the database under test.

[0160] In one implementation, step S505 may include: directly comparing the verification information of the test execution result with the verification information of the reference execution result; if they are consistent, then the database under test is determined to have no logical defects; if they are inconsistent, then the database under test is output as having logical defects.

[0161] It should be understood that, in the embodiments of this application, the number of reference databases can be one or more. When there are multiple reference databases, it is necessary to compare the verification information of each reference execution result with the verification information of the test execution result. If there is an inconsistency between the verification information of each reference execution result, or if there is an inconsistency between the verification information of each reference execution result and the verification information of the test execution result, the detection result that the database under test has a logical defect is output.

[0162] Optionally, when a logical defect is identified in the database under test, a logical defect detection report can be generated. This report records the entire logical defect detection process, including but not limited to: the execution process of database state creation statements, the execution process of query statements, etc. This facilitates subsequent development by enabling developers to fully reproduce the problem based on the logical defect detection process of the database under test and locate the code with logical defects from the configuration file template corresponding to the database under test.

[0163] In this embodiment, a reference database corresponding to the database under test is obtained. The reference database has a different database architecture than the database under test, but its basic database state is consistent with that of the database under test. By introducing a reference database with a different architecture than the database under test, the problem of failing to detect defects in the database under test due to identical deployment architectures can be avoided. Then, generated query statements are sent to both the reference database and the database under test. The test execution results of the query statements in the database under test and the reference execution results of the query statements in the reference database are obtained. The test execution results and the reference execution results are verified and converted to obtain verification information for the test execution results and the reference execution results, respectively. Based on the verification information of the test execution results and the reference execution results, logical defect detection is performed on the database under test to obtain the detection results. In this embodiment, by comparing the execution results of two databases under test and a reference database with different architectures, the problem of failing to detect database defects due to identical architecture deployments can be avoided, and the accuracy of database detection can be improved.

[0164] The following section describes the establishment of a basic database state for the reference database as described in the embodiments of this application. In some optional implementations, after deploying the reference database for the database under test, it is necessary to establish a basic database state for both the reference database and the database under test. Taking the reference database as an example:

[0165] In one implementation, establishing a basic database state for a reference database may include the following steps: randomly generating a state database establishment statement and sending the state database establishment statement to the reference database, and the reference database executing the state database establishment statement to establish the basic database state.

[0166] In another implementation, to better test logical flaws in the database, this embodiment can establish a more complex basic database state for the reference database. After establishing the basic database state by executing the state database creation statement on the reference database, please refer to... Figure 7This document provides a flowchart illustrating the process of establishing a basic database state for a database, as provided in an embodiment of this application. Establishing a basic database state for a reference database may include: ① Querying the basic database state established by the reference database based on the database state establishment statement; in one implementation, a state queryer can be used to query the basic database state established by the reference database based on the database state establishment statement. ② Determining whether the basic database state of the reference database is sufficiently complex. In one implementation, the database complexity of the reference database is determined based on the queried basic database state. Illustratively, the basic database state includes a data table, which contains multiple fields. The number of fields in the data table can be determined, and the database complexity of the reference database is determined based on the number of fields. A higher number of fields indicates higher database complexity. Then, it is determined whether the database complexity of the reference database meets the complexity condition. If the database complexity of the reference database meets the complexity condition, the basic database state of the reference database is determined to be sufficiently complex, and step ③ is executed; if the database complexity of the reference database does not meet the complexity condition, the basic database state of the reference database is determined to be uncomplex, and step ④ is executed. ③ Completing the establishment of the basic database state for the reference database. ④ A new database state establishment statement needs to be generated. Figure 7 In the process, a new database state establishment statement is randomly generated using an SQL generator. ⑤ This new database state establishment statement is sent to the reference database so that the reference database establishes its basic database state based on the new statement, thus completing the establishment of the basic database state for the reference database.

[0167] It should be noted that the database under test will also receive the corresponding database state establishment statements. This allows both the database under test and the reference databases to establish their basic database states based on the received statements. Furthermore, when there are multiple reference databases, each reference database can receive the corresponding database state establishment statements to establish its own basic database state.

[0168] It should be understood that during the SQL statement generation and adaptation process, query statements (such as DQL statements) and database state establishment statements (including DML and DDL statements) in the test cases are randomly generated. The test cases are used to test the database under test. During the SQL statement generation process, the SQL generator will try to generate the special dialect of the database under test (i.e., the syntactic differences between the database under test and other databases) to add additional features. In other words, in this embodiment, the SQL statement corresponding to the database under test is first generated according to the syntactic features of the database under test. However, there are differences in some important syntaxes between different deployment architectures of the same type of database or between different databases. Without syntax compatibility, the test cannot cover the relevant parts. That is, when there are syntactic differences between the database under test and the reference database, if the reference database directly executes the SQL statement corresponding to the database under test, it may cause execution errors due to the special dialect. Therefore, in order to ensure that the reference database does not cause execution errors due to the special dialect during execution, this embodiment will select an appropriate syntax adaptation method based on the specific state of the reference database (such as database type, supported syntax, etc.) to generate the SQL statement corresponding to the reference database. This effectively ensures compatibility with the unique syntax of different databases and deployment architectures, improving the accuracy of database testing. Syntax adaptation methods include, but are not limited to, equivalent syntax adaptation and differential syntax removal. For ease of understanding, an SQL statement used to establish database state will be used as an example.

[0169] The above-mentioned statement for generating database status can specifically include the following steps:

[0170] S11. Generate the initial database state establishment statement according to the syntax characteristics of the database to be tested.

[0171] It should be understood that the generated initial database state establishment statement can be sent to the database under test, so that the database under test can establish a basic database state based on the initial database state establishment statement.

[0172] S12. Obtain the syntax supported by the reference database.

[0173] In this embodiment of the application, the syntax supported by the reference database can be configured in advance based on the type of the reference database, and then the syntax supported by the reference database can be directly obtained according to the type of the reference database.

[0174] S13. Based on the syntax supported by the reference database, perform syntax adaptation on the initial database state creation statement to obtain the database state creation statement of the reference database.

[0175] Among them, grammar adaptation includes at least one of the following: equivalent grammar adaptation and differential grammar elimination.

[0176] (1) Equivalent Syntax Adaptation: Equivalent syntax refers to the existence of dialects in databases with overall consistent syntax (e.g., compatibility with mainstream syntaxes such as MySQL or PostgreSQL). These dialects have the same semantics and do not affect the execution result semantically. In such cases, statements are equivalently replaced according to the syntax supported by the reference database, thus ensuring compatibility with dialects of different databases. Specifically, step S13 may include: determining the statements to be replaced in the initial database state establishment statement based on the syntax supported by the reference database; replacing the statements to be replaced in the initial database state establishment statement with the syntax supported by the reference database to obtain the database state establishment statement of the reference database.

[0177] It should be understood that when there are multiple reference databases, database state creation statements for each reference database can be generated based on equivalent syntax adaptation. This allows each reference database to execute its own database state creation statement to create the corresponding database state, ensuring that the execution results of the final execution statements of each reference database are not affected. In other words, it allows each reference database to ultimately create the same basic database state.

[0178] (2) Difference Syntax Removal: Difference syntax refers to the unsupported distributed syntax requiring multiple nodes in different database architectures, such as in a minimal deployment of the same type of database (e.g., the reference database obtained by minimizing the deployment of the database under test mentioned above). However, this only affects the storage or optimization method of the database and does not affect the execution result. Deleting this part of the syntax does not change the semantics. For example, in Table 1, there is a table sharding statement "shardKey=c_i2226xal4" in the distributed database. This statement controls the table sharding method and the storage method on the node, but it does not affect the content and structure of the data table. Storing the entire data table on a single node without sharding does not affect the execution result. Therefore, for this type of reference database, such difference syntax can be removed. By removing difference syntax, compatibility with different database deployment architectures or other database syntax can be achieved, which can improve the accuracy of subsequent testing of the database under test.

[0179] Table 1

[0180]

[0181] Specifically, step S13 may include: if the initial database state creation statement contains a target statement whose syntax is not supported by the reference database, and removing the target statement does not affect the execution result of the initial database state creation statement, then the target statement is removed from the initial database state creation statement to obtain the database state creation statement for the reference database. For example, in Table 1, deleting shardKey=c_i2226xal4 does not affect data storage, so shardKey=c_i2226xal4 can be deleted. The phrase "does not affect the execution result of the initial database state creation statement" means that removing the target statement results in the same execution result as the initial database state creation statement. For example, in Table 1, the target statement is shardKey=c_i2226xal4; deleting this target statement results in the same execution result as the initial database state creation statement, meaning it does not affect the content and structure of the data tables stored in the reference database and the database under test.

[0182] In summary, to address the issue that tests cannot cover relevant parts due to differences in important syntax between different deployment architectures of the same type of database and between different databases, without syntax compatibility testing, the embodiments of this application can perform syntax adaptation by means of equivalent syntax adaptation and difference syntax elimination during the SQL statement generation process. This can effectively ensure compatibility with the special syntax of different databases and deployment architectures, avoid false errors caused by the database being unable to execute corresponding statements due to syntax differences, and further improve the detection accuracy of the database under test.

[0183] Optionally, there are multiple reference databases. After the basic database status of each reference database is established, the basic database status of each reference database can be accessed. If the basic database status of each reference database is consistent, a query statement can be generated and step S502 can be executed. If they are inconsistent, the basic database status of each reference database needs to be re-established so that the basic database status of each reference database is consistent.

[0184] Furthermore, in two databases with the same basic database state (such as the reference database and the database under test mentioned above), the semantics of the same standard query statement (such as an SQL query statement) should be completely consistent. Therefore, by constructing two databases with the same basic database state, executing SQL statements, and comparing their results, one can attempt to detect potential semantic defects in the database. However, this cannot be achieved through simple direct comparison. This is mainly due to the following reasons: First, for some SQL statements, the standard does not specify the order of their execution results; as long as the execution results conform to the standard semantics, the database can return data rows in any order. Therefore, before comparison, the results need to be sorted. The order of the execution results refers to the fact that the results include multiple rows of data, and these rows can be arranged in any order. For example, if the results include rows 1 and 2, the order of the results when returned can be: rows 1 first, rows 2 second; conversely, the order of the results when returning can be: rows 2 first, rows 1 second. Secondly, for some SQL statements involving floating-point numbers, a certain degree of floating-point error is permissible. This is due to the special nature of floating-point arithmetic. On different hardware architectures, with different compilers, using different floating-point algorithms, different floating-point precision constraints, and different floating-point operation orders, the results may differ due to rounding errors, accumulated errors, and other error factors. Third, for some SQL statements, the standard does not specify the uniqueness of their execution results, meaning that there may be multiple execution results, all of which conform to the standard semantics. The database can return any execution result that conforms to the semantics.

[0185] Therefore, in order to control the output order of the databases (i.e., the reference database and the database under test) to maintain a stable execution result, this embodiment of the application can apply additional constraints during the generation of the query statement. These constraints can control the order of the database output results, ensuring that the final database outputs a unique execution result. The query statement generation method includes: obtaining constraints; adding constraints to the query statement to be generated; and generating the query statement. The constraints include one or more of the following:

[0186] ① If a query statement uses a large number of nested trigonometric functions, the error will become uncontrollable as data is continuously processed and returned, leading to abnormal execution results. Therefore, constraints can include limiting the number of nested trigonometric functions in the generated query statement. By limiting the number of nested trigonometric functions, the use of a large number of nested trigonometric function calls can be avoided, preventing the error in the execution results from becoming uncontrollable and avoiding abnormal execution results between databases.

[0187] ② In some cases, it may be necessary to perform modulo operations on the execution result of floating-point data (i.e., floating-point numbers). Although modulo operations on floating-point data do not affect the absolute error, they will amplify the relative error many times over, leading to significant differences in the execution results. Therefore, in this embodiment, the constraint may include: restricting the floating-point modulo processing on the execution result corresponding to the generated query statement. This can reduce the relative error caused by floating-point modulo operations and avoid significant differences in execution results between databases.

[0188] ③Since the binary representations of several similar floating-point numbers may differ significantly, performing binary bitwise operations on them may also lead to significant differences in the results. Therefore, in this embodiment of the application, if the execution result corresponding to the query statement to be generated includes floating-point numbers, binary bitwise operations on floating-point numbers are restricted. This allows for binary bitwise operations on floating-point numbers in the execution result corresponding to the query statement to be generated to a certain extent, thereby reducing the differences in execution results between databases.

[0189] ④ In some cases, GROUP BY clauses are used on floating-point data columns included in the data table. However, GROUP BY clauses use precise comparison methods to group data. Due to errors, similar but different floating-point numbers will be grouped into two groups. Of course, in some cases, if two identical floating-point numbers are calculated, they will only be grouped into one group. This can easily lead to differences in execution results. Therefore, in this embodiment, the constraint condition may include: restricting the use of GROUP BY clauses on target data columns included in the data table in the query statement to be generated. Target data columns refer to floating-point data columns in the data table. By restricting the use of GROUP BY clauses on floating-point data columns, differences in execution results can be avoided. Floating-point data columns refer to data in the data column that is a floating-point number.

[0190] ⑤ In some cases, deduplication operations are performed on the floating-point data columns included in the data table. However, deduplication can use precise comparison methods to remove duplicate data. Similar but different floating-point numbers due to errors may not be removed. Of course, in some cases, if two identical floating-point numbers are calculated, one will be removed, which can easily lead to differences in execution results. Therefore, in this embodiment, the constraint condition may include: restricting the deduplication operation on the target data columns included in the data table in the query statement to be generated. By restricting the deduplication operation on floating-point type columns, differences in execution results can be avoided.

[0191] ⑥ Regarding point three above, for some SQL statements, the standard does not specify the uniqueness of their execution results. This means there may be multiple execution results, all conforming to the standard semantics, but the database can return any semantically correct result. This also leads to instability in the execution results, and consequently, inaccuracy in testing the database under test. Therefore, constraints need to be added to obtain a unique execution result. Below are some practical examples:

[0192] Scenario 1: When the query to be generated uses a clause that limits the number of results (such as the LIMIT clause), the standard does not specify the actual number of rows to be returned if a sorting clause (i.e., the ORDER BY clause) is not used simultaneously. Similarly, if a sorting clause is used, but the columns to be sorted have identical data, the standard also does not specify the actual number of rows to be returned. For example... Figure 8 The diagram illustrates an embodiment of this application that adds constraints to output stable execution results. In data table t1, the data stored in column c1 of rows 1-3 is all 1. When the execution results are required to be sorted in ascending order in column c1 and the number of result rows is limited to 2, if the statement to be generated is query statement 1 (i.e., SELECT c2FROM t1 ORDEY BY c1 LIMIT2), if the query statement 1 is executed directly without adding constraints, the database can return rows 1 and 2 as in execution result 1, or rows 3 and 1 as in execution result 2. However, if the two returned rows contain other data columns that do not store the same data as column c1, differences will be reflected in the results. For example, column c2 in execution result 1 contains x and y, but column c2 in execution result 2 contains z and x. Both execution results conform to the semantics of a standard query statement and cannot be considered to contain any semantic defects.

[0193] Therefore, when the query statement to be generated uses a clause limiting the number of execution results, obtaining the constraint conditions can include: if the query statement to be generated does not contain a sorting clause, the constraint conditions include using a sorting clause in the query statement to be generated and increasing the number of sorted data columns in the sorting clause; or, if the query statement to be generated uses a sorting clause and the sorted data columns contain the same data, then increasing the number of sorted data columns in the sorting clause. Correspondingly, adding constraints to the query statement to be generated and generating the query statement includes: if the query statement to be generated does not contain a sorting clause, then adding a sorting clause in the query statement to be generated and increasing the number of sorted data columns in the sorting clause to obtain the final query statement. If the query statement to be generated uses a sorting clause, then directly increasing the number of sorted data columns in the query statement to obtain the final query statement.

[0194] In other words, when the query statement to be generated contains a clause limiting the number of results, if it is found that the query statement to be generated does not have a sorting clause, or if the query statement to be generated uses a sorting clause and the sorted data columns contain the same data, resulting in multiple possible sorting results (i.e., execution results), multiple possible execution results can be observed from the outside. At this time, more data columns can be added to the sorting clause until the constraint is strong enough to make the execution result output by the database unique. In one implementation, the data columns contained in the execution result can be fully sorted, that is, all data columns in the execution result are added to the sorting clause. Since the row numbers of data rows are not part of the execution result, the order in which identical data rows appear is no longer important, because they cannot be distinguished from the outside. In this way, by adding all data columns to the sorting clause, the data and order before limiting the number of results are uniquely determined. When limiting the number of results, the specified data is taken continuously from front to back, so the execution result is also uniquely determined, thus producing a stable execution result. For example, Figure 8 In the given query, the query to be generated is: SELECT c2FROM t1 ORDEY BY c1 LIMIT2 (i.e., query 1). At this point, the query contains a clause limiting the number of results (i.e., a LIMIT clause, limiting the number of results to 2), and it also uses a sorting clause. Therefore, by adding column c2 to the sorting clause (i.e., adding a constraint), the generated query will look like this: Figure 8The query statement shown is 2 (that is, adding c2 to SELECT c2 FROM t1 ORDEY BY c1 LIMIT2 to generate the query statement SELECT c2 FROM t1 ORDEY BY c1, c2 LIMIT2). In this way, the database executes the query statement with a unique result constraint, such as the result in statement 1.

[0195] Scenario 2: When using certain window functions (such as rank(), dense_rank(), etc.), these functions divide the data in the table into windows and return results based on the relative position of the data rows within a given window. Similarly, if the sorted data columns within a window contain identical data, the order of these data rows is not standardized. However, because the results returned by these window functions depend on the actual sorting position of the data rows within the window, multiple semantically consistent execution results may be produced. For example... Figure 9 The diagram shown is a schematic representation of an embodiment of this application that adds constraints to output stable execution results. Figure 9 In data table t2, the data stored in column c2 of rows 1-2 is all 1. When a window is split using column c1 and the data within the split window is sorted using column c2, two scenarios may occur: the first row is in the first position and the second row is in the second position (result 1); or the first row is in the second position and the second row is in the first position (result 2). Therefore, for the first row, the value calculated by the rank() function may be 1, corresponding to the first scenario, and the output would be as follows. Figure 9 The execution result is 1; or, the value calculated by the rank() function may also be 2, corresponding to the second case, and the output is as follows. Figure 9 The execution result 2 in the example both conform to the semantics of a standard query statement, so a simple comparison cannot demonstrate that they contain semantic defects.

[0196] Therefore, when the query to be generated uses a window function that depends on the relative position of data rows within the split window, obtaining constraints in this case can include: if no sort clause is used in the split window corresponding to the window function, the constraint includes: using a sort clause in the split window corresponding to the window function, and increasing the number of sorted data columns in the sort clause; or, if a sort clause is used in the split window corresponding to the window function, and the sorted data columns in the sort clause contain the same data, the constraint includes: increasing the number of sorted data columns in the sort clause. Correspondingly, adding constraints to the query to be generated and generating the query includes: if the query to be generated does not contain a sort clause, adding a sort clause and increasing the sorted data columns in the sort clause to obtain the final query. If the query to be generated uses a sort clause, directly increasing the sorted data columns in the query to obtain the final query.

[0197] In other words, when using window functions that depend on the relative positions of data rows within a split window, if it's found that there's no sort clause within the split window, or if a sort clause is used but the sorted data columns contain identical data, resulting in non-unique sorting results (i.e., non-unique execution results) within the split window, then more data columns are added to the sort clause until the constraint is strong enough to make the sorting result unique within the split window. In one implementation, for simplicity, a full sort can be performed directly within the split window, meaning all data columns within the split window participate in the sorting. It should be understood that since row numbers cannot be directly observed externally, two rows with identical data will produce identical results in other non-window function column expressions. Treating the data rows corresponding to their output results as a set, the possible cases of this set are unique, and therefore will not be considered different cases externally. Thus, once the sorting result in the window function is determined, the execution result of the entire query statement will be uniquely determined. For example... Figure 9 As shown, the query statement to be generated is query statement 3 (i.e., ELECT c3, rank()over(PARTITION BY c1 orderby c2)FORM t2). If no constraints are added to the query statement to be generated, the execution result after executing query statement 3 might be... Figure 9The execution result 1 can also be execution result 2. Therefore, in this embodiment, by adding column c3 to the sort clause (i.e., adding a constraint), the generated query statement is query statement 4 (i.e., SELECT c3, rank() over (PARTITION BY c1 ORDERBY c2, c3) FORM t2); by adding column c3 to the sort clause, the execution result obtained after the generated query statement is executed can be constrained to a unique case, such as the case of execution result 1.

[0198] Scenario 3: When multiple window functions are used in a data column of the query to be generated, and these window functions actually use different windows, each window function may have multiple semantically correct results because some data columns contain the same data. These results can be arbitrarily combined in the same returned data row, resulting in multiple semantically correct execution results. This situation differs from the case of a single window function because although the same data in the data column may have multiple semantically correct results, these results will all appear uniquely in the final execution result. For example, ... Figure 10 This is a schematic diagram of adding constraints to output stable execution results according to an embodiment of this application. In the data table t3, the window is also divided by column c1 and sorted by column c2. If the query statement to be generated is query statement 5, without adding constraints, since the first row and the second row have the same data 1 in column c2, the result of the rank() function in the first row can be 1 or 2. However, there will not be two 1s or two 2s in the execution result. No matter which row calculates the result 1, the other row must be the remaining semantically consistent case, and finally completely covers the entire semantically consistent execution result. However, when using multiple window functions with different windows, the values ​​of these window functions can be arbitrarily combined. For example, after executing query statement 5 (i.e., "SELECT rank()over(PARTITION BY c1ORDER BY c2)as r1,rank()over(PARTITION BY c1 ORDER BY c2)as r2 FROM3"), in execution result 1, the results of the two window functions for the first and second rows are both 1 and 2. However, in execution result 2, the first rank() function calculates 1 and 2, but the second window function calculates 2 and 1, resulting in different execution results. Both of these execution results are allowed by standard SQL semantics, and it cannot be assumed that such different execution results represent a hidden semantic defect.

[0199] Therefore, the query statement to be generated uses window functions that depend on the relative positions of data rows within the partitioning window. Since there are multiple window functions, the constraints are as follows: if multiple window functions partition the data table and the data rows are sorted differently within the partitioning windows corresponding to each window function, then the constraints include: retaining one window function from the multiple window functions and increasing the number of sorted data columns in the sorting clause of the partitioning window corresponding to the retained window function. Correspondingly, constraints are added to the query statement to be generated. Generating the query statement involves: retaining one window function from the query statement to be generated and increasing the number of sorted data columns in the sorting clause corresponding to the retained window function, resulting in the final query statement.

[0200] In other words, when a window function depends on the order of the actual data rows within the corresponding partitioned window, and this type of window function appears multiple times, and these window functions do not use the same window partitioning sort, the possible return values ​​of multiple window functions can be arbitrarily combined. In this case, only one random window function is retained, the rest are removed, and sorting constraints are applied as in case 2. This avoids the possibility of arbitrary combinations of results leading to non-uniqueness of the final execution result. Figure 10 As shown, the query statement to be generated is: Query statement 5(SELECT, rank()over(PARTITION BY c1ORDERBY c2)as r1, rank()over(PARTITION BY c1 ORDERBY c2)as r2FROM t3). The query statement to be generated uses two window functions (r1 and r2). At this time, by removing the window function expression in column r2 and adding the number of sorted data columns in the sort clause in column r1, we can get the generated query statement 6(SELECT, rank()over(PARTITION BY c1 ORDERBY c2)as r1, FROM t3). That is, by deleting rank()over(PARTITION BY c1 ORDERBY c2)as r2 in query statement 5, we can remove column r2 from the execution result, but constrain the possible execution result to a single case. That is, the final execution result obtained by executing the query statement includes the part shown in the dashed box in execution result 1.

[0201] Scenario 4: When the query to be generated uses a GROUP BY clause, and the return value of the aggregate function depends on the order of the data rows within the group defined by the GROUP BY clause (e.g., aggregate functions include, but are not limited to, the group_concat() function, which concatenates a column of data within a group into a single long string according to the group's order), the order of the columns on which the aggregate function depends is not strictly constrained. For example, if the columns to be sorted contain identical data, or if they are not sorted at all, the string concatenation order may be arbitrarily specified, leading to various possible execution results. For example... Figure 11 If the query to be generated is query statement 7 (i.e., SELECT c1, group_concat(c2) as gc FROM t4 GROUP BY c1), then query statement 7 will be executed directly. When grouping column c1 of data table t4, the order of the first and second rows within the group is not determined. If the first row comes first, the return value of group_concat(c2) will be "x, y", while if the second row comes first, the return value of group_concat(c2) will be "y, x". Both different return values ​​are allowed, and such cases should be avoided as unexpected situations.

[0202] Therefore, when the query statement to be generated uses a GROUP BY clause, and the return value of the aggregate function used in the query statement depends on the order of the data rows within the group defined by the GROUP BY clause, the constraints are obtained as follows: if the aggregate function allows sorting of the data rows within the group, the constraints include adding a parameter to the statement to be generated to indicate sorting of the data rows within the group; if the aggregate function does not allow sorting of the data rows within the group, the constraints include removing the aggregate function from the statement to be generated. Correspondingly, adding constraints to the query statement to generate the query statement includes: adding parameters to the statement to be generated, generating the query statement, or directly removing the aggregate function from the query statement to be generated, generating the query statement.

[0203] In other words, when the return value of an aggregate function depends on the order of the data rows within a group, resulting in a non-unique aggregation result, if the aggregate function provides additional parameters to allow for additional sorting of the data rows within the group, then such parameters are added to constrain the order of the data columns actually aggregated by the function, ensuring uniqueness. One implementation can perform a full sort on the data columns within the group. If such parameters are not provided, the entire function call expression is removed. This guarantees the uniqueness of the execution result. Figure 11The diagram illustrates a method for adding constraints to output stable execution results according to an embodiment of this application. By adding the extra parameter "ORDER BY c2" (adding a constraint) to the group_concat(c2) function, the generated query statement 8 (i.e., SELECT c1, group_concat(c2, ORDER BY c2) asgc FROM t4 GROUP BY c1) uniquely determines the order of the c2 column within the group, ensuring that the final execution result has only one possibility, namely, execution result 1.

[0204] In summary, since the same SQL statement (i.e., query statement) can return rows in different orders when executed multiple times on the same system, the database output results are often unpredictable, leading to a large number of false positives. Therefore, in this embodiment, by imposing constraints to generate stable query statements, the unstable order of execution results is avoided, effectively preventing false detections of the database under test due to unstable execution result order, and improving the detection accuracy of the database under test. Furthermore, this embodiment does not require splitting the query statement, making it better suited for scenarios where arbitrarily complex query statements are generated for testing the database under test.

[0205] Please see Figure 12 This is a flowchart illustrating a database detection method provided in an embodiment of this application. The database detection method can be executed by a computer device and may include the following steps S1201-S1206:

[0206] S1201. Obtain the reference database corresponding to the database under test. There may be one or more reference databases, and the database architecture of each reference database is different from that of the database under test.

[0207] S1202. Send the generated query statements to each reference database and the database under test respectively;

[0208] S1203. Obtain the test execution results of the query statements executed in the database under test, and obtain the reference execution results of the query statements executed in each reference database.

[0209] S1204. Verify and convert the test execution results and each reference execution result respectively to obtain the verification information of the test execution results and the verification information of each reference execution result.

[0210] It should be understood that the verification and transformation process for the reference execution results of each reference database and the test execution results of the database under test is the same. Here, we will use the verification and transformation of the reference execution results of a reference database as an example. In one implementation, the reference execution result includes data rows and N data columns corresponding to the data rows, where N is a positive integer. The verification and transformation of the reference execution results of the reference database yields the verification information for the reference execution results, including the following steps s21-s24:

[0211] Step s21: Traverse each data column in the preset order, obtain the data column traversed for the i-th time, and determine the data type of the data column traversed for the i-th time.

[0212] It should be understood that any database (i.e., the reference database or the database under test) will return a row of data after executing a query statement. For example, the query statement returns a row of data: xiaoli, 26, 95.5. This row of data corresponds to three data columns, which are, from left to right, xiaoli, 26, and 95.5. Of course, the database can also return multiple rows of data based on the query results, with each row corresponding to a specific set of data columns. In other words, the reference execution result includes multiple rows of data columns. The preset order can be from left to right or from right to left, and this embodiment does not impose any limitation on this.

[0213] Step s22: According to the data type, use the hash seed and the hash value corresponding to the data column traversed in the (i-1)th traversal to perform hash calculation on the data in the data column traversed in the i-th traversal, and obtain the hash value corresponding to the data column traversed in the i-th traversal.

[0214] In this embodiment of the application, an initial hash value is set for each data row, for example, the initial hash value is hash=0; when i=1, the data in the data column traversed for the first time can be hashed according to the data type, using the hash seed and the initial hash value to obtain the hash value corresponding to the data column traversed for the first time. Then, the data columns are traversed in a preset order and the above steps s11-s14 are executed to finally obtain the hash value corresponding to the data row, that is, the hash value corresponding to a row of data.

[0215] Step s22 includes any one of the following steps:

[0216] First, the data type is of type 1. For example, type 1 includes precise data types, which include: first subtype (such as integer type), second subtype (such as binary sequence type), third subtype (such as string type), etc.

[0217] ① If the data type belongs to the first subtype of the first type, then based on the first parameter, the hash seed, and the hash value corresponding to the data column traversed in the (i-1)th iteration, a hash calculation is performed on the data in the data column traversed in the ith iteration to obtain the hash value corresponding to the data column traversed in the ith iteration. The formula for calculating the hash value corresponding to the data column traversed in the ith iteration is as follows:

[0218] Hash(i)=Hash(i-1)* Seed + X * C1 Formula (1)

[0219] Where Hash(i) represents the hash value corresponding to the data column traversed in the i-th iteration, and Hash(i-1) represents the hash value corresponding to the data column traversed in the (i-1)-th iteration; when i=1, Hash(i-1) = the initial hash value; Seed represents the hash seed, X represents the data in the data column traversed in the i-th iteration, and C1 represents the first parameter.

[0220] ② If the data type belongs to the second subtype of the first type, then based on the second parameter, the hash seed, and the hash value corresponding to the data column traversed in the (i-1)th iteration, a hash calculation is performed on the data in the data column traversed in the ith iteration to obtain the hash value corresponding to the data column traversed in the ith iteration. The formula for calculating the hash value corresponding to the data column traversed in the ith iteration is as follows:

[0221] Hash(i)=Hash(i-1)* Seed + byte * C2 Formula (2)

[0222] Where Hash(i) represents the hash value corresponding to the data column traversed in the i-th iteration, and Hash(i-1) represents the hash value corresponding to the data column traversed in the (i-1)-th iteration; when i=1, Hash(i-1) = the initial hash value; Seed represents the hash seed, byte represents the data in the data column traversed in the i-th iteration, and C2 represents the second parameter.

[0223] It should be noted here that if the data in the data column is a binary sequence S, then each byte in the binary sequence S can be hashed according to the above formula (2). When the data in the data column is stored in the database, the binary sequence S can be regarded as a whole byte.

[0224] ③ For the third subtype, taking string as an example, due to the limitation of precise data storage, its data type may not be a string. That is to say, there are many possible cases for a string, such as a string may include ordinary strings; floating-point numbers represented as strings; numerical values ​​represented as strings (such as large integers represented as strings, such as 1122285563), etc. Therefore, we first try to normalize the data in the data column traversed in the i-th iteration. Specifically, we convert the data in the data column traversed in the i-th iteration into data of the second type. Then, we use the corresponding hash calculation method to process it according to whether the conversion is successful. Specifically, if the data type belongs to the third subtype of the first type, we perform the second type conversion on the data in the data column traversed in the i-th iteration to obtain the conversion result; if the conversion result indicates that the data failed to be converted into the second type (floating-point type), it means that the data type is an ordinary string. Then, according to the second parameter, the hash seed, and the hash value corresponding to the data column traversed in the (i-1)-th iteration, we perform hash calculation on the data in the data column traversed in the i-th iteration to obtain the hash value corresponding to the data column traversed in the i-th iteration.

[0225] Specifically, the process of performing hash calculations on the data in the i-th traversed data column based on the second parameter, the hash seed, and the hash value corresponding to the data column traversed in the (i-1)th traversal can include: converting the data in the i-th traversed data column to lowercase, and then performing hash calculations on the converted data based on the second parameter, the hash seed, and the hash value corresponding to the data column traversed in the (i-1)th traversal to obtain the hash value corresponding to the data column traversed in the i-th traversal.

[0226] If the conversion result indicates that the data has been successfully converted to the second type (floating-point type), it means that the data type is not a normal string. Then, the data in the data column traversed in the i-th iteration is truncated to obtain the truncated data corresponding to the data column traversed in the i-th iteration. Then, based on the second parameter, the hash seed, and the hash value corresponding to the data column traversed in the (i-1)-th iteration, the truncated data is hashed to obtain the hash value corresponding to the truncated data. The hash value corresponding to the truncated data is determined as the hash value corresponding to the data column traversed in the i-th iteration.

[0227] The conversion result includes the second type of data obtained after converting the data in the data column traversed in the i-th iteration. The truncated data corresponding to the data column traversed in the i-th iteration can include: truncating the second type of data obtained after conversion to obtain the truncated data corresponding to the data column traversed in the i-th iteration.

[0228] Second, the data type belongs to the second type, which is a non-precise data type, such as the floating-point data type.

[0229] If the data type belongs to the second type, the data in the data column traversed in the i-th iteration is truncated to obtain the truncated data corresponding to the data column traversed in the i-th iteration; according to the second parameter, the hash seed, and the hash value corresponding to the data column traversed in the (i-1)-th iteration, the truncated data is hashed to obtain the hash value corresponding to the truncated data; the hash value corresponding to the truncated data is determined as the hash value corresponding to the data column traversed in the i-th iteration. For the specific calculation method, please refer to the above formula (2).

[0230] The process of truncating the data in the i-th traversed data column to obtain the truncated data corresponding to the i-th traversed data column can include: truncating the data in the i-th traversed data column according to floating-point precision; if the first value of the truncated part is 1, then performing a carry-over operation to obtain the truncated data corresponding to the i-th traversed data column. For example, Figure 13 This is a schematic diagram illustrating floating-point truncation provided in an embodiment of this application. Figure 13 In this approach, the data in the i-th traversed data column can be represented as a binary number, i.e., 0110101000101010111010101; then, according to floating-point precision, 01101010001010111010101 is truncated, with the first digit of the truncated position being 1 (i.e., ...). Figure 13 As shown in Figure 1301, a carry operation is required to obtain the truncated data corresponding to the data column traversed for the i-th time, namely 01101010001011000000000.

[0231] Optionally, if the difference between the truncated data and the target value (e.g., 0) is less than a threshold, the data in the i-th traversed data column is directly set to the target value to eliminate errors near the target value and the problem of different sign bits when calculating the hash of the target value according to the binary sequence. Then, the hash can be calculated according to the binary sequence.

[0232] Step s23: When i = N, determine the verification information of the reference execution result based on the hash value corresponding to the data column traversed in the Nth iteration.

[0233] Specifically, the hash value corresponding to the data column traversed for the Nth time can be determined as the hash value corresponding to the data row, and the verification information of the reference execution result can be determined based on the hash value corresponding to the data row.

[0234] When the reference execution result includes N data columns corresponding to a data row, the hash value corresponding to that data row can be used as the verification information of the reference execution result.

[0235] When the reference execution result includes data columns corresponding to multiple data rows, the hash value corresponding to each data row can be obtained by following steps s21-s24 above. Based on the hash value of each data row, the verification information of the reference execution result is determined. In one implementation, the verification information of the reference execution result includes the hash value corresponding to each data row. In another implementation, the hash values ​​corresponding to all data rows can be added or concatenated to obtain the verification information of the reference execution result; this application embodiment does not impose any limitation on this approach.

[0236] Step s24: When i is less than N, continue to traverse each data column in the preset order to determine the verification information of the reference execution result. That is, the data column traversed in the preset order is taken as the new data column traversed for the i-th time, and processed according to the above steps s22-s24.

[0237] In summary, since the execution results are not always in the same order, and due to the special nature of floating-point arithmetic, different execution results may occur on different hardware, different compilers, different operation orders, and different floating-point operation configurations. Furthermore, floating-point errors sometimes exist. This leads to a large number of false alarms when comparing the execution results between the reference database and the database under test. Therefore, this application embodiment provides a hashing method corresponding to steps s21-s24 above, which can perform hash calculations on the N data columns corresponding to the data row to obtain the verification information of the data row. This effectively reduces false alarms caused by sorting and floating-point operation errors in the execution results. In addition, for the second type of data, this application embodiment can truncate the data according to floating-point precision, maintaining a certain tolerance to reduce false alarms.

[0238] To facilitate understanding, a concrete example illustrates the hash calculation process. For instance, the hash value of this data row includes a data row and three corresponding data columns: 4, 26, and 95.5. The hash calculation process for the data columns corresponding to the data row includes: ① Traversing the three data columns in a preset order to obtain the first traversed data column, whose data is 4; ② Determining that the data type of the first traversed data column is the first subtype of the first type (such as an integer type), hash calculation can be performed according to Formula 1 to obtain the hash value corresponding to the first traversed data column. ③ Continue traversing the 3 data columns to obtain the second traversed data column, whose data is 26; ④ Determine that the data type of the second traversed data column is the first subtype of the first type. Perform a hash calculation according to Formula 1 to obtain the hash value corresponding to the second traversed data column; ⑤ Continue traversing the 3 data columns to obtain the third traversed data column, whose data is 95.5; ⑥ Determine that the data type of the third traversed data column is the second type (floating-point type). Truncate the data in the third traversed data column to obtain the truncated data. Perform a hash calculation according to Formula 2 to obtain the hash value corresponding to the third traversed data column. At this point, all data columns corresponding to this data row have been traversed. Use the hash value corresponding to the third traversed data column as the hash value of this data row, and determine the verification information of the reference execution result based on the hash value of this data row.

[0239] It should be understood that the verification information of the test execution results can be found in the specific implementation of the verification and conversion of the reference execution results, and the embodiments of this application do not limit it in any way.

[0240] S1205. Compare the verification information of each reference execution result with the verification information of the test execution result.

[0241] S1206. If there is inconsistency between the verification information of each reference execution result, or if there is inconsistency between the verification information of each reference execution result and the verification information of the test execution result, then output the detection result that the database under test has logical defects.

[0242] In this embodiment, one or more reference databases corresponding to the database under test are obtained, and the database architecture of each reference database is different from that of the database under test. Generated query statements are sent to each reference database and the database under test respectively. Test execution results obtained from executing the query statements in the database under test are obtained, as are reference execution results obtained from executing the query statements in each reference database. Verification conversion is performed on the test execution results and each reference execution result to obtain verification information for the test execution results and verification information for each reference execution result. The verification information for each reference execution result is compared with the verification information for the test execution results. If there is an inconsistency between the verification information for each reference execution result, or if there is an inconsistency between the verification information for each reference execution result and the verification information for the test execution result, a detection result indicating a logical defect in the database under test is output. Based on the verification information of the test execution results and the verification information of the reference execution results, logical defect detection is performed on the database under test to obtain the detection result for the database under test. As can be seen, the embodiments of this application provide multiple reference databases, and these reference databases have different database architectures from the database under test. This enables the comparison of execution results between the database under test and the multiple reference databases, and can detect database defects that cannot be detected due to the same deployment architecture and relatively fixed logic. It can also effectively detect logical problems in the database under test that are affected by the database architecture, thereby improving the detection accuracy of the database under test.

[0243] It should be understood that the database detection method provided in this application embodiment was tested on three open-source distributed databases: OceanBase, TiDB, and CockroachDB. As shown in Table 2, after manual deduplication, a total of 20 unique code logic defects were found, of which 18 were confirmed by the development object.

[0244] Table 2 shows the logical problems found in open-source distributed databases.

[0245] database Discover confirm OceanBase 11 11 TiDB 7 6 CockroachDB 2 1 total 20 18

[0246] Tables 3 and 4 show examples of defects found in TiDB using the database detection method provided in this application. The left side shows a fragment of the execution result output from the distributed multi-node TiDB, and the right side shows a fragment of the same execution result output from the single-node version of TiDB. The third column of the result is all 1s in the single-node version, while some are 0s in the distributed multi-node version. This was confirmed by the developers as a logical vulnerability in the distributed version code, and the problem has now been fixed.

[0247] Table 3. Execution result snippets from distributed multi-node TiDB.

[0248] 18887865999 2131706870 0 25528841221 2527144552 0 58552525626 5262564855 0 28484994949 8855555555 1

[0249] Table 4 shows a snippet of the execution results output from a single TiDB node.

[0250] 18887865999 2131706870 1 25528841221 2527144552 1 58552525626 5262564855 1 28484994949 8855555555 1

[0251] As can be seen, the database detection method provided in this application can detect the existence of logical defects in the database under test relatively accurately, which makes it easier for the development object to locate the logical defects based on the detected logical defects.

[0252] The following section will describe the database detection device provided in the embodiments of this application.

[0253] Please see Figure 14 , Figure 14 This is a schematic diagram of a database detection device provided in an embodiment of this application. The database detection device can be a computer program (including program code) in a computer device; for example, the database detection device can be application software in a computer device. The database detection device can be used to execute... Figure 5 and Figure 12 Some or all of the steps in the method embodiments shown. Please refer to [link / reference]. Figure 14 The database detection device includes the following units:

[0254] Processing unit 1401 is used to obtain a reference database corresponding to the database under test, wherein the database architecture of the reference database and the database under test are different;

[0255] The sending unit 1402 is used to send the generated query statements to the reference database and the database to be tested, respectively.

[0256] The processing unit 1401 is also used to obtain the test execution result obtained by executing the query statement on the database under test, and to obtain the reference execution result obtained by executing the query statement on the reference database;

[0257] The processing unit 1401 is also used to perform verification conversion on the test execution result and the reference execution result respectively to obtain verification information of the test execution result and verification information of the reference execution result;

[0258] The processing unit 1401 is also used to perform logical defect detection on the database under test based on the verification information of the test execution result and the verification information of the reference execution result, and to obtain the detection result of the database under test.

[0259] Among them, the differences in database architecture between the reference database and the database under test include at least one of the following:

[0260] The number of data node groups included in the reference database is different from the number of data node groups included in the database under test;

[0261] The number of coordinating nodes included in the reference database differs from the number of coordinating nodes included in the database under test.

[0262] The number of backup nodes included in the data node group in the reference database is different from the number of backup nodes included in the data node group in the database under test;

[0263] The reference database is of a different type than the database under test.

[0264] The basic database state of the reference database is consistent with the basic database state of the database under test. Processing unit 1401 is also used for:

[0265] Generate the database state creation statement and send it to the reference database;

[0266] Query the basic database status established based on the database status creation statement in the reference database;

[0267] Based on the queried basic database status, determine the database complexity of the reference database;

[0268] If the database complexity of the reference database meets the complexity condition, then the basic database state for the reference database is established.

[0269] The processing unit 1401 is also used for:

[0270] If the database complexity of the reference database does not meet the complexity condition, a new database state creation statement will be generated.

[0271] The new database state creation statement is sent to the reference database so that the reference database can create a basic database state based on the new database state creation statement, thus completing the creation of the basic database state for the reference database.

[0272] Specifically, when generating the database status establishment statement, the processing unit 1401 can be used for:

[0273] Generate initial database state establishment statements according to the syntax characteristics of the database under test;

[0274] Obtain the syntax supported by the reference database;

[0275] Based on the syntax supported by the reference database, the initial database state creation statement is syntactically adapted to obtain the database state creation statement for the reference database.

[0276] Specifically, when processing unit 1401 performs syntax adaptation on the initial database state establishment statement according to the syntax supported by the reference database to obtain the database state establishment statement of the reference database, it can be used for:

[0277] Based on the syntax supported by the reference database, determine the statements that need to be replaced in the initial database state creation statement;

[0278] By using the syntax supported by the reference database, the statements that need to be replaced in the initial database state creation statement are replaced to obtain the database state creation statement of the reference database.

[0279] Specifically, when processing unit 1401 performs syntax adaptation on the initial database state establishment statement according to the syntax supported by the reference database to obtain the database state establishment statement of the reference database, it can be used for:

[0280] If the initial database state creation statement contains a target statement that does not belong to the syntax supported by the reference database, and removing the target statement does not affect the execution result of the initial database state creation statement, then the target statement is removed from the initial database state creation statement to obtain the database state creation statement of the reference database.

[0281] The basic database state of the reference database is consistent with that of the database under test, and the basic database state includes data tables; the processing unit 1401 is also used for:

[0282] Obtain the constraints;

[0283] Add constraints to the query statement to be generated, and then generate the query statement.

[0284] The constraints include at least one or more of the following:

[0285] Limit the number of nested trigonometric functions in the query statement to be generated;

[0286] If the execution result of the query statement to be generated includes floating-point numbers, then the modulo operation of floating-point numbers is restricted.

[0287] If the execution result of the query statement to be generated includes floating-point numbers, then binary bit operations on floating-point numbers are restricted.

[0288] In the query statement to be generated, restrict the use of grouping clauses on the target data columns included in the data table;

[0289] In the query statement to be generated, the deduplication operation is restricted to the target data column included in the data table. The target data column refers to the floating-point data column in the data table.

[0290] The query statement to be generated contains a clause limiting the number of execution results. Processing unit 1401, when obtaining the constraints, specifically uses the following:

[0291] If the query to be generated does not contain a sort clause, the constraints include using a sort clause in the query and increasing the number of columns to be sorted in the sort clause; or,

[0292] If the query statement to be generated uses a sort clause, and the sorted data columns contain the same data, then increase the number of sorted data columns in the sort clause.

[0293] The data table includes data rows and corresponding data columns; the query statement to be generated uses a window function that depends on the relative position of the data rows within the segmented window, and the window function is used to segment the data in the data table into windows; the processing unit 1401, when obtaining constraints, is specifically used for:

[0294] If no sort clause is used in the split window corresponding to the window function, the constraints include: using a sort clause in the split window corresponding to the window function, and increasing the number of columns to be sorted in the sort clause; or,

[0295] If a sort clause is used in the split window corresponding to the window function, and the sorted data columns in the sort clause contain the same data, then the constraint includes: increasing the number of sorted data columns in the sort clause.

[0296] The data table includes data rows; the query statement to be generated uses window functions that depend on the relative position of the data rows within the segmented window, and there are multiple window functions; the processing unit 1401, when obtaining constraints, is specifically used for:

[0297] If multiple window functions are used to partition a data table, and the data rows are partitioned and sorted differently within the partition windows corresponding to each window function, then the constraints include: retaining one window function from among the multiple window functions, and increasing the number of data columns to be sorted in the sorting clauses included in the partition windows corresponding to the retained window function.

[0298] The query statement to be generated uses a grouping clause, and the return value of the aggregate function used in the query statement depends on the order of the data rows within the group defined by the grouping clause. Processing unit 1401, when obtaining constraints, specifically performs the following:

[0299] If the aggregate function allows sorting of rows within a group, the constraints include adding a parameter to the statement to be generated that indicates sorting of rows within the group.

[0300] If aggregate functions are not allowed to sort rows within a group, then the constraint includes: removing aggregate functions from the statement to be generated.

[0301] The number of reference databases is one or more. When the processing unit 1401 performs logical defect detection on the database under test based on the verification information of the test execution results and the verification information of the reference execution results, and obtains the detection results of the database under test, it can be specifically used for:

[0302] Compare the verification information of each reference execution result with the verification information of the test execution result;

[0303] If there are inconsistencies between the verification information of each reference execution result, or between the verification information of each reference execution result and the verification information of the test execution result, the detection result of logical defects in the database under test will be output, and a logical defect detection report will be generated.

[0304] The reference execution result includes a data row and N data columns corresponding to the data row, where N is a positive integer. When processing unit 1401 verifies and transforms the reference execution result to obtain verification information, it can specifically be used for:

[0305] The data columns are traversed sequentially according to a preset order. The data column traversed for the i-th time is obtained, and the data type of the data column traversed for the i-th time is determined.

[0306] According to the data type, the hash seed and the hash value corresponding to the data column traversed in the (i-1)th traversal are used to perform hash calculation on the data in the data column traversed in the i-th traversal, so as to obtain the hash value corresponding to the data column traversed in the i-th traversal.

[0307] When i = N, the verification information of the reference execution result is determined based on the hash value corresponding to the data column traversed in the Nth iteration;

[0308] When i is less than N, continue to traverse each data column in the preset order to determine the verification information of the reference execution result.

[0309] Specifically, when processing unit 1401 performs hash calculations on the data in the data column traversed in the (i-1)th traversal according to the data type, using a hash seed and the hash value corresponding to the data column traversed in the (i-1)th traversal, to obtain the hash value corresponding to the data column traversed in the i-th traversal, it can be used for:

[0310] If the data type belongs to the first subtype of the first type, then according to the first parameter, the hash seed and the hash value corresponding to the data column traversed in the (i-1)th traversal, the data in the data column traversed in the i-th traversal are hashed to obtain the hash value corresponding to the data column traversed in the i-th traversal.

[0311] If the data type belongs to the second subtype of the first type, then according to the second parameter, the hash seed and the hash value corresponding to the data column traversed in the (i-1)th traversal, the data in the data column traversed in the i-th traversal are hashed to obtain the hash value corresponding to the data column traversed in the i-th traversal.

[0312] If the data type belongs to the third subtype of the first type, then the data in the data column traversed in the i-th iteration will be converted to the second type to obtain the conversion result; if the conversion result indicates that the data failed to be converted to the second type, then according to the second parameter, the hash seed and the hash value corresponding to the data column traversed in the (i-1)-th iteration, the data in the data column traversed in the i-th iteration will be hashed to obtain the hash value corresponding to the data column traversed in the i-th iteration.

[0313] The processing unit 1401 is also used for:

[0314] If the data type is of type 2, or the conversion result indicates that the data has been successfully converted to type 2, then the data in the data column traversed in the i-th iteration is truncated to obtain the truncated data corresponding to the data column traversed in the i-th iteration.

[0315] Based on the second parameter, the hash seed, and the hash value corresponding to the data column traversed in the (i-1)th iteration, perform hash calculation on the truncated data to obtain the hash value corresponding to the truncated data;

[0316] The hash value corresponding to the truncated data is determined as the hash value corresponding to the data column traversed in the i-th iteration.

[0317] In this embodiment, a reference database corresponding to the database under test is obtained. The reference database has a different database architecture than the database under test. Generated query statements are sent to both the reference database and the database under test. The test execution results of the query statements executed in the database under test and the reference execution results of the query statements executed in the reference database are obtained. The test execution results and the reference execution results are verified and converted to obtain verification information for the test execution results and the reference execution results, respectively. Based on the verification information of the test execution results and the reference execution results, logical defect detection is performed on the database under test to obtain the detection results. It is evident that by introducing a reference database with a different architecture than the database under test, the execution results of the database under test and the reference database with different architectures can be directly compared. This avoids the problem of databases with the same deployment architecture generating the same erroneous logic, thus failing to detect defects in the database under test and improving the accuracy of database detection.

[0318] Furthermore, this application also provides a schematic diagram of the structure of a computer device, which can be found in [reference needed]. Figure 15The computer device can be a terminal, and may include a processor 1501, an input device 1502, an output device 1503, and a memory 1504. The processor 1501, input device 1502, output device 1503, and memory 1504 are connected via a bus. The memory 1504 stores a computer program, which includes program instructions. The processor 1501 executes the program instructions stored in the memory 1504. By running the program instructions stored in the memory 1504, the processor 1501 causes the computer device to perform the method steps in the foregoing embodiments of this application. Furthermore, the effects achieved after the computer device executes the method steps in the various embodiments through the processor can be referred to the description of the foregoing embodiments, and will not be repeated here.

[0319] In this application, the term "unit" refers to a computer program or part of a computer program that has a predetermined function and works with other related parts to achieve a predetermined goal, and can be implemented wholly or partially using software, hardware (such as processing circuitry or memory), or a combination thereof. Similarly, a processor (or multiple processors or memory) can be used to implement one or more units. Furthermore, each unit can be part of an overall unit that includes the functionality of that unit.

[0320] Furthermore, it should be noted that this application also provides a computer-readable storage medium storing a computer program, which includes program instructions. When a processor executes these program instructions, it can execute the aforementioned... Figure 5 or Figure 12 The methods described in the corresponding embodiments are therefore not repeated here. For technical details not disclosed in the computer-readable storage medium embodiments related to this application, please refer to the description of the method embodiments of this application. As an example, program instructions may be deployed on a computer device, executed on multiple computer devices located in one location, or executed on multiple computer devices distributed in multiple locations and interconnected through a communication network.

[0321] According to one aspect of this application, a computer program product is provided, comprising a computer program stored in a computer-readable storage medium. A processor of a computer device reads the computer program from the computer-readable storage medium, and the processor executes the computer program, enabling the computer device to perform the aforementioned... Figure 5 or Figure 12 The methods described in the corresponding embodiments are therefore not repeated here.

[0322] Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), or random access memory (RAM), etc.

[0323] The above-disclosed embodiments are merely preferred embodiments of this application and should not be construed as limiting the scope of this application. Therefore, any equivalent variations made in accordance with the claims of this application shall still fall within the scope of this application.

Claims

1. A detection method for a database, characterized by, The method comprises: obtaining a reference database corresponding to a to-be-tested database, the database architecture of the reference database being different from that of the to-be-tested database; sending the generated query statement to the reference database and the to-be-tested database respectively; obtaining a test execution result obtained by executing the query statement by the to-be-tested database, and obtaining a reference execution result obtained by executing the query statement by the reference database; respectively verifying and converting the test execution result and the reference execution result to obtain verification information of the test execution result and verification information of the reference execution result; performing logical defect detection on the to-be-tested database according to the verification information of the test execution result and the verification information of the reference execution result to obtain a detection result of the to-be-tested database.

2. The method of claim 1, wherein, The database architecture of the reference database is different from that of the to-be-tested database, including at least one of the following: the number of data node groups included in the reference database is different from the number of data node groups included in the to-be-tested database; the number of coordination nodes included in the reference database is different from the number of coordination nodes included in the to-be-tested database; the number of standby nodes included in a data node group in the reference database is different from the number of standby nodes included in a data node group in the to-be-tested database; the type of the reference database is different from the type of the to-be-tested database.

3. The method of claim 1, wherein, The basic database state of the reference database is consistent with the basic database state of the to-be-tested database, and the method further comprises: generating a database state establishment statement and sending the database state establishment statement to the reference database; querying the basic database state established by the reference database based on the database state establishment statement; determining the database complexity of the reference database according to the queried basic database state; if the database complexity of the reference database meets the complexity condition, the basic database state of the reference database is established.

4. The method of claim 3, wherein, The method further comprises: if the database complexity of the reference database does not meet the complexity condition, a new database state establishment statement is generated; the new database state establishment statement is sent to the reference database, so that the reference database establishes a basic database state based on the new database state establishment statement, to complete the establishment of the basic database state of the reference database.

5. The method of claim 4, wherein, The method further comprises: generating an initial database state establishment statement according to the syntax characteristics of the to-be-tested database; obtaining the syntax supported by the reference database; performing syntax adaptation on the initial database state establishment statement according to the syntax supported by the reference database to obtain the database state establishment statement of the reference database.

6. The method of claim 5, wherein, The method further comprises: determining the statement to be replaced in the initial database state establishment statement according to the syntax supported by the reference database; Replace the statement to be replaced in the initial database state establishment statement with a statement supported by the reference database to obtain a database state establishment statement of the reference database.

7. The method of claim 5, wherein, The syntax adaptation of the initial database state establishment statement according to the syntax supported by the reference database comprises: If there is a target statement in the initial database state establishment statement that does not belong to the syntax supported by the reference database, and the target statement is removed without affecting the execution result of the initial database state establishment statement, the target statement is removed from the initial database state establishment statement to obtain the database state establishment statement of the reference database.

8. The method of claim 1, wherein, The basic database state of the reference database is consistent with the basic database state of the to-be-tested database, and the basic database state comprises a data table; the method further comprises: Obtaining a constraint condition; Adding the constraint condition in the to-be-generated query statement to generate a query statement; The constraint condition comprises at least one or more of the following: Limiting the number of nested trigonometric functions in the to-be-generated query statement; If the execution result corresponding to the to-be-generated query statement comprises a floating-point number, limiting the modulo processing of the floating-point number; If the execution result corresponding to the to-be-generated query statement comprises a floating-point number, limiting the binary bit operation on the floating-point number; In the to-be-generated query statement, limiting the use of a grouping clause on a target data column included in the data table; In the to-be-generated query statement, limiting the execution of a deduplication operation on a target data column included in the data table, the target data column being a data column of a floating-point type in the data table.

9. The method of claim 8, wherein, The to-be-generated query statement uses a clause for limiting the number of execution results, and the constraint condition comprises: If the to-be-generated query statement does not use a sorting clause, the constraint condition comprises using a sorting clause in the to-be-generated query statement and increasing the number of sorted data columns in the sorting clause; or, If the to-be-generated query statement uses a sorting clause and the sorted data columns contain the same data, the constraint condition comprises increasing the number of sorted data columns in the sorting clause.

10. The method of claim 8, wherein, The data table comprises data rows and data columns corresponding to the data rows, and the to-be-generated query statement uses a window function that depends on the relative positions of the data rows in a partition window, the window function being used for partitioning windows in the data table; The constraint condition comprises: If the partition window corresponding to the window function does not use a sorting clause, the constraint condition comprises using a sorting clause in the partition window corresponding to the window function and increasing the number of sorted data columns in the used sorting clause; or, If the partition window corresponding to the window function uses a sorting clause and the sorted data columns in the used sorting clause contain the same data, the constraint condition comprises increasing the number of sorted data columns in the used sorting clause.

11. The method of claim 8, wherein, The data table comprises data rows; the query statement to be generated uses window functions that depend on the relative positions of the data rows in the partitioned window, and the number of the window functions is multiple; the constraint condition comprises: If multiple window functions are used to partition the data table, and the partitioned orders of the data rows in the partitioned windows corresponding to the multiple window functions are different, the constraint condition comprises: retaining one window function from the multiple window functions, and adding the number of the sorted data columns in the order clause included in the partitioned window corresponding to the retained window function.

12. The method of claim 8, wherein, The query statement to be generated uses a grouping clause, and the return value of an aggregation function used in the query statement to be generated depends on the order of the data rows in a group grouped by the grouping clause, and the constraint condition comprises: If the aggregation function allows the data rows in the group to be sorted, the constraint condition comprises: adding a parameter in the query statement to be generated, the parameter being used to indicate that the data rows in the group are sorted; If the aggregation function does not allow the data rows in the group to be sorted, the constraint condition comprises: deleting the aggregation function in the query statement to be generated.

13. The method of claim 1, wherein, The number of the reference databases is one or more, the logical defect detection of the database to be tested is performed according to the verification information of the test execution result and the verification information of the reference execution result, and the detection result of the database to be tested is obtained, comprising: Comparing the verification information of each reference execution result and the verification information of the test execution result; If there is inconsistency between the verification information of each reference execution result or between the verification information of each reference execution result and the verification information of the test execution result, outputting the detection result that the database to be tested has a logical defect, and generating a logical defect detection report.

14. The method of claim 1, wherein, The reference execution result comprises data rows and N data columns corresponding to the data rows, and N is a positive integer; The verification conversion of the reference execution result is performed to obtain the verification information of the reference execution result, comprising: sequentially traversing each data column according to a preset order, obtaining an i-th traversed data column, and determining the data type of the i-th traversed data column; performing hash calculation on the data in the i-th traversed data column according to the data type, a hash seed, and a hash value corresponding to an (i-1)-th traversed data column to obtain a hash value corresponding to the i-th traversed data column; when i=N, determining the verification information of the reference execution result according to the hash value corresponding to the N-th traversed data column; when i is less than N, continuing to sequentially traverse each data column according to the preset order to determine the verification information of the reference execution result.

15. The method of claim 14, wherein, The hash calculation on the data in the i-th traversed data column according to the data type, a hash seed, and a hash value corresponding to an (i-1)-th traversed data column to obtain a hash value corresponding to the i-th traversed data column comprises any one of the following steps: If the data type belongs to a first sub-type in the first type, a hash value corresponding to the i-th traversed data column is obtained by performing hash calculation on data in the i-th traversed data column according to the first parameter, the hash seed, and a hash value corresponding to a (i-1)-th traversed data column; If the data type belongs to a second sub-type in the first type, a hash value corresponding to the i-th traversed data column is obtained by performing hash calculation on data in the i-th traversed data column according to the second parameter, the hash seed, and a hash value corresponding to a (i-1)-th traversed data column. If the data type belongs to a third sub-type in the first type, a conversion result is obtained by performing second-type conversion processing on data in the i-th traversed data column; if the conversion result indicates that the data conversion into the second type fails, a hash value corresponding to the i-th traversed data column is obtained by performing hash calculation on data in the i-th traversed data column according to the second parameter, the hash seed, and a hash value corresponding to a (i-1)-th traversed data column.

16. The method of claim 15, wherein, The method further comprises: If the data type belongs to the second type, or the conversion result indicates that the data conversion into the second type succeeds, truncated data corresponding to the i-th traversed data column is obtained by performing truncation processing on data in the i-th traversed data column; A hash value corresponding to the truncated data is obtained by performing hash calculation on the truncated data according to the second parameter, the hash seed, and a hash value corresponding to a (i-1)-th traversed data column. The hash value corresponding to the truncated data is determined as the hash value corresponding to the i-th traversed data column.

17. A detecting apparatus for a database, characterized by comprising: Comprise: A processing unit configured to obtain a reference database corresponding to a to-be-tested database, wherein a database architecture of the reference database is different from that of the to-be-tested database; A sending unit configured to send a generated query statement to the reference database and the to-be-tested database respectively; The processing unit is further configured to obtain a test execution result obtained by executing the query statement on the to-be-tested database, and obtain a reference execution result obtained by executing the query statement on the reference database; The processing unit is further configured to perform verification conversion on the test execution result and the reference execution result respectively, to obtain verification information of the test execution result and verification information of the reference execution result; The processing unit is further configured to perform logic defect detection on the to-be-tested database according to the verification information of the test execution result and the verification information of the reference execution result, to obtain a detection result of the to-be-tested database.

18. A computer device, comprising: Comprise: A processor adapted to execute a computer program; A computer readable storage medium, the computer readable storage medium has a computer program stored therein, when the computer program is executed by the processor, the detection method for a database as claimed in any one of claims 1-16 is executed.

19. A computer-readable storage medium, characterized in that, The computer storage medium stores a computer program, and the computer program is executed by a processor to perform the detection method for the database according to any one of claims 1-16.

20. A computer program product, characterised in that, The computer program product comprises a computer program, and the computer program is executed by a processor to implement the detection method for the database according to any one of claims 1-16.

Images