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>