Method and apparatus for processing sql statement

Abstract

Embodiments of the present specification provide a processing method and device for SQL statements. In the processing method, an original SQL statement to be processed is obtained, which includes a main query statement and a subquery statement. The query source table clauses of the main query statement and the subquery statement each include a first data table and a second data table. The main query condition clause of the main query statement includes a target query condition that depends on the subquery result of the subquery statement. It is determined whether the target query condition supports an empty subquery result. According to the determination result, the original SQL statement is equivalently rewritten to obtain a target SQL statement. The target SQL statement is used to query based on an inner join result set or an outer join result set of the first and second data tables.

Description

Technical Field

[0001] This specification relates to one or more embodiments in the field of databases, and more particularly to a method and apparatus for processing SQL statements. Background Technology

[0002] In recent years, with the development of computers and databases, SQL (Structured Query Language) has been widely used as a computer programming language specifically for databases, becoming the most important and commonly used relational database query language.

[0003] In existing database query techniques, the execution of some SQL statements containing subqueries typically involves establishing nested joins between the main query's data table (hereinafter referred to as the main table) and the subquery's data table (hereinafter referred to as the subtable). However, this nested join method requires repeated scanning of the subtable, which generates unnecessary overhead and prevents the query optimizer from developing an efficient query plan.

[0004] Therefore, there is a need to provide a processing solution for SQL statements so that the query optimizer can formulate a more efficient query plan based on the processed SQL statements. Summary of the Invention

[0005] This specification describes one or more embodiments of a method and apparatus for processing SQL statements, which helps a query optimizer to develop more efficient query plans.

[0006] Firstly, it provides a method for processing SQL statements, including:

[0007] Obtain the raw SQL statement to be processed, including the main query statement and the subquery statement; the main query source table clause of the main query statement includes a first data table, and the subquery source table clause of the subquery statement includes a second data table; the main query condition clause of the main query statement includes a target query condition that depends on the subquery result of the subquery statement.

[0008] Determine whether the target query condition supports the subquery result being empty;

[0009] Based on the judgment result, the original SQL statement is rewritten equivalently to obtain the target SQL statement; the target SQL statement is used to perform a query based on the inner join result set or outer join result set of the first and second data tables.

[0010] Secondly, a device for processing SQL statements is provided, including:

[0011] The acquisition unit is used to acquire the original SQL statement to be processed, including a main query statement and a subquery statement; the main query source table clause of the main query statement includes a first data table, and the subquery source table clause of the subquery statement includes a second data table; the main query condition clause of the main query statement includes a target query condition that depends on the subquery result of the subquery statement.

[0012] The judgment unit is used to determine whether the target query condition supports the subquery result being empty;

[0013] The rewriting unit is used to rewrite the original SQL statement equivalently based on the judgment result to obtain the target SQL statement; the target SQL statement is used to perform a query based on the inner join result set or outer join result set of the first and second data tables.

[0014] Thirdly, a computer storage medium is provided that stores a computer program thereon, which, when executed in a computer, causes the computer to perform the method of the first aspect.

[0015] Fourthly, a computing device is provided, including a memory and a processor, wherein the memory stores executable code, and the processor executes the executable code to implement the method of the first aspect.

[0016] The SQL statement processing method and apparatus provided in one or more embodiments of this specification, where the query source table clauses of the main query statement and subquery statement of the original SQL statement each include different data tables, and the main query condition clause of the main query statement includes a target query condition that depends on the subquery result of the subquery statement, rewrites the original SQL statement equivalently into a target SQL statement that queries based on the inner join result set or the outer join result set, depending on whether the target query condition supports the subquery result being null. In other words, this solution eliminates the subquery statement by establishing an inner join or outer join between the main table and the subtable, thereby reducing the number of scans on the subtable. It should be noted that for inner joins or outer joins, the query optimizer can more flexibly choose the number of joins and the join algorithm when formulating the query plan, which helps the query optimizer to formulate a more efficient execution plan. Attached Figure Description

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

[0018] Figure 1This is a schematic diagram illustrating an implementation scenario of one embodiment disclosed in this specification;

[0019] Figure 2 A flowchart illustrating a method for processing SQL statements according to one embodiment is shown;

[0020] Figure 3 A flowchart illustrating a method for processing SQL statements according to another embodiment is shown;

[0021] Figure 4 A schematic diagram of an apparatus for processing SQL statements according to one embodiment is shown. Detailed Implementation

[0022] The solution provided in this specification will now be described with reference to the accompanying drawings.

[0023] Before describing the solutions provided in the embodiments of this specification, the components of an SQL statement will be briefly explained.

[0024] Generally speaking, an SQL statement mainly consists of three parts: the query selection clause (also known as the SELECT clause), the query source table clause (also known as the FROM clause), and the query condition clause (also known as the WHERE clause).

[0025] The query select clause can include columns from a data table or view, as well as grouping aggregate functions. These grouping aggregate functions are used to perform aggregation operations on one or more columns in a data table or view. Examples include functions such as SUM(), AVG(), MAX(), MIN(), or COUNT().

[0026] The source table clause can include objects such as tables or views. It should be understood that when the source table clause includes two objects, such as two tables, two views, or one table and one view, a join must be established between those two objects.

[0027] It should be noted that by establishing a connection between these two objects, they can be merged to form a new result set. Based on the resulting result set, database operations such as querying, adding, deleting, and modifying data can be performed.

[0028] Regarding the two objects mentioned above, a connection can be established using a specified connection type. It should be noted that when specifying a connection type, the syntax for the source table query clause can be expressed as: `table1<connection type>JOIN table2`. Of course, the `table` here can also be replaced with a view.

[0029] The above-mentioned join types can include, for example, inner join, outer join, or cross join. Different join types can be further subdivided; for example, inner join can include equi-join, non-equi-join, and natural join; outer join can include left join, right join, and full join.

[0030] Furthermore, regarding the connection between the two objects mentioned above, corresponding join conditions can be specified. In one example, this join condition can be included in the query source table clause.

[0031] It should be noted that when a join condition is also specified, the syntax of the above query source table clause can be expressed as: data table1<join type> JOIN data table2 ON<join condition>.

[0032] In another example, the join condition can also be included in the query condition clause of the SQL statement.

[0033] Finally, the query condition clause can include several query conditions. These query conditions can be divided into query conditions without filter terms (also known as ordinary query conditions) and query conditions with filter terms (also known as predefined query conditions). The filter terms can include any of the following: IN, NOT IN, ANY, ALL, EXISTS, and NOTEXISTS.

[0034] In some embodiments, query conditions can be simple conditions or compound conditions. A compound condition can be understood as a combination of multiple simple conditions. For example, multiple simple conditions can be combined using logical relationships such as "AND" or "OR" to form a compound condition.

[0035] It should be noted that in practice, SQL query condition clauses can be nested within other SQL statements. These are called SQL statements containing subqueries, where the innermost statement is called the subquery, and the outermost statement is called the main query. SQL statements containing subqueries typically have lower execution efficiency (see the background section for reasons), therefore they need to be rewritten equivalently so that the query optimizer can develop a more efficient query plan.

[0036] Traditional techniques typically only allow for equivalent rewriting of specific SQL statements containing subqueries, such as those with subqueries using the ANY / ALL filter. Specifically, this is achieved by establishing a semi-join / anti-join between the main query's and the subquery's tables to eliminate the subquery, thus achieving an equivalent rewrite. Another example is equivalent rewriting of SQL statements with subqueries containing aggregate functions. This is done by establishing a join between the main query's and the subquery's tables, then grouping the join result set to eliminate the subquery, thereby achieving an equivalent rewrite.

[0037] However, in many cases, SQL statements containing subqueries do not fall into any of the above categories. For this type of SQL statement (referred to as ordinary SQL statements containing subqueries), it is usually impossible to rewrite them equivalently.

[0038] The inventors of this application have discovered that, for SQL statements containing subqueries, when the subquery statements are not included in the aforementioned filter terms, or when the query conditions of the main query that depend on the subquery are of a normal type, the subquery statements can be eliminated by establishing an inner join or outer join between the data tables of the main query and the subquery, thereby achieving an equivalent rewriting of the SQL statement.

[0039] The following is a detailed explanation of this plan.

[0040] Figure 1 This is a schematic diagram illustrating an implementation scenario of one of the embodiments disclosed in this specification. Figure 1 In a distributed database, any database node can include a query optimizer and an executor. The query optimizer can include a query rewriting module and a plan-making module. The query rewriting module receives the raw SQL statement. It then parses the raw SQL statement and, based on predefined rules, determines whether further processing is needed. These rules specifically determine whether executing the SQL statement will incur unnecessary overhead, such as whether repeated table scans are necessary, and / or whether nested joins are required.

[0041] When processing the original SQL statement is required, it is rewritten equivalently to obtain the target SQL statement. This target SQL statement is used to perform queries based on the inner join result set or outer join result set of the data table.

[0042] Afterwards, the query rewriting module can send the target SQL statement to the planning module. The planning module then determines the corresponding join order and join algorithm for the target SQL statement, and then formulates a query plan. The query plan is then sent to the executor for execution, thereby enabling the query to be performed on the distributed database.

[0043] It should be understood that Figure 1 This is merely an illustrative example. In practical applications, the query rewriting module may not be part of the query optimizer, and this specification does not limit this.

[0044] It should be noted that in the embodiments of this specification, the original SQL statement is divided into two types according to whether the subquery result of the subquery contained in the original SQL statement exceeds one row, and then the corresponding rewriting method is described for each type of SQL statement.

[0045] Specifically, an SQL statement containing a subquery whose subquery result is no more than one row is called an SQL statement containing a subquery with uniqueness, while an SQL statement containing a subquery whose subquery result is more than one row is called an SQL statement containing a subquery without uniqueness.

[0046] In one example, it can be determined whether the result of a subquery exceeds one row based on whether the attribute columns or combinations of attribute columns involved in the subquery conditions satisfy the uniqueness constraint.

[0047] For example, suppose the subquery condition is: T1.C1 = T2.C1 AND T2.C3 = 1, and a unique index is pre-created for T2 as follows: CREATE INDEX UIDX_C1_C3 ON T2(C1,C3). Therefore, the SQL statement that includes the above subquery is the SQL statement that includes a unique subquery.

[0048] The following section will first explain how to rewrite SQL statements containing unique subqueries.

[0049] Figure 2 A flowchart illustrating a method for processing SQL statements according to one embodiment is shown. This method can be executed by any device, apparatus, platform, or cluster of devices with computing and processing capabilities. Figure 2 As shown, the method may include the following steps.

[0050] Step 202: Obtain the original SQL statement to be processed, which includes the main query statement and the subquery statement.

[0051] The main query statement here includes a SELECT clause, a FROM clause, and a WHERE clause. The subquery statement also includes a SELECT clause, a FROM clause, and a WHERE clause.

[0052] The FROM clause of the main query statement includes the first data table, and the FROM clause of the subquery statement includes the second data table. Furthermore, the WHERE clause of the main query statement includes the target query condition that depends on the result of the subquery statement.

[0053] It should be noted that executing the original SQL statement requires scanning the second table once for each row in the first table. This execution method is similar to a nested join where the first table drives the second table, meaning the second table is scanned repeatedly. However, repeated scanning of the tables incurs unnecessary overhead, especially when the tables contain a large amount of data distributed across different database nodes. This can significantly impact database query efficiency, necessitating processing of the original SQL statement.

[0054] In one example, the original SQL statement above could be expressed as follows:

[0055] Q1:SELECT T1.C1,T1.C2,T1.C3 FROM T1 WHERE T1.C2>(SELECT T2.C2 FROM T2WHERE T1.C1=T2.C1 AND T2.C3=1);

[0056] In the original SQL statement, the statement enclosed in parentheses is the subquery statement. The FROM clause of the subquery statement includes the second data table T2, and the WHERE clause contains the attribute columns T2.C1 and T2.C3, which are combinations of attribute columns that satisfy the uniqueness constraint.

[0057] Furthermore, in the original SQL statement, the statement other than the subquery statement is the main query statement. The FROM clause of the main query statement includes the first data table T1, and the WHERE clause includes the target query condition: T1.C2>(SELECT T2.C2 FROM T2 WHERE T1.C1=T2.C1 AND T2.C3=1).

[0058] It should be noted that in the above example, for each row in T1, T2 needs to be scanned once, that is, T2 needs to be scanned repeatedly, thus requiring processing of Q1.

[0059] In another example, the original SQL statement above could be as follows:

[0060] Q2: SELECT T1.C1,T1.C2,T1.C3 FROM T1 WHERE(SELECT NVL(T2.C2,0)FROMT2WHERE T1.C1=T2.C1 AND T2.C3=1)IS NULL;

[0061] In this example, the FROM clause of the subquery statement includes the second data table T2, and the attribute columns T2.C1 and T2.C3 involved in the WHERE clause are a combination of attribute columns that satisfy the uniqueness constraint.

[0062] In addition, the FROM clause of the main query statement includes the first data table T1, and the WHERE clause includes the target query condition: (SELECT NVL(T2.C2,0)FROM T2 WHERE T1.C1=T2.C1 AND T2.C3=1)IS NULL.

[0063] It should be noted that in this example, for each row in T1, T2 needs to be scanned once, that is, T2 needs to be scanned repeatedly, thus requiring processing of Q2.

[0064] For the original SQL statement that needs to be processed, it can be determined whether the SQL statement meets the preset rewrite conditions. If the preset rewrite conditions are met, the original SQL statement is processed.

[0065] In one example, the above rewrite condition could include: the target query condition in the main query condition clause does not belong to a predefined type of query condition (see above).

[0066] In another example, the above rewrite condition may also include: the subquery statement does not contain the following syntax components: ROMNUM, GROUP BY, aggregate functions, HAVING, ORDER BY, LIMIT, window functions, hierarchical queries, or set operations.

[0067] Taking Q1 and Q2 as examples above, both of them meet the preset rewriting conditions.

[0068] Step 204: Determine whether the target query condition supports a subquery result that is empty.

[0069] In other words, it determines whether the target query condition is met or true when the subquery result is empty.

[0070] Taking Q1 as an example, if the subquery result is empty, the target query condition would be: C2>(NULL). However, this comparison result is UNKNOWN, therefore the target query condition is not true. It is clear that the target query condition for Q1 does not support an empty subquery result.

[0071] Taking Q2 as an example again, when the subquery result is empty, the target query condition above is: (NULL)IS NULL. Obviously, the target query condition is true. Therefore, the target query condition of Q2 supports an empty subquery result.

[0072] Step 206: Based on the judgment result, the original SQL statement is rewritten equivalently to obtain the target SQL statement. The target SQL statement is used to perform queries based on the inner join result set or outer join result set of the first and second data tables.

[0073] Specifically, if the judgment result indicates that the target query condition does not support the subquery result being empty, the original SQL statement is equivalently rewritten into the first target SQL statement, which is used to perform a query based on the inner join result set of the first data table and the second data table.

[0074] In the following description of this specification, the statement that the target query condition does not support the subquery result is referred to as the null rejection property of the target query condition.

[0075] The specific steps for rewriting the above equivalent SQL statement into the first target SQL statement are as follows:

[0076] Add the second data table to the FROM clause of the main query statement;

[0077] Add the query conditions from the WHERE clause of the subquery to the WHERE clause of the main query.

[0078] Replace the subquery statement with the subquery expression in the SELECT clause of the subquery statement.

[0079] Of course, in practical applications, other statements with the same function as the subquery statement can be used to replace the subquery statement in order to rewrite the original SQL statement into the first target SQL statement. This manual does not limit this.

[0080] It should be noted that after performing the above equivalent rewriting steps, the subquery statement can be eliminated, and the first target SQL statement after equivalent rewriting is used to query based on the inner join result set of the two data tables. That is, only an inner join needs to be established for the two data tables. This allows the query optimizer to flexibly choose different join algorithms, such as hash join or merge join, both of which can greatly reduce the number of scans on the second data table.

[0081] For example, by equivalently rewriting the aforementioned Q1, we can obtain Q1':

[0082] SELECT T1.C1,T1.C2,T1.C3 FROM T1,T2 WHERE T1.C2>T2.C2 AND T1.C1=T2.C1 AND T2.C3=1;

[0083] Specifically, Q1' is obtained by performing the following equivalent rewrite on Q1: adding T2 to the FROM clause of the main query statement, adding the query condition T1.C1=T2.C1 AND T2.C3=1 from the WHERE clause of the subquery statement to the WHERE clause of the main query statement, and replacing the subquery statement with the subquery expression T2.C2 from the SELECT clause of the subquery statement.

[0084] It should be noted that for the equivalent rewritten Q1' described above, the query optimizer can use either a hash join or a merge join for T1 and T2. Both algorithms can significantly reduce the number of scans for T2. Furthermore, when the amount of data after filtering T2 is small, the amount of data in T1 is large, and T1.C1 = T2.C1 has a strong filtering effect on T1, for Q1', the query optimizer can also choose to perform a nested join (NEST LOOP JOIN) driven by T2 for T1, and convert T1.C1 = T2.C1 into a filtering clause for T1, thereby significantly reducing the amount of data scanned for T1.

[0085] Furthermore, if the judgment result indicates that the target query condition supports a null subquery result, the original SQL statement is equivalently rewritten into a second target SQL statement for querying based on the outer join result sets of the first and second data tables.

[0086] In other words, when the target query condition does not have the property of rejecting null values, the original SQL statement is equivalently rewritten into the second target SQL statement.

[0087] The specific steps for rewriting the above equivalent SQL statement into the second target SQL statement are as follows:

[0088] Add the second data table as the right table in the outer join to the FROM clause of the main query statement;

[0089] Add the query conditions in the WHERE clause of the subquery as the join conditions of the outer join to the FROM clause of the main query.

[0090] Based on the attribute columns in the second data table that satisfy the non-null constraint, and the subquery expression in the SELECT clause of the subquery statement, construct the first condition control statement and use it to replace the subquery statement.

[0091] It should be noted that, in order to achieve an equivalent rewrite of the original SQL statement, the first condition control statement here should meet the following conditions: when the first and second data tables are successfully joined, output the value of the subquery expression in the subquery statement; otherwise, output NULL.

[0092] In one example, the steps to construct the first condition control statement can be as follows: the first condition control statement is constructed by taking the values ​​of the attribute columns that satisfy the non-null constraint as the judgment conditions, and taking the value of the subquery expression and the null value as the two return values ​​corresponding to the two judgment results.

[0093] In one example, the first conditional control statement above can be constructed based on the `case_when_then_else` function. Specifically, it can be expressed as: `case when(not_null_expr is not null)then(select_expr)else(null)end`. Here, `not_null_expr` is the attribute column in the second data table that satisfies the non-null constraint, and `select_expr` is the subquery expression.

[0094] In another example, the first conditional control statement mentioned above can be constructed based on a question mark expression. Specifically, it can be represented as: (not_null_expr is not null)? (select_expr):(null).

[0095] It should be noted that after performing the above equivalent rewriting steps, the subquery statement can be eliminated, and the second target SQL statement after equivalent rewriting is used to query based on the outer join result set of the two data tables. That is, only the outer join needs to be established for the two data tables. This allows the query optimizer to flexibly choose different join algorithms, such as hash join or merge join, and both of these algorithms can greatly reduce the number of scans on the second data table.

[0096] For example, by equivalently rewriting the aforementioned Q2, we can obtain Q2':

[0097] SELECT T1.C1,T1.C2,T1.C3 FROMT1 LEFT JOIN T2 ON T1.C1=T2.C1 ANDT2.C3=1 WHERE(CASE WHEN T2.C1 ISNOT NULL THEN NVL(T2.C2,0)ELSE NULL END)ISNULL;

[0098] Specifically, Q2' is obtained by performing the following equivalent rewrite on Q2:

[0099] Add T2 as the right table in the outer join to the FROM clause of the main query. Add the query condition from the WHERE clause of the subquery, T1.C1 = T2.C1 AND T2.C3 = 1, to the ON clause of the FROM clause of the main query as the join condition for the outer join. Replace the subquery with a CASE WHEN THEN Else statement.

[0100] In the case_when_then_else statement, not_null_expr is T2.C1, and select_expr is a subquery expression.

[0101] It's important to note that T2.C1 is used as the `not_null_expr` in the `case_when_then_else` statement because the outer join condition includes: T1.C1 = T2.C1. Therefore, when T1 and T2 are successfully joined (i.e., there are matching rows in T1 and T2), the value of T1.C1 will definitely not be NULL. Only when the join fails will the value of T1.C1 become NULL because the outer join is not empty. Therefore, we can determine whether T1 and T2 are successfully joined based on T1.C1.

[0102] The following explanation, in conjunction with Q2 and Q2', clarifies the correctness of the equivalent rewrite of the second target SQL statement.

[0103] When performing a data query based on Q2, for a data row r in T1, if the result of the subquery driven by r is a single row, then there must be a data row in T2 that matches r, and T2.C1 must not be NULL. If T2.C1 is not NULL, the case_when_then_else statement in Q2' outputs T2.C2, which is the same as the result of the subquery in Q2.

[0104] When performing a data query based on Q2, for a data row r in T1, if the result of the subquery driving the subquery is zero rows (i.e., empty), then there will definitely be no data row in T2 that can match r. The outer join will fill in the empty rows in T2.C1, and the case_when_then_else statement in Q2' will output NULL, which is the same as the result of the subquery statement in Q2.

[0105] In summary, the second target SQL statement is equivalent to the original SQL statement.

[0106] The following explains the rationale for rewriting the original SQL statement into the first target SQL statement when the target query condition has the property of rejecting null values, and for rewriting the original SQL statement into the second target SQL statement when it does not have the property of rejecting null values.

[0107] It should be understood that if the target query condition that depends on the subquery result has a null rejection property, then when the subquery result has zero rows, the target query condition will necessarily not be met, and thus the main query statement can filter the current query rows. This means that when a data row in T1 cannot match any data row in T2, that data row can be directly filtered out, and therefore can be equivalently rewritten as an SQL statement that queries based on the inner join result set.

[0108] If the target query condition that depends on the subquery result does not have the property of rejecting null values, then when the subquery result is zero rows, the target query condition may be satisfied. Therefore, it is incorrect to directly filter out the current row. Thus, it needs to be rewritten as an equivalent SQL statement that queries based on the outer join result set.

[0109] Regarding Q2, for a data row r in T1, if no data row in T2 satisfies: T1.C1 = T2.C1 AND T2.C3 = 1, then the subquery result is NULL. Consequently, the ISNULL condition is met, and data row r will not be filtered. In this case, if Q2 is rewritten as an SQL statement querying based on an inner join result set, data row r will be filtered because it cannot match any data row in T2. ​​Therefore, this rewrite is incorrect, and Q2 needs to be rewritten as an SQL statement querying based on an outer join result set.

[0110] In summary, the embodiments of this specification, for SQL statements containing unique subqueries, can determine whether to rewrite the original SQL statement into a first target SQL statement based on an inner join result set or a second target SQL statement based on an outer join result set by judging whether the target query condition has a null rejection property. For the first / second target SQL statement, the query optimizer can flexibly choose the join order and join algorithm when formulating the query plan, thus expanding the choice of query plan and helping to formulate a more efficient query plan.

[0111] The above explains the equivalent rewriting method for SQL statements containing unique subqueries. The following explains the equivalent rewriting method for SQL statements containing non-unique subqueries. Essentially, the latter adds an error reporting function compared to the former. That is, when the result of a subquery exceeds one row, an error message is output. This is because when the subquery result exceeds one row, the value of the expression corresponding to the target query condition cannot be determined, hence the need for an error report.

[0112] To implement the above error reporting function, this manual defines a custom aggregate function: scalar(val) (hereinafter referred to as the custom function) and an expression: read_scalar(val) (hereinafter referred to as the custom expression).

[0113] Regarding `scalar(val)`, its input variable `val` is the subquery expression in the `SELECT` clause of the subquery statement; it is used to output an indicator value indicating whether the subquery result is abnormal based on the number of rows. Specifically, if zero rows are input, NULL is output; if one row is input, the value of the subquery expression is output; if more than one row is input, an indicator value indicating an abnormality is output, such as the special flag `MUL_VAL`. It should be understood that both NULL and the value of the subquery expression are indicators indicating normal operation.

[0114] Regarding `read_scalar(val)`, its input variable `val` is the subquery statement. It is used to output an error message when the result of the subquery statement is an indicator value indicating an anomaly. For example, this error message could be, "The subquery statement's result exceeds one row." It is also used to directly output the indicator value when the result of the subquery statement is an indicator value indicating normality.

[0115] Taking Q1 above as an example, after adding the custom function and custom expression, it can be as follows:

[0116] Q3: SELECT T1.C1,T1.C2,T1.C3 FROM T1 WHERE C2>read_scalar(SELECTscalar(T2.C2)FROM T2 WHERE T1.C1=T2.C1 AND T2.C3=1);

[0117] It should be understood that Q3 and Q1 are equivalent here, as will be explained in the following analysis.

[0118] If the subquery result of the subquery statement (original subquery statement) in Q1 is no more than 1 row, then scalar(T2.C2) in Q3 will output T2.C2 (i.e., the normal indicator value), and then read_scalar will directly output T2.C2, which is the same as the subquery result of the original subquery statement.

[0119] If the subquery result in Q1 exceeds one row, then scalar(T2.C2) in Q3 will output an exception indicator value, and read_scalar will then output an error message. Similarly, execution of Q1 will also result in an error.

[0120] The following provides a detailed explanation of the equivalent rewriting methods for SQL statements after adding user-defined functions and sub-defined expressions.

[0121] Figure 3 A flowchart illustrating a method for processing SQL statements according to another embodiment is shown. This method can be executed by any device, apparatus, platform, or cluster of devices with computing and processing capabilities. Figure 3 As shown, the method may include the following steps.

[0122] Step 302: Obtain the original SQL statement to be processed, which includes the main query statement and the subquery statement.

[0123] The main query statement here includes a SELECT clause, a FROM clause, and a WHERE clause. The subquery statement also includes a SELECT clause, a FROM clause, and a WHERE clause.

[0124] The FROM clause of the main query statement above includes the first data table, and the WHERE clause includes the target query condition that depends on the result of the subquery statement. This target query condition includes the user-defined expression `read_scalar(val)`, where `val` is the subquery statement.

[0125] The FROM clause of the above subquery statement includes a second data table, and the SELECT clause includes a user-defined function scalar(val), where val is the subquery expression in the SELECT clause of the subquery statement.

[0126] In one example, the original SQL statement could be Q3 as described above.

[0127] In another example, the original SQL statement above could be expressed as follows:

[0128] Q4: SELECT T1.C1,T1.C2,T1.C3 FROM T1 WHERE read_scalar(SELECT scalar(NVL(T2.C2,0))FROM T2 WHERE T1.C1=T2.C1 AND T2.C3=1)IS NULL;

[0129] It should be understood that Q4 here is equivalent to Q2 above, and will not be repeated here.

[0130] Since Q3 is equivalent to Q1 and Q4 is equivalent to Q2, both of them satisfy the preset rewrite conditions.

[0131] Step 304: Determine whether the target query condition supports a subquery result that is empty.

[0132] Similar to the method used in Q1, the target query condition in Q3 does not support null subquery results. Similarly, similar to the method used in Q2, the target query condition in Q4 supports null subquery results.

[0133] Step 306: Based on the judgment result, the original SQL statement is rewritten equivalently to obtain the target SQL statement. The target SQL statement is used to perform queries based on the inner join result set or outer join result set of the first and second data tables.

[0134] Specifically, if the judgment result indicates that the target query condition does not support the subquery result being empty, the original SQL statement is equivalently rewritten into the first target SQL statement, which is used to perform a query based on the inner join result set of the first data table and the second data table.

[0135] The specific steps for rewriting the above equivalent SQL statement into the first target SQL statement are as follows:

[0136] Add the second data table to the FROM clause of the main query statement;

[0137] Add the query conditions from the WHERE clause of the subquery to the WHERE clause of the main query.

[0138] Include user-defined functions and user-defined expressions in the main query statement.

[0139] In one implementation, including user-defined functions and user-defined expressions in the main query statement can include:

[0140] Add a grouping clause (GROUP BY clause) and its corresponding grouping filter clause (HAVING clause) to the main query statement, and set the grouping column in the grouping clause to the attribute column (e.g., primary key column) that satisfies the uniqueness constraint in the first data table;

[0141] Move the target query criteria to the grouping filter clause;

[0142] Replace the input variables of the custom expression with a custom function, and replace the subquery statement with the resulting intermediate expression.

[0143] Of course, in practical applications, other statements with the same function as the subquery statement can be used to replace the subquery statement in order to rewrite the original SQL statement into the first target SQL statement. This manual does not limit this.

[0144] It should be understood that the difference between the equivalent rewrite of an SQL statement containing a unique subquery and the equivalent rewrite of an SQL statement containing a non-unique subquery lies in the addition of user-defined functions and sub-defined expressions to the main query statement.

[0145] It should be noted that after performing the above equivalent rewriting steps, the subquery statement can be eliminated, and the first target SQL statement after equivalent rewriting is used to query based on the inner join result set of the two data tables. That is, only an inner join needs to be established for the two data tables. This allows the query optimizer to flexibly choose different join algorithms, such as hash join or merge join. Both of these algorithms can greatly reduce the number of scans on the second data table, thereby improving the efficiency of distributed queries.

[0146] Taking Q3 as an example, after equivalent rewriting, we can obtain the following Q3':

[0147] SELECT T1.C1,T1.C2,T1.C3 FROM T1,T2 WHERE T1.C1=T2.C1 AND T2.C3=1GROUP BY T1.PK HAVING T1.C2>read_scalar(scalar(T2.C2);

[0148] Specifically, Q3' is obtained by performing the following equivalent rewrite on Q3: adding T2 to the FROM clause of the main query statement, adding the query condition in the WHERE clause of the subquery statement: T1.C1=T2.C1 AND T2.C3=1 to the WHERE clause of the main query statement, and including the user-defined function: scalar(val) and the user-defined expression: read_scalar(val) in the main query statement.

[0149] The main query statement containing user-defined functions and expressions can include: adding a GROUP BY clause and a corresponding HAVING clause to the main query statement. The grouping column in the GROUP BY clause is the hidden primary key column PK in T1. The target query condition is moved to the HAVING clause, and the subquery statement is replaced with the intermediate expression: read_scalar(scalar(T2.C2)). This intermediate expression is constructed as follows: keeping the input variable of (scalar(T2.C2) unchanged, and replacing the input variable of read_scalar with it, thus obtaining: read_scalar(scalar(T2.C2)).

[0150] Here, after rewriting Q3 as equivalent to Q3', the query optimizer can flexibly choose the join order and join algorithm when formulating a query plan for it, which expands the choice of query plan and helps to formulate a more efficient query plan.

[0151] In step 306, if the judgment result indicates that the target query condition supports an empty subquery result, the original SQL statement is equivalently rewritten into a second target SQL statement for querying based on the outer join result set of the first and second data tables.

[0152] The specific steps for rewriting the above equivalent SQL statement into the second target SQL statement are as follows:

[0153] Add the second data table as the right table in the outer join to the FROM clause of the main query statement;

[0154] Add the query conditions in the WHERE clause of the subquery as the join conditions of the outer join to the FROM clause of the main query.

[0155] Include user-defined functions and user-defined expressions in the main query statement.

[0156] In one implementation, including user-defined functions and user-defined expressions in the main query statement can include:

[0157] Add a grouping clause and its corresponding grouping filter clause to the main query statement, and set the grouping column in the grouping clause to the attribute column that satisfies the uniqueness constraint in the first data table;

[0158] Move the target query criteria to the grouping filter clause;

[0159] Based on the attribute columns in the second data table that satisfy the non-null constraint, as well as the user-defined function and user-defined expression, construct a second conditional control statement and use it to replace the subquery statement.

[0160] It should be noted that, in order to achieve an equivalent rewrite of the original SQL statement, the second condition control statement here should meet the following conditions: when the first and second data tables are successfully joined, output the value of the subquery expression in the subquery statement; otherwise, output NULL.

[0161] In one example, the steps to construct the second conditional control statement are as follows: replace the input variables of the custom expression with a custom function to obtain an intermediate expression; construct the second conditional control statement by using the values ​​of the attribute columns that satisfy the non-null constraint as the judgment conditions, and using the value of the intermediate expression and the null value as the two return values ​​corresponding to the two judgment results.

[0162] In one example, the second conditional control statement mentioned above can be constructed based on the `case_when_then_else` function. Specifically, it can be expressed as: `case when(not_null_expr is not null) then read_scalar(scalar(select_expr) else(null) end`. Here, `not_null_expr` is the attribute column in the second data table that satisfies the non-null constraint, and `select_expr` is the subquery expression.

[0163] In another example, the second conditional control statement mentioned above can be constructed based on a question mark expression. Specifically, it can be represented as: (not_null_expr is not null)? read_scalar(scalar(select_expr):(null).

[0164] It should be noted that after performing the above equivalent rewriting steps, the subquery statement can be eliminated, and the second target SQL statement after equivalent rewriting is used to query based on the outer join result set of the two data tables. That is, only the outer join needs to be established for the two data tables. This allows the query optimizer to flexibly choose different join algorithms, such as hash join or merge join. Both of these algorithms can greatly reduce the number of scans on the second data table, thereby improving the efficiency of distributed queries.

[0165] For example, by equivalently rewriting the aforementioned Q4, we can obtain Q4':

[0166] SELECT T1.C1,T1.C2,T1.C3 FROM T1 LEFT JOIN T2 ON T1.C1=T2.C1 ANDT2.C3=1 GROUP BY T1.PK HAVING(CASE WHEN T2.C1 IS NOT NULL THEN read_scalar(scalar(NVL(T2.C2,0)))ELSE NULL END)ISNULL;

[0167] Specifically, Q4' is obtained by performing the following equivalent rewrite on Q4: adding T2 as the right table of the outer join to the FROM clause of the main query statement, adding the query condition in the WHERE clause of the subquery statement: T1.C1=T2.C1ANDT2.C3=1 to the ON clause of the FROM clause of the main query statement as the join condition of the outer join, and including the user-defined function: scalar(val) and the user-defined expression: read_scalar(val) in the main query statement.

[0168] In this context, including user-defined functions and expressions in the main query statement can involve: adding a GROUP BY clause and its corresponding HAVING clause to the main query statement. The grouping column in the GROUP BY clause is the hidden primary key column PK in T1. The target query condition is moved to the HAVING clause, and the subquery statement is replaced with a CASE WHEN THEN Else statement.

[0169] In the case_when_then_else statement, not_null_expr is T1.C1, and read_scalar(scalar(NVL(T2.C2,0))) is obtained by keeping the input variable of scalar(NVL(T2.C2,0) in the SELECT clause of the subquery unchanged and replacing the input variable of read_scalar with it.

[0170] The correctness of rewriting Q4 as equivalent to Q4' can be found in the explanation above regarding the correctness of rewriting Q2 as equivalent to Q2', and will not be repeated here. Furthermore, after rewriting Q4 as equivalent to Q4', the query optimizer can flexibly choose the join order and join algorithm when developing a query plan, which helps to create a more efficient query plan.

[0171] In summary, the embodiments in this specification, for SQL statements containing subqueries that do not possess uniqueness, can determine whether to equivalently rewrite the original SQL statement into a first target SQL statement based on an inner join result set or a second target SQL statement based on an outer join result set by judging whether the target query condition has a null rejection property (for an explanation of its rationality, please refer to the above explanation of the rationality of SQL statements containing unique subqueries). Furthermore, this solution, by adding custom functions and custom expressions to the SQL statement, can implement an error reporting mechanism when the function in the subquery result exceeds one row, thus ensuring compatibility with various scenarios.

[0172] In summary, this solution can perform equivalent rewriting of SQL statements containing subqueries of ordinary types, thereby enabling more SQL statements containing subqueries to be equivalently rewritten.

[0173] Corresponding to the above-described SQL statement processing method, one embodiment of this specification also provides an SQL statement processing apparatus, such as... Figure 4 As shown, the device may include:

[0174] The acquisition unit 402 is used to acquire the raw SQL statement to be processed, which includes a main query statement and a subquery statement. The main query source table clause of the main query statement includes a first data table, the subquery source table clause of the subquery statement includes a second data table, and the main query condition clause of the main query statement includes target query conditions that depend on the results of the subqueries.

[0175] Here, the target query condition is not a predefined type of query condition, which includes any of the following filter terms: IN, NOT IN, ANY, ALL, EXISTS, and NOT EXISTS.

[0176] Decision unit 404 is used to determine whether the target query condition supports a subquery result that is empty.

[0177] The rewriting unit 406 is used to rewrite the original SQL statement equivalently according to the judgment result to obtain the target SQL statement, which is used to query based on the inner join result set or outer join result set of the first and second data tables.

[0178] Rewrite unit 406 is specifically used for:

[0179] If the judgment result indicates that the target query condition does not support the subquery result being empty, then the original SQL statement is equivalently rewritten into the first target SQL statement, which is used to perform a query based on the inner join result set of the first and second data tables.

[0180] In one example, rewriting unit 406 may include:

[0181] Add module 4062 to add the second data table to the main query source table clause;

[0182] Add module 4062, which is also used to add the query conditions in the subquery condition clause of the subquery statement to the main query condition clause;

[0183] Replacement module 4064 is used to replace a subquery statement with a subquery expression in the subquery select clause of the subquery statement.

[0184] In another example, the subquery select clause of the subquery statement includes a custom function that outputs an indication value indicating whether the subquery result is abnormal based on the number of rows in the subquery result; the target query condition includes a custom expression that outputs an error message when the indication value indicates an abnormality.

[0185] Rewriting unit 406 may include:

[0186] Add module 4062 to add the second data table to the main query source table clause;

[0187] Add module 4062, which is also used to add the query conditions in the subquery condition clause of the subquery statement to the main query condition clause;

[0188] Add module 4062, which is also used to include the custom function and custom expression in the main query statement.

[0189] In this context, the input variable for a user-defined function is a subquery expression, and the input variable for a user-defined expression is a subquery statement.

[0190] Module 4062 is specifically used for:

[0191] Add a grouping clause and its corresponding grouping filter clause to the main query statement, and set the grouping column in the grouping clause to the attribute column that satisfies the uniqueness constraint in the first data table;

[0192] Move the target query criteria to the grouping filter clause;

[0193] Replace the input variables of the custom expression with a custom function, and replace the subquery statement with the resulting intermediate expression.

[0194] Rewriting unit 406 is also specifically used for:

[0195] If the judgment result indicates that the target query condition supports a null subquery result, then the original SQL statement is equivalently rewritten into a second target SQL statement, which is used to perform a query based on the outer join result set of the first and second data tables.

[0196] In one example, the rewriting unit 406 may include:

[0197] Add module 4062 to add the second data table as the right table in the outer join to the source table clause of the main query;

[0198] Add module 4062, which is also used to add the query conditions in the subquery condition clause of the subquery statement as the join conditions of the outer join to the main query source table clause;

[0199] Replacement module 4064 is used to construct a first condition control statement based on the attribute columns that satisfy the non-null constraint in the second data table and the subquery expression in the subquery selection clause of the subquery statement, and to replace the subquery statement with it.

[0200] The construction of the first conditional control statement may include:

[0201] The first condition control statement is constructed by using the values ​​of attribute columns that satisfy the non-null constraint as the judgment conditions, and using the value of the subquery expression and the null value as the two return values ​​corresponding to the two judgment results.

[0202] In another example, the subquery select clause of the subquery statement includes a custom function that outputs an indication value indicating whether an anomaly is present based on the number of rows in the subquery result, and the target query condition includes a custom expression that outputs an error message when the indication value indicates an anomaly.

[0203] Rewriting unit 406 may include:

[0204] Add module 4062 to add the second data table as the right table in the outer join to the source table clause of the main query;

[0205] Add module 4062, which is also used to add the query conditions in the subquery condition clause of the subquery statement as the join conditions of the outer join to the main query source table clause;

[0206] Add module 4062, which is also used to include custom functions and custom expressions in the main query statement.

[0207] The input variable of the user-defined function is the subquery expression, and the input variable of the user-defined expression is the subquery statement;

[0208] Module 4062 is specifically used for:

[0209] Add a grouping clause and its corresponding grouping filter clause to the main query statement, and set the grouping column in the grouping clause to the attribute column that satisfies the uniqueness constraint in the first data table;

[0210] Move the target query criteria to the grouping filter clause;

[0211] Based on the attribute columns in the second data table that satisfy the non-null constraint, as well as the user-defined function and user-defined expression, construct a second conditional control statement and use it to replace the subquery statement.

[0212] Constructing the second conditional control statement can include: replacing the input variables of a custom expression with a custom function to obtain an intermediate expression; and constructing the second conditional control statement by using the values ​​of attribute columns that satisfy the non-null constraint as the judgment conditions, and using the value of the intermediate expression and the null value as the two return values ​​corresponding to the two judgment results.

[0213] Optionally, the device may further include:

[0214] The sending unit (not shown in the figure) is used to send the target SQL statement to the query optimizer so that the query optimizer can formulate a target query plan based on the target SQL statement.

[0215] Optionally, the device is located in the query optimizer and further includes:

[0216] The formulation unit (not shown in the figure) is used to formulate the target query plan based on the target SQL statement.

[0217] The functions of each functional module of the apparatus in the above embodiments of this specification can be implemented through the steps of the above method embodiments. Therefore, the specific working process of the apparatus provided in one embodiment of this specification will not be repeated here.

[0218] The SQL statement processing apparatus provided in one embodiment of this specification can eliminate subqueries, which helps the query optimizer to develop a more efficient query plan.

[0219] According to another embodiment, a computer-readable storage medium is also provided, on which a computer program is stored, which, when executed in a computer, causes the computer to perform a combination Figure 2 or Figure 3 The method described in [the document / article].

[0220] According to another embodiment, a computing device is also provided, including a memory and a processor, wherein the memory stores executable code, and when the processor executes the executable code, it implements a combination... Figure 2 or Figure 3 The method described.

[0221] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0222] The steps of the methods or algorithms described in conjunction with the disclosure in this specification can be implemented in hardware or by a processor executing software instructions. The software instructions can consist of corresponding software modules, which can be stored in RAM, flash memory, ROM, EPROM, EEPROM, registers, hard disk, external hard disk, CD-ROM, or any other form of storage medium well known in the art. An exemplary storage medium is coupled to the processor, enabling the processor to read information from and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and storage medium can reside in an ASIC. Alternatively, the ASIC can reside in a server. Of course, the processor and storage medium can also exist as discrete components in the server.

[0223] Those skilled in the art will recognize that, in one or more of the examples above, the functions described in this invention can be implemented using hardware, software, firmware, or any combination thereof. When implemented in software, these functions can be stored in a computer-readable medium or transmitted as one or more instructions or code on a computer-readable medium. Computer-readable media include computer storage media and communication media, wherein communication media include any medium that facilitates the transfer of a computer program from one place to another. Storage media can be any available medium accessible to a general-purpose or special-purpose computer.

[0224] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0225] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of this specification. It should be understood that the above description is only a specific embodiment of this specification and is not intended to limit the scope of protection of this specification. Any modifications, equivalent substitutions, improvements, etc., made on the basis of the technical solution of this specification should be included within the scope of protection of this specification.

Claims

1. A method for processing SQL statements, comprising: Obtain the raw SQL statement to be processed, including the main query statement and the subquery statement; The main query source table clause of the main query statement includes a first data table, and the subquery source table clause of the subquery statement includes a second data table; the main query condition clause of the main query statement includes a target query condition that depends on the subquery result of the subquery statement. Determine whether the target query condition supports the subquery result being empty; If the judgment result indicates that the target query condition does not support the subquery result being empty, then the original SQL statement is equivalently rewritten as the first target SQL statement, which is used to perform a query based on the inner join result set of the first and second data tables.

2. The method according to claim 1, wherein, The step of equivalently rewriting the original SQL statement into the first target SQL statement includes: Add the second data table to the main query source table clause; Add the query conditions from the subquery condition clause of the subquery statement to the main query condition clause; Replace the subquery statement with the subquery expression in the subquery select clause of the subquery statement.

3. The method according to claim 1, wherein, The subquery select clause of the subquery statement includes a user-defined function, which is used to output an indication value indicating whether the result of the subquery is abnormal based on the number of rows in the result of the subquery; the target query condition includes a user-defined expression, which is used to output an error message when the indication value indicates an abnormality. The step of equivalently rewriting the original SQL statement into the first target SQL statement includes: Add the second data table to the main query source table clause; Add the query conditions from the subquery condition clause of the subquery statement to the main query condition clause; The main query statement includes the custom function and the custom expression.

4. The method according to claim 3, wherein, The input variable of the custom function is the subquery expression; the input variable of the custom expression is the subquery statement. The main query statement includes the user-defined function and user-defined expression, including: Add a grouping clause and its corresponding grouping filter clause to the main query statement, and set the grouping column in the grouping clause to the attribute column in the first data table that satisfies the uniqueness constraint; Move the target query condition to the grouping filter clause; Replace the input variables of the custom expression with the custom function, and replace the subquery statement with the resulting intermediate expression.

5. The method according to claim 1, further comprising: If the judgment result indicates that the target query condition supports the subquery result being empty, then the original SQL statement is equivalently rewritten into a second target SQL statement for querying based on the outer join result set of the first and second data tables.

6. The method according to claim 5, wherein, The step of equivalently rewriting the original SQL statement into a second target SQL statement includes: Add the second data table as the right table in the outer join to the main query source table clause; The query conditions in the subquery condition clause of the subquery statement are added as the join conditions of the outer join to the main query source table clause; Based on the attribute columns in the second data table that satisfy the non-null constraint, and the subquery expression in the subquery select clause of the subquery statement, a first condition control statement is constructed and used to replace the subquery statement.

7. The method according to claim 6, wherein, The construction of the first conditional control statement includes: The first condition control statement is constructed by using the values ​​of the attribute columns that satisfy the non-null constraint as the judgment conditions, and using the value of the subquery expression and the null value as the two return values ​​corresponding to the two judgment results.

8. The method according to claim 5, wherein, The subquery select clause of the subquery statement includes a user-defined function, which is used to output an indication value indicating whether the result of the subquery is abnormal based on the number of rows in the result of the subquery; the target query condition includes a user-defined expression, which is used to output an error message when the indication value indicates an abnormality. The step of equivalently rewriting the original SQL statement into a second target SQL statement includes: Add the second data table as the right table in the outer join to the main query source table clause; The query conditions in the subquery condition clause of the subquery statement are added as the join conditions of the outer join to the main query source table clause; The main query statement includes the custom function and the custom expression.

9. The method according to claim 8, wherein, The input variable of the custom function is the subquery expression; the input variable of the custom expression is the subquery statement. The main query statement includes the user-defined function and user-defined expression, including: Add a grouping clause and its corresponding grouping filter clause to the main query statement, and set the grouping column in the grouping clause to the attribute column in the first data table that satisfies the uniqueness constraint; Move the target query condition to the grouping filter clause; Based on the attribute columns in the second data table that satisfy the non-null constraint, as well as the custom function and custom expression, a second conditional control statement is constructed and used to replace the subquery statement.

10. The method according to claim 9, wherein, The construction of the second conditional control statement includes: Replace the input variables of the custom expression with the custom function to obtain the intermediate expression; The second condition control statement is constructed by using the values ​​of the attribute columns that satisfy the non-null constraint as the judgment conditions, and using the value of the intermediate expression and the null value as the two return values ​​corresponding to the two judgment results.

11. The method according to claim 1, further comprising: The target SQL statement is sent to the query optimizer so that the query optimizer can formulate a target query plan based on the target SQL statement.

12. The method according to claim 1, wherein, The method is executed by a query optimizer; the method further includes: Based on the target SQL statement, formulate a target query plan.

13. The method according to claim 1, wherein, The target query condition does not belong to the predefined type of query condition; the predefined type of query condition includes any of the following filter terms: IN, NOT IN, ANY, ALL, EXISTS, and NOTEXISTS.

14. An apparatus for processing SQL statements, comprising: The acquisition unit is used to acquire the raw SQL statement to be processed, including the main query statement and the subquery statement; The main query source table clause of the main query statement includes a first data table, and the subquery source table clause of the subquery statement includes a second data table; the main query condition clause of the main query statement includes a target query condition that depends on the subquery result of the subquery statement. The judgment unit is used to determine whether the target query condition supports the subquery result being empty; The rewriting unit is configured to rewrite the original SQL statement into a first target SQL statement if the judgment result indicates that the target query condition does not support the subquery result being empty, for querying based on the inner join result set of the first and second data tables.

15. The apparatus according to claim 14, wherein, The rewriting unit includes: An add module is used to add the second data table to the main query source table clause; The adding module is also used to add the query conditions in the sub-query condition clause of the sub-query statement to the main query condition clause; The replacement module is used to replace the subquery statement with a subquery expression in the subquery select clause of the subquery statement.

16. The apparatus according to claim 14, wherein, The subquery select clause of the subquery statement includes a user-defined function, which is used to output an indication value indicating whether the result of the subquery is abnormal based on the number of rows in the subquery result. The target query conditions include a custom expression, which is used to output an error message when the indicator value indicates an anomaly. The rewriting unit includes: An add module is used to add the second data table to the main query source table clause; The adding module is also used to add the query conditions in the sub-query condition clause of the sub-query statement to the main query condition clause; The adding module is also used to include the custom function and custom expression in the main query statement.

17. The apparatus according to claim 16, wherein, The input variable of the custom function is the subquery expression; the input variable of the custom expression is the subquery statement. The added module is specifically used for: Add a grouping clause and its corresponding grouping filter clause to the main query statement, and set the grouping column in the grouping clause to the attribute column in the first data table that satisfies the uniqueness constraint; Move the target query condition to the grouping filter clause; Replace the input variables of the custom expression with the custom function, and replace the subquery statement with the resulting intermediate expression.

18. The apparatus according to claim 14, wherein, The rewriting unit is specifically used for: If the judgment result indicates that the target query condition supports the subquery result being empty, then the original SQL statement is equivalently rewritten into a second target SQL statement for querying based on the outer join result set of the first and second data tables.

19. The apparatus according to claim 18, wherein, The rewriting unit includes: An add module is used to add the second data table as the right table in the outer join to the main query source table clause; The adding module is also used to add the query conditions in the subquery condition clause of the subquery statement as the join conditions of the outer join to the main query source table clause; The replacement module is used to construct a first condition control statement based on the attribute columns in the second data table that satisfy the non-null constraint and the subquery expression in the subquery selection clause of the subquery statement, and to replace the subquery statement with the first condition control statement.

20. The apparatus according to claim 18, wherein, The subquery select clause of the subquery statement includes a user-defined function, which is used to output an indication value indicating whether the result of the subquery is abnormal based on the number of rows in the subquery result. The target query conditions include a custom expression, which is used to output an error message when the indicator value indicates an anomaly. The rewriting unit includes: An add module is used to add the second data table as the right table in the outer join to the main query source table clause; The adding module is also used to add the query conditions in the subquery condition clause of the subquery statement as the join conditions of the outer join to the main query source table clause; The adding module is also used to include the custom function and custom expression in the main query statement.

21. The apparatus according to claim 20, wherein, The input variable of the custom function is the subquery expression; the input variable of the custom expression is the subquery statement. The added module is specifically used for: Add a grouping clause and its corresponding grouping filter clause to the main query statement, and set the grouping column in the grouping clause to the attribute column in the first data table that satisfies the uniqueness constraint; Move the target query condition to the grouping filter clause; Based on the attribute columns in the second data table that satisfy the non-null constraint, as well as the custom function and custom expression, a second conditional control statement is constructed and used to replace the subquery statement.

22. A computer-readable storage medium having a computer program stored thereon, wherein, When the computer program is executed in a computer, it causes the computer to perform the method according to any one of claims 1-13.

23. A computing device comprising a memory and a processor, wherein, The memory stores executable code, and when the processor executes the executable code, it implements the method of any one of claims 1-13.

Images