You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/10/10 06:42:20 UTC
[GitHub] [shardingsphere] 564155611 opened a new issue, #14418: the left join sql result handler bug
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] closed issue #14418: the left join sql result handler bug
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #14418: the left join sql result handler bug
URL: https://github.com/apache/shardingsphere/issues/14418
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] the left join sql result handler bug [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #14418:
URL: https://github.com/apache/shardingsphere/issues/14418#issuecomment-2026021948
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] commented on issue #14418: the left join sql result handler bug
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #14418:
URL: https://github.com/apache/shardingsphere/issues/14418#issuecomment-1272350519
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org