CONCAT negative decimal value is chopping last digit in TiDB

[ramanich1]

Bug Report

1. Minimal reproduce step (Required)

drop table if exists t1;
create table t1 (f1 decimal(5,5));
insert into t1 values (-0.12345);
select concat(f1),f1 from t1;

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

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table t1 (f1 decimal(5,5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (-0.12345);
Query OK, 1 row affected (0.00 sec)

mysql> select concat(f1),f1 from t1;
+------------+----------+
| concat(f1) | f1       |
+------------+----------+
| -0.12345   | -0.12345 |
+------------+----------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.22 sec)

mysql> create table t1 (f1 decimal(5,5));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values (-0.12345);
Query OK, 1 row affected (0.02 sec)

mysql> select concat(f1),f1 from t1;
+------------+----------+
| concat(f1) | f1       |
+------------+----------+
| -0.1234    | -0.12345 |
+------------+----------+
1 row in set, 1 warning (0.01 sec)

4. What is your TiDB version? (Required)

+-------------------------+--------------------------------------------------------------------------+
| Variable_name           | Value                                                                    |
+-------------------------+--------------------------------------------------------------------------+
| innodb_version          | 5.6.25                                                                   |
| protocol_version        | 10                                                                       |
| tidb_analyze_version    | 2                                                                        |
| tidb_row_format_version | 2                                                                        |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2                                                    |
| version                 | 5.7.25-TiDB-v5.2.1                                                       |
| version_comment         | TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible |
| version_compile_machine | x86_64                                                                   |
| version_compile_os      | osx10.8                                                                  |
+-------------------------+--------------------------------------------------------------------------+
9 rows in set (0.00 sec)

[aytrack]

TIDB

mysql> select concat(f1),f1 from t1;
Field   1:  `concat(f1)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_bin (46)
Length:     28
Max_length: 7
Decimals:   31
Flags:

Field   2:  `f1`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     7
Max_length: 8
Decimals:   5
Flags:      NUM


+------------+----------+
| concat(f1) | f1       |
+------------+----------+
| -0.1234    | -0.12345 |
+------------+----------+
1 row in set, 1 warning (0.00 sec)

mysql> select concat(cast(f1 as char)) from t1;
Field   1:  `concat(cast(f1 as char))`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  utf8mb4_bin (46)
Length:     67108860
Max_length: 8
Decimals:   31
Flags:


+--------------------------+
| concat(cast(f1 as char)) |
+--------------------------+
| -0.12345                 |
+--------------------------+
1 row in set (0.00 sec)

Mysql 8.0.26

mysql> select concat(f1),f1 from t1;select concat(f1),f1 from t1;
Field   1:  `concat(f1)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_general_ci (45)
Length:     28
Max_length: 8
Decimals:   31
Flags:

Field   2:  `f1`
Catalog:    `def`
Database:   `hchwang`
Table:      `t1`
Org_table:  `t1`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     7
Max_length: 8
Decimals:   5
Flags:      NUM


+------------+----------+
| concat(f1) | f1       |
+------------+----------+
| -0.12345   | -0.12345 |
+------------+----------+
1 row in set (0.04 sec)

[morgo]

I think this is critical? Decimal is precison math, and the scenario is not unexpected for user-supplied queries.

[aytrack]

I think this is critical? Decimal is precison math, and the scenario is not unexpected for user-supplied queries.

yes, and it affects all versions.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.

[cyliu0]

Crc32 has been affected by this too. Already fixed in master. In case someone will be curious about different crc32 results in different versions of TiDB

[guo-shaoge]

Crc32 has been affected by this too. Already fixed in master. In case someone will be curious about different crc32 results in different versions of TiDB

Can you please paste the schema of jxtest.

[cyliu0]

k is decimal(8,8)

[guo-shaoge]

THX. So according to the definition of k, the outputs of crc32(k) and concat_ws() are both reasonable.(Also same with MySQL).