fankaifeng opened a new issue, #28966:
URL: https://github.com/apache/shardingsphere/issues/28966
## Bug Report
### Which version of ShardingSphere did you use?
5.1.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
JDBC
### Expected behavior
SELECT customer.id, tenant_id, user_id, user_phone, first_name, last_name,
CASE WHEN (ISNULL(full_name)=1) || (LENGTH(trim(full_name))=0) THEN
concat_ws('',first_name,last_name) ELSE full_name END AS full_name, sms_phone,
whats_app_phone, email, country, city, address, birthday, remark, source_type,
source_channel_key, customer.created_at, `status`, customer.updated_at,
time_zone, gender, customize_field, what_app_subscribe, sms_subscribe
FROM customer_123
WHERE customer.`is_deleted` = 0 AND tenant_id = 144 AND id IN (
SELECT customer_id
FROM customer_tag_mapping_123
WHERE tenant_id = 144 and tag_id IN ( 27306 ) GROUP BY customer_id HAVING
COUNT(*) = 1 ) ORDER BY id DESC
LIMIT 0, 10;
### Actual behavior
SELECT customer.id, tenant_id, user_id, user_phone, first_name, last_name,
CASE WHEN (ISNULL(full_name)=1) || (LENGTH(trim(full_name))=0) THEN
concat_ws('',first_name,last_name) ELSE full_name END AS full_name, sms_phone,
whats_app_phone, email, country, city, address, birthday, remark, source_type,
source_channel_key, customer.created_at, `status`, customer.updated_at,
time_zone, gender, customize_field, what_app_subscribe, sms_subscribe
FROM customer_123
WHERE customer.`is_deleted` = 0 AND tenant_id = 144 AND id IN (
SELECT customer_id
FROM customer_tag_mapping
WHERE tenant_id = 144 and tag_id IN ( 27306 ) GROUP BY customer_id HAVING
COUNT(*) = 1 ) ORDER BY id DESC
LIMIT 0, 10;
The query of table customer_tag_mapping was not routed to the correct
sharding table
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
# 分片规则配置
rules:
sharding:
# 分片算法配置
sharding-algorithms:
customer-inline:
# 分片算法类型
type: INLINE
props:
# 分片算法的行表达式
algorithm-expression: customer_$->{ tenant_id % 128 }
customer-tag-mapping-inline:
# 分片算法类型
type: INLINE
props:
# 分片算法的行表达式
algorithm-expression: customer_tag_mapping_$->{ tenant_id %
128 }
CREATE TABLE `customer_0` (
`id` bigint(20) NOT NULL,
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID',
....
CREATE TABLE `customer_tag_mapping_0` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID，自动增长',
`tenant_id` bigint(20) DEFAULT NULL,
`customer_id` bigint(20) NOT NULL COMMENT '客户ID',
`tag_id` bigint(20) NOT NULL COMMENT '标签ID',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '主动创建时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `tag_user` (`customer_id`,`tag_id`) USING BTREE,
KEY `fk_utm_tag_id` (`tag_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户和标签的关联';
### 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]
