Bug #43930 Add sql_mode that throws warnings when rounding non-integer numbers to integers
Submitted: 28 Mar 2009 10:18
Reporter: Johan Idrén Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S4 (Feature request)
Version:5.0.x OS:Any
Assigned to: CPU Architecture:Any

[28 Mar 2009 10:18] Johan Idrén
Description:
If you try to add a non-number value in an integer column, you get a warning. But if you try to insert a non-integer number, it will round it to the nearest integer and insert the value, but WITHOUT any warning. 

How to repeat:
mysql> CREATE TABLE `table1` (`number` int(20) DEFAULT NULL) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into table1 values ('19.5454559');
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1;
+-------+
| number |
+-------+
| 20 | 
+-------+
1 row in set (0.00 sec)

mysql> insert into table1 values ('a9.5454559');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'a9.5454559' for column 'number' at row 1 | 
+---------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from table1;
+-------+
| phone |
+-------+
| 10 | 
+-------+

Suggested fix:
Please add a warning stating previous value and rounded value

Mockup:
mysql> insert into table1 values ('9.5454559');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------+
| Warning | XXX | Incorrect integer value: '9.5454559' for column 'number' at row 1, rounded to '10' | 
+---------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)