MySQL Bugs: #6147: Traditional: Assigning a string to a numeric column has unexpected results

Bug #6147	Traditional: Assigning a string to a numeric column has unexpected results
Submitted:	18 Oct 2004 20:03	Modified:	4 Oct 2006 1:30
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
When sql_mode='traditional', all numbers that are out-of-range 
for a data type must be rejected. Also, according to WL#2038, 
MySQL should round/truncate values the same way as Oracle 
does. But we are  not correctly interpreting strings assigned to 
a numeric column; while Oracle first converts the string to a 
number, and then makes the rounding/truncation decision, MySQL 
appears to truncate the string and then do the conversion,  
leading to unexpected behaviour. 
 
 

How to repeat:
mysql> set sql_mode='traditional'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create table t1 (col1 tinyint); 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> insert into t1 values (127); 
Query OK, 1 row affected (0.00 sec) 
mysql> insert into t1 values (127.1); 
Query OK, 1 row affected (0.00 sec) 
-- These both show the correct response. Like Oracle, 
MySQL rounds the value 127.1 to 127. 
 
mysql> insert into t1 values (127.5); 
ERROR 1264 (22003): Out of range value adjusted for column 'col1' at row 1 
mysql> insert into t1 values (127.9); 
ERROR 1264 (22003): Out of range value adjusted for column 'col1' at row 1 
-- These both show the correct response. Like Oracle, 
MySQL rounds both values to 128, which is out-of-range for TINYINT. 
 
mysql> select * from t1; 
+------+ 
| col1 | 
+------+ 
|  127 | 
|  127 | 
+------+ 
-- Table t1 correctly has two rows. 
 
mysql> insert into t1 values ('127'); 
Query OK, 1 row affected (0.01 sec) 
-- This is the correct response. 
 
mysql> insert into t1 values ('127.1'); 
ERROR 1265 (01000): Data truncated for column 'col1' at row 1 
-- This is the incorrect response. '127.1' should be converted to the  
number 127.1, then  rounded to 127 (as above and with Oracle). 
 
mysql> insert into t1 values ('127.5'); 
ERROR 1265 (01000): Data truncated for column 'col1' at row 1 
mysql> insert into t1 values ('127.9'); 
ERROR 1265 (01000): Data truncated for column 'col1' at row 1 
-- Both of these INSERTs are rejected, but for the wrong reason. 
The error should be SQLSTATE 22003, rather than string truncartion. 
 
mysql> select * from t1; 
+------+ 
| col1 | 
+------+ 
|  127 | 
|  127 | 
|  127 | 
+------+ 
-- Table t1 should have 4 rows, all 127. 
 
mysql> delete from t1; 
 
mysql> insert into t1 values(cast(127.5 as unsigned integer)); 
ERROR 1264 (22003): Out of range value adjusted for column 'col1' at row 1 
-- This is the correct response. 
 
mysql> insert into t1 values(cast('127.5' as unsigned integer)); 
Query OK, 1 row affected (0.00 sec) 
-- This is the incorrect response. The explicit CAST of the string should  
have the same result as in the previous example, i.e. the INSERT  
should be rejected with SQLSTATE 22003. (because the CAST result  
should be the number 128). 
 
mysql> insert into t1 values(convert(127.5,unsigned integer)); 
ERROR 1264 (22003): Out of range value adjusted for column 'col1' at row 1 
-- This is the correct result. 
 
mysql> insert into t1 values(convert('127.5',unsigned integer)); 
Query OK, 1 row affected (0.00 sec) 
-- This is the incorrect response. The explicit CONVERT of the string should  
have the same result as in the previous example, i.e. the INSERT  
should be rejected with SQLSTATE 22003. (because the CONVERT result  
should be the number 128).

Verified on latest Bk source.

More information on this issue.
The problem appears to be connected to how MySQL does rounding
on INSERT. The rounding should be based on the data type of the
target, and is being done correctly unless (a) the source is a string that
is being converted to a number and (b) the target is an integer:

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

mysql> insert into t values (2.44,2.44,2.44,2.44);
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> insert into t values (2.55,2.55,2.55,2.55);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> insert into t values (2.66,2.66,2.66,2.66);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from t;
+------+------+------+------+
| i    | f    | d0   | d1   |
+------+------+------+------+
|    2 | 2.44 | 2    | 2.4  |
|    3 | 2.55 | 3    | 2.6  |
|    3 | 2.66 | 3    | 2.7  |
+------+------+------+------+
3 rows in set (0.00 sec)
--As expected; exact numeric source gets rounded based on data type of 
target (round half-up for i, d0, d1; no rounding for float)

mysql> insert into t values ('2.44','2.44','2.44','2.44');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> insert into t values ('2.55','2.55','2.55','2.55');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> insert into t values ('2.66','2.66','2.66','2.66');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> select * from t;
+------+------+------+------+
| i    | f    | d0   | d1   |
+------+------+------+------+
|    2 | 2.44 | 2    | 2.4  |
|    3 | 2.55 | 3    | 2.6  |
|    3 | 2.66 | 3    | 2.7  |
|    2 | 2.44 | 2    | 2.4  |
|    2 | 2.55 | 3    | 2.6  |
|    2 | 2.66 | 3    | 2.7  |
+------+------+------+------+
6 rows in set (0.00 sec)
-- Not as expected; approximate numeric source should get rounded based on data 
type of target. Float decimal, and decimal(3,1) columns are treated correctly; integer 
column is treated incorrectly, it should result in 2,3,3. Conclusion: bug in INSERT
for integers when source is approximate numeric *if* source is approximate numeric
because it's a string

mysql> insert into t values (2.44e0,2.44e0,2.44e0,2.44e0);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> insert into t values (2.55e0,2.55e0,2.55e0,2.55e0);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> insert into t values (2.66e0,2.66e0,2.66e0,2.66e0);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from t;
+------+------+------+------+
| i    | f    | d0   | d1   |
+------+------+------+------+
|    2 | 2.44 | 2    | 2.4  |
|    3 | 2.55 | 3    | 2.6  |
|    3 | 2.66 | 3    | 2.7  |
|    2 | 2.44 | 2    | 2.4  |
|    2 | 2.55 | 3    | 2.6  |
|    2 | 2.66 | 3    | 2.7  |
|    2 | 2.44 | 2    | 2.4  |
|    3 | 2.55 | 3    | 2.6  |
|    3 | 2.66 | 3    | 2.7  |
+------+------+------+------+
9 rows in set (0.00 sec)
-- As expected; approximate numeric source being rounded based on data type of target.
Note that when the approximate numeric source is a numeric literal rather than a string,
the rounding into an integer target is correct.

mysql> insert into t values ('2.44e0','2.44e0','2.44e0','2.44e0');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> insert into t values ('2.55e0','2.55e0','2.55e0','2.55e0');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> insert into t values ('2.66e0','2.66e0','2.66e0','2.66e0');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> select * from t;
+------+------+------+------+
| i    | f    | d0   | d1   |
+------+------+------+------+
|    2 | 2.44 | 2    | 2.4  |
|    3 | 2.55 | 3    | 2.6  |
|    3 | 2.66 | 3    | 2.7  |
|    2 | 2.44 | 2    | 2.4  |
|    2 | 2.55 | 3    | 2.6  |
|    2 | 2.66 | 3    | 2.7  |
|    2 | 2.44 | 2    | 2.4  |
|    3 | 2.55 | 3    | 2.6  |
|    3 | 2.66 | 3    | 2.7  |
|    2 | 2.44 | 2    | 2.4  |
|    2 | 2.55 | 3    | 2.6  |
|    2 | 2.66 | 3    | 2.7  |
+------+------+------+------+
12 rows in set (0.00 sec)
-- Not as expected; again, approximate numeric source into integer target should be 
rounded based on data type of target (correct result is 2,3,3), all other targets show 
correct result. Note again the fact that it's because the source is a string that the
integer problem occurs.

Additional information regarding incorrect assignment
of numeric values specified as strings when inserted
into integer columns.

I believe the results can be explained as follows:  If MySQL
is inserting into an INT column, and the source is a string,
MySQL tries to convert it to a number but stops at the decimal
point.  (Probably this is more general, like "stops at first non-digit"
but it does appear to stop at the decimal point.)

Test script:

drop table if exists t;
create table t (i int, d decimal);
insert into t values(2.55,2.55);
insert into t values(2.55e0,2.55e0);
insert into t values(2.55e1,2.55e1);
insert into t values('2.55','2.55');
insert into t values('2.55e0','2.55e0');
insert into t values('2.55e1','2.55e1');
insert into t values('2.55e2','2.55e2');
select * from t;

The last few rows insert strings, so they require 
string-to-number conversion.  If that conversion 
is always really to double, the results for both  
columns should, I believe, be the same.  But
here's the result:

+------+------+
| i    | d    |
+------+------+
|    3 | 3    |
|    3 | 3    |
|   26 | 26   |
|    2 | 3    |
|    2 | 3    |
|    2 | 26   |
|    2 | 255  |
+------+------+

Note the INT column results.  It appears that when
the target column is an integer, the
string-to-number conversion is not "to double" but
"until the first non-digit" or something.  The
conversion appears to stop at the decimal point
because it entirely ignores the exponent.

The problem is demonstrated most clearly by the
values expressed as strings are numbers in
scientific notation.  But the conversion problem
is more general in that, for values with any
fractional part, the fractional part will be
truncated, not rounded.

I can verify this in 5.0.18 on several different platforms
Several scripts stoped working after migration from 4.1 to 5.0.
It seems to me that this bug will actually stop migration of many hosts to the new version.

For instance a very popular Invision Power Board script(latest version) will not even install properly on 5.0.18 because of this problem.

Assigning an empty string to an int column is the problem: example query is:
INSERT INTO column (id, something) VALUES ('','some data')

where id is an int.
Beats me why does this happend but i know for sure that the problem is fixed as soon as i change type of the "id" column to something like varchar

discussed briefly with monty, and he has suggested making the string-to-integer conversion routines (like strntoll10, or something like that) handle doing the rounding, rather than any sort of string->decimal->integer conversion.

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/commits/7268

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/commits/9366

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/commits/12925

ChangeSet@1.2286, 2006-10-02 14:17:41+05:00, bar@mysql.com +1 -0
  bug#6147 - fixing ndb test results (forgot to include into the main commit)

Pushed into 5.0-rpl, currently marked as 5.0.26

Available in 5.0.26.

Available in 5.1.12-beta.

Noted in 5.0.26, 5.1.12 changelogs.

With TRADITIONAL SQL mode, assignment of out-of-bound values and
rounding of assigned values was done correctly, but assignment of the
same numbers represented as strings sometimes was handled
differently.