(mybatis + shardingjdbc ) multithreading paging query Incorrect result

[matchmen]

environment： spring boot + mybatis + shatdingjdbc
multithreading paging query,database exists enough data;
thread1 logic SQL: select * from table_name limit 0,2;
thread2 logic SQL: select * from table_name limit 2,2;
....
sometimes thread1 or thread2 return result's size is 0 or less than 2, sometimes thread1 and thread2 return result exist repeated data.

[tuohai666]

Could you try to execute the same SQL without sharding-jdbc and watch the results? What do you mean repeated data?
Also, please fill in the issue template as follow. Thanks.

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response more than 7 days and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Expected behavior

Actual behavior

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

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

[RaigorJiang]

Hi, I hava met the same situation.
When I use a single thread to perform paging queries, the result of each query is correct.
But then I use a threadpool to excute the same queries(Every 'Logic SQL' is as same as above), I found repeated results of different threads, or sometimes the result size of some threads is not correct.

Which version of ShardingSphere did you use?
3.1.0 & 4.0.0-RC1-SNAPSHOT

Which project did you use? Sharding-JDBC or Sharding-Proxy?
Sharding-JDBC

Expected behavior
Paging query gets the correct result when single or multi-threaded

Actual behavior
Paging query performance is random when multi-threaded

Reason analyze (If you can)
I found through debugging that the offset after rewrite in each thread is correct, but when the resultsets are merged, skipOff() gets the wrong limit object(in LimitDecoratorMergedResult.java). 

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. There are 2 datasources in my test environment, and the sharding rule is:
   
2. The original SQL is:
   'SELECT l.id from tb_loan l where l.status in( ?, ?) and l.ACCOUNT_DATE < ? order by l.id
   limit ? , ?; '
   the parameters are same except limit value;

(the l.id is PRIMARY KEY and is Globally unique)

3. Create a threadpool and put some threads to excute query, the limit value of thread-1 is '0, 2'', of thread-2 is '2,2', and so on;
4. In my test case, there are 15 records in ds_1 and 11 records in ds_2, so there are 13 pages need to be query;
5. Log the limit value in LimitDecoratorMergedResult, we can find the same offset of different thread.
6. But now in ShardingPreparedStatement#getResultSet() method, the value of ((SelectStatement) routeResult.getSqlStatement()).getLimit().getOffsetValue() is as expected and has no repeated data.

Thanks to the work done by the open source team, hope you can fix it. 💯

[tuohai666]

Thanks for your professional report, I am about to fix it.

[tuohai666]

@RaigorStonehoof Can you call me on wechat(15810310987)?

[RaigorJiang]

@RaigorStonehoof Can you call me on wechat(15810310987)?

OK

[terrymanu]

The reason is we should not modify SQLStatement after cached.
Should clone a new limit object and save to SQLRouteResult.

[RaigorJiang]

Verified, It's OK now, thank you!