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: us...@infra.apache.org With regards, Apache Git Services
