Bug #30453 String not cast to int correctly
Submitted: 16 Aug 2007 13:17 Modified: 14 Dec 2007 18:07
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.40/5.1 OS:Any
Assigned to: Alexey Kopytov CPU Architecture:Any

[16 Aug 2007 13:17] Baron Schwartz
Description:
MySQL casts a quoted string incorrectly upon insert to an integer column.

How to repeat:
mysql> create table trunc_test(i int not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into trunc_test(i) values(4188.32999999999992724042385816574096679687500), ('4188.32999999999992724042385816574096679687500'), (4188);
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'i' at row 2 | 
+---------+------+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from trunc_test;
+------------+
| i          |
+------------+
|       4188 | 
| 2147483647 | 
|       4188 | 
+------------+
3 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.40-log | 
+------------+
1 row in set (0.00 sec)

mysql> drop table trunc_test;
Query OK, 0 rows affected (0.00 sec)
[16 Aug 2007 13:44] MySQL Verification Team
Thank you for the bug report. Verified with 5.0/5.1 according as
described. Below 4.1 result:

[miguel@skybr 4.1]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.24-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table trunc_test(i int not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into trunc_test(i) values(4188.32999999999992724042385816574096679687500), ('4188.32999999999992724042385816574096679687500'), (4188);
Query OK, 3 rows affected, 1 warning (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> select * from trunc_test;
+------+
| i    |
+------+
| 4188 |
| 4188 |
| 4188 |
+------+
3 rows in set (0.04 sec)

mysql>
[12 Oct 2007 14:03] Konstantin Osipov
Upgrading since it's a regression.
[15 Oct 2007 6:34] 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/commits/35542

ChangeSet@1.2528, 2007-10-15 10:34:34+04:00, kaa@polly.(none) +3 -0
  Fix for bug #30453: String not cast to int correctly.
  
  Problem:
  
  my_strntoull10rnd_8bit() handled incorrectly cases when the input
  string contains a decimal point and is long enough to overrun the
  'unsigned long long' type. The position of the decimal point was not
  taken into account which resulted in miscalculated numbers and
  truncation to appropriate SQL data type limits.
  
  Solution:
  
  Fix my_strntoull10rnd_8bit() to take the position of a decimal point
  into account in such cases.
[15 Oct 2007 6:43] Alexey Kopytov
Bug #31104 was marked as a duplicate of this one.
[23 Oct 2007 7:40] Alexander Barkov
The patch http://lists.mysql.com/commits/35542 is ok to push.
[23 Oct 2007 9:02] Tatiana Azundris Nuernberg
OK to push
[7 Dec 2007 23:08] Bugs System
Pushed into 6.0.5-alpha
[7 Dec 2007 23:09] Bugs System
Pushed into 5.1.23-rc
[7 Dec 2007 23:10] Bugs System
Pushed into 5.0.54
[14 Dec 2007 18:07] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented in the 5.0.54, 5.1.23, and 6.0.5 changelogs as follows:

        When casting a string value to an integer, cases where the input
        string contained a decimal point and was long enough to overrun
        the unsigned long long type were not handled
        correctly. The position of the decimal point was not taken into
        account which resulted in miscalculated numbers and incorrect
        truncation to appropriate SQL data type limits.