Error while running SQL Server Query with Alias

[Afsalmc]

Question

I'm getting Error

org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax: SELECT
[Limit1].[AccountID] AS [AccountID]
FROM ( SELECT TOP (1)
[AccountID] AS [AccountID] FROM [Account]
) AS [Limit1], null
at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.twoPhaseParse(SQLParserExecutor.java:69)
at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.parse(SQLParserExecutor.java:48)
at org.apache.shardingsphere.sql.parser.api.SQLParserEngine.parse(SQLParserEngine.java:47)
at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserExecutor.parse(SQLStatementParserExecutor.java:46)
at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine.parse(SQLStatementParserEngine.java:47)
at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:52)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.createQueryContext(ShardingSphereStatement.java:499)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute0(ShardingSphereStatement.java:428)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute(ShardingSphereStatement.java:376)

while executing query

 SELECT
[Limit1].[AccountID] AS [AccountID]
FROM ( SELECT TOP (1)
[AccountID] AS [AccountID] FROM [Account]
) AS [Limit1]

I'm not getting any error if I modify the query like this :

 SELECT
[Limit1].[AccountID] AS [AccountID]
FROM ( SELECT TOP (1)
[AccountID]  FROM [Account]
) AS [Limit1]

From the error I'm guessing the parsing logic is by default not fully SQL Server compatible. I'm using Version 5.4.0, any help from folks here 😊

[strongduanmu]

Hi @Afsalmc, thank you for your feedback. Let's see if there are friends in the community willing to optimize this problem.

[Afsalmc]

Hmm .. looks like an issue with the ANTLR grammar for SQL Server shardingsphere implements. No issues with Tsql ANTLR official grammar.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.