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 9fd7d08e82e fix:Support for the WINDOW clause in SELECT statements
(#36278)
9fd7d08e82e is described below
commit 9fd7d08e82e7ca1f69f8a432babe26d0ffc74a4b
Author: cxy <[email protected]>
AuthorDate: Thu Aug 14 09:38:11 2025 +0800
fix:Support for the WINDOW clause in SELECT statements (#36278)
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 9 +++--
.../main/antlr4/imports/sqlserver/DMLStatement.g4 | 10 +++++-
.../antlr4/imports/sqlserver/SQLServerKeyword.g4 | 4 +++
.../parser/src/main/resources/case/dml/select.xml | 38 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 1 +
5 files changed, 59 insertions(+), 3 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 bfff4f7ed5c..9ea2035af6a 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
@@ -570,7 +570,8 @@ nullTreatment
;
overClause
- : OVER LP_ partitionByClause? orderByClause? rowRangeClause? RP_
+ : OVER LP_ partitionByClause? orderByClause? rowRangeClause? RP_
+ | OVER identifier
;
partitionByClause
@@ -581,6 +582,10 @@ rowRangeClause
: (ROWS | RANGE) windowFrameExtent
;
+windowSpecification
+ : LP_ identifier? partitionByClause? orderByClause? rowRangeClause? RP_
+ ;
+
windowFrameExtent
: windowFramePreceding | windowFrameBetween
;
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 92792f7756c..2cefdd81418 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
@@ -130,7 +130,7 @@ aggregationClause
;
selectClause
- : selectWithClause? SELECT duplicateSpecification? projections intoClause?
onFileGroupClause? (fromClause withTempTable? withTableHint?)? whereClause?
groupByClause? havingClause? orderByClause? forClause? optionHint?
+ : selectWithClause? SELECT duplicateSpecification? projections intoClause?
onFileGroupClause? (fromClause withTempTable? withTableHint?)? whereClause?
groupByClause? havingClause? orderByClause? forClause? optionHint? windowClause?
;
duplicateSpecification
@@ -164,6 +164,14 @@ qualifiedShorthand
: identifier DOT_ASTERISK_
;
+windowClause
+ : WINDOW windowItem (COMMA_ windowItem)*
+ ;
+
+windowItem
+ : identifier AS windowSpecification
+ ;
+
onFileGroupClause
: ON identifier
;
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 d7f81f99e72..8f3306d0ea3 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
@@ -2182,3 +2182,7 @@ RUNTIME
ONNX
: O N N X
;
+
+WINDOW
+ : W I N D O W
+ ;
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 d70e3c0624f..20e2deb6407 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -11677,4 +11677,42 @@
</function-table>
</from>
</select>
+
+ <select sql-case-id="select_window_clause" parameters="1">
+ <projections start-index="7" stop-index="112">
+ <column-projection name="user_id" start-index="7" stop-index="13"
/>
+ <expression-projection text="ROW_NUMBER() OVER w" start-index="16"
stop-index="50" alias="row_number">
+ <expr>
+ <function start-index="16" stop-index="34"
text="ROW_NUMBER() OVER w" function-name="ROW_NUMBER" />
+ </expr>
+ </expression-projection>
+ <expression-projection text="RANK() OVER w" start-index="53"
stop-index="75" alias="rank">
+ <expr>
+ <function start-index="53" stop-index="65" text="RANK()
OVER w" function-name="RANK" />
+ </expr>
+ </expression-projection>
+ <expression-projection text="DENSE_RANK() OVER w" start-index="78"
stop-index="112" alias="dense_rank">
+ <expr>
+ <function start-index="78" stop-index="96"
text="DENSE_RANK() OVER w" function-name="DENSE_RANK" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="119" stop-index="125" />
+ </from>
+ <where start-index="127" stop-index="144">
+ <expr>
+ <binary-operation-expression start-index="133"
stop-index="144">
+ <left>
+ <column name="order_id" start-index="133"
stop-index="140" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="144"
stop-index="144" />
+ <parameter-marker-expression parameter-index="0"
start-index="144" stop-index="144" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </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 0c624a2d463..88b41210444 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
@@ -368,4 +368,5 @@
<sql-case id="select_identity_function" value="SELECT IDENTITY(int,1,1) AS
ID_Num INTO NewTable FROM OldTable;" db-types="SQLServer"/>
<sql-case id="select_group_by_with_distributed_agg" value="SELECT
CustomerKey FROM FactInternetSales GROUP BY CustomerKey WITH (DISTRIBUTED_AGG)"
db-types="SQLServer"/>
<sql-case id="select_with_predict_function" value="SELECT d.*, p.Score
FROM PREDICT(MODEL = @model, DATA = dbo.mytable AS d) WITH (Score FLOAT) AS p;"
db-types="SQLServer"/>
+ <sql-case id="select_window_clause" value="SELECT user_id, ROW_NUMBER()
OVER w AS 'row_number', RANK() OVER w AS 'rank', DENSE_RANK() OVER w AS
'dense_rank' FROM t_order WHERE order_id = ? WINDOW w AS (ORDER BY user_id)"
db-types="SQLServer"/>
</sql-cases>
