Method and system for automated proof of sql equivalence based on linear integer algebra

Abstract

The application provides a method and system for automatic proof of SQL equivalence based on linear integer algebra, comprising: a plan generation step of parsing an input SQL query statement to generate an original logical plan; an ORDER BY processing step of processing an ORDER BY sorting operation in the original logical plan to generate a new logical plan without the ORDER BY sorting operation, which is submitted to a subsequent step for equivalence verification; a U-expression generation step of parsing the logical plan to generate a corresponding algebraic expression U-expression; a normalization step of normalizing and simplifying the U-expression to generate a standard form U-expression; a LIA * conversion step of generating a LIA * expression according to the standard form U-expression; a first-order logic expression generation step of generating a LIA expression according to the LIA * expression, and performing automatic verification of SQL equivalence by means of an SMT solver. The application perfects modeling of SQL features such as an aggregation function, and significantly enhances the capability of automatic verification of SQL equivalence.

Description

Technical Field

[0001] This invention relates to the field of SQL equivalence proof technology, and more specifically, to a method and system for automated SQL equivalence proof based on linear integer algebra. Background Technology

[0002] Relational databases are frequently used in internet applications to store and manage data, typically employing Structured Query Language (SQL) for CRUD operations. Proving the equivalence of two SQL queries is a fundamental problem in the database field. Such SQL equivalence proofs can be used in many important scenarios, such as proving or disproving existing query rewriting rules, or discovering new rewriting rules. Therefore, database developers have always sought more efficient methods for proving SQL equivalence.

[0003] Existing methods for proving SQL equivalence mainly fall into two categories: SQL syntax-based methods and SQL semantic-based methods. SQL syntax-based methods determine equivalence by verifying the isomorphism of the normalized algebraic representations of two SQL queries. Representative SQL syntax-based proof tools include UDP and SPES. UDP transforms each SQL query into a U-semiring expression (U-expression). Specifically, the U-expression represents the SQL query as an algebraic expression f(t), which returns the number of tuples t in the query result. UDP then verifies the isomorphism of the U-expressions corresponding to the two SQL queries based on syntax, thus determining whether the two SQL queries are equivalent. SPES, on the other hand, transforms the SQL query into a tree-structured algebraic representation. SPES then calls a Satisfiability Modulo Theories (SMT) solver to verify the equivalence of the predicates between the two SQL queries. Finally, it verifies the isomorphism of the tree-structured algebraic expressions corresponding to the two SQL queries based on syntax, thus determining whether the SQL queries are equivalent. Compared to UDP, SPES enhances support for specific predicates by calling the SMT solver to verify the equivalence of different predicates in SQL. However, this proof method based on SQL syntax struggles to prove the equivalence of two SQL queries with significantly different syntactic structures. For example, consider the following two SQL query statements:

[0004] Q1:(select x from R)union(select x from R)

[0005] Q2:select distinct x from R where exists(select x from R)

[0006] Neither UDP nor SPES can verify the equivalence of the two SQL statements above because the first SQL query lacks EXISTS, while the second SQL query lacks UNION; these two SQL statements are completely different in syntax. However, these two SQL statements are semantically equivalent and return the same final result. Therefore, methods for proving SQL equivalence based on syntax lack the ability to prove SQL statements with different syntax but the same semantics.

[0007] Existing SQL semantics-based proof methods remain very limited in terms of the types of queries they can handle. For example, WeTune, a recent tool for proving SQL equivalence based on SQL semantics, uses U-expressions to model SQL statements under package semantics, then directly converts the U-expressions into first-order logical expressions and calls the SMT solver for verification. However, when modeling certain basic SQL features (such as aggregate functions), the algebraic expression of the query ultimately contains the sum of all possible tuples, also known as unbounded summation. WeTune lacks a method to convert unbounded summation into first-order logical expressions and can only perform heuristic verification in certain specific cases, such as when the U-expressions corresponding to two SQL statements each contain only one unbounded summation. However, WeTune struggles to handle unbounded summations with other syntactic structures within the U-expression.

[0008] Currently, there is no method to prove SQL equivalence that can transform an unbounded summation of an arbitrary syntactic structure into a first-order logical expression, and methods that express SQL semantics based on U-expressions struggle to handle statements containing ORDER BY clauses. Therefore, previous work has certain limitations.

[0009] The Linear Integer Algebra (LIA) formula is a first-order logic formula and one of the mathematical tools used in this invention. It is defined as follows:

[0010]

[0011] A: = T1≤T2|T1=T2

[0012] T:=x|c|T1+T2|c×T1|ite(F,T1,T2)

[0013] Here, T represents an expression that produces an integer result, x represents an integer variable, and c represents an integer constant. Because T is linear, there cannot be multiplication of variables within T. The expression ite(F,T1,T2) means that if expression F is true, it returns T1; otherwise, it returns T2. A represents a first-order logic formula with predicates "≤" and "=". A LIA formula can be a single-atom first-order logic formula or a LIA formula connected by multiple logical connectives.

[0014] LIA * The formula is an extension of the LIA formula and is one of the mathematical tools used in this invention. Furthermore, each LIA... * All formulas have a satisfiability equivalent LIA formula. Therefore, for LIA... * The reasoning behind the formula can be simplified to reasoning about the LIA formula. In the formal definition of LIA... * Before the formula, we need to introduce the addition closure operator *. It is an operator defined on the set of integer vectors, as shown below:

[0015]

[0016] Where S is a set of integer vectors, and It is an integer vector, S * Let S be the set of any linear combinations of any number of elements, where n is a non-negative integer that can take any value, and λ is the set of elements in S. i And i is an integer. LIA * The standard form of the formula is as follows, where Let F1 and F2 be integer vectors, and F2 be LIA formulas:

[0017]

[0018] The additive closure operator can model the sum of any number of vectors, also known as unbounded summation. LIA * The theory provides a method for reasoning formulas with unbounded summations. A LIA expression is a logical expression about integers that can be directly solved using existing SMT solvers. * The expression extends the LIA expression to represent the summation of any number of integer vectors, and each LIA... * All formulas have a mutually satisfyable LIA formula. Therefore, for LIA... * The reasoning for the formula can be simplified to reasoning for the LIA formula that does not include unbounded summation. Specifically, existing work has proven that for every LIA... * All formulas have a satisfiability equivalent LIA formula, according to the following theorem: Given a LIA * formula There exists an integer k such that the LIA * The formula is equivalent to the following formula in terms of satisfiability.

[0019]

[0020] Existing work has designed an algorithm that can automatically assign a given LIA * The formula is transformed into another LIA formula that is equivalent to its satisfiability. Summary of the Invention

[0021] To address the shortcomings of existing technologies, this invention provides a method and system for automated proof of SQL equivalence based on linear integer algebra.

[0022] According to the present invention, a method and system for automated proof of SQL equivalence based on linear integer algebra are provided, the scheme of which is as follows:

[0023] Firstly, a method for automated proof of SQL equivalence based on linear integer algebra is provided, the method comprising:

[0024] Plan generation steps: Parse the input SQL query statement to generate the original logical plan, which is a preprocessing step for conversion into the corresponding U-expression; each SQL statement corresponds to one original logical plan;

[0025] ORDER BY processing steps: Process the ORDER BY sorting operation in the original logical plan, generate a new logical plan without ORDER BY sorting operation, and hand it over to the subsequent steps for equivalence verification;

[0026] U-expression generation steps: parse the logical plan and generate the corresponding U-expression;

[0027] Normalization steps: Normalize and simplify the U-expression to generate a standard form of U-expression;

[0028] LIA * Conversion steps: Generate LIA based on the standard form of U-expression. * expression;

[0029] Steps for generating a first-order logic expression: Based on LIA * The expression generates a first-order logical expression, which is then used to perform SQL equivalence verification.

[0030] Preferably, the ORDER BY processing step includes: for any pair of logical plans, deleting and merging the ORDER BY operations in them according to predefined rules, and then generating a new logical plan without ORDER BY operations, ensuring that if the new logical plan is equivalent, then the original SQL query statement is also equivalent.

[0031] Preferably, the U-expression generation step includes: generating a corresponding U-expression for any logical plan, specifically in the form of a function f(x) representing the number of tuples x in the query result; the algebraic expression includes unbounded summation, representing relevant SQL features including aggregate functions.

[0032] Preferably, the LIA * The transformation steps include: generating the corresponding LIA for any pair of U-expressions. * An expression, specifically a logical expression about integers, is required. If this logical expression is not satisfied, then the two SQL queries are equivalent. (LIA) * The expression contains an additive closure, which corresponds to unbounded summation in U-expression.

[0033] Preferably, the first-order logic expression generation step includes: for any LIA * The expression generates a corresponding first-order logical expression, which is a proposition. If the proposition is not satisfiable, then the two SQL query statements are equivalent. The satisfiability of the proposition is determined by the SMT solver.

[0034] Secondly, a system for automated SQL equivalence proof based on linear integer algebra is provided, the system comprising:

[0035] Logical plan generation module: Converts query statements into corresponding logical plans;

[0036] ORDER BY processing module: processes ORDER BY clauses in the logical plan, generates a new plan without ORDER BY clauses, and calls other modules to verify the equivalence of the new plan;

[0037] U-expression generation module: Converts logical plans into corresponding U-expressions;

[0038] LIA * Validation module: Converts U-expression into corresponding LIA. * Expression, and LIA * The expression is further transformed into a corresponding first-order logical expression for SQL equivalence verification.

[0039] Preferably, the ORDER BY processing module includes:

[0040] The ORDER BY operation delete and merge submodule deletes and merges ORDER BY operations in the logical plan according to a series of predefined rules.

[0041] New plan generation submodule: Generates a new logical plan that does not contain ORDER BY.

[0042] Preferably, the U-expression expression generation module includes:

[0043] The raw U-expression generation submodule converts the logical plan of the query statement into the corresponding unnormalized raw U-expression expression;

[0044] The normalization submodule normalizes the original U-expression.

[0045] Preferably, the normalization submodule includes:

[0046] Ordinary regularization method unit: It rewrites the U-expression into a regular form without using the database's integrity constraint information for regularization;

[0047] Constraint-based normalization method unit: Normalizes the U-expression based on the integrity constraint information in the database, rewriting it into a normal form.

[0048] Preferably, the LIA * The verification module includes:

[0049] LIA * Expression generation submodule: Generates LIA based on two U-expressions * An expression such that if the generated LIA* expression is not satisfied, then the two U-expressions are equivalent;

[0050] First-order logic expression generation submodule: If the generated expression is LIA * If the expression is not satisfied, then LIA is used to convert it into a first-order logic expression. * The expression is not satisfied;

[0051] Verification submodule: Uses the SMT solver to check the satisfiability of the generated first-order logic expression to provide equivalence results for the input SQL query statement.

[0052] Compared with the prior art, the present invention has the following beneficial effects:

[0053] 1. This invention proposes and implements the conversion of U-expressions containing unbounded summation with arbitrary syntactic structures into LIA. * Formula, then LIA * The method of transforming formulas into first-order logic formulas enhances the ability to prove SQL equivalence.

[0054] 2. This invention processes the ORDER BY clause using a divide-and-conquer approach, making it the first validator to support the equivalence of queries containing ORDER BY. By verifying the equivalence of clauses without ORDER BY, it further proves the equivalence of SQL queries containing ORDER BY, improving the modeling of SQL features such as aggregate functions and enhancing the ability to automatically verify SQL equivalence.

[0055] 3. This invention is the first to support the conversion of query statements containing certain common SQL features into U-expressions, including aggregate functions, intersect, intersect all, values, and scalar subqueries.

[0056] Other beneficial effects of the present invention will be explained in detail through the introduction of specific technical features and technical solutions in specific embodiments. Those skilled in the art should be able to understand the beneficial technical effects brought about by these technical features and technical solutions through the introduction of these technical features and technical solutions. Attached Figure Description

[0057] Other features, objects, and advantages of the present invention will become more apparent from the following detailed description of non-limiting embodiments with reference to the accompanying drawings:

[0058] Figure 1 This is a schematic diagram of a process of the present invention. Detailed Implementation

[0059] The present invention will now be described in detail with reference to specific embodiments. These embodiments will help those skilled in the art to further understand the present invention, but do not limit the invention in any way. It should be noted that those skilled in the art can make several changes and improvements without departing from the concept of the present invention. These all fall within the protection scope of the present invention.

[0060] This invention provides a method for automated proof of SQL equivalence based on linear integer algebra. It translates SQL into a mathematical expression (U-expression) to accurately represent the semantics of the SQL statement, and then transforms the U-expression into another algebraic expression (LIA). * Expression, and LIA * The expression is transformed into a first-order logic LIA expression, enabling automated solving of LIA expressions using the SMT solver. Input two valid SQL queries; output whether these two SQL queries are equivalent, i.e., whether executing these two SQL statements on any database always returns the same result. (See reference...) Figure 1 As shown, the specific steps of this method include:

[0061] Plan generation steps: Parse the input SQL query statement to generate the original logical plan, which is a preprocessing step for conversion into the corresponding U-expression; each SQL statement corresponds to an original logical plan, which is a representation of the SQL query operation flow and contains a series of operators.

[0062] ORDER BY processing steps: The original logical plan is processed to handle sorting operations such as ORDER BY, generating a new logical plan without such operations. This new logical plan is then submitted for equivalence verification in subsequent steps. For any pair of logical plans, ORDER BY operations are deleted and merged according to predefined rules, further generating a new logical plan without ORDER BY operations. This ensures that if the new logical plan is equivalent, then the original SQL query statement is also equivalent.

[0063] The U-expression generation steps are as follows: The logical plan is parsed to generate the corresponding U-expression. For any logical plan, a corresponding algebraic expression U-expression is generated, specifically in the form of a function f(x) representing the number of tuples x in the query result; this algebraic expression uses unbounded summation to represent relevant SQL features, including aggregate functions.

[0064] Normalization steps: Normalize and simplify the U-expression to generate a standard form of U-expression.

[0065] LIA * Conversion steps: Generate LIA based on the standard form of U-expression. * (Linear Integer Arithmetic Star) expression. For any pair of U-expressions, generate the corresponding LIA.* An expression, specifically a logical expression about integers, can be used to deduce the equivalence of two SQL queries if this logical expression is not satisfied; LIA * The expression contains an additive closure, which corresponds to unbounded summation in U-expression.

[0066] Steps for generating a first-order logic expression: Based on LIA * The expression generates a LIA (Linear Integer Arithmetic) expression. Because LIA expressions are first-order logical expressions, they can be used with an SMT solver for automated SQL equivalence verification. For any LIA... * The expression generates a corresponding first-order logical expression, specifically in the form of a proposition. If the proposition is not satisfied, then the two input SQL query statements are equivalent.

[0067] LIA * The satisfiability problem of an expression is transformed into the satisfiability problem of a LIA expression. If a LIA expression is not satisfiable, then LIA... * If the expression is unsatisfiable, then the SMT solver is used to determine the satisfiability of the LIA expression.

[0068] This invention also provides a system for automated SQL equivalence proof based on linear integer algebra. This system can be implemented by executing the steps of the method for automated SQL equivalence proof based on linear integer algebra. That is, those skilled in the art can understand the method for automated SQL equivalence proof based on linear integer algebra as a preferred embodiment of the system. Specifically, the system includes:

[0069] Logical plan generation module: Converts query statements into corresponding logical plans;

[0070] The ORDER BY processing module processes the ORDER BY clause in the logical plan, generates a new plan without ORDER BY clauses, and calls other modules to verify the equivalence of the new plan.

[0071] The ORDER BY processing module includes: an ORDER BY operation deletion and merging submodule: which deletes and merges ORDER BY operations in the logical plan according to a series of predefined rules; and a new plan generation submodule: which generates a new logical plan that does not contain ORDER BY.

[0072] U-expression generation module: Converts logical plans into corresponding U-expressions.

[0073] The U-expression generation module includes: a raw U-expression generation submodule, which converts the logical plan of the query statement into the corresponding unnormalized raw U-expression expression; and a normalization submodule, which normalizes the raw U-expression expression. The normalization submodule includes:

[0074] Ordinary regularization method unit: rewrites the U-expression into a regular form without using integrity constraint information; Constraint regularization method unit: rewrites the U-expression into a regular form based on integrity constraint information.

[0075] LIA * Validation module: Generate LIA based on U-expression * Expression, and LIA * The expression is further transformed into a corresponding first-order logical expression for SQL equivalence verification.

[0076] The LIA * The verification module includes:

[0077] LIA * Expression generation submodule: Generates LIA based on two U-expressions * The expression, such that if LIA * If an expression is not satisfied, then two U-expressions are equivalent.

[0078] First-order logic expression generation submodule: based on LIA * The expression generates a first-order logic expression such that if the first-order logic expression is not satisfied, then LIA... * The expression is not satisfied;

[0079] Verification submodule: Uses the SMT solver to check the satisfiability of the generated first-order logic expression. If it is not satisfiable, the input SQL query statement is deemed equivalent; otherwise, it is deemed not equivalent.

[0080] The present invention will now be described in more detail.

[0081] This invention provides a method for automated proof of SQL equivalence based on linear integer algebra, the method comprising:

[0082] 1) Use the logical plan generation module to convert the query statement into the corresponding logical plan.

[0083] 2) The ORDER BY clause in the logical plan is processed by the ORDER BY processing module to generate a new plan without ORDER BY clause, and then other modules are called to verify the equivalence of the new plan.

[0084] 3) The logical plan is converted into an algebraic expression U-expression through the U-expression expression generation module.

[0085] 4) Through LIA * The verification module converts the U-expression into the corresponding LIA. * Expression, and LIA * The expression is further transformed into a corresponding first-order logical expression for SQL equivalence verification.

[0086] The ORDER BY processing module uses a series of predefined rules to delete and merge ORDER BY operations in the logical plan, and then generates two new logical plans that do not contain ORDER BY operations. This ensures that if the new logical plans are equivalent, then the original two SQL statements are also equivalent.

[0087] Each logical plan is converted into a U-expression using a predefined series of transformation rules. If two U-expressions are equivalent, then the original two SQL statements are equivalent. In addition to supporting the SQL features already supported by existing work, it also supports aggregate functions, intersect, intersect all, values, and scalar subqueries.

[0088] This invention proposes to transform the equivalence problem of any two U-expressions into a single LIA. * The method for solving the satisfiability problem of expressions, if LIA * If an expression is not satisfied, then two U-expressions are equivalent.

[0089] LIA * The satisfiability problem of an expression is transformed into the satisfiability problem of a LIA expression. If a LIA expression is not satisfiable, then LIA... * The expression is unsatisfiable. Then, the satisfiability of the LIA expression is determined using an existing SMT solver.

[0090] The complete implementation process of this invention is described below:

[0091] Plan generation steps: Parse the SQL to generate a logical plan.

[0092] ORDER BY processing steps: Parse a pair of logical plans P1 and P2. First, eliminate and merge the ORDER BY clauses according to predefined rules to generate logical plans P1' and P2'. Then, compare the structures of the two logical plans, extract the new logical plan without ORDER BY clauses, and check their equivalence in subsequent steps. If each pair of new logical plans is equivalent, then the original SQL query is equivalent.

[0093] Specifically, this invention eliminates and merges ORDER BY in the logical plan according to the following rules:

[0094] 1. ORDER BY operators that do not contain LIMIT and OFFSET operations in the sub-logic plan can be eliminated.

[0095] 2. When the logical plan corresponds to an SQL query statement of the following form:

[0096] (R1 order by expression limit number_rows offset offset_value)

[0097] [union all|full join|left join]R2

[0098] order by expression limit number_rows offset offset_value

[0099] The logical plan for rewriting the SQL query as follows:

[0100] R1[union all|full join|left join]R2

[0101] order by expression limit number_rows offset offset_value

[0102] 3. When the logical plan corresponds to an SQL query statement of the following form:

[0103] R order by expression limit number_rows1 offset offset_value1

[0104] order by expression limit number_rows2 offset offset_value2

[0105] The logical plan for rewriting the SQL query as follows:

[0106] R order by expression limit number_rows3 offset offset_value3

[0107] Where offset_value3 equals offset_value1 + offset_value2;

[0108] When (offset_value2+number_rows2)≤number_rows1, number_rows3 equals number_rows2; otherwise, number_rows3 equals the larger of number_rows1-offset_value2 and 0.

[0109] Specifically, the present invention repeatedly searches for paired ORDER BY operators to compare the structures of two logical plans P1' and P2' according to the following steps:

[0110] Step 1: If neither P1' nor P2' contains ORDER BY, the ORDER BY processing step ends; if only one of P1' and P2' contains ORDER BY, the original SQL query statement is considered not equivalent.

[0111] Step 2: Select one ORDER BY operator O1 from P1'.

[0112] Step 3: Select the ORDER BY operator in P2' that has the same parameters as O1 and the same LIMIT and OFFSET. Assume that the ORDER BY operators that meet the above conditions form a set S.

[0113] Step 4: Let the subplan of O1 be P3; for each ORDER BY operator O2 in S, let its subplan be P4. If subsequent steps verify the equivalence of P3 and P4, then O1 and O2 are considered to be paired, and O1 and its subplans and O2 and its subplans are replaced with the same relation. If all ORDER BY operators in S are not paired with O1, then the original SQL query statement is considered not to be equivalent.

[0114] Step 5: Return to Step 1.

[0115] U-expression generation steps: Parse the logical plan to generate the corresponding U-expression; where U-expression refers to an algebraic expression f(x), representing the number of tuples x in the query result.

[0116] Specifically, this invention models SQL features using U-expression (1-6 are the same as existing work WeTune):

[0117] 1. The U-expression of a tuple t in a relation R is represented as [[R]](t), which can also be simplified to R(t), representing the number of tuples t in relation R.

[0118] 2. For SQL features with deduplication semantics such as distinct, U-expression uses "e", which means that if the expression e (also a U-expression) is 0, then 0 is returned; otherwise, 1 is returned.

[0119] 3. For predicates, U-expression uses [], which means that if the predicate b is true, it returns 1, otherwise it returns 0.

[0120] 4. For the NOT operator, U-expression uses not(e), which means that if the expression e is 0, it returns 1, otherwise it returns 0.

[0121] 5. For projection, U-expression uses ∑ t f(t) represents the summation of f(t) for each possible tuple t, where f is a U-expression in t, and the output is a natural number. Because the number of possible tuples can be infinite, this summation is called unbounded summation.

[0122] 6. For join and union all, U-expression uses e1+e2 to represent the result of adding the two expressions.

[0123] 7. For aggregate functions, construct the U-expression based on a SQL structure:

[0124] select Rx,agg_func([distinct]Ry)from R group by Rx having p

[0125] In this structure, [distinct] indicates that there may be distinct cases in the aggregate function, and represents the result of the aggregate function after deduplication.

[0126] Based on this structure, for the aggregate function agg_func which is SUM and does not contain distinct, its U-expression is:

[0127] x(t1) and y(t1) represent the projections of tuple t1 onto the x and y attributes, respectively. [IsNull(y(t1))] indicates whether tuple y(t1) is NULL, returning 1 if it is, and 0 otherwise.

[0128] For the aggregate function COUNT that does not contain distinct, its U-expression is:

[0129]

[0130] For the aggregate function MAX that does not contain distinct, its U-expression is:

[0131]

[0132] For the aggregate function MIN and without distinct, its U-expression is:

[0133]

[0134] For the aggregation function AVG that does not contain distinct, its U-expression is:

[0135]

[0136] Here, COUNT Expr represents the U-expression corresponding to the aggregate function COUNT, while SUM Expr represents the U-expression corresponding to the aggregate function SUM.

[0137] For the aggregate function SUM containing distinct, its U-expression is:

[0138]

[0139] For aggregate functions COUNT, MAX, MIN, and AVG that contain distinct, the same logic applies as for aggregate functions SUM that contain distinct.

[0140] 8. For INTERSECT, the U-expression corresponds to f(t) := ||f l ()×f r ()‖, where f l () and f r () corresponds to the U-expression of the two clauses of INTERSECT.

[0141] 9. For INTERSECT ALL, the U-expression corresponds to f(t) := ([f l ()≤f r ()]×f l ())+(not([ l ()≤f r ()])×f r ()), where f l () and f r () corresponds to the U-expression of the two clauses of INTERSECT ALL.

[0142] 10. For VALUES(t1)...(t n ), U-expression corresponds to f(t)∶=[t=t1]+...+[t= n ].

[0143] 11. For scalar subqueries, assume their SQL structure is as follows:

[0144] select x from R where p

[0145] Its corresponding subquery statement can only return a single tuple, therefore the U-expression corresponds to...

[0146] Normalization steps: Normalize the U-expression using the algorithm in the existing working UDP to generate a standard form of the U-expression, which is the same as the standard form defined in the existing working UDP.

[0147] LIA * Transformation steps: For a pair of standard form U-expressions f1(t) and f2(t), generate LIA * An expression is a proposition; if it is not satisfied, then the SQL query statement is equivalent.

[0148] Specifically, the present invention implements the following steps in sequence:

[0149] 1. Generate proposition P:

[0150] 2. Repeat this step until there are no unbounded sums in proposition P: For each unbounded sum in proposition P that is not among other unbounded sums. Use an integer variable v i Replace it, and make these variables modified by existential quantifiers, then append propositions (v1,…,v) using logical AND. n)∈{(x1,…,x n )∣x1=E1…∧x n =E n} * For the initial proposition P, this step is to make P become Where f1(t) and f2(t) are obtained by unbounded summation. to integer variable v i Replacement (1≤i≤n).

[0151] 3. For the U-expression contained in proposition P, perform the following transformation.

[0152] For tuples t and R(t), convert them into integer variables.

[0153] For [b], convert it to ite(b,1,0), and recursively convert b according to the rules of this step.

[0154] For not(b), convert it to ite(b=0,1,0), and recursively convert b according to the rules of this step.

[0155] For ‖E‖, convert it to ite(E=0,0,1), and recursively convert E according to the rules of this step.

[0156] First-order logic expression generation steps: When the expression generated in the previous step is LIA * When expressing an expression, according to the LIA * The expression generates a corresponding first-order logical expression. If the first-order logical expression cannot be satisfied, then the SQL query statement is equivalent.

[0157] Specifically, for different forms of LIA * The present invention implements the following methods to generate first-order logic expressions:

[0158] 1. For the standard form of LIA * The expression, in the form of Where F1,2 are LIA expressions. This invention, using existing methods (refer to Ruzica Piskac and Viktor Kuncak. 2008. Linear Arithmetic with Stars. Computer Aided Verification.), obtains a set of constant integer vectors. Make And generate a first-order logic expression Where λ1,…,λ k It is an integer variable.

[0159] 2. For nested LIAs* The expression, in the form of

[0160]

[0161] Where F is a LIA expression. It can be a LIA expression or a standard form of LIA. * Expressions or nested LIAs * Regarding the expression, this patent proposes the following theorem: For any nested LIA * Expression l∶= if If the expression l is satisfiable equivalent to the expression F2 in LIA, then the expression l is... Satisfaction equivalence.

[0162] Based on this theorem, this invention recursively processes all LIAs * The formula is replaced with an LIA formula equivalent to its satisfiability to transform the entire nested LIA. * The formula is converted into a first-order logical expression.

[0163] 3. For LIAs containing parameterized addition closures * The expression, in the form of F2 contains Variables other than integers or non-integer types are called parameters. The following steps are performed:

[0164] First, this invention transforms F2 into an equivalent disjunctive normal form (P). 11 ∧P 12 ∧...)∨…∨(P n1 ∧P n2 ∧...), where each P ij These are all atomic logical formulas or their negations. Given a first-order logical expression, this invention transforms it into disjunctive normal form by repeatedly applying the distributive law, De Morgan's law, etc. Simultaneously, because disjunctive normal form does not accept ite expressions, this invention transforms the expression v = ite(c,t,e) into...

[0165] Secondly, according to the theorem Equivalent to Therefore, the expression It can be equivalently transformed into

[0166]

[0167] Then, this patent identifies the predicate P containing parameters. ijAnd the following theorem is proposed to decouple these predicates from the above equation: Assume and It is an integer vector. It is independent of and A vector. Independent of If and only if for any a i and b j They all have a i ≠b j Therefore, for any first-order logic expressions P, P1, and P2, we have the expression:

[0168]

[0169] Equivalent to the satisfiability of the following formula

[0170]

[0171] Based on the above theorem, this patent identifies predicate P that may contain parameters. ij and LIA * Expression conversion The form is given, where P1 is a formula containing only nonparametric variables, and P2 is a formula with parameters. This patent further examines whether... Independent of and If satisfied, the expression can be further transformed into... This completes the connection between the parameterized predicate P2 and LIA. * Decoupling of expressions. Then, continue using the methods from this step to generate first-order logic expressions.

[0172] 4. For LIAs containing nonlinear additive closures * The expression, in the form of Where P contains the product of integer variables. This invention multiplies each integer variable in P. Replace with a new integer variable y j And through logic and additional propositions That is, LIA * The expression becomes in express The formula after replacing the product with integer variables. Then, the method from this step can be used to generate first-order logic expressions.

[0173] This invention also provides a system for automated SQL equivalence proof based on linear integer algebra, according to the above-mentioned equivalence verification algorithm, comprising the following modules:

[0174] Logical plan generation module: Converts query statements into corresponding logical plans;

[0175] ORDER BY processing module: processes ORDER BY operations and generates new plans that do not contain ORDER BY;

[0176] U-expression generation module: Converts logical plans into corresponding U-expressions;

[0177] LIA * Validation module: Generate LIA based on U-expression * The expression is then converted into an LIA expression and its corresponding first-order logical expression for SQL equivalence verification.

[0178] The ORDER BY processing module includes: an ORDER BY operation deletion and merging submodule; and a new plan generation submodule.

[0179] The U-expression generation module includes: a raw U-expression generation submodule, which converts the logical plan of the query statement into the corresponding unnormalized raw U-expression expression; a normal normalization method unit, which rewrites the U-expression into an equivalent form without using integrity constraint information; and a constraint normalization method unit, which normalizes the U-expression based on database integrity constraints and rewrites it into an equivalent form.

[0180] Among them, LIA * The verification module includes: LIA * Expression generation submodule: Generates LIA based on U-expression * Expressions. First-order logic expression generation submodule: Converts LIA* expressions into first-order logic expressions. Verification submodule: Uses the SMT solver to check the satisfiability of the generated first-order logic expressions to provide equivalence results for the input SQL query.

[0181] This invention validates 232 pairs of equivalent SQL queries from Calcite, successfully proving all equivalent SQL queries from Calcite. In contrast, existing work on WeTune can only prove 78 pairs of equivalent SQL queries from Calcite.

[0182] This invention provides a method and system for automated proof of SQL equivalence based on linear integer algebra, modeling SQL statements as U-expressions and proposing U-expressions and LIA. * This invention presents a method for converting between SQL statements and first-order logical expressions. It transforms the equivalence conditions of two SQL statements into first-order logical expressions, and then verifies the equivalence of the SQL statements by solving the first-order logical expressions using an SMT solver. Compared to existing technologies, this invention supports more SQL features and is based on LIA. * The formula provides a method to transform a U-expression containing unbounded summation into a first-order logical expression, solving the problem that existing work struggles to handle unbounded summation and greatly enhancing the ability to verify SQL equivalence.

[0183] Those skilled in the art will understand that, besides implementing the system and its various devices, modules, and units provided by this invention in the form of purely computer-readable program code, the same functions can be achieved entirely through logical programming of the method steps, making the system and its various devices, modules, and units of this invention function in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers. Therefore, the system and its various devices, modules, and units provided by this invention can be considered as a hardware component, and the devices, modules, and units included therein for implementing various functions can also be considered as structures within the hardware component; alternatively, the devices, modules, and units for implementing various functions can be considered as both software modules implementing the method and structures within the hardware component.

[0184] Specific embodiments of the present invention have been described above. It should be understood that the present invention is not limited to the specific embodiments described above, and those skilled in the art can make various changes or modifications within the scope of the claims, which do not affect the essence of the present invention. Unless otherwise specified, the embodiments and features described in this application can be arbitrarily combined with each other.

Claims

1. A method for automated proof of SQL equivalence based on linear integer algebra, characterized in that, include: Plan generation steps: Parse the input SQL query statement to generate the original logical plan; where each SQL statement corresponds to one original logical plan; ORDER BY processing steps: Process the ORDER BY sorting operation in the original logical plan, generate a new logical plan without ORDER BY sorting operation, and hand it over to the subsequent steps for equivalence verification; U-expression generation steps: parse the logical plan and generate the corresponding U-expression; Normalization steps: Normalize and simplify the U-expression to generate a standard form of U-expression; LIA * Conversion steps: Generate LIA based on the standard form of U-expression. * expression; Steps for generating a first-order logic expression: Based on LIA * The expression generates a first-order logical expression, which is then used to perform SQL equivalence verification.

2. The method for automated SQL equivalence proof based on linear integer algebra according to claim 1, characterized in that, The ORDER BY processing steps include: for each original logical plan, deleting and merging ORDER BY operations according to predefined rules, and then generating a new logical plan without ORDER BY operations, ensuring that if the new logical plan is equivalent, then the original SQL query statement is also equivalent.

3. The method for automated SQL equivalence proof based on linear integer algebra according to claim 1, characterized in that, The LIA * The transformation steps include: generating the corresponding LIA for any pair of U-expressions. * An expression, specifically a logical expression about integers, is required. If this logical expression is not satisfied, then the two SQL queries are equivalent. (LIA) * The expression contains an additive closure, which corresponds to unbounded summation in U-expression.

4. The method for automated SQL equivalence proof based on linear integer algebra according to claim 1, characterized in that, The first-order logic expression generation step includes: for any LIA * The expression generates a corresponding first-order logical expression, specifically a proposition. If the proposition is not satisfied, then the SQL query statement is equivalent.

5. A system for automated SQL equivalence proof based on linear integer algebra, based on the method for automated SQL equivalence proof based on linear integer algebra as described in any one of claims 1-4, characterized in that, include: Logical plan generation module: Converts query statements into corresponding logical plans; ORDER BY processing module: processes ORDER BY clauses in the logical plan, generates a new plan without ORDER BY clauses, and calls other modules to verify the equivalence of the new plan; U-expression generation module: Converts logical plans into corresponding U-expressions; LIA * Validation module: Converts U-expression into corresponding LIA. * Expression, and LIA * The expression is further transformed into the corresponding first-order logical expression for SQL equivalence verification. The ORDER BY processing module includes: The ORDER BY operation delete and merge submodule: deletes and merges ORDER BY operations in the logical plan according to a series of predefined rules; New plan generation submodule: Generates a new logical plan that does not contain ORDER BY.

6. The system for automated SQL equivalence proof based on linear integer algebra according to claim 5, characterized in that, The U-expression expression generation module includes: The raw U-expression generation submodule converts the logical plan of the query statement into the corresponding unnormalized raw U-expression expression; The normalization submodule normalizes the original U-expression.

7. The system for automated SQL equivalence proof based on linear integer algebra according to claim 6, characterized in that, The normalization submodule includes: Ordinary regularization method unit: Regularization is performed without using integrity constraint information, and the U-expression is rewritten into a regular form; Constraint Regularization Method Unit: Based on the integrity constraint information of the attributes in the table, the U-expression is rewritten into a regular form.

8. The system for automated SQL equivalence proof based on linear integer algebra according to claim 5, characterized in that, The LIA * The verification module includes: LIA * Expression generation submodule: converts U-expression to LIA * The expression, if this LIA * If the expression is not satisfied, then the two SQL query statements are equivalent. First-order logic expression generation submodule: This module generates LIA expressions. * If an expression is converted into a first-order logical expression and it is not satisfied, then the SQL query statement is equivalent. Verification submodule: Uses the SMT solver to check the satisfiability of the generated first-order logic expression to provide equivalence results for the input SQL query statement.

Images