lyflyy opened a new issue #10806: URL: https://github.com/apache/shardingsphere/issues/10806
## I also had this issue in 【4.1.1】 ## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore 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? 4.1.1 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-JDBC ### Expected behavior I want to return 10 pieces of data in the SQL result ### Actual behavior The rewritten SQL queries 2147483647 pieces of data ### Reason analyze (If you can) ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. ShardingSphere-SQL LOG detail ``` 2021-06-14 16:40:46.205 DEBUG 12448 --- [nio-8080-exec-1] c.s.d.mapper.OrdersMapper.officialDemo : ==> Preparing: SELECT user_id, SUM(price) `sum` FROM orders where create_time > ? and create_time < ? GROUP BY user_id ORDER BY `sum` DESC LIMIT 0, 10 2021-06-14 16:40:46.231 DEBUG 12448 --- [nio-8080-exec-1] c.s.d.mapper.OrdersMapper.officialDemo : ==> Parameters: 2020-07-09 13:54:57.0(Timestamp), 2022-07-19 13:54:57.0(Timestamp) 2021-06-14 16:40:47.088 INFO 12448 --- [nio-8080-exec-1] ShardingSphere-SQL : Logic SQL: SELECT user_id, SUM(price) `sum` FROM orders where create_time > ? and create_time < ? GROUP BY user_id ORDER BY `sum` DESC LIMIT 0, 10 2021-06-14 16:40:47.088 INFO 12448 --- [nio-8080-exec-1] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@109c4794, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@66e38b62), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@66e38b62, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=31, distinctRow=false, projections=[ColumnProjection(owner=null, name=user_id, alias=Optional.empty), AggregationProjection(type=SUM, innerExpression=(price), alias=Optional[sum], derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@1bba7f5d, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@3e02c417, paginationContext=org.apache.shardi ngsphere.sql.parser.binder.segment.select.pagination.PaginationContext@4c5084a4, containsSubquery=false) 2021-06-14 16:40:47.088 INFO 12448 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: dbs0 ::: SELECT user_id, SUM(price) `sum` FROM orders_6 where create_time > ? and create_time < ? GROUP BY user_id ORDER BY `sum` DESC LIMIT 0, 2147483647 ::: [2020-07-09 13:54:57.0, 2022-07-19 13:54:57.0] 2021-06-14 16:40:47.088 INFO 12448 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: dbs1 ::: SELECT user_id, SUM(price) `sum` FROM orders_6 where create_time > ? and create_time < ? GROUP BY user_id ORDER BY `sum` DESC LIMIT 0, 2147483647 ::: [2020-07-09 13:54:57.0, 2022-07-19 13:54:57.0] 2021-06-14 16:40:47.088 INFO 12448 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: dbs2 ::: SELECT user_id, SUM(price) `sum` FROM orders_6 where create_time > ? and create_time < ? GROUP BY user_id ORDER BY `sum` DESC LIMIT 0, 2147483647 ::: [2020-07-09 13:54:57.0, 2022-07-19 13:54:57.0] 2021-06-14 16:40:48.780 DEBUG 12448 --- [nio-8080-exec-1] c.s.d.mapper.OrdersMapper.officialDemo : <== Total: 10 ``` ### Example codes for reproduce this issue (such as a github link). 1. I use read/write separation and my configuration file is as follows ``` # 数据源 db0,db1 logging.level.com.sharding.demo.mapper=debug spring.shardingsphere.props.sql.show=true spring.shardingsphere.props.max.connections.size.per.query=1 mybatis-plus.mapper-locations=classpath:/mapper/*.xml spring.shardingsphere.datasource.names = db0,db1,db2,dbs0,dbs1,dbs2 spring.shardingsphere.datasource.db0.type = com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.db0.driver-class-name = com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.db0.jdbc-url = jdbc:mysql://127.0.0.1:3306/db0?characterEncoding=utf8&useSSL=false spring.shardingsphere.datasource.db0.username = root spring.shardingsphere.datasource.db0.password = root spring.shardingsphere.datasource.db1.type = com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.db1.jdbc-url = jdbc:mysql://127.0.0.1:3306/db1?characterEncoding=utf8&useSSL=false spring.shardingsphere.datasource.db1.username = root spring.shardingsphere.datasource.db1.password = root spring.shardingsphere.datasource.db2.type = com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.db2.driver-class-name = com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.db2.jdbc-url = jdbc:mysql://127.0.0.1:3306/db2?characterEncoding=utf8&useSSL=false spring.shardingsphere.datasource.db2.username = root spring.shardingsphere.datasource.db2.password = root spring.shardingsphere.datasource.dbs0.type = com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.dbs0.driver-class-name = com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.dbs0.jdbc-url = jdbc:mysql://127.0.0.1:3307/db0?characterEncoding=utf8&useSSL=false spring.shardingsphere.datasource.dbs0.username = root spring.shardingsphere.datasource.dbs0.password = root spring.shardingsphere.datasource.dbs1.type = com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.dbs1.driver-class-name = com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.dbs1.jdbc-url = jdbc:mysql://127.0.0.1:3307/db1?characterEncoding=utf8&useSSL=false spring.shardingsphere.datasource.dbs1.username = root spring.shardingsphere.datasource.dbs1.password = root spring.shardingsphere.datasource.dbs2.type = com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.dbs2.driver-class-name = com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.dbs2.jdbc-url = jdbc:mysql://127.0.0.1:3307/db2?characterEncoding=utf8&useSSL=false spring.shardingsphere.datasource.dbs2.username = root spring.shardingsphere.datasource.dbs2.password = root #master-slave 基于master1和master2主从集群实现读写分离 spring.shardingsphere.sharding.master-slave-rules.db0.master-data-source-name=db0 spring.shardingsphere.sharding.master-slave-rules.db0.slave-data-source-names=dbs0 spring.shardingsphere.sharding.master-slave-rules.db1.master-data-source-name=db1 spring.shardingsphere.sharding.master-slave-rules.db1.slave-data-source-names=dbs1 spring.shardingsphere.sharding.master-slave-rules.db2.master-data-source-name=db2 spring.shardingsphere.sharding.master-slave-rules.db2.slave-data-source-names=dbs2 #多个从库的时候使用负载均衡 spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN # user 表策略 用户按照性别进行分库， 2个库, id取模进行分表 3张表, db_male ,db_female user_0, user_1, user_2 # 分库策略 根据id取模确定数据进哪个数据库 spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column = sex spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression = db$->{sex % 2} # 分表策略 spring.shardingsphere.sharding.tables.user.actual-data-nodes = db$->{0..1}.user_$->{0..2} # 分表字段member_id spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column = id # 分表策略 根据member_id取模,确定数据最终落在那个表中 spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression = user_$->{id % 3} # 使用SNOWFLAKE算法生成主键 spring.shardingsphere.sharding.tables.user.key-generator.column = id spring.shardingsphere.sharding.tables.user.key-generator.type = SNOWFLAKE # 商品按照类型取模分库，3个库，id取模进行分表 db_type1, db_type2, db_type3, goods_0, goods_1, goods_2 # 分库策略 根据id取模确定数据进哪个数据库 spring.shardingsphere.sharding.tables.goods.database-strategy.inline.sharding-column = type spring.shardingsphere.sharding.tables.goods.database-strategy.inline.algorithm-expression = db$->{type % 3} # 分表策略 spring.shardingsphere.sharding.tables.goods.actual-data-nodes = db$->{0..2}.goods_$->{0..2} # 分表字段member_id spring.shardingsphere.sharding.tables.goods.table-strategy.inline.sharding-column = id # 分表策略 根据member_id取模,确定数据最终落在那个表中 spring.shardingsphere.sharding.tables.goods.table-strategy.inline.algorithm-expression = goods_$->{id % 3} # 使用SNOWFLAKE算法生成主键 spring.shardingsphere.sharding.tables.goods.key-generator.column = id spring.shardingsphere.sharding.tables.goods.key-generator.type = SNOWFLAKE # 订单按照下单月份进行分表，年份进行分库， db_2020,db_2021,db_2022 #声明虚拟表 spring.shardingsphere.sharding.tables.orders.actual-data-nodes=db$->{0..2}.orders_$->{0..11} #声明表内的主键 spring.shardingsphere.sharding.tables.orders.key-generator.column=id #声明主键生成策略 spring.shardingsphere.sharding.tables.orders.key-generator.type=SNOWFLAKE ##声明根据哪个字段进行分片 spring.shardingsphere.sharding.tables.orders.database-strategy.standard.sharding-column=create_time ##自定义分片规则类 ## 分库规则orders_auto_increment spring.shardingsphere.sharding.tables.orders.database-strategy.standard.precise-algorithm-class-name=com.sharding.demo.algorithm.OrderDatabaseShardingAlgorithm spring.shardingsphere.sharding.tables.orders.database-strategy.standard.range-algorithm-class-name=com.sharding.demo.algorithm.OrderDatabaseShardingRangeAlgorithm #声明根据哪个字段进行分片 spring.shardingsphere.sharding.tables.orders.table-strategy.standard.sharding-column=create_time #自定义分片规则类 spring.shardingsphere.sharding.tables.orders.table-strategy.standard.precise-algorithm-class-name=com.sharding.demo.algorithm.OrderTableShardingAlgorithm spring.shardingsphere.sharding.tables.orders.table-strategy.standard.range-algorithm-class-name=com.sharding.demo.algorithm.OrderTableShardingRangeAlgorithm # 配置公共表 # 一个实体类对应两张表，覆盖 spring.main.allow-bean-definition-overriding=true spring.shardingsphere.sharding.broadcast-tables=common_dict # 配置数据库中 t_dict 表主键 dict_id 生成策略 SNOWFLAKE 雪花算法 spring.shardingsphere.sharding.tables.common_dict.key-generator.column=dict_id spring.shardingsphere.sharding.tables.common_dict.key-generator.type=SNOWFLAKE ``` 2. i use mybatis ``` <select id="officialDemo" resultType="com.sharding.demo.vo.OffocialDemoVo"> SELECT user_id, SUM(price) `sum` FROM orders <if test="orders.createTimeStart != null"> where create_time > #{orders.createTimeStart} and create_time < #{orders.createTimeEnd} </if> GROUP BY user_id ORDER BY `sum` DESC LIMIT 0, 10 </select> ``` _Originally posted by @lyflyy in https://github.com/apache/shardingsphere/issues/2062#issuecomment-860525708_ -- 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: [email protected]
