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 2021/09/08 12:45:24 UTC
[GitHub] [shardingsphere] zJiaJun opened a new issue #12297: Error routing,unable to left join condition field as partition key
zJiaJun opened a new issue #12297:
URL: https://github.com/apache/shardingsphere/issues/12297
### Which version of ShardingSphere did you use?
```
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
```
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
```
spring.shardingsphere.sharding.default-data-source-name=crm
spring.shardingsphere.sharding.tables.wk_crm_customer.actual-data-nodes=crm.wk_crm_customer,crm.wk_crm_customer_other
spring.shardingsphere.sharding.tables.wk_crm_customer.key-generator.column=id
spring.shardingsphere.sharding.tables.wk_crm_customer.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.wk_crm_customer.table-strategy.standard.sharding-column=tenant_id
spring.shardingsphere.sharding.tables.wk_crm_customer.table-strategy.standard.precise-algorithm-class-name=xxxx.sharding.CustomShardingAlgorithm
```
```
SELECT crt.*,
ccc.customer_id,
ccc.customer_name,
ccb.business_name,
a.name AS contacts_name
FROM wk_crm_contract AS crt
LEFT JOIN wk_crm_customer AS ccc ON crt.customer_id = ccc.customer_id AND ccc.tenant_id = '1000'
LEFT JOIN wk_crm_business AS ccb ON crt.business_id = ccb.business_id
LEFT JOIN wk_crm_contacts AS a ON crt.contacts_id = a.contacts_id
WHERE crt.contract_id = 13
AND crt.tenant_id = '1000'
```
The correct route is to the WK_CRM_CUSTOMER table and perform a query
### Actual behavior
```
2021-09-08 20:08:13 crm INFO ShardingSphere-SQL - Actual SQL: crm ::: SELECT crt.*, ccc.customer_id, ccc.customer_name, ccb.business_name, a.name AS contacts_name, (SELECT count(1) FROM wk_crm_receivables WHERE contract_id = crt.contract_id) AS receivablesCount, (SELECT IFNULL(sum(money), 0) FROM wk_crm_receivables WHERE contract_id = crt.contract_id AND check_status = 1) AS receivablesMoney FROM wk_crm_contract AS crt LEFT JOIN wk_crm_customer AS ccc ON crt.customer_id = ccc.customer_id AND ccc.tenant_id = '1000' LEFT JOIN wk_crm_business AS ccb ON crt.business_id = ccb.business_id LEFT JOIN wk_crm_contacts AS a ON crt.contacts_id = a.contacts_id WHERE crt.contract_id = ? AND crt.tenant_id = '1000' ::: [13]
2021-09-08 20:08:13 crm INFO ShardingSphere-SQL - Actual SQL: crm ::: SELECT crt.*, ccc.customer_id, ccc.customer_name, ccb.business_name, a.name AS contacts_name, (SELECT count(1) FROM wk_crm_receivables WHERE contract_id = crt.contract_id) AS receivablesCount, (SELECT IFNULL(sum(money), 0) FROM wk_crm_receivables WHERE contract_id = crt.contract_id AND check_status = 1) AS receivablesMoney FROM wk_crm_contract AS crt LEFT JOIN wk_crm_customer_other AS ccc ON crt.customer_id = ccc.customer_id AND ccc.tenant_id = '1000' LEFT JOIN wk_crm_business AS ccb ON crt.business_id = ccb.business_id LEFT JOIN wk_crm_contacts AS a ON crt.contacts_id = a.contacts_id WHERE crt.contract_id = ? AND crt.tenant_id = '1000' ::: [13]
```
Two queries were executed
### Reason analyze (If you can)
I debugged the source code and found that the SQL parsed four tables, but the conditional (Collection<ShardingCondition>) set was empty, RouteResult.routeUnits set size is 2, so it's done 2 times, but if it's one, it's fine.
`whereSegment.andPredicates` include the crt.contract_id and the crt.tenant_id, not belong to wk_crm_ customer.
```
ShardingRouteEngineFactory.getShardingRoutingEngine
Collection<String> shardingTableNames = shardingRule.getShardingLogicTableNames(tableNames);
shardingTableNames is a wk_crm_customer with size equal 1
if (1 == shardingTableNames.size() || shardingRule.isAllBindingTables(shardingTableNames)) {
return new ShardingStandardRoutingEngine(shardingTableNames.iterator().next(), sqlStatementContext, shardingConditions, properties);
}
```
So I think the table in the left JOIN is resolved, but the condition (tenant_id is dynamically concatenated, not written in XML) is not resolved.
I tried Hint, but it didn't work, any suggestions?
### 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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12297: Error routing,unable to left join condition field as partition key
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12297:
URL: https://github.com/apache/shardingsphere/issues/12297#issuecomment-917787875
> @zJiaJun Hi thanks for your feedback, it looks this bug-fix will be included in the incoming release, right?
> @strongduanmu
@tristaZero Yes, master branch is correct. @zJiaJun You can wait for the `5.0.0-RC-1` version to be released.
--
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] tristaZero commented on issue #12297: Error routing,unable to left join condition field as partition key
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #12297:
URL: https://github.com/apache/shardingsphere/issues/12297#issuecomment-919676312
@zJiaJun Or you can just build a master branch to use it for your urgent case.
--
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] tristaZero commented on issue #12297: Error routing,unable to left join condition field as partition key
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #12297:
URL: https://github.com/apache/shardingsphere/issues/12297#issuecomment-917610427
@zJiaJun Hi thanks for your feedback, it looks this bug-fix will be included in the incoming release, right?
@strongduanmu
--
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] tristaZero closed issue #12297: Error routing,unable to left join condition field as partition key
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #12297:
URL: https://github.com/apache/shardingsphere/issues/12297
--
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] zJiaJun commented on issue #12297: Error routing,unable to left join condition field as partition key
Posted by GitBox <gi...@apache.org>.
zJiaJun commented on issue #12297:
URL: https://github.com/apache/shardingsphere/issues/12297#issuecomment-915828908
I tested version `5.0.0-beta`, and still have problem.
and https://github.com/apache/shardingsphere/pull/11463 this PR is not in `5.0.0-beta`,
so 5.0.0-beta doesnt't start at all, I think it's a very serious problem.
I don't know if there are any other bugs in `5.0.0-beta`, so I will use `4.1.1` and update the sql
--
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] strongduanmu commented on issue #12297: Error routing,unable to left join condition field as partition key
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12297:
URL: https://github.com/apache/shardingsphere/issues/12297#issuecomment-915663387
Hi @zJiaJun, thank you for your feedback. Can you try the latest `5.0.0-beta` version? The 4.x version does not support join queries very well.
--
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