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 2020/01/04 16:23:04 UTC
[GitHub] [incubator-shardingsphere] lssprogress opened a new issue #3869:
version:4.0.0-RC1 group by bug
lssprogress opened a new issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869
sharding version: 4.0.0-RC1
mybatis-plus version: 3.2.0
logic table name:C
related physical tables as follows:
table A:
act_amount order_type
1000 0
1000 1
table B:
act_amount order_type
300 0
500 1
sql desc: select sum(act_amount),order_type from C group by order_type
result expected:
0 1300
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571870074
> Put them to comment.
> If the content is too much, you can upload to github repo, and provide repo address in the comments.
@Slf4j
public class OrderTableShardingAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
String tableNode=null;
Date createDate = DateUtil.parseDateFromStrYyyyMMdd2(preciseShardingValue.getValue());
Calendar c = Calendar.getInstance();
c.setTime(createDate);
int year = c.get(Calendar.YEAR);
int season = DateUtil.getSeason(createDate);
log.debug("按照季度进行数据分片，精准查询，当前为第{}年，第{}季度：",year,season);
for(Object obj:collection){
String oneNode=obj+"";
if(oneNode.endsWith(year+""+season)){
tableNode = oneNode;
break;
}
}
return tableNode;
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
Collection<String> collect = new ArrayList<>();
Calendar calendar=Calendar.getInstance();
String dateUpperStr=rangeShardingValue.getValueRange().upperEndpoint();
String dateLowerStr=rangeShardingValue.getValueRange().lowerEndpoint();
Date dateUpper=DateUtil.parseDateFromStrYyyyMMdd2(dateUpperStr);
Date dateLower=DateUtil.parseDateFromStrYyyyMMdd2(dateLowerStr);
calendar.setTime(dateUpper);
int yearUpper=calendar.get(Calendar.YEAR);
int seasonUpper = DateUtil.getSeason(dateUpper);
calendar.setTime(dateLower);
int yearLower=calendar.get(Calendar.YEAR);
int seasonLower = DateUtil.getSeason(dateLower);
boolean add = false;
for(String obj:collection){
String tableNoe=obj+"";
if(!add){
if(tableNoe.endsWith(yearLower+""+seasonLower)){
add = true;
}
}
if(tableNoe.endsWith(yearUpper+""+seasonUpper)){
collect.add(tableNoe);
break;
}
if(add){
collect.add(tableNoe);
}
}
log.debug("按照季度进行数据分片，范围查询，当前范围：{}", JSONUtil.parseArray(collect));
return collect;
}
}
CREATE TABLE tunnel_order20194 (
id bigint(20) NOT NULL AUTO_INCREMENT,
line_order_id varchar(50) DEFAULT NULL,
line_no int(11) DEFAULT NULL COMMENT,
plate_no varchar(10) DEFAULT NULL,
plate_color varchar(2) DEFAULT NULL,
pay_type char(1) DEFAULT NULL,
carry_cnt int(11) DEFAULT NULL,
vehicle_type char(1) DEFAULT NULL,
pay_amount int(11) DEFAULT NULL,
act_amount int(11) DEFAULT NULL,
coupon_amount int(11) DEFAULT NULL,
order_status char(1) DEFAULT NULL,
pay_time datetime DEFAULT NULL,
pay_success_time datetime DEFAULT NULL,
pass_time datetime DEFAULT NULL,
shift char(1) DEFAULT NULL,
shift_date varchar(10) DEFAULT NULL,
order_type char(1) DEFAULT NULL,
charger_id varchar(32) DEFAULT NULL,
charger_name varchar(20) DEFAULT NULL,
vehicle_id varchar(32) DEFAULT NULL,
user_id varchar(32) DEFAULT NULL,
user_name varchar(50) DEFAULT NULL,
pay_order_no varchar(32) DEFAULT NULL,
offline_record_id varchar(50) DEFAULT NULL,
is_lease char(1) DEFAULT 'N',
img_url varchar(50) DEFAULT NULL',
create_by varchar(30) DEFAULT NULL,
create_time datetime DEFAULT NULL',
update_by varchar(30) DEFAULT NULL,
update_time datetime DEFAULT NULL,
logic_delete char(1) DEFAULT '0',
create_date varchar(10) DEFAULT NULL,
obu_id varchar(50) DEFAULT NULL,
fee_seri_no varchar(32) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;
CREATE TABLE tunnel_order20201 (
id bigint(20) NOT NULL AUTO_INCREMENT,
line_order_id varchar(50) DEFAULT NULL,
line_no int(11) DEFAULT NULL COMMENT,
plate_no varchar(10) DEFAULT NULL,
plate_color varchar(2) DEFAULT NULL,
pay_type char(1) DEFAULT NULL,
carry_cnt int(11) DEFAULT NULL,
vehicle_type char(1) DEFAULT NULL,
pay_amount int(11) DEFAULT NULL,
act_amount int(11) DEFAULT NULL,
coupon_amount int(11) DEFAULT NULL,
order_status char(1) DEFAULT NULL,
pay_time datetime DEFAULT NULL,
pay_success_time datetime DEFAULT NULL,
pass_time datetime DEFAULT NULL,
shift char(1) DEFAULT NULL,
shift_date varchar(10) DEFAULT NULL,
order_type char(1) DEFAULT NULL,
charger_id varchar(32) DEFAULT NULL,
charger_name varchar(20) DEFAULT NULL,
vehicle_id varchar(32) DEFAULT NULL,
user_id varchar(32) DEFAULT NULL,
user_name varchar(50) DEFAULT NULL,
pay_order_no varchar(32) DEFAULT NULL,
offline_record_id varchar(50) DEFAULT NULL,
is_lease char(1) DEFAULT 'N',
img_url varchar(50) DEFAULT NULL',
create_by varchar(30) DEFAULT NULL,
create_time datetime DEFAULT NULL',
update_by varchar(30) DEFAULT NULL,
update_time datetime DEFAULT NULL,
logic_delete char(1) DEFAULT '0',
create_date varchar(10) DEFAULT NULL,
obu_id varchar(50) DEFAULT NULL,
fee_seri_no varchar(32) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;
INSERT INTO tunneldb.tunnel_order20194(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040165892034562, '116201912312357519120', 16, '鲁B11111', '02', '1', 5, '1', 1000, 1000, NULL, '1', '2019-12-31 23:57:51', '2019-12-31 23:57:49', '2019-12-31 23:57:51', '1', '2020-01-01', '1', 'b0109', 'xxxx', NULL, 'MTC', 'MTC', '8aaa84ba6e224f71016f5cad73a57e28', NULL, 'N', '116201912312357510', 'b0109', '2019-12-31 23:57:49', NULL, '2019-12-31 23:57:50', '0', '2019-12-31', NULL, NULL);
INSERT INTO tunneldb.tunnel_order20194(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040302508904449, '119201912312358244810', 19, '鲁B22222', '02', '0', 5, '1', 1000, 1000, NULL, '1', '2019-12-31 23:58:24', '2019-12-31 23:58:22', '2019-12-31 23:58:24', '1', '2020-01-01', '1', 'b0283', 'xxxx', NULL, 'MTC', 'MTC', '8aaa85d56e224f71016f5cadf2e17527', NULL, 'N', '119201912312358240', 'b0283', '2019-12-31 23:58:21', NULL, '2019-12-31 23:58:23', '0', '2019-12-31', NULL, NULL);
INSERT INTO tunneldb.tunnel_order20201(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040708349702145, '116202001010000012300', 16, '京F33333', '02', '0', 5, '1', 1000, 300, NULL, '1', '2020-01-01 00:00:01', '2019-12-31 23:59:59', '2020-01-01 00:00:01', '1', '2020-01-01', '1', 'b0109', 'xxxx', NULL, 'MTC', 'MTC', '8aaa84ba6e224f71016f5caf6cd97e2c', NULL, 'N', '116202001010000010', 'b0109', '2019-12-31 23:59:58', NULL, '2020-01-01 00:00:00', '0', '2020-01-01', NULL, NULL);
INSERT INTO tunneldb.tunnel_order20201(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040709268254722, '121202001010000014510', 21, '鲁44444', '02', '1', 5, '1', 1000, 500, NULL, '1', '2020-01-01 00:00:01', '2019-12-31 23:59:59', '2020-01-01 00:00:01', '1', '2020-01-01', '1', 'b0242', 'xxxx', NULL, 'MTC', 'MTC', '8aaa84ba6e224f71016f5caf6db57e2d', NULL, 'N', '121202001010000010', 'b0242', '2019-12-31 23:59:58', NULL, '2019-12-31 23:59:59', '0', '2020-01-01', NULL, NULL);
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-570893577
Please try 4.0.0 rc3
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] KomachiSion edited a comment on issue
#3869: version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
KomachiSion edited a comment on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571853200
@lssprogress Would you mind provide these two information to help us reproduce problem?
1. table structure and init datas in `tunnel_order20194` and `tunnel_order20201`, you can avoid sensitive information.
2. `config.OrderTableShardingAlgorithm` algorithm code.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] KomachiSion commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
KomachiSion commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571875557
Thanks, I will try to reproduce 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress removed a comment on issue
#3869: version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress removed a comment on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571868530
@Slf4j
public class OrderTableShardingAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
String tableNode=null;
Date createDate = DateUtil.parseDateFromStrYyyyMMdd2(preciseShardingValue.getValue());
Calendar c = Calendar.getInstance();
c.setTime(createDate);
int year = c.get(Calendar.YEAR);
int season = DateUtil.getSeason(createDate);
log.debug("按照季度进行数据分片，精准查询，当前为第{}年，第{}季度：",year,season);
for(Object obj:collection){
String oneNode=obj+"";
if(oneNode.endsWith(year+""+season)){
tableNode = oneNode;
break;
}
}
return tableNode;
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
Collection<String> collect = new ArrayList<>();
Calendar calendar=Calendar.getInstance();
String dateUpperStr=rangeShardingValue.getValueRange().upperEndpoint();
String dateLowerStr=rangeShardingValue.getValueRange().lowerEndpoint();
Date dateUpper=DateUtil.parseDateFromStrYyyyMMdd2(dateUpperStr);
Date dateLower=DateUtil.parseDateFromStrYyyyMMdd2(dateLowerStr);
calendar.setTime(dateUpper);
int yearUpper=calendar.get(Calendar.YEAR);
int seasonUpper = DateUtil.getSeason(dateUpper);
calendar.setTime(dateLower);
int yearLower=calendar.get(Calendar.YEAR);
int seasonLower = DateUtil.getSeason(dateLower);
boolean add = false;
for(String obj:collection){
String tableNoe=obj+"";
if(!add){
if(tableNoe.endsWith(yearLower+""+seasonLower)){
add = true;
}
}
if(tableNoe.endsWith(yearUpper+""+seasonUpper)){
collect.add(tableNoe);
break;
}
if(add){
collect.add(tableNoe);
}
}
log.debug("按照季度进行数据分片，范围查询，当前范围：{}", JSONUtil.parseArray(collect));
return collect;
}
}
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571400257
> Can you provide the log of `sql.show` and your sharding rule configuration?
**config:**
spring.shardingsphere.sharding.tables.tunnel_order.actual-data-nodes=ds0.tunnel_order$->{2016..2020}$->{1..4}
spring.shardingsphere.sharding.tables.tunnel_order.table-strategy.standard.sharding-column=create_date
spring.shardingsphere.sharding.tables.tunnel_order.table-strategy.standard.precise-algorithm-class-name=OrderTableShardingAlgorithm
spring.shardingsphere.sharding.tables.tunnel_order.table-strategy.standard.range-algorithm-class-name=config.OrderTableShardingAlgorithm
**code:**
tunnelOrderQueryWrapper.select("ifnull(sum(act_amount),0) as amount","pay_type")
.eq("order_status", SysParamConstaint.OrderStatus.ORDER_STATUS_PAY_SUCCESS.getValue())
.eq("date_format(pay_success_time,'%Y-%m-%d')", GlobalUtil.formatDateStr(date, "yyyy-MM-dd"))
.eq("logic_delete", SysParamConstaint.DeletedStatus.NOT_DELETED.getValue())
.eq("order_type", SysParamConstaint.TunnelOrderType.ORDER_TYPE_SUIETONG_QRCODE.getValue())
.between("create_date", GlobalUtil.formatDateStr(GlobalUtil.dealDateByNum(date,-1),"yyyy-MM-dd")
,GlobalUtil.formatDateStr(GlobalUtil.dealDateByNum(date,1),"yyyy-MM-dd"))
.groupBy("pay_type");
List<Map<String, Object>> result = tunnelOrderMapper.selectMaps(tunnelOrderQueryWrapper);
**log:**
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : Logic SQL: SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=tunnel_order, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=create_date, tableName=tunnel_order), operator=BETWEEN, compareOperator=null, positionValueMap={}, positionIndexMap={0=4, 1=5})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=tunnel_order, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=241)], parametersIndex=6, logicSQL=SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type)), containStar=false, firstSelectItemStartIndex=9, selectListStopIndex=52, groupByLastIndex=240, items=[CommonSelectItem(expression=ifnull(sum(act_amount),0), alias=Optional.of(amount)), CommonSelectItem(expression=pay_type, alias=Optional.absent())], groupByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(pay_type), orderDirection=ASC, nullOrderDirection=ASC, index=2, expression=null, alias=Optional.absent())], orderByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(pay_type), orderDirection=ASC, nullOrderDirection=ASC, index=2, expression=null, alias=Optional.absent())], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order20194
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type ORDER BY pay_type ASC ::: [1, 2019-12-31, 0, 1, 2019-12-30, 2020-01-01]
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order20201
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type ORDER BY pay_type ASC ::: [1, 2019-12-31, 0, 1, 2019-12-30, 2020-01-01]
2020-01-07 09:53:22.378 INFO 13164 --- [nio-7081-exec-1] 结果：[{amount=1000, pay_type=0}, {amount=1000, pay_type=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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571148055
Can you provide the log of `sql.show` and your sharding rule 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] KomachiSion edited a comment on issue
#3869: version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
KomachiSion edited a comment on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-572352936
@lssprogress , I have reproduced the problem, the reason is the `ifnull()` method covered `sum()` method.
SS parsed SQL and found `amount` is the result of `ifnull()` which is not aggregation, so the merge engine use replace result instead of sum results.
The solution is change the SQL as `sum(ifnull(act_amount, 0))`, it will be ok.
Example code in:
https://github.com/KomachiSion/shardingsphere-issues/tree/master/issue3869
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] KomachiSion commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
KomachiSion commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571853200
@lssprogress Would you mind provide these two information to help us reproduce problem?
1. table structure and init datas in `tunnel_order20194` and `tunnel_order20201`
2. `config.OrderTableShardingAlgorithm` algorithm code.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571854937
> 1. table structure and init datas in `tunnel_order20194` and `tunnel_order20201`, you can avoid sensitive information.
How can I provide it to you？
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-570894876
> Please try 4.0.0 rc3
I have tried rc3 with nothing changed
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress edited a comment on issue
#3869: version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress edited a comment on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571400257
> Can you provide the log of `sql.show` and your sharding rule configuration?
**config:**
```
spring.shardingsphere.sharding.tables.tunnel_order.actual-data-nodes=ds0.tunnel_order$->{2016..2020}$->{1..4}
spring.shardingsphere.sharding.tables.tunnel_order.table-strategy.standard.sharding-column=create_date
spring.shardingsphere.sharding.tables.tunnel_order.table-strategy.standard.precise-algorithm-class-name=config.OrderTableShardingAlgorithm
spring.shardingsphere.sharding.tables.tunnel_order.table-strategy.standard.range-algorithm-class-name=config.OrderTableShardingAlgorithm
```
**code:**
```
tunnelOrderQueryWrapper.select("ifnull(sum(act_amount),0) as amount","pay_type")
.eq("order_status", SysParamConstaint.OrderStatus.ORDER_STATUS_PAY_SUCCESS.getValue())
.eq("date_format(pay_success_time,'%Y-%m-%d')", GlobalUtil.formatDateStr(date, "yyyy-MM-dd"))
.eq("logic_delete", SysParamConstaint.DeletedStatus.NOT_DELETED.getValue())
.eq("order_type", SysParamConstaint.TunnelOrderType.ORDER_TYPE_SUIETONG_QRCODE.getValue())
.between("create_date", GlobalUtil.formatDateStr(GlobalUtil.dealDateByNum(date,-1),"yyyy-MM-dd")
,GlobalUtil.formatDateStr(GlobalUtil.dealDateByNum(date,1),"yyyy-MM-dd"))
.groupBy("pay_type");
List<Map<String, Object>> result = tunnelOrderMapper.selectMaps(tunnelOrderQueryWrapper);
```
**log:**
```
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : Logic SQL: SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=tunnel_order, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=create_date, tableName=tunnel_order), operator=BETWEEN, compareOperator=null, positionValueMap={}, positionIndexMap={0=4, 1=5})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=tunnel_order, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=241)], parametersIndex=6, logicSQL=SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type)), containStar=false, firstSelectItemStartIndex=9, selectListStopIndex=52, groupByLastIndex=240, items=[CommonSelectItem(expression=ifnull(sum(act_amount),0), alias=Optional.of(amount)), CommonSelectItem(expression=pay_type, alias=Optional.absent())], groupByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(pay_type), orderDirection=ASC, nullOrderDirection=ASC, index=2, expression=null, alias=Optional.absent())], orderByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(pay_type), orderDirection=ASC, nullOrderDirection=ASC, index=2, expression=null, alias=Optional.absent())], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order20194
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type ORDER BY pay_type ASC ::: [1, 2019-12-31, 0, 1, 2019-12-30, 2020-01-01]
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order20201
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type ORDER BY pay_type ASC ::: [1, 2019-12-31, 0, 1, 2019-12-30, 2020-01-01]
2020-01-07 09:53:22.378 INFO 13164 --- [nio-7081-exec-1] 结果：[{amount=1000, pay_type=0}, {amount=1000, pay_type=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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress edited a comment on issue
#3869: version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress edited a comment on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571870074
> Put them to comment.
> If the content is too much, you can upload to github repo, and provide repo address in the comments.
```java
@Slf4j
public class OrderTableShardingAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
String tableNode=null;
Date createDate = DateUtil.parseDateFromStrYyyyMMdd2(preciseShardingValue.getValue());
Calendar c = Calendar.getInstance();
c.setTime(createDate);
int year = c.get(Calendar.YEAR);
int season = DateUtil.getSeason(createDate);
log.debug("按照季度进行数据分片，精准查询，当前为第{}年，第{}季度：",year,season);
for(Object obj:collection){
String oneNode=obj+"";
if(oneNode.endsWith(year+""+season)){
tableNode = oneNode;
break;
}
}
return tableNode;
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
Collection<String> collect = new ArrayList<>();
Calendar calendar=Calendar.getInstance();
String dateUpperStr=rangeShardingValue.getValueRange().upperEndpoint();
String dateLowerStr=rangeShardingValue.getValueRange().lowerEndpoint();
Date dateUpper=DateUtil.parseDateFromStrYyyyMMdd2(dateUpperStr);
Date dateLower=DateUtil.parseDateFromStrYyyyMMdd2(dateLowerStr);
calendar.setTime(dateUpper);
int yearUpper=calendar.get(Calendar.YEAR);
int seasonUpper = DateUtil.getSeason(dateUpper);
calendar.setTime(dateLower);
int yearLower=calendar.get(Calendar.YEAR);
int seasonLower = DateUtil.getSeason(dateLower);
boolean add = false;
for(String obj:collection){
String tableNoe=obj+"";
if(!add){
if(tableNoe.endsWith(yearLower+""+seasonLower)){
add = true;
}
}
if(tableNoe.endsWith(yearUpper+""+seasonUpper)){
collect.add(tableNoe);
break;
}
if(add){
collect.add(tableNoe);
}
}
log.debug("按照季度进行数据分片，范围查询，当前范围：{}", JSONUtil.parseArray(collect));
return collect;
}
}
```
```sql
CREATE TABLE tunnel_order20194 (
id bigint(20) NOT NULL AUTO_INCREMENT,
line_order_id varchar(50) DEFAULT NULL,
line_no int(11) DEFAULT NULL COMMENT,
plate_no varchar(10) DEFAULT NULL,
plate_color varchar(2) DEFAULT NULL,
pay_type char(1) DEFAULT NULL,
carry_cnt int(11) DEFAULT NULL,
vehicle_type char(1) DEFAULT NULL,
pay_amount int(11) DEFAULT NULL,
act_amount int(11) DEFAULT NULL,
coupon_amount int(11) DEFAULT NULL,
order_status char(1) DEFAULT NULL,
pay_time datetime DEFAULT NULL,
pay_success_time datetime DEFAULT NULL,
pass_time datetime DEFAULT NULL,
shift char(1) DEFAULT NULL,
shift_date varchar(10) DEFAULT NULL,
order_type char(1) DEFAULT NULL,
charger_id varchar(32) DEFAULT NULL,
charger_name varchar(20) DEFAULT NULL,
vehicle_id varchar(32) DEFAULT NULL,
user_id varchar(32) DEFAULT NULL,
user_name varchar(50) DEFAULT NULL,
pay_order_no varchar(32) DEFAULT NULL,
offline_record_id varchar(50) DEFAULT NULL,
is_lease char(1) DEFAULT 'N',
img_url varchar(50) DEFAULT NULL',
create_by varchar(30) DEFAULT NULL,
create_time datetime DEFAULT NULL',
update_by varchar(30) DEFAULT NULL,
update_time datetime DEFAULT NULL,
logic_delete char(1) DEFAULT '0',
create_date varchar(10) DEFAULT NULL,
obu_id varchar(50) DEFAULT NULL,
fee_seri_no varchar(32) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;
CREATE TABLE tunnel_order20201 (
id bigint(20) NOT NULL AUTO_INCREMENT,
line_order_id varchar(50) DEFAULT NULL,
line_no int(11) DEFAULT NULL COMMENT,
plate_no varchar(10) DEFAULT NULL,
plate_color varchar(2) DEFAULT NULL,
pay_type char(1) DEFAULT NULL,
carry_cnt int(11) DEFAULT NULL,
vehicle_type char(1) DEFAULT NULL,
pay_amount int(11) DEFAULT NULL,
act_amount int(11) DEFAULT NULL,
coupon_amount int(11) DEFAULT NULL,
order_status char(1) DEFAULT NULL,
pay_time datetime DEFAULT NULL,
pay_success_time datetime DEFAULT NULL,
pass_time datetime DEFAULT NULL,
shift char(1) DEFAULT NULL,
shift_date varchar(10) DEFAULT NULL,
order_type char(1) DEFAULT NULL,
charger_id varchar(32) DEFAULT NULL,
charger_name varchar(20) DEFAULT NULL,
vehicle_id varchar(32) DEFAULT NULL,
user_id varchar(32) DEFAULT NULL,
user_name varchar(50) DEFAULT NULL,
pay_order_no varchar(32) DEFAULT NULL,
offline_record_id varchar(50) DEFAULT NULL,
is_lease char(1) DEFAULT 'N',
img_url varchar(50) DEFAULT NULL',
create_by varchar(30) DEFAULT NULL,
create_time datetime DEFAULT NULL',
update_by varchar(30) DEFAULT NULL,
update_time datetime DEFAULT NULL,
logic_delete char(1) DEFAULT '0',
create_date varchar(10) DEFAULT NULL,
obu_id varchar(50) DEFAULT NULL,
fee_seri_no varchar(32) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;
INSERT INTO tunneldb.tunnel_order20194(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040165892034562, '116201912312357519120', 16, '鲁B11111', '02', '1', 5, '1', 1000, 1000, NULL, '1', '2019-12-31 23:57:51', '2019-12-31 23:57:49', '2019-12-31 23:57:51', '1', '2020-01-01', '1', 'b0109', 'xxxx', NULL, 'MTC', 'MTC', '8aaa84ba6e224f71016f5cad73a57e28', NULL, 'N', '116201912312357510', 'b0109', '2019-12-31 23:57:49', NULL, '2019-12-31 23:57:50', '0', '2019-12-31', NULL, NULL);
INSERT INTO tunneldb.tunnel_order20194(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040302508904449, '119201912312358244810', 19, '鲁B22222', '02', '0', 5, '1', 1000, 1000, NULL, '1', '2019-12-31 23:58:24', '2019-12-31 23:58:22', '2019-12-31 23:58:24', '1', '2020-01-01', '1', 'b0283', 'xxxx', NULL, 'MTC', 'MTC', '8aaa85d56e224f71016f5cadf2e17527', NULL, 'N', '119201912312358240', 'b0283', '2019-12-31 23:58:21', NULL, '2019-12-31 23:58:23', '0', '2019-12-31', NULL, NULL);
INSERT INTO tunneldb.tunnel_order20201(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040708349702145, '116202001010000012300', 16, '京F33333', '02', '0', 5, '1', 1000, 300, NULL, '1', '2020-01-01 00:00:01', '2019-12-31 23:59:59', '2020-01-01 00:00:01', '1', '2020-01-01', '1', 'b0109', 'xxxx', NULL, 'MTC', 'MTC', '8aaa84ba6e224f71016f5caf6cd97e2c', NULL, 'N', '116202001010000010', 'b0109', '2019-12-31 23:59:58', NULL, '2020-01-01 00:00:00', '0', '2020-01-01', NULL, NULL);
INSERT INTO tunneldb.tunnel_order20201(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040709268254722, '121202001010000014510', 21, '鲁44444', '02', '1', 5, '1', 1000, 500, NULL, '1', '2020-01-01 00:00:01', '2019-12-31 23:59:59', '2020-01-01 00:00:01', '1', '2020-01-01', '1', 'b0242', 'xxxx', NULL, 'MTC', 'MTC', '8aaa84ba6e224f71016f5caf6db57e2d', NULL, 'N', '121202001010000010', 'b0242', '2019-12-31 23:59:58', NULL, '2019-12-31 23:59:59', '0', '2020-01-01', NULL, NULL);
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress edited a comment on issue
#3869: version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress edited a comment on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571870074
> Put them to comment.
> If the content is too much, you can upload to github repo, and provide repo address in the comments.
```
@Slf4j
public class OrderTableShardingAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
String tableNode=null;
Date createDate = DateUtil.parseDateFromStrYyyyMMdd2(preciseShardingValue.getValue());
Calendar c = Calendar.getInstance();
c.setTime(createDate);
int year = c.get(Calendar.YEAR);
int season = DateUtil.getSeason(createDate);
log.debug("按照季度进行数据分片，精准查询，当前为第{}年，第{}季度：",year,season);
for(Object obj:collection){
String oneNode=obj+"";
if(oneNode.endsWith(year+""+season)){
tableNode = oneNode;
break;
}
}
return tableNode;
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
Collection<String> collect = new ArrayList<>();
Calendar calendar=Calendar.getInstance();
String dateUpperStr=rangeShardingValue.getValueRange().upperEndpoint();
String dateLowerStr=rangeShardingValue.getValueRange().lowerEndpoint();
Date dateUpper=DateUtil.parseDateFromStrYyyyMMdd2(dateUpperStr);
Date dateLower=DateUtil.parseDateFromStrYyyyMMdd2(dateLowerStr);
calendar.setTime(dateUpper);
int yearUpper=calendar.get(Calendar.YEAR);
int seasonUpper = DateUtil.getSeason(dateUpper);
calendar.setTime(dateLower);
int yearLower=calendar.get(Calendar.YEAR);
int seasonLower = DateUtil.getSeason(dateLower);
boolean add = false;
for(String obj:collection){
String tableNoe=obj+"";
if(!add){
if(tableNoe.endsWith(yearLower+""+seasonLower)){
add = true;
}
}
if(tableNoe.endsWith(yearUpper+""+seasonUpper)){
collect.add(tableNoe);
break;
}
if(add){
collect.add(tableNoe);
}
}
log.debug("按照季度进行数据分片，范围查询，当前范围：{}", JSONUtil.parseArray(collect));
return collect;
}
}
```
```sql
CREATE TABLE tunnel_order20194 (
id bigint(20) NOT NULL AUTO_INCREMENT,
line_order_id varchar(50) DEFAULT NULL,
line_no int(11) DEFAULT NULL COMMENT,
plate_no varchar(10) DEFAULT NULL,
plate_color varchar(2) DEFAULT NULL,
pay_type char(1) DEFAULT NULL,
carry_cnt int(11) DEFAULT NULL,
vehicle_type char(1) DEFAULT NULL,
pay_amount int(11) DEFAULT NULL,
act_amount int(11) DEFAULT NULL,
coupon_amount int(11) DEFAULT NULL,
order_status char(1) DEFAULT NULL,
pay_time datetime DEFAULT NULL,
pay_success_time datetime DEFAULT NULL,
pass_time datetime DEFAULT NULL,
shift char(1) DEFAULT NULL,
shift_date varchar(10) DEFAULT NULL,
order_type char(1) DEFAULT NULL,
charger_id varchar(32) DEFAULT NULL,
charger_name varchar(20) DEFAULT NULL,
vehicle_id varchar(32) DEFAULT NULL,
user_id varchar(32) DEFAULT NULL,
user_name varchar(50) DEFAULT NULL,
pay_order_no varchar(32) DEFAULT NULL,
offline_record_id varchar(50) DEFAULT NULL,
is_lease char(1) DEFAULT 'N',
img_url varchar(50) DEFAULT NULL',
create_by varchar(30) DEFAULT NULL,
create_time datetime DEFAULT NULL',
update_by varchar(30) DEFAULT NULL,
update_time datetime DEFAULT NULL,
logic_delete char(1) DEFAULT '0',
create_date varchar(10) DEFAULT NULL,
obu_id varchar(50) DEFAULT NULL,
fee_seri_no varchar(32) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;
CREATE TABLE tunnel_order20201 (
id bigint(20) NOT NULL AUTO_INCREMENT,
line_order_id varchar(50) DEFAULT NULL,
line_no int(11) DEFAULT NULL COMMENT,
plate_no varchar(10) DEFAULT NULL,
plate_color varchar(2) DEFAULT NULL,
pay_type char(1) DEFAULT NULL,
carry_cnt int(11) DEFAULT NULL,
vehicle_type char(1) DEFAULT NULL,
pay_amount int(11) DEFAULT NULL,
act_amount int(11) DEFAULT NULL,
coupon_amount int(11) DEFAULT NULL,
order_status char(1) DEFAULT NULL,
pay_time datetime DEFAULT NULL,
pay_success_time datetime DEFAULT NULL,
pass_time datetime DEFAULT NULL,
shift char(1) DEFAULT NULL,
shift_date varchar(10) DEFAULT NULL,
order_type char(1) DEFAULT NULL,
charger_id varchar(32) DEFAULT NULL,
charger_name varchar(20) DEFAULT NULL,
vehicle_id varchar(32) DEFAULT NULL,
user_id varchar(32) DEFAULT NULL,
user_name varchar(50) DEFAULT NULL,
pay_order_no varchar(32) DEFAULT NULL,
offline_record_id varchar(50) DEFAULT NULL,
is_lease char(1) DEFAULT 'N',
img_url varchar(50) DEFAULT NULL',
create_by varchar(30) DEFAULT NULL,
create_time datetime DEFAULT NULL',
update_by varchar(30) DEFAULT NULL,
update_time datetime DEFAULT NULL,
logic_delete char(1) DEFAULT '0',
create_date varchar(10) DEFAULT NULL,
obu_id varchar(50) DEFAULT NULL,
fee_seri_no varchar(32) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;
INSERT INTO tunneldb.tunnel_order20194(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040165892034562, '116201912312357519120', 16, '鲁B11111', '02', '1', 5, '1', 1000, 1000, NULL, '1', '2019-12-31 23:57:51', '2019-12-31 23:57:49', '2019-12-31 23:57:51', '1', '2020-01-01', '1', 'b0109', 'xxxx', NULL, 'MTC', 'MTC', '8aaa84ba6e224f71016f5cad73a57e28', NULL, 'N', '116201912312357510', 'b0109', '2019-12-31 23:57:49', NULL, '2019-12-31 23:57:50', '0', '2019-12-31', NULL, NULL);
INSERT INTO tunneldb.tunnel_order20194(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040302508904449, '119201912312358244810', 19, '鲁B22222', '02', '0', 5, '1', 1000, 1000, NULL, '1', '2019-12-31 23:58:24', '2019-12-31 23:58:22', '2019-12-31 23:58:24', '1', '2020-01-01', '1', 'b0283', 'xxxx', NULL, 'MTC', 'MTC', '8aaa85d56e224f71016f5cadf2e17527', NULL, 'N', '119201912312358240', 'b0283', '2019-12-31 23:58:21', NULL, '2019-12-31 23:58:23', '0', '2019-12-31', NULL, NULL);
INSERT INTO tunneldb.tunnel_order20201(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040708349702145, '116202001010000012300', 16, '京F33333', '02', '0', 5, '1', 1000, 300, NULL, '1', '2020-01-01 00:00:01', '2019-12-31 23:59:59', '2020-01-01 00:00:01', '1', '2020-01-01', '1', 'b0109', 'xxxx', NULL, 'MTC', 'MTC', '8aaa84ba6e224f71016f5caf6cd97e2c', NULL, 'N', '116202001010000010', 'b0109', '2019-12-31 23:59:58', NULL, '2020-01-01 00:00:00', '0', '2020-01-01', NULL, NULL);
INSERT INTO tunneldb.tunnel_order20201(id, line_order_id, line_no, plate_no, plate_color, pay_type, carry_cnt, vehicle_type, pay_amount, act_amount, coupon_amount, order_status, pay_time, pay_success_time, pass_time, shift, shift_date, order_type, charger_id, charger_name, vehicle_id, user_id, user_name, pay_order_no, offline_record_id, is_lease, img_url, create_by, create_time, update_by, update_time, logic_delete, create_date, obu_id, fee_seri_no) VALUES
(1212040709268254722, '121202001010000014510', 21, '鲁44444', '02', '1', 5, '1', 1000, 500, NULL, '1', '2020-01-01 00:00:01', '2019-12-31 23:59:59', '2020-01-01 00:00:01', '1', '2020-01-01', '1', 'b0242', 'xxxx', NULL, 'MTC', 'MTC', '8aaa84ba6e224f71016f5caf6db57e2d', NULL, 'N', '121202001010000010', 'b0242', '2019-12-31 23:59:58', NULL, '2019-12-31 23:59:59', '0', '2020-01-01', NULL, NULL);
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] KomachiSion closed issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
KomachiSion closed issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress edited a comment on issue
#3869: version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress edited a comment on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571400257
> Can you provide the log of `sql.show` and your sharding rule configuration?
**config:**
spring.shardingsphere.sharding.tables.tunnel_order.actual-data-nodes=ds0.tunnel_order$->{2016..2020}$->{1..4}
spring.shardingsphere.sharding.tables.tunnel_order.table-strategy.standard.sharding-column=create_date
spring.shardingsphere.sharding.tables.tunnel_order.table-strategy.standard.precise-algorithm-class-name=config.OrderTableShardingAlgorithm
spring.shardingsphere.sharding.tables.tunnel_order.table-strategy.standard.range-algorithm-class-name=config.OrderTableShardingAlgorithm
**code:**
tunnelOrderQueryWrapper.select("ifnull(sum(act_amount),0) as amount","pay_type")
.eq("order_status", SysParamConstaint.OrderStatus.ORDER_STATUS_PAY_SUCCESS.getValue())
.eq("date_format(pay_success_time,'%Y-%m-%d')", GlobalUtil.formatDateStr(date, "yyyy-MM-dd"))
.eq("logic_delete", SysParamConstaint.DeletedStatus.NOT_DELETED.getValue())
.eq("order_type", SysParamConstaint.TunnelOrderType.ORDER_TYPE_SUIETONG_QRCODE.getValue())
.between("create_date", GlobalUtil.formatDateStr(GlobalUtil.dealDateByNum(date,-1),"yyyy-MM-dd")
,GlobalUtil.formatDateStr(GlobalUtil.dealDateByNum(date,1),"yyyy-MM-dd"))
.groupBy("pay_type");
List<Map<String, Object>> result = tunnelOrderMapper.selectMaps(tunnelOrderQueryWrapper);
**log:**
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : Logic SQL: SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=tunnel_order, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=create_date, tableName=tunnel_order), operator=BETWEEN, compareOperator=null, positionValueMap={}, positionIndexMap={0=4, 1=5})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=tunnel_order, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=241)], parametersIndex=6, logicSQL=SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type)), containStar=false, firstSelectItemStartIndex=9, selectListStopIndex=52, groupByLastIndex=240, items=[CommonSelectItem(expression=ifnull(sum(act_amount),0), alias=Optional.of(amount)), CommonSelectItem(expression=pay_type, alias=Optional.absent())], groupByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(pay_type), orderDirection=ASC, nullOrderDirection=ASC, index=2, expression=null, alias=Optional.absent())], orderByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(pay_type), orderDirection=ASC, nullOrderDirection=ASC, index=2, expression=null, alias=Optional.absent())], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order20194
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type ORDER BY pay_type ASC ::: [1, 2019-12-31, 0, 1, 2019-12-30, 2020-01-01]
2020-01-07 09:53:22.371 INFO 13164 --- [nio-7081-exec-1] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT
ifnull(sum(act_amount),0) as amount,pay_type
FROM tunnel_order20201
WHERE (order_status = ? AND date_format(pay_success_time,'%Y-%m-%d') = ? AND logic_delete = ? AND order_type = ? AND create_date BETWEEN ? AND ?) GROUP BY pay_type ORDER BY pay_type ASC ::: [1, 2019-12-31, 0, 1, 2019-12-30, 2020-01-01]
2020-01-07 09:53:22.378 INFO 13164 --- [nio-7081-exec-1] 结果：[{amount=1000, pay_type=0}, {amount=1000, pay_type=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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] KomachiSion commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
KomachiSion commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-572352936
@lssprogress , I have reproduced the problem, the reason is the `ifnull()` method covered `sum()` method.
SS parse SQL found `amount` is the result of `ifnull()` which is not aggregation, so the merge engine use replace result instead of sum results.
The solution is change the SQL as `sum(ifnull(act_amount, 0))`, it will be ok.
Example code in:
https://github.com/KomachiSion/shardingsphere-issues/tree/master/issue3869
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-572387437
> @lssprogress , I have reproduced the problem, the reason is the `ifnull()` method covered `sum()` method.
>
> SS parsed SQL and found `amount` is the result of `ifnull()` which is not aggregation, so the merge engine use replace result instead of sum results.
>
> The solution is change the SQL as `sum(ifnull(act_amount, 0))`, it will be ok.
>
> Example code in:
> https://github.com/KomachiSion/shardingsphere-issues/tree/master/issue3869
I see. Thank you
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] KomachiSion commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
KomachiSion commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571856043
Put them to comment.
If the content is too much, you can upload to github repo, and provide repo address in the comments.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress edited a comment on issue
#3869: version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress edited a comment on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-570894876
> Please try 4.0.0 rc3
I have tried rc3,but the result is the same
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] lssprogress commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
lssprogress commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-571868530
@Slf4j
public class OrderTableShardingAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
String tableNode=null;
Date createDate = DateUtil.parseDateFromStrYyyyMMdd2(preciseShardingValue.getValue());
Calendar c = Calendar.getInstance();
c.setTime(createDate);
int year = c.get(Calendar.YEAR);
int season = DateUtil.getSeason(createDate);
log.debug("按照季度进行数据分片，精准查询，当前为第{}年，第{}季度：",year,season);
for(Object obj:collection){
String oneNode=obj+"";
if(oneNode.endsWith(year+""+season)){
tableNode = oneNode;
break;
}
}
return tableNode;
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
Collection<String> collect = new ArrayList<>();
Calendar calendar=Calendar.getInstance();
String dateUpperStr=rangeShardingValue.getValueRange().upperEndpoint();
String dateLowerStr=rangeShardingValue.getValueRange().lowerEndpoint();
Date dateUpper=DateUtil.parseDateFromStrYyyyMMdd2(dateUpperStr);
Date dateLower=DateUtil.parseDateFromStrYyyyMMdd2(dateLowerStr);
calendar.setTime(dateUpper);
int yearUpper=calendar.get(Calendar.YEAR);
int seasonUpper = DateUtil.getSeason(dateUpper);
calendar.setTime(dateLower);
int yearLower=calendar.get(Calendar.YEAR);
int seasonLower = DateUtil.getSeason(dateLower);
boolean add = false;
for(String obj:collection){
String tableNoe=obj+"";
if(!add){
if(tableNoe.endsWith(yearLower+""+seasonLower)){
add = true;
}
}
if(tableNoe.endsWith(yearUpper+""+seasonUpper)){
collect.add(tableNoe);
break;
}
if(add){
collect.add(tableNoe);
}
}
log.debug("按照季度进行数据分片，范围查询，当前范围：{}", JSONUtil.parseArray(collect));
return collect;
}
}
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] taojintianxia commented on issue #3869:
version:4.0.0-RC1 group by bug
Posted by GitBox <gi...@apache.org>.
taojintianxia commented on issue #3869: version:4.0.0-RC1 group by bug
URL: https://github.com/apache/incubator-shardingsphere/issues/3869#issuecomment-572009947
> sharding version: 4.0.0-RC1
> mybatis-plus version: 3.2.0
> logic table name:C
> related physical tables and datas as follows:
> table A:
> act_amount order_type
> 1000 0
> 1000 1
>
> table B:
> act_amount order_type
> 300 0
> 500 1
>
> sql desc: select order_type,sum(act_amount) from C group by order_type
> result expected:
> order_type sum(act_amount)
> 0 1300
> 1 1500
> but the real result is :
> order_type sum(act_amount)
> 0 1000
> 1 1000
> thus it can be seen that the result data comes from table A only and the table B data is discarded
After I tried the test senario you described , I got the result you expected
The version of frameworks I used as followings :
|name|version|
|-|-|
|sharding-jdbc-spring-boot-starter | 4.0.0-RC3 |
|mybatis-plus-boot-starter | 3.3.0 |
|springboot | 1.5.22.RELEASE |
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services