You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by ch...@apache.org on 2025/08/11 01:22:14 UTC

(shardingsphere) branch master updated: fix:Enhance support for the GROUP BY clause (#36213)

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

chengzhang 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 048cd8cd5b7 fix:Enhance support for the GROUP BY clause (#36213)
048cd8cd5b7 is described below

commit 048cd8cd5b73d87ba080b8489a069fb2066c6d06
Author: cxy <xi...@qq.com>
AuthorDate: Mon Aug 11 09:22:08 2025 +0800

    fix:Enhance support for the GROUP BY clause (#36213)
---
 .../src/main/antlr4/imports/sqlserver/BaseRule.g4  |  2 +-
 .../main/antlr4/imports/sqlserver/DMLStatement.g4  | 22 +++++-
 .../antlr4/imports/sqlserver/SQLServerKeyword.g4   | 12 ++++
 .../statement/SQLServerStatementVisitor.java       | 79 +++++++++++++++++++++-
 .../parser/src/main/resources/case/dml/select.xml  | 17 +++++
 .../main/resources/sql/supported/dml/select.xml    |  1 +
 6 files changed, 129 insertions(+), 4 deletions(-)

diff --git a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4 b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
index 2c5ea7548d6..d9bf302ee4e 100644
--- a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
+++ b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
@@ -121,7 +121,7 @@ unreservedWord
     | ELASTIC_POOL | SERVICE_OBJECTIVE | DATABASE_NAME | ALLOW_CONNECTIONS | GEO | NAMED | DATEFIRST | BACKUP_STORAGE_REDUNDANCY | FORCE_FAILOVER_ALLOW_DATA_LOSS | SECONDARY | FAILOVER | DEFAULT_FULLTEXT_LANGUAGE
     | DEFAULT_LANGUAGE | INLINE | NESTED_TRIGGERS | TRANSFORM_NOISE_WORDS | TWO_DIGIT_YEAR_CUTOFF | PERSISTENT_LOG_BUFFER | DIRECTORY_NAME | DATEFORMAT | DELAYED_DURABILITY | TRANSFER | SCHEMA | PASSWORD | AUTHORIZATION
     | MEMBER | SEARCH | TEXT | SECOND | PRECISION | VIEWS | PROVIDER | COLUMNS | SUBSTRING | RETURNS | SIZE | CONTAINS | MONTH | INPUT | YEAR
-    | TIMESTAMP | TRIM | USER | RIGHT | JSON | SID | OPENQUERY | ACTION | TARGET | HOUR | MINUTE | TABLE | NODES | VALUE | EXIST | CHANGETABLE | VERSION | CHANGES | MODEL | AI_GENERATE_EMBEDDINGS | PARAMETERS | USE | FREETEXTTABLE | NCHAR | LEFT | RANK | ROLLUP | PIVOT | UNPIVOT | PARSE | TRY_PARSE | HIERARCHYID | PATINDEX | POSITION | FORCESEEK | FORCESCAN | NOEXPAND | SPATIAL_WINDOW_MAX_CELLS | LANGUAGE | CATALOG | PRODUCT | SYSTEM | TABLESAMPLE | LABEL | VALUES | COLLATION | VECTOR_SE [...]
+    | TIMESTAMP | TRIM | USER | RIGHT | JSON | SID | OPENQUERY | ACTION | TARGET | HOUR | MINUTE | TABLE | NODES | VALUE | EXIST | CHANGETABLE | VERSION | CHANGES | MODEL | AI_GENERATE_EMBEDDINGS | PARAMETERS | USE | FREETEXTTABLE | NCHAR | LEFT | RANK | ROLLUP | PIVOT | UNPIVOT | PARSE | TRY_PARSE | HIERARCHYID | PATINDEX | POSITION | FORCESEEK | FORCESCAN | NOEXPAND | SPATIAL_WINDOW_MAX_CELLS | LANGUAGE | CATALOG | PRODUCT | SYSTEM | TABLESAMPLE | LABEL | VALUES | COLLATION | VECTOR_SE [...]
     ;
 
 databaseName
diff --git a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4 b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
index 7342159a761..905a5050dfe 100644
--- a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
+++ b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
@@ -246,7 +246,27 @@ whereClause
     ;
 
 groupByClause
-    : GROUP BY orderByItem (COMMA_ orderByItem)* (WITH ROLLUP)?
+    : GROUP BY (groupByItem (COMMA_ groupByItem)* | orderByItem (COMMA_ orderByItem)* (WITH ROLLUP)?)
+    ;
+
+groupByItem
+    : rollupCubeClause | groupingSetsClause | expr
+    ;
+
+rollupCubeClause
+    : (ROLLUP | CUBE) LP_ groupingExprList RP_
+    ;
+
+groupingSetsClause
+    : GROUPING SETS LP_ (rollupCubeClause | groupingExprList) (COMMA_ (rollupCubeClause | groupingExprList))* RP_
+    ;
+
+groupingExprList
+    : expressionList (COMMA_ expressionList)*
+    ;
+
+expressionList
+    : expr (COMMA_ expr)* | LP_ expr? (COMMA_ expr?)* RP_
     ;
 
 havingClause
diff --git a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4 b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4
index b259c79f881..df0e40d9899 100644
--- a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4
+++ b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4
@@ -2154,3 +2154,15 @@ METRIC
 TOP_N
     : T O P UL_ N
     ;
+
+CUBE
+    : C U B E
+    ;
+
+GROUPING
+    : G R O U P I N G
+    ;
+
+SETS
+    : S E T S
+    ;
diff --git a/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java b/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
index 52d92903043..bb318aa1b47 100644
--- a/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
+++ b/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
@@ -255,6 +255,11 @@ import org.apache.shardingsphere.sql.parser.statement.core.value.parametermarker
 import org.apache.shardingsphere.sql.parser.statement.sqlserver.ddl.statistics.SQLServerUpdateStatisticsStatement;
 import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.VectorSearchFunctionContext;
 import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.VectorSearchParametersContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.GroupByItemContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.RollupCubeClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.GroupingSetsClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.GroupingExprListContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ExpressionListContext;
 
 import java.util.Collection;
 import java.util.Collections;
@@ -1343,12 +1348,82 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
     @Override
     public ASTNode visitGroupByClause(final GroupByClauseContext ctx) {
         Collection<OrderByItemSegment> items = new LinkedList<>();
-        for (OrderByItemContext each : ctx.orderByItem()) {
-            items.add((OrderByItemSegment) visit(each));
+        if (!ctx.groupByItem().isEmpty()) {
+            for (GroupByItemContext each : ctx.groupByItem()) {
+                items.addAll(generateOrderByItemsFromGroupByItem(each));
+            }
+        } else if (!ctx.orderByItem().isEmpty()) {
+            for (OrderByItemContext each : ctx.orderByItem()) {
+                items.add((OrderByItemSegment) visit(each));
+            }
         }
         return new GroupBySegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), items);
     }
     
+    private Collection<OrderByItemSegment> generateOrderByItemsFromGroupByItem(final GroupByItemContext ctx) {
+        Collection<OrderByItemSegment> result = new LinkedList<>();
+        if (null != ctx.expr()) {
+            OrderByItemSegment item = (OrderByItemSegment) extractValueFromGroupByItemExpression(ctx.expr());
+            result.add(item);
+        } else if (null != ctx.rollupCubeClause()) {
+            result.addAll(generateOrderByItemSegmentsFromRollupCubeClause(ctx.rollupCubeClause()));
+        } else {
+            result.addAll(generateOrderByItemSegmentsFromGroupingSetsClause(ctx.groupingSetsClause()));
+        }
+        return result;
+    }
+    
+    private ASTNode extractValueFromGroupByItemExpression(final ExprContext ctx) {
+        ASTNode expression = visit(ctx);
+        if (expression instanceof ColumnSegment) {
+            ColumnSegment column = (ColumnSegment) expression;
+            return new ColumnOrderByItemSegment(column, OrderDirection.ASC, null);
+        }
+        if (expression instanceof LiteralExpressionSegment) {
+            LiteralExpressionSegment literalExpression = (LiteralExpressionSegment) expression;
+            return new IndexOrderByItemSegment(literalExpression.getStartIndex(), literalExpression.getStopIndex(),
+                    SQLUtils.getExactlyNumber(literalExpression.getLiterals().toString(), 10).intValue(), OrderDirection.ASC, null);
+        }
+        return new ExpressionOrderByItemSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), getOriginalText(ctx), OrderDirection.ASC, null, (ExpressionSegment) expression);
+    }
+    
+    private Collection<OrderByItemSegment> generateOrderByItemSegmentsFromRollupCubeClause(final RollupCubeClauseContext ctx) {
+        return new LinkedList<>(generateOrderByItemSegmentsFromGroupingExprList(ctx.groupingExprList()));
+    }
+    
+    private Collection<OrderByItemSegment> generateOrderByItemSegmentsFromGroupingSetsClause(final GroupingSetsClauseContext ctx) {
+        Collection<OrderByItemSegment> result = new LinkedList<>();
+        if (null != ctx.rollupCubeClause()) {
+            for (RollupCubeClauseContext each : ctx.rollupCubeClause()) {
+                result.addAll(generateOrderByItemSegmentsFromRollupCubeClause(each));
+            }
+        }
+        if (null != ctx.groupingExprList()) {
+            for (GroupingExprListContext each : ctx.groupingExprList()) {
+                result.addAll(generateOrderByItemSegmentsFromGroupingExprList(each));
+            }
+        }
+        return result;
+    }
+    
+    private Collection<OrderByItemSegment> generateOrderByItemSegmentsFromGroupingExprList(final GroupingExprListContext ctx) {
+        Collection<OrderByItemSegment> result = new LinkedList<>();
+        for (ExpressionListContext each : ctx.expressionList()) {
+            result.addAll(generateOrderByItemSegmentsFromExpressionList(each));
+        }
+        return result;
+    }
+    
+    private Collection<OrderByItemSegment> generateOrderByItemSegmentsFromExpressionList(final ExpressionListContext ctx) {
+        Collection<OrderByItemSegment> result = new LinkedList<>();
+        if (null != ctx.expr()) {
+            for (ExprContext each : ctx.expr()) {
+                result.add((OrderByItemSegment) extractValueFromGroupByItemExpression(each));
+            }
+        }
+        return result;
+    }
+    
     /**
      * Get original text.
      *
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 cb99a9fc4ef..e2878c26adf 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -11597,4 +11597,21 @@
             </function-table>
         </from>
     </select>
+
+    <select sql-case-id="select_grouping_sets">
+        <projections start-index="7" stop-index="47">
+            <column-projection name="Country" start-index="7" stop-index="13" />
+            <column-projection name="Region" start-index="16" stop-index="21" />
+            <aggregation-projection type="SUM" expression="SUM(Sales)" alias="TotalSales" start-index="24" stop-index="33" />
+        </projections>
+        <from>
+            <simple-table name="Sales" start-index="54" stop-index="58" />
+        </from>
+        <group-by>
+            <column-item name="Country" start-index="93" stop-index="99"/>
+            <column-item name="Region" start-index="102" stop-index="107"/>
+            <column-item name="Country" start-index="117" stop-index="123"/>
+            <column-item name="Region" start-index="126" stop-index="131"/>
+        </group-by>
+    </select>
 </sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index e8cd2b0c635..2cbac88ce57 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -364,4 +364,5 @@
     <sql-case id="select_collation_identifier" value="SELECT DATABASEPROPERTYEX('AdventureWorks2022', 'Collation') AS Collation, DATABASEPROPERTYEX('AdventureWorks2022', 'Edition') AS Edition;" db-types="SQLServer"/>
     <sql-case id="select_with_temptable_no_alias" value="SELECT * FROM OPENXML(@idoc, '/ROOT/Customer', 1) WITH (CustomerID VARCHAR(10),ContactName VARCHAR(20));" db-types="SQLServer"/>
     <sql-case id="select_vector_search_function" value="SELECT r.* FROM VECTOR_SEARCH(TABLE = dbo.sessions AS s, COLUMN = embedding, SIMILAR_TO = @qv, METRIC = 'cosine', TOP_N = 10) AS r;" db-types="SQLServer"/>
+    <sql-case id="select_grouping_sets" value="SELECT Country, Region, SUM(Sales) AS TotalSales FROM Sales GROUP BY GROUPING SETS ( ROLLUP (Country, Region), CUBE (Country, Region));" db-types="SQLServer"/>
 </sql-cases>