apache / apache/shardingsphere

Error while running SQL Server Query with Alias

Open
#28,707 2 comments 0 reactions 0 assignees View on GitHub
db: SQLServer in: SQL parse status: volunteer wanted
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
11h 38m
Merged PRs (30d)
326

Description

## 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 😊

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the supplied SQL Server query and comparing it with the working variant. Trace parsing through SQLParserEngine.java, SQLParserExecutor.java, and ShardingSphereSQLParserEngine.java; done means the aliased TOP query parses without SQLParsingException and has a regression test.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.