Method for realizing multi-table (+) external connection based on openGauss

By performing semantic analysis and conversion processing on SQL in openGauss, multiple table (+) connections are realized, which solves the problem of multi-table out-of-table connections in the database, expands application scenarios and provides a query method that is compatible with Oracle.

CN118916387APending Publication Date: 2024-11-08广州海量数据库技术有限公司
View PDF 2 Cites 0 Cited by

Patent Information

Application Number
CN202410931571.0
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-12
Publication Date
2024-11-08

AI Technical Summary

Technical Problem

The existing openGauss database has not yet implemented multiple tables (+) external connections, which limits its application scenarios and applicable areas, and cannot meet the external connection needs of three tables and more than three tables.

Method used

By converting SQL containing (+) external connections in the semantic analysis stage, converting it into ansi SQL standard connection to realize multi-table (+) external connections. The specific steps include filtering subexpressions containing (+) and building Directed graphs, topological sorting and generation of ansi SQL standard connections.

Benefits of technology

Expand the application scenarios of external connection technology, realize the support of three tables and more than three tables in openGauss, expand its applicable areas, provide query methods compatible with Oracle databases, and simplify the migration of developers. and optimize work.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118916387A_ABST
    Figure CN118916387A_ABST
Patent Text Reader

Abstract

The invention relates to a method for realizing multi-table (+) external connection based on openGauss. According to the method, sql containing (+) external connection is converted into sql of an anni SQL standard connection in a semantic analysis stage, and then multi-table (+) external connection is realized in openGauss; the method specifically comprises the following steps: screening out all sub-expressions containing (+); respectively constructing a directed graph according to each sub-expression containing (+); performing topological sorting on the directed graph which does not contain the directed ring, finding a corresponding sub-expression containing (+) according to a topological sorting result, and re-sorting the sub-expressions; and generating an anni SQL standard connection according to a reordering result. By means of the method, (+) external connection of three or more tables can be achieved in openGauss, the application scene of the external connection technology is expanded, the application field of the external connection technology is expanded, and the application requirement of the external connection technology in the openGauss service scene can be better met.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of relational database management (RDBMS), and in particular to a method for realizing multi-table (+) outer connection based on openGauss. Background Art

[0002] Outer join is a technique for obtaining related data from two or more tables in a relational database. Outer join allows users to obtain data from tables when performing table join queries, even if there are no records in some tables that meet the join conditions.

[0003] There are three types of outer joins:

[0004] 1. LEFT OUTER JOIN: Returns all records in the left table, even if there are no records in the right table that meet the join condition. All data in the left table will be returned, and NULL will be returned if there is no matching record in the right table.

[0005] 2.RIGHT OUTER JOIN: Returns all records in the right table, even if there are no records in the left table that meet the join condition. All data in the right table will be returned, and NULL will be returned if there is no matching record in the left table.

[0006] 3. FULL OUTER JOIN: Returns all records in the left and right tables. When there are no records in the left or right table that meet the join conditions, the corresponding part will return NULL.

[0007] Oracle provides a dialect that can use the (+) symbol to implement outer joins. In the query statement, put (+) on one side of the join condition of the table that needs to return all records. For example, if a left outer join is required, add (+) after the join condition of the right table; if a right outer join is required, add (+) after the join condition of the left table.

[0008] Using outer joins can simplify query logic, improve query efficiency, and meet the needs of various business scenarios. However, up to now, there is no available method to implement multi-table (+) outer joins based on openGauss. Summary of the invention

[0009] In order to expand the application scenarios of outer join technology and expand its applicable fields, the present invention proposes for the first time how to implement multi-table (+) outer join based on openGauss. The multi-table (+) outer join syntax of Oracle based on openGauss adopted in the present invention can provide a query method compatible with the Oracle database, enabling developers to migrate and optimize applications more conveniently.

[0010] Take the following scenario as an example:

[0011] create table t1(id int,str varchar(50),a int);

[0012] create table t2(id int,str varchar(50),a int);

[0013] create table t3(id int,str varchar(50),a int);

[0014] insert into t1 values(1,'aaa',42);

[0015] insert into t1 values(11,'a1',43);

[0016] insert into t1 values(1,'bbb',60);

[0017] insert into t1 values(12,'b1',44);

[0018] insert into t1 values(3,'aaa',60);

[0019] insert into t1 values(13,'c1',45);

[0020] insert into t1 values(100,'b1',46);

[0021] select*from t1,t2,t3 where t1.id(+)=t2.id and t1.str(+)=t3.str andt2.a(+)=t3.a;

[0022] This SQL statement first performs a right outer join on t2 and t3, with the join condition being t2.a=t3.a, and then performs a right outer join on t1 and (the result of the right outer join on t2 and t3), with the join conditions being t1.id=t2.id and t1.str=t3.str.

[0023] If you use the ANSI SQL standard join method, the SQL is as follows: select * from t1 right join (t2 right join t3 on t2.a = t3.a) on t1.id = t2.id and t1.str = t3.str;

[0024] The final output is:

[0025]

[0026] (3 rows)

[0027] However, currently only (+) outer joins between two tables are implemented in the openGauss database, and (+) outer joins between three tables or more are not supported.

[0028] In view of the above problems, the present invention implements (+) outer join of three tables or more in openGauss through the following technical solution.

[0029] Specifically, the present invention provides a method for realizing multi-table (+) outer join based on openGauss. The method converts the SQL containing (+) outer join into SQL of ANSI SQL standard join in the semantic analysis stage, and then realizes multi-table (+) outer join in openGauss; Figure 1 As shown, the conversion process in this method includes the following steps:

[0030] S1. Filter out all sub-expressions containing (+);

[0031] S2. construct a directed graph according to each sub-expression containing (+) obtained by screening;

[0032] S3. For the constructed directed graph, check whether it contains a directed cycle, perform topological sorting on the directed graph that does not contain a directed cycle, find the corresponding sub-expression containing (+) according to the result of the topological sorting, and reorder the sub-expression;

[0033] S4. Generate an ANSI SQL standard connection based on the reordering results.

[0034] Furthermore, the method for implementing multi-table (+) outer join based on openGauss of the present invention, in step S1, of filtering out all sub-expressions containing (+), includes: using and / or as separators to filter out all sub-expressions containing (+) in the where condition.

[0035] Furthermore, the directed graph construction rule described in step S2 of the method for implementing multi-table (+) outer join based on openGauss of the present invention is: for tables that exist (+), a directed edge is established to all tables that do not exist (+).

[0036] Furthermore, the method for implementing multi-table (+) outer join based on openGauss of the present invention further comprises in step S3: if the constructed directed graph contains a directed cycle, it is determined to be a syntax error and an error is reported.

[0037] Furthermore, the method for implementing multi-table (+) outer join based on openGauss in the present invention, in step S4, generating ansi SQL standard join according to the reordering result, comprises:

[0038] (1) If there is a join condition between two tables, use outer join right join, and the table containing (+) is used as the left table of right join;

[0039] (2) If there is no join condition between the two tables, use inner join on 1=1.

[0040] Further, the method for implementing multi-table (+) outer join based on openGauss of the present invention comprises the following steps S4: for t1.col1(+)=t2.col2+t3.col3, if the result of topological sorting is t3, t2, t1, at this time, firstly generate ansi SQL standard join with t3 and t2, when there is no join condition between t3 and t2, the first generated join is t2 inner joint3 on 1=1; record t2 inner join t3 on 1=1 as "t23", then generate ansi SQL standard join again with "t23" and t1, when there is a join condition t1.col1(+)=t2.col2+t3.col3 between t1 and "t23", the table with (+) is t1, and the next generated join is t1 right join "t23" on t1.col1=t2.col2+t3.col3; the finally generated ansi SQL standard join is t1 right join (t2inner join t3 on 1=1) on t1.col1=t2.col2+t3.col3.

[0041] On the other hand, the present invention also provides a system for implementing multi-table (+) outer joins based on openGauss. The system converts the SQL containing (+) outer joins into SQL of ANSI SQL standard joins during the semantic analysis stage, and then implements multi-table (+) outer joins in openGauss. The system implements the steps of the method for implementing multi-table (+) outer joins based on openGauss when running.

[0042] In addition, the present invention also provides a computer-readable storage medium, on which a computer program is stored. When the program is executed by a processor, the steps of the method for realizing multi-table (+) outer join based on openGauss are implemented.

[0043] In summary, the method for implementing multi-table (+) outer join based on openGauss in the present invention converts the SQL containing (+) outer join into the SQL of ANSI SQL standard join in the semantic analysis stage, and finally implements the (+) outer join of three tables or more in openGauss. This method expands the application scenarios of outer join technology and expands its applicable fields, so that it can better meet the application requirements in openGauss business scenarios. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] In order to more clearly illustrate the technical solution of the present invention, the following briefly introduces the drawings required for use in the description of the present invention. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without paying any creative work.

[0045] Figure 1 The figure is a flow chart of the overall implementation of the method of the present invention. DETAILED DESCRIPTION

[0046] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments, and the present invention can also be implemented or applied through other different specific implementation methods. The details in this specification can also be modified or changed in various ways based on different viewpoints and applications without departing from the spirit of the present invention.

[0047] At the same time, it should be understood that the protection scope of the present invention is not limited to the specific embodiments described below; it should also be understood that the terms used in the embodiments of the present invention are for describing specific embodiments rather than for limiting the protection scope of the present invention.

[0048] Example: A method for implementing multi-table (+) outer join based on openGauss

[0049] Take the following scenario as an example:

[0050] create table t1(id int,str varchar(50),a int);

[0051] create table t2(id int,str varchar(50),a int);

[0052] create table t3(id int,str varchar(50),a int);

[0053] insert into t1 values(1,'aaa',42);

[0054] insert into t1 values(11,'a1',43);

[0055] insert into t1 values(1,'bbb',60);

[0056] insert into t1 values(12,'b1',44);

[0057] insert into t1 values(3,'aaa',60);

[0058] insert into t1 values(13,'c1',45);

[0059] insert into t1 values(100,'b1',46);

[0060] select*from t1,t2,t3 where t1.id(+)=t2.id and t1.str(+)=t3.str andt2.a(+)=t3.a;

[0061] This SQL statement first performs a right outer join on t2 and t3, with the join condition being t2.a=t3.a, and then performs a right outer join on t1 and (the result of the right outer join on t2 and t3), with the join conditions being t1.id=t2.id and t1.str=t3.str.

[0062] If you use the ANSI SQL standard join method, the SQL is as follows: select * from t1 right join (t2 right join t3 on t2.a = t3.a) on t1.id = t2.id and t1.str = t3.str;

[0063] The final output is:

[0064]

[0065] (3 rows)

[0066] However, currently only (+) outer joins between two tables are implemented in the openGauss database, and (+) outer joins between three tables or more are not supported.

[0067] In view of the above problems, the present invention implements the (+) outer join of three or more tables in openGauss through the following technical solution. The main implementation steps are as follows:

[0068] According to the above example, we found that since openGauss itself supports ANSI SQL standard connections, we only need to convert the outer connection with (+) into ANSI SQL standard connections. In this method, we convert it in the semantic analysis stage. The conversion process is as follows:

[0069] (1) Use and / or as separators to filter out all sub-expressions containing (+) in the where condition. For example, if the where condition is an expression such as t1.col1(+)=t2.col2 and t3.col3=t4.col4andt5.col5=t6.col6(+), then there are two sub-expressions containing (+), namely t1.col1(+)=t2.col2 and t5.col5=t6.col6(+). Then, only these two sub-expressions need to be processed, while t3.col3=t4.col4 does not need to be processed.

[0070] (2) Construct a directed graph based on each sub-expression containing (+). The construction rule is: for tables with (+), establish a directed edge to all tables without (+). For example, if t1.col1(+) = t2.col2, you need to establish a directed edge from t1->t2; and if t1.col1(+) = t2.col2 + t3.col3, you need to establish two directed edges, one from t1->t2 and the other from t1->t3.

[0071] (3) For the constructed directed graph, check whether there is a directed cycle in it. If there is a directed cycle, it is a syntax error and needs to be reported. If there is no directed cycle, perform topological sorting on it, find the corresponding sub-expression with (+) according to the result of topological sorting, and reorder the sub-expressions. For example, t1.col1(+)=t2.col2 and t2.col3(+)=t3.col4 and t3.col5(+)=t1.col6. The directed graph constructed by this expression has a directed cycle: t1->t2->t3->t1, so it is a syntax error and needs to be reported. Another example, t1.col1(+)=t2.col2 andt2.col3(+)=t3.col4 and t1.col5(+)=t3.col6, the directed graph constructed by this expression is t1->t2, t1->t3, t2->t3, there is no directed cycle, so it can be topologically sorted, and the result of topological sorting is t3, t2, t1, and then the sub-expressions are reordered according to this result: the first item is t3, but there is no sub-expression of t3.xx(+), so it is skipped; the second item is t2, and the only sub-expression with t2.xx(+) is t2.col3(+)=t3.col4, so it is placed at the front; the third item is t1, and the sub-expressions with t1.xx(+) are t1.col1(+)=t2.col2 and t1.col5(+)=t3.col6, so it can be placed at the back. The final sorted expressions are: t2.col3(+)=t3.col4 and t1.col1(+)=t2.col2 and t1.col5(+)=t3.col6.

[0072] (4) Generate an ANSI SQL standard inner / outer join based on the result of topological sorting. If there is a join condition between the two tables, use the outer join right join, and the table with (+) is used as the left table of the right join; if there is no join condition between the two tables, use the inner join on 1=1. For example, t1.col1(+)=t2.col2+t3.col3: the result of its topological sorting is t3, t2, t1, so first use t3 and t2 to generate an ANSI SQL standard join. Since there is no join condition between t3 and t2, the first generated join is t2inner join t3 on 1=1. Temporarily record t2 innerjoin t3 on 1=1 as "t23". Next, use "t23" and t1 to generate an ANSI SQL standard connection again. Since there is a connection condition t1.col1(+)=t2.col2+t3.col3 between t1 and "t23", and the table with (+) is t1, the next connection generated is t1 right join "t23" on t1.col1=t2.col2+t3.col3. The final generated ANSI SQL standard connection is: t1 right join (t2inner join t3 on 1=1) on t1.col1=t2.col2+t3.col3.

[0073] For the example mentioned above:

[0074] select*from t1,t2,t3 where t1.id(+)=t2.id and t1.str(+)=t3.str andt2.a(+)=t3.a;

[0075] The four-step process is as follows:

[0076] (1) Filter all sub-expressions containing (+), three in total: t1.id(+)=t2.id, t1.str(+)=t3.str, t2.a(+)=t3.a.

[0077] (2) Construct a directed graph based on these three sub-expressions: the directed graph is t1->t2, t1->t3, t2->t3.

[0078] (3) There are no directed cycles in this directed graph, so it can be topologically sorted, and the result is t3, t2, t1.

[0079] (4) Generate ANSI SQL standard joins based on the topological sorting results: first, t3 and t2, the result generated is t2right join t3 on t2.a=t3.a; next, (t3 t2) and t1, the result generated is t1 right join (t2right join t3 on t2.a=t3.a) on t1.id=t2.id and t1.str=t3.str.

[0080] After the above four steps, the select * from t1, t2, t3 where t1.id (+) = t2.id and t1.str (+) = t3.str and t2.a (+) = t3.a, a SQL statement with (+) outer join, was successfully transformed into the select * from t1 right join (t2 right join t3 on t2.a = t3.a) on t1.id = t2.id and t1.str = t3.str, a SQL statement with ANSI SQL standard join (in fact, these two SQL statements are equivalent). Finally, the (+) outer join of three or more tables was realized in openGauss.

[0081] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any technician familiar with the profession can make some changes or modifications to the technical contents disclosed above without departing from the scope of the technical solution of the present invention to obtain equivalent embodiments of equivalent changes. However, any simple modification, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solution of the present invention should be included in the scope of protection of the claims of the present invention.

Claims

1. A method for implementing multi-table (+) outer join based on openGauss, characterized in that: The method converts the SQL containing (+) outer join into the SQL of ANSI SQL standard join in the semantic analysis stage, and then realizes multi-table (+) outer join in openGauss. The conversion process comprises the following steps: S1. Filter out all sub-expressions containing (+); S2. construct a directed graph according to each sub-expression containing (+) obtained by screening; S3. For the constructed directed graph, check whether it contains a directed cycle, perform topological sorting on the directed graph that does not contain a directed cycle, find the corresponding sub-expression containing (+) according to the result of the topological sorting, and reorder the sub-expression; S4. Generate an ANSI SQL standard connection based on the reordering results.

2. The method for implementing multi-table (+) outer join based on openGauss according to claim 1, characterized in that: The step S1 of filtering out all sub-expressions containing (+) includes: using and / or as a separator to filter out all sub-expressions containing (+) in the where condition.

3. The method for implementing multi-table (+) outer join based on openGauss according to claim 1, characterized in that: The rule for constructing the directed graph described in step S2 is: for a table that has (+), establish a directed edge to all tables that do not have (+).

4. The method for implementing multi-table (+) outer join based on openGauss according to claim 1, characterized in that: Step S3 also includes: if the constructed directed graph contains a directed cycle, it is determined to be a syntax error and an error is reported.

5. The method for implementing multi-table (+) outer join based on openGauss according to claim 1, characterized in that: The step S4 of generating ansi SQL standard connection according to the reordering result includes: (1) If there is a join condition between the two tables, use the outer join right join, and the table containing (+) is the left table of the right join; (2) If there is no join condition between the two tables, use inner join on 1=1.

6. The method for implementing multi-table (+) outer join based on openGauss according to claim 5, characterized in that: Step S4 includes: for t1.col1(+)=t2.col2+t3.col3, if the result of its topological sorting is t3, t2, t1, at this time, first use t3 and t2 to generate ansi SQL standard connection, when there is no connection condition between t3 and t2, the first connection generated is t2 inner join t3 on 1=1; t2 inner join t3 on1=1 is recorded as "t23", and then "t23" and t1 are used to generate ansi SQL standard connection again. When there is a connection condition t1.col1(+)=t2.col2+t3.col3 between t1 and "t23", the table with (+) is t1, and the next connection generated is t1 right join "t23" on t1.col1=t2.col2+t3.col3; the final generated ansi SQL standard connection is t1 right join (t2 inner join t3 on 1=1)ont1.col1=t2.col2+t3.col3.

7. A system for implementing multi-table (+) outer join based on openGauss, characterized in that: The system converts the SQL containing (+) outer join into SQL of ANSI SQL standard join in the semantic analysis stage, and then implements multi-table (+) outer join in openGauss; when the system is running, the steps of the method for implementing multi-table (+) outer join based on openGauss described in any one of claims 1 to 6 are implemented.

8. A computer-readable storage medium having a computer program stored thereon, wherein when the program is executed by a processor, the steps of the method for implementing multi-table (+) outer join based on openGauss according to any one of claims 1 to 6 are implemented.

Citation Information

Patent Citations

  • Batch deletion method and system for expressions based on CAD system

    CN110502812A

  • Method and device for generating SQL (Structured Query Language) statement for report and electronic equipment

    CN115495508A