{"action":"created","issue":{"url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/1672","repository_url":"https://api.github.com/repos/sharding-sphere/sharding-sphere","labels_url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/1672/labels{/name}","comments_url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/1672/comments","events_url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/1672/events","html_url":"https://github.com/sharding-sphere/sharding-sphere/issues/1672","id":393984555,"node_id":"MDU6SXNzdWUzOTM5ODQ1NTU=","number":1672,"title":"spring data jpa auto limit page some grammar  not support","user":{"login":"ppj19891020","id":4514718,"node_id":"MDQ6VXNlcjQ1MTQ3MTg=","avatar_url":"https://avatars2.githubusercontent.com/u/4514718?v=4","gravatar_id":"","url":"https://api.github.com/users/ppj19891020","html_url":"https://github.com/ppj19891020","followers_url":"https://api.github.com/users/ppj19891020/followers","following_url":"https://api.github.com/users/ppj19891020/following{/other_user}","gists_url":"https://api.github.com/users/ppj19891020/gists{/gist_id}","starred_url":"https://api.github.com/users/ppj19891020/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ppj19891020/subscriptions","organizations_url":"https://api.github.com/users/ppj19891020/orgs","repos_url":"https://api.github.com/users/ppj19891020/repos","events_url":"https://api.github.com/users/ppj19891020/events{/privacy}","received_events_url":"https://api.github.com/users/ppj19891020/received_events","type":"User","site_admin":false},"labels":[{"id":1010965807,"node_id":"MDU6TGFiZWwxMDEwOTY1ODA3","url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/labels/investigate","name":"investigate","color":"f9d0c4","default":false}],"state":"open","locked":false,"assignee":{"login":"tuohai666","id":24643893,"node_id":"MDQ6VXNlcjI0NjQzODkz","avatar_url":"https://avatars2.githubusercontent.com/u/24643893?v=4","gravatar_id":"","url":"https://api.github.com/users/tuohai666","html_url":"https://github.com/tuohai666","followers_url":"https://api.github.com/users/tuohai666/followers","following_url":"https://api.github.com/users/tuohai666/following{/other_user}","gists_url":"https://api.github.com/users/tuohai666/gists{/gist_id}","starred_url":"https://api.github.com/users/tuohai666/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tuohai666/subscriptions","organizations_url":"https://api.github.com/users/tuohai666/orgs","repos_url":"https://api.github.com/users/tuohai666/repos","events_url":"https://api.github.com/users/tuohai666/events{/privacy}","received_events_url":"https://api.github.com/users/tuohai666/received_events","type":"User","site_admin":false},"assignees":[{"login":"tuohai666","id":24643893,"node_id":"MDQ6VXNlcjI0NjQzODkz","avatar_url":"https://avatars2.githubusercontent.com/u/24643893?v=4","gravatar_id":"","url":"https://api.github.com/users/tuohai666","html_url":"https://github.com/tuohai666","followers_url":"https://api.github.com/users/tuohai666/followers","following_url":"https://api.github.com/users/tuohai666/following{/other_user}","gists_url":"https://api.github.com/users/tuohai666/gists{/gist_id}","starred_url":"https://api.github.com/users/tuohai666/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tuohai666/subscriptions","organizations_url":"https://api.github.com/users/tuohai666/orgs","repos_url":"https://api.github.com/users/tuohai666/repos","events_url":"https://api.github.com/users/tuohai666/events{/privacy}","received_events_url":"https://api.github.com/users/tuohai666/received_events","type":"User","site_admin":false}],"milestone":null,"comments":11,"created_at":"2018-12-25T08:19:50Z","updated_at":"2018-12-26T02:44:45Z","closed_at":null,"author_association":"NONE","body":"junit test:\r\n> page is 1 and  pagesize is 1000\r\n```\r\n@Test\r\n    public void testSelece(){\r\n        PageRequest pageRequest = new PageRequest(1, 1000);\r\n        userRepository.getWithoutUserIdListBy(\"appcode_idxyer_iphone\",new Date(),pageRequest);\r\n    }\r\n```\r\nUserRepository interface:\r\n```\r\n  @Query(\"SELECT u FROM UserPo u WHERE u.userId is NULL and u.appName = ?1 and u.modifyTime < ?2\")\r\n  List<UserPo> getWithoutUserIdListBy(String appName, Date date, Pageable pageable);\r\n```\r\n\r\n\r\nsee sql log limit 0, 2000\r\n```\r\n2018-12-25 16:14:20.480  INFO 34103 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding\r\n2018-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 ?, ?\r\n2018-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=[])\r\n2018-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]]\r\n```"},"comment":{"url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/comments/449891925","html_url":"https://github.com/sharding-sphere/sharding-sphere/issues/1672#issuecomment-449891925","issue_url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/1672","id":449891925,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0OTg5MTkyNQ==","user":{"login":"ppj19891020","id":4514718,"node_id":"MDQ6VXNlcjQ1MTQ3MTg=","avatar_url":"https://avatars2.githubusercontent.com/u/4514718?v=4","gravatar_id":"","url":"https://api.github.com/users/ppj19891020","html_url":"https://github.com/ppj19891020","followers_url":"https://api.github.com/users/ppj19891020/followers","following_url":"https://api.github.com/users/ppj19891020/following{/other_user}","gists_url":"https://api.github.com/users/ppj19891020/gists{/gist_id}","starred_url":"https://api.github.com/users/ppj19891020/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ppj19891020/subscriptions","organizations_url":"https://api.github.com/users/ppj19891020/orgs","repos_url":"https://api.github.com/users/ppj19891020/repos","events_url":"https://api.github.com/users/ppj19891020/events{/privacy}","received_events_url":"https://api.github.com/users/ppj19891020/received_events","type":"User","site_admin":false},"created_at":"2018-12-26T02:44:45Z","updated_at":"2018-12-26T02:44:45Z","author_association":"NONE","body":"> OK. Can you debug 3.1.0-SNAPSHOT with breakpoint?\r\n> ParsingSQLRouter.java\r\n> line: 130\r\n> boolean isSingleRouting = routingResult.isSingleRouting();\r\n> To find out whether the SQL is sigle routed. If no, try to find the route result in routingResult(line: 128)\r\n\r\nisSingleRouting is true.\r\na moment ago,I found another problem,not sharding table do not route the default datasource.\r\n\r\nsharding config code:\r\n```\r\n   // 配置真实数据源\r\n    Map<String, DataSource> dataSourceMap = new HashMap<>();\r\n    dataSourceMap.put(\"ds0\",sharding01DataSource());\r\n    dataSourceMap.put(\"ds1\",sharding02DataSource());\r\n    dataSourceMap.put(\"ds2\",sharding03DataSource());\r\n    dataSourceMap.put(\"ds3\",sharding04DataSource());\r\n    dataSourceMap.put(\"main\",moduleDataSource());\r\n\r\n    // 配置Order表规则\r\n    TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();\r\n    orderTableRuleConfig.setLogicTable(\"push_message\");\r\n    orderTableRuleConfig.setActualDataNodes(\"ds${0..3}.push_message\");\r\n\r\n    //分布式主键\r\n    orderTableRuleConfig.setKeyGeneratorColumnName(\"id\");\r\n    ShardingDefaultKeyGenerator shardingDefaultKeyGenerator = new ShardingDefaultKeyGenerator();\r\n    orderTableRuleConfig.setKeyGenerator(shardingDefaultKeyGenerator);\r\n\r\n    // 配置分库策略\r\n    orderTableRuleConfig.setDatabaseShardingStrategyConfig(\r\n            new InlineShardingStrategyConfiguration(\"traceid\", \"ds${traceid%4}\"));\r\n\r\n    // 配置分片规则\r\n    ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();\r\n    shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);\r\n    shardingRuleConfig.setDefaultDataSourceName(\"main\");//默认主数据源配置\r\n```\r\n\r\ntest code\r\n```\r\n@Test\r\n    public void testSelece(){\r\n        PageRequest pageRequest = new PageRequest(1, 1000);\r\n        List<UserPo> userPos = userRepository.getWithoutUserIdListBy(\"appcode_idxyer_iphone\",new Date(),pageRequest);\r\n        log.info(\"size:\"+userPos.size());\r\n    }\r\n```\r\nuserRepository\r\n```\r\n  @Query(\"SELECT u FROM UserPo u WHERE u.userId is NULL and u.appName = ?1 and u.modifyTime < ?2\")\r\n  List<UserPo> getWithoutUserIdListBy(String appName, Date date, Pageable pageable);\r\n```\r\n\r\ndebug code:\r\n1. variable tableNames size is 0\r\n2. execute code: routingEngine = new UnicastRoutingEngine(shardingRule, tableNames);\r\n3. route calcule,default use first element:  result.getTableUnits().getTableUnits().add(new TableUnit(shardingRule.getShardingDataSourceNames().getDataSourceNames().iterator().next()));\r\n\r\n```\r\nprivate RoutingResult route(final SQLStatement sqlStatement, final ShardingConditions shardingConditions) {\r\n        Collection<String> tableNames = sqlStatement.getTables().getTableNames();\r\n        RoutingEngine routingEngine;\r\n        if (sqlStatement instanceof UseStatement) {\r\n            routingEngine = new IgnoreRoutingEngine();\r\n        } else if (shardingRule.isAllBroadcastTables(tableNames) && !(sqlStatement instanceof SelectStatement)) {\r\n            routingEngine = new DatabaseBroadcastRoutingEngine(shardingRule);\r\n        } else if (sqlStatement instanceof DDLStatement || (sqlStatement instanceof DCLStatement && ((DCLStatement) sqlStatement).isGrantForSingleTable())) {\r\n            routingEngine = new TableBroadcastRoutingEngine(shardingRule, sqlStatement);\r\n        } else if (sqlStatement instanceof ShowDatabasesStatement || ((sqlStatement instanceof ShowTablesStatement || sqlStatement instanceof ShowTableStatusStatement) && tableNames.isEmpty())\r\n                || sqlStatement instanceof SetParamStatement || sqlStatement instanceof ResetParamStatement) {\r\n            routingEngine = new DatabaseBroadcastRoutingEngine(shardingRule);\r\n        } else if (sqlStatement instanceof DCLStatement) {\r\n            routingEngine = new InstanceBroadcastRoutingEngine(shardingRule, shardingDataSourceMetaData);\r\n        } else if (shardingRule.isAllInDefaultDataSource(tableNames)) {\r\n            routingEngine = new DefaultDatabaseRoutingEngine(shardingRule, tableNames);\r\n        } else if (shardingConditions.isAlwaysFalse()) {\r\n            routingEngine = new UnicastRoutingEngine(shardingRule, tableNames);\r\n        } else if (sqlStatement instanceof DALStatement) {\r\n            routingEngine = new UnicastRoutingEngine(shardingRule, tableNames);\r\n        } else if (tableNames.isEmpty() && sqlStatement instanceof SelectStatement || shardingRule.isAllBroadcastTables(tableNames) && sqlStatement instanceof SelectStatement) {\r\n            routingEngine = new UnicastRoutingEngine(shardingRule, tableNames);\r\n        } else if (tableNames.isEmpty()) {\r\n            routingEngine = new DatabaseBroadcastRoutingEngine(shardingRule);\r\n        } else if (1 == tableNames.size() || shardingRule.isAllBindingTables(tableNames)) {\r\n            routingEngine = new StandardRoutingEngine(shardingRule, tableNames.iterator().next(), shardingConditions);\r\n        } else {\r\n            // TODO config for cartesian set\r\n            routingEngine = new ComplexRoutingEngine(shardingRule, tableNames, shardingConditions);\r\n        }\r\n        return routingEngine.route();\r\n\r\n```"}}
{"actor":{"display_login":"ppj19891020"}}