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 08:55:28 UTC
[GitHub] [incubator-shardingsphere] pxchen opened a new issue #2987:
SQLServer OFFSET FETCH pagination return wrong results.
pxchen opened a new issue #2987: SQLServer OFFSET FETCH pagination return wrong results.
URL: https://github.com/apache/incubator-shardingsphere/issues/2987
## 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
SQL with "OFFSET 0 ROW FETCH NEXT 2 ROWS ONLY" returns 2 items.
### Actual behavior
SQL with "OFFSET 0 ROW FETCH NEXT 2 ROWS ONLY" returns 4 items.
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
1. DataBase
Two SQLServer databases, each has two tables : t_order_0 and t_order_1.
2. Datas
Database : ds0
only table t_order_0 has data, t_order_1 has no data.
Database : ds1
only table t_order_1 has data, t_order_0 has no data.
3. shardingrule configuration
```YAML
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
```
4. Ouput
```
2019-09-06 16:46:47 INFO [ShardingSphere-SQL] Rule Type: sharding
2019-09-06 16:46:47 INFO [ShardingSphere-SQL] Logic SQL: select user_id, order_id, order_no from t_order order by order_id asc OFFSET 0 ROW FETCH NEXT 2 ROWS ONLY
2019-09-06 16:46:47 INFO [ShardingSphere-SQL] SQLStatement: ShardingSelectOptimizedStatement(tables=Tables(tables=[Table(name=t_order, alias=Optional.absent())], schema=Optional.absent()), groupBy=org.apache.shardingsphere.core.optimize.sharding.segment.select.groupby.GroupBy@63fd4873, orderBy=org.apache.shardingsphere.core.optimize.sharding.segment.select.orderby.OrderBy@1e11bc55, selectItems=SelectItems(startIndex=7, stopIndex=33, distinctRow=false, items=[ColumnSelectItem(owner=null, name=user_id, alias=Optional.absent()), ColumnSelectItem(owner=null, name=order_id, alias=Optional.absent()), ColumnSelectItem(owner=null, name=order_no, alias=Optional.absent())], tables=[TableSegment(startIndex=40, stopIndex=46, name=t_order, quoteCharacter=NONE, owner=Optional.absent(), alias=Optional.absent())]), pagination=org.apache.shardingsphere.core.optimize.sharding.segment.select.pagination.Pagination@7544a1e4, containsSubquery=false)
2019-09-06 16:46:47 INFO [ShardingSphere-SQL] Actual SQL: ds0 ::: select user_id, order_id, order_no from t_order_0 order by order_id asc OFFSET 0 ROW FETCH NEXT 2 ROWS ONLY
2019-09-06 16:46:47 INFO [ShardingSphere-SQL] Actual SQL: ds0 ::: select user_id, order_id, order_no from t_order_1 order by order_id asc OFFSET 0 ROW FETCH NEXT 2 ROWS ONLY
2019-09-06 16:46:47 INFO [ShardingSphere-SQL] Actual SQL: ds1 ::: select user_id, order_id, order_no from t_order_0 order by order_id asc OFFSET 0 ROW FETCH NEXT 2 ROWS ONLY
2019-09-06 16:46:47 INFO [ShardingSphere-SQL] Actual SQL: ds1 ::: select user_id, order_id, order_no from t_order_1 order by order_id asc OFFSET 0 ROW FETCH NEXT 2 ROWS ONLY
Begin to print results
order00
order01
order02
order03
Finish to print results
```
From above we can see that returns 4 items, but expected is 2.
### 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