Inconsistent cast conversion in Tiflash

[curry-oss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Drop table if exists test_table;
CREATE TABLE test_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    col1 VARCHAR(255),
    col2 TINYINT,
    col3 SMALLINT
);

INSERT INTO test_table (col1, col2, col3) VALUES
    ('-9223372036854775809', -128, -32768),
    ('-9223372036854775808', 127, 32767);

ALTER TABLE test_table SET TIFLASH REPLICA 1;

SELECT /*+ READ_FROM_STORAGE(TIKV[test_table])*/
    id,
    col1,
    CAST(col1 AS SIGNED) AS signed_int,
    CAST(col1 AS UNSIGNED) AS unsigned_int,
    CAST(col1 AS DECIMAL(65,30)) AS decimal_value,
    CAST(col1 AS DOUBLE) AS double_value
FROM
    test_table;
    
SELECT /*+ READ_FROM_STORAGE(TIFLASH[test_table])*/
    id,
    col1,
    CAST(col1 AS SIGNED) AS signed_int,
    CAST(col1 AS UNSIGNED) AS unsigned_int,
    CAST(col1 AS DECIMAL(65,30)) AS decimal_value,
    CAST(col1 AS DOUBLE) AS double_value
FROM
    test_table;

2. What did you expect to see? (Required)

I find that unsigned_int is inconsistent across two queries.

3. What did you see instead (Required)

mysql> SELECT /*+ READ_FROM_STORAGE(TIKV[test_table])*/
    ->     id,
    ->     col1,
    ->     CAST(col1 AS SIGNED) AS signed_int,
    ->     CAST(col1 AS UNSIGNED) AS unsigned_int,
    ->     CAST(col1 AS DECIMAL(65,30)) AS decimal_value,
    ->     CAST(col1 AS DOUBLE) AS double_value
    -> FROM
    ->     test_table;
+----+----------------------+----------------------+---------------------+-----------------------------------------------------+-----------------------+
| id | col1                 | signed_int           | unsigned_int        | decimal_value                                       | double_value          |
+----+----------------------+----------------------+---------------------+-----------------------------------------------------+-----------------------+
|  1 | -9223372036854775809 | -9223372036854775808 | 9223372036854775808 | -9223372036854775809.000000000000000000000000000000 | -9.223372036854776e18 |
|  2 | -9223372036854775808 | -9223372036854775808 | 9223372036854775808 | -9223372036854775808.000000000000000000000000000000 | -9.223372036854776e18 |
+----+----------------------+----------------------+---------------------+-----------------------------------------------------+-----------------------+
2 rows in set, 4 warnings (0.01 sec)

mysql> SELECT /*+ READ_FROM_STORAGE(TIFLASH[test_table])*/     id,     col1,     CAST(col1 AS SIGNED) AS signed_int,     CAST(col1 AS UNSIGNED) AS unsigned_int,     CAST(col1 AS DECIMAL(65,30)) AS decimal_value,     CAST(col1 AS DOUBLE) AS double_value FROM
  test_table;
+----+----------------------+----------------------+---------------------+-----------------------------------------------------+-----------------------+
| id | col1                 | signed_int           | unsigned_int        | decimal_value                                       | double_value          |
+----+----------------------+----------------------+---------------------+-----------------------------------------------------+-----------------------+
|  1 | -9223372036854775809 | -9223372036854775808 |                   0 | -9223372036854775809.000000000000000000000000000000 | -9.223372036854776e18 |
|  2 | -9223372036854775808 | -9223372036854775808 | 9223372036854775808 | -9223372036854775808.000000000000000000000000000000 | -9.223372036854776e18 |
+----+----------------------+----------------------+---------------------+-----------------------------------------------------+-----------------------+
2 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

TiDB v8.4.0

[curry-oss]

/label affects-8.4

[curry-oss]

/label affects-8.5

[curry-oss]

@jebter Hello, could you help us check this bug, and #9654 && #9664? We would like to know whether this inconsistency is permitted. Thanks!

[yibin87]

Confirm that tidb, mysql gets 9223372036854775808 for cast('-9223372036854775808' as UNSIGNED). Thus TiFlash needs to be consistent with tidb, we'll fix this behavior.

[yibin87]

And for other negative strings than "-9223372036854775808", tiflash works the same as tidb.

[curry-oss]

Thanks for your kind reply~