This is an automated email from the ASF dual-hosted git repository.
terrymanu 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 1f1aa6d766d Fix SQL Server row number column binding (#39229)
1f1aa6d766d is described below
commit 1f1aa6d766d031da6fd911c04654fdaf31b8ee90
Author: Liang Zhang <[email protected]>
AuthorDate: Fri Jul 24 12:57:15 2026 +0800
Fix SQL Server row number column binding (#39229)
* Fix SQL Server row number column binding
Remove row-number identifiers from unparenthesized function metadata and
preserve normalized TOP projection aliases for binding and pagination.
* Fix SQL Server row number column binding
Remove row-number identifiers from unparenthesized function metadata and
preserve normalized TOP projection aliases for binding and pagination.
---
.../database/option/SQLServerFunctionOption.java | 3 +-
.../option/SQLServerFunctionOptionTest.java | 21 ++++++----
.../statement/SQLServerStatementVisitor.java | 5 ++-
.../dml/pagination/top/TopProjectionSegment.java | 3 +-
.../pagination/top/TopProjectionSegmentTest.java} | 36 ++++++++--------
.../binder/src/test/resources/cases/dml/select.xml | 48 ++++++++++++++++++++++
.../binder/src/test/resources/sqls/dml/select.xml | 1 +
7 files changed, 83 insertions(+), 34 deletions(-)
diff --git
a/database/connector/dialect/sqlserver/src/main/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/option/SQLServerFunctionOption.java
b/database/connector/dialect/sqlserver/src/main/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/option/SQLServerFunctionOption.java
index 6338d9a6952..17db2ea54bb 100644
---
a/database/connector/dialect/sqlserver/src/main/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/option/SQLServerFunctionOption.java
+++
b/database/connector/dialect/sqlserver/src/main/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/option/SQLServerFunctionOption.java
@@ -28,9 +28,8 @@ import java.util.Collection;
*/
public final class SQLServerFunctionOption implements DialectFunctionOption {
- // TODO remove ROWNUM, ROWNUM_ and ROW_NUMBER @duanzhengqiang
private static final Collection<String> UNPARENTHESIZED_FUNCTION_NAMES =
new CaseInsensitiveSet<>(Arrays.asList(
- "CURRENT_TIMESTAMP", "CURRENT_USER", "ROWNUM", "ROWNUM_",
"ROW_NUMBER", "SESSION_USER", "SYSTEM_USER", "USER"));
+ "CURRENT_TIMESTAMP", "CURRENT_USER", "SESSION_USER",
"SYSTEM_USER", "USER"));
@Override
public Collection<String> getUnparenthesizedFunctionNames() {
diff --git
a/database/connector/dialect/sqlserver/src/test/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/option/SQLServerFunctionOptionTest.java
b/database/connector/dialect/sqlserver/src/test/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/option/SQLServerFunctionOptionTest.java
index a36e374ea5f..3e713a1a6ee 100644
---
a/database/connector/dialect/sqlserver/src/test/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/option/SQLServerFunctionOptionTest.java
+++
b/database/connector/dialect/sqlserver/src/test/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/option/SQLServerFunctionOptionTest.java
@@ -19,6 +19,12 @@ package
org.apache.shardingsphere.database.connector.sql92.sqlserver.metadata.da
import org.junit.jupiter.api.Test;
+import java.util.Arrays;
+import java.util.Collection;
+
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.is;
+import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertTrue;
class SQLServerFunctionOptionTest {
@@ -27,13 +33,12 @@ class SQLServerFunctionOptionTest {
@Test
void assertGetUnparenthesizedFunctionNames() {
-
assertTrue(functionOption.getUnparenthesizedFunctionNames().contains("CURRENT_TIMESTAMP"));
-
assertTrue(functionOption.getUnparenthesizedFunctionNames().contains("CURRENT_USER"));
-
assertTrue(functionOption.getUnparenthesizedFunctionNames().contains("ROWNUM"));
-
assertTrue(functionOption.getUnparenthesizedFunctionNames().contains("ROWNUM_"));
-
assertTrue(functionOption.getUnparenthesizedFunctionNames().contains("ROW_NUMBER"));
-
assertTrue(functionOption.getUnparenthesizedFunctionNames().contains("SESSION_USER"));
-
assertTrue(functionOption.getUnparenthesizedFunctionNames().contains("SYSTEM_USER"));
-
assertTrue(functionOption.getUnparenthesizedFunctionNames().contains("USER"));
+ Collection<String> actual =
functionOption.getUnparenthesizedFunctionNames();
+ assertThat(actual.size(), is(5));
+ assertTrue(actual.containsAll(Arrays.asList("CURRENT_TIMESTAMP",
"CURRENT_USER", "SESSION_USER", "SYSTEM_USER", "USER")));
+ assertTrue(actual.contains("current_timestamp"));
+ assertFalse(actual.contains("ROWNUM"));
+ assertFalse(actual.contains("ROWNUM_"));
+ assertFalse(actual.contains("ROW_NUMBER"));
}
}
diff --git
a/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/SQLServerStatementVisitor.java
b/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/SQLServerStatementVisitor.java
index a8a8bd532f9..cfefb57ecc8 100644
---
a/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/SQLServerStatementVisitor.java
+++
b/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/SQLServerStatementVisitor.java
@@ -1921,13 +1921,14 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
int startIndex = ctx.topNum().getStart().getStartIndex();
int stopIndex = ctx.topNum().getStop().getStopIndex();
ASTNode topNum = visit(ctx.topNum());
+ String alias = null == ctx.alias() ? null : ((AliasSegment)
visit(ctx.alias())).getIdentifier().getValue();
if (topNum instanceof NumberLiteralValue) {
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);
+ return new TopProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), rowNumberSegment, alias);
}
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);
+ return new TopProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), (RowNumberValueSegment) parameterSegment, alias);
}
@Override
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/pagination/top/TopProjectionSegment.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/pagination/top/TopProjectionSegment.java
index 9bf43f391c0..fd2c17d5c90 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/pagination/top/TopProjectionSegment.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/pagination/top/TopProjectionSegment.java
@@ -39,7 +39,6 @@ public final class TopProjectionSegment implements
ProjectionSegment {
@Override
public String getColumnLabel() {
- // TODO return column label according to database result
- return "TOP";
+ return null == alias || alias.isEmpty() ? "TOP" : alias;
}
}
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/pagination/top/TopProjectionSegment.java
b/parser/sql/statement/core/src/test/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/pagination/top/TopProjectionSegmentTest.java
similarity index 56%
copy from
parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/pagination/top/TopProjectionSegment.java
copy to
parser/sql/statement/core/src/test/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/pagination/top/TopProjectionSegmentTest.java
index 9bf43f391c0..bb65ee80ba4 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/pagination/top/TopProjectionSegment.java
+++
b/parser/sql/statement/core/src/test/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/pagination/top/TopProjectionSegmentTest.java
@@ -17,29 +17,25 @@
package
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.pagination.top;
-import lombok.Getter;
-import lombok.RequiredArgsConstructor;
-import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ProjectionSegment;
-import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.pagination.rownum.RowNumberValueSegment;
+import org.junit.jupiter.api.Test;
-/**
- * Top projection segment.
- */
-@RequiredArgsConstructor
-@Getter
-public final class TopProjectionSegment implements ProjectionSegment {
-
- private final int startIndex;
-
- private final int stopIndex;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.is;
+
+class TopProjectionSegmentTest {
- private final RowNumberValueSegment top;
+ @Test
+ void assertGetColumnLabelWithoutAlias() {
+ assertThat(new TopProjectionSegment(0, 0, null,
null).getColumnLabel(), is("TOP"));
+ }
- private final String alias;
+ @Test
+ void assertGetColumnLabelWithEmptyAlias() {
+ assertThat(new TopProjectionSegment(0, 0, null, "").getColumnLabel(),
is("TOP"));
+ }
- @Override
- public String getColumnLabel() {
- // TODO return column label according to database result
- return "TOP";
+ @Test
+ void assertGetColumnLabelWithAlias() {
+ assertThat(new TopProjectionSegment(0, 0, null,
"rownum_").getColumnLabel(), is("rownum_"));
}
}
diff --git a/test/it/binder/src/test/resources/cases/dml/select.xml
b/test/it/binder/src/test/resources/cases/dml/select.xml
index 99d21a4d5ff..5f29a1ca41a 100644
--- a/test/it/binder/src/test/resources/cases/dml/select.xml
+++ b/test/it/binder/src/test/resources/cases/dml/select.xml
@@ -1766,4 +1766,52 @@
<parameter-marker-expression parameter-index="0" start-index="147"
stop-index="147" />
</parameters>
</select>
+
+ <select sql-case-id="select_with_top_and_delimited_row_number_alias"
parameters="10, 5">
+ <projections start-index="7" stop-index="20">
+ <column-projection name="rownum_" start-index="7" stop-index="20"
start-delimiter="[" end-delimiter="]">
+ <owner name="row_" start-index="7" stop-index="10" />
+ </column-projection>
+ </projections>
+ <from>
+ <subquery-table alias="row_" start-index="27" stop-index="115">
+ <subquery>
+ <select>
+ <projections start-index="35" stop-index="94">
+ <top-projection alias="rownum_" start-index="35"
stop-index="94">
+ <top-value value="10" parameter-index="0"
start-index="40" stop-index="40" />
+ </top-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" alias="o"
start-index="101" stop-index="109">
+ <table-bound>
+ <original-database name="foo_db_1" />
+ <original-schema name="dbo" />
+ </table-bound>
+ </simple-table>
+ </from>
+ </select>
+ </subquery>
+ </subquery-table>
+ </from>
+ <where start-index="117" stop-index="140">
+ <expr>
+ <binary-operation-expression start-index="123"
stop-index="140">
+ <left>
+ <column name="rownum_" start-index="123"
stop-index="136" start-delimiter="[" end-delimiter="]">
+ <owner name="row_" start-index="123"
stop-index="126" />
+ </column>
+ </left>
+ <operator>></operator>
+ <right>
+ <parameter-marker-expression parameter-index="1"
start-index="140" stop-index="140" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <parameters>
+ <parameter-marker-expression parameter-index="0" start-index="40"
stop-index="40" />
+ <parameter-marker-expression parameter-index="1" start-index="140"
stop-index="140" />
+ </parameters>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/binder/src/test/resources/sqls/dml/select.xml
b/test/it/binder/src/test/resources/sqls/dml/select.xml
index 6f807a4fb90..ef3b0a22380 100644
--- a/test/it/binder/src/test/resources/sqls/dml/select.xml
+++ b/test/it/binder/src/test/resources/sqls/dml/select.xml
@@ -32,4 +32,5 @@
<sql-case id="select_with_left_join_where_outer_column" value="SELECT
t1.user_id, t1.user_name, t2.order_id, t2.creation_date FROM t_user t1 LEFT
JOIN t_order t2 ON t1.user_id = t2.user_id WHERE t1.user_name = ?"
db-types="MySQL" case-types="PLACEHOLDER"/>
<sql-case id="select_with_left_join_having_outer_column" value="SELECT
COUNT(*) AS count FROM t_order o LEFT JOIN t_user u ON o.user_id = u.user_id
GROUP BY u.user_id HAVING u.user_id IS NOT NULL" db-types="MySQL"/>
<sql-case id="select_with_outer_join_operator" value="SELECT o.order_id
FROM t_order o, t_order_item i WHERE o.order_id = i.order_id(+)"
db-types="Oracle"/>
+ <sql-case id="select_with_top_and_delimited_row_number_alias"
value="SELECT row_.[rownum_] FROM (SELECT TOP (?) ROW_NUMBER() OVER (ORDER BY
o.order_id) AS [rownum_] FROM t_order o) row_ WHERE row_.[rownum_] > ?"
db-types="SQLServer" case-types="PLACEHOLDER"/>
</sql-cases>
