A database field reference rebinding resolution method, device and medium

Abstract

The application discloses a database field reference rebinding parsing method, equipment and medium, belongs to the field of database SQL semantic parsing, and the method comprises the steps of: in the SQL semantic parsing stage, when the field reference appears in the syntax tree, identifying the field reference, and determining the expression context where the field reference is located; according to the corresponding search order of the expression context, taking the parsing state of the current query block as the starting parsing level, and sequentially matching the source table field, the output alias and the grouping field of the field reference; if the matching result of the field reference is not obtained in the current query block, jump to the parent query block, and repeat the matching operation until all query blocks are traversed; when the field reference matches the field of the outer query block, performing rebinding processing on the field reference to generate a final expression node. Without changing the original parsing main frame, the application realizes alias parsing under multiple query blocks, aligns the database semantics and error behaviors.

Description

Technical Field

[0001] This application relates to the field of database SQL semantic parsing technology, and in particular to a database field reference rebinding parsing method, device and medium. Background Technology

[0002] PostgreSQL follows the standard SQL specification, and field lookups only traverse the underlying data tables of each query block. It does not recognize output aliases defined in the SELECT clause, while MySQL supports directly referencing SELECT aliases in HAVING and ORDER BY. The semantic behavior of the two is different.

[0003] In MySQL compatibility retrofit scenarios, the existing fixed-path field binding mechanism cannot identify aliases across query blocks when faced with multi-level nested subqueries. This leads to parsing conflicts when original table columns, SELECT aliases, and GROUP-BY grouping fields have the same name. Furthermore, the existing parsing logic lacks a unified candidate decision mechanism; after field parsing failure, it enters multiple fallback paths such as pseudo-columns and functions, resulting in inconsistent error behavior compared to MySQL. This causes some MySQL business SQL queries to execute abnormally after migration. Directly rewriting the existing parsing framework on a large scale would compromise the stability of the database kernel. Summary of the Invention

[0004] To address the technical problems described in the background, this application provides a database field reference rebinding parsing method, device, and medium. The method includes: during the SQL semantic parsing phase, when a field reference appears in the syntax tree, identifying the field reference and determining the expression context in which the field reference resides; based on the search order corresponding to the expression context, starting with the parsing state of the current query block as the initial parsing level, sequentially matching the source table field, output alias, and grouping field of the field reference; if no matching result is found for the field reference in the current query block, jumping to the parent query block and repeating the matching operation until all levels of query blocks are traversed; when the field reference matches a field in an outer query block, performing rebinding processing on the field reference to generate a final expression node.

[0005] In one example, based on the search order corresponding to the expression context, and taking the parsing state of the current query block as the starting parsing level, the field references are matched sequentially with the source table fields, output aliases, and grouping fields. Specifically, when it is determined to be a non-HAVING expression context, the source table fields within the current query block are matched first; if the source table fields fail to match, the output aliases and grouping fields of the current query block are matched; if the output aliases and grouping fields fail to match, the process jumps to the parent query block to continue the matching operation.

[0006] In one example, based on the search order corresponding to the expression context, and taking the parsing state of the current query block as the starting parsing level, the field references are matched sequentially against the source table fields, output aliases, and grouping fields. Specifically, when it is determined to be a HAVING expression context, the output aliases and grouping fields of the current query block are matched first; if the output aliases and grouping fields fail to match, the source table fields within the current query block are matched; if the source table fields fail to match, the process jumps to the parent query block to continue the matching operation.

[0007] In one example, when the field reference matches an output alias but does not match a grouping field, the method includes: determining the candidate type of the output alias, setting the user-defined output alias as a high-priority candidate, and setting the output name corresponding to the original column of the data table as a low-priority candidate; prioritizing the use of high-priority candidates for field binding, and using low-priority candidates to complete field binding if no high-priority candidates exist; if multiple different candidates with the same priority exist, the output field ambiguity error is identified.

[0008] In one example, when the field reference matches both the output alias and the grouping field, the method includes: comparing the expression corresponding to the output alias with the expression corresponding to the grouping field; if the expressions are identical, directly selecting the expression to complete the field binding; if the expressions are inconsistent and the current context is HAVING expression, outputting an ambiguity warning and selecting the expression corresponding to the grouping field for binding; if the two expressions are inconsistent and the current context is other than HAVING expression context, outputting a field ambiguity error and terminating the field parsing process.

[0009] In one example, when the field reference matches a field in the outer query block, a rebinding process is performed on the field reference to generate the final expression node. Specifically, this includes: extracting the source table name and source column name corresponding to the field from the expression node of the field in the outer query block; constructing a qualified field reference with table name and column name format based on the source table name and source column name; re-submitting the qualified field reference into the field reference parsing process for secondary parsing to obtain the corresponding expression intermediate node; and generating the final expression node based on the expression intermediate node.

[0010] In one example, when performing secondary resolution on a qualified field reference, the method further includes: locating the outer query block corresponding to the qualified field reference based on the hierarchical offset, and verifying the existence of a real physical column within the namespace of the outer query block; when the real physical column exists, if the qualified field reference is in the context of a HAVING expression and the outer query block has a grouping field, enabling secondary resolution mode, disabling the matching logic of explicitly outputting aliases, and only verifying the original table fields and the grouping fields.

[0011] In one example, the method also includes: when neither the actual physical column nor the outer query block grouping field exists, disabling the secondary parsing mode and outputting an error result consistent with MySQL-compatible semantics.

[0012] On the other hand, embodiments of this application provide a database field reference rebinding parsing device, including: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform any of the above-described database field reference rebinding parsing methods.

[0013] On the other hand, embodiments of this application provide a non-volatile computer storage medium for rebinding and resolving field references in a database, which stores computer-executable instructions capable of executing any of the above-mentioned methods for rebinding and resolving field references in a database.

[0014] The above-described technical solutions adopted in the embodiments of this application can achieve the following beneficial effects: This application, while retaining the original database parsing framework, supports direct reference to output aliases in HAVING and ORDER BY scenarios through context-driven dynamic lookup logic. It also adapts to multi-level nested query blocks, meeting MySQL compatibility semantic requirements and reducing the workload of migrating and rewriting business SQL. A candidate differentiation mechanism is implemented that prioritizes explicit aliases over original columns, while coordinating decision-making between output aliases and grouping fields to resolve parsing misjudgments and ambiguities caused by duplicate names, improving the accuracy of SQL semantic parsing. For references to restricted fields generated by outer rebinding, a secondary validation and error convergence mechanism is added to block unreasonable pseudo-column and whole-row references, ensuring consistent error behavior with MySQL and preventing unexpected parsing results. Attached Figure Description

[0015] To more clearly illustrate the technical solution of this application, some embodiments of this application will be described in detail below with reference to the accompanying drawings, in which: Figure 1 A flowchart illustrating a database field reference rebinding resolution method provided in this application embodiment; Figure 2 This is a schematic diagram of the structure of a database field reference rebinding resolution device provided in an embodiment of this application. Detailed Implementation

[0016] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0017] Some embodiments of this application will now be described in detail with reference to the accompanying drawings.

[0018] Figure 1 This is a flowchart illustrating a database field reference rebinding resolution method provided in this embodiment of the application. This method can be applied to different business domains. Certain input parameters or intermediate results in this process can be manually adjusted to help improve accuracy.

[0019] The analysis method involved in the embodiments of this application can be implemented by a terminal device or a server, and this application does not impose any special limitations on it. For ease of understanding and description, the following embodiments are all described in detail using a server as an example.

[0020] Based on this Figure 1 The process may include the following steps: S101: During the SQL semantic parsing phase, when a field reference appears in the syntax tree, the field reference is identified, and the expression context in which the field reference is located is determined.

[0021] In some embodiments of this application, the SQL statement first undergoes syntax analysis to generate a syntax tree, and then enters the semantic analysis stage. This step relies on the internal conversion function of field reference to complete the identification of column reference type field reference nodes in the syntax tree. The column reference is the field reference to be parsed.

[0022] After identifying a field reference, the parsing state corresponding to the current parsing environment is read to determine the expression context in which the field reference is located. The context type mainly includes HAVING expression context and non-HAVING expression context. The result of the context determination will directly determine the search order of subsequent field matching.

[0023] The parsing state here maintains the information of the current query block. Each query block contains its own target list, source table namespace, grouping information, HAVING expression information, and parent parsing state. The context recognition result will be passed down to the subsequent matching logic, providing a basis for distinguishing between the two different parsing paths: non-HAVING field parsing functions and HAVING reference parsing functions.

[0024] S102: Based on the search order corresponding to the expression context, and taking the parsing state of the current query block as the starting parsing level, the field references are sequentially matched against the source table fields, output aliases, and grouping fields.

[0025] In some embodiments of this application, if S101 determines that it is a non-HAVING expression context, the search logic corresponding to the non-HAVING field parsing function is executed, and the FROM source table field in the current query block is matched first; if the source table field fails to match, the target list and group list retrieval function is called to match the output alias and GROUP BY grouping field of the current query block.

[0026] During the matching of output aliases, a dual-priority matching mechanism of aliases and original column names is executed. Output aliases explicitly defined by the user through the AS keyword are set as high-priority candidates, while the output names corresponding to the original columns of the data table are set as low-priority candidates. Only when the alias flag of the target entry is true is it recognized as an explicit SELECT output alias. The default name automatically generated by the expression is not used as an explicit alias for high-priority matching.

[0027] High-priority candidates are prioritized for field binding. If no high-priority candidate exists, low-priority candidates are used to complete the field binding. If multiple different candidates with the same priority exist, a field ambiguity error is output.

[0028] When both the output alias and the grouping field are matched, the alias and GROUP BY collaborative decision-making mechanism is executed. The expression corresponding to the output alias is compared with the expression corresponding to the grouping field. If the expression content is the same, the expression is directly selected to complete the field binding. If the expression is different and the current context is HAVING expression, an ambiguity prompt is output and the expression corresponding to the grouping field is selected for binding. If the two expressions are different and the current context is an expression context other than HAVING expression context, an ambiguity error is output and the field parsing process is terminated.

[0029] If S101 is determined to be a HAVING expression context, the lookup logic corresponding to the HAVING reference resolution function is executed. Priority is given to matching the output aliases and grouping fields of the current query block. Similarly, the target list and grouping list retrieval functions are called to complete the alias priority judgment and alias-grouping field collaborative decision-making. When the output aliases and grouping fields fail to match, the source table fields in the current query block are then matched.

[0030] All the above matching processes start with the parsing status of the current query block as the initial parsing level for matching operations. After all matching at the current level is completed, the process proceeds to S103.

[0031] S103: If no matching result is found for the field referenced in the current query block, jump to the parent query block and repeat the matching operation until all levels of query blocks are traversed.

[0032] In some embodiments of this application, when all the source table fields, output aliases, and grouping fields of the current query block do not match the field reference, the parsing status corresponding to the parent query block saved in the current parsing status is obtained, the jump to the parent query block is completed, and the count of the upward backtracking levels is recorded.

[0033] After jumping to the parent query block, the entire matching logic corresponding to the context in S102 is reused to rematch the source table fields, output aliases and grouping fields under the parent query block in sequence. The jump and matching process is executed in a loop, and the parent query block chain is continuously traversed upwards until the parsing status points to null, which means that all levels of query blocks have been traversed.

[0034] During the upward search process, the non-HAVING context always maintains the matching order of source table fields first, while the HAVING context always maintains the matching order of output aliases and grouping fields first. The multi-level query block dynamic rebinding mechanism runs through this traversal process to realize field and alias recognition across query blocks. If no matching result is found after traversing all query blocks, the final output is an error message indicating that the column does not exist. If a field is successfully matched in a parent query block at a certain level, the process proceeds to S104.

[0035] S104: When the field reference matches a field in the outer query block, rebinding is performed on the field reference to generate the final expression node.

[0036] In some embodiments of this application, when a field of an outer query block is hit during the upward traversal of the parent query block, and the current context is HAVING while the condition of obtaining the table name to which the field belongs is met, the dynamic rebinding process is initiated. First, based on the variable type expression node of the field in the outer query block, the source table name and source column name corresponding to the field are extracted in reverse. Based on the source table name and source column name, a qualified field reference in the table name and column name format is constructed.

[0037] Subsequently, the restricted field reference is resubmitted into the column reference field reference resolution process for secondary resolution. In the secondary resolution stage, the outer query block corresponding to the restricted field reference is located based on the hierarchical offset. The existence of a real physical column is verified within the namespace of the outer query block. When the restricted field reference is in the context of a HAVING expression and the condition of a grouping field in the outer query block is met, the secondary resolution mode is activated, the matching logic of explicitly outputting aliases is hidden, and only the original table fields and grouping fields are verified, thereby achieving the avoidance of same-name aliases.

[0038] When neither the actual physical column nor the outer query block grouping field exists, the secondary parsing mode is turned off, a column non-existence error flag is set, pseudo-column matching, whole row reference matching, and function and column ambiguity fallback parsing path are blocked, and an unknown column error result with MySQL-compatible semantics is output, thus completing error convergence. After the secondary parsing is completed, the corresponding expression intermediate node is obtained, and the final expression node of the variable type is generated based on the expression intermediate node, thus completing the entire process of parsing and rebinding the field reference.

[0039] It should be noted that, although the embodiments in this application are based on... Figure 1 Steps S101 to S104 will be described sequentially, but this does not mean that steps S101 and S104 must be performed in a strict order. The reason this embodiment follows this order is... Figure 1 The order in which steps S101 to S104 are described is provided to facilitate understanding of the technical solutions of the embodiments of this application by those skilled in the art. In other words, in the embodiments of this application, the order of steps S101 to S104 can be appropriately adjusted according to actual needs.

[0040] Figure 2 A schematic diagram of a database field reference rebinding resolution device provided in this application embodiment includes: At least one processor; and, A memory that is communicatively connected to at least one processor; wherein, The memory stores instructions that can be executed by at least one processor, which enable at least one processor to perform any of the above-mentioned database field reference rebinding resolution methods.

[0041] Some embodiments of this application provide a non-volatile computer storage medium for rebinding and resolving field references in a database, which stores computer-executable instructions capable of executing any of the above-described methods for rebinding and resolving field references in a database.

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

[0043] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0044] Those skilled in the art will understand that the embodiments of this application can be provided as methods or apparatuses. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can be implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0045] This application is described with reference to methods, apparatus (systems) according to embodiments of this application.

[0046] For example, in a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0047] Memory may include non-persistent storage in computer-readable media, random access memory (RAM), and non-volatile memory such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0048] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0049] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

[0050] The above are merely embodiments of this application and are not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, or improvements made within the technical principles of this application should fall within the protection scope of this application.

Claims

1. A database field reference rebinding resolution method, characterized in that, The method includes: During the SQL semantic parsing phase, when a field reference appears in the syntax tree, the field reference is identified, and the expression context in which the field reference is located is determined. Based on the search order corresponding to the expression context, and taking the parsing state of the current query block as the starting parsing level, the field references are matched sequentially with the source table fields, output aliases, and grouping fields; If no matching result is found for the field referenced in the current query block, jump to the parent query block and repeat the matching operation until all levels of query blocks are traversed; When the field reference matches a field in the outer query block, the field reference is rebounded to generate the final expression node.

2. The method according to claim 1, characterized in that, Based on the search order corresponding to the expression context, and taking the parsing state of the current query block as the starting parsing level, the field references are matched sequentially with the source table fields, output aliases, and grouping fields, specifically including: When the context is determined to be a non-HAVING expression, the source table fields within the current query block are matched first. If no match is found in the source table fields, the output aliases and grouping fields of the current query block are matched. If the output alias and grouping fields fail to match, jump to the parent query block to continue the matching operation.

3. The method according to claim 1, characterized in that, Based on the search order corresponding to the expression context, and taking the parsing state of the current query block as the starting parsing level, the field references are matched sequentially with the source table fields, output aliases, and grouping fields, specifically including: When the context is determined to be a HAVING expression, the output alias and grouping fields of the current query block are matched first. If the output alias and grouping field fail to match, match the source table field within the current query block; If the source table field fails to match, jump to the parent query block to continue the matching operation.

4. The method according to claim 1, characterized in that, When the field reference matches an output alias but does not match a grouping field, the method includes: Determine the candidate types for output aliases, set user-defined output aliases as high-priority candidates, and set the output names corresponding to the original columns of the data table as low-priority candidates; Prioritize using high-priority candidates for field binding; if no high-priority candidates exist, use low-priority candidates to complete the field binding. If there are multiple different candidates with the same priority, the output field will be ambiguous.

5. The method according to claim 1, characterized in that, When the field reference matches an output alias and also matches a grouping field, the method includes: Compare the expression corresponding to the output alias with the expression corresponding to the grouping field; If the expressions are identical, the expression can be used directly to complete the field binding; If the expressions are inconsistent and the current context is HAVING expression, output ambiguity hints and select the expression corresponding to the grouping field for binding; If the two expressions are inconsistent and are in a context other than the HAVING expression context, the output field ambiguity error will be detected, and the field parsing process will be terminated.

6. The method according to claim 1, characterized in that, When the field reference matches a field in the outer query block, a rebinding process is performed on the field reference to generate the final expression node, specifically including: Based on the expression nodes of the outer query block fields, extract the source table name and source column name corresponding to the fields in reverse; Based on the source table name and source column name, construct a qualified field reference with table name and column name format; The restricted field reference is re-entered into the field reference parsing process for secondary parsing to obtain the corresponding intermediate node of the expression. The final expression node is generated based on the intermediate nodes of the expression.

7. The method according to claim 6, characterized in that, When performing secondary resolution on qualified field references, the method further includes: The outer query block is referenced by the specified field based on the hierarchical offset, and the existence of the real physical column is verified within the namespace of the outer query block. When the actual physical column exists, if the referenced field is in the context of a HAVING expression and the outer query block has a grouping field, the secondary parsing mode is enabled, the matching logic for explicitly outputting aliases is hidden, and only the original table fields and the grouping fields are verified.

8. The method according to claim 7, characterized in that, The method further includes: When neither the actual physical column nor the outer query block grouping field exists, the secondary parsing mode is turned off, and an error result consistent with MySQL-compatible semantics is output.

9. A database field reference rebinding resolution device, characterized in that, include: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, which, when executed by the at least one processor, enables the at least one processor to perform a database field reference rebinding resolution method as described in any one of claims 1-8.

10. A database field reference rebinding resolution storage medium, storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of executing the database field reference rebinding resolution method according to any one of claims 1-8.

Images