SQLServer OFFSET FETCH pagination return wrong results.

[pxchen]

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

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.

The query SQL is without sharding field(there is no where condition)

select user_id, order_id, order_no from t_order order by order_id asc OFFSET 0 ROW FETCH NEXT 2 ROWS ONLY

Example codes for reproduce this issue (such as a github link).

[EricLye]

can you show your yaml?I have problems when i integrate sharding jdbc and sqlserver.It didnot work with sharding jdbc version 3.1.0 or 4.0.0-RC2.
I tried mysql,and it works well.I do not know the reason.Maybe the reason is sqlserver?

[pxchen]

I tried mysql,and it works well.I do not know the reason.Maybe the reason is sqlserver?
-- It is not the reason of SQLServer, it is the bug of sharding-jdbc sqlserver module code。

[SteNicholas]

@terrymanu Please assign this to me for fixing the bug above mentioned.