Bug #4485 Floating point conversions are inconsistent.
Submitted: 9 Jul 2004 14:23 Modified: 29 Sep 2008 16:33
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:All OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Tags: affects_connectors
Triage: Triaged: D2 (Serious)

[9 Jul 2004 14:23] Konstantin Osipov
Description:
Consider the following example:
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (f float, d double, cf char(20), cd char(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 (f, d) values (1.1, 1.1);
Query OK, 1 row affected (8.22 sec)

mysql> update t1 set cf=f, cd=d;
Query OK, 1 row affected (4.65 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+------+------------------+------+
| f    | d    | cf               | cd   |
+------+------+------------------+------+
|  1.1 |  1.1 | 1.10000002384186 | 1.1  |
+------+------+------------------+------+
1 row in set (4.40 sec)

As you can see, no matter exceeds value floating point precision or not, when it's conversion to character string is incorrect.
The fact that the value itself is printed OK, and value of double column is converted
and printed ok as well says that something is wrong in float -> string conversion 
sequence inside the server (field_conf function).

How to repeat:
See given SQL log.

Suggested fix:
Fix field_conv function to distinct float and double precision.
[9 Jul 2004 17:56] Konstantin Osipov
Another example (from PeterG talk about SQL standard compliance)
mysql> create table t1 (f float, d double);
ERROR 1050: Table 't1' already exists
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (f float, d double);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 (f, d) values (0.1, 0.1);
Query OK, 1 row affected (0.00 sec)

mysql> select avg(f), avg(d) from t1;
+------------------+--------+
| avg(f)           | avg(d) |
+------------------+--------+
| 0.10000000149012 |    0.1 |
+------------------+--------+
1 row in set (0.00 sec)
[18 Jan 2005 10:43] Konstantin Osipov
Monty says: new decimal should fix this and similar bugs nicely. 
Let us wait for new decimal type and fix it in 5.0 if there still be need.
[29 Sep 2008 16:33] Konstantin Osipov
Now that we both have a new decimal type and dtoa library there is no reason to keep this bug in "To be fixed later" state.
[29 Sep 2008 16:38] Konstantin Osipov
WL#3977
[29 Sep 2008 16:42] Konstantin Osipov
Still reproducible in 6.0.8
[21 Jun 2015 15:41] Paul Graydon
Still reproduceable in 5.5.43
[11 Nov 2017 18:29] Federico Razzoli
Both tests still fail on 8.0.3:

mysql> create table t1 (f float, d double, cf char(20), cd char(20));
Query OK, 0 rows affected (0.26 sec)

mysql> insert into t1 (f, d) values (1.1, 1.1);
Query OK, 1 row affected (0.05 sec)

mysql> update t1 set cf=f, cd=d;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+------+-------------------+------+
| f    | d    | cf                | cd   |
+------+------+-------------------+------+
|  1.1 |  1.1 | 1.100000023841858 | 1.1  |
+------+------+-------------------+------+
1 row in set (0.00 sec)

mysql> select avg(f), avg(d) from t1;
+-------------------+--------+
| avg(f)            | avg(d) |
+-------------------+--------+
| 1.100000023841858 |    1.1 |
+-------------------+--------+
1 row in set (0.00 sec)
[11 Nov 2017 18:38] Alexey Kopytov
https://dev.mysql.com/worklog/task/?id=3977 explains the reasons for this inconsistency.