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

[shardingsphere] branch master updated: WithSegment supported (#28555)

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

duanzhengqiang 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 898dcc9a84f WithSegment supported (#28555)
898dcc9a84f is described below

commit 898dcc9a84f9344a4b27b9e80f24d7d1595d9fe4
Author: kanha gupta <92...@users.noreply.github.com>
AuthorDate: Tue Sep 26 08:56:52 2023 +0530

    WithSegment supported (#28555)
---
 .../converter/segment/with/WithConverter.java      | 66 ++++++++++++++++++++++
 .../statement/delete/DeleteStatementConverter.java | 13 ++++-
 .../converter/SQLNodeConverterEngineIT.java        |  2 +-
 .../src/test/resources/converter/delete.xml        |  8 ++-
 4 files changed, 83 insertions(+), 6 deletions(-)

diff --git a/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/segment/with/WithConverter.java b/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/segment/with/WithConverter.java
new file mode 100644
index 00000000000..326ef647bbb
--- /dev/null
+++ b/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/segment/with/WithConverter.java
@@ -0,0 +1,66 @@
+/*
+ * 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.sqlfederation.compiler.converter.segment.with;
+
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlWithItem;
+import org.apache.calcite.sql.SqlWith;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
+import org.apache.shardingsphere.sqlfederation.compiler.converter.segment.expression.ExpressionConverter;
+import org.apache.shardingsphere.sqlfederation.compiler.converter.statement.select.SelectStatementConverter;
+
+import java.util.Collection;
+import java.util.Optional;
+import java.util.stream.Collectors;
+
+/**
+ * With converter.
+ */
+public final class WithConverter {
+    
+    /**
+     * Convert the given WithSegment and query into an SqlNodeList.
+     *
+     * @param withSegment with segment
+     * @param query SqlNode
+     * @return SqlNodeList
+     */
+    public Optional<SqlNodeList> convert(final WithSegment withSegment, final SqlNode query) {
+        SqlIdentifier name = new SqlIdentifier(withSegment.getCommonTableExpressions().iterator().next().getIdentifier().getValue(), SqlParserPos.ZERO);
+        SqlNode selectSubquery = new SelectStatementConverter().convert(withSegment.getCommonTableExpressions().iterator().next().getSubquery().getSelect());
+        ExpressionConverter converter = new ExpressionConverter();
+        Collection<ColumnSegment> collectionColumns = withSegment.getCommonTableExpressions().iterator().next().getColumns();
+        Collection<SqlNode> convertedColumns;
+        SqlNodeList columns = null;
+        if (!collectionColumns.isEmpty()) {
+            convertedColumns = collectionColumns.stream().map(converter::convert).filter(Optional::isPresent).map(Optional::get).collect(Collectors.toList());
+            columns = new SqlNodeList(convertedColumns, SqlParserPos.ZERO);
+        }
+        SqlWithItem sqlWithItem = new SqlWithItem(SqlParserPos.ZERO, name, columns, selectSubquery);
+        SqlNodeList sqlWithItems = new SqlNodeList(SqlParserPos.ZERO);
+        sqlWithItems.add(sqlWithItem);
+        SqlWith sqlWith = new SqlWith(SqlParserPos.ZERO, sqlWithItems, query);
+        SqlNodeList result = new SqlNodeList(SqlParserPos.ZERO);
+        result.add(sqlWith);
+        return Optional.of(result);
+    }
+}
diff --git a/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/statement/delete/DeleteStatementConverter.java b/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/statement/delete/DeleteStatementConverter.java
index 154bb26f6db..a60ed85ce22 100644
--- a/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/statement/delete/DeleteStatementConverter.java
+++ b/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/statement/delete/DeleteStatementConverter.java
@@ -24,12 +24,14 @@ import org.apache.calcite.sql.SqlNodeList;
 import org.apache.calcite.sql.SqlOrderBy;
 import org.apache.calcite.sql.parser.SqlParserPos;
 import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
 import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.DeleteStatement;
 import org.apache.shardingsphere.sql.parser.sql.dialect.handler.dml.DeleteStatementHandler;
 import org.apache.shardingsphere.sqlfederation.compiler.converter.segment.from.TableConverter;
 import org.apache.shardingsphere.sqlfederation.compiler.converter.segment.limit.PaginationValueSQLConverter;
 import org.apache.shardingsphere.sqlfederation.compiler.converter.segment.orderby.OrderByConverter;
 import org.apache.shardingsphere.sqlfederation.compiler.converter.segment.where.WhereConverter;
+import org.apache.shardingsphere.sqlfederation.compiler.converter.segment.with.WithConverter;
 import org.apache.shardingsphere.sqlfederation.compiler.converter.statement.SQLStatementConverter;
 
 import java.util.Optional;
@@ -41,7 +43,7 @@ public final class DeleteStatementConverter implements SQLStatementConverter<Del
     
     @Override
     public SqlNode convert(final DeleteStatement deleteStatement) {
-        SqlDelete sqlDelete = convertDelete(deleteStatement);
+        SqlNode sqlDelete = convertDelete(deleteStatement);
         SqlNodeList orderBy = DeleteStatementHandler.getOrderBySegment(deleteStatement).flatMap(optional -> new OrderByConverter().convert(optional)).orElse(SqlNodeList.EMPTY);
         Optional<LimitSegment> limit = DeleteStatementHandler.getLimitSegment(deleteStatement);
         if (limit.isPresent()) {
@@ -52,10 +54,15 @@ public final class DeleteStatementConverter implements SQLStatementConverter<Del
         return orderBy.isEmpty() ? sqlDelete : new SqlOrderBy(SqlParserPos.ZERO, sqlDelete, orderBy, null, null);
     }
     
-    private SqlDelete convertDelete(final DeleteStatement deleteStatement) {
+    private SqlNode convertDelete(final DeleteStatement deleteStatement) {
         SqlNode deleteTable = new TableConverter().convert(deleteStatement.getTable()).orElseThrow(IllegalStateException::new);
         SqlNode condition = deleteStatement.getWhere().flatMap(optional -> new WhereConverter().convert(optional)).orElse(null);
         SqlIdentifier alias = deleteStatement.getTable().getAliasName().map(optional -> new SqlIdentifier(optional, SqlParserPos.ZERO)).orElse(null);
-        return new SqlDelete(SqlParserPos.ZERO, deleteTable, condition, null, alias);
+        SqlDelete sqlDelete = new SqlDelete(SqlParserPos.ZERO, deleteTable, condition, null, alias);
+        Optional<WithSegment> with = DeleteStatementHandler.getWithSegment(deleteStatement);
+        if (with.isPresent()) {
+            return new WithConverter().convert(DeleteStatementHandler.getWithSegment(deleteStatement).get(), sqlDelete).get();
+        }
+        return sqlDelete;
     }
 }
diff --git a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/SQLNodeConverterEngineIT.java b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/SQLNodeConverterEngineIT.java
index fc1233355d9..708fb470cc2 100644
--- a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/SQLNodeConverterEngineIT.java
+++ b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/SQLNodeConverterEngineIT.java
@@ -89,7 +89,7 @@ class SQLNodeConverterEngineIT {
         
         @Override
         public Stream<? extends Arguments> provideArguments(final ExtensionContext extensionContext) {
-            return getTestParameters("MySQL", "PostgreSQL", "openGauss", "Oracle").stream();
+            return getTestParameters("MySQL", "PostgreSQL", "openGauss", "Oracle", "SQLServer").stream();
         }
         
         private Collection<Arguments> getTestParameters(final String... databaseTypes) {
diff --git a/test/it/optimizer/src/test/resources/converter/delete.xml b/test/it/optimizer/src/test/resources/converter/delete.xml
index 7145066efdb..1f320631d0b 100644
--- a/test/it/optimizer/src/test/resources/converter/delete.xml
+++ b/test/it/optimizer/src/test/resources/converter/delete.xml
@@ -36,6 +36,10 @@
     <test-cases sql-case-id="delete_with_schema" expected-sql="DELETE FROM &quot;db1&quot;.&quot;t_order&quot;" db-types="PostgreSQL, openGauss"/>
     <test-cases sql-case-id="delete_with_order_by_row_count" expected-sql="DELETE FROM `t_order` WHERE `order_id` = 1000 AND `user_id` = 1001 AND `status` = 'init' ORDER BY `order_id` LIMIT 10" db-types="MySQL" sql-case-types="LITERAL" />
     <test-cases sql-case-id="delete_with_order_by_row_count" expected-sql="DELETE FROM `t_order` WHERE `order_id` = ? AND `user_id` = ? AND `status` = ? ORDER BY `order_id` LIMIT ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
-    <test-cases sql-case-id="delete_with_alias" expected-sql="DELETE FROM `t_order` AS `o` AS `o` WHERE `status` = 'init'" db-types="MySQL,SQLServer" sql-case-types="LITERAL" />
-    <test-cases sql-case-id="delete_with_alias" expected-sql="DELETE FROM `t_order` AS `o` AS `o` WHERE `status` = ?" db-types="MySQL,SQLServer" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="delete_with_alias" expected-sql="DELETE FROM `t_order` AS `o` AS `o` WHERE `status` = 'init'" db-types="MySQL" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="delete_with_alias" expected-sql="DELETE FROM [t_order] AS [o] AS [o] WHERE [status] = 'init'" db-types="SQLServer" sql-case-types="LITERAL" />
+    <test-cases sql-case-id="delete_with_alias" expected-sql="DELETE FROM `t_order` AS `o` AS `o` WHERE `status` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="delete_with_alias" expected-sql="DELETE FROM [t_order] AS [o] AS [o] WHERE [status] = ?" db-types="SQLServer" sql-case-types="PLACEHOLDER" />
+    <test-cases sql-case-id="delete_with_with_clause" expected-sql="(WITH [cte] ([order_id], [user_id]) AS (SELECT [order_id], [user_id] FROM [t_order]) DELETE FROM ([cte], [t_order]) WHERE [t_order].[order_id] = [cte].[order_id])" db-types="SQLServer" />
+    <test-cases sql-case-id="delete_without_columns_with_with_clause" expected-sql="(WITH [cte] AS (SELECT [order_id], [user_id] FROM [t_order]) DELETE FROM ([cte], [t_order]) WHERE [t_order].[order_id] = [cte].[order_id])" db-types="SQLServer" />
 </sql-node-converter-test-cases>