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/09 10:36:41 UTC
[GitHub] [shardingsphere] 448700174 opened a new issue #12315: left join result in cross table merge
448700174 opened a new issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315
## Question
My SQL is as follows, and the sharding column is tenant_id
select count(*)
from care_task t,care_task_item i left join (select * from care_task_detail d where d.tenant_id = #{tenantId}) d on d.task_item_id = i.id
where i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1
and t.tenant_id = #{tenantId}
and i.tenant_id = #{tenantId}
--
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] RaigorJiang commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-917879808
Hello @448700174
I read your sharding algorithm `TenantIdShardingAlgorithm `, where:
`String actualTableName = logicTableName + "_" + tenantId;`
Does it mean that the suffix of the expected actual table name is consistent with tenant_id?
Then, for the last logic SQL, what is the tenant_id entered?
> But another SQL can't work even I config as this.
SELECT
cps.id,
cpt.area_id,
cpt.area_name,
cps.start_time,
cps.end_time,
cps.schedule_count,
cps.done_count
FROM care_patrol_task cpt,care_patrol_subtask cps
WHERE cpt.tenant_id = #{tenantId}
and cpt.id = cps.task_id
AND cps.carer_id = #{carerId}
AND cps.status = #{status}
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-915997997
the actual sql is :
select i.id,i.operate_time,i.operator_name,group_concat("{\"k\":\"",d.option_name,"\",\"v\":\"",d.option_value, "\"}") options
from care_task_12 t,care_task_item_12 i left join care_task_detail_21 d on d.task_item_id = i.id and d.tenant_id = ?
where i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1
and t.tenant_id = ?
and i.tenant_id = ?
select i.id,i.operate_time,i.operator_name,group_concat("{\"k\":\"",d.option_name,"\",\"v\":\"",d.option_value, "\"}") options
from care_task_12 t,care_task_item_12 i left join care_task_detail_22 d on d.task_item_id = i.id and d.tenant_id = ?
where i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1
and t.tenant_id = ?
and i.tenant_id = ?
...
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-918071381
I used various format sql, all of them is the same result.
--
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 #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-917602013
@448700174 Thanks for your question and answer. ;-)
Which version do you use?
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-917795037
But another SQL can't work even I config as this.
SELECT
cps.id,
cpt.area_id,
cpt.area_name,
cps.start_time,
cps.end_time,
cps.schedule_count,
cps.done_count
FROM care_patrol_task cpt,care_patrol_subtask cps
WHERE cpt.tenant_id = #{tenantId}
and cpt.id = cps.task_id
AND cps.carer_id = #{carerId}
AND cps.status = #{status}
binding-tables:
- care_task,care_task_item,care_task_detail
- care_patrol_task,care_patrol_item
- care_patrol_task,care_patrol_subtask
The actual SQL is like this:
2021-09-13 10:55:39.265 [http-nio-8099-exec-1] INFO ShardingSphere-SQL - Actual SQL: ds ::: SELECT
cps.id,
cpt.area_id,
cpt.area_name,
cps.start_time,
cps.end_time,
cps.schedule_count,
cps.done_count
FROM care_patrol_task_76 cpt,care_patrol_subtask_76 cps
WHERE cpt.tenant_id = ?
and cpt.id = cps.task_id
AND cps.carer_id = ?
AND cps.status = ? ::: [12, 14, 1]
2021-09-13 10:55:39.265 [http-nio-8099-exec-1] INFO ShardingSphere-SQL - Actual SQL: ds ::: SELECT
cps.id,
cpt.area_id,
cpt.area_name,
cps.start_time,
cps.end_time,
cps.schedule_count,
cps.done_count
FROM care_patrol_task_77 cpt,care_patrol_subtask_77 cps
WHERE cpt.tenant_id = ?
and cpt.id = cps.task_id
AND cps.carer_id = ?
AND cps.status = ? ::: [12, 14, 1]
2021-09-13 10:55:39.265 [http-nio-8099-exec-1] INFO ShardingSphere-SQL - Actual SQL: ds ::: SELECT
cps.id,
cpt.area_id,
cpt.area_name,
cps.start_time,
cps.end_time,
cps.schedule_count,
cps.done_count
FROM care_patrol_task_78 cpt,care_patrol_subtask_78 cps
WHERE cpt.tenant_id = ?
and cpt.id = cps.task_id
AND cps.carer_id = ?
AND cps.status = ? ::: [12, 14, 1]
--
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] RaigorJiang commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-916596725
@448700174
Sorry, the information you gave is a bit scattered. Please provide more information:
- ShardingSphere-JDBC or ShardingSphere-Proxy?
- which vision do you use?
- what's your `complete` sharding configuration? I dont' see sharding algorithm.
- what are the Logic SQL and Actual SQL? Set props.sql-show to true to print them.
In addition, there is `count(*)` in your SQL, but `Actual SQL` does not contain `count`? Please provide the correct info.
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-921509561
But there is a new problem, some column's value cannot map to entity.
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-921525255
And another simple query execute failed:
2021-09-17 14:07:30.209 [http-nio-8099-exec-9] INFO ShardingSphere-SQL - Logic SQL: select s.* from care_patrol_subtask s where s.tenant_id = ? and s.task_id = ? and s.carer_id=?
2021-09-17 14:07:30.209 [http-nio-8099-exec-9] INFO ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2021-09-17 14:07:30.209 [http-nio-8099-exec-9] INFO ShardingSphere-SQL - Actual SQL: ds ::: select s.* from care_patrol_subtask_1 s where s.tenant_id = ? and s.task_id = ? and s.carer_id=? ::: [12, 1438503092900110336, 14]
2021-09-17 14:07:30.209 [http-nio-8099-exec-9] INFO ShardingSphere-SQL - Actual SQL: ds ::: select s.* from care_patrol_subtask_2 s where s.tenant_id = ? and s.task_id = ? and s.carer_id=? ::: [12, 1438503092900110336, 14]
2021-09-17 14:07:30.209 [http-nio-8099-exec-9] INFO ShardingSphere-SQL - Actual SQL: ds ::: select s.* from care_patrol_subtask_3 s where s.tenant_id = ? and s.task_id = ? and s.carer_id=? ::: [12, 1438503092900110336, 14]
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-916592582
In class ShardingStandardRoutingEngine:
private Collection<DataNode> routeTables(final TableRule tableRule, final String routedDataSource,
final ShardingStrategy tableShardingStrategy, final List<ShardingConditionValue> tableShardingValues) {
Collection<String> availableTargetTables = tableRule.getActualTableNames(routedDataSource);
Collection<String> routedTables = new LinkedHashSet<>(tableShardingValues.isEmpty()
? availableTargetTables : tableShardingStrategy.doSharding(**availableTargetTables**, tableShardingValues, properties));
Collection<DataNode> result = new LinkedList<>();
for (String each : routedTables) {
result.add(new DataNode(routedDataSource, each));
}
return result;
}
Is there a bug ?
--
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] RaigorJiang commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-918101650
@strongduanmu
Please take a look, is it a routing problem?
--
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] 448700174 edited a comment on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 edited a comment on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-921509561
But there is a new problem, some column's value cannot map to entity.
The failed column including account_type,status,is_accounted,creator_id,and all left join columns(category,category_type,category_title,goods_title,evidence).
The origin SQL is:
SELECT
cod.*,
cc.category,
cc.category_type,
cc.category_title,
cg.goods_title,
cg.evidence
FROM
care_order_detail cod
left join care_goods cg on cg.id = cod.goods_id
left join care_category cc on cc.id = cg.category_id
WHERE 1=1
AND cod.id = #{id}
and cod.tenant_id = #{tenantId}
--
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] tuichenchuxin commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-920066229
> Which jar I need update?
The stable version will be released soon, you can try to get master branch to test now.
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-918081687
And I think the reason is the binding-bables of care_patrol_subtask not effictive.
binding-tables:
- care_task,care_task_item,care_task_detail
- care_patrol_task,care_patrol_subtask,care_patrol_item
Is there something I missed?
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-916119228
when I add this configuration:
binding-tables:
- care_task,care_task_item,care_task_detail
Got the following errors:
Route table care_task_12 does not exist, available actual table: [care_task_detail_1, care_task_detail_2, care_task_detail_3, care_task_detail_4, care_task_detail_5, care_task_detail_6, care_task_detail_7, care_task_detail_8, ...
What's wrong with my configuration?
--
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] RaigorJiang commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-915992937
Hi @448700174
The issue description only shows your SQL, what do you want to express?
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-916000300
Can I use binding-tables to solve the problem?
--
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 #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-918106653
Thank you for feedback @448700174, i will check this issue later.
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-917791174
It's 5.0.0-beta
--
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] 448700174 removed a comment on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 removed a comment on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-921521823
And another simple query failed:
select s.* from care_patrol_subtask s where s.tenant_id = ? and s.task_id = ? and s.carer_id=?
The actual SQL is:
2021-09-17 13:55:59.570 [http-nio-8099-exec-5] INFO ShardingSphere-SQL - Actual SQL: ds ::: select s.* from care_patrol_subtask_1 s where s.tenant_id = ? and s.task_id = ? and s.carer_id=? ::: [12, 1438502973387612160, 14]
2021-09-17 13:55:59.570 [http-nio-8099-exec-5] INFO ShardingSphere-SQL - Actual SQL: ds ::: select s.* from care_patrol_subtask_2 s where s.tenant_id = ? and s.task_id = ? and s.carer_id=? ::: [12, 1438502973387612160, 14]
2021-09-17 13:55:59.570 [http-nio-8099-exec-5] INFO ShardingSphere-SQL - Actual SQL: ds ::: select s.* from care_patrol_subtask_3 s where s.tenant_id = ? and s.task_id = ? and s.carer_id=? ::: [12, 1438502973387612160, 14]
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-921521823
And another simple query failed:
select s.* from care_patrol_subtask s where s.tenant_id = ? and s.task_id = ? and s.carer_id=?
The actual SQL is:
2021-09-17 13:55:59.570 [http-nio-8099-exec-5] INFO ShardingSphere-SQL - Actual SQL: ds ::: select s.* from care_patrol_subtask_1 s where s.tenant_id = ? and s.task_id = ? and s.carer_id=? ::: [12, 1438502973387612160, 14]
2021-09-17 13:55:59.570 [http-nio-8099-exec-5] INFO ShardingSphere-SQL - Actual SQL: ds ::: select s.* from care_patrol_subtask_2 s where s.tenant_id = ? and s.task_id = ? and s.carer_id=? ::: [12, 1438502973387612160, 14]
2021-09-17 13:55:59.570 [http-nio-8099-exec-5] INFO ShardingSphere-SQL - Actual SQL: ds ::: select s.* from care_patrol_subtask_3 s where s.tenant_id = ? and s.task_id = ? and s.carer_id=? ::: [12, 1438502973387612160, 14]
--
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] 448700174 edited a comment on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 edited a comment on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-916835191
select count(*)
from care_task t,care_task_item i left join (select * from care_task_detail d where d.tenant_id = #{tenantId}) d on d.task_item_id = i.id
where i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1 and t.tenant_id = #{tenantId}
binding-tables:
- care_task,care_task_item,care_task_detail
This can work.
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-916603164
If I remve the binding-tables config,The LogicSQL is:
...
2021-09-10 11:27:54.248 [http-nio-8099-exec-1] INFO ShardingSphere-SQL - Actual SQL: ds ::: select count(*)
from care_task_12 t,care_task_item_12 i left join care_task_detail_67 d on d.task_item_id = i.id and d.tenant_id = ?
where i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1
and t.tenant_id = ?
and i.tenant_id = ?
and t.target_value=?
and t.target_type=?
and t.service_id=?
AND i.operate_time >= ?
and i.operate_time <= ? ::: [12, 12, 12, 959, 0, 1128, 2020-01-01 00:00:00, 2021-09-09 23:59:59]
2021-09-10 11:27:54.248 [http-nio-8099-exec-1] INFO ShardingSphere-SQL - Actual SQL: ds ::: select count(*)
from care_task_12 t,care_task_item_12 i left join care_task_detail_68 d on d.task_item_id = i.id and d.tenant_id = ?
where i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1
and t.tenant_id = ?
and i.tenant_id = ?
and t.target_value=?
and t.target_type=?
and t.service_id=?
AND i.operate_time >= ?
and i.operate_time <= ? ::: [12, 12, 12, 959, 0, 1128, 2020-01-01 00:00:00, 2021-09-09 23:59:59]
2021-09-10 11:27:54.248 [http-nio-8099-exec-1] INFO ShardingSphere-SQL - Actual SQL: ds ::: select count(*)
from care_task_12 t,care_task_item_12 i left join care_task_detail_69 d on d.task_item_id = i.id and d.tenant_id = ?
where i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1
and t.tenant_id = ?
and i.tenant_id = ?
...
--
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] 448700174 edited a comment on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 edited a comment on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-916000300
Can I use binding-tables to solve this problem?
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-915999693
It traversal all care_task_detail (from 1 to 99).
My config is:
care_task_detail:
actual-data-nodes: ds.care_task_detail_$->{1..99}
table-strategy:
standard:
--
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] RaigorJiang edited a comment on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
RaigorJiang edited a comment on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-917879808
Hello @448700174
I read your sharding algorithm `TenantIdShardingAlgorithm `, where:
`String actualTableName = logicTableName + "_" + tenantId;`
Does it mean that the suffix of the expected actual table name is consistent with tenant_id?
Then, for the last logic SQL, is the tenant_id entered is 12?
Should the expected routing result be only `FROM care_patrol_task_12 cpt,care_patrol_subtask_12 cps`?
> But another SQL can't work even I config as this.
SELECT
cps.id,
cpt.area_id,
cpt.area_name,
cps.start_time,
cps.end_time,
cps.schedule_count,
cps.done_count
FROM care_patrol_task cpt,care_patrol_subtask cps
WHERE cpt.tenant_id = #{tenantId}
and cpt.id = cps.task_id
AND cps.carer_id = #{carerId}
AND cps.status = #{status}
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-916602491
1.The version is shardingSphere-JDBC 5.0.0.beta
2.The config is:
shardingsphere:
props:
sql-show: true
rules:
sharding:
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
sharding-algorithms:
tenant-id-sharding:
type: CLASS_BASED
props:
strategy: STANDARD
algorithmClassName: com.youban.common.dao.TenantIdShardingAlgorithm
binding-tables:
- care_task,care_task_detail
tables:
care_task:
actual-data-nodes: ds.care_task_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_task_detail:
actual-data-nodes: ds.care_task_detail_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_task_item:
actual-data-nodes: ds.care_task_item_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_patrol_task:
actual-data-nodes: ds.care_patrol_task_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_patrol_item:
actual-data-nodes: ds.care_patrol_item_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_patrol_subtask:
actual-data-nodes: ds.care_patrol_subtask_$->{1..99}
table-strategy:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
care_order_detail:
actual-data-nodes: ds.care_order_detail_$->{1..99}
table-strategy:
standard:
sharding-column: tenant_id
sharding-algorithm-name: tenant-id-sharding
3.The sharding algorithm class is:
public class TenantIdShardingAlgorithm implements StandardShardingAlgorithm<Integer> {
private static final ConcurrentHashMap<String, List<String>> ACTUAL_TABLES = new ConcurrentHashMap<>();
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
String logicTableName = shardingValue.getLogicTableName();
String tenantId = shardingValue.getValue() + "";
String actualTableName = logicTableName + "_" + tenantId;
List<String> actualTalbes = ACTUAL_TABLES.computeIfAbsent(logicTableName, key -> new ArrayList<>());
// create table if not exists
if (!actualTalbes.contains(actualTableName)) {
String create = String.format("create table if not exists `%s` like `%s` ", actualTableName,
logicTableName);
Connection connection = null;
try {
DataSource dataSource = SpringContextUtils.getBean(DataSource.class);
connection = dataSource.getConnection();
Statement statement = connection.createStatement();
if (0 == statement.executeUpdate(create)) {
log.info("create table success: " + actualTableName);
actualTalbes.add(actualTableName);
} else {
log.error("create table fail: " + actualTableName);
}
statement.close();
connection.close();
} catch (Exception e) {
log.error("createActualTable", e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// nothing todo
}
}
}
}
return actualTableName;
}
@Override
public String getType() {
return null;
}
@Override
public void init() {
}
@Override
public Collection<String> doSharding(Collection<String> paramCollection,
RangeShardingValue<Integer> paramRangeShardingValue) {
return paramCollection;
}
}
4.The Logic SQL is:
select count(*)
from care_task t,care_task_item i left join care_task_detail d on d.task_item_id = i.id and d.tenant_id = #{tenantId}
where i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1
and t.tenant_id = #{tenantId}
and i.tenant_id = #{tenantId}
5.The Actual SQL is:
### SQL: select count(*) from care_task t,care_task_item i left join care_task_detail d on d.task_item_id = i.id and d.tenant_id = ? where i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1 and t.tenant_id = ? and i.tenant_id = ? and t.target_value=? and t.target_type=? and t.service_id=? AND i.operate_time >= ? and i.operate_time <= ?
### Cause: org.apache.shardingsphere.infra.exception.ShardingSphereException: Route table care_task_12 does not exist, available actual table: [care_task_detail_1, care_task_detail_2, care_task_detail_3, care_task_detail_4, care_task_detail_5, care_task_detail_6, care_task_detail_7, care_task_detail_8, care_task_detail_9, care_task_detail_10, care_task_detail_11, care_task_detail_12, care_task_detail_13, care_task_detail_14, ...]
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
... 81 common frames omitted
Caused by: org.apache.shardingsphere.infra.exception.ShardingSphereException: Route table care_task_12 does not exist, available actual table: [care_task_detail_1, care_task_detail_2, care_task_detail_3, care_task_detail_4, care_task_detail_5, care_task_detail_6, care_task_detail_7, care_task_detail_8, care_task_detail_9, care_task_detail_10, care_task_detail_11, care_task_detail_12, care_task_detail_13, ....]
at org.apache.shardingsphere.sharding.route.strategy.type.standard.StandardShardingStrategy.doSharding(StandardShardingStrategy.java:73)
at org.apache.shardingsphere.sharding.route.strategy.type.standard.StandardShardingStrategy.doSharding(StandardShardingStrategy.java:57)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeTables(ShardingStandardRoutingEngine.java:214)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route0(ShardingStandardRoutingEngine.java:194)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditionsWithCondition(ShardingStandardRoutingEngine.java:114)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditions(ShardingStandardRoutingEngine.java:107)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.getDataNodes(ShardingStandardRoutingEngine.java:84)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:69)
at org.apache.shardingsphere.sharding.route.engine.type.complex.ShardingComplexRoutingEngine.route(ShardingComplexRoutingEngine.java:57)
--
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 #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-922174506
> Yes, the master code fix this problem.
Because master code has already fix this problem, I will close this issue.
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-918069080
Yes, the tenantId is 12, I expected routing result is care_patrol_task_12 cpt,care_patrol_subtask_12 cps.
--
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] tuichenchuxin commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-921517511
@448700174
Maybe you can describe the problem more clearly or provide a demo? Maybe raise a new issue to describe?
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-919966901
Which jar I need update?
--
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] tuichenchuxin commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-919868109
@448700174
I saw the actual sql is like
``` FROM care_patrol_task_76 cpt,care_patrol_subtask_76 cps```
```FROM care_patrol_task_77 cpt,care_patrol_subtask_77 cps```
these tables have binding relationship. If they are not binding each other, the sql will be like
```FROM care_patrol_task_1 cpt,care_patrol_subtask_2 cps```
So, may be the sharding column or strategy ?
And Maybe can you try to use the latest code to see if you have the same problem?
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-917846181
Any suggestions?
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-921499248
> > Which jar I need update?
>
> The stable version will be released soon, you can try to get master branch to test now.
Yes, the master code fix this problem.
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-919043603
Do you have any suggestion?@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] 448700174 edited a comment on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 edited a comment on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-916119228
After I add this configuration:
binding-tables:
- care_task,care_task_item,care_task_detail
then got the following errors:
Route table care_task_12 does not exist, available actual table: [care_task_detail_1, care_task_detail_2, care_task_detail_3, care_task_detail_4, care_task_detail_5, care_task_detail_6, care_task_detail_7, care_task_detail_8, ...
What's wrong with my configuration?
--
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 closed issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315
--
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] 448700174 commented on issue #12315: left join result in cross table merge
Posted by GitBox <gi...@apache.org>.
448700174 commented on issue #12315:
URL: https://github.com/apache/shardingsphere/issues/12315#issuecomment-916835191
select count(*)
from care_task t,care_task_item i left join (select * from care_task_detail d where d.tenant_id = #{tenantId}) d on d.task_item_id = i.id
where i.task_id = t.id and t.enabled = 1 and t.done_count > 0 and i.enabled = 1
binding-tables:
- care_task,care_task_item,care_task_detail
This can work.
--
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