This is an automated email from the ASF dual-hosted git repository.
zhaojinchao 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 7e886d48bed Optimize the SQL parsing in Oracle support merge into
select. (#28247)
7e886d48bed is described below
commit 7e886d48bed08a9caa4a44c712d5272994819824
Author: Cong Hu <[email protected]>
AuthorDate: Thu Aug 24 17:46:32 2023 +0800
Optimize the SQL parsing in Oracle support merge into select. (#28247)
---
.../statement/SQLStatementContextFactory.java | 5 +
.../statement/dml/MergeStatementContext.java | 33 +++++++
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 2 +-
.../statement/type/OracleDMLStatementVisitor.java | 13 ++-
.../parser/src/main/resources/case/dml/merge.xml | 102 +++++++++++++++++++++
.../src/main/resources/sql/supported/dml/merge.xml | 1 +
6 files changed, 153 insertions(+), 3 deletions(-)
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/statement/SQLStatementContextFactory.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/statement/SQLStatementContextFactory.java
index 95dde06e49f..22bf72d0906 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/statement/SQLStatementContextFactory.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/statement/SQLStatementContextFactory.java
@@ -59,6 +59,7 @@ import
org.apache.shardingsphere.infra.binder.context.statement.dal.ShowCreateTa
import
org.apache.shardingsphere.infra.binder.context.statement.dal.ShowIndexStatementContext;
import
org.apache.shardingsphere.infra.binder.context.statement.dal.ShowTableStatusStatementContext;
import
org.apache.shardingsphere.infra.binder.context.statement.dal.ShowTablesStatementContext;
+import
org.apache.shardingsphere.infra.binder.context.statement.dml.MergeStatementContext;
import
org.apache.shardingsphere.infra.binder.context.statement.dcl.DenyUserStatementContext;
import
org.apache.shardingsphere.infra.binder.context.statement.dml.SelectStatementContext;
import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
@@ -109,6 +110,7 @@ import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dal.MySQ
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLLoadDataStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLLoadXMLStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.opengauss.ddl.OpenGaussCursorStatement;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml.OracleMergeStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.dcl.SQLServerDenyUserStatement;
import java.util.List;
@@ -173,6 +175,9 @@ public final class SQLStatementContextFactory {
if (sqlStatement instanceof MySQLLoadXMLStatement) {
return new LoadXMLStatementContext((MySQLLoadXMLStatement)
sqlStatement);
}
+ if (sqlStatement instanceof OracleMergeStatement) {
+ return new MergeStatementContext((OracleMergeStatement)
sqlStatement);
+ }
throw new UnsupportedSQLOperationException(String.format("Unsupported
SQL statement `%s`", sqlStatement.getClass().getSimpleName()));
}
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/statement/dml/MergeStatementContext.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/statement/dml/MergeStatementContext.java
new file mode 100644
index 00000000000..40ab28d71d2
--- /dev/null
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/statement/dml/MergeStatementContext.java
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.infra.binder.context.statement.dml;
+
+import lombok.Getter;
+import
org.apache.shardingsphere.infra.binder.context.statement.CommonSQLStatementContext;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml.OracleMergeStatement;
+
+/**
+ * Load xml statement context.
+ */
+@Getter
+public final class MergeStatementContext extends CommonSQLStatementContext {
+
+ public MergeStatementContext(final OracleMergeStatement sqlStatement) {
+ super(sqlStatement);
+ }
+}
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
index 30859d8b31c..2f230d27517 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
@@ -719,7 +719,7 @@ hint
;
intoClause
- : INTO (tableName | viewName) alias?
+ : INTO (tableName | viewName | subquery) alias?
;
usingClause
diff --git
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
index 006199edca8..e1f1e4ff065 100644
---
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
+++
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
@@ -1135,7 +1135,7 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
@Override
public ASTNode visitMerge(final MergeContext ctx) {
OracleMergeStatement result = new OracleMergeStatement();
- result.setTarget((SimpleTableSegment) visit(ctx.intoClause()));
+ result.setTarget((TableSegment) visit(ctx.intoClause()));
result.setSource((TableSegment) visit(ctx.usingClause()));
result.setExpr((ExpressionSegment) visit(ctx.usingClause().expr()));
if (null != ctx.mergeUpdateClause()) {
@@ -1194,7 +1194,16 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
}
return result;
}
- SimpleTableSegment result = (SimpleTableSegment) visit(ctx.viewName());
+ if (null != ctx.viewName()) {
+ SimpleTableSegment result = (SimpleTableSegment)
visit(ctx.viewName());
+ if (null != ctx.alias()) {
+ result.setAlias((AliasSegment) visit(ctx.alias()));
+ }
+ return result;
+ }
+ OracleSelectStatement subquery = (OracleSelectStatement)
visit(ctx.subquery());
+ SubquerySegment subquerySegment = new
SubquerySegment(ctx.subquery().start.getStartIndex(),
ctx.subquery().stop.getStopIndex(), subquery);
+ SubqueryTableSegment result = new
SubqueryTableSegment(subquerySegment);
if (null != ctx.alias()) {
result.setAlias((AliasSegment) visit(ctx.alias()));
}
diff --git a/test/it/parser/src/main/resources/case/dml/merge.xml
b/test/it/parser/src/main/resources/case/dml/merge.xml
index 34425e915c9..62800c80341 100644
--- a/test/it/parser/src/main/resources/case/dml/merge.xml
+++ b/test/it/parser/src/main/resources/case/dml/merge.xml
@@ -293,4 +293,106 @@
</where>
</insert>
</merge>
+ <merge sql-case-id="merge_into_select">
+ <target>
+ <subquery-table alias="D">
+ <subquery>
+ <select>
+ <projections start-index="19" stop-index="19" >
+ <shorthand-projection start-index="19"
stop-index="19" />
+ </projections>
+ <from>
+ <simple-table name="bonuses" start-index="26"
stop-index="32" />
+ </from>
+ <where start-index="34" stop-index="57">
+ <expr>
+ <binary-operation-expression start-index="40"
stop-index="57">
+ <left>
+ <column name="department_id"
start-index="40" stop-index="52" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="80"
start-index="56" stop-index="57" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </subquery-table>
+ </target>
+ <source>
+ <subquery-table alias="S">
+ <subquery>
+ <select>
+ <from>
+ <simple-table name="employees" start-index="116"
stop-index="124" />
+ </from>
+ <projections start-index="76" stop-index="109">
+ <column-projection name="employee_id"
start-index="76" stop-index="86" />
+ <column-projection name="salary" start-index="89"
stop-index="94" />
+ <column-projection name="department_id"
start-index="97" stop-index="109" />
+ </projections>
+ <where start-index="126" stop-index="149">
+ <expr>
+ <binary-operation-expression start-index="132"
stop-index="149">
+ <left>
+ <column name="department_id"
start-index="132" stop-index="144" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="80"
start-index="148" stop-index="149" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </subquery-table>
+ </source>
+ <expr>
+ <binary-operation-expression start-index="158" stop-index="186">
+ <left>
+ <column name="employee_id" start-index="158"
stop-index="170">
+ <owner name="D" start-index="158" stop-index="158" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="employee_id" start-index="174"
stop-index="186">
+ <owner name="S" start-index="174" stop-index="174" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ <update>
+ <set start-index="218" stop-index="249">
+ <assignment start-index="218" stop-index="249">
+ <column name="bonus" start-index="218" stop-index="224">
+ <owner name="D" start-index="218" stop-index="218" />
+ </column>
+ <assignment-value>
+ <literal-expression value="D.bonus + S.salary*.01"
start-index="228" stop-index="249" />
+ </assignment-value>
+ </assignment>
+ </set>
+ </update>
+ <delete>
+ <where start-index="251" stop-index="280">
+ <expr>
+ <binary-operation-expression start-index="265"
stop-index="279">
+ <left>
+ <column name="salary" start-index="265"
stop-index="272">
+ <owner name="S" start-index="265"
stop-index="265" />
+ </column>
+ </left>
+ <operator>></operator>
+ <right>
+ <literal-expression value="8000" start-index="276"
stop-index="279" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </delete>
+ </merge>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/merge.xml
b/test/it/parser/src/main/resources/sql/supported/dml/merge.xml
index ca9d0e8d79b..de66626b021 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/merge.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/merge.xml
@@ -22,4 +22,5 @@
<sql-case id="merge_update_table" value="MERGE INTO people_target pt USING
people_source ps ON (pt.person_id = ps.person_id) WHEN MATCHED THEN UPDATE SET
pt.first_name = ps.first_name, pt.last_name = ps.last_name, pt.title =
ps.title" db-types="Oracle" />
<sql-case id="merge_update_table_with_delete" value="MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees WHERE
department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN
UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary > 8000)"
db-types="Oracle" />
<sql-case id="merge_update_and_insert_table" value="MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM hr.employees WHERE
department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED
THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary =
8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES
(S.employee_id, S.salary*.01) WHERE (S.salary <= 8000);"
db-types="Oracle" />
+ <sql-case id="merge_into_select" value="MERGE INTO (SELECT * FROM bonuses
WHERE department_id = 80) D USING (SELECT employee_id, salary, department_id
FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE
(S.salary > 8000)" db-types="Oracle" />
</sql-cases>
