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 2020/08/24 09:55:33 UTC

[GitHub] [shardingsphere] qiancheng8866 opened a new issue #7032: SQLServer OFFSET FETCH pagination return wrong results

qiancheng8866 opened a new issue #7032:
URL: https://github.com/apache/shardingsphere/issues/7032


   ### Which version of ShardingSphere did you use?
   Version 4.0.0 or above
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-JDBC
   
   ### Expected behavior
   SQL with "OFFSET 0 ROW FETCH NEXT 5ROWS ONLY" returns 5 items.
   
   ### Actual behavior
   SQL with "OFFSET 0 ROW FETCH NEXT 5 ROWS ONLY" returns 10 items.
   
   ### Reason analyze (If you can)
   The question that was raised earlier([SQLServer OFFSET FETCH pagination return wrong results](https://github.com/apache/shardingsphere/issues/2987)), SteNicholas flag has been fixed，But the problem still exists when I use it 
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   1. sharding rule configuration：
   TableRuleConfiguration dealDataRuleConfig = new TableRuleConfiguration(
                   "ras_enterprise_financing_tools_trade_sharding",
                   "enterpriseShardingDataSource.ras_enterprise_financing_tools_trade_${1991..2030}");
   
           ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
           shardingRuleConfig.getBindingTableGroups().add("ras_enterprise_financing_tools_trade_sharding");
           shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new NoneShardingStrategyConfiguration());
           shardingRuleConfig.setDefaultTableShardingStrategyConfig(
                   new ComplexShardingStrategyConfiguration("date_year",new DealDataWorkComplexKeyAlgorithm()));
           shardingRuleConfig.getTableRuleConfigs().add(dealDataRuleConfig);
   
   2.SQL  to execute:
   SELECT id,trade_date
           FROM ras_enterprise_financing_tools_trade_sharding
           WHERE date_year IN('2017','2018')
           order by trade_date offset 0 row fetch next 5 rows only
   3.Ouput:
   24 八月 2020 17:52:30,447 38208 [http-nio-9020-exec-1] INFO  [] - Actual SQL: enterpriseShardingDataSource ::: SELECT id,trade_date
           FROM ras_enterprise_financing_tools_trade_2017
           WHERE date_year IN('2017','2018')
           order by trade_date offset 0 row fetch next 5 rows only
   24 八月 2020 17:52:30,447 38208 [http-nio-9020-exec-1] INFO  [] - Actual SQL: enterpriseShardingDataSource ::: SELECT id,trade_date
           FROM ras_enterprise_financing_tools_trade_2018
           WHERE date_year IN('2017','2018')
           order by trade_date offset 0 row fetch next 5 rows only
   <==    Columns: id, trade_date
   <==        Row: 1, 2017-01-01
   <==        Row: 551, 2017-01-01
   <==        Row: 1038, 2017-01-01
   <==        Row: 1039, 2017-01-02
   <==        Row: 552, 2017-01-02
   <==        Row: 1, 2018-01-01
   <==        Row: 366, 2018-01-01
   <==        Row: 731, 2018-01-01
   <==        Row: 1096, 2018-01-01
   <==        Row: 1461, 2018-01-01
   <==      Total: 10
   
   ### 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

[GitHub] [shardingsphere] tristaZero commented on issue #7032: SQLServer OFFSET FETCH pagination return wrong results

Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7032:
URL: https://github.com/apache/shardingsphere/issues/7032#issuecomment-681313837


   @jingshanglu Nice job. Welcome your test using the master branch!


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

[GitHub] [shardingsphere] tristaZero closed issue #7032: SQLServer OFFSET FETCH pagination return wrong results

Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #7032:
URL: https://github.com/apache/shardingsphere/issues/7032


   


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

[GitHub] [shardingsphere] jingshanglu commented on issue #7032: SQLServer OFFSET FETCH pagination return wrong results

Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #7032:
URL: https://github.com/apache/shardingsphere/issues/7032#issuecomment-680673522


   @qiancheng8866 Pr #7077  fix it, you can try it.


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