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 <[email protected]>
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" />
