spring data jpa auto limit page some grammar not support #1672

wuqiu-ai · 2018-12-25T08:19:50Z

junit test:

page is 1 and pagesize is 1000

@Test
    public void testSelece(){
        PageRequest pageRequest = new PageRequest(1, 1000);
        userRepository.getWithoutUserIdListBy("appcode_idxyer_iphone",new Date(),pageRequest);
    }

UserRepository interface:

  @Query("SELECT u FROM UserPo u WHERE u.userId is NULL and u.appName = ?1 and u.modifyTime < ?2")
  List<UserPo> getWithoutUserIdListBy(String appName, Date date, Pageable pageable);

see sql log limit 0, 2000

2018-12-25 16:14:20.480  INFO 34103 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
2018-12-25 16:14:20.480  INFO 34103 --- [           main] ShardingSphere-SQL                       : Logic SQL: select userpo0_.id as id1_5_, userpo0_.createTime as createTi2_5_, userpo0_.appCode as appCode3_5_, userpo0_.appName as appName4_5_, userpo0_.appVersion as appVersi5_5_, userpo0_.deviceMc as deviceMc6_5_, userpo0_.deviceToken as deviceTo7_5_, userpo0_.deviceTokenType as deviceTo8_5_, userpo0_.deviceType as deviceTy9_5_, userpo0_.modifyTime as modifyT10_5_, userpo0_.osVersion as osVersi11_5_, userpo0_.userId as userId12_5_, userpo0_.version as version13_5_ from push_user userpo0_ where (userpo0_.userId is null) and userpo0_.appName=? and userpo0_.modifyTime<? limit ?, ?
2018-12-25 16:14:20.480  INFO 34103 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@410c1f44), containStar=false, selectListLastPosition=460, groupByLastPosition=0, items=[CommonSelectItem(expression=userpo0_.deviceType, alias=Optional.of(deviceTy9_5_)), CommonSelectItem(expression=userpo0_.id, alias=Optional.of(id1_5_)), CommonSelectItem(expression=userpo0_.version, alias=Optional.of(version13_5_)), CommonSelectItem(expression=userpo0_.deviceToken, alias=Optional.of(deviceTo7_5_)), CommonSelectItem(expression=userpo0_.osVersion, alias=Optional.of(osVersi11_5_)), CommonSelectItem(expression=userpo0_.modifyTime, alias=Optional.of(modifyT10_5_)), CommonSelectItem(expression=userpo0_.appName, alias=Optional.of(appName4_5_)), CommonSelectItem(expression=userpo0_.appCode, alias=Optional.of(appCode3_5_)), CommonSelectItem(expression=userpo0_.deviceMc, alias=Optional.of(deviceMc6_5_)), CommonSelectItem(expression=userpo0_.createTime, alias=Optional.of(createTi2_5_)), CommonSelectItem(expression=userpo0_.appVersion, alias=Optional.of(appVersi5_5_)), CommonSelectItem(expression=userpo0_.deviceTokenType, alias=Optional.of(deviceTo8_5_)), CommonSelectItem(expression=userpo0_.userId, alias=Optional.of(userId12_5_))], groupByItems=[], orderByItems=[], limit=Limit(databaseType=MySQL, offset=LimitValue(value=1000, index=2, boundOpened=false), rowCount=LimitValue(value=1000, index=3, boundOpened=false)), subQueryStatement=null, subQueryStatements=[])
2018-12-25 16:14:20.481  INFO 34103 --- [           main] ShardingSphere-SQL                       : Actual SQL: main ::: select userpo0_.id as id1_5_, userpo0_.createTime as createTi2_5_, userpo0_.appCode as appCode3_5_, userpo0_.appName as appName4_5_, userpo0_.appVersion as appVersi5_5_, userpo0_.deviceMc as deviceMc6_5_, userpo0_.deviceToken as deviceTo7_5_, userpo0_.deviceTokenType as deviceTo8_5_, userpo0_.deviceType as deviceTy9_5_, userpo0_.modifyTime as modifyT10_5_, userpo0_.osVersion as osVersi11_5_, userpo0_.userId as userId12_5_, userpo0_.version as version13_5_ from push_user userpo0_ where (userpo0_.userId is null) and userpo0_.appName=? and userpo0_.modifyTime<? limit ?, ? ::: [[appcode_idxyer_iphone, 2018-12-25 16:14:18.693, 0, 2000]]

The text was updated successfully, but these errors were encountered:

wuqiu-ai · 2018-12-25T09:16:03Z

note:
1.shardshere version is 3.1.0.M1
2.This table is not sharding ，but execute

      //ParsingSQLRouter.java  method:route
      if (sqlStatement instanceof SelectStatement && null != ((SelectStatement) sqlStatement).getLimit()) {
            processLimit(parameters, (SelectStatement) sqlStatement);
       }

I suggest not sharding table do not rewrite limit.

tuohai666 · 2018-12-25T09:16:11Z

Is the result right? Although 2000 in log, you application actually got 1000 rows.
Is this SQL route to a single table?
What's the version?
Which project? (Sharding-JDBC or Sharding-Sphere)

I expect all 4 answers corresponding to the 4 questions above. Please answer them one by one.

tuohai666 · 2018-12-25T09:25:26Z

note:
1.shardshere version is 3.1.0.M1
2.This table is not sharding ，but execute
      //ParsingSQLRouter.java  method:route
      if (sqlStatement instanceof SelectStatement && null != ((SelectStatement) sqlStatement).getLimit()) {
            processLimit(parameters, (SelectStatement) sqlStatement);
       }
I suggest not sharding table do not rewrite limit.

You are right. In 3.1.0-SNAPSHOT, single routing sql will not rewrite limit.
3.1.0-SNAPSHOT is on the dev branch now, would you like to build it and verify the change? (This is also a question, expect your answer please sir, whether you say yes or no.)

wuqiu-ai · 2018-12-25T10:00:24Z

a moment ago，I use dev branch（version：3.1.0-SNAPSHOT）,junit test error.
see sql log limit 0, 2000

2018-12-25 17:50:37.198  INFO 36149 --- [           main] ShardingSphere-SQL                       : Logic SQL: select userpo0_.id as id1_5_, userpo0_.createTime as createTi2_5_, userpo0_.appCode as appCode3_5_, userpo0_.appName as appName4_5_, userpo0_.appVersion as appVersi5_5_, userpo0_.deviceMc as deviceMc6_5_, userpo0_.deviceToken as deviceTo7_5_, userpo0_.deviceTokenType as deviceTo8_5_, userpo0_.deviceType as deviceTy9_5_, userpo0_.modifyTime as modifyT10_5_, userpo0_.osVersion as osVersi11_5_, userpo0_.userId as userId12_5_, userpo0_.version as version13_5_ from push_user userpo0_ where (userpo0_.userId is null) and userpo0_.appName=? and userpo0_.modifyTime<? limit ?, ?
2018-12-25 17:50:37.198  INFO 36149 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@524023c8), containStar=false, selectListLastPosition=460, groupByLastPosition=0, items=[CommonSelectItem(expression=userpo0_.id, alias=Optional.of(id1_5_)), CommonSelectItem(expression=userpo0_.createTime, alias=Optional.of(createTi2_5_)), CommonSelectItem(expression=userpo0_.appCode, alias=Optional.of(appCode3_5_)), CommonSelectItem(expression=userpo0_.appName, alias=Optional.of(appName4_5_)), CommonSelectItem(expression=userpo0_.appVersion, alias=Optional.of(appVersi5_5_)), CommonSelectItem(expression=userpo0_.deviceMc, alias=Optional.of(deviceMc6_5_)), CommonSelectItem(expression=userpo0_.deviceToken, alias=Optional.of(deviceTo7_5_)), CommonSelectItem(expression=userpo0_.deviceTokenType, alias=Optional.of(deviceTo8_5_)), CommonSelectItem(expression=userpo0_.deviceType, alias=Optional.of(deviceTy9_5_)), CommonSelectItem(expression=userpo0_.modifyTime, alias=Optional.of(modifyT10_5_)), CommonSelectItem(expression=userpo0_.osVersion, alias=Optional.of(osVersi11_5_)), CommonSelectItem(expression=userpo0_.userId, alias=Optional.of(userId12_5_)), CommonSelectItem(expression=userpo0_.version, alias=Optional.of(version13_5_))], groupByItems=[], orderByItems=[], limit=Limit(databaseType=MySQL, offset=LimitValue(value=1000, index=2, boundOpened=false), rowCount=LimitValue(value=1000, index=3, boundOpened=false)), subQueryStatement=null, subQueryStatements=[])
2018-12-25 17:50:37.199  INFO 36149 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select userpo0_.id as id1_5_, userpo0_.createTime as createTi2_5_, userpo0_.appCode as appCode3_5_, userpo0_.appName as appName4_5_, userpo0_.appVersion as appVersi5_5_, userpo0_.deviceMc as deviceMc6_5_, userpo0_.deviceToken as deviceTo7_5_, userpo0_.deviceTokenType as deviceTo8_5_, userpo0_.deviceType as deviceTy9_5_, userpo0_.modifyTime as modifyT10_5_, userpo0_.osVersion as osVersi11_5_, userpo0_.userId as userId12_5_, userpo0_.version as version13_5_ from push_user userpo0_ where (userpo0_.userId is null) and userpo0_.appName=? and userpo0_.modifyTime<? limit ?, ? ::: [[appcode_idxyer_iphone, 2018-12-25 17:49:46.728, 0, 2000]]
2018-12-25 17:50:47.605  INFO 36149 --- [TaskExecutor-13] c.d.p.push.support.RedisResourceLoader   : redis info memory - {used_memory_rss_human=68.21M, maxmemory_policy=noeviction, used_memory_human=67.88M, mem_allocator=jemalloc-4.0.3, mem_fragmentation_ratio=1.00, used_memory_lua_human=37.00K, used_memory=71174752, maxmemory=268435456, used_memory_lua=37888, used_memory_peak=80980128, used_memory_rss=71520256, maxmemory_human=256.00M, used_memory_peak_human=77.23M, total_system_memory_human=125.81G, total_system_memory=135085195264}
2018-12-25 17:50:51.626  WARN 36149 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1146, SQLState: 42S02
2

Read the latest code，not find uplated,please check whether to merge code.

        boolean isSingleRouting = routingResult.isSingleRouting();
        if (sqlStatement instanceof SelectStatement && null != ((SelectStatement) sqlStatement).getLimit()) {
            processLimit(parameters, (SelectStatement) sqlStatement);
        }

tuohai666 · 2018-12-25T11:40:41Z

Is the result right? Although 2000 in log, you application actually got 1000 rows.

Is this SQL route to a single table?

What's the version?

Which project? (Sharding-JDBC or Sharding-Sphere)

I expect all 4 answers corresponding to the 4 questions above. Please answer them one by one.

Where's the answer?

wuqiu-ai · 2018-12-25T11:45:53Z

1.test result fail.
2.the sql is single table.
3.version:3.1.0-SNAPSHOT
4.sharding-jdbc

tuohai666 · 2018-12-25T12:01:47Z

1.test result fail.

Fail what?
What your test case exactly expect? Do not mention the log any more.

wuqiu-ai · 2018-12-25T13:04:32Z

    @Test
    public void testSelece(){
        PageRequest pageRequest = new PageRequest(1, 1000);
        userRepository.getWithoutUserIdListBy("appcode_idxyer_iphone",new Date(),pageRequest);
    }

    @Query("SELECT u FROM UserPo u WHERE u.userId is NULL and u.appName = ?1 and u.modifyTime < ?2")
     List<UserPo> getWithoutUserIdListBy(String appName, Date date, Pageable pageable);

the table is not sharding,so i expect limit 1000,1000,but sharding generate sql is 0,2000.

tuohai666 · 2018-12-25T13:10:19Z

Have you counted the result rows?

wuqiu-ai · 2018-12-25T13:17:34Z

yes，business code generate logical problems.
this answer should single routing sql will not rewrite limit，but now single routing sql will rewrite limit.

You are right. In 3.1.0-SNAPSHOT, single routing sql will not rewrite limit.
3.1.0-SNAPSHOT is on the dev branch now, would you like to build it and verify the change? (This is also a question, expect your answer please sir, whether you say yes or no.)

tuohai666 · 2018-12-26T00:24:57Z

OK. Can you debug 3.1.0-SNAPSHOT with breakpoint?
ParsingSQLRouter.java
line: 130
boolean isSingleRouting = routingResult.isSingleRouting();
To find out whether the SQL is sigle routed. If no, try to find the route result in routingResult(line: 128)

wuqiu-ai · 2018-12-26T02:44:45Z

OK. Can you debug 3.1.0-SNAPSHOT with breakpoint?
ParsingSQLRouter.java
line: 130
boolean isSingleRouting = routingResult.isSingleRouting();
To find out whether the SQL is sigle routed. If no, try to find the route result in routingResult(line: 128)

isSingleRouting is true.
a moment ago,I found another problem,not sharding table do not route the default datasource.

sharding config code:

   // 配置真实数据源
    Map<String, DataSource> dataSourceMap = new HashMap<>();
    dataSourceMap.put("ds0",sharding01DataSource());
    dataSourceMap.put("ds1",sharding02DataSource());
    dataSourceMap.put("ds2",sharding03DataSource());
    dataSourceMap.put("ds3",sharding04DataSource());
    dataSourceMap.put("main",moduleDataSource());

    // 配置Order表规则
    TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
    orderTableRuleConfig.setLogicTable("push_message");
    orderTableRuleConfig.setActualDataNodes("ds${0..3}.push_message");

    //分布式主键
    orderTableRuleConfig.setKeyGeneratorColumnName("id");
    ShardingDefaultKeyGenerator shardingDefaultKeyGenerator = new ShardingDefaultKeyGenerator();
    orderTableRuleConfig.setKeyGenerator(shardingDefaultKeyGenerator);

    // 配置分库策略
    orderTableRuleConfig.setDatabaseShardingStrategyConfig(
            new InlineShardingStrategyConfiguration("traceid", "ds${traceid%4}"));

    // 配置分片规则
    ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
    shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
    shardingRuleConfig.setDefaultDataSourceName("main");//默认主数据源配置

test code

@Test
    public void testSelece(){
        PageRequest pageRequest = new PageRequest(1, 1000);
        List<UserPo> userPos = userRepository.getWithoutUserIdListBy("appcode_idxyer_iphone",new Date(),pageRequest);
        log.info("size:"+userPos.size());
    }

userRepository

  @Query("SELECT u FROM UserPo u WHERE u.userId is NULL and u.appName = ?1 and u.modifyTime < ?2")
  List<UserPo> getWithoutUserIdListBy(String appName, Date date, Pageable pageable);

debug code:

variable tableNames size is 0
execute code: routingEngine = new UnicastRoutingEngine(shardingRule, tableNames);
route calcule,default use first element: result.getTableUnits().getTableUnits().add(new TableUnit(shardingRule.getShardingDataSourceNames().getDataSourceNames().iterator().next()));

private RoutingResult route(final SQLStatement sqlStatement, final ShardingConditions shardingConditions) {
        Collection<String> tableNames = sqlStatement.getTables().getTableNames();
        RoutingEngine routingEngine;
        if (sqlStatement instanceof UseStatement) {
            routingEngine = new IgnoreRoutingEngine();
        } else if (shardingRule.isAllBroadcastTables(tableNames) && !(sqlStatement instanceof SelectStatement)) {
            routingEngine = new DatabaseBroadcastRoutingEngine(shardingRule);
        } else if (sqlStatement instanceof DDLStatement || (sqlStatement instanceof DCLStatement && ((DCLStatement) sqlStatement).isGrantForSingleTable())) {
            routingEngine = new TableBroadcastRoutingEngine(shardingRule, sqlStatement);
        } else if (sqlStatement instanceof ShowDatabasesStatement || ((sqlStatement instanceof ShowTablesStatement || sqlStatement instanceof ShowTableStatusStatement) && tableNames.isEmpty())
                || sqlStatement instanceof SetParamStatement || sqlStatement instanceof ResetParamStatement) {
            routingEngine = new DatabaseBroadcastRoutingEngine(shardingRule);
        } else if (sqlStatement instanceof DCLStatement) {
            routingEngine = new InstanceBroadcastRoutingEngine(shardingRule, shardingDataSourceMetaData);
        } else if (shardingRule.isAllInDefaultDataSource(tableNames)) {
            routingEngine = new DefaultDatabaseRoutingEngine(shardingRule, tableNames);
        } else if (shardingConditions.isAlwaysFalse()) {
            routingEngine = new UnicastRoutingEngine(shardingRule, tableNames);
        } else if (sqlStatement instanceof DALStatement) {
            routingEngine = new UnicastRoutingEngine(shardingRule, tableNames);
        } else if (tableNames.isEmpty() && sqlStatement instanceof SelectStatement || shardingRule.isAllBroadcastTables(tableNames) && sqlStatement instanceof SelectStatement) {
            routingEngine = new UnicastRoutingEngine(shardingRule, tableNames);
        } else if (tableNames.isEmpty()) {
            routingEngine = new DatabaseBroadcastRoutingEngine(shardingRule);
        } else if (1 == tableNames.size() || shardingRule.isAllBindingTables(tableNames)) {
            routingEngine = new StandardRoutingEngine(shardingRule, tableNames.iterator().next(), shardingConditions);
        } else {
            // TODO config for cartesian set
            routingEngine = new ComplexRoutingEngine(shardingRule, tableNames, shardingConditions);
        }
        return routingEngine.route();

tuohai666 · 2018-12-26T02:58:47Z

isSingleRouting is true.

If so, the failure of your test case failure is because your test case is wrong. I believe the output will be a 1000 rows result set in 3.1.0-SNAPSHOT.
Your test case is invalid to me and others who can see this issue until you print the output result rows.

For the new problem, it should not be discuss in this issue. I suggest you open a new issue and never mention your test cases again(because they don't manke sense, and invalid enough). Instead you mention confs, SQLs and the expected result. Also please don't analyze, focus to the problem and describe it clearly, that's enough.

wuqiu-ai · 2018-12-26T03:52:31Z

ok,let me fix new problem.now test case still faild.

tuohai666 · 2019-01-07T09:09:05Z

This problem is fixed in dev branch.
Thanks a lot for your effort these days.

terrymanu added the type: bug label Dec 25, 2018

terrymanu assigned tuohai666 Dec 25, 2018

terrymanu added investigate and removed type: bug labels Dec 25, 2018

terrymanu added this to the 3.1.0.1 milestone Jan 4, 2019

terrymanu closed this as completed Jan 7, 2019

terrymanu added type: bug and removed investigate labels Jan 7, 2019

clambertus unassigned tuohai666 Jan 15, 2019

tuohai666 mentioned this issue Jan 16, 2019

The result list return wrong size when there's limit sub clause for single routing #1759

Closed

tuohai666 self-assigned this Jan 18, 2019

tuohai666 mentioned this issue Feb 18, 2019

pagination is not working well after 3.1.0 #1857

Closed

tuohai666 mentioned this issue May 10, 2019

When routing to only one table, can it do not rewrite limit? #2360

Closed

tuohai666 mentioned this issue May 22, 2019

limit bug fixed in which released version？ #2419

Closed

tuohai666 mentioned this issue Aug 20, 2019

When use mybatis recent version how to use executeQuery() #2891

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

spring data jpa auto limit page some grammar not support #1672

spring data jpa auto limit page some grammar not support #1672

wuqiu-ai commented Dec 25, 2018

wuqiu-ai commented Dec 25, 2018

tuohai666 commented Dec 25, 2018

tuohai666 commented Dec 25, 2018

wuqiu-ai commented Dec 25, 2018

tuohai666 commented Dec 25, 2018

wuqiu-ai commented Dec 25, 2018

tuohai666 commented Dec 25, 2018

wuqiu-ai commented Dec 25, 2018

tuohai666 commented Dec 25, 2018

wuqiu-ai commented Dec 25, 2018

tuohai666 commented Dec 26, 2018

wuqiu-ai commented Dec 26, 2018

tuohai666 commented Dec 26, 2018 •

edited

Loading

wuqiu-ai commented Dec 26, 2018

tuohai666 commented Jan 7, 2019

spring data jpa auto limit page some grammar not support #1672

spring data jpa auto limit page some grammar not support #1672

Comments

wuqiu-ai commented Dec 25, 2018

wuqiu-ai commented Dec 25, 2018

tuohai666 commented Dec 25, 2018

tuohai666 commented Dec 25, 2018

wuqiu-ai commented Dec 25, 2018

tuohai666 commented Dec 25, 2018

wuqiu-ai commented Dec 25, 2018

tuohai666 commented Dec 25, 2018

wuqiu-ai commented Dec 25, 2018

tuohai666 commented Dec 25, 2018

wuqiu-ai commented Dec 25, 2018

tuohai666 commented Dec 26, 2018

wuqiu-ai commented Dec 26, 2018

tuohai666 commented Dec 26, 2018 • edited Loading

wuqiu-ai commented Dec 26, 2018

tuohai666 commented Jan 7, 2019

tuohai666 commented Dec 26, 2018 •

edited

Loading