Invalid column type decimal(0,0) can be created unexpectedly

[JaySon-Huang]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

-- If we create a column with decimal(0,0), then tidb will automatically fix it as decimal(10,0)
-- But when we create a column with decimal(0), then tidb will create  decimal(0,0), which is not as expected
TiDB test> create table a(b decimal(0,0), c decimal(0));
TiDB test> show create table a;
+-------+-------------------------------------------------------------+
| Table | Create Table                                                |
+-------+-------------------------------------------------------------+
| a     | CREATE TABLE `a` (                                          |
|       |   `b` decimal(10,0) DEFAULT NULL,                           |
|       |   `c` decimal(0,0) DEFAULT NULL                             |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------+

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

TiDB test> create table a(b decimal(0,0), c decimal(0));
TiDB test> show create table a;
+-------+-------------------------------------------------------------+
| Table | Create Table                                                |
+-------+-------------------------------------------------------------+
| a     | CREATE TABLE `a` (                                          |
|       |   `b` decimal(10,0) DEFAULT NULL,                           |
|       |   `c` decimal(10,0) DEFAULT NULL                            |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------+

3. What did you see instead (Required)

TiDB test> create table a(b decimal(0,0), c decimal(0));
TiDB test> show create table a;
+-------+-------------------------------------------------------------+
| Table | Create Table                                                |
+-------+-------------------------------------------------------------+
| a     | CREATE TABLE `a` (                                          |
|       |   `b` decimal(10,0) DEFAULT NULL,                           |
|       |   `c` decimal(0,0) DEFAULT NULL                             |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------+

And this will lead to tiflash failed to sync the schema and cause queries error or failed to start tiflash-server. Reported from asktug: https://asktug.com/t/topic/1027023/16

4. What is your TiDB version? (Required)

select tidb_version() \G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v6.5.4
Edition: Community
Git Commit Hash: d7ce2f2faa1da3177a0f0a7e825f6e8fccd13ec8
Git Branch: heads/refs/tags/v6.5.4
UTC Build Time: 2023-08-23 08:32:40
GoVersion: go1.19.12
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[JaySon-Huang]

/label component/ddl

[ti-chi-bot]

@JaySon-Huang: The label(s) component/ddl cannot be applied. These labels are supported: fuzz/sqlancer, challenge-program, compatibility-breaker, first-time-contributor, contribution, good first issue, correctness, duplicate, proposal, security, ok-to-test, needs-ok-to-test, needs-more-info, needs-cherry-pick-release-5.4, needs-cherry-pick-release-6.1, needs-cherry-pick-release-6.5, needs-cherry-pick-release-7.1, needs-cherry-pick-release-7.5, needs-cherry-pick-release-8.1, affects-5.4, affects-6.1, affects-6.5, affects-7.1, affects-7.5, affects-8.1, may-affects-5.4, may-affects-6.1, may-affects-6.5, may-affects-7.1, may-affects-7.5, may-affects-8.1.

In response to this:

/label component/ddl

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[seiya-annie]

/report community