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)
