query result for float type is incompatible with previous version

[cfzjywxk]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table tf(f1 float(10,2));
insert into tf values("-34028234.66");
select * from tf;

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

mysql> select * from tf;
+--------------+
| f1           |
+--------------+
| -34028236.00 |
+--------------+
1 row in set (0.00 sec)

This result is the same with tidb version v4.0 and mysql.

3. What did you see instead (Required)

mysql> select * from tf;
+-----------+
| f1        |
+-----------+
| -34028236 |
+-----------+
1 row in set (0.01 sec)

Seems the float type logic is changed in the master branch and incompatibility is introduced.

4. What is your TiDB version? (Required)

master version with commit hash 9cb9b69b61a76e9cc3f8cfa27f86635c2496cf16

[b41sh]

MySQL don't support FLOAT(M,D) and DOUBLE(M,D) since 8.0.17
https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html
we ignored decimal in PR 21788

[cfzjywxk]

@b41sh Got, do we have related document to remind users about this incompatibily doing upgrade.

[b41sh]

@cfzjywxk This is recently modified, the document should not have been modified yet.

[johan-j]

There is still issues with float type.
To reproduce:
create table t(a float);
insert into t( -34028236.66);

mysql8:

+-----------+
| a         |
+-----------+
| -34028200 |
+-----------+

tidb:
+-----------+
| a         |
+-----------+
| -34028236 |
+-----------+

The issue reported here in (22791), seems to have started after https://github.com/pingcap/tidb/pull/21788.

[johan-j]

/assign

[johan-j]

Im working on this in #22823

The issue I mention in the comment above:

create table t(a float);
insert into t(a) values(-34028236.66);

Can be solved without solving the deprecated double(m,n)/float(m,n) compatibility issue mentioned in this issue.
So Im thinking if I should create a new issue for this instead, to track them separately?
So 2 issues:

• query result for float type is incompatible with previous version #22791: query result for float type is incompatible with previous version
• : Float column type, returns to specific result

Solving the float(m,n) problem I can do by printing in scientific format (which mysql does not), the same way as: #21692. If this is OK solution, I can implement that and solve both.

[zimulala]

/cc @XuHuaiyu

[xiongjiwei]

we have a document that says the difference

[bb7133]

Close it since we decide not to fix it.

[github-actions]

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