You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by tu...@apache.org on 2023/08/29 05:58:59 UTC
[shardingsphere] branch master updated: Support extracting columns from oracle case when expressions (#28299)
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 04c01d524d7 Support extracting columns from oracle case when expressions (#28299)
04c01d524d7 is described below
commit 04c01d524d760e35ead3649a9e22c855cd868ea7
Author: ZhangCheng <ch...@apache.org>
AuthorDate: Tue Aug 29 13:58:52 2023 +0800
Support extracting columns from oracle case when expressions (#28299)
* Support extracting columns from oracle case when expressions
* Support extracting columns from oracle case when expressions
---
.../visitor/statement/OracleStatementVisitor.java | 17 +++
.../segment/assignment/AssignmentValueAssert.java | 3 +
.../impl/assignment/ExpectedAssignmentValue.java | 4 +
.../parser/src/main/resources/case/dml/select.xml | 2 -
.../parser/src/main/resources/case/dml/update.xml | 121 ++++++++++++++++++++-
.../main/resources/sql/supported/dml/update.xml | 2 +-
6 files changed, 143 insertions(+), 6 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 5400b5d2e8d..a8f1c794a0d 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
@@ -106,6 +106,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.type.TypeSegm
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
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.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;
@@ -559,9 +560,25 @@ public abstract class OracleStatementVisitor extends OracleStatementBaseVisitor<
return result;
}
}
+ if (null != ctx.caseExpression()) {
+ return visit(ctx.caseExpression());
+ }
return new CommonExpressionSegment(startIndex, stopIndex, ctx.getText());
}
+ @Override
+ public ASTNode visitCaseExpression(final OracleStatementParser.CaseExpressionContext ctx) {
+ ExpressionSegment caseExpr = null == ctx.simpleExpr() ? null : (ExpressionSegment) visit(ctx.simpleExpr());
+ Collection<ExpressionSegment> whenExprs = new ArrayList<>(ctx.caseWhen().size());
+ Collection<ExpressionSegment> thenExprs = new ArrayList<>(ctx.caseWhen().size());
+ for (OracleStatementParser.CaseWhenContext each : ctx.caseWhen()) {
+ whenExprs.add((ExpressionSegment) visit(each.expr(0)));
+ thenExprs.add((ExpressionSegment) visit(each.expr(1)));
+ }
+ ExpressionSegment elseExpr = null == ctx.caseElse() ? null : (ExpressionSegment) visit(ctx.caseElse().expr());
+ return new CaseWhenExpression(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), caseExpr, whenExprs, thenExprs, elseExpr);
+ }
+
@Override
public ASTNode visitPrivateExprOfDb(final PrivateExprOfDbContext ctx) {
if (null != ctx.intervalExpression()) {
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 e3b9e5f1e51..04067cb62e8 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.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;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment;
@@ -61,6 +62,8 @@ public final class AssignmentValueAssert {
ExpressionAssert.assertFunction(assertContext, (FunctionSegment) actual, expected.getFunction());
} else if (actual instanceof CommonExpressionSegment) {
ExpressionAssert.assertCommonExpression(assertContext, (CommonExpressionSegment) actual, expected.getCommonExpression());
+ } else if (actual instanceof CaseWhenExpression) {
+ ExpressionAssert.assertCaseWhenExpression(assertContext, (CaseWhenExpression) actual, expected.getCaseWhenExpression());
}
}
}
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 0ed36a71dba..fcb6f80115d 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.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;
import org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.expr.simple.ExpectedParameterMarkerExpression;
@@ -45,6 +46,9 @@ public final class ExpectedAssignmentValue extends AbstractExpectedSQLSegment {
@XmlElement(name = "common-expression")
private ExpectedCommonExpression commonExpression;
+ @XmlElement(name = "case-when-expression")
+ private ExpectedCaseWhenExpression caseWhenExpression;
+
@XmlElement
private ExpectedColumn column;
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml b/test/it/parser/src/main/resources/case/dml/select.xml
index 93ce5e385ee..18e3d27ac04 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -899,7 +899,6 @@
<literal-expression value="NOT NULL" start-index="44" stop-index="51" />
</right>
</binary-operation-expression>
- <common-expression text="item_id IS NOT NULL" start-index="33" stop-index="51" />
</left>
<operator>AND</operator>
<right>
@@ -951,7 +950,6 @@
<literal-expression value="NOT NULL" start-index="44" stop-index="51" />
</right>
</binary-operation-expression>
- <common-expression text="item_id IS NOT NULL" start-index="33" stop-index="51" />
</left>
<operator>AND</operator>
<right>
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 eef34478eb7..a72c0a2b98f 100644
--- a/test/it/parser/src/main/resources/case/dml/update.xml
+++ b/test/it/parser/src/main/resources/case/dml/update.xml
@@ -620,19 +620,134 @@
<assignment start-index="31" stop-index="106" literal-start-index="31" literal-stop-index="107">
<column name="row_status" start-index="31" stop-index="40" />
<assignment-value>
- <common-expression text="case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end" literal-text="case WHEN (id=3) THEN 2 WHEN (id=4) THEN 2 WHEN (id=10) THEN 2 end" start-index="42" stop-index="106" literal-start-index="42" literal-stop-index="107" />
+ <case-when-expression>
+ <when-exprs>
+ <binary-operation-expression start-index="53" stop-index="56" literal-start-index="53" literal-stop-index="56">
+ <left>
+ <column name="id" start-index="53" stop-index="54" literal-start-index="53" literal-stop-index="54" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="3" start-index="56" stop-index="56" />
+ <parameter-marker-expression parameter-index="0" start-index="56" stop-index="56" literal-start-index="56" literal-stop-index="56" />
+ </right>
+ </binary-operation-expression>
+ </when-exprs>
+ <when-exprs>
+ <binary-operation-expression start-index="72" stop-index="75" literal-start-index="72" literal-stop-index="75">
+ <left>
+ <column name="id" start-index="72" stop-index="73" literal-start-index="72" literal-stop-index="73" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="4" start-index="75" stop-index="75" />
+ <parameter-marker-expression parameter-index="2" start-index="75" stop-index="75" literal-start-index="75" literal-stop-index="75" />
+ </right>
+ </binary-operation-expression>
+ </when-exprs>
+ <when-exprs>
+ <binary-operation-expression start-index="91" stop-index="94" literal-start-index="91" literal-stop-index="95">
+ <left>
+ <column name="id" start-index="91" stop-index="92" literal-start-index="91" literal-stop-index="92" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="10" start-index="94" stop-index="94" literal-start-index="94" literal-stop-index="95" />
+ <parameter-marker-expression parameter-index="4" start-index="94" stop-index="94" literal-start-index="94" literal-stop-index="94" />
+ </right>
+ </binary-operation-expression>
+ </when-exprs>
+ <then-exprs>
+ <literal-expression value="2" start-index="64" stop-index="64" />
+ <parameter-marker-expression parameter-index="1" start-index="64" stop-index="64" literal-start-index="64" literal-stop-index="64" />
+ </then-exprs>
+ <then-exprs>
+ <literal-expression value="2" start-index="83" stop-index="83" />
+ <parameter-marker-expression parameter-index="3" start-index="83" stop-index="83" literal-start-index="83" literal-stop-index="83" />
+ </then-exprs>
+ <then-exprs>
+ <literal-expression value="2" start-index="103" stop-index="103" literal-start-index="103" literal-stop-index="103" />
+ <parameter-marker-expression parameter-index="5" start-index="102" stop-index="102" literal-start-index="103" literal-stop-index="103" />
+ </then-exprs>
+ </case-when-expression>
</assignment-value>
</assignment>
<assignment start-index="113" stop-index="189" literal-start-index="114" literal-stop-index="200">
<column name="update_user" start-index="113" stop-index="123" literal-start-index="114" literal-stop-index="124" />
<assignment-value>
- <common-expression text="case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end" literal-text="case WHEN (id=3) THEN 'll' WHEN (id=4) THEN 'll' WHEN (id=10) THEN 'll' end" start-index="125" stop-index="189" literal-start-index="126" literal-stop-index="200" />
+ <case-when-expression>
+ <when-exprs>
+ <binary-operation-expression start-index="136" stop-index="139" literal-start-index="137" literal-stop-index="140">
+ <left>
+ <column name="id" start-index="136" stop-index="137" literal-start-index="137" literal-stop-index="138" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="3" start-index="140" stop-index="140" literal-start-index="140" literal-stop-index="140" />
+ <parameter-marker-expression parameter-index="6" start-index="139" stop-index="139" literal-start-index="139" literal-stop-index="139" />
+ </right>
+ </binary-operation-expression>
+ </when-exprs>
+ <when-exprs>
+ <binary-operation-expression start-index="155" stop-index="158" literal-start-index="159" literal-stop-index="162">
+ <left>
+ <column name="id" start-index="155" stop-index="156" literal-start-index="159" literal-stop-index="160" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="4" start-index="162" stop-index="162" literal-start-index="162" literal-stop-index="162" />
+ <parameter-marker-expression parameter-index="8" start-index="158" stop-index="158" literal-start-index="158" literal-stop-index="158" />
+ </right>
+ </binary-operation-expression>
+ </when-exprs>
+ <when-exprs>
+ <binary-operation-expression start-index="174" stop-index="177" literal-start-index="181" literal-stop-index="185">
+ <left>
+ <column name="id" start-index="174" stop-index="175" literal-start-index="181" literal-stop-index="182" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="10" start-index="184" stop-index="185" literal-start-index="184" literal-stop-index="185" />
+ <parameter-marker-expression parameter-index="10" start-index="177" stop-index="177" literal-start-index="177" literal-stop-index="177" />
+ </right>
+ </binary-operation-expression>
+ </when-exprs>
+ <then-exprs>
+ <literal-expression value="ll" start-index="148" stop-index="151" literal-start-index="148" literal-stop-index="151" />
+ <parameter-marker-expression parameter-index="7" start-index="147" stop-index="147" literal-start-index="147" literal-stop-index="147" />
+ </then-exprs>
+ <then-exprs>
+ <literal-expression value="ll" start-index="170" stop-index="173" literal-start-index="170" literal-stop-index="173" />
+ <parameter-marker-expression parameter-index="9" start-index="166" stop-index="166" literal-start-index="166" literal-stop-index="166" />
+ </then-exprs>
+ <then-exprs>
+ <literal-expression value="ll" start-index="193" stop-index="196" literal-start-index="193" literal-stop-index="196" />
+ <parameter-marker-expression parameter-index="11" start-index="185" stop-index="185" literal-start-index="185" literal-stop-index="185" />
+ </then-exprs>
+ </case-when-expression>
</assignment-value>
</assignment>
<assignment start-index="192" stop-index="230" literal-start-index="203" literal-stop-index="270">
<column name="update_time" start-index="192" stop-index="202" literal-start-index="203" literal-stop-index="213" />
<assignment-value>
- <common-expression text="case WHEN (id=?) THEN ? end" literal-text="case WHEN (id=3) THEN '2020-08-10T17:15:25.979+0800' end" start-index="204" stop-index="230" literal-start-index="215" literal-stop-index="270" />
+ <case-when-expression>
+ <when-exprs>
+ <binary-operation-expression start-index="215" stop-index="218" literal-start-index="226" literal-stop-index="229">
+ <left>
+ <column name="id" start-index="215" stop-index="216" literal-start-index="226" literal-stop-index="227" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="3" start-index="229" stop-index="229" literal-start-index="229" literal-stop-index="229" />
+ <parameter-marker-expression parameter-index="12" start-index="218" stop-index="218" literal-start-index="218" literal-stop-index="218" />
+ </right>
+ </binary-operation-expression>
+ </when-exprs>
+ <then-exprs>
+ <literal-expression value="2020-08-10T17:15:25.979+0800" start-index="237" stop-index="266" literal-start-index="237" literal-stop-index="266" />
+ <parameter-marker-expression parameter-index="13" start-index="226" stop-index="226" literal-start-index="226" literal-stop-index="226" />
+ </then-exprs>
+ </case-when-expression>
</assignment-value>
</assignment>
</set>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/update.xml b/test/it/parser/src/main/resources/sql/supported/dml/update.xml
index f47e805fc80..6b3fcc6b2ef 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/update.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/update.xml
@@ -30,7 +30,7 @@
<sql-case id="update_with_where_calculation" value="UPDATE t_order SET status = ? WHERE order_id = order_id - ? AND user_id = ?" />
<sql-case id="update_with_column_equal_column" value="update t_order set order_id = order_id, status = 'init' where order_id = order_id AND order_id = ?" db-types="MySQL" />
<sql-case id="update_with_case_when" value="update stock_freeze_detail set row_status=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end,
- update_user=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end, update_time=case WHEN (id=?) THEN ? end where tenant_id = ?" db-types="MySQL" />
+ update_user=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end, update_time=case WHEN (id=?) THEN ? end where tenant_id = ?" db-types="MySQL,Oracle" />
<sql-case id="update_with_order_by_row_count" value="UPDATE t_order SET status = ? WHERE order_id = ? AND user_id = ? ORDER BY order_id LIMIT ?" db-types="MySQL" />
<sql-case id="update_with_number" value="UPDATE t_order SET order_id = ? WHERE user_id = ?" db-types="PostgreSQL,openGauss" />
<sql-case id="update_with_with_clause" value="WITH cte (order_id, user_id, status) AS (SELECT order_id, user_id, status FROM t_order) UPDATE t_order SET status = ? FROM t_order AS t JOIN cte AS c ON t.order_id = c.order_id WHERE c.order_id = ?" db-types="SQLServer" />