You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2022/11/25 09:09:02 UTC
[shardingsphere] branch master updated: Optimize MySQL, PostgreSQL and openGauss natural join sql parse (#22408)
This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 881d0d49eed Optimize MySQL, PostgreSQL and openGauss natural join sql parse (#22408)
881d0d49eed is described below
commit 881d0d49eedff8d5bcfa2afe373af6af8f1fdc6d
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Fri Nov 25 17:08:55 2022 +0800
Optimize MySQL, PostgreSQL and openGauss natural join sql parse (#22408)
---
.../statement/impl/MySQLStatementSQLVisitor.java | 1 +
.../impl/OpenGaussStatementSQLVisitor.java | 6 +-
.../impl/PostgreSQLStatementSQLVisitor.java | 6 +-
.../segment/generic/table/JoinTableSegment.java | 2 +
.../asserts/segment/table/TableAssert.java | 1 +
.../segment/impl/table/ExpectedJoinTable.java | 3 +
.../src/main/resources/case/dml/select-join.xml | 160 +++++++++++++++++++++
.../dml/{select-relation.xml => select-join.xml} | 5 +
8 files changed, 182 insertions(+), 2 deletions(-)
diff --git a/sql-parser/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java b/sql-parser/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
index cb86d3dee1e..341ee708eec 100644
--- a/sql-parser/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
+++ b/sql-parser/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
@@ -1588,6 +1588,7 @@ public abstract class MySQLStatementSQLVisitor extends MySQLStatementBaseVisitor
result.setStartIndex(tableSegment.getStartIndex());
result.setStopIndex(ctx.stop.getStopIndex());
result.setJoinType(getJoinType(ctx));
+ result.setNatural(null != ctx.naturalJoinType());
TableSegment right = null != ctx.tableFactor() ? (TableSegment) visit(ctx.tableFactor()) : (TableSegment) visit(ctx.tableReference());
result.setRight(right);
return null != ctx.joinSpecification() ? visitJoinSpecification(ctx.joinSpecification(), result) : result;
diff --git a/sql-parser/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/impl/OpenGaussStatementSQLVisitor.java b/sql-parser/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/impl/OpenGaussStatementSQLVisitor.java
index 319236c2bf7..e64d9a84389 100644
--- a/sql-parser/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/impl/OpenGaussStatementSQLVisitor.java
+++ b/sql-parser/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/impl/OpenGaussStatementSQLVisitor.java
@@ -1158,6 +1158,7 @@ public abstract class OpenGaussStatementSQLVisitor extends OpenGaussStatementBas
TableSegment right = (TableSegment) visit(ctx.tableReference());
tableSegment.setRight(right);
tableSegment.setJoinType(getJoinType(ctx));
+ tableSegment.setNatural(null != ctx.naturalJoinType());
return null != ctx.joinQual() ? visitJoinQual(ctx.joinQual(), tableSegment) : tableSegment;
}
@@ -1194,7 +1195,10 @@ public abstract class OpenGaussStatementSQLVisitor extends OpenGaussStatementBas
if (null != ctx.LEFT()) {
return JoinType.LEFT.name();
}
- return JoinType.RIGHT.name();
+ if (null != ctx.RIGHT()) {
+ return JoinType.RIGHT.name();
+ }
+ return JoinType.INNER.name();
}
private JoinTableSegment visitJoinQual(final JoinQualContext ctx, final JoinTableSegment joinTableSource) {
diff --git a/sql-parser/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java b/sql-parser/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java
index 290d42b687e..a23fd578311 100644
--- a/sql-parser/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java
+++ b/sql-parser/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java
@@ -1125,6 +1125,7 @@ public abstract class PostgreSQLStatementSQLVisitor extends PostgreSQLStatementP
TableSegment right = (TableSegment) visit(ctx.tableReference());
tableSegment.setRight(right);
tableSegment.setJoinType(getJoinType(ctx));
+ tableSegment.setNatural(null != ctx.naturalJoinType());
return null != ctx.joinQual() ? visitJoinQual(ctx.joinQual(), tableSegment) : tableSegment;
}
@@ -1154,7 +1155,10 @@ public abstract class PostgreSQLStatementSQLVisitor extends PostgreSQLStatementP
if (null != ctx.LEFT()) {
return JoinType.LEFT.name();
}
- return JoinType.RIGHT.name();
+ if (null != ctx.RIGHT()) {
+ return JoinType.RIGHT.name();
+ }
+ return JoinType.INNER.name();
}
private static String getOutJoinType(final OuterJoinTypeContext ctx) {
diff --git a/sql-parser/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/JoinTableSegment.java b/sql-parser/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/JoinTableSegment.java
index 3ae5527efba..811f4c3af8e 100644
--- a/sql-parser/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/JoinTableSegment.java
+++ b/sql-parser/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/JoinTableSegment.java
@@ -41,6 +41,8 @@ public final class JoinTableSegment implements TableSegment {
private TableSegment left;
+ private boolean natural;
+
private String joinType;
private TableSegment right;
diff --git a/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/asserts/segment/table/TableAssert.java b/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/asserts/segment/table/TableAssert.java
index 67f8bc467e4..8c3a8376897 100644
--- a/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/asserts/segment/table/TableAssert.java
+++ b/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/asserts/segment/table/TableAssert.java
@@ -134,6 +134,7 @@ public final class TableAssert {
assertIs(assertContext, actual.getRight(), expected.getRight());
ExpressionAssert.assertExpression(assertContext, actual.getCondition(), expected.getOnCondition());
assertJoinType(assertContext, actual.getJoinType(), expected.getJoinType());
+ assertThat(assertContext.getText("Natural should be the same."), actual.isNatural(), is(expected.isNatural()));
assertThat(assertContext.getText("Column size assertion error: "), actual.getUsing().size(), is(expected.getUsingColumns().size()));
int count = 0;
for (ExpectedColumn each : expected.getUsingColumns()) {
diff --git a/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/cases/parser/domain/segment/impl/table/ExpectedJoinTable.java b/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/cases/parser/domain/segment/impl/table/ExpectedJoinTable.java
index 478a7ce269d..5bee91f59d7 100644
--- a/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/cases/parser/domain/segment/impl/table/ExpectedJoinTable.java
+++ b/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/cases/parser/domain/segment/impl/table/ExpectedJoinTable.java
@@ -49,4 +49,7 @@ public final class ExpectedJoinTable extends AbstractExpectedDelimiterSQLSegment
@XmlAttribute(name = "join-type")
private String joinType;
+
+ @XmlAttribute(name = "natural")
+ private boolean natural;
}
diff --git a/test/parser/src/main/resources/case/dml/select-join.xml b/test/parser/src/main/resources/case/dml/select-join.xml
index 13cfef09dd8..722368b63de 100644
--- a/test/parser/src/main/resources/case/dml/select-join.xml
+++ b/test/parser/src/main/resources/case/dml/select-join.xml
@@ -395,4 +395,164 @@
</column-item>
</order-by>
</select>
+
+ <select sql-case-id="select_natural_join" parameters="1">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <join-table join-type="INNER" natural="true">
+ <left>
+ <simple-table name="t_order" alias="o" start-index="14" stop-index="22" />
+ </left>
+ <right>
+ <simple-table name="t_order_item" alias="i" start-index="37" stop-index="50" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="52" stop-index="71">
+ <expr>
+ <binary-operation-expression start-index="58" stop-index="71">
+ <left>
+ <column name="order_id" start-index="58" stop-index="67">
+ <owner name="o" start-index="58" stop-index="58" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="71" stop-index="71" />
+ <parameter-marker-expression parameter-index="0" start-index="71" stop-index="71" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_natural_inner_join" parameters="1">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <join-table join-type="INNER" natural="true">
+ <left>
+ <simple-table name="t_order" alias="o" start-index="14" stop-index="22" />
+ </left>
+ <right>
+ <simple-table name="t_order_item" alias="i" start-index="43" stop-index="56" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="58" stop-index="77">
+ <expr>
+ <binary-operation-expression start-index="64" stop-index="77">
+ <left>
+ <column name="order_id" start-index="64" stop-index="73">
+ <owner name="o" start-index="64" stop-index="64" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="77" stop-index="77" />
+ <parameter-marker-expression parameter-index="0" start-index="77" stop-index="77" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_natural_left_join" parameters="1">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <join-table join-type="LEFT" natural="true">
+ <left>
+ <simple-table name="t_order" alias="o" start-index="14" stop-index="22" />
+ </left>
+ <right>
+ <simple-table name="t_order_item" alias="i" start-index="42" stop-index="55" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="57" stop-index="76">
+ <expr>
+ <binary-operation-expression start-index="63" stop-index="76">
+ <left>
+ <column name="order_id" start-index="63" stop-index="72">
+ <owner name="o" start-index="63" stop-index="63" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="76" stop-index="76" />
+ <parameter-marker-expression parameter-index="0" start-index="76" stop-index="76" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_natural_right_join" parameters="1">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <join-table join-type="RIGHT" natural="true">
+ <left>
+ <simple-table name="t_order" alias="o" start-index="14" stop-index="22" />
+ </left>
+ <right>
+ <simple-table name="t_order_item" alias="i" start-index="43" stop-index="56" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="58" stop-index="77">
+ <expr>
+ <binary-operation-expression start-index="64" stop-index="77">
+ <left>
+ <column name="order_id" start-index="64" stop-index="73">
+ <owner name="o" start-index="64" stop-index="64" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="77" stop-index="77" />
+ <parameter-marker-expression parameter-index="0" start-index="77" stop-index="77" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_natural_full_join" parameters="1">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <join-table join-type="FULL" natural="true">
+ <left>
+ <simple-table name="t_order" alias="o" start-index="14" stop-index="22" />
+ </left>
+ <right>
+ <simple-table name="t_order_item" alias="i" start-index="42" stop-index="55" />
+ </right>
+ </join-table>
+ </from>
+ <where start-index="57" stop-index="76">
+ <expr>
+ <binary-operation-expression start-index="63" stop-index="76">
+ <left>
+ <column name="order_id" start-index="63" stop-index="72">
+ <owner name="o" start-index="63" stop-index="63" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="76" stop-index="76" />
+ <parameter-marker-expression parameter-index="0" start-index="76" stop-index="76" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
</sql-parser-test-cases>
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-relation.xml b/test/parser/src/main/resources/sql/supported/dml/select-join.xml
similarity index 76%
rename from test/parser/src/main/resources/sql/supported/dml/select-relation.xml
rename to test/parser/src/main/resources/sql/supported/dml/select-join.xml
index 1b49647aaee..b16d6ed95c3 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-relation.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-join.xml
@@ -25,4 +25,9 @@
<sql-case id="select_full_outer_join_related_with_alias" value="SELECT d.department_id AS d_dept_id, e.department_id AS e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name" db-types="Oracle" />
<sql-case id="select_full_outer_join_using_related_with_alias" value="SELECT department_id AS d_e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e USING (department_id) ORDER BY department_id, e.last_name" db-types="Oracle" />
<sql-case id="select_cross_apply_join_related_with_alias" value="SELECT d.department_name, v.employee_id, v.last_name FROM departments d CROSS APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations') ORDER BY d.department_name, v.employee_id" db-types="Oracle" />
+ <sql-case id="select_natural_join" value="SELECT * FROM t_order o NATURAL JOIN t_order_item i WHERE o.order_id = ?" db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="select_natural_inner_join" value="SELECT * FROM t_order o NATURAL INNER JOIN t_order_item i WHERE o.order_id = ?" db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="select_natural_left_join" value="SELECT * FROM t_order o NATURAL LEFT JOIN t_order_item i WHERE o.order_id = ?" db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="select_natural_right_join" value="SELECT * FROM t_order o NATURAL RIGHT JOIN t_order_item i WHERE o.order_id = ?" db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="select_natural_full_join" value="SELECT * FROM t_order o NATURAL FULL JOIN t_order_item i WHERE o.order_id = ?" db-types="PostgreSQL,openGauss" />
</sql-cases>