flyduckforever opened a new issue, #18783: URL: https://github.com/apache/shardingsphere/issues/18783
> For English only, other languages will not accept. Before report a > bug, make sure you have: Searched open and closed [GitHub > issues](https://github.com/apache/shardingsphere/issues). Read > documentation: [ShardingSphere > Doc](https://shardingsphere.apache.org/document/current/en/overview). > Please pay attention on issues you submitted, because we maybe need > more details. If no response anymore and we cannot reproduce it on > current information, we will close it. Please answer these questions > before submitting your issue. Thanks!* ## Which version of ShardingSphere did you use? 5.2.1 ## Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-JDBC ## Expected behavior I segment orders and order details according to the quarter Logic SQL: `SELECT count(0) FROM dp_trade_payment_order dp LEFT JOIN dp_trade_payment_record r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'` Actual SQL: ` SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2022_03 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'` ## Actual behavior Logic SQL: `SELECT count(0) FROM dp_trade_payment_order dp LEFT JOIN dp_trade_payment_record r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'` Actual SQL: ` SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2020_01 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'` `SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2020_02 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'` `SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2020_03 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'` `SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2020_04 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'` `SELECT count(0) FROM dp_trade_payment_order_2022_03 dp LEFT JOIN dp_trade_payment_record_2021_01 r ON dp.platform_order_no = r.platform_order_no AND dp.platform_order_no = 'DD202207011147529522641' AND dp.merchant_no = 'M202010131029473208935' AND dp.order_time >= '2022-07-02 00:00:00.000' AND dp.order_time <= '2022-07-02 23:59:59.999' AND dp.org_platform_order_no IS NULL AND dp.cancel_flag != '1'` ..... ## Reason analyze (If you can) 1、My binding table configuration error 2、My custom fragment rule is wrong ## Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. yml： ```yaml # 数据源配置 spring: # 配置sharding jdbc分片规则 shardingsphere: props: sql-show: true datasource: # 连接名称（下面要用这个名称来区分库） names: ds0 ds0: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://123.56.4.237:3306/decard_pay_dev?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8 username: qiqi password: qiqi # 配置分片规则 rules: sharding: tables: dp_trade_payment_order: # 配置数据节点 actual-data-nodes: ds0.dp_trade_payment_order_$->{2020..2025}_0$->{1..4} # 分表策略 table-strategy: complex: sharding-columns: platform_order_no sharding-algorithm-name: order-algorithm dp_trade_payment_record: # 配置数据节点 actual-data-nodes: ds0.dp_trade_payment_record_$->{2020..2025}_0$->{1..4} # 分表策略 table-strategy: complex: sharding-columns: platform_order_no sharding-algorithm-name: order-algorithm sharding-algorithms: order-algorithm: type: CLASS_BASED props: strategy: COMPLEX algorithmClassName: com.decard.sharding.config.OrderComplexKeysShardingAlgorithm bindingTables: dp_trade_payment_order,dp_trade_payment_record ``` Custom sharding rules： ```java package com.decard.sharding.config; import com.alibaba.fastjson.JSONObject; import com.decard.sharding.config.parser.OrderTimeParser; import com.decard.sharding.config.parser.PaySuccessTimeParser; import com.decard.sharding.config.parser.PlatformOrderNoParser; import com.decard.sharding.util.DateUtils; import com.google.common.collect.Range; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingAlgorithm; import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingValue; import java.util.*; import java.util.function.Function; import java.util.stream.Collectors; /** * 复合分片算法 */ @Slf4j public class OrderComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm { /** * 平台订单号列名 */ private static final String COLUMN_PLATFORM_ORDER_NO = "platform_order_no"; /** * 订单时间列名 */ private static final String COLUMN_ORDER_TIME = "order_time"; /** * 支付成功时间列名 */ private static final String COLUMN_PAY_SUCCESS_TIME = "pay_success_time"; private PlatformOrderNoParser platformOrderNoParser = new PlatformOrderNoParser(); private OrderTimeParser orderTimeParser = new OrderTimeParser(); private PaySuccessTimeParser paySuccessTimeParser = new PaySuccessTimeParser(); @Override public Collection<String> doSharding(Collection availableTargetNames, ComplexKeysShardingValue shardingValue) { Set<String> tables = new HashSet<>(); log.info("\navailableTargetNames:======>>\n{}", JSONObject.toJSONString(availableTargetNames, true)); log.info("\nshardingValue:======>>\n{}", JSONObject.toJSONString(shardingValue, true)); //逻辑表名 String logicTableName = shardingValue.getLogicTableName(); // 平台订单号 Collection<Comparable> platformOrderNoList = (Collection<Comparable>) shardingValue.getColumnNameAndShardingValuesMap().getOrDefault(COLUMN_PLATFORM_ORDER_NO,new ArrayList<>(1)); Set<String> platformTableSet = platformOrderNoList.stream() .map(item -> DateUtils.getYearAndQuarter(platformOrderNoParser.apply(item))) .filter(item -> StringUtils.isNotBlank(item)) .map(item -> logicTableName + "_" + item) .collect(Collectors.toSet()); Range<Comparable> platformOrderNoRange = (Range<Comparable>) shardingValue.getColumnNameAndRangeValuesMap().get(COLUMN_PLATFORM_ORDER_NO); Set<String> platformOrderNoRangeSet = getTablesByRange(platformOrderNoParser, platformOrderNoRange, availableTargetNames); // 订单时间 Collection<Comparable> orderTimeList = (Collection<Comparable>) shardingValue.getColumnNameAndShardingValuesMap().getOrDefault(COLUMN_ORDER_TIME,new ArrayList<>(1)); Set<String> orderTimeSet = orderTimeList.stream() .map(item -> DateUtils.getYearAndQuarter(orderTimeParser.apply(item))) .filter(item -> StringUtils.isNotBlank(item)) .map(item -> logicTableName + "_" + item) .collect(Collectors.toSet()); Range<Comparable> orderTimeRange = (Range<Comparable>) shardingValue.getColumnNameAndRangeValuesMap().get(COLUMN_ORDER_TIME); Set<String> orderTimeRangeSet = getTablesByRange(orderTimeParser, orderTimeRange, availableTargetNames); // 支付成功时间 Collection<Comparable> paySuccessTimeList = (Collection<Comparable>) shardingValue.getColumnNameAndShardingValuesMap().getOrDefault(COLUMN_PAY_SUCCESS_TIME,new ArrayList<>(1)); Set<String> paySuccessTimeTableSet = paySuccessTimeList.stream() .map(item -> DateUtils.getYearAndQuarter(paySuccessTimeParser.apply(item))) .filter(item -> StringUtils.isNotBlank(item)) .map(item -> logicTableName + "_" + item) .collect(Collectors.toSet()); Range<Comparable> paySuccessTimeRange = (Range<Comparable>) shardingValue.getColumnNameAndRangeValuesMap().get(COLUMN_PAY_SUCCESS_TIME); Set<String> paySuccessTimeRangeSet = getTablesByRange(paySuccessTimeParser, paySuccessTimeRange, availableTargetNames); tables.addAll(platformTableSet); tables.addAll(platformOrderNoRangeSet); tables.addAll(orderTimeSet); tables.addAll(orderTimeRangeSet); tables.addAll(paySuccessTimeTableSet); tables.addAll(paySuccessTimeRangeSet); log.info("\n路由的表:======>>\n{}", JSONObject.toJSONString(tables, true)); return tables; } private Set<String> getTablesByRange(Function<Comparable,Date> parser, Range<Comparable> range, Collection<String> availableTargetNames){ Set<String> tables = new HashSet<>(); if(range == null){ return tables; } Date beginTime = null; Date endTime = null; String begin = ""; String end = ""; if (range.hasLowerBound()) { Comparable lowerEndpoint = range.lowerEndpoint(); beginTime = parser.apply(lowerEndpoint); begin = DateUtils.getYearAndQuarter(beginTime); } if (range.hasUpperBound()) { Comparable upperEndpoint = range.upperEndpoint(); endTime = parser.apply(upperEndpoint); end = DateUtils.getYearAndQuarter(endTime); } if(beginTime != null && endTime != null && beginTime.compareTo(endTime) > 0){ return tables; } for (String availableTargetName : availableTargetNames) { String substring = availableTargetName.substring(availableTargetName.length() - 7); if(beginTime != null && endTime != null){ if (convertInt(substring) >= convertInt(begin) && convertInt(substring) <= convertInt(end)) { tables.add(availableTargetName); } }else if(beginTime != null && convertInt(substring) >= convertInt(begin)){ tables.add(availableTargetName); }else if(endTime != null && convertInt(substring) <= convertInt(end)){ tables.add(availableTargetName); } } return tables; } /** * @return */ public int convertInt(String x){ x = StringUtils.remove(x, "_"); return Integer.parseInt(x); } @Override public Properties getProps() { return null; } @Override public void init(Properties properties) { } } ``` · ## Example codes for reproduce this issue (such as a github link). -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
