MySQL Bugs: #5931: Traditional: FLOAT and DOUBLE out-of-range values are accepted

Bug #5931	Traditional: FLOAT and DOUBLE out-of-range values are accepted
Submitted:	6 Oct 2004 18:05	Modified:	1 Apr 2005 9:38
Reporter:	Trudy Pelzer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:		CPU Architecture:	Any

Description:
When sql_mode='traditional', a numeric value that is  
out-of-range for the stated data type must be rejected. 
But FLOAT and DOUBLE PRECISION values that are lower 
than the documented range are being accepted. 
 
The MySQL Reference Manual says: 
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]  
A small (single-precision) floating-point number. Allowable values are  
-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to  
3.402823466E+38. 
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]  
A normal-size (double-precision) floating-point number. Allowable 
values are -1.7976931348623157E+308 to -2.2250738585072014E-308,  
0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. 
 
According to the SQL Standard, any number whose result 
includes an exponent that is outside of the documented  
range must be rejected with SQLSTATE 22003: Out of range value... 
 

How to repeat:
mysql> set sql_mode='traditional'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create table t1 (col1 float); 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> insert into t1 values (-1.1e-39); 
Query OK, 1 row affected (0.00 sec) 
-- Technically, this INSERT should be rejected with 
SQLSTATE 22003. But the result of the INSERT, 
see below, is exactly the same as the value inserted, 
so I think we can accept this. 
 
mysql> insert into t1 values (-1.1e-40); 
-- This is an incorrect response. The INSERT should be 
rejected with SQLSTATE 22003. 
 
mysql> select*  from t1; 
+-------------+ 
| col1        | 
+-------------+ 
|    -1.1e-39 | 
| -1.10001e-40 | 
+-------------+ 
2 rows in set (0.00 sec) 
 
mysql> create table t2 (col1 double precision); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into t2 values (-2.2e-309); 
Query OK, 1 row affected (0.00 sec) 
-- Technically, this INSERT should be rejected with 
SQLSTATE 22003. But the result of the INSERT, 
see below, is exactly the same as the value inserted, 
so I think we can accept this. 
 
mysql> insert into t2 values (-2.2e-310); 
Query OK, 1 row affected (0.00 sec) 
-- This is an incorrect response. The INSERT should be 
rejected with SQLSTATE 22003. 
 
mysql> select * from t2; 
+------------------------+ 
| col1                   | 
+------------------------+ 
|              -2.2e-309 | 
| -2.19999999999998e-310 | 
+------------------------+ 
2 rows in set (0.00 sec)

Verified against latest BK source tree.

For the moment we are not handling underflows in float/double in MySQL
This is something we will consider for future MySQL versions

Identical (except for last query's approximation) in 8.0.3:

mysql> set sql_mode='traditional'; 
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t1 values (-1.1e-39); 
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values (-1.1e-40); 
Query OK, 1 row affected (0.05 sec)

mysql> select*  from t1; 
+--------------+
| col1         |
+--------------+
|     -1.1e-39 |
| -1.10001e-40 |
+--------------+
2 rows in set (0.00 sec)

mysql> create table t2 (col1 double precision);
Query OK, 0 rows affected (0.25 sec)

mysql> insert into t2 values (-2.2e-309); 
Query OK, 1 row affected (0.04 sec)

mysql> insert into t2 values (-2.2e-310); 
Query OK, 1 row affected (0.05 sec)

mysql> select * from t2; 
+-----------+
| col1      |
+-----------+
| -2.2e-309 |
| -2.2e-310 |
+-----------+
2 rows in set (0.00 sec)