Text2SQL self-correction method based on error pattern perception

Abstract

This invention relates to a Text2SQL self-correction method based on error pattern awareness, belonging to the field of data processing technology. It includes: abstracting general error patterns from isolated error messages to enable the model to have generalizable diagnostic capabilities, and constructing a knowledge graph based on these error patterns; abandoning traditional single-step blind correction, constructing a dynamic feedback loop containing state memory to force the model to escape local optima; and performing adaptive iterative repair termination based on a comprehensive score. This invention models the SQL correction process as a closed-loop control flow of "diagnosis-retrieval-verification," breaking through the traditional "static verification + manual rules" paradigm. It transforms database execution feedback into a structured error pattern representation, guiding a Large Language Model (LLM) to establish generalizable SQL error diagnosis and repair capabilities.

Description

Technical Field

[0001] This invention relates to a text2SQL self-correction method based on error pattern awareness, belonging to the field of data processing technology. Background Technology

[0002] In the digital age where data-driven decision-making is increasingly becoming a core requirement across industries, Structured Query Language (SQL), as the industry-standard tool for accessing, manipulating, and analyzing relational databases, presents a significant barrier to non-professional users (such as business analysts, managers, or novice data users) for efficiently acquiring data insights and value due to its inherent technical complexity. Text-to-SQL (Text2SQL) technology significantly reduces the barrier to entry for database interaction by automatically and accurately converting users' query intentions, described in everyday natural language, into executable SQL query statements, achieving a true "barrier-to-entry" effect. It not only simplifies the data access process but also acts as a crucial bridge connecting human natural language interaction methods with the machine-processable structured data world, thereby empowering a wider range of users to seamlessly utilize data resources.

[0003] In recent years, benefiting from breakthroughs in artificial intelligence, especially the widespread application and optimization of Large Language Models (LLMs) based on the Transformer architecture, Text2SQL technology has achieved significant performance leaps in academic research and benchmark evaluations. For example, on authoritative benchmark sets such as Spider, the model has significantly improved accuracy in metrics such as cross-database generalization and complex query generation, reflecting the powerful potential of LLMs in capturing the mapping relationship between natural language semantics and SQL structure. However, despite encouraging performance in experimental environments, the robustness of generated SQL queries in real-world industrial scenarios has not yet reached the reliability level of human experts. In practical applications in industrial scenarios such as financial risk control, healthcare, and retail operations, the application of Text2SQL systems is often hindered by various errors in the generated results. These errors not only lead to query failures but may also cause data misinterpretation and decision-making risks. Therefore, error correction technology, as an indispensable safeguard in the Text2SQL system architecture, is crucial for improving output reliability and enhancing system robustness, and is an important support for promoting the technology from the laboratory to large-scale industrial deployment.

[0004] Currently, Text2SQL systems driven by Large Language Models (LLM) face multiple technical bottlenecks in error correction. Traditional methods treat SQL execution errors (such as "column not found") as isolated syntax anomalies rather than generalizable error patterns, requiring the LLM to relearn diagnostic logic for each repair. Existing correction strategies over-optimize execution success rates, often altering the original query intent by deleting key WHERE conditions. During multi-round iterative repair, the LLM is prone to falling into an oscillating cycle of "repair-destruction-re-repair" (such as repeatedly switching JOIN conditions). Meanwhile, fixed-round termination mechanisms cannot adapt to the dynamic distribution of query complexity, resulting in computational redundancy for simple queries and insufficient repair for complex queries. Summary of the Invention

[0005] To address the shortcomings of existing technologies, this invention proposes a Text2SQL self-correction method based on error pattern awareness. It models the SQL correction process as a closed-loop control flow of "diagnosis-retrieval-verification", breaking through the traditional "static verification + manual rules" paradigm. It transforms database execution feedback into a structured error pattern representation, guiding the Large Language Model (LLM) to establish generalizable SQL error diagnosis and repair capabilities.

[0006] The present invention adopts the following technical solution:

[0007] A text2SQL self-correction method based on error pattern awareness includes:

[0008] S1. In order to enable the model to have generalizable diagnostic capabilities, general error patterns are abstracted from isolated error messages, and a knowledge graph is constructed based on the error patterns.

[0009] S2, This invention abandons the traditional single-step blind correction and constructs a dynamic feedback loop that includes state memory, thereby forcing the model to escape the local optimum trap;

[0010] S3, terminates the adaptive iterative repair based on the comprehensive score.

[0011] Preferably, in step S1, the open-source Text2SQL technology is used to generate SQL queries on the Spider and Bird public datasets, and more than 2,000 SQL execution failure logs are analyzed. Through clustering algorithms and manual verification, the SQL execution errors are summarized into 7 common error patterns. The error patterns include error pattern ID, error mechanism, typical error message and repair strategy template.

[0012] Each type of error mode Formalize into triples =<feature fingerprint, repair strategy, boundary constraints>, construct a knowledge graph The feature fingerprint corresponds to the error mode ID and typical error in the error mode, the repair strategy corresponds to the repair strategy template in the error mode, and the boundary constraint is a negative constraint condition to prevent over-correction.

[0013] The preferred, seven common error modes are as follows:

[0014] ① Error Pattern ID: EP-1; Error Mechanism: Pattern Mismatch; Typical Error: no such column, table not found; Repair Strategy Template: Replace column name / Add missing table;

[0015] ② Error Mode ID: EP-2; Error Mechanism: Value Range Conflict; Typical Errors: invalid literal, typemismatch; Repair Strategy Template: Type Conversion / Value Normalization;

[0016] ③ Error Mode ID: EP-3; Error Mechanism: Logical Structure Defect; Typical Errors: Misuse of Aggregate, Group By Missing; Repair Strategy Template: Rewrite Subquery / Complete GROUP BY;

[0017] ④ Error Mode ID: EP-4; Error Mechanism: Broken connection path; Typical Error: Ambiguous column, join error; Repair Strategy Template: Complete JOIN conditions / Specify aliases;

[0018] ⑤ Error Mode ID: EP-5; Error Mechanism: Syntax violation; Typical Error: Syntax error; Repair Strategy Template: Correct punctuation / keyword completion;

[0019] ⑥ Error Pattern ID: EP-6; Error Mechanism: Semantic Shift; Typical Errors: Empty Result Set / Abnormal Statistics; Repair Strategy Template: Reconstruct Query Range / Adjust Aggregation Granularity;

[0020] ⑦ Error Mode ID: EP-7; Error Mechanism: Resource Constraint Conflict; Typical Error: Timeout; Repair Strategy Template: Add LIMIT / Simplify Subquery.

[0021] Preferably, the boundary constraints are:

[0022] EP-1:

[0023] Locked editing area: Only column name identifiers in the SELECT or WHERE clause can be modified; Value preservation constraint: Modification of any literal numeric or string on the right side of comparison operators (such as >, =) is strictly prohibited; Structure preservation constraint: Adding or deleting existing filter conditions is strictly prohibited;

[0024] EP-2:

[0025] Operation focus: Adaptation is only performed on the data type at the error location;

[0026] Semantic immutability: Changing column names is strictly prohibited; only modifying the format of values ​​(e.g., '2023' → 2023) or adding conversion functions is allowed.

[0027] EP-3:

[0028] Structure alignment constraint: enforces strict consistency between non-aggregate fields in the SELECT clause and the GROUP BY clause;

[0029] Filter conditions are frozen: Modification of the filtering logic in the WHERE clause is strictly prohibited (this part usually represents the user's core filtering intent).

[0030] Nested locking: If a subquery is involved, changing the join key of the subquery is prohibited; only the aggregation method can be adjusted.

[0031] EP-4:

[0032] Alias ​​enforcement constraint: Requires an explicit table prefix (e.g., T1.name) to be added to all column names that appear in SELECT, WHERE, and ORDER BY statements;

[0033] Entity preservation constraints: Prohibit the introduction of new tables not mentioned in the schema, and prohibit the deletion of currently connected tables (unless it is confirmed that they are redundant);

[0034] Join type locking: Unless explicitly stated in the error message, the JOIN type is kept unchanged by default to prevent changes to the range of the result set;

[0035] EP-5:

[0036] Minimum edit distance constraint: Only allow modification of non-semantic parts of the SQL (punctuation, keywords);

[0037] Semantic absolute freeze: Modification of any table name, column name, literal value, or string is strictly prohibited;

[0038] Structural integrity: Deleting the entire clause is prohibited;

[0039] EP-6:

[0040] Predicate relaxation constraints: allow fine-tuning of conditions in the WHERE clause (such as case conversion, fuzzy matching LIKE, and widening of numerical range);

[0041] Projection Preservation Constraint: Do not modify the returned columns in the SELECT clause; if the user asks for "name", do not change the returned column to "age" just because it cannot be found.

[0042] Intent anchoring: The modified conditions must maintain a "weak equivalence" semantically with the original intent;

[0043] EP-7:

[0044] Complexity constraints: force the addition of a LIMIT clause (if the intent allows) or optimize the JOIN order;

[0045] Result set consistency: The goal of optimization is "speed", not "change", and the optimized SQL logic must be mathematically equivalent to the original SQL;

[0046] Subquery flattening: Rewrite nested subqueries as JOIN.

[0047] Preferably, the implementation process of step S2 is as follows:

[0048] S2-1, change the current SQL Execute in a sandbox environment and capture error logs. A hybrid retrieval strategy is used to obtain the most matching error pattern from the knowledge graph. ;

[0049] S2-2, Construct a structured prompt word containing "diagnosis-constraint-instruction". The retrieved error patterns To prevent the model from modifying clauses unrelated to errors during LLM injection, this invention displays the principle of minimal editing in the prompt:

[0050] Based on error mode Strictly adhere to boundary constraints right Local corrections should be made to the erroneous segments, but modifications to query logic unrelated to the errors are strictly prohibited;

[0051] S2-3, Oscillation suppression based on structural fingerprints.

[0052] Preferably, the hybrid retrieval strategy in step S2-1 is as follows:

[0053] First, define the first Error patterns Confidence scoring function as follows:

[0054]

[0055] in, As a balancing factor, a value of 0.4 is set to achieve a smooth transition from hard keyword matching to soft semantic matching. For keyword matching indicator functions, if error log Includes The defined typical error keywords are as follows: Otherwise, it is 0; Represents the cosine similarity function; Indicates error log Semantic vectors generated based on word embedding models Indicates error mode Semantic vectors generated based on word embedding models;

[0056] Subsequently, based on confidence scores from the knowledge graph The highest-scoring error pattern in the mid-positioning :

[0057] .

[0058] Preferably, in steps S2-3, to prevent the LLM from falling into a binary oscillation cycle of "fixing A causes B to be wrong, fixing B causes A to be wrong", this invention maintains a historical SQL correction record. In generating the first SQL round-robin correction Then, calculate the first... Wheel and Tree edit distance of each round of SQL :

[0059]

[0060] in, This indicates that the SQL parser will be used to... The abstract grammar tree structure of transformation, This indicates the minimum number of node edit operations required to transform one tree into another using the Zhang-Shasha algorithm.

[0061] The oscillation state is determined when the following conditions are met:

[0062]

[0063] in The tolerance threshold is set to 0 in this embodiment, meaning that only when the structures are completely identical will it be considered an infinite loop;

[0064] Once an oscillation is detected, roll back to... The status is determined, and in the error pattern retrieval of the current step, the error patterns used in the previous round are forcibly filtered out. Instead, it tries the error pattern with the second-best confidence score, thereby forcing the model to escape the local optimum trap.

[0065] Preferably, in step S3, in order to balance computational cost and repair quality, the present invention designs a multi-dimensional comprehensive scoring function. To determine the endpoint of dynamic decision-making iteration;

[0066] Define the current database state as The original natural language query is ;

[0067] Define the comprehensive scoring function as follows:

[0068]

[0069] in, This indicates the sandbox execution status; a value of 0 indicates an SQL execution error, and a value of 1 indicates otherwise. These are weight parameters;

[0070] This indicates the effective coverage of schema elements referenced in the SQL statement within the database:

[0071]

[0072] Indicates from the generated The set of all Schema elements parsed from the file; Represents database The actual set of elements in;

[0073] This represents the semantic consistency score based on LLM criteria, used to measure the corrected SQL. Has semantic shift occurred (e.g., to make the SQL query work, the core filtering condition in the original problem was removed)?

[0074]

[0075] in, This represents the embedding vector of an SQL statement after natural language back-translation. The embedding vector representing the user query; This represents the cosine similarity function.

[0076] when When the iteration stops, the repair process terminates and the current optimal solution is output.

[0077] Preferred, It is 0.4 It is 0.6.

[0078] Preferably, the threshold Set to 0.95.

[0079] For any details not covered in this invention, please refer to the prior art.

[0080] The beneficial effects of this invention are as follows:

[0081] 1. This invention overcomes the limitations of existing technologies that treat SQL error logs as isolated, discrete strings. By constructing a structured error pattern knowledge base containing feature fingerprints and repair strategies, i.e., a knowledge graph based on error patterns, it reduces massive discrete errors to seven general error patterns (such as pattern mismatch, broken connection paths, etc.). This abstraction process enables Large Language Models (LLMs) to identify the same error essence through superficial error differences, thereby achieving cross-database and cross-task diagnostic logic transfer. It avoids the model repeatedly learning diagnostic logic for similar errors, significantly reduces the dependence on few sample examples, and improves the system's generalized diagnostic capability when facing unknown database architectures.

[0082] 2. This invention employs a dual control mechanism combining structured prompts based on boundary constraints and semantic consistency scores. At the generation end, negative constraints such as "locking the edit area" and "value preservation constraints" are injected to explicitly prohibit the model from modifying non-erroneous segments. At the verification end, vector similarity metrics are used to quantify the deviation of the query intent before and after the correction.

[0083] Existing technologies often achieve successful SQL execution by removing WHERE conditions or simplifying aggregation logic (i.e., "pseudo-fixing"). This invention, through a closed loop of "pre-constraint + post-verification," forces the model to perform syntactic corrections while preserving core business logic. This effectively prevents the sacrifice of accuracy for executability, ensuring that the final generated SQL not only runs but also accurately reflects the user's original query intent. It is particularly suitable for scenarios with extremely high data accuracy requirements, such as finance and healthcare.

[0084] 3. This invention introduces a trajectory monitoring and rollback mechanism based on AST tree edit distance. By calculating the structural differences between the current SQL and historical states, the system can keenly detect the binary oscillation characteristic of "fixing A leads to B being wrong, and fixing B leads to A being wrong" (i.e., the code structure repeatedly jumps between two error states). Once structural similarity is detected, the system uses a tabu search strategy to force a rollback to the branch node and blocks the optimal strategy that causes the oscillation, instead exploring a suboptimal path. This mechanism fundamentally breaks the local optimum trap, prevents the ineffective use of computing resources, and ensures the convergence and stability of the system when dealing with complex scenarios such as multi-table joins and nested subqueries.

[0085] 4. This invention abandons the traditional fixed-round termination strategy and proposes a multi-dimensional comprehensive scoring function based on execution status, schema coverage, and semantic consistency to dynamically determine the iteration endpoint. This mechanism endows the system with the ability to perceive task complexity: for simple queries, termination occurs immediately once the score is met (possibly requiring only one round); for complex queries, sufficient iterations are allowed within the scoring threshold. This dynamic decision-making mechanism avoids wasted computational power on simple tasks and insufficient repair of complex tasks, maximizing the repair success rate while minimizing token consumption and latency. Attached Figure Description

[0086] The accompanying drawings, which form part of this application, are used to provide a further understanding of this application. The illustrative embodiments of this application and their descriptions are used to explain this application and do not constitute an undue limitation of this application.

[0087] Figure 1 This is a flowchart of the Text2SQL self-correction method based on error pattern awareness of the present invention;

[0088] Figure 2 This is a schematic diagram of the knowledge graph node construction in this invention. Detailed Implementation

[0089] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this invention will be clearly and completely described below with reference to the accompanying drawings. However, this is not the only description; all aspects not described in detail herein are based on conventional techniques in the art.

[0090] Example 1

[0091] A text2SQL self-correction method based on error pattern awareness, such as Figure 1 As shown, it includes:

[0092] S1. In order to enable the model to have generalizable diagnostic capabilities, general error patterns are abstracted from isolated error messages, and a knowledge graph is constructed based on the error patterns.

[0093] S2, This invention abandons the traditional single-step blind correction and constructs a dynamic feedback loop that includes state memory, thereby forcing the model to escape the local optimum trap;

[0094] S3, terminates the adaptive iterative repair based on the comprehensive score.

[0095] Example 2

[0096] A text2SQL self-correction method based on error pattern awareness, as described in Example 1, differs in that, in step S1, SQL queries are generated from the Spider and Bird public datasets using open-source Text2SQL technology, and more than 2,000 SQL execution failure logs are analyzed. Through clustering algorithms and manual verification, SQL execution errors are summarized into 7 common error patterns, including error pattern ID, error mechanism, typical error message, and repair strategy template, as shown in Table 1:

[0097] Table 1 General Error Mode Table

[0098]

[0099] As can be seen from Table 1, the seven common error modes are as follows:

[0100] ① Error Pattern ID: EP-1; Error Mechanism: Pattern Mismatch; Typical Error: no such column, table not found; Repair Strategy Template: Replace column name / Add missing table;

[0101] ② Error Mode ID: EP-2; Error Mechanism: Value Range Conflict; Typical Errors: invalid literal, typemismatch; Repair Strategy Template: Type Conversion / Value Normalization;

[0102] ③ Error Mode ID: EP-3; Error Mechanism: Logical Structure Defect; Typical Errors: Misuse of Aggregate, Group By Missing; Repair Strategy Template: Rewrite Subquery / Complete GROUP BY;

[0103] ④ Error Mode ID: EP-4; Error Mechanism: Broken connection path; Typical Error: Ambiguous column, join error; Repair Strategy Template: Complete JOIN conditions / Specify aliases;

[0104] ⑤ Error Mode ID: EP-5; Error Mechanism: Syntax violation; Typical Error: Syntax error; Repair Strategy Template: Correct punctuation / keyword completion;

[0105] ⑥ Error Pattern ID: EP-6; Error Mechanism: Semantic Shift; Typical Errors: Empty Result Set / Abnormal Statistics; Repair Strategy Template: Reconstruct Query Range / Adjust Aggregation Granularity;

[0106] ⑦ Error Mode ID: EP-7; Error Mechanism: Resource Constraint Conflict; Typical Error: Timeout; Repair Strategy Template: Add LIMIT / Simplify Subquery.

[0107] Each type of error mode Formalize into triples =<feature fingerprint, repair strategy, boundary constraints>, construct a knowledge graph The feature fingerprint corresponds to the error mode ID and typical error in the error mode, the repair strategy corresponds to the repair strategy template in the error mode, and the boundary constraint is a negative constraint condition to prevent over-correction.

[0108] Boundary constraints are shown in Table 2:

[0109] Table 2 Boundary Constraint Table

[0110]

[0111] Specifically:

[0112] EP-1:

[0113] Locked editing area: Only column name identifiers in the SELECT or WHERE clause can be modified; Value preservation constraint: Modification of any literal numeric or string on the right side of comparison operators (such as >, =) is strictly prohibited; Structure preservation constraint: Adding or deleting existing filter conditions is strictly prohibited;

[0114] EP-2:

[0115] Operation focus: Adaptation is only performed on the data type at the error location;

[0116] Semantic immutability: Changing column names is strictly prohibited; only modifying the format of values ​​(e.g., '2023' → 2023) or adding conversion functions is allowed.

[0117] EP-3:

[0118] Structure alignment constraint: enforces strict consistency between non-aggregate fields in the SELECT clause and the GROUP BY clause;

[0119] Filter conditions are frozen: Modification of the filtering logic in the WHERE clause is strictly prohibited (this part usually represents the user's core filtering intent).

[0120] Nested locking: If a subquery is involved, changing the join key of the subquery is prohibited; only the aggregation method can be adjusted.

[0121] EP-4:

[0122] Alias ​​enforcement constraint: Requires an explicit table prefix (e.g., T1.name) to be added to all column names that appear in SELECT, WHERE, and ORDER BY statements;

[0123] Entity preservation constraints: Prohibit the introduction of new tables not mentioned in the schema, and prohibit the deletion of currently connected tables (unless it is confirmed that they are redundant);

[0124] Join type locking: Unless explicitly stated in the error message, the JOIN type is kept unchanged by default to prevent changes to the range of the result set;

[0125] EP-5:

[0126] Minimum edit distance constraint: Only allow modification of non-semantic parts of the SQL (punctuation, keywords);

[0127] Semantic absolute freeze: Modification of any table name, column name, literal value, or string is strictly prohibited;

[0128] Structural integrity: Deleting the entire clause is prohibited;

[0129] EP-6:

[0130] Predicate relaxation constraints: allow fine-tuning of conditions in the WHERE clause (such as case conversion, fuzzy matching LIKE, and widening of numerical range);

[0131] Projection Preservation Constraint: Do not modify the returned columns in the SELECT clause; if the user asks for "name", do not change the returned column to "age" just because it cannot be found.

[0132] Intent anchoring: The modified conditions must maintain a "weak equivalence" semantically with the original intent;

[0133] EP-7:

[0134] Complexity constraints: force the addition of a LIMIT clause (if the intent allows) or optimize the JOIN order;

[0135] Result set consistency: The goal of optimization is "speed", not "change", and the optimized SQL logic must be mathematically equivalent to the original SQL;

[0136] Subquery flattening: Rewrite nested subqueries as JOIN.

[0137] Taking error pattern EP-1 as an example, the knowledge graph node construction example is as follows: Figure 2 As shown.

[0138] Example 3

[0139] An error pattern-aware Text2SQL self-correction method, as described in Example 2, differs in that the implementation process of step S2 is as follows:

[0140] S2-1, change the current SQL Execute in a sandbox environment and capture error logs. A hybrid retrieval strategy is used to obtain the most matching error pattern from the knowledge graph. ;

[0141] The hybrid search strategy is as follows:

[0142] First, define the first Error patterns Confidence scoring function as follows:

[0143]

[0144] in, As a balancing factor, a value of 0.4 is set to achieve a smooth transition from hard keyword matching to soft semantic matching. For keyword matching indicator functions, if error log Includes The defined typical error keywords are as follows: Otherwise, it is 0; Represents the cosine similarity function; Indicates error log Semantic vectors generated based on word embedding models Indicates error mode Semantic vectors generated based on word embedding models;

[0145] Subsequently, based on confidence scores from the knowledge graph The highest-scoring error pattern in the mid-positioning :

[0146] .

[0147] S2-2, Construct a structured prompt word containing "diagnosis-constraint-instruction". The retrieved error patterns To prevent the model from modifying clauses unrelated to errors during LLM injection, this invention displays the principle of minimal editing in the prompt:

[0148] Based on error mode Strictly adhere to boundary constraints right Local corrections should be made to the erroneous segments, but modifications to query logic unrelated to the errors are strictly prohibited;

[0149] S2-3, Oscillation suppression based on structural fingerprints.

[0150] In steps S2-3, to prevent the LLM from falling into a binary oscillation cycle of "fixing A causes B to fail, and fixing B causes A to fail," this invention maintains a historical SQL correction record. In generating the first SQL round-robin correction Then, calculate the first... Wheel and Tree edit distance of each round of SQL :

[0151]

[0152] in, This indicates that the SQL parser will be used to... The abstract grammar tree structure of transformation, This indicates the minimum number of node edit operations required to transform one tree into another using the Zhang-Shasha algorithm.

[0153] The oscillation state is determined when the following conditions are met:

[0154]

[0155] in The tolerance threshold is set to 0 in this embodiment, meaning that only when the structures are completely identical will it be considered an infinite loop;

[0156] Once an oscillation is detected, roll back to... The status is determined, and in the error pattern retrieval of the current step, the error patterns used in the previous round are forcibly filtered out. Instead, it tries the error pattern with the second-best confidence score, thereby forcing the model to escape the local optimum trap.

[0157] Example 4

[0158] A text2SQL self-correction method based on error pattern awareness, as described in Example 3, differs in that, in step S3, to balance computational cost and repair quality, this invention designs a multi-dimensional comprehensive scoring function. To determine the endpoint of dynamic decision-making iteration;

[0159] Define the current database state as The original natural language query is ;

[0160] Define the comprehensive scoring function as follows:

[0161]

[0162] in, This indicates the sandbox execution status; a value of 0 indicates an SQL execution error, and a value of 1 indicates otherwise. As a weighting parameter, in this embodiment It is 0.4 It is 0.6;

[0163] This indicates the effective coverage of schema elements referenced in the SQL statement within the database:

[0164]

[0165] Indicates from the generated The set of all Schema elements parsed from the file; Represents database The actual set of elements in;

[0166] This represents the semantic consistency score based on LLM criteria, used to measure the corrected SQL. Has semantic shift occurred (e.g., to make the SQL query work, the core filtering condition in the original problem was removed)?

[0167]

[0168] in, This represents the embedding vector of an SQL statement after natural language back-translation. The embedding vector representing the user query; This represents the cosine similarity function.

[0169] when When the iteration terminates and the current optimal solution is output, the threshold is set. Set to 0.95.

[0170] The above description represents the preferred embodiments of the present invention. It should be noted that those skilled in the art can make various improvements and modifications without departing from the principles of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A Text2SQL self-correction method based on error pattern awareness, characterized in that, include: S1, abstract common error patterns from isolated error messages, and build a knowledge graph based on the error patterns; S2, construct a dynamic feedback loop that includes state memory, thereby forcing the model to escape the local optimum trap; S3, adaptive iterative repair based on comprehensive score terminates; The implementation process of step S2 is as follows: S2-1, change the current SQL Execute in a sandbox environment and capture error logs. A hybrid retrieval strategy is used to obtain the most matching error pattern from the knowledge graph. ; S2-2, Constructing Structured Prompt Keywords The retrieved error patterns Inject into the LLM and demonstrate the principle of minimal editing during injection; S2-3, Oscillation suppression based on structural fingerprints; The hybrid retrieval strategy in step S2-1 is as follows: First, define the first Error patterns Confidence scoring function as follows: in, For balance coefficient, For keyword matching indicator functions, if error log Includes The defined typical error keywords are as follows: Otherwise, it is 0; Represents the cosine similarity function; Indicates error log Semantic vectors generated based on word embedding models Indicates error mode Semantic vectors generated based on word embedding models; Subsequently, based on confidence scores from the knowledge graph The highest-scoring error pattern in the mid-positioning : ； In step S2-3, maintain the SQL historical correction record. In generating the first SQL round-robin correction Then, calculate the first... Wheel and Tree edit distance of each round of SQL : in, This indicates that the SQL parser will be used to... The abstract grammar tree structure of transformation, This indicates the minimum number of node edit operations required to transform one tree into another using the Zhang-Shasha algorithm. The oscillation state is determined when the following conditions are met: in This is the tolerance threshold; Once an oscillation is detected, roll back to... The status is determined, and in the error pattern retrieval of the current step, the error patterns used in the previous round are forcibly filtered out. Instead, it tries the error pattern with the second-best confidence score, thereby forcing the model to escape the local optimum trap.

2. The Text2SQL self-correction method based on error pattern awareness according to claim 1, characterized in that, In step S1, SQL queries are generated for the Spider and Bird public datasets using open-source Text2SQL technology, and SQL execution errors are summarized into 7 common error patterns. The error patterns include error pattern ID, error mechanism, typical error message and repair strategy template. Each type of error mode Formalize into triples =<feature fingerprint, repair strategy, boundary constraints>, construct a knowledge graph The feature fingerprint corresponds to the error mode ID and typical error in the error mode, the repair strategy corresponds to the repair strategy template in the error mode, and the boundary constraint is a negative constraint condition to prevent over-correction.

3. The Text2SQL self-correction method based on error pattern awareness according to claim 2, characterized in that, The seven common error modes are as follows: ① Error Pattern ID: EP-1; Error Mechanism: Pattern Mismatch; Typical Error: no such column, table not found; Repair Strategy Template: Replace column name / Add missing table; ② Error Mode ID: EP-2; Error mechanism: value range conflict; typical error messages: invalid literal, typemismatch; repair strategy template: type conversion / value normalization; ③ Error Mode ID: EP-3; Error mechanism: logical structure defect; typical error: misuse of aggregate, group by missing; repair strategy template: rewrite subquery / complete GROUP BY; ④ Error Mode ID: EP-4; Error mechanism: Broken join path; Typical error: ambiguous column, joinerror; Repair strategy template: Complete the JOIN condition / Specify alias; ⑤ Error Mode ID: EP-5; mistake Mechanism: Syntax violation; Typical error: syntax error; Repair strategy template: Correct punctuation / keyword completion; ⑥ Error Mode ID: EP-6; Error mechanism: semantic drift; Typical error message: Empty result set / Abnormal statistics; Repair strategy template: Refactor query scope / Adjust aggregation granularity; ⑦ Error Mode ID: EP-7; Error mechanism: resource constraint conflict; typical error: timeout; repair strategy template: add LIMIT / simplify subquery.

4. The Text2SQL self-correction method based on error pattern awareness according to claim 3, characterized in that, The boundary constraints are: EP-1: Locked editing area: Only column name identifiers in the SELECT or WHERE clause can be modified; Value preservation constraint: Modification of any literal numeric or string on the right side of a comparison operator is strictly prohibited; Structure preservation constraint: Adding or deleting existing filter conditions is strictly prohibited; EP-2: Operation focus: Adaptation is only performed on the data type at the error location; Semantic immutability: Changing column names is strictly prohibited; only modifying the value format or adding conversion functions is allowed. EP-3: Structure alignment constraint: enforces strict consistency between non-aggregate fields in the SELECT clause and the GROUP BY clause; Filter condition freezing: Modification of the filter logic in the WHERE clause is strictly prohibited; Nested locking: If a subquery is involved, changing the join key of the subquery is prohibited; only the aggregation method can be adjusted. EP-4: Alias ​​enforcement constraint: Requires an explicit addition of a table prefix to all column names appearing in SELECT, WHERE, and ORDER BY statements; Entity preservation constraints: Prohibit the introduction of new tables not mentioned in the schema, and also prohibit the deletion of currently connected tables; Join type locking: Unless explicitly stated in the error message, the JOIN type is kept unchanged by default to prevent changes to the range of the result set; EP-5: Minimum edit distance constraint: Only allow modification of the non-semantic parts of the SQL; Semantic absolute freeze: Modification of any table name, column name, literal value, or string is strictly prohibited; Structural integrity: Deleting the entire clause is prohibited; EP-6: Predicate relaxation constraints: allow for fine-tuning of conditions in the WHERE clause; Projection Preservation Constraint: Prevents modification of columns returned in the SELECT clause; Intent anchoring: The modified conditions must maintain a "weak equivalence" semantically with the original intent; EP-7: Complexity constraints: force the addition of a LIMIT clause or optimize the JOIN order; Result set consistency: The goal of optimization is "speed", not "change", and the optimized SQL logic must be mathematically equivalent to the original SQL; Subquery flattening: Rewrite nested subqueries as JOIN.

5. The Text2SQL self-correction method based on error pattern awareness according to claim 4, characterized in that, In step S3, the current database state is defined as follows: The original natural language query is ; Define the comprehensive scoring function as follows: in, This indicates the sandbox execution status; a value of 0 indicates an SQL execution error, and a value of 1 indicates otherwise. These are weight parameters; This indicates the effective coverage of schema elements referenced in the SQL statement within the database: Indicates from the generated The set of all Schema elements parsed from the file; Represents database The actual set of elements in; This represents the semantic consistency score based on LLM criteria, used to measure the corrected SQL. Has semantic drift occurred? in, This represents the embedding vector of an SQL statement after natural language back-translation. The embedding vector representing the user query; when When the iteration repair terminates, the current optimal solution is output, where This is the threshold for the overall score.

6. The Text2SQL self-correction method based on error pattern awareness according to claim 5, characterized in that, It is 0.4 It is 0.

6.

7. The Text2SQL self-correction method based on error pattern awareness according to claim 6, characterized in that, threshold Set to 0.95.

Images