Partition Table Decimal Precision Expansion

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 分区表 decimal精度无法扩容

| username: 林夕一指

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] V6.5.0+
[Reproduction Path] Changing the decimal type field of a partitioned table from decimal(10,0) to decimal(20,4) results in an error: Unsupported modify column: table is partition table. According to the official documentation, it seems that there is no column type change.

| username: tidb菜鸟一只 | Original post link

I tested it, and it really doesn’t work. However, the length of the varchar type can be modified. Is the decimal type more special?

| username: WalterWj | Original post link

I remember that in MySQL, the precision of the decimal type in partitioned tables cannot be directly modified either.

| username: dba远航 | Original post link

A regular table should work, but a partitioned table won’t.

| username: MrSylar | Original post link

Varchar can only be increased, not decreased.

| username: tidb菜鸟一只 | Original post link

Changing from decimal(10,0) to decimal(20,4) increases the precision, and the field is not truncated.

| username: 林夕一指 | Original post link

I originally wanted to look at the source code, but I didn’t know where to start. :sweat_smile:

| username: 林夕一指 | Original post link

I also tried changing from decimal(10,0) to decimal(11,0), and it is not supported either.

| username: tidb菜鸟一只 | Original post link

However, changing varchar(20) to varchar(30) is possible, but changing it to varchar(10) is not.

| username: 林夕一指 | Original post link

This is the standard expectation :joy:, fields can only be expanded, not reduced.

| username: TiDBer_QKDdYGfz | Original post link

Mark, I really didn’t know about this restriction before.

| username: tidb菜鸟一只 | Original post link

So it might really be that the decimal field type is quite special.

| username: TiDBer_LM | Original post link

I tried it with MySQL and it worked. For TiDB, the internal format for decimal is in pkg/types/mydecimal. I see there is an add method, maybe it is not fully developed yet?

| username: 林夕一指 | Original post link

Based on the source code location you mentioned, I checked it. The pkg/types/mydecimal directory contains functions for decimal type calculations. I think it should be in ddl/column.go.

| username: lemonade010 | Original post link


| username: TiDBer_LM | Original post link

Yes, it should be in the file you mentioned, the onModifyColumn method. Check how MySQL handles it.

| username: 路在何chu | Original post link

It is not supported. You can only add a column, then update, and delete that column. In change.

| username: jhm633 | Original post link

The current product limitation can be tracked as an enhancement: Decimal type fields cannot be resized or precision improved within the partition table · Issue #54441 · pingcap/tidb · GitHub. The product will gradually optimize this in the future. The PR for restricting partition table modification is: ddl: restricting MODIFY COLUMN on partitioning columns. by mjonss · Pull Request #38670 · pingcap/tidb · GitHub.

| username: 林夕一指 | Original post link

This is the issue I submitted. :joy: After receiving the relevant reply, I checked the code. The source code in pkg/ddl/ddl_api.go intercepted the error.

Looking at the needChangeColumnData function, I found that the source code should actually be doing a decimal expansion check. Then, when judging the same type of change, it directly returns true. After reading the comments, how does a non-partitioned table change?

| username: tidb菜鸟一只 | Original post link

The main point is here:

If needChangeColumnData is true, it will reach here. As long as it is a partitioned table, it will directly return a “table is partition table” error. Only non-partitioned tables can proceed normally.