Correction method and generation system for converting natural language into SQL (Structured Query Language) of large language model
By employing pre-execution static analysis and a performance feedback loop, and utilizing large language models and database query execution plans (QEPs) to automatically correct SQL queries, the system addresses logical errors and performance bottlenecks in enterprise applications, achieving efficient and reliable SQL generation.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-12-09
- Publication Date
- 2026-04-03
AI Technical Summary
Existing technologies cannot effectively identify and intercept logically flawed SQL queries in enterprise applications, and the generated queries are often unoptimized, leading to high resource consumption and latency.
By performing pre-execution static analysis and performance feedback closed-loop steps, the initial SQL statement is generated using a large language model. Logical correction and performance optimization are then performed by combining a logical anti-pattern rule base and a query execution plan (QEP), forming an automated closed loop.
It enables the automated generation and optimization of high-quality, enterprise-level SQL queries from natural language, ensuring correct SQL syntax, reliable logic, and efficient execution, while reducing manual intervention.
Smart Images
Figure CN121786049A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the fields of artificial intelligence and database technology, specifically to a method and system for correcting and generating natural language to SQL using a large language model. Background Technology
[0002] With the development of large language models, Text-to-SQL technology has become a key tool for data democratization. However, in enterprise applications, existing solutions face significant challenges:
[0003] Logical fallacies are highly concealed: Most mainstream frameworks employ an "execution-guided self-correction" mechanism, which corrects SQL by capturing database errors (such as syntax errors). However, many generated SQL statements are syntactically correct and execute successfully, but return erroneous data due to logical pitfalls (such as improper use of WHERE filtering after LEFT JOIN causing it to degenerate into INNER JOIN). Existing error feedback mechanisms cannot identify this situation of "successful execution but logical error".
[0004] Uncontrollable performance bottlenecks: Although the generated SQL queries may run, they are often unoptimized and may result in suboptimal execution paths (such as full table scans instead of index lookups). In enterprise environments with large amounts of data, such queries can lead to extremely high resource consumption and latency, and may even crash the database service.
[0005] Existing technologies lack an end-to-end solution that can both intercept logical errors upfront and optimize execution efficiency afterward. Summary of the Invention
[0006] The technical objective of this invention is to provide a correction method and generation system for converting natural language to SQL in a large language model. This method can greatly reduce manual intervention, ensure not only the correctness of SQL syntax, but also the reliability of logic and high efficiency of execution. By combining logical correctness with execution efficiency, it realizes the automated generation and optimization of high-quality, enterprise-level SQL queries from natural language.
[0007] The technical solution adopted by this invention to solve its technical problem is:
[0008] A method for correcting natural language to SQL conversion using a large language model, the implementation of which includes the following steps:
[0009] Receive natural language query requests and database schema information, and generate initial SQL statements using a large language model;
[0010] Pre-execution static analysis: The generated SQL statement is input into the static analysis module, and feature matching is performed based on the preset logical anti-pattern rule base; if an anti-pattern is matched, logical correction feedback is generated and rewriting is triggered;
[0011] Execution and performance analysis: After the SQL statement passes static analysis, it is submitted to the database engine for execution; if the execution is successful, the query execution plan is obtained.
[0012] Performance feedback closed-loop steps: parse the query execution plan and identify performance bottleneck indicators; if a performance bottleneck exists, generate performance optimization feedback and send it to the large language model for rewriting;
[0013] Output the final SQL statement that simultaneously satisfies both logical validation rules and performance metrics requirements.
[0014] By performing pre-execution static analysis, logical anti-patterns are proactively identified and logical fallacies are eliminated before queries access the database.
[0015] By parsing the Query Execution Plan (QEP), the database's internal performance metrics are transformed into feedback signals, guiding the model to optimize query efficiency.
[0016] Through multi-agent collaboration, an automated closed loop of "logic correction" and "performance tuning" is achieved.
[0017] It not only ensures correct SQL syntax, but also guarantees reliable logic and efficient execution of Text-to-SQL generation.
[0018] Furthermore, the logical anti-patterns detected in the pre-execution static analysis step include at least one of the following:
[0019] An incorrect aggregate function was used in the retained table of the outer join operation;
[0020] The non-null filtering of non-retained tables in the WHERE clause of the outer join operation caused join degradation.
[0021] The NOT IN subquery involves columns that may contain NULL values.
[0022] Furthermore, in the performance feedback closed-loop step, identifying performance bottleneck indicators specifically includes:
[0023] Detect whether a full table scan operation exists and if the amount of data in the table exceeds a preset threshold;
[0024] Detect whether the join operation uses an inefficient join algorithm (such as an unindexed nested loop);
[0025] Compare the query optimizer's estimated number of rows with the actual number of rows scanned to detect any statistical bias.
[0026] Furthermore, the specific implementation process of this method is as follows:
[0027] S1, Initial Generation Phase:
[0028] User inputs natural language questions and database schema;
[0029] The generator agent generates an initial SQL query based on a large language model;
[0030] S2, Pre-execution static analysis loop (logic correction):
[0031] The generated SQL is passed to the pre-execution static analysis module;
[0032] The pre-execution static analysis module does not connect to the database, but scans the code based on its built-in "SQL logic anti-pattern rule library";
[0033] The detection content includes: use of aggregate functions (such as COUNT(*) in outer joins), implicit type conversion, pseudo-join conditions that lead to Cartesian product, and null value (NULL) logical traps, etc.
[0034] Feedback mechanism: If an anti-pattern is detected, a structured logical error description is generated (e.g., "An anti-pattern detected: LEFTJOIN followed by filtering of the right table causes join degradation") and sent to the optimizer agent for rewriting until the check is passed;
[0035] S3. Execution and runtime verification:
[0036] The SQL, after static analysis, is sent to the evaluator and execution module for execution;
[0037] If execution fails (e.g., a runtime error such as a table not existing), the error message is sent to the optimizer agent for correction.
[0038] S4. Performance optimization loop based on execution plan (performance correction):
[0039] If the query is executed successfully, the system records the execution time; if it exceeds a preset threshold, the performance analysis module is triggered.
[0040] The performance analysis module obtains the actual execution plan (QEP) for the query using database commands (such as EXPLAIN ANALYZE).
[0041] The performance analysis module analyzes the following: parsing the QEP tree and identifying high-cost operations, including: full table scan, inefficient join algorithms (such as using Nested Loop Join on a large table), index misses, and excessively large deviations in the estimated number of rows.
[0042] Feedback mechanism: Generate performance optimization suggestions in natural language (e.g., "Suggestion: A full table scan of the Transactions table was detected. It is recommended to rewrite the query to utilize the Time index") and send them to the optimizer agent;
[0043] S5. Correction and Iteration:
[0044] Based on the received feedback (logical warnings, runtime errors, or performance suggestions), the optimizer agent rewrites the SQL using specific prompt word templates and sends the new version back to the process starting point (static analysis module), forming a closed loop.
[0045] This invention also claims a system for generating natural language to SQL from a large language model, comprising:
[0046] Generator agent module: Used to receive user commands and generate SQL;
[0047] Pre-execution static analysis module: Configured with a logical anti-pattern rule base, used to detect logical defects in SQL without connecting to the database;
[0048] Execution and performance monitoring module: Used to execute SQL and extract the Query Execution Plan (QEP) returned by the database;
[0049] Optimizer agent module: Used to receive logical feedback from the static analysis module and performance feedback from the execution module, driving the large language model to iteratively correct the SQL.
[0050] Furthermore, the pre-execution static analysis module detects at least one of the following logical anti-patterns:
[0051] An incorrect aggregate function was used in the retained table of the outer join operation;
[0052] The non-null filtering of non-retained tables in the WHERE clause of the outer join operation caused join degradation.
[0053] The NOT IN subquery involves columns that may contain NULL values.
[0054] Furthermore, the execution and performance monitoring module parses the query execution plan and identifies performance bottleneck indicators; if a performance bottleneck exists, it generates performance optimization feedback and sends it to the large language model for rewriting; the specific performance bottleneck indicators include:
[0055] Detect whether a full table scan operation exists and if the amount of data in the table exceeds a preset threshold;
[0056] Detect whether the join operation uses an inefficient join algorithm (such as an unindexed nested loop);
[0057] Compare the query optimizer's estimated number of rows with the actual number of rows scanned to detect any statistical bias.
[0058] Furthermore, the system implements the following process for generating Text-to-SQL:
[0059] S1, Initial Generation Phase:
[0060] User inputs natural language questions and database schema;
[0061] The generator proxy module generates an initial SQL query based on a large language model;
[0062] S2, Pre-execution static analysis loop (logic correction):
[0063] The generated SQL is passed to the pre-execution static analysis module;
[0064] The pre-execution static analysis module does not connect to the database, but scans the code based on its built-in "SQL logic anti-pattern rule library";
[0065] The detection content includes: use of aggregate functions (such as COUNT(*) in outer joins), implicit type conversion, pseudo-join conditions that lead to Cartesian product, and null value (NULL) logical traps, etc.
[0066] Feedback mechanism: If an anti-pattern is detected, a structured logical error description is generated (e.g., "An anti-pattern detected: LEFTJOIN followed by filtering of the right table causes join degradation") and sent to the optimizer agent for rewriting until the check is passed;
[0067] S3. Execution and runtime verification:
[0068] The SQL, after static analysis, is sent to the evaluator and execution module for execution;
[0069] If execution fails (e.g., a runtime error such as a table not existing), the error message is sent to the optimizer agent for correction.
[0070] S4. Performance optimization loop based on execution plan (performance correction):
[0071] If the query is executed successfully, the system records the execution time; if it exceeds a preset threshold, performance analysis is triggered.
[0072] The performance analysis involves obtaining the actual execution plan (QEP) for the query using database commands (such as EXPLAIN ANALYZE).
[0073] Analysis content: Parse the QEP tree to identify high-cost operations, specifically including: Full Table Scan, inefficient join algorithms (such as using Nested Loop Join on a large table), index miss, and excessively large deviation in the estimated number of rows;
[0074] Feedback mechanism: Generate performance optimization suggestions in natural language (e.g., "Suggestion: A full table scan of the Transactions table was detected. It is recommended to rewrite the query to utilize the Time index") and send them to the optimizer agent;
[0075] S5. Correction and Iteration:
[0076] Based on the received feedback (logical warnings, runtime errors, or performance suggestions), the optimizer agent rewrites the SQL using specific prompt word templates and sends the new version back to the process starting point (static analysis module), forming a closed loop.
[0077] The present invention also claims a correction device for converting natural language to SQL in a large language model, comprising: at least one memory and at least one processor;
[0078] The at least one memory is used to store a machine-readable program;
[0079] The at least one processor is used to call the machine-readable program to implement the above method.
[0080] The present invention also claims a computer-readable medium storing computer instructions that, when executed by a processor, enable the implementation of the above-described method.
[0081] Compared with existing technologies, the correction method and generation system for converting natural language to SQL using a large language model, as proposed in this invention, have the following advantages:
[0082] 1. High reliability: Through upfront static logical analysis, logical errors caused by "illusions" are avoided, greatly improving the accuracy of data query results.
[0083] 2. High performance guarantee: Performance optimization is built into the generation process to ensure that the generated SQL has the characteristics of low latency and low resource consumption in the production environment, reducing the enterprise's computing power cost.
[0084] 3. Automation and closed loop: No manual intervention is required for database administrators, achieving fully automated conversion from natural language to high-performance SQL. Attached Figure Description
[0085] Figure 1This is a flowchart illustrating the method for correcting natural language to SQL using a large language model, as provided in this embodiment of the invention. Detailed Implementation
[0086] The present invention will be further described below with reference to specific embodiments.
[0087] This invention provides a method for correcting natural language to SQL conversion in a large language model. Based on a dual feedback mechanism, the method achieves Text-to-SQL conversion. The implementation of this method includes the following steps:
[0088] 1. Receive natural language query requests and database schema information, and generate initial SQL statements using a large language model;
[0089] 2. Pre-execution static analysis: The generated SQL statement is input into the static analysis module, and feature matching is performed based on the preset logical anti-pattern rule base; if an anti-pattern is matched, logical correction feedback is generated and rewriting is triggered;
[0090] 3. Execution and Performance Analysis: After the SQL statement passes static analysis, it is submitted to the database engine for execution; if the execution is successful, the query execution plan is obtained.
[0091] 4. Performance feedback closed-loop steps: Analyze the query execution plan and identify performance bottleneck indicators; if a performance bottleneck exists, generate performance optimization feedback and send it to the large language model for rewriting;
[0092] 5. Output the final SQL statement that simultaneously meets the logical verification rules and performance indicator requirements.
[0093] The logical anti-patterns detected in the pre-execution static analysis step include at least one of the following:
[0094] An incorrect aggregate function was used in the retained table of the outer join operation;
[0095] The non-null filtering of non-retained tables in the WHERE clause of the outer join operation caused join degradation.
[0096] The NOT IN subquery involves columns that may contain NULL values.
[0097] In the performance feedback closed-loop step, identifying performance bottleneck indicators specifically includes:
[0098] Detect whether a full table scan operation exists and if the amount of data in the table exceeds a preset threshold;
[0099] Detect whether the join operation uses an inefficient join algorithm (such as an unindexed nested loop);
[0100] Compare the query optimizer's estimated number of rows with the actual number of rows scanned to detect any statistical bias.
[0101] The key innovation of this method lies in:
[0102] Static anti-pattern matching: Applying code review techniques from software engineering to intermediate state checks in Text-to-SQL generation.
[0103] QEP-driven optimization: For the first time, the database kernel-level Query Execution Plan (QEP) is parsed into prompt words that can be understood by a large language model, serving as reinforcement learning signals.
[0104] By performing pre-execution static analysis, logical anti-patterns are proactively identified and logical fallacies are eliminated before queries access the database.
[0105] By parsing the Query Execution Plan (QEP), the database's internal performance metrics are transformed into feedback signals, guiding the model to optimize query efficiency.
[0106] Through multi-agent collaboration, an automated closed loop of "logic correction" and "performance tuning" is achieved.
[0107] This method not only ensures correct SQL syntax, but also guarantees reliable logic and efficient execution of Text-to-SQL generation.
[0108] Combined with appendix Figure 1 As shown, the specific implementation process of this method is as follows:
[0109] S1, Initial Generation Phase:
[0110] Users input natural language questions and database schemas.
[0111] The generator agent generates an initial SQL query based on a large language model.
[0112] S2, Pre-execution static analysis loop (logic correction):
[0113] The generated SQL is passed to the pre-execution static analysis module.
[0114] The pre-execution static analysis module does not connect to the database; instead, it scans the code based on its built-in "SQL logic anti-pattern rule library".
[0115] The detection includes: use of aggregate functions (such as COUNT(*) in outer joins), implicit type conversions, pseudo-join conditions that lead to Cartesian products, and null value (NULL) logical traps.
[0116] Feedback mechanism: If an anti-pattern is detected, a structured logical error description is generated (e.g., "Anti-pattern detected: LEFTJOIN followed by filtering of the right table causes join degradation") and sent to the optimizer agent for rewriting until the check is passed.
[0117] S3. Execution and runtime verification:
[0118] The SQL obtained through static analysis is sent to the evaluator and execution module for execution.
[0119] If execution fails (e.g., a runtime error such as a table not existing), the error message is sent to the optimizer agent for correction.
[0120] S4. Performance optimization loop based on execution plan (performance correction):
[0121] If the query is executed successfully, the system records the execution time; if it exceeds a preset threshold, the performance analysis module is triggered.
[0122] The performance analysis module obtains the actual execution plan (QEP) for the query using database commands such as EXPLAIN ANALYZE.
[0123] The performance analysis module analyzes the following: parsing the QEP tree and identifying high-cost operations, including: full table scan, inefficient join algorithms (such as using Nested Loop Join on a large table), index misses, and excessively large deviations in the estimated number of rows.
[0124] Feedback mechanism: Generate performance optimization suggestions in natural language (such as "Suggestion: A full table scan of the Transactions table was detected. It is recommended to rewrite the query to utilize the Time index") and send them to the optimizer agent.
[0125] S5. Correction and Iteration:
[0126] Based on the received feedback (logical warnings, runtime errors, or performance suggestions), the optimizer agent rewrites the SQL using specific prompt word templates and sends the new version back to the process starting point (static analysis module), forming a closed loop.
[0127] The following is an example of the code implementation for this method:
[0128]
[0129]
[0130] To illustrate the application of this method in more detail, specific application examples are provided below.
[0131] Example 1:
[0132] In a sales data query system of an e-commerce company, a user entered a natural language question: "List the total order amount of all registered customers in 2023, including those customers who did not place any orders."
[0133] Generator Agent: Initial SQL statement generation: LEFTJOIN was used to connect the customer table and the order table, but a filter condition of OrderDate='2023' was added to the WHERE clause.
[0134] Pre-execution static analysis module: When scanning the code, it identifies "anti-pattern: outer join degradation" based on the rule base (that is, after joining the retained tables, the non-retained table fields are filtered for non-null, causing LEFTJOIN to be equivalent to INNERJOIN, resulting in the loss of data of customers without orders).
[0135] Optimizer agent: Upon receiving feedback of "logic defect", it automatically moves the year filter condition from the WHERE clause to the ON join condition.
[0136] The execution and performance monitoring module runs the corrected SQL, confirms successful execution and eliminates performance issues such as full table scans, and finally outputs logically correct query results.
[0137] Example 2:
[0138] In a bank's transaction history inquiry service, a user entered a natural language question: "Inquire about large transfer records exceeding 500,000 yuan in the past 24 hours."
[0139] Generator Agent: Generates standard SQL queries and passes the logical checks of the pre-execution static analysis module.
[0140] Execution and performance monitoring module: When the SQL was executed in the sandbox, although it ran successfully, the execution time was detected to exceed the preset threshold (e.g., 2 seconds).
[0141] Performance Analysis Module: Extracts the Query Execution Plan (QEP) returned by the database, identifies that a full table scan (Full Table Scan) occurred on the Transactions table, and that the database optimizer did not select the expected index due to statistical bias.
[0142] The optimizer agent received a "performance optimization suggestion: full table scan is too costly, it is recommended to use time indexes" and then rewrote the SQL (e.g., add forced index hints or optimize the time range syntax). After execution, the query time was reduced to less than 100 milliseconds.
[0143] Example 3:
[0144] On a large log analysis platform, a user inputs a natural language question: "Retrieve all transaction records with a transaction amount greater than 1 million."
[0145] The system generates the SQL statement: 'SELECT * FROM Logs WHERE amount > 1000000'. This statement has a simple logical structure and successfully passes the check of the pre-execution static analysis module.
[0146] The execution module ran the query and found that the time taken was abnormal, exceeding the preset threshold (e.g., 2 seconds).
[0147] The performance analysis module intervened and obtained the execution plan through the EXPLAIN command. It was diagnosed that because the amount field was defined as VARCHAR type in the database, and the query used numerical comparison, an implicit type conversion occurred, which prevented the index from being hit.
[0148] Based on the performance feedback that "implicit type conversion caused index failure", the optimizer agent corrected the SQL to a character comparison form (or suggested converting field types), and the system finally output the SQL statement that met the performance requirements.
[0149] This invention also provides a natural language to SQL generation system based on a large language model. This system constructs a multi-agent system comprising a generator, an evaluator, and an optimizer, and designs two nested feedback loops:
[0150] Inner loop (logical layer): Before execution, static code scanning is performed on the SQL, and logical errors are intercepted based on the rule base.
[0151] Outer ring (performance layer): After execution, the query execution plan of the database is obtained and analyzed, and optimization is performed based on performance metrics.
[0152] The system includes:
[0153] Generator agent module: Used to receive user commands and generate SQL;
[0154] Pre-execution static analysis module: Configured with a logical anti-pattern rule base, used to detect logical defects in SQL without connecting to the database;
[0155] Execution and performance monitoring module: Used to execute SQL and extract the Query Execution Plan (QEP) returned by the database;
[0156] Optimizer agent module: Used to receive logical feedback from the static analysis module and performance feedback from the execution module, driving the large language model to iteratively correct the SQL.
[0157] The pre-execution static analysis module detects at least one of the following logical anti-patterns:
[0158] An incorrect aggregate function was used in the retained table of the outer join operation;
[0159] The non-null filtering of non-retained tables in the WHERE clause of the outer join operation caused join degradation.
[0160] The NOT IN subquery involves columns that may contain NULL values.
[0161] The execution and performance monitoring module parses the query execution plan and identifies performance bottleneck indicators. If a performance bottleneck exists, it generates performance optimization feedback and sends it to the large language model for rewriting. The specific performance bottleneck indicators include:
[0162] Detect whether a full table scan operation exists and if the amount of data in the table exceeds a preset threshold;
[0163] Detect whether the join operation uses an inefficient join algorithm (such as an unindexed nested loop);
[0164] Compare the query optimizer's estimated number of rows with the actual number of rows scanned to detect any statistical bias.
[0165] The process for generating Text-to-SQL in this system is as follows:
[0166] S1, Initial Generation Phase:
[0167] Users input natural language questions and database schemas.
[0168] The generator proxy module generates an initial SQL query based on a large language model.
[0169] S2, Pre-execution static analysis loop (logic correction):
[0170] The system will pass the generated SQL to the pre-execution static analysis module.
[0171] The pre-execution static analysis module does not connect to the database; instead, it scans the code based on its built-in "SQL logic anti-pattern rule library".
[0172] The detection includes: use of aggregate functions (such as COUNT(*) in outer joins), implicit type conversions, pseudo-join conditions that lead to Cartesian products, and null value (NULL) logical traps.
[0173] Feedback mechanism: If an anti-pattern is detected, a structured logical error description is generated (e.g., "Anti-pattern detected: LEFTJOIN followed by filtering of the right table causes join degradation") and sent to the optimizer agent for rewriting until the check is passed.
[0174] S3. Execution and runtime verification:
[0175] The SQL obtained through static analysis is sent to the evaluator and execution module for execution.
[0176] If execution fails (e.g., a runtime error such as a table not existing), the error message is sent to the optimizer agent for correction.
[0177] S4. Performance optimization loop based on execution plan (performance correction):
[0178] If the query is executed successfully, the system records the execution time; if it exceeds a preset threshold, performance analysis is triggered.
[0179] The performance analysis is performed by obtaining the actual execution plan (QEP) of the query through database commands (such as EXPLAIN ANALYZE).
[0180] Analysis content: Parse the QEP tree to identify high-cost operations, specifically including: Full Table Scan, inefficient join algorithms (such as using Nested Loop Join on large tables), index misses, and excessively large errors in estimating the number of rows.
[0181] Feedback mechanism: Generate performance optimization suggestions in natural language (such as "Suggestion: A full table scan of the Transactions table was detected. It is recommended to rewrite the query to utilize the Time index") and send them to the optimizer agent.
[0182] S5. Correction and Iteration:
[0183] Based on the received feedback (logical warnings, runtime errors, or performance suggestions), the optimizer agent rewrites the SQL using specific prompt word templates and sends the new version back to the process starting point (static analysis module), forming a closed loop.
[0184] This system implements a multi-stage, dual-closed-loop correction framework, comprising two core feedback loops: 1. A pre-execution static analysis loop, which proactively identifies and corrects logical defects using an anti-pattern rule base before SQL execution; 2. A performance optimization loop based on the execution plan, which parses the database query execution plan (QEP) after successful SQL execution and generates optimization feedback for performance issues such as full table scans and inefficient joins. By combining logical correctness with execution efficiency, this system achieves automated generation and tuning of high-quality, enterprise-level SQL queries from natural language.
[0185] This invention also provides a correction device for converting natural language to SQL using a large language model, comprising: at least one memory and at least one processor;
[0186] The at least one memory is used to store a machine-readable program;
[0187] The at least one processor is used to call the machine-readable program to implement the large language model natural language to SQL correction method described in the above embodiments.
[0188] This invention also provides a computer-readable medium storing computer instructions. When executed by a processor, the computer instructions cause the processor to perform the large language model natural language to SQL correction method described in the above embodiments. Specifically, a system or apparatus equipped with a storage medium storing software program code that implements the functions of any of the above embodiments can be provided, and the computer (or CPU or MPU) of the system or apparatus can read and execute the program code stored in the storage medium.
[0189] In this case, the program code read from the storage medium can itself implement the function of any of the above embodiments, and therefore the program code and the storage medium storing the program code constitute part of the present invention.
[0190] Examples of storage media used to provide program code include floppy disks, hard disks, magneto-optical disks, optical disks (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), magnetic tapes, non-volatile memory cards, and ROMs. Alternatively, program code can be downloaded from a server computer via a communication network.
[0191] Furthermore, it should be clear that not only can the program code read by the computer be executed, but also the operating system or other components operating on the computer can be instructed based on the program code to perform some or all of the actual operations, thereby realizing the function of any of the embodiments described above.
[0192] Furthermore, it is understood that the program code read from the storage medium is written to the memory set in the expansion board inserted into the computer or to the memory set in the expansion unit connected to the computer. Then, based on the instructions of the program code, the CPU or other components installed on the expansion board or expansion unit execute some and all of the actual operations, thereby realizing the function of any of the embodiments described above.
[0193] The present invention has been shown and described in detail above with reference to the accompanying drawings and preferred embodiments. However, the present invention is not limited to these disclosed embodiments. Based on the above embodiments, those skilled in the art will know that more embodiments of the present invention can be obtained by combining the code review methods in the different embodiments. These embodiments are also within the protection scope of the present invention.
Claims
1. A method for correcting natural language to SQL conversion using a large language model, characterized in that, The implementation of this method includes the following steps: Receive natural language query requests and database schema information, and generate initial SQL statements using a large language model; Pre-execution static analysis: The generated SQL statement is input into the static analysis module, and feature matching is performed based on the preset logical anti-pattern rule base; if an anti-pattern is matched, logical correction feedback is generated and rewriting is triggered; Execution and performance analysis: After the SQL statement passes static analysis, it is submitted to the database engine for execution; if the execution is successful, the query execution plan is obtained. Performance feedback closed-loop steps: parse the query execution plan and identify performance bottleneck indicators; if a performance bottleneck exists, generate performance optimization feedback and send it to the large language model for rewriting; Output the final SQL statement that simultaneously satisfies both logical validation rules and performance metrics requirements.
2. The method for correcting natural language to SQL conversion using a large language model according to claim 1, characterized in that, The logical anti-patterns detected in the pre-execution static analysis step include at least one of the following: An incorrect aggregate function was used in the reserved table of the outer join operation; The WHERE clause of the outer join operation performs non-null filtering on non-retained tables, resulting in join degradation. The NOT IN subquery involves columns that may contain NULL values.
3. The method for correcting natural language to SQL conversion using a large language model according to claim 1, characterized in that, In the performance feedback closed-loop step, identifying performance bottleneck indicators specifically includes: Detect whether a full table scan operation exists and if the amount of data in the table exceeds a preset threshold; Detect whether the connection operation uses an inefficient connection algorithm; Compare the query optimizer's estimated number of rows with the actual number of rows scanned to detect any statistical bias.
4. The method for correcting natural language to SQL conversion using a large language model according to claim 1, characterized in that... The specific implementation process of this method is as follows: S1, Initial Generation Phase: User inputs natural language questions and database schema; The generator agent generates an initial SQL query based on a large language model; S2, Pre-execution static analysis loop: The generated SQL is passed to the pre-execution static analysis module; The pre-execution static analysis module does not connect to the database, but scans the code based on its built-in "SQL logic anti-pattern rule library"; The detection content includes: aggregate function usage, implicit type conversion, pseudo-join conditions that lead to Cartesian product, and null value logic traps; Feedback mechanism: If an anti-pattern is found, a structured logical error description is generated and sent to the optimizer agent for rewriting until the check is passed; S3. Execution and runtime verification: The SQL, after static analysis, is sent to the evaluator and execution module for execution; If the execution fails, the error message is sent to the optimizer agent for correction. S4. Performance optimization loop based on execution plan: If the query is executed successfully, the system records the execution time; if it exceeds a preset threshold, the performance analysis module is triggered. The performance analysis module obtains the actual execution plan (QEP) for the query through database commands; The performance analysis module analyzes the following: parsing the QEP tree and identifying high-cost operations, specifically including: full table scan, inefficient join algorithm, index miss, and excessive deviation in the estimated number of rows; Feedback mechanism: Generate performance optimization suggestions in natural language and send them to the optimizer agent; S5. Correction and Iteration: Based on the feedback received, the optimizer agent rewrites the SQL using specific prompt word templates and sends the new version back to the start of the process, forming a closed loop.
5. A natural language to SQL generation system based on a large language model, characterized in that, include: Generator agent module: Used to receive user commands and generate SQL; Pre-execution static analysis module: Configured with a logical anti-pattern rule base, used to detect logical defects in SQL without connecting to the database; Execution and performance monitoring module: used to execute SQL and extract the query execution plan returned by the database; Optimizer agent module: Used to receive logical feedback from the static analysis module and performance feedback from the execution module, driving the large language model to iteratively correct the SQL.
6. The natural language to SQL generation system based on a large language model according to claim 1, characterized in that, The pre-execution static analysis module detects at least one of the following logical anti-patterns: An incorrect aggregate function was used in the reserved table of the outer join operation; The WHERE clause of the outer join operation performs non-null filtering on non-retained tables, resulting in join degradation. The NOT IN subquery involves columns that may contain NULL values.
7. The natural language to SQL generation system based on a large language model according to claim 1, characterized in that, The execution and performance monitoring module parses the query execution plan and identifies performance bottleneck indicators. If a performance bottleneck exists, generate performance optimization feedback and send it to the large language model for rewriting; The specific metrics for identifying performance bottlenecks include: Detect whether a full table scan operation exists and if the amount of data in the table exceeds a preset threshold; Detect whether the connection operation uses an inefficient connection algorithm; Compare the query optimizer's estimated number of rows with the actual number of rows scanned to detect any statistical bias.
8. The natural language to SQL generation system based on a large language model according to claim 1, characterized in that, The process for generating Text-to-SQL in this system is as follows: S1, Initial Generation Phase: User inputs natural language questions and database schema; The generator proxy module generates an initial SQL query based on a large language model; S2, Pre-execution static analysis loop: The generated SQL is passed to the pre-execution static analysis module; The pre-execution static analysis module does not connect to the database, but scans the code based on its built-in "SQL logic anti-pattern rule library"; The detection content includes: aggregate function usage, implicit type conversion, pseudo-join conditions that lead to Cartesian product, and null value logic traps; Feedback mechanism: If an anti-pattern is found, a structured logical error description is generated and sent to the optimizer agent for rewriting until the check is passed; S3. Execution and runtime verification: The SQL, after static analysis, is sent to the evaluator and execution module for execution; If the execution fails, the error message is sent to the optimizer agent for correction. S4. Performance optimization loop based on execution plan: If the query is executed successfully, the system records the execution time; if it exceeds a preset threshold, performance analysis is triggered. The performance analysis involves obtaining the actual execution plan (QEP) of the query through database commands. Analysis content: Parse the QEP tree to identify high-cost operations, specifically including: full table scan, inefficient join algorithm, index miss, and excessively large deviation in the estimated number of rows; Feedback mechanism: Generate performance optimization suggestions in natural language and send them to the optimizer agent; S5. Correction and Iteration: Based on the feedback received, the optimizer agent rewrites the SQL using specific prompt word templates and sends the new version back to the start of the process, forming a closed loop.
9. A correction device for converting natural language to SQL using a large language model, characterized in that, include: At least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is configured to invoke the machine-readable program to implement the method according to any one of claims 1 to 4.
10. A computer-readable medium, characterized in that, The computer-readable medium stores computer instructions that, when executed by a processor, enable the implementation of the method described in any one of claims 1 to 4.