Description:
When sql_mode='traditional', all columns with a numeric
data type must reject out of range values. But if the
data type is NUMERIC, MySQL accepts out of range values.
Note: Per the SQL Standard, the difference between
the DECIMAL and NUMERIC data types is that, for
DECIMAL, the column may accept numbers with a
greater precision than the defined precision but
NUMERIC may not. Thus, a column defined as
DECIMAL(5,2) may accept values greater than
999.99 (e.g. 1000.00) and/or less than -999.99
(e.g. -1000.00) but a NUMERIC(5,2) column may
accept only values between -999.99 and +999.99.
How to repeat:
mysql> CREATE TABLE t1 (col1 NUMERIC(4,2));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (10.55);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (-10.55);
Query OK, 1 row affected (0.00 sec)
-- These both show the correct response; the values
exactly match the defined precision.
mysql> INSERT INTO t1 VALUES (10.5555);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (-10.5555);
Query OK, 1 row affected (0.00 sec)
-- These both show the correct response; the extra
digits in the scale will be rounded, as it normal.
mysql> INSERT INTO t1 VALUES (101.55);
Query OK, 1 row affected (0.00 sec)
-- This is the incorrect response; a 3 digit precision
cannot be assigned to a column that allows only
2 digit precision. The INSERT should fail with
SQLSTATE 22003 numeric value out of range.
