Tidb: Double precision does not compatible with MySQL

Created on 8 Jan 2021 · 5Comments · Source: pingcap/tidb

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t;
create table t (a double(5, 3));
insert into t values (1);
select * from t;

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

Result of MySQL 8.0 and 5.7:

mysql> select * from t;
+-------+
| a     |
+-------+
| 1.000 |
+-------+

3. What did you see instead (Required)

| a |
+---+
| 1 |
+---+

4. What is your TiDB version? (Required)

master, cec1a926583b008f919430b35a032a38098ddcbc

siexecution

Source

tangenta

Most helpful comment

According to MySQL's doc, https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html ,

As of MySQL 8.0.17, the nonstandard FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated and you should expect support for it to be removed in a future version of MySQL.

using double with specified precision is deprecated, the user should use decimal if wanted, and TiDB works in this case

drop table if exists t;
create table t (a decimal(5, 3));
insert into t values (1);
select * from t;

TiDB [test]> select * from t;
+-------+
| a     |
+-------+
| 1.000 |
+-------+
1 row in set (0.001 sec)

ichn-hu on 11 Jan 2021

👍2

All 5 comments

According to MySQL's doc, https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html ,

As of MySQL 8.0.17, the nonstandard FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated and you should expect support for it to be removed in a future version of MySQL.

using double with specified precision is deprecated, the user should use decimal if wanted, and TiDB works in this case

drop table if exists t;
create table t (a decimal(5, 3));
insert into t values (1);
select * from t;

TiDB [test]> select * from t;
+-------+
| a     |
+-------+
| 1.000 |
+-------+
1 row in set (0.001 sec)

ichn-hu on 11 Jan 2021

👍2

@jebter can we close this issue?

ichn-hu on 11 Jan 2021

We should document it here: https://docs.pingcap.com/tidb/stable/mysql-compatibility#type-system-differences