Description:
The manual page "Floating-Point Types (Approximate Value) - FLOAT, DOUBLE"
states in all current versions (8.0, 8.4, 9.x):
"A precision from 0 to 23 results in a 4-byte single-precision FLOAT
column. A precision from 24 to 53 results in an 8-byte double-precision
DOUBLE column."
e.g. https://dev.mysql.com/doc/refman/8.4/en/floating-point-types.html
This contradicts both the actual server behavior and another page of the
same manual. The "Numeric Data Type Syntax" page states correctly:
"If p is from 0 to 24, the data type becomes FLOAT with no M or D
values. If p is from 25 to 53, the data type becomes DOUBLE with no
M or D values."
https://dev.mysql.com/doc/refman/8.4/en/numeric-type-syntax.html
The server implements the latter: FLOAT(24) creates a 4-byte FLOAT
column; only FLOAT(25) and above create a DOUBLE column. This is
intentional and correct, since IEEE 754 single precision has 24
significand bits (23 stored + 1 implicit), so a precision of 24 bits
still fits into a 4-byte FLOAT. The implementation in
sql/create_field.cc (identical in 8.0 and 8.4) uses
PRECISION_FOR_FLOAT = 24:
/* change FLOAT(precision) to FLOAT or DOUBLE */
if (display_width_in_codepoints && !fld_decimals) {
const size_t tmp_length = m_max_display_width_in_codepoints;
if (tmp_length > PRECISION_FOR_DOUBLE) {
my_error(ER_WRONG_FIELD_SPEC, MYF(0), fld_name);
return true;
} else if (tmp_length > PRECISION_FOR_FLOAT) {
sql_type = MYSQL_TYPE_DOUBLE;
...
i.e. DOUBLE is chosen only for p > 24. The floating-point-types page is
therefore off by one at the lower boundary; it should read 0 to 24 /
25 to 53, matching the numeric-type-syntax page.
For reference, other systems document the same boundary that MySQL
actually implements: MariaDB (0-24 -> FLOAT, 25-53 -> DOUBLE),
PostgreSQL (float(1)-float(24) -> real, float(25)-float(53) -> double
precision), and SQL Server (n = 1-24 -> 4 bytes with REAL defined as
float(24), n = 25-53 -> 8 bytes).
How to repeat:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 9.4.0 |
+-----------+
mysql> CREATE TABLE t (a FLOAT(23), b FLOAT(24), c FLOAT(25));
Query OK, 0 rows affected
mysql> DESCRIBE t;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a | float | YES | | NULL | |
| b | float | YES | | NULL | |
| c | double | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
According to the floating-point-types page, column b (FLOAT(24)) should
be DOUBLE. It is FLOAT, as correctly described on the
numeric-type-syntax page. Verified on 9.4.0 and 8.3.0 (Docker official
images).
Suggested fix:
On https://dev.mysql.com/doc/refman/<version>/en/floating-point-types.html
(all maintained versions), change the sentence to:
"A precision from 0 to 24 results in a 4-byte single-precision FLOAT
column. A precision from 25 to 53 results in an 8-byte double-precision
DOUBLE column."
This aligns it with the numeric-type-syntax page and the actual
server behavior.
