| 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: | |
| 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 |
[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.
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.