Bug #4485 Floating point conversions are inconsistent.

• Submitted: 9 Jul 2004 14:23
• Modified: 29 Sep 2008 16:33
• Reporter: Konstantin Osipov (OCA)
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: All
• OS: Linux (Linux)
• CPU Architecture: Any
• Tags: affects_connectors

[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.