This is an automated email from the ASF dual-hosted git repository.
tuichenchuxin 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 da9e192c6d4 Fix oracle merge sql parsing (#28246)
da9e192c6d4 is described below
commit da9e192c6d499655a0eb7092a832edaf417616b5
Author: ZhangCheng <[email protected]>
AuthorDate: Thu Aug 24 15:54:59 2023 +0800
Fix oracle merge sql parsing (#28246)
* Fix oracle merge sql parsing
* e2e
---
.../statement/type/OracleDMLStatementVisitor.java | 59 +++++++++++++++----
.../sql/common/statement/dml/MergeStatement.java | 6 +-
.../oracle/dml/OracleInsertStatement.java | 12 ++++
.../oracle/dml/OracleUpdateStatement.java | 7 +++
.../statement/dml/impl/MergeStatementAssert.java | 11 ++--
.../statement/dml/InsertStatementTestCase.java | 4 ++
.../jaxb/statement/dml/MergeStatementTestCase.java | 4 +-
.../parser/src/main/resources/case/dml/merge.xml | 68 ++++++++++++++++++++++
.../src/main/resources/sql/supported/dml/merge.xml | 1 +
9 files changed, 149 insertions(+), 23 deletions(-)
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 5b946bc4754..006199edca8 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
@@ -20,6 +20,7 @@ package
org.apache.shardingsphere.sql.parser.oracle.visitor.statement.type;
import org.antlr.v4.runtime.misc.Interval;
import org.apache.shardingsphere.sql.parser.api.ASTNode;
import
org.apache.shardingsphere.sql.parser.api.visitor.statement.type.DMLStatementVisitor;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AliasContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AssignmentValueContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AssignmentValuesContext;
@@ -156,6 +157,8 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.Sim
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SubqueryTableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.XmlTableSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.InsertStatement;
+import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.UpdateStatement;
import org.apache.shardingsphere.sql.parser.sql.common.util.SQLUtils;
import
org.apache.shardingsphere.sql.parser.sql.common.value.collection.CollectionValue;
import
org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
@@ -167,6 +170,7 @@ import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml.Ora
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml.OracleSelectStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml.OracleUpdateStatement;
+import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.LinkedList;
@@ -1135,14 +1139,49 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
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()));
+ result.setUpdate((UpdateStatement)
visitMergeUpdateClause(ctx.mergeUpdateClause()));
+ }
+ if (null != ctx.mergeInsertClause()) {
+ result.setInsert((InsertStatement)
visitMergeInsertClause(ctx.mergeInsertClause()));
+ }
+ return result;
+ }
+
+ @SuppressWarnings("unchecked")
+ @Override
+ public ASTNode visitMergeInsertClause(final
OracleStatementParser.MergeInsertClauseContext ctx) {
+ OracleInsertStatement result = new OracleInsertStatement();
+ if (null != ctx.mergeInsertColumn()) {
+ result.setInsertColumns((InsertColumnsSegment)
visit(ctx.mergeInsertColumn()));
+ }
+ if (null != ctx.mergeColumnValue()) {
+ result.getValues().addAll(((CollectionValue<InsertValuesSegment>)
visit(ctx.mergeColumnValue())).getValue());
+ }
+ if (null != ctx.whereClause()) {
+ result.setWhere((WhereSegment) visit(ctx.whereClause()));
+ }
+ return result;
+ }
+
+ @Override
+ public ASTNode visitMergeInsertColumn(final
OracleStatementParser.MergeInsertColumnContext ctx) {
+ Collection<ColumnSegment> columnSegments = new
ArrayList<>(ctx.columnName().size());
+ for (ColumnNameContext each : ctx.columnName()) {
+ if (null != each.name()) {
+ columnSegments.add((ColumnSegment) visit(each));
}
}
+ return new InsertColumnsSegment(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), columnSegments);
+ }
+
+ @Override
+ public ASTNode visitMergeColumnValue(final
OracleStatementParser.MergeColumnValueContext ctx) {
+ CollectionValue<InsertValuesSegment> result = new CollectionValue<>();
+ List<ExpressionSegment> segments = new LinkedList<>();
+ for (ExprContext each : ctx.expr()) {
+ segments.add(null == each ? new
CommonExpressionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ctx.getText()) : (ExpressionSegment) visit(each));
+ }
+ result.getValue().add(new
InsertValuesSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), segments));
return result;
}
@@ -1189,13 +1228,13 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
@Override
public ASTNode visitMergeUpdateClause(final MergeUpdateClauseContext ctx) {
- OracleMergeStatement result = new OracleMergeStatement();
- result.getUpdate().setSetAssignment((SetAssignmentSegment)
visit(ctx.mergeSetAssignmentsClause()));
+ OracleUpdateStatement result = new OracleUpdateStatement();
+ result.setSetAssignment((SetAssignmentSegment)
visit(ctx.mergeSetAssignmentsClause()));
if (null != ctx.whereClause()) {
- result.getUpdate().setWhere((WhereSegment)
visit(ctx.whereClause()));
+ result.setWhere((WhereSegment) visit(ctx.whereClause()));
}
if (null != ctx.deleteWhereClause()) {
- result.getDelete().setWhere((WhereSegment)
visit(ctx.deleteWhereClause()));
+ result.setDeleteWhere((WhereSegment)
visit(ctx.deleteWhereClause()));
}
return result;
}
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/MergeStatement.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/MergeStatement.java
index 5d576a7b886..c9dd114ae7b 100644
---
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/MergeStatement.java
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/MergeStatement.java
@@ -22,8 +22,6 @@ import lombok.Setter;
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.
@@ -38,7 +36,7 @@ public abstract class MergeStatement extends
AbstractSQLStatement implements DML
private ExpressionSegment expr;
- private UpdateStatement update = new OracleUpdateStatement();
+ private UpdateStatement update;
- private DeleteStatement delete = new OracleDeleteStatement();
+ private InsertStatement insert;
}
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
index 8098debf1d2..c59d28d4c93 100644
---
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleInsertStatement.java
@@ -18,6 +18,7 @@
package org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml;
import lombok.Setter;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.WhereSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.InsertMultiTableElementSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.InsertStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
@@ -32,6 +33,8 @@ public final class OracleInsertStatement extends
InsertStatement implements Orac
private InsertMultiTableElementSegment insertMultiTableElementSegment;
+ private WhereSegment where;
+
/**
* Get insert multi table element segment.
*
@@ -40,4 +43,13 @@ public final class OracleInsertStatement extends
InsertStatement implements Orac
public Optional<InsertMultiTableElementSegment>
getInsertMultiTableElementSegment() {
return Optional.ofNullable(insertMultiTableElementSegment);
}
+
+ /**
+ * Get where segment.
+ *
+ * @return where segment
+ */
+ public Optional<WhereSegment> getWhere() {
+ return Optional.ofNullable(where);
+ }
}
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleUpdateStatement.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleUpdateStatement.java
index 8375d7d1282..b489f6829bb 100644
---
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleUpdateStatement.java
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/dml/OracleUpdateStatement.java
@@ -17,11 +17,18 @@
package org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml;
+import lombok.Getter;
+import lombok.Setter;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.WhereSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.UpdateStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
/**
* Oracle update statement.
*/
+@Getter
+@Setter
public final class OracleUpdateStatement extends UpdateStatement implements
OracleStatement {
+
+ private WhereSegment deleteWhere;
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/dml/impl/MergeStatementAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/dml/impl/MergeStatementAssert.java
index c691f92412b..d65dacf1666 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/dml/impl/MergeStatementAssert.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/dml/impl/MergeStatementAssert.java
@@ -20,6 +20,7 @@ package
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.statement.
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.MergeStatement;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.dml.OracleInsertStatement;
import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.SQLCaseAssertContext;
import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.segment.expression.ExpressionAssert;
import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.segment.set.SetClauseAssert;
@@ -91,13 +92,9 @@ public final class MergeStatementAssert {
WhereClauseAssert.assertIs(assertContext,
actual.getUpdate().getWhere().get(),
expected.getUpdateClause().getWhereClause());
}
}
- if (null != expected.getDeleteClause()) {
- if (null == expected.getDeleteClause().getWhereClause()) {
- assertFalse(actual.getDelete().getWhere().isPresent(),
assertContext.getText("Actual delete where segment should not exist."));
- } else {
- assertTrue(actual.getDelete().getWhere().isPresent(),
assertContext.getText("Actual delete where segment should exist."));
- WhereClauseAssert.assertIs(assertContext,
actual.getDelete().getWhere().get(),
expected.getDeleteClause().getWhereClause());
- }
+ if (null != expected.getInsertClause() && null !=
expected.getInsertClause().getWhereClause() && actual.getInsert() instanceof
OracleInsertStatement) {
+ assertTrue(((OracleInsertStatement)
actual.getInsert()).getWhere().isPresent(), assertContext.getText("Actual
insert where segment should exist."));
+ WhereClauseAssert.assertIs(assertContext, ((OracleInsertStatement)
actual.getInsert()).getWhere().get(),
expected.getInsertClause().getWhereClause());
}
}
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/dml/InsertStatementTestCase.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/dml/InsertStatementTestCase.java
index 599e69a3ae3..995293a76d4 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/dml/InsertStatementTestCase.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/dml/InsertStatementTestCase.java
@@ -27,6 +27,7 @@ import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.s
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.output.ExpectedOutputClause;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.set.ExpectedSetClause;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.table.ExpectedSimpleTable;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.where.ExpectedWhereClause;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.with.ExpectedWithClause;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.SQLParserTestCase;
@@ -71,4 +72,7 @@ public final class InsertStatementTestCase extends
SQLParserTestCase {
@XmlElement(name = "returning")
private ExpectedReturningClause returningClause;
+
+ @XmlElement(name = "where")
+ private ExpectedWhereClause whereClause;
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/dml/MergeStatementTestCase.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/dml/MergeStatementTestCase.java
index 066987f8a82..61cb3cf8ad9 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/dml/MergeStatementTestCase.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/dml/MergeStatementTestCase.java
@@ -44,6 +44,6 @@ public final class MergeStatementTestCase extends
SQLParserTestCase {
@XmlElement(name = "update")
private UpdateStatementTestCase updateClause;
- @XmlElement(name = "delete")
- private DeleteStatementTestCase deleteClause;
+ @XmlElement(name = "insert")
+ private InsertStatementTestCase insertClause;
}
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 cc6acd52303..34425e915c9 100644
--- a/test/it/parser/src/main/resources/case/dml/merge.xml
+++ b/test/it/parser/src/main/resources/case/dml/merge.xml
@@ -225,4 +225,72 @@
</where>
</delete>
</merge>
+ <merge sql-case-id="merge_update_and_insert_table">
+ <target>
+ <simple-table alias="D" name="bonuses" start-index="11"
stop-index="19" literal-start-index="11" literal-stop-index="19" />
+ </target>
+ <source>
+ <subquery-table alias="S">
+ <subquery>
+ <select>
+ <projections start-index="38" stop-index="71"
literal-start-index="38" literal-stop-index="71">
+ <column-projection name="employee_id"
start-index="38" stop-index="48" literal-start-index="38"
literal-stop-index="48" />
+ <column-projection name="salary" start-index="51"
stop-index="56" literal-start-index="51" literal-stop-index="56" />
+ <column-projection name="department_id"
start-index="59" stop-index="71" literal-start-index="59"
literal-stop-index="71" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="78"
stop-index="89" literal-start-index="78" literal-stop-index="89">
+ <owner name="hr" start-index="78"
stop-index="79" literal-start-index="78" literal-stop-index="79" />
+ </simple-table>
+ </from>
+ <where start-index="94" stop-index="117"
literal-start-index="94" literal-stop-index="117">
+ <expr>
+ <binary-operation-expression start-index="100"
stop-index="117" literal-start-index="100" literal-stop-index="117">
+ <left>
+ <column name="department_id"
start-index="100" stop-index="112" literal-start-index="100"
literal-stop-index="112" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="80"
start-index="116" stop-index="117" literal-start-index="116"
literal-stop-index="117" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </subquery-table>
+ </source>
+ <expr>
+ <binary-operation-expression start-index="129" stop-index="157"
literal-start-index="129" literal-stop-index="157">
+ <left>
+ <column name="employee_id" start-index="129"
stop-index="141" literal-start-index="129" literal-stop-index="141">
+ <owner name="D" start-index="129" stop-index="129"
literal-start-index="129" literal-stop-index="129" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="employee_id" start-index="145"
stop-index="157" literal-start-index="145" literal-stop-index="157">
+ <owner name="S" start-index="145" stop-index="145"
literal-start-index="145" literal-stop-index="145" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ <insert>
+ <where start-index="365" stop-index="388"
literal-start-index="365" literal-stop-index="388">
+ <expr>
+ <binary-operation-expression start-index="372"
stop-index="387" literal-start-index="372" literal-stop-index="387">
+ <left>
+ <column name="salary" start-index="372"
stop-index="379" literal-start-index="372" literal-stop-index="379">
+ <owner name="S" start-index="372"
stop-index="372" literal-start-index="372" literal-stop-index="372" />
+ </column>
+ </left>
+ <operator><=</operator>
+ <right>
+ <literal-expression value="8000" start-index="384"
stop-index="387" literal-start-index="384" literal-stop-index="387" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </insert>
+ </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 39669775ce0..ca9d0e8d79b 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
@@ -21,4 +21,5 @@
<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-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-cases>
