Bug #115277 CAST from float to string produces inconsistent results
Submitted: 11 Jun 5:43 Modified: 11 Jun 9:30
Reporter: loing chen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: cast

[11 Jun 5:43] loing chen
Description:
> select cast(col as varchar) from t1;
+-----------------------+
| cast(col as varchar)  |
+-----------------------+
| -3.40282e38           |
+-----------------------+
1 row in set (0.01 sec)

> select f1(col) from t1;
+------------------------+
| f1(col)                |
+------------------------+
| -3.4028234663852886e38 |
+------------------------+
1 row in set (0.01 sec)

How to repeat:
CREATE TABLE t1 (col float) ;

INSERT INTO t1 VALUES (-3.402823466E+38);

delimiter |
create function f1(str varchar(1024)) returns varchar(1024)
begin
    return str;
end;|
delimiter ;

> select cast(col as varchar) from t1;
+-----------------------+
| cast(col as varchar)  |
+-----------------------+
| -3.40282e38           |
+-----------------------+
1 row in set (0.01 sec)

> select f1(col) from t1;
+------------------------+
| f1(col)                |
+------------------------+
| -3.4028234663852886e38 |
+------------------------+
1 row in set (0.01 sec)

Suggested fix:
When invoking the my_gcvt() function within the Field_str::store() method to convert a floating-point number into a string, it is imperative not to indiscriminately use MY_GCVT_ARG_DOUBLE. Instead, the choice should be contingent upon the actual parameter type: for float values, MY_GCVT_ARG_FLOAT must be employed to ensure accurate representation.
[11 Jun 9:30] MySQL Verification Team
Hi Mr. chen,

Thank you for your bug report.

However, it is not a bug.

Simply, your stored function goes against SQL Standard. SQL Standard prescribes that mixing of different data types should always return an error.

MySQL goes a step further and tries to do more. So, MySQL has adopted a set of common denominators. When converting any floating point to CHAR type, or the other way around, a DOUBLE is used for the better precision. 

Since, this is not prescribed in SQL Standard, this set of denominators is here to stay and it will not be changed.

Not a bug.