apache / apache/shardingsphere
Error while running SQL Server Query with Alias
- 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
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