You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2019/09/06 09:17:45 UTC
[GitHub] [incubator-shardingsphere] pxchen opened a new issue #2989:
SQLServer TOP + ROW_NUMBER() pagination SQL error
pxchen opened a new issue #2989: SQLServer TOP + ROW_NUMBER() pagination SQL error
URL: https://github.com/apache/incubator-shardingsphere/issues/2989
## Bug Report
### Which version of ShardingSphere did you use?
4.0.0-RC2
### Which project did you use? Sharding-JDBC or Sharding-Proxy?
Sharding-JDBC
### Expected behavior
top + row pagination SQL execute successfully.
### Actual behavior
top + row pagination SQL execute failed.
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
#### SQL to execute
follow the doc [Pagination](https://shardingsphere.apache.org/document/current/en/features/sharding/use-norms/pagination/) "Support TOP + ROW_NUMBER() OVER pagination:" section.
I wrote the SQL
```SQL
SELECT * FROM (SELECT TOP (2) ROW_NUMBER() OVER (ORDER BY o.order_id DESC) AS rownum, * FROM t_order o) AS temp WHERE temp.rownum > 0 ORDER BY temp.order_id
```
but after execute it, the following output come
```
line 1:86 no viable alternative at input '(SELECTTOP(2)ROW_NUMBER()OVER(ORDERBYo.order_idDESC)ASrownum,*'
2019-09-06 17:09:22 INFO [ShardingSphere-SQL] Rule Type: sharding
2019-09-06 17:09:22 INFO [ShardingSphere-SQL] Logic SQL: SELECT * FROM (SELECT TOP (2) ROW_NUMBER() OVER (ORDER BY o.order_id DESC) AS rownum, * FROM t_order o) AS temp WHERE temp.rownum > 0 ORDER BY temp.order_id
2019-09-06 17:09:22 INFO [ShardingSphere-SQL] SQLStatement: ShardingSelectOptimizedStatement(tables=Tables(tables=[], schema=Optional.absent()), groupBy=org.apache.shardingsphere.core.optimize.sharding.segment.select.groupby.GroupBy@436390f4, orderBy=org.apache.shardingsphere.core.optimize.sharding.segment.select.orderby.OrderBy@4d157787, selectItems=SelectItems(startIndex=7, stopIndex=7, distinctRow=false, items=[ShorthandSelectItem(owner=Optional.absent())], tables=[]), pagination=org.apache.shardingsphere.core.optimize.sharding.segment.select.pagination.Pagination@68ed96ca, containsSubquery=false)
2019-09-06 17:09:22 INFO [ShardingSphere-SQL] Actual SQL: ds0 ::: SELECT * FROM (SELECT TOP (2) ROW_NUMBER() OVER (ORDER BY o.order_id DESC) AS rownum, * FROM t_order o) AS temp WHERE temp.rownum > 0 ORDER BY temp.order_id
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 't_order'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
```
My logic table is t_order, and the acutal table is t_order_0 and t_order_1, but the actual SQL is still with table t_order.
#### Sharding rule configuration
```
shardingRule:
tables:
t_order:
actualDataNodes: ds${0..1}.t_order_${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
defaultDataSourceName: ds0
defaultTableStrategy:
none:
defaultKeyGenerator:
type: SNOWFLAKE
column: order_id
props:
sql.show: true
```
### Example codes for reproduce this issue (such as a github link).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services