Bug #6961 Traditional: assignment of string to numeric column should fail

Description:
When sql_mode='traditional', all assignments of values with an 
incorrect data type must fail. But if the assignment is of a math 
expression or is the result of an explicit CAST, the server accepts  
the change. 

How to repeat:
mysql> set sql_mode='traditional'; 
mysql> create table t1 (col1 smallint); 
 
mysql> insert into t1 values('a'); 
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'col1' at row 1 
-- This is the correct response; a string can't be assigned 
to a numeric column. 
 
mysql> insert into t1 values(cast('a' as unsigned integer)); 
Query OK, 1 row affected (0.00 sec) 
-- This is the incorrect response; it's no different from the 
previous example. The INSERT should fail with SQLSTATE HY000. 
 
mysql> insert into t1 values('a'+0); 
Query OK, 1 row affected (0.00 sec) 
-- This is also incorrect. It isn't possible to add a string to 
a number, so the INSERT should fail with SQLSTATE HY000. 
 
mysql> select * from t1; 
+------+ 
| col1 | 
+------+ 
|    0 | 
|    0 | 
+------+ 
2 rows in set (0.00 sec) 
-- The table should have zero rows.

More information on this issue.
The precision math changes seem to have corrected some of the
problem, but the following problem still exists with DECIMAL columns.

mysql> create table t (i int, d decimal, f float);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t (i) values ('1x');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into t (d) values ('1x');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into t (f) values ('1x');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t;
+------+------+------+
| i    | d    | f    |
+------+------+------+
|    1 | NULL | NULL |
| NULL | 1    | NULL |
| NULL | NULL |    1 |
+------+------+------+
3 rows in set (0.00 sec)
-- As expected; sql_mode='' so the source value gets truncated after the opening digit.

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

mysql> insert into t (i) values ('1x');
ERROR 1265 (01000): Data truncated for column 'i' at row 1
mysql> select * from t;
+------+------+------+
| i    | d    | f    |
+------+------+------+
|    1 | NULL | NULL |
| NULL | 1    | NULL |
| NULL | NULL |    1 |
+------+------+------+
3 rows in set (0.01 sec)
-- The correct response.

mysql> insert into t (d) values ('1x');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'd' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t;
+------+------+------+
| i    | d    | f    |
+------+------+------+
|    1 | NULL | NULL |
| NULL | 1    | NULL |
| NULL | NULL |    1 |
| NULL | 1    | NULL |
+------+------+------+
4 rows in set (0.00 sec)
-- Not correct. As with the INTEGER column, the INSERT must be rejected
because it is not possible to convert '1x' to a valid number.

mysql> insert into t (f) values ('1x');
ERROR 1265 (01000): Data truncated for column 'f' at row 1
mysql> select * from t;
+------+------+------+
| i    | d    | f    |
+------+------+------+
|    1 | NULL | NULL |
| NULL | 1    | NULL |
| NULL | NULL |    1 |
| NULL | 1    | NULL |
+------+------+------+
4 rows in set (0.00 sec)
-- This is correct.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24840

Note that we still allow assignment of a string to a numeric column
The issue is that we should only allow a string that can be converted to a number without any loss. (With is the behaviour after this patch)

Fixed in 5.0.7

Noted in 5.0.7 changelog.

Fixed in 5.0.6

Moved changelog entry from 5.0.7 to 5.0.6.