Bug #79690 Insert -0.1 and string '-0.1' into unsigned int returns different result

• Submitted: 17 Dec 2015 12:54
• Modified: 17 Dec 2015 17:52
• Reporter: Su Dylan
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8
• OS: Any
• CPU Architecture: Any

[17 Dec 2015 12:54] Su Dylan

Description:
Output:
=======
mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (uint8 int unsigned);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ('-0.1');
P TABQuery OK, 1 row affected (0.00 sec)

mysql> DROP TABLE t1;
EATE TABLE t1 (uiQuery OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (uint8 int unsigned);
-0.1);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (-0.1);
ERROR 1264 (22003): Out of range value for column 'uint8' at row 1
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
Insert -0.1 and string '-0.1' into column unsigned returns different result.

How to repeat:

DROP TABLE t1;
CREATE TABLE t1 (uint8 int unsigned);
INSERT INTO t1 VALUES ('-0.1'); 
DROP TABLE t1;
CREATE TABLE t1 (uint8 int unsigned);
INSERT INTO t1 VALUES (-0.1); 

Suggested fix:
Insert -0.1 and string '-0.1' into column unsigned int returns the same result.

[17 Dec 2015 17:52] MySQL Verification Team

Actually, the answer is quite simple.

Those two constants , one being string and the other a double, pass through two different set of functions when being converted to the unsigned integer.

When it is double, range checking if first done, which leads to "out of the range" error, as sign '-'  makes a result a negative one. 

When a constant is a string, it passes through the function for string conversion to the integer. This function is a little bit more flexible as it rounds the value to the nearest integer, which is 0. 

Hence, the explanation. Our documentation does not cover such internal details of our server functioning and it does not need to.

[17 Dec 2015 19:33] MySQL Verification Team

To put it in short term. A difference is in order in which conversion is performed in the conversion routines.

With the string, it's cast to int and rounded, THEN checked.
With the double, it's checked immediately, and found out of bounds.

So simply, our conversions do not process values in the same order. That is because the nature of each function and its algorithms dictate the behavior, which will not be changed.