Bug #102995 The result of AVG(REVERSE(FLOAT)) is incorrect
Submitted: 16 Mar 2021 9:59 Modified: 16 Mar 2021 13:45
Reporter: Guo Wenxuan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[16 Mar 2021 9:59] Guo Wenxuan
Description:
Consistent results for different values

How to repeat:
mysql> create table t1(col1 float);

mysql> insert into t1 values(1.0899e38);

mysql> insert into t1 values(2.19639e38);

mysql> insert into t1 values(-4.10048e37);

mysql> select REVERSE(col1) from t4;
+---------------+
| REVERSE(col1) |
+---------------+
| 83e9980.1     |
| 83e93691.2    |
| 73e84001.4-   |
+---------------+
3 rows in set (0.09 sec)

mysql> select avg(REVERSE(col1)) from t4 group by col1;
+------------------------+
| avg(REVERSE(col1))     |
+------------------------+
| 1.7976931348623157e308 |
| 1.7976931348623157e308 |
| 1.7976931348623157e308 |
+------------------------+
3 rows in set (0.09 sec)
[16 Mar 2021 13:21] Guo Wenxuan
Sorry, the table should be t1
[16 Mar 2021 13:45] MySQL Verification Team
Hi Mr. wen,

Thank you for your bug report.

However, this is not a bug.

You have applied the string function to the floating point number, which forced MySQL server to return maximum values for that type when AVG() function is applied.

Not a bug.