You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by ji...@apache.org on 2023/08/02 03:28:04 UTC

[shardingsphere] branch master updated: [Oracle SQL] Support OE-4-6/OE-4-7/OE-4-8 of Oracle SQL (#27614)

This is an automated email from the ASF dual-hosted git repository.

jianglongtao 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 65a3656b88d [Oracle SQL] Support OE-4-6/OE-4-7/OE-4-8 of Oracle SQL (#27614)
65a3656b88d is described below

commit 65a3656b88dcf0ed7d589c986b3d06fb31d4a91f
Author: Liao Lanyu <14...@qq.com>
AuthorDate: Wed Aug 2 11:27:58 2023 +0800

    [Oracle SQL] Support OE-4-6/OE-4-7/OE-4-8 of Oracle SQL (#27614)
    
    * support OE-4-6 OE-4-7 OE-4-8
    
    * support OE-4-6 OE-4-7 OE-4-8 test
    
    * check style
---
 .../src/main/antlr4/imports/oracle/BaseRule.g4     | 11 ++++++
 .../visitor/statement/OracleStatementVisitor.java  | 12 +++++++
 .../statement/type/OracleDMLStatementVisitor.java  |  9 +++++
 .../segment/dml/expr/MultisetExpression.java       | 42 ++++++++++++++++++++++
 .../main/resources/case/dml/select-expression.xml  | 39 ++++++++++++++++++++
 .../sql/supported/dml/select-expression.xml        |  3 ++
 6 files changed, 116 insertions(+)

diff --git a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4 b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index c504038c6b8..3e8dfad4eec 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -623,6 +623,7 @@ expr
     | LP_ expr RP_
     | booleanPrimary
     | expr datetimeExpr
+    | multisetExpr
     ;
 
 andOperator
@@ -1984,3 +1985,13 @@ xmlTableOptions
 xmlTableColumn
     : columnName (FOR ORDINALITY | (dataType | XMLTYPE (LP_ SEQUENCE RP_ BY REF)?) (PATH STRING_)? (DEFAULT expr)?)
     ;
+
+multisetExpr
+    : columnName MULTISET multisetOperator (ALL | DISTINCT)? columnName
+    ;
+
+multisetOperator
+    : EXCEPT
+    | INTERSECT
+    | UNION
+    ;
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 468dc72ed78..155b54c98d2 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
@@ -112,6 +112,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.InExpres
 import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.IntervalDayToSecondExpression;
 import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.IntervalYearToMonthExpression;
 import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ListExpression;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.MultisetExpression;
 import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.NotExpression;
 import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.XmlNameSpaceStringAsIdentifierSegment;
 import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.XmlNameSpacesClauseSegment;
@@ -354,9 +355,20 @@ public abstract class OracleStatementVisitor extends OracleStatementBaseVisitor<
         if (null != ctx.datetimeExpr()) {
             return createDatetimeExpression(ctx, ctx.datetimeExpr());
         }
+        if (null != ctx.multisetExpr()) {
+            return createMultisetExpression(ctx);
+        }
         return new NotExpression(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), (ExpressionSegment) visit(ctx.expr(0)), false);
     }
     
+    private ASTNode createMultisetExpression(final ExprContext ctx) {
+        ExpressionSegment left = (ColumnSegment) visitColumnName(ctx.multisetExpr().columnName(0));
+        ExpressionSegment right = (ColumnSegment) visitColumnName(ctx.multisetExpr().columnName(1));
+        String text = ctx.start.getInputStream().getText(new Interval(ctx.start.getStartIndex(), ctx.stop.getStopIndex()));
+        String keyWord = ctx.multisetExpr().DISTINCT() != null ? "DISTINCT" : "ALL";
+        return new MultisetExpression(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), left, right, ctx.multisetExpr().multisetOperator().getText(), keyWord, text);
+    }
+    
     private ASTNode createDatetimeExpression(final ExprContext ctx, final DatetimeExprContext datetimeExpr) {
         ExpressionSegment left = (ExpressionSegment) visit(ctx.expr(0));
         String text = ctx.start.getInputStream().getText(new Interval(ctx.start.getStartIndex(), ctx.stop.getStopIndex()));
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 61d74d29a19..bc77c1a7766 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
@@ -115,6 +115,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BinaryOp
 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.MultisetExpression;
 import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.IntervalExpressionProjection;
 import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.XmlPiFunctionSegment;
 import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.XmlQueryAndExistsFunctionSegment;
@@ -721,6 +722,14 @@ public final class OracleDMLStatementVisitor extends OracleStatementVisitor impl
             result.setAlias(alias);
             return result;
         }
+        if (projection instanceof MultisetExpression) {
+            MultisetExpression multisetExpression = (MultisetExpression) projection;
+            int startIndex = multisetExpression.getStartIndex();
+            int stopIndex = null == alias ? multisetExpression.getStopIndex() : alias.getStopIndex();
+            ExpressionProjectionSegment result = new ExpressionProjectionSegment(startIndex, stopIndex, multisetExpression.getText(), multisetExpression);
+            result.setAlias(alias);
+            return result;
+        }
         if (projection instanceof DatetimeExpression) {
             DatetimeExpression datetimeExpression = (DatetimeExpression) projection;
             return null == datetimeExpression.getRight()
diff --git a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/expr/MultisetExpression.java b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/expr/MultisetExpression.java
new file mode 100644
index 00000000000..6bdab9f8030
--- /dev/null
+++ b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/expr/MultisetExpression.java
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr;
+
+import lombok.Getter;
+import lombok.RequiredArgsConstructor;
+import lombok.Setter;
+
+@RequiredArgsConstructor
+@Getter
+@Setter
+public class MultisetExpression implements ExpressionSegment {
+    
+    private final int startIndex;
+    
+    private final int stopIndex;
+    
+    private final ExpressionSegment left;
+    
+    private final ExpressionSegment right;
+    
+    private final String operator;
+    
+    private final String keyWord;
+    
+    private final String text;
+}
diff --git a/test/it/parser/src/main/resources/case/dml/select-expression.xml b/test/it/parser/src/main/resources/case/dml/select-expression.xml
index b05d066f90a..fbf1d6cd94d 100644
--- a/test/it/parser/src/main/resources/case/dml/select-expression.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-expression.xml
@@ -2641,4 +2641,43 @@
             <simple-table name="mining_data_apply_v" start-index="62" stop-index="80" />
         </from>
     </select>
+
+    <select sql-case-id="select_with_multiset_except_expression">
+        <from>
+            <simple-table name="customers_demo" start-index="103" stop-index="116" />
+        </from>
+        <projections start-index="7" stop-index="96">
+            <column-projection name="customer_id" start-index="7" stop-index="17" />
+            <expression-projection text="cust_address_ntab MULTISET EXCEPT DISTINCT cust_address2_ntab" alias="multiset_except" start-index="20" stop-index="96" />
+        </projections>
+        <order-by>
+            <column-item name="customer_id" order-direction="ASC" start-index="127" stop-index="137"/>
+        </order-by>
+    </select>
+
+    <select sql-case-id="select_with_multiset_intersect_expression">
+        <from>
+            <simple-table name="customers_demo" start-index="109" stop-index="122" />
+        </from>
+        <projections start-index="7" stop-index="102">
+            <column-projection name="customer_id" start-index="7" stop-index="17" />
+            <expression-projection text="cust_address_ntab MULTISET INTERSECT DISTINCT cust_address2_ntab" alias="multiset_intersect" start-index="20" stop-index="102" />
+        </projections>
+        <order-by>
+            <column-item name="customer_id" order-direction="ASC" start-index="133" stop-index="143"/>
+        </order-by>
+    </select>
+
+    <select sql-case-id="select_with_multiset_union_expression">
+        <from>
+            <simple-table name="customers_demo" start-index="92" stop-index="105" />
+        </from>
+        <projections start-index="7" stop-index="85">
+            <column-projection name="customer_id" start-index="7" stop-index="17" />
+            <expression-projection text="cust_address_ntab MULTISET UNION cust_address2_ntab" alias="multiset_union" start-index="20" stop-index="85" />
+        </projections>
+        <order-by>
+            <column-item name="customer_id" order-direction="ASC" start-index="116" stop-index="126"/>
+        </order-by>
+    </select>
 </sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml b/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
index 870ef81a3f1..7415e69befa 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
@@ -109,4 +109,7 @@
     <sql-case id="select_interval_day_to_second_expression" value="SELECT (SYSTIMESTAMP - order_date) DAY(9) TO SECOND FROM orders" db-types="Oracle" />
     <sql-case id="select_feature_function" value="SELECT FEATURE_VALUE(nmf_sh_sample, 3 USING *) FROM nmf_sh_sample_apply_prepared" db-types="Oracle" />
     <sql-case id="select_cluster_function" value="SELECT CLUSTER_PROBABILITY(km_sh_clus_sample, 2 USING *) FROM mining_data_apply_v" db-types="Oracle" />
+    <sql-case id="select_with_multiset_except_expression" value="SELECT customer_id, cust_address_ntab MULTISET EXCEPT DISTINCT cust_address2_ntab multiset_except FROM customers_demo ORDER BY customer_id;" db-types="Oracle" />
+    <sql-case id="select_with_multiset_intersect_expression" value="SELECT customer_id, cust_address_ntab MULTISET INTERSECT DISTINCT cust_address2_ntab multiset_intersect FROM customers_demo ORDER BY customer_id;" db-types="Oracle" />
+    <sql-case id="select_with_multiset_union_expression" value="SELECT customer_id, cust_address_ntab MULTISET UNION cust_address2_ntab multiset_union FROM customers_demo ORDER BY customer_id;" db-types="Oracle" />
 </sql-cases>