564155611 opened a new issue, #14418: URL: https://github.com/apache/shardingsphere/issues/14418
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: ### `application.properties` for [my spring boot project](https://github.com/564155611/sharding-sphere-demo "click this"): ```properties #打印sql路由结果 spring.shardingsphere.props.sql-show=true # 配置真实数据源 spring.shardingsphere.datasource.names=m0,s0,m1,s1,m2,s2 # 配置第 1 个主从 spring.shardingsphere.datasource.m0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m0.jdbc-url=jdbc:mysql://192.168.12.161:3306/sharding1?characterEncoding=UTF-8&useSSL=false&useUnicode=true&serverTimezone=UTC&allowPublicKeyRetrieval=true spring.shardingsphere.datasource.m0.username=root spring.shardingsphere.datasource.m0.password=123456 #从 spring.shardingsphere.datasource.s0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.s0.jdbc-url=jdbc:mysql://192.168.12.161:3307/sharding1?characterEncoding=UTF-8&useSSL=false&useUnicode=true&serverTimezone=UTC&allowPublicKeyRetrieval=true spring.shardingsphere.datasource.s0.username=root spring.shardingsphere.datasource.s0.password=123456 #主从负载策略配置 spring.shardingsphere.rules.readwrite-splitting.data-sources.ms0.write-data-source-name=m0 spring.shardingsphere.rules.readwrite-splitting.data-sources.ms0.read-data-source-names[0]=s0 spring.shardingsphere.rules.readwrite-splitting.data-sources.ms0.load-balancer-name=random # 配置第 2 个主从 spring.shardingsphere.datasource.m1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m1.jdbc-url=jdbc:mysql://192.168.12.162:3306/sharding2?characterEncoding=UTF-8&useSSL=false&useUnicode=true&serverTimezone=UTC&allowPublicKeyRetrieval=true spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=123456 #从 spring.shardingsphere.datasource.s1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.s1.jdbc-url=jdbc:mysql://192.168.12.162:3307/sharding2?characterEncoding=UTF-8&useSSL=false&useUnicode=true&serverTimezone=UTC&allowPublicKeyRetrieval=true spring.shardingsphere.datasource.s1.username=root spring.shardingsphere.datasource.s1.password=123456 #主从负载策略配置 spring.shardingsphere.rules.readwrite-splitting.data-sources.ms1.write-data-source-name=m1 spring.shardingsphere.rules.readwrite-splitting.data-sources.ms1.read-data-source-names[0]=s1 spring.shardingsphere.rules.readwrite-splitting.data-sources.ms1.load-balancer-name=round-robin # 配置第 3 个主从 spring.shardingsphere.datasource.m2.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m2.jdbc-url=jdbc:mysql://192.168.12.163:3306/sharding3?characterEncoding=UTF-8&useSSL=false&useUnicode=true&serverTimezone=UTC&allowPublicKeyRetrieval=true spring.shardingsphere.datasource.m2.username=root spring.shardingsphere.datasource.m2.password=123456 #从 spring.shardingsphere.datasource.s2.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.s2.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.s2.jdbc-url=jdbc:mysql://192.168.12.163:3307/sharding3?characterEncoding=UTF-8&useSSL=false&useUnicode=true&serverTimezone=UTC&allowPublicKeyRetrieval=true spring.shardingsphere.datasource.s2.username=root spring.shardingsphere.datasource.s2.password=123456 #主从负载策略配置 spring.shardingsphere.rules.readwrite-splitting.data-sources.ms2.write-data-source-name=m2 spring.shardingsphere.rules.readwrite-splitting.data-sources.ms2.read-data-source-names[0]=s2 spring.shardingsphere.rules.readwrite-splitting.data-sources.ms2.load-balancer-name=random #负载均衡的策略引用 spring.shardingsphere.rules.readwrite-splitting.load-balancers.random.type=RANDOM spring.shardingsphere.rules.readwrite-splitting.load-balancers.round-robin.type=ROUND_ROBIN # 配置 t_order 表规则 spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=ms$->{0..2}.t_order_$->{0..1} # 配置分库策略 spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-column=user_id spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name=database-inline # 配置分表策略 spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=order-inline # 配置 t_order_item 表规则... spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=ms$->{0..2}.t_order_item_$->{0..1} # 配置分库策略 spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-column=user_id spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-algorithm-name=database-inline # 配置分表策略 spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-column=order_id spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-name=order-item-inline # 配置 分片算法 spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINE spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expression=ms$->{user_id % 3} spring.shardingsphere.rules.sharding.sharding-algorithms.order-inline.type=INLINE spring.shardingsphere.rules.sharding.sharding-algorithms.order-inline.props.algorithm-expression=t_order_$->{order_id % 2} spring.shardingsphere.rules.sharding.sharding-algorithms.order-item-inline.type=INLINE spring.shardingsphere.rules.sharding.sharding-algorithms.order-item-inline.props.algorithm-expression=t_order_item_$->{order_id % 2} #spring.shardingsphere.rules.sharding.binding-tables[0]=t_order,t_order_item ``` ### `t_order_1` | order\_id | total\_amount | user\_id | | :--- | :--- | :--- | | 3 | 300.00 | 3 | ### `t_order_item_0` | id | order\_id | product\_id | product\_num | sold\_price | user\_id | | :--- | :--- | :--- | :--- | :--- | :--- | | <null> | <null> | <null> | <null> | <null> | <null> | ### `t_order_item_1` | id | order\_id | product\_id | product\_num | sold\_price | user\_id | | :--- | :--- | :--- | :--- | :--- | :--- | | 3 | 3 | 3 | 1 | 300.00 | 3 | **THE SQL**:: select o.user_id,o.order_id,i.id from t_order o left join t_order_item i where o.order_id=i.order_id ```java List<Map<String, Object>> mapList = orderItemMapper.selectOrderJoinItemsByOrderId(1); log.info("mapList.size()==>{}",mapList.size());//the result is 2 (NOT USING INNER JOIN) ``` **puzzle**:There should be only one record in the query result, but there are two records in the query. The result obtained from "actual sql:: t_order_1 left join t_order_item_0" should be removed from the final result set. Although there are many alternatives to avoid this problem (such as inner join, binding-tables), I hope you can avoid this problem from the code logic - 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? 5.0.0 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? Sharding-JDBC ### 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). -- 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. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
