search

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.87k stars 6.73k forks source link

version:4.0.0-RC1 group by bug #3869

Closed lssprogress closed 4 years ago

lssprogress commented 4 years ago

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

terrymanu commented 4 years ago

Please try 4.0.0 rc3

lssprogress commented 4 years ago

Please try 4.0.0 rc3

I have tried rc3,but the result is the same

terrymanu commented 4 years ago

Can you provide the log of sql.show and your sharding rule configuration?

lssprogress commented 4 years ago

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}]
KomachiSion commented 4 years ago

@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.
lssprogress commented 4 years ago
  1. table structure and init datas in tunnel_order20194 and tunnel_order20201, you can avoid sensitive information.

How can I provide it to you？

KomachiSion commented 4 years ago

Put them to comment. If the content is too much, you can upload to github repo, and provide repo address in the comments.

lssprogress commented 4 years ago

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);
KomachiSion commented 4 years ago

Thanks, I will try to reproduce problem.

taojintianxia commented 4 years ago

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

image

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
KomachiSion commented 4 years ago

@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

lssprogress commented 4 years ago

@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