MySQL Bugs: #79628: Result type for max(float column) is FLOAT, while expecting DOUBLE

Bug #79628	Result type for max(float column) is FLOAT, while expecting DOUBLE
Submitted:	14 Dec 2015 9:28	Modified:	13 Nov 2018 13:00
Reporter:	Su Dylan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Output:
=======
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1( a float);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (98953287.79);
Query OK, 1 row affected (0.01 sec)

mysql> select max(a),a from t1;
Field   1:  `max(a)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       FLOAT
Collation:  binary (63)
Length:     23
Max_length: 8
Decimals:   31
Flags:      BINARY NUM

Field   2:  `a`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       FLOAT
Collation:  binary (63)
Length:     12
Max_length: 8
Decimals:   31
Flags:      NUM

+----------+----------+
| max(a)   | a        |
+----------+----------+
| 98953288 | 98953300 |
+----------+----------+
1 row in set (0.00 sec)

mysql> select version();
Field   1:  `version()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  ? (45)
Length:     32
Max_length: 8
Decimals:   31
Flags:      NOT_NULL

+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
=========
The type for max(a) should be DOUBLE. The length is 23(correct).

How to repeat:

drop table t1;
create table t1( a float);
insert into t1 values (98953287.79); 
select max(a),a from t1;

Suggested fix:
The type for max(a) is DOUBLE

Hi,

Thank you for your bug report.

However, I fail to see where is the problem. If you have a column of the type FLOAT, then its maximum value can not be larger then the maximum value that a FLOAT type can hold, according to IEEE standard. The range for that type is:

1.175494*10^-38 - 3.402823*10^38

You also have zero and the same range for the negative numbers.

Length is totally irrelevant in this respect.

What is relevant is a number of significant digits. For FLOATs is 8 digits, for DOUBLE it is 16 digits and for LONG DOUBLE is 20 digits.

Hence, FLOAT can not be converted into DOUBLE at all, except by a typecast.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".