Bug #115277 CAST from float to string produces inconsistent results

• Submitted: 11 Jun 2024 5:43
• Modified: 11 Jun 2024 9:30
• Reporter: loing chen
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: cast

[11 Jun 2024 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 2024 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.