MySQL Bugs: #30453: String not cast to int correctly

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

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)

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>

Upgrading since it's a regression.

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.

Bug #31104 was marked as a duplicate of this one.

The patch http://lists.mysql.com/commits/35542 is ok to push.

OK to push

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.54

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.