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 7f57159f1a1 Support parsing SQL Server Top sql (#29491)
7f57159f1a1 is described below
commit 7f57159f1a1f80b07bd8615154f45d0a80d5946a
Author: LotusMoon <[email protected]>
AuthorDate: Tue Dec 26 18:56:35 2023 +0800
Support parsing SQL Server Top sql (#29491)
* Support parsing SQL Server Top sql
* Fix SqlServer top rewrite
* refactor top parsing
---
.../main/antlr4/imports/sqlserver/DMLStatement.g4 | 8 ++--
.../statement/SQLServerStatementVisitor.java | 16 +++----
.../dml/select-pagination-group-by-order-by.xml | 16 +++----
.../main/resources/case/dml/select-pagination.xml | 14 +++---
.../parser/src/main/resources/case/dml/select.xml | 52 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 4 ++
6 files changed, 83 insertions(+), 27 deletions(-)
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 ee6088cdba5..8c56339adfa 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
@@ -89,16 +89,16 @@ duplicateSpecification
;
projections
- : (unqualifiedShorthand | projection) (COMMA_ (unqualifiedShorthand |
projection))*
+ : (unqualifiedShorthand | projection | top (unqualifiedShorthand |
projection)?) (COMMA_ (unqualifiedShorthand | projection))*
;
projection
- : (alias EQ_)? (top | columnName | expr) | qualifiedShorthand
- | (top | columnName | expr) (AS? alias)? | qualifiedShorthand
+ : (alias EQ_)? (columnName | expr) | qualifiedShorthand
+ | (columnName | expr) (AS? alias)? | qualifiedShorthand
;
top
- : TOP LP_? topNum RP_? PERCENT? (WITH TIES)? (ROW_NUMBER LP_ RP_ OVER LP_
orderByClause RP_)?
+ : TOP LP_? topNum RP_? PERCENT? (WITH TIES)? (ROW_NUMBER LP_ RP_ OVER LP_
orderByClause RP_ (AS? alias)?)?
;
topNum
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 efe46ed1b7f..bbbcfea55ca 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
@@ -853,6 +853,9 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
@Override
public ASTNode visitProjections(final ProjectionsContext ctx) {
Collection<ProjectionSegment> projections = new LinkedList<>();
+ if (null != ctx.top()) {
+ projections.add((ProjectionSegment) visit(ctx.top()));
+ }
for (UnqualifiedShorthandContext each : ctx.unqualifiedShorthand()) {
projections.add(new
ShorthandProjectionSegment(each.getStart().getStartIndex(),
each.getStop().getStopIndex()));
}
@@ -1135,10 +1138,6 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
return result;
}
AliasSegment alias = null == ctx.alias() ? null : (AliasSegment)
visit(ctx.alias());
- if (null != ctx.top()) {
- RowNumberValueSegment rowNumber = (RowNumberValueSegment)
visit(ctx.top());
- return new
TopProjectionSegment(ctx.top().getStart().getStartIndex(),
ctx.top().getStop().getStopIndex(), rowNumber, null == alias ? null :
alias.getIdentifier().getValue());
- }
if (null != ctx.columnName()) {
ColumnSegment column = (ColumnSegment) visit(ctx.columnName());
ColumnProjectionSegment result = new
ColumnProjectionSegment(column);
@@ -1154,11 +1153,12 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
int stopIndex = ctx.topNum().getStop().getStopIndex();
ASTNode topNum = visit(ctx.topNum());
if (topNum instanceof NumberLiteralValue) {
- return new NumberLiteralRowNumberValueSegment(startIndex,
stopIndex, ((NumberLiteralValue) topNum).getValue().longValue(), false);
+ NumberLiteralRowNumberValueSegment rowNumberSegment = new
NumberLiteralRowNumberValueSegment(startIndex, stopIndex, ((NumberLiteralValue)
topNum).getValue().longValue(), false);
+ return new TopProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), rowNumberSegment, null != ctx.alias() ?
ctx.alias().getText() : null);
}
- ParameterMarkerSegment result = new
ParameterMarkerRowNumberValueSegment(startIndex, stopIndex,
((ParameterMarkerValue) topNum).getValue(), false);
- parameterMarkerSegments.add(result);
- return result;
+ ParameterMarkerSegment parameterSegment = new
ParameterMarkerRowNumberValueSegment(startIndex, stopIndex,
((ParameterMarkerValue) topNum).getValue(), false);
+ parameterMarkerSegments.add(parameterSegment);
+ return new TopProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), (RowNumberValueSegment) parameterSegment, null !=
ctx.alias() ? ctx.alias().getText() : null);
}
@Override
diff --git
a/test/it/parser/src/main/resources/case/dml/select-pagination-group-by-order-by.xml
b/test/it/parser/src/main/resources/case/dml/select-pagination-group-by-order-by.xml
index 782750ab655..e5ba2832a5d 100644
---
a/test/it/parser/src/main/resources/case/dml/select-pagination-group-by-order-by.xml
+++
b/test/it/parser/src/main/resources/case/dml/select-pagination-group-by-order-by.xml
@@ -253,7 +253,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="158">
- <top-projection alias="rownum_" start-index="22"
stop-index="70">
+ <top-projection alias="rownum_" start-index="22"
stop-index="81">
<top-value value="3" parameter-index="0"
start-index="26" stop-index="26" />
</top-projection>
<column-projection name="item_id" start-index="84"
stop-index="92">
@@ -397,7 +397,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="176">
- <top-projection alias="rownum_" start-index="22"
stop-index="88">
+ <top-projection alias="rownum_" start-index="22"
stop-index="99">
<top-value value="3" parameter-index="0"
start-index="26" stop-index="26" />
</top-projection>
<column-projection name="item_id"
start-index="102" stop-index="110">
@@ -541,7 +541,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="160">
- <top-projection alias="rownum_" start-index="22"
stop-index="72">
+ <top-projection alias="rownum_" start-index="22"
stop-index="83">
<top-value value="3" parameter-index="0"
start-index="27" stop-index="27" />
</top-projection>
<column-projection name="item_id" start-index="86"
stop-index="94">
@@ -685,7 +685,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="178">
- <top-projection alias="rownum_" start-index="22"
stop-index="90">
+ <top-projection alias="rownum_" start-index="22"
stop-index="101">
<top-value value="3" parameter-index="0"
start-index="27" stop-index="27" />
</top-projection>
<column-projection name="item_id"
start-index="104" stop-index="112">
@@ -829,7 +829,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="158">
- <top-projection alias="rownum_" start-index="22"
stop-index="70">
+ <top-projection alias="rownum_" start-index="22"
stop-index="81">
<top-value value="3" parameter-index="0"
start-index="26" stop-index="26" />
</top-projection>
<column-projection name="item_id" start-index="84"
stop-index="92">
@@ -973,7 +973,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="176">
- <top-projection alias="rownum_" start-index="22"
stop-index="88">
+ <top-projection alias="rownum_" start-index="22"
stop-index="99">
<top-value value="3" parameter-index="0"
start-index="26" stop-index="26" />
</top-projection>
<column-projection name="item_id"
start-index="102" stop-index="110">
@@ -1117,7 +1117,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="160">
- <top-projection alias="rownum_" start-index="22"
stop-index="72">
+ <top-projection alias="rownum_" start-index="22"
stop-index="83">
<top-value value="3" parameter-index="0"
start-index="27" stop-index="27" />
</top-projection>
<column-projection name="item_id" start-index="86"
stop-index="94">
@@ -1261,7 +1261,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="178">
- <top-projection alias="rownum_" start-index="22"
stop-index="90">
+ <top-projection alias="rownum_" start-index="22"
stop-index="101">
<top-value value="3" parameter-index="0"
start-index="27" stop-index="27" />
</top-projection>
<column-projection name="item_id"
start-index="104" stop-index="112">
diff --git a/test/it/parser/src/main/resources/case/dml/select-pagination.xml
b/test/it/parser/src/main/resources/case/dml/select-pagination.xml
index ec28c8481e9..46ae6047b18 100644
--- a/test/it/parser/src/main/resources/case/dml/select-pagination.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-pagination.xml
@@ -460,7 +460,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="160">
- <top-projection alias="rownum_" start-index="22"
stop-index="72">
+ <top-projection alias="rownum_" start-index="22"
stop-index="83">
<top-value value="3" parameter-index="0"
start-index="27" stop-index="27" />
</top-projection>
<column-projection name="item_id" start-index="86"
stop-index="94">
@@ -583,7 +583,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="178">
- <top-projection alias="rownum_" start-index="22"
stop-index="90">
+ <top-projection alias="rownum_" start-index="22"
stop-index="101">
<top-value value="3" parameter-index="0"
start-index="27" stop-index="27" />
</top-projection>
<column-projection name="item_id"
start-index="104" stop-index="112">
@@ -923,7 +923,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="160">
- <top-projection alias="rownum_" start-index="22"
stop-index="72">
+ <top-projection alias="rownum_" start-index="22"
stop-index="83">
<top-value value="3" parameter-index="0"
start-index="27" stop-index="27" />
</top-projection>
<column-projection name="item_id" start-index="86"
stop-index="94">
@@ -1062,7 +1062,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="178">
- <top-projection alias="rownum_" start-index="22"
stop-index="90">
+ <top-projection alias="rownum_" start-index="22"
stop-index="101">
<top-value value="3" parameter-index="0"
start-index="27" stop-index="27" />
</top-projection>
<column-projection name="item_id"
start-index="104" stop-index="112">
@@ -1201,7 +1201,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="160">
- <top-projection alias="rownum_" start-index="22"
stop-index="72">
+ <top-projection alias="rownum_" start-index="22"
stop-index="83">
<top-value value="3" parameter-index="0"
start-index="27" stop-index="27" />
</top-projection>
<column-projection name="item_id" start-index="86"
stop-index="94">
@@ -1340,7 +1340,7 @@
<subquery>
<select>
<projections start-index="22" stop-index="178">
- <top-projection alias="rownum_" start-index="22"
stop-index="90">
+ <top-projection alias="rownum_" start-index="22"
stop-index="101">
<top-value value="3" parameter-index="0"
start-index="27" stop-index="27" />
</top-projection>
<column-projection name="item_id"
start-index="104" stop-index="112">
@@ -1991,7 +1991,7 @@
</order-by>
<row-count value="5" start-index="52" stop-index="52" />
</select>
-
+
<select sql-case-id="select_pagination_with_offset_fetch" parameters="20">
<from>
<simple-table name="t_order" start-index="14" stop-index="20" />
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 8ecae2c21f6..7b7c20ee552 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -7806,4 +7806,56 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_with_top_restrict1">
+ <from>
+ <simple-table name="TableName" start-index="21" stop-index="29"/>
+ </from>
+ <projections start-index="7" stop-index="14">
+ <top-projection start-index="7" stop-index="12">
+ <top-value value="10" start-index="11" stop-index="12" />
+ </top-projection>
+ <shorthand-projection stop-index="14" start-index="14"/>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_top_restrict2">
+ <from>
+ <simple-table name="TableName" start-index="30" stop-index="38"/>
+ </from>
+ <projections start-index="7" stop-index="23">
+ <top-projection start-index="7" stop-index="12">
+ <top-value value="10" start-index="11" stop-index="12"/>
+ </top-projection>
+ <column-projection name="columnName" start-index="14"
stop-index="23"/>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_top_restrict3">
+ <from>
+ <simple-table name="TableName" start-index="37" stop-index="52"
alias="alias1"/>
+ </from>
+ <projections start-index="7" stop-index="30">
+ <top-projection start-index="7" stop-index="12">
+ <top-value value="10" start-index="11" stop-index="12"/>
+ </top-projection>
+ <column-projection name="columnName" start-index="14"
stop-index="30">
+ <owner name="alias1" start-index="14" stop-index="19"/>
+ </column-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_top_with_ties">
+ <from>
+ <simple-table name="TableName" start-index="47" stop-index="62"
alias="alias1"/>
+ </from>
+ <projections start-index="7" stop-index="40">
+ <top-projection start-index="7" stop-index="22">
+ <top-value value="10" start-index="11" stop-index="12"/>
+ </top-projection>
+ <column-projection name="columnName" start-index="24"
stop-index="40">
+ <owner name="alias1" start-index="24" stop-index="29"/>
+ </column-projection>
+ </projections>
+ </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 57c41287696..ea53597659d 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
@@ -235,4 +235,8 @@
<sql-case id="select_with_count_temp_table" value="SELECT COUNT(*) AS
[Number of rows] FROM #Test" db-types="SQLServer"/>
<sql-case id="select_with_bracket_alias" value="SELECT obj1.name AS
[XEvent-name], col2.name AS [XEvent-column], obj1.description AS [Descr-name],
col2.description AS [Descr-column] FROM sys.dm_xe_objects AS obj1 INNER JOIN
sys.dm_xe_object_columns AS col2 ON col2.object_name = obj1.name ORDER BY
obj1.name, col2.name" db-types="SQLServer"/>
<sql-case id="select_with_cross_apply" value="SELECT query = a.text,
start_time, percent_complete, eta =
dateadd(second,estimated_completion_time/1000, getdate()) FROM
sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE
r.command = 'RESTORE DATABASE'" db-types="SQLServer"/>
+ <sql-case id="select_with_top_restrict1" value="SELECT TOP 10 * FROM
TableName" db-types="SQLServer"/>
+ <sql-case id="select_with_top_restrict2" value="SELECT TOP 10 columnName
FROM TableName;" db-types="SQLServer"/>
+ <sql-case id="select_with_top_restrict3" value="SELECT TOP 10
alias1.columnName from TableName alias1" db-types="SQLServer"/>
+ <sql-case id="select_with_top_with_ties" value="SELECT TOP 10 WITH TIES
alias1.columnName from TableName alias1" db-types="SQLServer"/>
</sql-cases>
