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 2021/03/30 08:45:35 UTC
[shardingsphere] branch master updated: Oracle merge statement test
cases for merge update clause (#9692)
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 58ed258 Oracle merge statement test cases for merge update clause (#9692)
58ed258 is described below
commit 58ed2586f0309329fef71f7f6ed35597bb688ec7
Author: Thanoshan MV <48...@users.noreply.github.com>
AuthorDate: Tue Mar 30 14:15:13 2021 +0530
Oracle merge statement test cases for merge update clause (#9692)
* add merge update clause test case
* modify literal expression index
* modify merge statement and antlr definition
* add delete statement inside merge statement
---
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 16 ++-
.../impl/OracleDMLStatementSQLVisitor.java | 58 +++++++++
.../sql/common/statement/dml/MergeStatement.java | 6 +
.../segment/assignment/AssignmentValueAssert.java | 10 +-
.../statement/dml/impl/MergeStatementAssert.java | 35 ++++++
.../impl/assignment/ExpectedAssignmentValue.java | 4 +
.../statement/dml/MergeStatementTestCase.java | 5 +
.../src/main/resources/case/dml/merge.xml | 135 +++++++++++++++++++++
.../src/main/resources/sql/supported/dml/merge.xml | 2 +
9 files changed, 264 insertions(+), 7 deletions(-)
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
index e1c5eac..607b94f 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
@@ -176,7 +176,7 @@ havingClause
subquery
: LP_ unionClause RP_
;
-
+
lockClause
: FOR UPDATE
;
@@ -186,7 +186,7 @@ merge
;
hint
- : (BLOCK_COMMENT | INLINE_COMMENT)
+ : BLOCK_COMMENT | INLINE_COMMENT
;
intoClause
@@ -198,7 +198,11 @@ usingClause
;
mergeUpdateClause
- : WHEN MATCHED THEN UPDATE SET mergeAssignment (COMMA_ mergeAssignment)* whereClause? (DELETE whereClause)?
+ : WHEN MATCHED THEN UPDATE SET mergeSetAssignmentsClause whereClause? deleteWhereClause?
+ ;
+
+mergeSetAssignmentsClause
+ : mergeAssignment (COMMA_ mergeAssignment)*
;
mergeAssignment
@@ -206,7 +210,11 @@ mergeAssignment
;
mergeAssignmentValue
- : (expr | DEFAULT)
+ : expr | DEFAULT
+ ;
+
+deleteWhereClause
+ : DELETE whereClause
;
mergeInsertClause
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
index 2fd6085..6f5a552 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
@@ -28,6 +28,7 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.Assign
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ColumnNameContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ColumnNamesContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DeleteContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DeleteWhereClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DuplicateSpecificationContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ExprContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.FromClauseContext;
@@ -38,7 +39,11 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.IntoCl
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.JoinSpecificationContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.JoinedTableContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.LockClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeAssignmentContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeAssignmentValueContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeSetAssignmentsClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeUpdateClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MultipleTableNamesContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MultipleTablesClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.OrderByItemContext;
@@ -487,6 +492,15 @@ public final class OracleDMLStatementSQLVisitor extends OracleStatementSQLVisito
result.setTarget((SimpleTableSegment) visit(ctx.intoClause()));
result.setSource((TableSegment) visit(ctx.usingClause()));
result.setExpr((ExpressionSegment) (visit(ctx.usingClause().expr())));
+ if (null != ctx.mergeUpdateClause()) {
+ result.getUpdate().setSetAssignment((SetAssignmentSegment) visit(ctx.mergeUpdateClause().mergeSetAssignmentsClause()));
+ if (null != ctx.mergeUpdateClause().whereClause()) {
+ result.getUpdate().setWhere((WhereSegment) visit(ctx.mergeUpdateClause().whereClause()));
+ }
+ if (null != ctx.mergeUpdateClause().deleteWhereClause()) {
+ result.getDelete().setWhere((WhereSegment) visit(ctx.mergeUpdateClause().deleteWhereClause()));
+ }
+ }
return result;
}
@@ -530,4 +544,48 @@ public final class OracleDMLStatementSQLVisitor extends OracleStatementSQLVisito
}
return result;
}
+
+ @Override
+ public ASTNode visitMergeUpdateClause(final MergeUpdateClauseContext ctx) {
+ OracleMergeStatement result = new OracleMergeStatement();
+ result.getUpdate().setSetAssignment((SetAssignmentSegment) visit(ctx.mergeSetAssignmentsClause()));
+ if (null != ctx.whereClause()) {
+ result.getUpdate().setWhere((WhereSegment) visit(ctx.whereClause()));
+ }
+ if (null != ctx.deleteWhereClause()) {
+ result.getDelete().setWhere((WhereSegment) visit(ctx.deleteWhereClause()));
+ }
+ return result;
+ }
+
+ @Override
+ public ASTNode visitMergeSetAssignmentsClause(final MergeSetAssignmentsClauseContext ctx) {
+ Collection<AssignmentSegment> assignments = new LinkedList<>();
+ for (MergeAssignmentContext each : ctx.mergeAssignment()) {
+ assignments.add((AssignmentSegment) visit(each));
+ }
+ return new SetAssignmentSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), assignments);
+ }
+
+ @Override
+ public ASTNode visitMergeAssignment(final MergeAssignmentContext ctx) {
+ ColumnSegment column = (ColumnSegment) visitColumnName(ctx.columnName());
+ ExpressionSegment value = (ExpressionSegment) visit(ctx.mergeAssignmentValue());
+ return new AssignmentSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), column, value);
+ }
+
+ @Override
+ public ASTNode visitMergeAssignmentValue(final MergeAssignmentValueContext ctx) {
+ ExprContext expr = ctx.expr();
+ if (null != expr) {
+ return visit(expr);
+ }
+ return new CommonExpressionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), ctx.getText());
+ }
+
+ @Override
+ public ASTNode visitDeleteWhereClause(final DeleteWhereClauseContext ctx) {
+ ASTNode segment = visit(ctx.whereClause().expr());
+ return new WhereSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), (ExpressionSegment) segment);
+ }
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/MergeStatement.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/MergeStatement.java
index b1e5446..13c075a 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/MergeStatement.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/MergeStatement.java
@@ -23,6 +23,8 @@ import lombok.ToString;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.AbstractSQLStatement;
+import org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml.OracleDeleteStatement;
+import org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml.OracleUpdateStatement;
/**
* Merge statement.
@@ -37,4 +39,8 @@ public abstract class MergeStatement extends AbstractSQLStatement implements DML
private TableSegment source;
private ExpressionSegment expr;
+
+ private UpdateStatement update = new OracleUpdateStatement();
+
+ private DeleteStatement delete = new OracleDeleteStatement();
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/assignment/AssignmentValueAssert.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/assignment/AssignmentValueAssert.java
index 5045c51..c682698 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/assignment/AssignmentValueAssert.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/assignment/AssignmentValueAssert.java
@@ -19,13 +19,15 @@ package org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
-import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.SQLCaseAssertContext;
-import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.expression.ExpressionAssert;
-import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.assignment.ExpectedAssignmentValue;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.ParameterMarkerExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ExpressionProjectionSegment;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.SQLCaseAssertContext;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.column.ColumnAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.expression.ExpressionAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.assignment.ExpectedAssignmentValue;
/**
* Assignment value assert.
@@ -48,6 +50,8 @@ public final class AssignmentValueAssert {
// FIXME should be CommonExpressionProjection, not ExpressionProjectionSegment
} else if (actual instanceof ExpressionProjectionSegment) {
ExpressionAssert.assertCommonExpression(assertContext, (ExpressionProjectionSegment) actual, expected.getCommonExpression());
+ } else if (actual instanceof ColumnSegment) {
+ ColumnAssert.assertIs(assertContext, (ColumnSegment) actual, expected.getColumn());
}
}
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/MergeStatementAssert.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/MergeStatementAssert.java
index 86ecb6e..19f5aea 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/MergeStatementAssert.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/MergeStatementAssert.java
@@ -22,10 +22,14 @@ import lombok.NoArgsConstructor;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.MergeStatement;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.SQLCaseAssertContext;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.expression.ExpressionAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.set.SetClauseAssert;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.table.TableAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.where.WhereClauseAssert;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.statement.dml.MergeStatementTestCase;
+import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
/**
* Merge statement assert.
@@ -43,6 +47,8 @@ public final class MergeStatementAssert {
public static void assertIs(final SQLCaseAssertContext assertContext, final MergeStatement actual, final MergeStatementTestCase expected) {
assertTable(assertContext, actual, expected);
assertExpression(assertContext, actual, expected);
+ assertSetClause(assertContext, actual, expected);
+ assertWhereClause(assertContext, actual, expected);
}
private static void assertTable(final SQLCaseAssertContext assertContext, final MergeStatement actual, final MergeStatementTestCase expected) {
@@ -65,4 +71,33 @@ public final class MergeStatementAssert {
assertNull(assertContext.getText("Actual expression should not exist."), actual.getExpr());
}
}
+
+ private static void assertSetClause(final SQLCaseAssertContext assertContext, final MergeStatement actual, final MergeStatementTestCase expected) {
+ if (null != expected.getUpdateClause()) {
+ if (null != expected.getUpdateClause().getSetClause()) {
+ SetClauseAssert.assertIs(assertContext, actual.getUpdate().getSetAssignment(), expected.getUpdateClause().getSetClause());
+ } else {
+ assertNull(assertContext.getText("Actual assignment should not exist."), actual.getUpdate().getSetAssignment());
+ }
+ }
+ }
+
+ private static void assertWhereClause(final SQLCaseAssertContext assertContext, final MergeStatement actual, final MergeStatementTestCase expected) {
+ if (null != expected.getUpdateClause()) {
+ if (null != expected.getUpdateClause().getWhereClause()) {
+ assertTrue(assertContext.getText("Actual update where segment should exist."), actual.getUpdate().getWhere().isPresent());
+ WhereClauseAssert.assertIs(assertContext, actual.getUpdate().getWhere().get(), expected.getUpdateClause().getWhereClause());
+ } else {
+ assertFalse(assertContext.getText("Actual update where segment should not exist."), actual.getUpdate().getWhere().isPresent());
+ }
+ }
+ if (null != expected.getDeleteClause()) {
+ if (null != expected.getDeleteClause().getWhereClause()) {
+ assertTrue(assertContext.getText("Actual delete where segment should exist."), actual.getDelete().getWhere().isPresent());
+ WhereClauseAssert.assertIs(assertContext, actual.getDelete().getWhere().get(), expected.getDeleteClause().getWhereClause());
+ } else {
+ assertFalse(assertContext.getText("Actual delete where segment should not exist."), actual.getDelete().getWhere().isPresent());
+ }
+ }
+ }
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/assignment/ExpectedAssignmentValue.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/assignment/ExpectedAssignmentValue.java
index 551075b..6eba0750 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/assignment/ExpectedAssignmentValue.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/assignment/ExpectedAssignmentValue.java
@@ -20,6 +20,7 @@ package org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domai
import lombok.Getter;
import lombok.Setter;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.AbstractExpectedSQLSegment;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.column.ExpectedColumn;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.expr.complex.ExpectedCommonExpression;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.expr.simple.ExpectedLiteralExpression;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.expr.simple.ExpectedParameterMarkerExpression;
@@ -41,4 +42,7 @@ public final class ExpectedAssignmentValue extends AbstractExpectedSQLSegment {
@XmlElement(name = "common-expression")
private ExpectedCommonExpression commonExpression;
+
+ @XmlElement(name = "column")
+ private ExpectedColumn column;
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/MergeStatementTestCase.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/MergeStatementTestCase.java
index e084f43..9214a0e 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/MergeStatementTestCase.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/MergeStatementTestCase.java
@@ -41,4 +41,9 @@ public final class MergeStatementTestCase extends SQLParserTestCase {
@XmlElement(name = "expr")
private ExpectedExpression expr;
+ @XmlElement(name = "update")
+ private UpdateStatementTestCase updateClause;
+
+ @XmlElement(name = "delete")
+ private DeleteStatementTestCase deleteClause;
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/merge.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/merge.xml
index 3ef1946..10f8f4d 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/merge.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/merge.xml
@@ -90,4 +90,139 @@
</binary-operation-expression>
</expr>
</merge>
+ <merge sql-case-id="merge_update_table">
+ <target>
+ <simple-table name="people_target" alias="pt" start-index="11" stop-index="26" />
+ </target>
+ <source>
+ <simple-table name="people_source" alias="ps" start-index="34" stop-index="49" />
+ </source>
+ <expr>
+ <binary-operation-expression start-index="55" stop-index="81">
+ <left>
+ <column name="person_id" start-index="55" stop-index="66">
+ <owner name="pt" start-index="55" stop-index="56" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="person_id" start-index="70" stop-index="81">
+ <owner name="ps" start-index="70" stop-index="71" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ <update>
+ <set start-index="113" stop-index="191">
+ <assignment start-index="113" stop-index="141">
+ <column name="first_name" start-index="113" stop-index="125">
+ <owner name="pt" start-index="113" stop-index="114" />
+ </column>
+ <assignment-value>
+ <column name="first_name" start-index="129" stop-index="141">
+ <owner name="ps" start-index="129" stop-index="130" />
+ </column>
+ </assignment-value>
+ </assignment>
+ <assignment start-index="144" stop-index="170">
+ <column name="last_name" start-index="144" stop-index="155">
+ <owner name="pt" start-index="144" stop-index="145" />
+ </column>
+ <assignment-value>
+ <column name="last_name" start-index="159" stop-index="170">
+ <owner name="ps" start-index="159" stop-index="160" />
+ </column>
+ </assignment-value>
+ </assignment>
+ <assignment start-index="173" stop-index="191">
+ <column name="title" start-index="173" stop-index="180">
+ <owner name="pt" start-index="173" stop-index="174" />
+ </column>
+ <assignment-value>
+ <column name="title" start-index="184" stop-index="191">
+ <owner name="ps" start-index="184" stop-index="185" />
+ </column>
+ </assignment-value>
+ </assignment>
+ </set>
+ </update>
+ </merge>
+ <merge sql-case-id="merge_update_table_with_delete">
+ <target>
+ <simple-table name="bonuses" alias="D" start-index="11" stop-index="19" />
+ </target>
+ <source>
+ <subquery-table alias="S">
+ <subquery>
+ <select>
+ <from>
+ <simple-table name="employees" start-index="75" stop-index="83" />
+ </from>
+ <projections start-index="35" stop-index="68">
+ <column-projection name="employee_id" start-index="35" stop-index="45" />
+ <column-projection name="salary" start-index="48" stop-index="53" />
+ <column-projection name="department_id" start-index="56" stop-index="68" />
+ </projections>
+ <where start-index="85" stop-index="108">
+ <expr>
+ <binary-operation-expression start-index="91" stop-index="108">
+ <left>
+ <column name="department_id" start-index="91" stop-index="103"/>
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="80" start-index="107" stop-index="108" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </subquery-table>
+ </source>
+ <expr>
+ <binary-operation-expression start-index="117" stop-index="145">
+ <left>
+ <column name="employee_id" start-index="117" stop-index="129">
+ <owner name="D" start-index="117" stop-index="117" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="employee_id" start-index="133" stop-index="145">
+ <owner name="S" start-index="133" stop-index="133" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ <update>
+ <set start-index="177" stop-index="208">
+ <assignment start-index="177" stop-index="208">
+ <column name="bonus" start-index="177" stop-index="183">
+ <owner name="D" start-index="177" stop-index="177" />
+ </column>
+ <assignment-value>
+ <literal-expression value="D.bonus + S.salary*.01" start-index="187" stop-index="208" />
+ </assignment-value>
+ </assignment>
+ </set>
+ </update>
+ <delete>
+ <where start-index="210" stop-index="239">
+ <expr>
+ <binary-operation-expression start-index="224" stop-index="238">
+ <left>
+ <column name="salary" start-index="224" stop-index="231">
+ <owner name="S" start-index="224" stop-index="224" />
+ </column>
+ </left>
+ <operator>></operator>
+ <right>
+ <literal-expression value="8000" start-index="235" stop-index="238" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </delete>
+ </merge>
</sql-parser-test-cases>
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/merge.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/merge.xml
index 7111fbd..380ba60 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/merge.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/merge.xml
@@ -19,4 +19,6 @@
<sql-cases>
<sql-case id="merge_into_table_using_table" value="MERGE INTO people_target USING people_source ON (people_target.person_id = people_source.person_id)" db-types="Oracle"/>
<sql-case id="merge_into_table_using_subquery_alias" 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)" db-types="Oracle"/>
+ <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-cases>