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 b6f464f3248 Add more Oracle column assertions and parsing (#28304)
b6f464f3248 is described below
commit b6f464f324811f14571446badfea9d563f79172f
Author: ZhangCheng <[email protected]>
AuthorDate: Wed Aug 30 09:35:31 2023 +0800
Add more Oracle column assertions and parsing (#28304)
* Support extracting columns from oracle case when expressions
* Support extracting columns from oracle case when expressions
* Support extracting columns from oracle case when expressions
* Support extracting columns from oracle case when expressions
* Support null value routing
---
.../visitor/statement/OracleStatementVisitor.java | 16 ++++++
.../statement/type/OracleDMLStatementVisitor.java | 15 ++++++
.../segment/dml/expr/CaseWhenExpression.java | 2 +-
.../segment/assignment/AssignmentValueAssert.java | 34 +++++++++---
.../impl/assignment/ExpectedAssignmentValue.java | 4 ++
.../parser/src/main/resources/case/dml/insert.xml | 52 +++++++++++++++---
.../parser/src/main/resources/case/dml/merge.xml | 62 ++++++++++++++++++----
.../parser/src/main/resources/case/dml/update.xml | 27 ++++++++--
8 files changed, 183 insertions(+), 29 deletions(-)
diff --git
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
index a8f1c794a0d..9d94caa9781 100644
---
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
+++
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
@@ -560,9 +560,25 @@ public abstract class OracleStatementVisitor extends
OracleStatementBaseVisitor<
return result;
}
}
+ return visitRemainSimpleExpr(ctx, startIndex, stopIndex);
+ }
+
+ private ASTNode visitRemainSimpleExpr(final SimpleExprContext ctx, final
int startIndex, final int stopIndex) {
+ if (null != ctx.OR_()) {
+ ExpressionSegment left = (ExpressionSegment)
visit(ctx.simpleExpr(0));
+ ExpressionSegment right = (ExpressionSegment)
visit(ctx.simpleExpr(1));
+ String text = ctx.start.getInputStream().getText(new
Interval(ctx.start.getStartIndex(), ctx.stop.getStopIndex()));
+ return new BinaryOperationExpression(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), left, right, ctx.OR_().getText(), text);
+ }
if (null != ctx.caseExpression()) {
return visit(ctx.caseExpression());
}
+ if (null != ctx.BINARY()) {
+ return visit(ctx.simpleExpr(0));
+ }
+ for (SimpleExprContext each : ctx.simpleExpr()) {
+ visit(each);
+ }
return new CommonExpressionSegment(startIndex, stopIndex,
ctx.getText());
}
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 e1f1e4ff065..331a9681580 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
@@ -106,16 +106,21 @@ import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.XmlTab
import
org.apache.shardingsphere.sql.parser.oracle.visitor.statement.OracleStatementVisitor;
import org.apache.shardingsphere.sql.parser.sql.common.enums.JoinType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.OrderDirection;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dal.VariableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.AssignmentSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.ColumnAssignmentSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.InsertValuesSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.SetAssignmentSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.InsertColumnsSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BetweenExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BinaryOperationExpression;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.CaseWhenExpression;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.CollateExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.DatetimeExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.FunctionSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.InExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.MultisetExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.XmlPiFunctionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.XmlQueryAndExistsFunctionSegment;
@@ -803,9 +808,19 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
}
return result;
}
+ if (projection instanceof CaseWhenExpression || projection instanceof
VariableSegment || projection instanceof BetweenExpression || projection
instanceof InExpression
+ || projection instanceof CollateExpression) {
+ return createExpressionProjectionSegment(alias,
(ExpressionSegment) projection);
+ }
throw new UnsupportedOperationException("Unsupported Expression");
}
+ private ExpressionProjectionSegment
createExpressionProjectionSegment(final AliasSegment alias, final
ExpressionSegment projection) {
+ ExpressionProjectionSegment result = new
ExpressionProjectionSegment(projection.getStartIndex(),
projection.getStopIndex(), projection.getText(), projection);
+ result.setAlias(alias);
+ return result;
+ }
+
@Override
public ASTNode visitSelectFromClause(final SelectFromClauseContext ctx) {
return visit(ctx.fromClauseList());
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/expr/CaseWhenExpression.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/expr/CaseWhenExpression.java
index 23a2e832786..21b18d0f8d1 100644
---
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/expr/CaseWhenExpression.java
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/expr/CaseWhenExpression.java
@@ -43,6 +43,6 @@ public final class CaseWhenExpression implements
ExpressionSegment {
@Override
public String getText() {
- return caseExpr.getText();
+ return null == caseExpr ? "" : caseExpr.getText();
}
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/assignment/AssignmentValueAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/assignment/AssignmentValueAssert.java
index 04067cb62e8..05c3dda05cc 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/assignment/AssignmentValueAssert.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/assignment/AssignmentValueAssert.java
@@ -20,6 +20,7 @@ package
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.segment.as
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BinaryOperationExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.CaseWhenExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.FunctionSegment;
@@ -45,25 +46,44 @@ public final class AssignmentValueAssert {
* @param assertContext assert context
* @param actual actual expression segment
* @param expected expected assignment value
+ * @throws UnsupportedOperationException unsupported assertion segment
exception
*/
public static void assertIs(final SQLCaseAssertContext assertContext,
final ExpressionSegment actual, final ExpectedAssignmentValue expected) {
if (actual instanceof ParameterMarkerExpressionSegment) {
ExpressionAssert.assertParameterMarkerExpression(assertContext,
(ParameterMarkerExpressionSegment) actual,
expected.getParameterMarkerExpression());
- } else if (actual instanceof LiteralExpressionSegment) {
+ return;
+ }
+ if (actual instanceof LiteralExpressionSegment) {
ExpressionAssert.assertLiteralExpression(assertContext,
(LiteralExpressionSegment) actual, expected.getLiteralExpression());
// FIXME should be CommonExpressionProjection, not
ExpressionProjectionSegment
- } else if (actual instanceof ExpressionProjectionSegment) {
+ return;
+ }
+ if (actual instanceof ExpressionProjectionSegment) {
ExpressionAssert.assertCommonExpression(assertContext,
(ExpressionProjectionSegment) actual, expected.getCommonExpression());
- } else if (actual instanceof ColumnSegment) {
+ return;
+ }
+ if (actual instanceof ColumnSegment) {
ColumnAssert.assertIs(assertContext, (ColumnSegment) actual,
expected.getColumn());
- } else if (actual instanceof SubqueryExpressionSegment) {
+ return;
+ }
+ if (actual instanceof SubqueryExpressionSegment) {
ExpressionAssert.assertSubqueryExpression(assertContext,
(SubqueryExpressionSegment) actual, expected.getSubquery());
- } else if (actual instanceof FunctionSegment) {
+ return;
+ }
+ if (actual instanceof FunctionSegment) {
ExpressionAssert.assertFunction(assertContext, (FunctionSegment)
actual, expected.getFunction());
- } else if (actual instanceof CommonExpressionSegment) {
+ return;
+ }
+ if (actual instanceof CommonExpressionSegment) {
ExpressionAssert.assertCommonExpression(assertContext,
(CommonExpressionSegment) actual, expected.getCommonExpression());
- } else if (actual instanceof CaseWhenExpression) {
+ return;
+ }
+ if (actual instanceof CaseWhenExpression) {
ExpressionAssert.assertCaseWhenExpression(assertContext,
(CaseWhenExpression) actual, expected.getCaseWhenExpression());
+ return;
+ }
+ if (actual instanceof BinaryOperationExpression) {
+ ExpressionAssert.assertBinaryOperationExpression(assertContext,
(BinaryOperationExpression) actual, expected.getBinaryOperationExpression());
}
}
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/assignment/ExpectedAssignmentValue.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/assignment/ExpectedAssignmentValue.java
index fcb6f80115d..797b8ad074f 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/assignment/ExpectedAssignmentValue.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/assignment/ExpectedAssignmentValue.java
@@ -21,6 +21,7 @@ import lombok.Getter;
import lombok.Setter;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.AbstractExpectedSQLSegment;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.column.ExpectedColumn;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.expr.ExpectedBinaryOperationExpression;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.expr.ExpectedCaseWhenExpression;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.expr.complex.ExpectedCommonExpression;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.expr.simple.ExpectedLiteralExpression;
@@ -49,6 +50,9 @@ public final class ExpectedAssignmentValue extends
AbstractExpectedSQLSegment {
@XmlElement(name = "case-when-expression")
private ExpectedCaseWhenExpression caseWhenExpression;
+ @XmlElement(name = "binary-operation-expression")
+ private ExpectedBinaryOperationExpression binaryOperationExpression;
+
@XmlElement
private ExpectedColumn column;
diff --git a/test/it/parser/src/main/resources/case/dml/insert.xml
b/test/it/parser/src/main/resources/case/dml/insert.xml
index 253484364cd..b27c9ec4424 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -1200,12 +1200,36 @@
</value>
</values>
<on-duplicate-key-columns start-index="61" stop-index="126"
literal-start-index="65" literal-stop-index="130">
- <assignment start-index="85" stop-index="126"
literal-start-index="89" literal-stop-index="130">
+ <assignment>
<column name="salary" start-index="85" stop-index="90"
literal-start-index="89" literal-stop-index="94" />
<assignment-value>
- <!-- FIXME -->
- <!--<common-expression
text="VALUES(salary)+VALUES(salary)*0.2" />-->
- <common-expression text="VALUES(salary)" />
+ <binary-operation-expression start-index="94"
stop-index="126" literal-start-index="98" literal-stop-index="130">
+ <left>
+ <binary-operation-expression start-index="94"
stop-index="122" literal-start-index="98" literal-stop-index="126">
+ <left>
+ <function function-name="VALUES"
text="VALUES(salary)" start-index="94" stop-index="107"
literal-start-index="98" literal-stop-index="111">
+ <parameter>
+ <column name="salary"
start-index="101" stop-index="106" literal-start-index="105"
literal-stop-index="110" />
+ </parameter>
+
<literalText>VALUES(salary)</literalText>
+ </function>
+ </left>
+ <operator>+</operator>
+ <right>
+ <function function-name="VALUES"
text="VALUES(salary)" start-index="109" stop-index="122"
literal-start-index="113" literal-stop-index="126">
+ <parameter>
+ <column name="salary"
start-index="116" stop-index="121" literal-start-index="120"
literal-stop-index="125" />
+ </parameter>
+
<literalText>VALUES(salary)</literalText>
+ </function>
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>*</operator>
+ <right>
+ <literal-expression value="0.2" start-index="124"
stop-index="126" literal-start-index="128" literal-stop-index="130" />
+ </right>
+ </binary-operation-expression>
</assignment-value>
</assignment>
</on-duplicate-key-columns>
@@ -2067,7 +2091,15 @@
<column name="sales_mon" start-index="201"
stop-index="209" />
</assignment-value>
<assignment-value>
- <common-expression text="weekly_start_date+1"
start-index="212" stop-index="230" />
+ <binary-operation-expression start-index="212"
stop-index="230" literal-start-index="212" literal-stop-index="230">
+ <left>
+ <column name="weekly_start_date"
start-index="212" stop-index="228" literal-start-index="212"
literal-stop-index="228" />
+ </left>
+ <operator>+</operator>
+ <right>
+ <literal-expression value="1"
start-index="230" stop-index="230" literal-start-index="230"
literal-stop-index="230" />
+ </right>
+ </binary-operation-expression>
</assignment-value>
</value>
</values>
@@ -2227,7 +2259,15 @@
<assignment start-index="96" stop-index="116">
<column name="user_id" start-index="96" stop-index="102" />
<assignment-value>
- <common-expression literal-text="user_id + 1"
start-index="106" stop-index="116" />
+ <binary-operation-expression start-index="106"
stop-index="116" literal-start-index="106" literal-stop-index="116">
+ <left>
+ <column name="user_id" start-index="106"
stop-index="112" literal-start-index="106" literal-stop-index="112" />
+ </left>
+ <operator>+</operator>
+ <right>
+ <literal-expression value="1" start-index="116"
stop-index="116" literal-start-index="116" literal-stop-index="116" />
+ </right>
+ </binary-operation-expression>
</assignment-value>
</assignment>
<assignment start-index="119" stop-index="133">
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 62800c80341..005ea1d2703 100644
--- a/test/it/parser/src/main/resources/case/dml/merge.xml
+++ b/test/it/parser/src/main/resources/case/dml/merge.xml
@@ -197,12 +197,32 @@
</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>
+ <columns name="bonus" start-index="177" stop-index="183"
literal-start-index="177" literal-stop-index="183">
+ <owner name="D" start-index="177" stop-index="177"
literal-start-index="177" literal-stop-index="177" />
+ </columns>
<assignment-value>
- <literal-expression value="D.bonus + S.salary*.01"
start-index="187" stop-index="208" />
+ <binary-operation-expression start-index="187"
stop-index="208" literal-start-index="187" literal-stop-index="208">
+ <left>
+ <binary-operation-expression start-index="187"
stop-index="204" literal-start-index="187" literal-stop-index="204">
+ <left>
+ <column name="bonus" start-index="187"
stop-index="193" literal-start-index="187" literal-stop-index="193">
+ <owner name="D" start-index="187"
stop-index="187" literal-start-index="187" literal-stop-index="187" />
+ </column>
+ </left>
+ <operator>+</operator>
+ <right>
+ <column name="salary"
start-index="197" stop-index="204" literal-start-index="197"
literal-stop-index="204">
+ <owner name="S" start-index="197"
stop-index="197" literal-start-index="197" literal-stop-index="197" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>*</operator>
+ <right>
+ <literal-expression value="0.01"
start-index="206" stop-index="208" literal-start-index="206"
literal-stop-index="208" />
+ </right>
+ </binary-operation-expression>
</assignment-value>
</assignment>
</set>
@@ -366,13 +386,33 @@
</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>
+ <set start-index="218" stop-index="249" literal-start-index="218"
literal-stop-index="249">
+ <assignment>
+ <columns name="bonus" start-index="218" stop-index="224"
literal-start-index="218" literal-stop-index="224">
+ <owner name="D" start-index="218" stop-index="218"
literal-start-index="218" literal-stop-index="218" />
+ </columns>
<assignment-value>
- <literal-expression value="D.bonus + S.salary*.01"
start-index="228" stop-index="249" />
+ <binary-operation-expression start-index="228"
stop-index="249" literal-start-index="228" literal-stop-index="249">
+ <left>
+ <binary-operation-expression start-index="228"
stop-index="245" literal-start-index="228" literal-stop-index="245">
+ <left>
+ <column name="bonus" start-index="228"
stop-index="234" literal-start-index="228" literal-stop-index="234">
+ <owner name="D" start-index="228"
stop-index="228" literal-start-index="228" literal-stop-index="228" />
+ </column>
+ </left>
+ <operator>+</operator>
+ <right>
+ <column name="salary"
start-index="238" stop-index="245" literal-start-index="238"
literal-stop-index="245">
+ <owner name="S" start-index="238"
stop-index="238" literal-start-index="238" literal-stop-index="238" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>*</operator>
+ <right>
+ <literal-expression value="0.01"
start-index="247" stop-index="249" literal-start-index="247"
literal-stop-index="249" />
+ </right>
+ </binary-operation-expression>
</assignment-value>
</assignment>
</set>
diff --git a/test/it/parser/src/main/resources/case/dml/update.xml
b/test/it/parser/src/main/resources/case/dml/update.xml
index a72c0a2b98f..684251ad2d5 100644
--- a/test/it/parser/src/main/resources/case/dml/update.xml
+++ b/test/it/parser/src/main/resources/case/dml/update.xml
@@ -469,7 +469,16 @@
<assignment start-index="19" stop-index="37"
literal-stop-index="37">
<column name="status" start-index="19" stop-index="24" />
<assignment-value>
- <common-expression text="status - ?" literal-text="status
- 1" start-index="28" stop-index="37" literal-start-index="28"
literal-stop-index="37" />
+ <binary-operation-expression start-index="28"
stop-index="37" literal-start-index="28" literal-stop-index="37">
+ <left>
+ <column name="status" start-index="28"
stop-index="33" literal-start-index="28" literal-stop-index="33" />
+ </left>
+ <operator>-</operator>
+ <right>
+ <literal-expression value="1" start-index="37"
stop-index="37" />
+ <parameter-marker-expression parameter-index="0"
start-index="37" stop-index="37" literal-start-index="37"
literal-stop-index="37" />
+ </right>
+ </binary-operation-expression>
</assignment-value>
</assignment>
</set>
@@ -539,7 +548,6 @@
<parameter-marker-expression
parameter-index="1" start-index="58" stop-index="58" />
</right>
</binary-operation-expression>
- <common-expression text="order_id - ?"
literal-text="order_id - 2" start-index="47" stop-index="58" />
</right>
</binary-operation-expression>
</left>
@@ -1326,8 +1334,19 @@
<simple-table name="employees" start-index="7" stop-index="15" />
</table>
<set start-index="17" stop-index="40">
- <assignment start-index="17" stop-index="40">
- <column name="salary" start-index="21" stop-index="26" />
+ <assignment>
+ <columns name="salary" start-index="21" stop-index="26"
literal-start-index="21" literal-stop-index="26" />
+ <assignment-value>
+ <binary-operation-expression start-index="29"
stop-index="40" literal-start-index="29" literal-stop-index="40">
+ <left>
+ <column name="salary" start-index="29"
stop-index="35" literal-start-index="29" literal-stop-index="35" />
+ </left>
+ <operator>+</operator>
+ <right>
+ <literal-expression value="10" start-index="39"
stop-index="40" literal-start-index="39" literal-stop-index="40" />
+ </right>
+ </binary-operation-expression>
+ </assignment-value>
</assignment>
</set>
<where start-index="43" stop-index="76">
