hash_mod bigint complementary results are obtained.

[xiaoti2]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

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?

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

Expected behavior

Actual behavior

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

[xiaoti2]

Use ShardingSphere 5.5.1 and 5.5.2 to split into 32 shards for both full and incremental migration CREATE SHARDING TABLE RULE device_ota_upgrade_record(
STORAGE_UNITS(ds_1), SHARDING_COLUMN=product_id, TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="32"))
);
Product_id is a bigint.
Incremental data insertion:
Part of the data inserted into Device_ota_upgrade_decord_3 has been redirected to Device_ota_upgrade_decord_29
Part of the data inserted into Device_ota_upgrade.record_4 went to Device_ota_upgrade.record_28,
Part of the data inserted into Device_ota_upgrade_decord_5 has been redirected to Device_ota_upgrade_decord_27
It seems quite regular, 3+29=32, 4+28=32, 5+27=32. I don't know which aspect of the configuration went wrong. Data verification is not a problem. Is the full migration algorithm inconsistent with the incremental algorithm?

Here is an example of actual data:
select count(*) cnt,product_id from device_ota_upgrade_record_3 group by product_id;
+-----+---------------------+
| cnt | product_id |
+-----+---------------------+
| 123 | 1594642343748886528 |
| 105 | 1914264300839890944 |
+-----+---------------------+
2 rows in set (0.00 sec)

Incremental insertion: insert into device_ota_upgrade_record(id,product_id) values(88888888881,1914264300839890944);
select * from device_ota_upgrade_record_29 where id =88888888881;
Expected to fall into the third shard, but actually fell into 29 shards.

select count(*) cnt,product_id from device_ota_upgrade_record_4 group by product_id; (Full data distribution)
+------+---------------------+
| cnt | product_id |
+------+---------------------+
| 3034 | 28 |
| 124 | 92 |
| 1110 | 1714832026754129920 |
| 128 | 1812805249227071488 |
+------+---------------------+
4 rows in set (0.01 sec)
Incremental insertion:
insert into device_ota_upgrade_record(id,product_id) values(555552 ,1714832026754129920);
insert into device_ota_upgrade_record(id,product_id) values(555553 ,1812805249227071488);
Expected to fall on the Device_ota_upgrade.record_4 shard, actual Device_ota_upgrade.record_28.
select * from device_ota_upgrade_record_28 where id =555552;
select * from device_ota_upgrade_record_28 where id =555553;

The same applies to 8 shards: full migration, product_id data distribution
mysql> select count(*) cnt,product_id from device_ota_upgrade_record_5 group by product_id;
+-------+---------------------+
| cnt | product_id |
+-------+---------------------+
| 131 | 35 |
| 1 | 91 |
| 6797 | 155 |
| 2 | 1537616184546033664 |
| 96 | 1539892061970046976 |
| 5 | 1542415541089280000 |
| 34787 | 1729402509999788032 |
| 10837 | 1812812533583880192 |
+-------+---------------------+
8 rows in set (0.01 sec)
Incremental insertion:
insert into device_ota_upgrade_record(id,product_id) values(7888880 , 91);
insert into device_ota_upgrade_record(id,product_id) values(7888881 , 155);
insert into device_ota_upgrade_record(id,product_id) values(7888882 , 1537616184546033664);
insert into device_ota_upgrade_record(id,product_id) values(7888883 , 1729402509999788032);
Query incremental migration results:
mysql> select * from device_ota_upgrade_record_5 where id >= 7888880 and id <= 7888883;
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
| id | device_id | device_name | ota_task_id | device_curr_firmware_version | state | describe | product_id | upgrade_firmware_version | created_time | last_modified_time | start_time | end_time | error_code | detail_code |
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
| 7888883 | 0 | | 0 | NULL | 0 | NULL | 1729402509999788032 | NULL | 0 | 0 | NULL | NULL | NULL | NULL |
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
1 row in set (0.00 sec)

mysql> select * from device_ota_upgrade_record_3 where id >= 7888880 and id <= 7888883;
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
| id | device_id | device_name | ota_task_id | device_curr_firmware_version | state | describe | product_id | upgrade_firmware_version | created_time | last_modified_time | start_time | end_time | error_code | detail_code |
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
| 7888880 | 0 | | 0 | NULL | 0 | NULL | 91 | NULL | 0 | 0 | NULL | NULL | NULL | NULL |
| 7888881 | 0 | | 0 | NULL | 0 | NULL | 155 | NULL | 0 | 0 | NULL | NULL | NULL | NULL |
| 7888882 | 0 | | 0 | NULL | 0 | NULL | 1537616184546033664 | NULL | 0 | 0 | NULL | NULL | NULL | NULL |
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
3 rows in set (0.00 sec)

I lack coding ability， Provide an idea, possibility, asked DeepSeek ： (product_id.hashCode() & Integer.MAX_VALUE) % 32 MAX_VALUE = 0x7fffffff; The difference between the Math. abs ((long) shardingValue. hashCode())% shardingCount algorithm is somewhat similar to the one used in full usage Math.abs((long) shardingValue.hashCode()) % shardingCount Algorithm, incremental use of (product_id. hashCode()&Integer. MAXVNet)% 32 MAXVNet=0x7ffffff; When hashh_mod is negative, the incremental data is written to the number of shards of the complementary remainder.

System.out.println("----bigint------");
    BigInteger bigInteger = new BigInteger("1594642343748886528");
    System.out.println(Math.abs((long) bigInteger.hashCode()) % 32);
    System.out.println(bigInteger.hashCode());
    bigInteger = new BigInteger("1914264300839890944");
    System.out.println(Math.abs((long) bigInteger.hashCode()) % 32);
    System.out.println(bigInteger.hashCode());
    bigInteger = new BigInteger("1812805249227071488");
    System.out.println(bigInteger.hashCode());
    System.out.println(Math.abs((long) bigInteger.hashCode()) % 32);

    System.out.println("------big decimal-------");
    BigDecimal bigDecimal = new BigDecimal("1594642343748886528");
    System.out.println(bigDecimal.hashCode());
    System.out.println(Math.abs((long) bigDecimal.hashCode()) % 32);
    bigDecimal = new BigDecimal("1914264300839890944");
    System.out.println(bigDecimal.hashCode());
    System.out.println(Math.abs((long) bigDecimal.hashCode()) % 32);
    bigDecimal = new BigDecimal("1812805249227071488");
    System.out.println(bigDecimal.hashCode());
    System.out.println(Math.abs((long) bigDecimal.hashCode()) % 32);

    System.out.println("------long-------");
    Long aLong = Long.valueOf("1594642343748886528");
    System.out.println(aLong.hashCode());
    System.out.println(Math.abs((long) aLong.hashCode()) % 32);
    aLong = Long.valueOf("1914264300839890944");
    System.out.println(aLong.hashCode());
    System.out.println(Math.abs((long) aLong.hashCode()) % 32);
    aLong = Long.valueOf("1812805249227071488");
    System.out.println(aLong.hashCode());
    System.out.println(Math.abs((long) aLong.hashCode()) % 32);

----bigint------
3
-117741155
29
-2125479325
185096124
28
------big decimal-------
644991491
3
-1465349635
3
1443012548
4
------long-------
1557376707
3
1398089021
29
-435371836
28

[terrymanu]

• Understanding

• On 5.5.1/5.5.2 with hash_mod, sharding-count=32, sharding key product_id (bigint). Full data lands on shards like 3/4/5, but incremental inserts of the same product_id land on “complement” shards (3→29, 4→28, 5→27). Suspected that full and incremental use different
  sharding algorithms.

Root Cause

• hash_mod calls hashCode() on the Java object of the sharding value, then abs % sharding-count. The same numeric value yields different hashCode() results if passed as different Java types (Long / BigInteger / BigDecimal / String), leading to different routing. In your
  example, BigDecimal hashes to 3, but Long/BigInteger hashes to 29—exactly the 3 vs 29 complement—so full vs incremental likely use different Java types/decoding, not different algorithms.

Analysis

• Possible difference: full migration/initial load decodes bigint as BigDecimal or string; incremental writes bind Long/BigInteger via JDBC/driver.
• hash_mod relies on type consistency: the same sharding key must be passed with the same Java type to produce consistent routing.
• Missing info: table DDL, clients and parameter binding types for full vs incremental, whether any sync/migration pipeline converts types.

Conclusion / Suggestions

1. First, ensure the sharding key type is consistent: on the write side use setLong (or a single chosen type), and check the full/incremental pipeline decoding for bigint to avoid mixing BigDecimal/String with Long.
2. If the type is confirmed identical for full and incremental but the issue persists, please provide: table DDL, sharding rule, a minimal reproducible SQL with actual parameter types (print the parameter class), and the routing/SQL Show logs for further diagnosis.
3. hash_mod details: https://shardingsphere.apache.org/document/current/en/user-manual/common-config/builtin-algorithm/sharding/#hash_mod-sharding-algorithm

[xiaoti2]

thanks:
DDL： CREATE TABLE device_ota_upgrade_record (
'id' bigint unsigned NOT NULL COMMENT 'primary key'
'device_id' bigint unsigned NOT NULL COMMENT 'device ID'
'device_name' varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'Device name'
'ota_task_id' bigint unsigned NOT NULL COMMENT 'OTA upgrade task ID'
device_curr_firmware_version varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT "Current firmware version of the device"
'state' tinyint NOT NULL COMMENT 'ota upgrade status 1: Pending push 2: Pushed 3: Upgrade in progress 4: Upgrade Successful 5: Upgrade Failed 6: Cancelled '
'describe' varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'Upgrade information note'
'product_id' bigint unsigned DEFAULT NULL COMMENT 'product ID, added on 2022-08-04'
upgrade_firmware_version varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT "Upgraded firmware version, added on August 4,2022.
'created_time' bigint unsigned NOT NULL COMMENT 'creation time'
'last_modified_time' bigint unsigned NOT NULL COMMENT 'Last updated time'
'start_time' bigint unsigned DEFAULT NULL COMMENT 'Download or upgrade start time'
'end_time' bigint unsigned DEFAULT NULL COMMENT 'Download or upgrade end time'
'error_code' int DEFAULT NULL COMMENT 'error code'
'detail_code' int DEFAULT NULL COMMENT 'detail error code'
'firmware_id' bigint DEFAULT NULL COMMENT 'firmware id'
'product_unique_id' varchar(16) DEFAULT NULL COMMENT 'product id (string)'
PRIMARY KEY (id) USING BTREE,
KEY ota_task_id_index (ota_task_id) USING BTREE,
KEY device_ota_upgrade_record_ota_task_id_device_id_index (ota_task_id,device_id) USING BTREE,
KEY device_ota_upgrade_upgrade_firmware_index (product_id,upgrade_firmware_version,device_id) USING BTREE,
KEY device_ota_upgrade_firmware_version_index (product_id,upgrade_firmware_version,device_name) USING BTREE,
KEY device_ota_upgrade_record_device_id_state_index (device_id,state) USING BTREE
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT=' Device Firmware Upgrade record ';

CREATE TABLE device_ota_upgrade_record_x (
'id' bigint unsigned NOT NULL COMMENT 'primary key'
'device_id' bigint unsigned NOT NULL COMMENT 'device ID'
'device_name' varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'Device name'
'ota_task_id' bigint unsigned NOT NULL COMMENT 'OTA upgrade task ID'
device_curr_firmware_version varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT "Current firmware version of the device"
'state' tinyint NOT NULL COMMENT 'ota upgrade status 1: Pending push 2: Pushed 3: Upgrade in progress 4: Upgrade Successful 5: Upgrade Failed 6: Cancelled '
'describe' varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'Upgrade information note'
'product_id' bigint unsigned DEFAULT NULL COMMENT 'product ID, added on 2022-08-04'
upgrade_firmware_version varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT "Upgraded firmware version, added on August 4,2022.
'created_time' bigint unsigned NOT NULL COMMENT 'creation time'
'last_modified_time' bigint unsigned NOT NULL COMMENT 'Last updated time'
'start_time' bigint unsigned DEFAULT NULL COMMENT 'Download or upgrade start time'
'end_time' bigint unsigned DEFAULT NULL COMMENT 'Download or upgrade end time'
'error_code' int DEFAULT NULL COMMENT 'error code'
'detail_code' int DEFAULT NULL COMMENT 'detail error code'
PRIMARY KEY (id) USING BTREE,
KEY ota_task_id_index (ota_task_id) USING BTREE,
KEY device_ota_upgrade_record_ota_task_id_device_id_index (ota_task_id,device_id) USING BTREE,
KEY device_ota_upgrade_upgrade_firmware_index (product_id,upgrade_firmware_version,device_id) USING BTREE,
KEY device_ota_upgrade_firmware_version_index (product_id,upgrade_firmware_version,device_name) USING BTREE,
KEY device_ota_upgrade_record_device_id_state_index (device_id,state) USING BTREE
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT=' Device Firmware Upgrade Record ';

incremental data SQL: Insert SQL through the MySQL client：
client: mysql --version
mysql Ver 14.14 Distrib 5.7.44-48, for Linux (x86_64) using 6.2
insert into device_ota_upgrade_record(id,product_id) values(555550 ,28);
insert into device_ota_upgrade_record(id,product_id) values(555551 ,92);
insert into device_ota_upgrade_record(id,product_id) values(555552 ,1714832026754129920);
insert into device_ota_upgrade_record(id,product_id) values(555553 ,1812805249227071488);
insert into device_ota_upgrade_record(id,product_id) values(88888888881,1914264300839890944);
insert into device_ota_upgrade_record(id,product_id) values(555552 ,1714832026754129920);
insert into device_ota_upgrade_record(id,product_id) values(555553 ,1812805249227071488);

[terrymanu]

If you can’t keep the sharding key type consistent across all paths (full load, incremental, migration), avoid hash_mod, which depends on hashCode(). Instead, use a custom sharding algorithm that normalizes the value (e.g., parse to a long and then mod) so different input types still produce consistent shard results.

[relengxing]

The numeric type should not use hash_mod, But the inconsistency between incremental and full results is also confusing.

I searched for the code.
full migration use InventoryColumnValueReaderEngine.class

            case Types.BIGINT:
                if (isSigned(metaData, columnIndex)) {
                    return resultSet.getLong(columnIndex);
                } else {
                    return resultSet.getBigDecimal(columnIndex);
                }

incremental migration use MySQLBinlogUnsignedBigintHandler.class

    @Override
    public Number handle(final Long value) {
        return value < 0L ? BIGINT_MODULO.add(BigInteger.valueOf(value)) : value;
    }

unsign bigint convert to BigDecimal(full migration) or Long(incremental migration).
Is it possible to convert to the same type?

Adding a unified type normalization layer may be better.

// add new file：PipelineColumnValueNormalizer.java
package org.apache.shardingsphere.data.pipeline.core.ingest.record;

import org.apache.shardingsphere.data.pipeline.core.metadata.model.PipelineColumnMetaData;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Types;

public final class PipelineColumnValueNormalizer {
    
    public static Serializable normalize(PipelineColumnMetaData columnMetaData, Serializable value) {
        if (null == value) {
            return null;
        }
        
        int dataType = columnMetaData.getDataType();
        boolean isSigned = columnMetaData.isSigned();
        
        switch (dataType) {
            case Types.BIGINT:
                return normalizeBigint(value, isSigned);
            case Types.INTEGER:
                return normalizeInt(value, isSigned);
            // other type ...
            default:
                return value;
        }
    }
    
    private static Serializable normalizeBigint(Serializable value, boolean isSigned) {
        if (isSigned) {
            if (value instanceof Long) {
                return value;
            }
            if (value instanceof Number) {
                return ((Number) value).longValue();
            }
            if (value instanceof String) {
                return Long.parseLong((String) value);
            }
        } else {
            if (value instanceof BigDecimal) {
                return value;
            }
            if (value instanceof BigInteger) {
                return new BigDecimal((BigInteger) value);
            }
            if (value instanceof Long) {
                long longValue = (Long) value;
                if (longValue < 0) {
                    BigInteger modulo = new BigInteger("18446744073709551616");
                    return new BigDecimal(modulo.add(BigInteger.valueOf(longValue)));
                }
                return BigDecimal.valueOf(longValue);
            }
            if (value instanceof Number) {
                return new BigDecimal(value.toString());
            }
        }
        return value;
    }
    
    private static Serializable normalizeInt(Serializable value, boolean isSigned) {
        if (isSigned) {
            if (value instanceof Integer) {
                return value;
            }
            if (value instanceof Number) {
                return ((Number) value).intValue();
            }
        } else {
            if (value instanceof Long) {
                return value;
            }
            if (value instanceof Number) {
                return ((Number) value).longValue();
            }
        }
        return value;
    }
}

or modify MySQLBinlogUnsignedBigintHandler.java

@Override
public Number handle(final Long value) {
        return value < 0L ? BIGINT_MODULO.add(BigInteger.valueOf(value)) : new BigDecimal(value.toString());
}

[sandynz]

@xiaoti2 @relengxing thanks for your feedback.
We'll improve it in next days.
And you could try MOD to replace HASH_MOD for now.

[relengxing]

@sandynz Do you have any suggestions for making changes. If possible, I hope to participate in the construction.

[sandynz]

@sandynz Do you have any suggestions for making changes. If possible, I hope to participate in the construction.

Thanks. I've tried to fix it, since there might be new release at next week.
You could try other issues of pipeline or migration.

[sandynz]

Hi @xiaoti2 , a new PR was merged to master branch, you could try on latest commit.