Description:
MySQL 5.6 manual says :
The maximum number of digits for DECIMAL is 65, but the actual range for a given DECIMAL column can be constrained by the precision or scale for a given column. When such a column is assigned a value with more digits following the decimal point than are permitted by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the permissible number of digits.)
https://dev.mysql.com/doc/refman/5.6/en/fixed-point-types.html
Suppose that you have a DECIMAL(4,2) and you insert 1.234. The data will be truncated to 1.23 with a warning even in sql_mode = STRICT_TRANS_TABLES.
The feature request is to add a more that detects this case and throws an error.
Current behavior :
MySQL [test]> insert into t(c) values (1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)
MySQL [test]> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------+
| Note | 1265 | Data truncated for column 'c' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)
MySQL [test]> select * from t;
+------+
| c |
+------+
| 1.23 |
+------+
1 row in set (0.00 sec)
How to repeat:
use test;
set sql_mode = 'STRICT_TRANS_TABLES';
drop table if exists t;
create table t(c DECIMAL(4,2) not null);
insert into t(c) values (1.234);
show warnings;
select * from t;
Suggested fix:
Throw an error when truncation happens in strict mode, instead of a warning.
Maybe a new sql_mode is required for backward compatibility.
