double type precision does not display in show create table while D is 0

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

As the TiDB Doc description, while D is omitted, the default is 0. but the type double has different behavior from double(10, 0) for insert some data.

If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.

mysql > create table t(a double)
Query OK, 0 rows affected
Time: 0.365s
mysql > create table t1(a double(10, 0));
Query OK, 0 rows affected
Time: 1.489s
mysql > insert into t values (-1.2500192275974457e+308);
Query OK, 1 row affected
Time: 0.163s
mysql > insert into t1 values (-1.2500192275974457e+308);
(1264, "Out of range value for column 'a' at row 1")

if there are differences, the show create table should not be same.

mysql > show create table t;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (\n  `a` double DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------+

1 row in set
Time: 0.088s
mysql > show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (\n  `a` double DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------------------------------------------------------+

mysql > desc t1;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | double | YES  |     | <null>  |       |
+-------+--------+------+-----+---------+-------+

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

show create table should have the precision while D is 0

mysql > show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (\n  `a` double(10,0) DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------+

mysql > desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | double(10,0) | YES  |     | <null>  |       |
+-------+--------------+------+-----+---------+-------+

3. What did you see instead (Required)

see above.

4. What is your TiDB version? (Required)

mysql > select tidb_version()\G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v4.0.10
Edition: Community
Git Commit Hash: dbade8cda4c5a329037746e171449e0a1dfdb8b3
Git Branch: heads/refs/tags/v4.0.10
UTC Build Time: 2021-01-15 02:59:27
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[TszKitLo40]

In MySQL8.0, it is the same result.

[AilinKid]

In TiDB, you can view the HTTP API via http://127.0.0.1:10080/schema/test/t to see the flen(M) and decimal(D).

If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10

the description above is for decimal type rather than double.

[morgo]

This is documented here: https://docs.pingcap.com/tidb/stable/mysql-compatibility#incompatibility-caused-by-deprecated-features

Because the feature is deprecated in MySQL, there are no current plans to support it.

[aytrack]

In MySQL8.0, it is the same result.

I run this again in MySQL 8.0.23, it shows the precision

mysql> create table t0(a double(10,0));
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show create table t0;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------+
| t0    | CREATE TABLE `t0` (
  `a` double(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

[aytrack]

I run this using decimal data type in TiDB, it shows the correct precision as I created. but when the flen(M) is ommited, the default value actually is 11

test> create table t0(a decimal);
Query OK, 0 rows affected
Time: 0.359s
test> show create table t0;
+-------+--------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------+
| t0    | CREATE TABLE `t0` (\n  `a` decimal(11,0) DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+--------------------------------------------------------------------------------------------------------------------+

1 row in set
Time: 0.059s
test> desc t0;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | decimal(11,0) | YES  |     | <null>  |       |
+-------+---------------+------+-----+---------+-------+

1 row in set
Time: 0.080s
mysql root@172.16.4.131:test> select tidb_version()\G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v4.0.10
Edition: Community
Git Commit Hash: dbade8cda4c5a329037746e171449e0a1dfdb8b3
Git Branch: heads/refs/tags/v4.0.10
UTC Build Time: 2021-01-15 02:59:27
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[AilinKid]

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

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

seems it is ok in current master

[morgo]

To confirm, there are two separate issues here:

1. TiDB does not implement precision for floating point values. This is documented at https://docs.pingcap.com/tidb/stable/mysql-compatibility#incompatibility-caused-by-deprecated-features (it is deprecated in MySQL, but not removed.) Because it will eventually be removed from MySQL, there are no current plans to implement this.

2. The default precision of decimal (fixed precision) differs between TiDB and MySQL. This issue has been resolved in master, where the precision now matches MySQL.

I am going to close this issue now, but please feel free to reopen if you have any additional questions. Thanks!

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

[v4.0.10]

6. Fixed versions

master