Bug #6961 Traditional: assignment of string to numeric column should fail
Submitted: 2 Dec 2004 19:13 Modified: 22 May 2005 23:47
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SuSE 9.1)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[2 Dec 2004 19:13] Trudy Pelzer
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.
[6 Apr 2005 23:18] Trudy Pelzer
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.
[13 May 2005 8:21] Bugs System
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
[18 May 2005 18:50] Michael Widenius
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)
[19 May 2005 9:53] Ramil Kalimullin
Fixed in 5.0.7
[19 May 2005 14:27] Paul Dubois
Noted in 5.0.7 changelog.
[19 May 2005 22:01] Magnus BlÄudd
Fixed in 5.0.6
[19 May 2005 22:48] Paul Dubois
Moved changelog entry from 5.0.7 to 5.0.6.