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:37:59 UTC
[GitHub] [shardingsphere] luoyexiaoran opened a new issue, #18949: count with subquery is error
luoyexiaoran opened a new issue, #18949:
URL: https://github.com/apache/shardingsphere/issues/18949
error sql:
select count(1) from ( select r.name, r.rubbishType,left(cd.collectDate,4) as collectDate,sum(cd.collectVolumeOfToday) as dailyOutput, sum(cd.collectTrashSumNum) as collectTrashSumNum,sum(cd.collectTimes) as collectTimes,cd.districtName,sum(cd.alarmNums) as alarmNum,r.classesName from cc_restaurant_collect_data cd LEFT JOIN cc_restaurant r on cd.restaurantId = r.id where cd.beenDeleted = 0 and r.beenDeleted = 0 and left(cd.collectDate,4)>=? and left(cd.collectDate,4)<=? group by cd.districtId,cd.restaurantId,left(cd.collectDate,4) ) a
every actual sql result is correct! but the aggregate result simply adds up all the results!
--
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] terrymanu commented on issue #18949: count with subquery is error
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #18949:
URL: https://github.com/apache/shardingsphere/issues/18949#issuecomment-1179496329
What is your version?
--
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 #18949: count with subquery is error
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #18949:
URL: https://github.com/apache/shardingsphere/issues/18949#issuecomment-1272349445
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
[GitHub] [shardingsphere] RaigorJiang commented on issue #18949: count with subquery is error
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #18949:
URL: https://github.com/apache/shardingsphere/issues/18949#issuecomment-1407563437
This issue has been inactive for a long time, and ShardingSphere has released several new versions, so I will close it.
Please try the latest version, and if the problem reproduces, please reopen it or submit a new one.
--
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] luoyexiaoran commented on issue #18949: count with subquery is error
Posted by GitBox <gi...@apache.org>.
luoyexiaoran commented on issue #18949:
URL: https://github.com/apache/shardingsphere/issues/18949#issuecomment-1179993657
> What is your version?
5.0.0-RC1-SNAPSHOT,,I'm trying to switch to 5.1.3,,But it is still wrong
--
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] terrymanu commented on issue #18949: count with subquery is error
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #18949:
URL: https://github.com/apache/shardingsphere/issues/18949#issuecomment-1180638972
What is your 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] luoyexiaoran commented on issue #18949: count with subquery is error
Posted by GitBox <gi...@apache.org>.
luoyexiaoran commented on issue #18949:
URL: https://github.com/apache/shardingsphere/issues/18949#issuecomment-1181204170
> What is your configuration?
I sharding it by the month,this sql select group one year data
--
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] luoyexiaoran commented on issue #18949: count with subquery is error
Posted by GitBox <gi...@apache.org>.
luoyexiaoran commented on issue #18949:
URL: https://github.com/apache/shardingsphere/issues/18949#issuecomment-1198843475
`rules:
- !SHARDING
# 配置分片规则
tables:
# 配置商户汇总表cc_restaurant_collect_data规则
cc_restaurant_collect_data:
actualDataNodes: ds0.cc_restaurant_collect_data$->{2020..2022}0$->{1..9},ds0.cc_restaurant_collect_data$->{2020..2022}1$->{0..2}
# 配置分表策略
tableStrategy:
standard:
shardingColumn: collectDate
shardingAlgorithmName: cc_restaurant_collect_data
# 商户汇总表（修正收运量）
cc_restaurant_collect_data_correct_weight:
actualDataNodes: ds0.cc_restaurant_collect_data_correct_weight$->{2020..2022}0$->{1..9},ds0.cc_restaurant_collect_data_correct_weight$->{2020..2022}1$->{0..2}
# 配置分表策略
tableStrategy:
standard:
shardingColumn: collectDaterestaurantCollectDataCorrectWeightController
shardingAlgorithmName: cc_restaurant_collect_data_correct_weight
# 配置商户汇总表cc_sypoint_collect_data规则
cc_sypoint_collect_data:
actualDataNodes: ds0.cc_sypoint_collect_data$->{2020..2022}0$->{1..9},ds0.cc_sypoint_collect_data$->{2020..2022}1$->{0..2}
# 配置分表策略
tableStrategy:
standard:
shardingColumn: collectDate
shardingAlgorithmName: cc_sypoint_collect_data
# 配置商户汇总表cc_rfid规则
cc_rfid:
actualDataNodes: ds0.cc_rfid$->{2020..2022}0$->{1..9},ds0.cc_rfid$->{2020..2022}1$->{0..2}
# 配置分表策略
tableStrategy:
standard:
shardingColumn: time
shardingAlgorithmName: cc_rfid
# 配置商户汇总表cc_weighbridge规则
cc_weighbridge:
actualDataNodes: ds0.cc_weighbridge$->{2020..2022}0$->{1..9},ds0.cc_weighbridge$->{2020..2022}1$->{0..2}
# 配置分表策略
tableStrategy:
standard:
shardingColumn: grossDateTime
shardingAlgorithmName: cc_weighbridge
# 配置商户汇总表cc_sypoint_collect_data规则
cc_car_alarm_event:
actualDataNodes: ds0.cc_car_alarm_event$->{2022}0$->{1..9},ds0.cc_car_alarm_event$->{2022}1$->{0..2}
# 配置分表策略
tableStrategy:
standard:
shardingColumn: alarmTime
shardingAlgorithmName: cc_car_alarm_event
shardingAlgorithms:
cc_restaurant_collect_data:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: com.vortex.cloud.envcloud.cc.config.sharding.RestaurantCollectDataShardingAlgorithm
cc_restaurant_collect_data_correct_weight:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: com.vortex.cloud.envcloud.cc.config.sharding.RestaurantCollectDataCorrectWeightShardingAlgorithm
cc_sypoint_collect_data:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: com.vortex.cloud.envcloud.cc.config.sharding.SypointCollectDataShardingAlgorithm
cc_rfid:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: com.vortex.cloud.envcloud.cc.config.sharding.RfidDataShardingAlgorithm
cc_weighbridge:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: com.vortex.cloud.envcloud.cc.config.sharding.WeighbridgeShardingAlgorithm
cc_car_alarm_event:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: com.vortex.cloud.envcloud.cc.config.sharding.CarAlarmEventShardingAlgorithm
#属性配置
props:
sql-show: true`
` @Override
public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
String collectDate = (String) shardingValue.getValue();
LinkedList<String> tableNames = CollectionUtil.newLinkedList();
tableNames.addAll(availableTargetNames);
//获取所有表名，确定范围
for (String tableName : tableNames) {
String month = tableName.substring(tableName.length() - 6);
if (month.equals(collectDate.substring(0, 7).replaceAll("-", ""))) {
return tableName;
}
}
throw new VortexException("未找到分表路由");
}
@Override
public Collection<String> doSharding(Collection availableTargetNames, RangeShardingValue shardingValue) {
List<String> result = CollectionUtil.newLinkedList();
Range<String> ranges = shardingValue.getValueRange();
result.addAll(availableTargetNames);
if (ranges.hasLowerBound()) {
String startDate = ranges.lowerEndpoint();
DateTime beginOfMonth = DateUtil.beginOfMonth(DateUtil.parse(startDate.substring(0, 7), "yyyy-MM"));
result = result.stream().filter(tableName -> {
String month = tableName.substring(tableName.length() - 6);
DateTime endOfMonth = DateUtil.endOfMonth(DateUtil.parse(month, "yyyyMM"));
return beginOfMonth.getTime() <= endOfMonth.getTime();
}).collect(Collectors.toList());
}
if (ranges.hasUpperBound()) {
String endDate = ranges.upperEndpoint();
DateTime endOfMonth = DateUtil.endOfMonth(DateUtil.parse(endDate.substring(0, 7), "yyyy-MM"));
result = result.stream().filter(tableName -> {
String month = tableName.substring(tableName.length() - 6);
DateTime beginOfMonth = DateUtil.beginOfMonth(DateUtil.parse(month, "yyyyMM"));
return beginOfMonth.getTime() <= endOfMonth.getTime();
}).collect(Collectors.toList());
}
return 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] RaigorJiang closed issue #18949: count with subquery is error
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang closed issue #18949: count with subquery is error
URL: https://github.com/apache/shardingsphere/issues/18949
--
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] terrymanu commented on issue #18949: count with subquery is error
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #18949:
URL: https://github.com/apache/shardingsphere/issues/18949#issuecomment-1193641550
Please use text with markdown syntax to provide your configuration, snapshot is difficult to search and copy
--
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] closed issue #18949: count with subquery is error
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #18949: count with subquery is error
URL: https://github.com/apache/shardingsphere/issues/18949
--
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